Using sqlalchemy with psycopg

Question

I am in need of combining the results of a SQLAlchemy query and a pyscopg query.

Currently I use psycopg to do most of my SQL selects in my code. This is done using a cursor and fetchall().

However, I have a separate microservice that returns some extra WHERE clauses I need for my statement, based on some variables. This is returned as a SQLAlchemy SELECT object. This is out of my control.

Example return:

select * from users where name = 'bar';

My current solution for this is to hardcode the results of the microservice (just the WHERE clauses) into an enum and add them into the pyscopg statement using an f-string. This is a temporary solution.

Simplified example:

user_name = "bar"

sql_enum = {
"foo": "name = 'foo'"
"bar": "name = 'bar'"
}

with conn.cursor() as cur:
    cur.execute(f"select * from users where location = 'FOOBAR' and {sql_enum[user_name]}")

I am looking for a way to better join these two statements. Any suggestions are greatly appreciated!

I'm a bit confused. You say that the "microservice … returns some extra WHERE clauses … as a SQLAlchemy SELECT object" but the example you give is a complete literal SQL statement. So does this microservice really return a `` object? If so, does that object have a `.whereclause` attribute? Please [edit] your question to provide more detail. — Gord Thompson, Nov 11 '22 at 13:44
@GordThompson apologies for the confusion. The microservice returns the full statement (`` object). It does have an attribute `.whereclause` which returns just the bit I want, so thanks for that! Now the question remains on how best to inject that where clause into the pyscopg statement — SoftwareThings, Nov 11 '22 at 15:17

score 0 · Answer 1 · answered Nov 11 '22 at 16:04

Rather than mess with dynamic SQL (f-strings, etc.), I would just start with a SQLAlchemy Core select() statement and then add the whereclause from the statement returned by the microservice:

import sqlalchemy as sa

engine = sa.create_engine("postgresql://scott:tiger@192.168.0.199/test")

users = sa.Table(
    "users", sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("name", sa.String(50)),
    sa.Column("location", sa.String(50))
)
users.drop(engine, checkfirst=True)
users.create(engine)

# mock return from microservice
from_ms = sa.select(sa.text("*")).select_from(users).where(users.c.name == "bar")

base_query = sa.select(users).where(users.c.location == "FOOBAR")

full_query = base_query.where(from_ms.whereclause)

engine.echo = True
with engine.begin() as conn:
    result = conn.execute(full_query)
    """SQL emitted:
    SELECT users.id, users.name, users.location 
    FROM users 
    WHERE users.location = %(location_1)s AND users.name = %(name_1)s
    [generated in 0.00077s] {'location_1': 'FOOBAR', 'name_1': 'bar'}
    """

Using sqlalchemy with psycopg

1 Answers1