3

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)
Tom
  • 287
  • 2
  • 5
  • 10
  • 1
    See this question: http://stackoverflow.com/questions/1947750/does-python-support-mysql-prepared-statements – ean5533 Jan 21 '12 at 19:37
  • Are you correctly passing in the `timestamp` variable? http://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python – JSuar Jan 21 '12 at 19:37

2 Answers2

2

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,))
voithos
  • 68,482
  • 12
  • 101
  • 116
1

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.

cosmix
  • 185
  • 6