Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to define composite primary key in SQLAlchemy

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?

like image 850
Charlie Carwile Avatar asked Oct 02 '13 02:10

Charlie Carwile


People also ask

How do you use a composite primary key?

A composite key is made by the combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness of a row is guaranteed, but when it is taken individually it does not guarantee uniqueness, or it can also be understood as a primary key made ...

Does SQLAlchemy require primary key?

¶ The SQLAlchemy ORM, in order to map to a particular table, needs there to be at least one column denoted as a primary key column; multiple-column, i.e. composite, primary keys are of course entirely feasible as well.

How do I create a composite primary key in PostgreSQL?

Composite Primary Key's Syntax in PostgreSQL We use the “PRIMARY KEY” keyword with a bracket in which we write the columns' names separated with commas to specify them as a composite primary key.


1 Answers

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) 
like image 113
Ali Cirik Avatar answered Sep 29 '22 00:09

Ali Cirik