Why does Session.merge only look at primary key and not all unique keys?

Russell Warren

unread,

Dec 15, 2010, 5:14:15 PM12/15/10

to sqlalchemy

Why does Session.merge only look at primary key and not all unique
keys?

Leaving aside some irritating DBMS restrictions on PKs and some
automatic indexing that tends to happen, the PK is not fundamentally
different than other unique keys and I don't see why SQLA
distinguishes them from an integrity/relationship perspective. In
databases where it is already frustrating that they have funky PK
restrictions it is tough to make merge() work the way it seems it
should. For example, in the code below this post, Sqlite requires the
autoincrementing field to be the PK, and you can't composite it with
another field... with these restrictions I can't get merge() to work
the way "it should".

I was looking for a clean way in SQLAlchemy to do an "insert if not
exists" pattern, and merge() looked perfect, but I can't make it work
at the moment.

I'm also aware that in the sample code the 'name' field should really
just be the "primary" key and the problem goes away, but the reality
of the grander/real scheme is that the linking id is needed in
addition to other unique keys.

In addition to the docs, these existing threads are also very
relevant:

http://groups.google.com/group/sqlalchemy/browse_frm/thread/7483736b46d56943
http://groups.google.com/group/sqlalchemy/browse_thread/thread/79736ff7ef81d1b9/0b80b54dc45ecc28

To make the "insert if not exists" pattern work I'll likely/
reluctantly be doing the __new__ hack referred to in the latter thread
to achieve what I'm after in the end, but I really don't get why the
PK is getting special treatment.

Thanks,
Russ

Sample code:
####

from sqlalchemy import Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
import logging

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()
DeclarativeBase = declarative_base()

#Configure some clean and indented SQL logging...
class SqlFormatter(logging.Formatter):
def format(self, record):
prefix = " SQL >> "
record.msg = prefix + record.msg.replace("\n", "\n" + prefix)
return logging.Formatter.format(self, record)
sqlaLogger = logging.getLogger('sqlalchemy.engine')
sqlaLogger.setLevel(logging.INFO)
handler = logging.StreamHandler()
handler.setFormatter(SqlFormatter("%(message)s"))
sqlaLogger.addHandler(handler)

class MyStuff(DeclarativeBase):
__tablename__ = 'mystuff'

#Config below
id = Column(Integer, primary_key = True, autoincrement = True)
name = Column(String(100), nullable = False, unique = True)

#Config below no good due to composite PK...
#id = Column(Integer, primary_key = True, autoincrement = True)
#name = Column(String(100), nullable = False, primary_key = True)

#Config below doesn't give autoincrement...
#id = Column(Integer, primary_key = False, autoincrement = True)
#name = Column(String(100), nullable = False, primary_key = True)

def __init__(self, Name):
self.name = Name

DeclarativeBase.metadata.create_all(engine)
Session = sessionmaker(bind = engine)

print "Attempting 'foo' merge into empty DB..."
s1 = Session()
foo = s1.merge(MyStuff("foo"))
s1.commit()
s1.close()

print "Attempting 'foo' merge after it exists already..."
s2 = Session()
foo = s2.merge(MyStuff("foo"))
s2.commit()
s2.close()

Russell Warren

unread,

Dec 15, 2010, 5:25:13 PM12/15/10

to sqlalchemy

Whoops - my clipboard had an old pre-cleaned sample code in it that I
pasted. Sorry about the excess lines... maybe the logging mod will be
useful for some people, though?

The code is still right, just not fully cleaned up. The first Column
config is the one that works around the Sqlite PK/autoincrement
restriction, but doesn't work with merge() because it doesn't pick up
on the fact that 'foo' already exists and the merge tries to insert it
again, throwing an IntegrityError.

Michael Bayer