Error with SQL string: "Error while connecting to PostgreSQL operator does not exist: date = integer"

Question

I have a Python(3) script that is supposed to run each morning. In it, I call some SQL. However I'm getting an error message:

Error while connecting to PostgreSQL operator does not exist: date = integer

The SQL is based on the concatenation of a string:

ecom_dashboard_query = """
with 
days_data as (
select 
    s.date,
    s.user_type,
    s.channel_grouping,
    s.device_category,
    sum(s.sessions) as sessions,
    count(distinct s.dimension2) as daily_users,
    sum(s.transactions) as transactions,
    sum(s.transaction_revenue) as revenue
from ga_flagship_ecom.sessions s
where date = """ + run.start_date + """
group by 1,2,3,4
)

insert into flagship_reporting.ecom_dashboard
select *
from days_data;
"""

Here is the full error:

09:31:25  Error while connecting to PostgreSQL  operator does not exist: date = integer
09:31:25  LINE 14: where date = 2020-01-19
09:31:25                      ^
09:31:25  HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

I tried wrapping run.start_date within str like so: str(run.start_date) but I received the same error message.

I suspect it may be to do with the way I concatenate the SQL query string, but I am not sure.

The query runs fine in SQL directly with a hard coded date and no concatenation:

where date = '2020-01-19'

How can I get the query string to work correctly?

Don't use string concatenation, that's what caused the problem. No amount of quoting or escaping can fix the fundamental problems. Use parameters instead — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 22, 2020 at 14:49

neverwalkaloner · Accepted Answer · 2020-01-22 14:45:11Z

2

It's more better to pass query params to cursor.execute method. From docs

Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

So instead of string concatenation pass run.start_date as second argument of cursor.execute.

In your query instead of concatenation use %s:

where date = %s
group by 1,2,3,4

In your python code add second argument to execute method:

cur.execute(ecom_dashboard_query , (run.start_date,))

answered Jan 22, 2020 at 14:45

neverwalkaloner

47.6k7 gold badges101 silver badges103 bronze badges

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

2 Comments

Doug Fir Over a year ago

Thank you for telling me this! Just a follow up. You have a comma after run.start_date... is that a typo? Also, why is run.start_date in parenthesis? Would it be OK to also do cur.execute(ecom_dashboard_query , run.start_date)? Just curious

neverwalkaloner Over a year ago

@DougFir no, it not a typo, execute expecting sequence or mapping so if we need to provide one paramether we need to convert it to sequence(tuple) that's what this comma doing. You can read about this method here: initd.org/psycopg/docs/cursor.html#cursor.execute

Pablo · Accepted Answer · 2020-01-22 15:00:35Z

1

Your sentece is wrong:

where date = """ + run.start_date + """

try to compare a date and a string and this is not posible, you need to convert "run.start_date" to datetime and compare simply:

date_format = datetime.strptime(your_date_string, '%y-%m-%d')

and with this date converted to datetime do:

where date = date_format

Final code:

date_format = datetime.strptime(your_date_string, '%y-%m-%d')
ecom_dashboard_query = """
with 
days_data as (
select 
    s.date,
    s.user_type,
    s.channel_grouping,
    s.device_category,
    sum(s.sessions) as sessions,
    count(distinct s.dimension2) as daily_users,
    sum(s.transactions) as transactions,
    sum(s.transaction_revenue) as revenue
from ga_flagship_ecom.sessions s
where date = {}
group by 1,2,3,4
)

insert into flagship_reporting.ecom_dashboard
select *
from days_data;
""".format(date_format)

answered Jan 22, 2020 at 15:00

Pablo

3741 silver badge10 bronze badges

3 Comments

Doug Fir Over a year ago

Thank you for solving my first issue with understanding how to compare string and dates. I think I'm leaning to neverwalkerloners solution though since there are some issues with concatenating SQL strings more generally I wasn't aware off

Pablo Over a year ago

"format" is the best way to set vars to a query, you can put a variable or whatever you want followed by ",". So, is your query running yet?

Doug Fir Over a year ago

It's part of a pipeline that will run in an hour... will find out soon!

Collectives™ on Stack Overflow

Error with SQL string: "Error while connecting to PostgreSQL operator does not exist: date = integer"

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related