1

I am reading this SO post and also doing some searching.

I'm using psycopg2 to execute strings as sql queries:

import credentials as creds
import psycopg2
start_date = '2020-01-01'
end_date = '2020-01-02'
another_date = '2019-12-31'


my_sql_query = """

with 

t1 as (
select * 
from table
where date >= %s), # should be start_date

t2 as (
select *
from anothertable
where created_date >= %s # should be start_date
and created_date <= %s # should be end_date
)

t3 as (
select *
from anotheranothertable
where date >= %s # should be another_date
)

select t1.blah, t2.ha, t3.*
from t1
join t2 on t2.id = t1.id
join t3 on t3.id = t1.id

"""

Then

cursor.execute(my_sql_query, (start_date, end_date, another_date))

Here there are 4 %s instances. I actually have 3 variables for these:

start_date
end_date
another_date

The actual query I am using is longer and makes multiple references to these 3 date variables throughout.

Is there a simpler way to pass these so that they can be referenced perhaps by variable name or another method, rather than order?

Based on the example above, if I repeat a reference to a variable I would have to call cursor.execute with the variables in order that they appear, including repetitions:

cursor.execute(my_sql_query, (start_date, start_date, end_date, another_date))

But in my real script it would become much longer e.g.

cursor.execute(my_sql_query, (start_date, start_date, end_date, another_date, start_date, end_date, end_date, another_date, start_date))

What's the 'right' way to approach this?

Anmol Singh Jaggi
  • 8,376
  • 4
  • 36
  • 77
Doug Fir
  • 19,971
  • 47
  • 169
  • 299
  • 1
    did you check documentation for `execute()` ? Some functions can use string with `%(name)s` but I don't know if `execute()` also can use it. – furas Jul 11 '20 at 16:13
  • 1
    You can use `.format` or `f-strings` instead of `%` method, it allows for variable/index reference. – Rahul Bharadwaj Jul 11 '20 at 16:15

2 Answers2

3

You can just populate the variables yourself first and then invoke cursor.execute().

start_date = '2020-01-01'
end_date = '2020-01-02'
another_date = '2019-12-31'

date_map = {}
date_map['start_date'] = start_date
date_map['end_date'] = end_date
date_map['another_date'] = another_date

my_sql_query = """

with 

t1 as (
select * 
from table
where date >= {start_date}), # should be start_date

t2 as (
select *
from anothertable
where created_date >= {start_date} # should be start_date
and created_date <= {end_date} # should be end_date
)

t3 as (
select *
from anotheranothertable
where date >= {another_date} # should be another_date
)

select t1.blah, t2.ha, t3.*
from t1
join t2 on t2.id = t1.id
join t3 on t3.id = t1.id

"""

my_sql_query = my_sql_query.format(**date_map)
#cursor.execute(my_sql_query)
print(my_sql_query)
Anmol Singh Jaggi
  • 8,376
  • 4
  • 36
  • 77
1

You can use this to repeat it in the SQL and have the database API deal with the substitution in a safer manner:

Parameterized queries with psycopg2 / Python DB-API and PostgreSQL

Stephen
  • 11
  • 2