CODEWITHSUNDEEP
sqlalchemy
Cody
Cody

Reputation: 2649

How to define one-to-one and many-to-many relationship using SQLAlchemy Core

I have searched the documentation but didn't find anything useful. Please guide me in the right direction.

Upvotes: 1

Views: 2925

Answers (1)

Nader Alexan
Nader Alexan

Reputation: 2222

One-to-one

Use one table's id as a foreign and primary key in the other.

Example from overiq

employees = Table('employees', metadata,
    Column('employee_id', Integer(), primary_key=True),
    Column('first_name', String(200), nullable=False),
    Column('last_name', String(200), nullable=False),
    Column('dob', DateTime(), nullable=False),
    Column('designation', String(200), nullable=False),
)

employee_details = Table('employee_details', metadata,
    Column('employee_id', ForeignKey('employees.employee_id'), primary_key=True, ),
    Column('ssn', String(200), nullable=False),
    Column('salary', String(200), nullable=False),
    Column('blood_group', String(200), nullable=False),
    Column('residential_address', String(200), nullable=False),    
)

Many-to-one

From SQL Alchemy's official docs:

In SQLAlchemy as well as in DDL, foreign key constraints can be defined as additional attributes within the table clause, or for single-column foreign keys they may optionally be specified within the definition of a single column. The single column foreign key is more common, and at the column level is specified by constructing a ForeignKey object as an argument to a Column object:

user_preference = Table('user_preference', metadata,
    Column('pref_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
    Column('pref_name', String(40), nullable=False),
    Column('pref_value', String(100))
)

Many-to-many

Create a third table with two foreign keys referencing the tables you want to create a relationship between

Example from overiq

posts = Table('posts', metadata,
    Column('id', Integer(), primary_key=True),
    Column('post_title', String(200), nullable=False),
    Column('post_slug', String(200),  nullable=False),
    Column('content', Text(),  nullable=False),    
)

tags = Table('tags', metadata,
    Column('id', Integer(), primary_key=True),
    Column('tag', String(200), nullable=False),
    Column('tag_slug', String(200),  nullable=False),    
)

post_tags = Table('post_tags', metadata,
    Column('post_id', ForeignKey('posts.id')),
    Column('tag_id', ForeignKey('tags.id'))
)

Upvotes: 5

Related Questions