ERROR in executing SQL SELECT

Python Help
1

I’m still fairly new to Python so I can’t claim I know all the details. I’m using Python 3.11 on Windows 10.

I’m getting this error when I do a cursor.execute(sql,sqlparams) statement. As I’m trying to do proper SQL execution with variables in the SQL statement. The error is:

“psycopg2.errors.SyntaxError: syntax error at or near “)”
LINE 1: …field4 as empid FROM jobcost where (jcenddate >= ? ) LIMIT 10”

This is for Postgresql and I’m passing in a date string in the format ‘YYYY-MM-DD’ and in the SQL statement it must have single quotes around the date. Here is my Python code.

mysql = 'Select field1,field2,field3,field4 as empid FROM mytable where (field5 >= ? ) LIMIT 10' 
params = ('2024-03-14', ) # End with a comma to make a tuple.
cur.execute(sql,sqlparams) # cur is my cursor

The SQL should look like this: Select field1,field2,field3,field4 as empid FROM mytable where (field5 >= '2024-03-17' ) LIMIT 10 but I have no way of seeing the query with the date value in it to debug this.

Since I’m new to this surely I’m doing something wrong. The select worked if I did it insecurely using a different method.

What am I doing wrong here? Thank you.

EDIT: All strings in a Postgresql must be enclosed in single quotes like this: SELECT name,job WHERE name='Tom'. Dates are just represented as strings in Postgresql.

2

A quick look at the Psycopg2 Documentation shows how it should be done:

mysql = 'Select field1,field2,field3,field4 as empid FROM mytable where (field5 >= %s ) LIMIT 10' 
params = (datetime.date(2024, 03, 14), ) # End with a comma to make a tuple.
cur.execute(sql,sqlparams) # cur is my cursor

Two points:

  • we use %s as a placeholder (sorry for misleading You in another post, Postgresql seems to be a little bit different) and never surround it with quotes
  • we use datetime.date instead of raw string, the library does automatic conversion of the object to the right format

I hope I helped you :slight_smile:

3

As the traceback mentioned: “…SyntaxError”, it could probably occur at SQL statement, not at python driver level.

You can run your SQL statement above (replace ? with value) on PostgreSQL console to find the syntax error.

4

New code gets an error in "params = " line.

import datetime
        mysql = 'Select ccmasterid as job,ccjobpart,jcenddate,dcmasterid as empid FROM mytable where (jcenddate >= %s ) LIMIT 10' 
        params = (datetime.date('2024','3','14'), ) # End with a comma to make a tuple.
        cur.execute(mysql,sqlparams)

Error is: “-> params = (datetime.date(‘2024’,‘3’,‘14’), ) # End with a comma to make a tuple.
(Pdb) n
TypeError: descriptor ‘date’ for ‘datetime.datetime’ objects doesn’t apply to a ‘str’ object”

I tried making the year, month and day an int like in your example but I got the same error.

5

Oops sorry I was writing too fast :blush:

  • Values to datetime.date are passed as integers not strings
  • and in cur.execute you are writing sqlparams but a line above you store data in params variable

I feel unlucky today :frowning:

6

I got it working. Thank you!