How to store and retrieve date into Sqlite3 database using Python?

You can easily store and retrieve dates in SQLite3 database using Python's sqlite3 module. When inserting dates, pass the datetime.date object directly and Python handles the conversion automatically by using the detect_types=sqlite3.PARSE_DECLTYPES parameter.

Setting Up the Database Connection

First, create a connection with date parsing enabled and set up a table ?

import sqlite3
import datetime

# Enable automatic date parsing
conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
conn.execute('''CREATE TABLE TEST (ID TEXT PRIMARY KEY NOT NULL, DATE DATE)''')
conn.commit()
print("Table created successfully")

Table created successfully

Storing Dates in the Database

Insert date objects directly using parameterized queries ?

import sqlite3
import datetime

conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
conn.execute('''CREATE TABLE TEST (ID TEXT PRIMARY KEY NOT NULL, DATE DATE)''')

# Insert date object directly
conn.execute("INSERT INTO TEST (ID,DATE) VALUES (?, ?)", 
             ('My date', datetime.date(2018, 1, 4)))
conn.commit()
print("Date record inserted successfully")

Date record inserted successfully

Retrieving Dates from the Database

When you fetch values, SQLite automatically converts them back to Python date objects ?

import sqlite3
import datetime

conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
conn.execute('''CREATE TABLE TEST (ID TEXT PRIMARY KEY NOT NULL, DATE DATE)''')

# Insert a date
conn.execute("INSERT INTO TEST (ID,DATE) VALUES (?, ?)", 
             ('Sample ID', datetime.date(2023, 12, 25)))
conn.commit()

# Retrieve the date
cursor = conn.execute("SELECT ID, DATE FROM TEST")
for row in cursor:
    print("ID:", row[0])
    print("Date:", row[1])
    print("Type:", type(row[1]))

ID: Sample ID
Date: 2023-12-25
Type: <class 'datetime.date'>

Working with Multiple Date Formats

You can also store datetime objects with time information ?

import sqlite3
import datetime

conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
conn.execute('''CREATE TABLE EVENTS (ID TEXT, TIMESTAMP TIMESTAMP)''')

# Insert datetime with time
now = datetime.datetime.now()
conn.execute("INSERT INTO EVENTS VALUES (?, ?)", ('Event 1', now))
conn.commit()

# Retrieve datetime
cursor = conn.execute("SELECT * FROM EVENTS")
for row in cursor:
    print("Event ID:", row[0])
    print("Timestamp:", row[1])
    print("Type:", type(row[1]))

Event ID: Event 1
Timestamp: 2024-01-15 10:30:45.123456
Type: <class 'datetime.datetime'>

Key Points

Conclusion

SQLite3 with detect_types=sqlite3.PARSE_DECLTYPES automatically handles date conversion. Store Python date objects directly and retrieve them as proper datetime objects without manual parsing.