Inserting a Python datetime.datetime object into MySQL

For a time field, use:

import time    
time.strftime('%Y-%m-%d %H:%M:%S')

I think strftime also applies to datetime.

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

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'.

What database are you connecting to? I know Oracle can be picky about date formats and likes ISO 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(<string>, '<format>')

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 Server it is CONVERT.

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

    dt= datetime.now()

    query = """INSERT INTO table1(python_Date_col)
               VALUES (%s)
            """
    conn = ...... # Connection creating process
    cur = conn.cursor()
    cur.execute(query,(dt))

Above code will fail as "datetime.now()" produces "datetime.datetime(2014, 2, 11, 1, 16)" as a parameter value to insert statement.

Use the following method to capture the datetime which gives string value.

    dt= datetime.now().strftime("%Y%m%d%H%M%S")

I was able to successfully run the code after the change...

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)