CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2python-db-api
Tim Wilder
Tim Wilder

Reputation: 1647

Psycopg2 production appropriate mogrify?

I want to do exactly what cursor.mogrify does, but in a production appropriate way.

I'm updating some legacy Python code that builds queries by concatenating strings. I need to change this to escape safely.

The queries are long and built on a different server than they are run, so the normal process of using cursor.execute to escape is unattractive for both code clarity and practical viability reasons.

I would use mogrify, but I understand that it is intended for debugging purposes only.

I've looked around and can't seem to find a good answer to this. What do you suggest?

Upvotes: 1

Views: 2423

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

Don't use a tuple. Use a dictionary

d = {'p1': val1, 'p2': val2}
cur.execute("""
    select *
    from t
    where col1 = %(p1)s and col2 = %(p2)s
    """, d
)

If there are optional parameters pass then as null

d = {'p1': None, 'p2': val2}
cur.execute("""
    select *
    from t
    where
        (%(p1)s is null or col1 = %(p1)s)
        and
        (%(p2)s is null or col2 = %(p2)s)
    """, d
)

Establish an ssh connection to the server and connect through it.

ssh -L 5432:localhost:5432 remotehost.com

Upvotes: 3

Related Questions