How to store and retrieve a date into MySQL database using Python?

To insert a date in a MySQL database, you need to have a column of Type DATE or DATETIME in your table. Once you have that, you'll need to convert your date to a string format before inserting it into your database. You can use the datetime module's strftime() formatting function for this purpose.

Setting up the Database Connection

First, let's establish a connection to MySQL and create a sample table ?

import mysql.connector
from datetime import datetime

# Create connection (adjust credentials as needed)
connection = mysql.connector.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)

cursor = connection.cursor()

# Create a sample table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS events (
        id INT PRIMARY KEY,
        event_name VARCHAR(100),
        date_created DATETIME
    )
''')

print("Table created successfully")

Table created successfully

Storing a Date in MySQL

Convert the current datetime to a formatted string and insert it into the database ?

from datetime import datetime
import mysql.connector

# Get current datetime
now = datetime.now()
event_id = 1
event_name = "Python Workshop"

# Format datetime as string
formatted_date = now.strftime('%Y-%m-%d %H:%M:%S')
print(f"Formatted date: {formatted_date}")

# Insert into database
connection = mysql.connector.connect(
    host='localhost',
    user='your_username', 
    password='your_password',
    database='your_database'
)

cursor = connection.cursor()
cursor.execute(
    'INSERT INTO events(id, event_name, date_created) VALUES(%s, %s, %s)',
    (event_id, event_name, formatted_date)
)

connection.commit()
print("Date stored successfully")

Formatted date: 2024-01-15 14:30:25
Date stored successfully

Retrieving a Date from MySQL

Fetch the date from database and convert it back to a datetime object ?

from datetime import datetime
import mysql.connector

connection = mysql.connector.connect(
    host='localhost',
    user='your_username',
    password='your_password', 
    database='your_database'
)

cursor = connection.cursor()

# Fetch the record
cursor.execute('SELECT id, event_name, date_created FROM events WHERE id=1')
result = cursor.fetchone()

if result:
    event_id, event_name, date_str = result
    print(f"Retrieved: ID={event_id}, Name={event_name}, Date={date_str}")
    
    # Convert string back to datetime object
    created_date = datetime.strptime(str(date_str), '%Y-%m-%d %H:%M:%S')
    print(f"Parsed datetime: {created_date}")
    print(f"Type: {type(created_date)}")

connection.close()

Retrieved: ID=1, Name=Python Workshop, Date=2024-01-15 14:30:25
Parsed datetime: 2024-01-15 14:30:25
Type: <class 'datetime.datetime'>

Working with Different Date Formats

You can store dates in different formats depending on your requirements ?

from datetime import datetime

now = datetime.now()

# Different date formats
date_only = now.strftime('%Y-%m-%d')          # Date only
datetime_format = now.strftime('%Y-%m-%d %H:%M:%S')  # Full datetime
custom_format = now.strftime('%d/%m/%Y %I:%M %p')    # Custom format

print(f"Date only: {date_only}")
print(f"Full datetime: {datetime_format}")  
print(f"Custom format: {custom_format}")

Date only: 2024-01-15
Full datetime: 2024-01-15 14:30:25
Custom format: 15/01/2024 02:30 PM

Common Date Format Codes

Conclusion

Use strftime() to format datetime objects before storing them in MySQL. Use strptime() to parse date strings back to datetime objects when retrieving from the database. Always ensure your table has appropriate DATE or DATETIME column types.