raw SQL to sqlalchemy syntax

Question

I am using sqlaclhemy to query my database in my python project, I am fairly new to sqlalchemy, but like the concept at the moment, I am doing quite simple things like,

sel = select([staff.c.name]) \
    .select_from(staff) \
    .where(staff.c.workbase != "") \
    .where((staff.c.status != 'Left') & (staff.c.status != 'Name Changed'))

Part of my project requires a more complex sql query, but I want to keep it in sqlalchemy, my raw sql looks like this,

    SELECT A.a_allowance, B.b_allowance, C.c_allowance, A.name, A.leave_allowance
FROM
    (SELECT ROUND(leave_allowance * 0.32, 2) as a_allowance, name, leave_allowance FROM staff_list) A
    INNER JOIN
    (SELECT ROUND(leave_allowance * 0.40, 2) as b_allowance, name FROM staff_list) B
    ON A.name = B.name
    INNER JOIN
    (SELECT ROUND(leave_allowance * 0.28, 2) as c_allowance, name FROM staff_list) C
    ON A.name = C.name
    WHERE A.name = 'Jones Jones';

I not sure how to do the nested selects etc in sqlalchemy.

univerio · Accepted Answer · 2016-12-05 22:04:46Z

1

You can produce a join by using .join():

>>> print(foo.join(bar, foo.c.bar_id == bar.c.id))
foo JOIN bar ON foo.bar_id = bar.id

You can replace the table names in the expression by subqueries:

>>> left = select([foo.c.bar_id]).select_from(foo).where(foo.c.baz > 0).alias("left")
>>> print(left.join(bar, left.c.bar_id == bar.c.id))
(SELECT foo.bar_id AS bar_id 
FROM foo 
WHERE foo.baz > :baz_1) AS "left" JOIN bar ON "left".bar_id = bar.id

For the most part, subqueries act exactly like tables.

answered Dec 5, 2016 at 22:04

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

raw SQL to sqlalchemy syntax

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related