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() -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
