Inserting a python datetime.datetime object into mysql

Question

I have a date column in a mysql table. I want to insert a datetime.datetime() object into this column. What should i be using in the execute statement? I have tried:

now = datetime.datetime(2009,5,5)

cursor.execute("INSERT INTO table
(name, id, datecolumn) VALUES (%s, %s
, %s)",("name", 4,now))

I am getting an error as: "TypeError: not all arguments converted during string formatting" What should i use instead of %s?

Answer 1

Try using now.date() to get a Date object rather than a DateTime.

If that doesn't work, then converting that to a string should (MySQL isn't my area of expertise - I prefer PostgreSQL).

now = datetime.datetime(2009,5,5)
str_now = now.date().isoformat()
cursor.execute('INSERT INTO table (name, id, datecolumn) VALUES (%s,%s,%s)', ('name',4,str_now))

Answer 2

Well, you have a comma between the string and parameters. That should reasonably be %. (or is there a reason for passing the parameters to the cursor instead of just substituting them directly?)

Answer 3

What database are you connecting to? I know Oracle can be picky about date formats and likes ISO format.

** note oops just read you are on Mysql, just format the date and try it as a separate direct sql call to test.

In python, you can get an ISO date like

now.isoformat()

For instance oracle likes dates like

insert into x values(99, '31-may-09');

Depending on your database, if it is oracle you might need to TO_DATE it;

insert into x
values(99, to_date('2009/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));

The general usage of TO_DATE is:

TO_DATE(<string>, '<format>')

If using other database (I saw the cursor and thought oracle I could be wrong) then check their date format tools. For Mysql it is DATE_FORMAT() and MSSQL it is CONVERT.

Also using a tool like SQlAlchemy will remove differences like these and make your life easy.