SQLAlchemy 没有为多列 UniqueConstraint 生成正确的 SQL 语句 [英] SQLAlchemy not producing proper SQL statement for multi column UniqueConstraints
问题描述
以下是我尝试在 sqlalchemy 中实现多列唯一约束时所做的两种不同尝试,由于未生成正确的 SQL 语句,这两种尝试似乎都失败了.
尝试:
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, create_engine, UniqueConstraint, Boolean来自 sqlalchemy.orm 的导入关系、backref、sessionmaker从 sqlalchemy.ext.declarative 导入 declarative_base从 sqlalchemy.interfaces 导入 PoolListener导入 sqlalchemy类外键监听器(池监听器):定义连接(自我,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)会话 = sessionmaker(绑定 = 引擎)ses = 会话()Base = declarative_base()打印 sqlalchemy.__version__类 Foo(Base):__tablename__ = "foo"id = 列(整数,primary_key=True)虚拟 = 列(整数,唯一 = 真)班级酒吧(基地):__tablename__ = "bar"id = 列(整数,primary_key=True)baz = Column(Integer, ForeignKey("foo.id"))qux = Column(Integer, ForeignKey("foo.id"))UniqueConstraint("baz", "qux")类 Cruft(Base):__tablename__ = "cruft"id = 列(整数,primary_key=True)bar = Column(Integer, ForeignKey("foo.id"))qux = Column(Integer, ForeignKey("foo.id"))__table_args = (UniqueConstraint("bar", "qux"),)Base.metadata.create_all(引擎)
输出:
<预><代码>>>>0.8.22013-05-09 16:25:42,677 信息 sqlalchemy.engine.base.Engine PRAGMA table_info("foo")2013-05-09 16:25:42,677 信息 sqlalchemy.engine.base.Engine ()2013-05-09 16:25:42,677 信息 sqlalchemy.engine.base.Engine PRAGMA table_info("bar")2013-05-09 16:25:42,677 信息 sqlalchemy.engine.base.Engine ()2013-05-09 16:25:42,677 信息 sqlalchemy.engine.base.Engine PRAGMA table_info("cruft")2013-05-09 16:25:42,677 信息 sqlalchemy.engine.base.Engine ()2013-05-09 16:25:42,677 信息 sqlalchemy.engine.base.Engine创建表 foo (id 整数非空,虚拟整数,主键 (id),独一无二(假人))2013-05-09 16:25:42,677 信息 sqlalchemy.engine.base.Engine ()2013-05-09 16:25:42,767 信息 sqlalchemy.engine.base.Engine COMMIT2013-05-09 16:25:42,769 信息 sqlalchemy.engine.base.Engine创建表格栏(id 整数非空,巴兹整数,qux整数,主键 (id),外键 (baz) 参考 foo (id),外键 (qux) 参考 foo (id))2013-05-09 16:25:42,769 信息 sqlalchemy.engine.base.Engine ()2013-05-09 16:25:42,838 信息 sqlalchemy.engine.base.Engine COMMIT2013-05-09 16:25:42,839 信息 sqlalchemy.engine.base.Engine创建表 cruft (id 整数非空,酒吧整数,qux整数,主键 (id),外键(bar) REFERENCES foo (id),外键 (qux) 参考 foo (id))2013-05-09 16:25:42,839 信息 sqlalchemy.engine.base.Engine ()2013-05-09 16:25:42,917 信息 sqlalchemy.engine.base.Engine COMMIT有什么建议吗?
在声明性表配置中使用 UniqueConstraint
时,需要使用 __table_args__
属性(注意both 名称的两侧:
class Bar(Base):__tablename__ = "bar"__table_args__ = (UniqueConstraint("baz", "qux"),)id = 列(整数,primary_key=True)baz = Column(Integer, ForeignKey("foo.id"))qux = Column(Integer, ForeignKey("foo.id"))类 Cruft(Base):__tablename__ = "cruft"__table_args__ = (UniqueConstraint("bar", "qux"),)id = 列(整数,primary_key=True)bar = Column(Integer, ForeignKey("foo.id"))qux = Column(Integer, ForeignKey("foo.id"))
属性必须是元组或字典.
现在创建这两个表的结果是:
2013-05-09 13:38:44,180 INFO sqlalchemy.engine.base.Engine创建表 cruft (id 整数非空,酒吧整数,qux整数,主键 (id),唯一 (bar, qux),外键(bar) REFERENCES foo (id),外键 (qux) 参考 foo (id))...2013-05-09 13:38:44,181 信息 sqlalchemy.engine.base.Engine创建表格栏(id 整数非空,巴兹整数,qux整数,主键 (id),独特 (baz, qux),外键 (baz) 参考 foo (id),外键 (qux) 参考 foo (id))
另请参阅 在使用声明性 ORM 扩展时设置约束部分定义约束和索引一章.
Below are the two different attempts I have made in trying to achieve a multi-column unique constraint in sqlalchemy, both of which seems to have failed since a proper SQL statement is not being produced.
The attempts:
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, create_engine, UniqueConstraint, Boolean
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from 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 a UniqueConstraint
in a declarative table configuration, you need to specify it using the __table_args__
attribute (note the underscores on both sides of the 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 be either a tuple or a dictionary.
Creating these two tables now 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 the Setting up Constraints when using the Declarative ORM Extension section of the Defining Constraints and Indexes chapter.
这篇关于SQLAlchemy 没有为多列 UniqueConstraint 生成正确的 SQL 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!