drootnar drootnar - 3 months ago 20
Python Question

join values in sqlalchemy

I would like to run following postgreSQL query in SQLalchemy:

select c.*
from comments c
join (
values
(1,1),
(3,2),
(2,3),
(4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering


Is it possible to join something like list of lists or list of tuples and use them to provide ordering in SQLalchemy?

Answer
from sqlalchemy import *

from yourdbmodule import dbsession


VALUES = ((1, 1), (3, 2), (2, 3), (4, 4))


temp_table = Table(
    "temp_table", MetaData(),
    Column("id", INT, primary_key=True),
    Column("ordering", INT),
    prefixes=['TEMPORARY']
)
temp_table.create(bind=dbsession.bind)


dbsession.execute(temp_table.insert().values(VALUES))


# Now you can query it
dbsession.query(Comments)\
    .join(temp_table, Comments.id == temp_table.c.id)\
    .order_by(temp_table.c.ordering)\
    .all()
Comments