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

User · Answer

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 work   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

User · Answer

If you re just using a python datetime date  not a full datetime datetime   just cast the date as a string  This is very simple and works for me  mysql  python 2 7  Ubuntu   The column published date is a MySQL date field  the python variable publish date is datetime date       make the record for the passed link info sql stmt    INSERT INTO snippet links             link headline  link url  published date  author  source  coco id  link id            VALUES  s   s   s   s   s   s   s      sql data     title  link  str publish date                  author  posted by                 str coco id   str link id     try      dbc execute sql stmt  sql data   except Exception  e

User · Answer

For a time field  use   import time     time strftime   Y- m- d  H  M  S     I think strftime also applies to datetime

User · Answer

What database are you connecting to  I know Oracle can be picky about date formats and likes ISO nbsp 8601 format     Note  Oops  I 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  lt string gt     lt format gt      If using another 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 SQL nbsp Server it is CONVERT   Also using a tool like SQLAlchemy will remove differences like these and make your life easy

User · Answer

when iserting into t-sql  this fails   select CONVERT datetime  2019-09-13 09 04 35 823312  21    this works   select CONVERT datetime  2019-09-13 09 04 35 823  21    easy way   regexp   re compile r     d 6     def to splunk iso dt          Converts the datetime object to Splunk isoformat string           6-digits string      microseconds   regexp search dt  group 1      return regexp sub    d    round float microseconds    1000   dt

User · Answer

Use Python method datetime strftime format   where format     Y- m- d  H  M  S    import datetime  now   datetime datetime utcnow    cursor execute  INSERT INTO table  name  id  datecolumn  VALUES   s   s   s                     name   4  now strftime   Y- m- d  H  M  S       Timezones  If timezones are a concern  the MySQL timezone can be set for UTC as follows   cursor execute  SET time zone     00 00      And the timezone can be set in Python   now   datetime datetime utcnow   replace tzinfo datetime timezone utc      MySQL Documentation     MySQL recognizes DATETIME and TIMESTAMP values in these formats       As a string in either  YYYY-MM-DD HH MM SS  or  YY-MM-DD HH MM SS    format  A    relaxed    syntax is permitted here  too  Any punctuation   character may be used as the delimiter between date parts or time   parts  For example   2012-12-31 11 30 45    2012 12 31 11 30 45      2012 12 31 11 30 45   and  2012 12 31 11 30 45  are equivalent       The only delimiter recognized between a date and time part and a   fractional seconds part is the decimal point       The date and time parts can be separated by T rather than a space  For   example   2012-12-31 11 30 45   2012-12-31T11 30 45  are equivalent       As a string with no delimiters in either  YYYYMMDDHHMMSS  or    YYMMDDHHMMSS  format  provided that the string makes sense as a date    For example   20070523091528  and  070523091528  are interpreted as    2007-05-23 09 15 28   but  071122129015  is illegal  it has a   nonsensical minute part  and becomes  0000-00-00 00 00 00        As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format  provided   that the number makes sense as a date  For example  19830905132800 and   830905132800 are interpreted as  1983-09-05 13 28 00

User · Answer

You are most likely getting the TypeError because you need quotes around the datecolumn value   Try   now   datetime datetime 2009  5  5   cursor execute  INSERT INTO table  name  id  datecolumn  VALUES   s   s    s                      name   4  now     With regards to the format  I had success with the above command  which includes the milliseconds  and with   now strftime   Y- m- d  H  M  S     Hope this helps

[python] Inserting a Python datetime.datetime object into MySQL

Examples related to python

Examples related to mysql

Examples related to datetime