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 (http://initd.org/psycopg/docs/sql.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
])
Thread and process safetyThe Psycopg module and the connection objects are thread-safe: many threads can access the same database either using separate sessions and creating a connection per thread or using the same connection and creating separate cursors. In DB API 2.0 parlance, Psycopg is level 2 thread safe.
commit() Commit any pending transaction to the database. By default, Psycopg opens a transaction before executing the first command: if commit() is not called, the effect of any data manipulation will be lost.
Psycopg allows asynchronous interaction with other database sessions using the facilities offered by PostgreSQL commands LISTEN and NOTIFY.
psycopg2 is driver to run query for PostgreSQL from python. SQLAlchemy is the ORM which is not same as database driver.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With