Compile query from raw string (without using .text(...)) using Sqlalchemy connection and Postgres

Question

I am using Sqlalchemy 1.3 to connect to a PostgreSQL 9.6 database (through Psycopg).

I have a very, very raw Sql string formatted using Psycopg2 syntax which I can not modify because of some legacy issues:

statement_str = SELECT * FROM users WHERE user_id=%(user_id)s

Notice the %(user_id)s

I can happily execute that using a sqlalchemy connection just by doing:

connection = sqlalch_engine.connect()
rows = conn.execute(statement_str, user_id=self.user_id)

And it works fine. I get my user and all is nice and good.

Now, for debugging purposes I'd like to get the actual query with the %(user_id)s argument expanded to the actual value. For instance: If user_id = "foo", then get SELECT * FROM users WHERE user_id = 'foo'

I've seen tons of examples using sqlalchemy.text(...) to produce a statement and then get a compiled version. I have that thanks to other answers like this one or this one been able to produce a decent str when I have an SqlAlchemy query.

However, in this particular case, since I'm using a more cursor-specific syntax %(user_id) I can't do that. If I try:

text(statement_str).bindparams(user_id="foo")

I get: This text() construct doesn't define a bound parameter named 'user_id'

So I guess what I'm looking for would be something like

conn.compile(statement_str, user_id=self.user_id)

But I haven't been able to get that.

Savir · Accepted Answer · 2019-05-24 15:32:35Z

3

Ok I think I got it.

The combination of SqlAlchemy's raw_connection + Psycopg's mogrify seems to be the answer.

conn = sqlalch_engine.raw_connection()
try:
    cursor = conn.cursor()
    s_str = cursor.mogrify(statement_str, {'user_id': self.user_id})
    s_str = s_str.decode("utf-8")  # mogrify returns bytes

    # Some cleanup for niceness:
    s_str = s_str.replace('\n', ' ')
    s_str = re.sub(r'\s{2,}', ' ', s_str)
finally:
    conn.close()

I hope someone else finds this helpful

edited May 24, 2019 at 15:32

answered May 23, 2019 at 16:19

Savir

18.6k18 gold badges88 silver badges146 bronze badges

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

Comments

Buckeye14Guy · Accepted Answer · 2019-05-23 16:20:06Z

1

Not sure if this what you want but here goes.

Assuming statement_str is actually a string:

import sqlalchemy as sa

statement_str = "SELECT * FROM users WHERE user_id=%(user_id)s"
params = {'user_id': 'foo'}

query_text = sa.text(statement_str % params)

# str(query_text) should print "select * from users where user_id=foo"

answered May 23, 2019 at 16:20

Buckeye14Guy

8066 silver badges12 bronze badges

2 Comments

Savir Over a year ago

Thanks for your answer. The issue with that was that text(...) attempts to escape %(user_id)s to something like %%(user_id)

Buckeye14Guy Over a year ago

oh ok. Happy you got a solution.

Collectives™ on Stack Overflow

Compile query from raw string (without using .text(...)) using Sqlalchemy connection and Postgres

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related