Passing a datetime into psycopg2
datetime, postgresql, psycopg2, python
Solution
Have you read "Psycopg2 usage"?
If not, don't hesitate to do so.
Warning NEVER, NEVER, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.
Proper way of passing params into a query is shown below:
SQL = "INSERT INTO authors (name) VALUES (%s);" # Note: no quotes
data = ("O'Reilly", )
cur.execute(SQL, data) # Note: no % operator
Problem
I am trying to insert a datetime value into my postgres database using Psycopg2. My code was working before, but I switched from %s notation to {} notation, and my code broke. here is my code ``` for j in eveLists.itemList: tempPrice = fetchSellPrice(i, j) database_name = eveLists.DatabaseDict[i] now = datetime.datetime.utcnow() cur.execute("INSERT INTO {0} VALUES ({1}, {2}, {3}, NULL, {4}, {5}, NULL);".format( database_name, str(i), str(j), float(tempPrice), datetime.date.today(), now)) ``` I get the following error: ``` psycopg2.ProgrammingError: syntax error at or near "00" LINE 1: ...0000142, 2268, 3.11, NULL, 2017-05-09, 2017-05-10 00:40:03.3... ^ ``` It is treating the date and the time as two separate objects. I have tried several different ways which are commented out, and all throw various error messages. I have tried wrapping the datetime in quotes, ``` now = ("'%s'") % str(datetime.datetime.utcnow()) ``` which give an error ``` psycopg2.ProgrammingError: column "mydate" is of type date but expression is of type integer LINE 1: ...NTO temp_jita VALUES (30000142, 2268, 3.11, NULL, 2017-05-09... ^ HINT: You will need to rewrite or cast the expression. ``` It thinks my date is an integer, even though I wrapped it in quotes. I have also tried making a psycopg2 timestamp manually: ``` now = psycopg2.Timestamp(now.strftime("%Y"), now.strftime("%m"), now.strftime("%d"), now.strftime("%h"), now.strftime("%M"), int(now.strftime("%-S"))) ``` Which gives the following error: ``` int(now.strftime("%-S"))) TypeError: an integer is required (got type str) ``` I don't know how it thinks this is a string, especially since I cast it as an int! Any help would be appreciated. EDIT: The reason I passed the variables using {} notation and not %s notation is because I received an error for the following code: ``` for j in eveLists.itemList: tempPrice = fetchSellPrice(i, j) database_name = eveLists.DatabaseDict[i] now = datetime.datetime.utcnow() now = str(now) cur.execute("INSERT INTO %s VALUES (%s, %s, %s, NULL, %s, %s, NULL);", [database_name, str(i), str(j), float(tempPrice), datetime.date.today(), now ]) psycopg2.ProgrammingError: syntax error at or near "'temp_jita'" LINE 1: INSERT INTO 'temp_jita' VALUES ('30000142', '2268', 3.03, NU... ^ ``` Please refer to my previous post on the subject: How to remove the quotes from a string for SQL query in Python? EDIT: following @Zorg 's advice from this link (https://www.psycopg.org/docs/usage.html), this code worked for me: ``` cur.execute(sql.SQL("INSERT INTO {} VALUES (%s, %s, %s, NULL, %s, %s, NULL);").format(sql.Identifier(database_name)),[ str(i), str(j), float(tempPrice), datetime.date.today(), now ]) ```