Last active
September 11, 2022 16:57
-
-
Save ckkz-it/f0fdea78da3dcc596d2c9d38a4b9e938 to your computer and use it in GitHub Desktop.
SQL Alchemy Core multiple joins
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import datetime | |
import enum | |
from uuid import uuid4 | |
import sqlalchemy as sa | |
from sqlalchemy.dialects.postgresql import UUID | |
user_draw_source_relationship = sa.Table( | |
'users_draw_sources', meta, | |
sa.Column('user_id', UUID(as_uuid=True), sa.ForeignKey('users.id'), primary_key=True), | |
sa.Column('draw_source_id', UUID(as_uuid=True), sa.ForeignKey('draw_sources.id'), primary_key=True), | |
) | |
user = sa.Table( | |
'users', meta, | |
sa.Column('id', UUID(as_uuid=True), primary_key=True, default=uuid4), | |
sa.Column('name', sa.Text, nullable=False, default=''), | |
sa.Column('email', sa.Text, nullable=False, unique=True, default=''), | |
sa.Column('phone', sa.Text, nullable=False, default=''), | |
sa.Column('password', sa.Text, nullable=False), | |
sa.Column('created_at', sa.DateTime, nullable=False, default=datetime.datetime.utcnow), | |
) | |
company = sa.Table( | |
'companies', meta, | |
sa.Column('id', UUID(as_uuid=True), primary_key=True, default=uuid4), | |
sa.Column('name', sa.Text, nullable=False), | |
) | |
class DrawSourceType(enum.Enum): | |
marker = 'marker', | |
paints = 'paints', | |
draw_source = sa.Table( | |
'draw_sources', meta, | |
sa.Column('id', UUID(as_uuid=True), primary_key=True, default=uuid4), | |
sa.Column('type', sa.Enum(DrawSourceType), nullable=False), | |
sa.Column('name', sa.Text, nullable=False), | |
sa.Column('color', sa.Text, nullable=False), | |
sa.Column('code', sa.Text, nullable=False), | |
sa.Column('color_category', sa.Text, nullable=False, default=''), | |
sa.Column('company_id', sa.ForeignKey('companies.id'), nullable=True), | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from sqlalchemy import select | |
import db | |
query = select([db.draw_source, db.company], use_labels=True) \ | |
.select_from(db.draw_source.join(company).join(db.user_draw_source_relationship)) \ | |
.where(db.user_draw_source_relationship.c.user_id == '1') | |
# Will result in query | |
SELECT draw_sources.id AS draw_sources_id, | |
draw_sources.type AS draw_sources_type, | |
draw_sources.name AS draw_sources_name, | |
draw_sources.color AS draw_sources_color, | |
draw_sources.code AS draw_sources_code, | |
draw_sources.color_category AS draw_sources_color_category, | |
draw_sources.company_id AS draw_sources_company_id, | |
companies.id AS companies_id, | |
companies.name AS companies_name | |
FROM draw_sources | |
JOIN companies ON companies.id = draw_sources.company_id | |
JOIN users_draw_sources ON draw_sources.id = users_draw_sources.draw_source_id | |
WHERE users_draw_sources.user_id = :user_id_1 |
What is this
db
you are importing?
it's the first file in the gist
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
What is this
db
you are importing?