Declaring Table Classes with Multi-Column Primary Keys in SQLAlchemy

1. Define the Table Class:

2. Declare Columns:

3. Set Up Composite Primary Key:

Here's an example:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
  __tablename__ = 'users'

  # Multi-column primary key
  user_id = Column(Integer, primary_key=True)
  username = Column(String(50), primary_key=True)
  email = Column(String(100))

engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine)

In this example, the User table has two columns, user_id and username, that together form the primary key. This means a combination of these two values must be unique to identify each user record.

• Natural Keys: Sometimes, a combination of columns naturally defines a unique entity in your data. Composite keys reflect this real-world relationship.
• Flexibility: They offer more control over how rows are uniquely identified compared to a single column primary key.

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import PrimaryKeyConstraint

Base = declarative_base()

class Order(Base):
  __tablename__ = 'orders'

  order_id = Column(Integer)
  product_id = Column(Integer)
  quantity = Column(Integer)

  # Define composite primary key using __table_args__
  __table_args__ = (
      PrimaryKeyConstraint('order_id', 'product_id'),
  )

engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine)

This option defines the columns and then uses the __table_args__ attribute within the class to specify the primary key constraint. Here, PrimaryKeyConstraint is used with a tuple containing the column names.

Option 2: Using positional arguments

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class ProductReview(Base):
  __tablename__ = 'product_reviews'

  user_id = Column(Integer, primary_key=True)
  product_id = Column(Integer, primary_key=True)
  rating = Column(Integer)

  # Define composite primary key with positional arguments
  __mapper_args__ = {
      'primary_key': [user_id, product_id]
  }

engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine)

This option leverages the primary_key argument within the __mapper_args__ dictionary. Here, it's a list containing the column objects that form the composite key.

1. Using PrimaryKeyConstraint class directly:

This approach utilizes the PrimaryKeyConstraint class from sqlalchemy.schema to explicitly define the constraint.

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import PrimaryKeyConstraint

Base = declarative_base()

class Customer(Base):
  __tablename__ = 'customers'

  customer_id = Column(Integer)
  email = Column(String(100))
  phone_number = Column(String(20))

  # Define composite primary key using PrimaryKeyConstraint
  __table_args__ = (
      PrimaryKeyConstraint(customer_id, email),
  )

engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine)

Here, we create a PrimaryKeyConstraint instance with the desired column names within the __table_args__ attribute.

1. Using Table construct with declarative:

While the previous examples used a class to define the table, SQLAlchemy also offers the Table construct from sqlalchemy.schema. This approach separates the table definition from the model class.

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_Base
from sqlalchemy.schema import Table, PrimaryKeyConstraint

Base = declarative_base()

# Define the table structure
metadata = Base.metadata
users_table = Table(
  'users',
  metadata,
  Column('user_id', Integer, primary_key=True),
  Column('username', String(50), primary_key=True),
  Column('email', String(100)),
)

# Create a model class referencing the table
class User(object):
  __table__ = users_table

engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine)

In this variation, we define the table structure using Table with the column definitions and primary key settings. Then, a separate class User inherits from object and references the users_table using the __table__ attribute.