Python 如何在 SQLAlchemy 中定义复合主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19129289/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to define composite primary key in SQLAlchemy
提问by Charlie Carwile
I'm trying to use SQLAlchemy with MySQL to create a table mapping for a table with a composite primary key, and I'm unsure if I'm doing it right. The existing table is defined with the composite primary key.
我正在尝试将 SQLAlchemy 与 MySQL 一起使用，为具有复合主键的表创建表映射，但我不确定我是否做得对。现有表是用复合主键定义的。
Here's the mapping class definition:
这是映射类定义：
class table1(Base):
__tablename__ = 'table1'
col1 = Column(String, primary_key=True)
col2 = Column(String, primary_key=True)
col3 = Column(String)
def __init__ = (self, col1, col2, col3):
self.col1 = col1
self.col2 = col2
self.col3 = col3
this matches a record already in the database a = table1('test', 'test', 'test')
这匹配数据库中已有的记录 a = table1('test', 'test', 'test')
If I add this to the session and add the records in the table, then work with the data, I get a MySQL error (1062 Duplicate Entry).
如果我将其添加到会话并在表中添加记录，然后处理数据，我会收到 MySQL 错误（1062 重复条目）。
session.add(a)
b = session.query(table1)
for instance in b:
print(instance.col1, instance.col2)
If I'm working with a single-key table, I get this error instead:
如果我正在使用单键表，则会收到此错误：
New instance <table2 at 0x2f204d0> with identity key
(<class '__main__.table2'>,('test',)) conflicts with
persistent instance <table2 at 0x2f88770>
Am I defining the composite primary key incorrectly? If not, what am I doing wrong further down for me to get the MySQL error instead of a Python/SQLAlchemy error?
我是否错误地定义了复合主键？如果没有，我在进一步做错了什么以获得 MySQL 错误而不是 Python/SQLAlchemy 错误？
回答by Ali Cirik
I agree that the question is vague. But you can use the following as a guideline. This will select from a trial1table in a testdatabase in MySQL. Commented out parts are there as an alternative way to setup primary key constraints.
我同意这个问题是模糊的。但是您可以使用以下内容作为指导。这将从MySQL 数据库中的trial1表中进行选择test。注释掉的部分是设置主键约束的另一种方法。
from sqlalchemy import String, create_engine, MetaData, Column
from sqlalchemy.ext.declarative import declarative_base
# from sqlalchemy.schema import PrimaryKeyConstraint
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+pymysql://root:[email protected]/test')
metadata = MetaData(bind=engine)
Base = declarative_base(metadata=metadata)
class TableClassName(Base):
__tablename__ = 'table1'
col1 = Column(String, primary_key=True)
col2 = Column(String, primary_key=True)
col3 = Column(String)
# __table_args__ = (
# PrimaryKeyConstraint(
# col1,
# col2),
# {})
Session = sessionmaker(bind=engine)
session = Session()
b = session.query(TableClassName)
for instance in b:
print(instance.col1, instance.col2)
