Introduction

Inserting a Python datetime.datetime object into MySQL is usually straightforward if you use a proper database driver and parameterized SQL. The driver can convert the Python object into the SQL representation MySQL expects. The real decisions are about column type, timezone handling, and avoiding manual string formatting that creates subtle bugs.

Use Parameterized Queries and Let the Driver Convert the Value

The safest approach is to pass the datetime object as a query parameter rather than formatting it into the SQL string yourself.

With mysql-connector-python, a basic example looks like this:

1from datetime import datetime
2import mysql.connector
3
4conn = mysql.connector.connect(
5    host="localhost",
6    user="appuser",
7    password="secret",
8    database="demo"
9)
10
11cursor = conn.cursor()
12created_at = datetime.now()
13
14cursor.execute(
15    "INSERT INTO events (name, created_at) VALUES (%s, %s)",
16    ("signup", created_at)
17)
18
19conn.commit()
20cursor.close()
21conn.close()

The important part is the %s placeholders plus the tuple of values. The connector handles the conversion from Python's datetime object to a MySQL-compatible datetime literal.

Choose Between DATETIME and TIMESTAMP

In MySQL, the usual storage choices are DATETIME and TIMESTAMP.

A simple table might be:

1CREATE TABLE events (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(100) NOT NULL,
4    created_at DATETIME NOT NULL
5);

The practical difference is:

• 'DATETIME stores a calendar value as given'
• 'TIMESTAMP is more tied to timezone conversion behavior inside MySQL'

If your application works across time zones, decide explicitly whether you want MySQL to perform timezone-aware conversions or whether you want to store normalized UTC values yourself.

Prefer UTC for Cross-Time-Zone Systems

Naive local timestamps become painful once services, jobs, or users span time zones. A common pattern is to generate timezone-aware UTC datetimes in Python and store them consistently.

1from datetime import datetime, timezone
2
3created_at = datetime.now(timezone.utc)
4print(created_at)

If your driver and schema are configured sensibly, this makes the meaning of stored values much easier to preserve across systems.

The more distributed the system becomes, the more valuable this discipline becomes.

Avoid Manual String Formatting

A common anti-pattern is building SQL like this:

sql = f"INSERT INTO events (created_at) VALUES ('{created_at}')"

That is a bad habit for two reasons:

• it gives up the driver's type handling
• it encourages unsafe SQL construction in general

Even if the datetime string seems harmless, parameterized queries are the correct habit because they solve both formatting and SQL-injection concerns consistently.

Reading the Value Back Confirms the Round Trip

It is often useful to verify that the inserted value round-trips correctly.

1cursor = conn.cursor()
2cursor.execute("SELECT name, created_at FROM events ORDER BY id DESC LIMIT 1")
3row = cursor.fetchone()
4print(row)

If you are debugging timezone behavior, do this immediately after inserting a known timestamp so you can compare the Python-side value with the database-side result.

pymysql and Other Drivers Work Similarly

The exact connection API changes by library, but the rule stays the same: pass the datetime as a bound parameter.

A pymysql example is almost identical:

1from datetime import datetime
2import pymysql
3
4conn = pymysql.connect(host="localhost", user="appuser", password="secret", database="demo")
5
6with conn.cursor() as cursor:
7    cursor.execute(
8        "INSERT INTO events (name, created_at) VALUES (%s, %s)",
9        ("purchase", datetime.now())
10    )
11
12conn.commit()
13conn.close()

So the key idea is independent of any one driver: use parameters, not string interpolation.

Watch Naive Versus Aware Datetimes

Python distinguishes between naive datetimes, which have no timezone information, and aware datetimes, which do. MySQL does not magically fix ambiguous application logic for you.

If one part of the system writes local naive times and another part assumes UTC, the database will happily store both and the resulting confusion will look like random time shifts later.

That is why a consistent time policy is part of correct insertion logic, not an optional cleanup step.

Common Pitfalls

The most common mistake is formatting the datetime into the SQL string manually instead of passing it as a parameter. That throws away the driver's conversion logic.

Another mistake is mixing DATETIME, TIMESTAMP, local time, and UTC without deciding which semantics the application actually needs.

Developers also forget that Python naive datetimes do not carry timezone meaning, even though the printed value looks complete.

Summary

• Use parameterized queries and pass Python datetime objects directly to the MySQL driver.
• Let the connector handle conversion instead of formatting the SQL string manually.
• Choose DATETIME or TIMESTAMP deliberately based on your time-handling needs.
• Prefer UTC for systems that cross time zones.
• The hard part is usually timezone semantics, not the insertion syntax itself.