What is the syntax to INSERT datetime variable INTO VALUES

Question

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)

Answer 1

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,))

Answer 2

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.