Reputation: 287
What is the syntax for inserting a SQL datetime data type into a row? The following in Python gives a NULL value for the timestamp variable only.
timestamp = datetime.datetime.today()
print timestamp
query = "INSERT INTO table1 (name, class, time_update) VALUES('ONE','TWO',@timestamp)"
cursor.execute(query)
Upvotes: 3
Views: 5909
Reputation: 185
It really depends on the database. For MySQL, according to this, you can specify a timestamp/datetime in several formats, mostly based on ISO8601: 'YYYY-MM-DD', 'YY-MM-DD', 'YYYYMMDD' and 'YYMMDD' (without delimiters) or if you want greater precision 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' ('YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS').
As far as querying goes you should probably parameterise (it's safer and a very good habit) by specifying a placeholder in the query string. For MySQL you could do:
query = "INSERT INTO table1 (name, class, time_update) VALUES('ONE','TWO',%s)"
cursor.execute(query, (timestamp,))
but the syntax for placeholders varies (depending on the db interface/driver) — see the documentation for your DB/Python interface.
Upvotes: 1
Reputation: 70602
I'm sure it depends on which database backend you're using, but in SQLite for example, you need to send your parameter as part of the query (that's what a parameterized statement is all about):
timestamp = datetime.datetime.today()
print timestamp
query = "INSERT INTO table1 (name, class, time_update) VALUES('ONE','TWO',?)"
cursor.execute(query, (timestamp,))
Upvotes: 2