How to define composite primary key in SQLAlchemy

Question

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.

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')

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).

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?

I'm not really sure, I get what you are doing. If the value already exists, you need to query for it, not create a new one: a = session.query(table1).filter(table1.col1 == 'test').filter(table1.col2 == 'test').one(). If this was not clear to you then I strongly suggest to take the tutorials provided in the documentation. If I am wrong about your question, please elaborate. — javex
There is not enough info about what is going on. How to create a Minimal, Complete, and Verifiable example It seems that with both composite and non-composite keys you are trying to add a row that has a key subrow value that is already in a table, but the key declaration says that a given key subrow value can only appear once in a table, hence the error messages. As I write, this question has a bounty from TerrenceBrannon, who might be better off posting a new question so that they can give sufficient details from their own situation. — philipxy

Ali Cirik Ali Cirik · Accepted Answer · 2015-05-06T04:26:19

I agree that the question is vague. But you can use the following as a guideline. This will select from a trial1 table in a test database in MySQL. Commented out parts are there as an alternative way to setup primary key constraints.

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)

How to define composite primary key in SQLAlchemy

1 Answers