Python insert datetime into Mysql DB

laugh · 17-08-2011 04:01PM #1

Hi I'm having some difficulty inserting a row in a table using python MySQLdb:

I've verified that my connection code and credentials work by printing the mysql version.

I am passing some arguments to this simple script to have them inserted in a DB.

#!/usr/bin/python -tt

import MySQLdb as mdb
import sys

value_id = sys.argv[1]
value = sys.argv[2]
date = sys.argv[3]
time = sys.argv[4] 
dt = "'" + date + " " + time + "'"


try:
  conn = mdb.connect('localhost', 'test', 'test', 'test');

  cursor = conn.cursor()
  print date + " " + time
  cursor.execute("INSERT INTO test (ID, VALUE_ID, VALUE, DATE) VALUES (NULL, value_id, value, dt) ")
  cursor.close()
  conn.close()

except mdb.Error, e:
  print "Error %d: %s" % (e.args[0],e.args[1])
  sys.exit(1)

I get the following when I run this:

$ ./insert_stat.py 101 2169.41 2011-08-17 15:16:00
Error 1054: Unknown column 'dt' in 'field list'

I've tried a multitude of different quotes around this and it still will not work.

At my mysql prompt the following works (ID the first column is an auto incrementing INT) :

INSERT INTO test VALUES (NULL, 101, '1786.85', '2011-08-17 12:10:00');

Any ideas?

dlofnep · 17-08-2011 04:03PM

Code is probably fine - it's telling you that it can't find a column called 'dt' in the table. Check the table and see if the date column is actually called dt.

laugh · 17-08-2011 04:09PM

dlofnep wrote: »

Code is probably fine - it's telling you that it can't find a column called 'dt' in the table. Check the table and see if the date column is actually called dt.

Hey thank you for the reply, the column is called DATE as specified by the INSERT statement (ID, VALUE_ID, VALUE, DATE), dt is a variable containing a datetime that I want to insert. When I do a print of dt just before the insert it prints out exactly the format of date I used at the mysql prompt.

+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| ID      | int(11)  | NO   | PRI | NULL    | auto_increment | 
| VALUE_ID | int(11)  | NO   |     |         |                | 
| VALUE   | float    | NO   |     |         |                | 
| DATE    | datetime | NO   |     |         |                | 
+---------+----------+------+-----+---------+----------------+

Procasinator · 17-08-2011 04:24PM

You are passing in a string "dt" rather than the actual value of dt.

Never used the MySQLdb, but it appears from the documentation that execute has an args argument. Or you could use Python's format strings/concatenation.


cursor.execute("INSERT INTO test (ID, VALUE_ID, VALUE, DATE) VALUES (NULL, value_id, value, %s)", dt) # Might require a tuple, which would be (dt, )

#Or on the string itself
"INSERT INTO test (ID, VALUE_ID, VALUE, DATE) VALUES (NULL, value_id, value, '%s')"%dt

dlofnep · 17-08-2011 06:14PM

Oops sorry

I read it wrong.

Python insert datetime into Mysql DB

Comments