python - SQLAlchemy not producing proper SQL statement for multi column UniqueConstraints -
below 2 different attempts have made in trying achieve multi-column unique constraint in sqlalchemy, both of seems have failed since proper sql statement not being produced.
the attempts:
from sqlalchemy import column, integer, string, text, foreignkey, datetime, create_engine, uniqueconstraint, boolean sqlalchemy.orm import relationship, backref, sessionmaker sqlalchemy.ext.declarative import declarative_base sqlalchemy.interfaces import poollistener import sqlalchemy class foreignkeyslistener(poollistener): def connect(self, dbapi_con, con_record): db_cursor = dbapi_con.execute('pragma foreign_keys=on') engine = create_engine(r"sqlite:///" + r"d:\\foo.db", listeners=[foreignkeyslistener()], echo = true) session = sessionmaker(bind = engine) ses = session() base = declarative_base() print sqlalchemy.__version__ class foo(base): __tablename__ = "foo" id = column(integer, primary_key=true) dummy = column(integer, unique = true) class bar(base): __tablename__ = "bar" id = column(integer, primary_key=true) baz = column(integer, foreignkey("foo.id")) qux = column(integer, foreignkey("foo.id")) uniqueconstraint("baz", "qux") class cruft(base): __tablename__ = "cruft" id = column(integer, primary_key=true) bar = column(integer, foreignkey("foo.id")) qux = column(integer, foreignkey("foo.id")) __table_args = (uniqueconstraint("bar", "qux"),) base.metadata.create_all(engine) the output:
>>> 0.8.2 2013-05-09 16:25:42,677 info sqlalchemy.engine.base.engine pragma table_info("foo") 2013-05-09 16:25:42,677 info sqlalchemy.engine.base.engine () 2013-05-09 16:25:42,677 info sqlalchemy.engine.base.engine pragma table_info("bar") 2013-05-09 16:25:42,677 info sqlalchemy.engine.base.engine () 2013-05-09 16:25:42,677 info sqlalchemy.engine.base.engine pragma table_info("cruft") 2013-05-09 16:25:42,677 info sqlalchemy.engine.base.engine () 2013-05-09 16:25:42,677 info sqlalchemy.engine.base.engine create table foo ( id integer not null, dummy integer, primary key (id), unique (dummy) ) 2013-05-09 16:25:42,677 info sqlalchemy.engine.base.engine () 2013-05-09 16:25:42,767 info sqlalchemy.engine.base.engine commit 2013-05-09 16:25:42,769 info sqlalchemy.engine.base.engine create table bar ( id integer not null, baz integer, qux integer, primary key (id), foreign key(baz) references foo (id), foreign key(qux) references foo (id) ) 2013-05-09 16:25:42,769 info sqlalchemy.engine.base.engine () 2013-05-09 16:25:42,838 info sqlalchemy.engine.base.engine commit 2013-05-09 16:25:42,839 info sqlalchemy.engine.base.engine create table cruft ( id integer not null, bar integer, qux integer, primary key (id), foreign key(bar) references foo (id), foreign key(qux) references foo (id) ) 2013-05-09 16:25:42,839 info sqlalchemy.engine.base.engine () 2013-05-09 16:25:42,917 info sqlalchemy.engine.base.engine commit any suggestions?
when using uniqueconstraint in declarative table configuration, need specify using __table_args__ attribute (note underscores on both sides of name:
class bar(base): __tablename__ = "bar" __table_args__ = (uniqueconstraint("baz", "qux"),) id = column(integer, primary_key=true) baz = column(integer, foreignkey("foo.id")) qux = column(integer, foreignkey("foo.id")) class cruft(base): __tablename__ = "cruft" __table_args__ = (uniqueconstraint("bar", "qux"),) id = column(integer, primary_key=true) bar = column(integer, foreignkey("foo.id")) qux = column(integer, foreignkey("foo.id")) the attribute must either tuple or dictionary.
creating these 2 tables results in:
2013-05-09 13:38:44,180 info sqlalchemy.engine.base.engine create table cruft ( id integer not null, bar integer, qux integer, primary key (id), unique (bar, qux), foreign key(bar) references foo (id), foreign key(qux) references foo (id) ) ... 2013-05-09 13:38:44,181 info sqlalchemy.engine.base.engine create table bar ( id integer not null, baz integer, qux integer, primary key (id), unique (baz, qux), foreign key(baz) references foo (id), foreign key(qux) references foo (id) ) also see setting constraints when using declarative orm extension section of defining constraints , indexes chapter.
Comments
Post a Comment