hselbie hselbie - 7 months ago 7
SQL Question

SQL formatting examples for long queries in python for psycopg2 connections

Typically I use Pandas to look at and play with data, it's what I'm familiar with.

If I'm pulling data from a postgres database using the psycopg2 module I'm putting the SQL queries in a python file, but if it's a complicated query it extends on one line well off the screen.

Is there a way to get a sql query with newlines in python? i.e. some kind of special character wrapper that makes python ignore the spaces?

this

sql = SELECT devices.id, devices.names, devices.addresses, devices.charging, devices.covered, record_temperatures.time
FROM devices
WHERE devices.imei like '%0444258';

df = pd.read_sql_query(sql, con=conn)


Not this

sql = SELECT devices.id, devices.names, devices.addresses, devices.charging, devices.covered, record_temperatures.time FROM devices WHERE devices.imei like '%0444258';

df = pd.read_sql_query(sql, con=conn)

Answer

you can use backslash as below :

sql = "SELECT devices.id, devices.names, devices.addresses,\
devices.charging, devices.covered, record_temperatures.time \
FROM devices \
WHERE devices.imei like '%0444258'";

or you can also use triple quote :

sql = """SELECT devices.id, devices.names, devices.addresses,  
devices.charging, devices.covered, record_temperatures.time
FROM devices
WHERE devices.imei like '%0444258';
"""