This lesson demonstrates how to work with SQLite date and timestamp types in Python and vice-versa. Most of the time, we need to insert Python date or DateTime value into an SQLite table. Also, we need to read the SQLite date and timestamp values stored in the SQLite3 database and convert them into Python date and DateTime types.
Also Read:
Table of contents
Prerequisites
Before executing the following program, please make sure you have an SQLite table with a timestamp as a column from which you want to retrieve/insert data.
For this lesson, I am using the ‘new_developers’ table present in my SQLite database.
If a table is not present in your SQLite database, then please refer to the following articles: –
Python example to insert/Retrieve DateTime from SQLite table
In a usual scenario, when you execute the insert query with the DateTime object, the Python sqlite3 module converts it into a string format instead of an actual DateTime. And when you run a SELECT query from Python to read DateTime values from SQLite table, the sqlite3 module will convert it into a string object. Let’s understand this scenario with a simple example.
import datetime
import sqlite3
def addDeveloper(id, name, joiningDate):
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db')
cursor = sqliteConnection.cursor()
print("Connected to SQLite")
sqlite_create_table_query = '''CREATE TABLE new_developers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
joiningDate timestamp);'''
cursor = sqliteConnection.cursor()
cursor.execute(sqlite_create_table_query)
# insert developer detail
sqlite_insert_with_param = """INSERT INTO 'new_developers'
('id', 'name', 'joiningDate')
VALUES (?, ?, ?);"""
data_tuple = (id, name, joiningDate)
cursor.execute(sqlite_insert_with_param, data_tuple)
sqliteConnection.commit()
print("Developer added successfully \n")
# get developer detail
sqlite_select_query = """SELECT name, joiningDate from new_developers where id = ?"""
cursor.execute(sqlite_select_query, (1,))
records = cursor.fetchall()
for row in records:
developer = row[0]
joining_Date = row[1]
print(developer, " joined on", joiningDate)
print("joining date type is", type(joining_Date))
cursor.close()
except sqlite3.Error as error:
print("Error while working with SQLite", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("sqlite connection is closed")
addDeveloper(1, 'Mark', datetime.datetime.now())
Output:
Connected to SQLite Developer added successfully Mark joined on 2019-06-28 21:12:35.799206 joining date type is <class 'str'> sqlite connection is closed
As we can see, we inserted a date object, but when we retrieved the details from a table, we got a string type. But we don’t want string type. We want the DateTime type so we can directly use it.
To solve this problem, use detect_types as PARSE_DECLTYPES and PARSE_COLNAMES as arguments in the connect method of the sqlite3 module.
sqlite3.PARSE_DECLTYPES
If you use this parameter in the connect method, then the sqlite3 module parses the declared type for each column it returns.
It will parse the declared type then use the type converters dictionary to execute the converter function registered for that type there.
sqlite3.PARSE_COLNAMES
If you use this parameter in the connect method, then the SQLite interface parses the column name for each column it returns. It will use the converters dictionary and then use the converter function found there to return the value.
Let see the example now. In this example, when we read DateTime from the SQLite table we must get the joining date type as a datetime.
import datetime
import sqlite3
def addDeveloper(id, name, joiningDate):
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db',
detect_types=sqlite3.PARSE_DECLTYPES |
sqlite3.PARSE_COLNAMES)
cursor = sqliteConnection.cursor()
print("Connected to SQLite")
sqlite_create_table_query = '''CREATE TABLE new_developers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
joiningDate timestamp);'''
cursor = sqliteConnection.cursor()
cursor.execute(sqlite_create_table_query)
# insert developer detail
sqlite_insert_with_param = """INSERT INTO 'new_developers'
('id', 'name', 'joiningDate')
VALUES (?, ?, ?);"""
data_tuple = (id, name, joiningDate)
cursor.execute(sqlite_insert_with_param, data_tuple)
sqliteConnection.commit()
print("Developer added successfully \n")
# get developer detail
sqlite_select_query = """SELECT name, joiningDate from new_developers where id = ?"""
cursor.execute(sqlite_select_query, (1,))
records = cursor.fetchall()
for row in records:
developer = row[0]
joining_Date = row[1]
print(developer, " joined on", joiningDate)
print("joining date type is", type(joining_Date))
cursor.close()
except sqlite3.Error as error:
print("Error while working with SQLite", error)
finally:
if (sqliteConnection):
sqliteConnection.close()
print("sqlite connection is closed")
addDeveloper(1, 'Mark', datetime.datetime.now())
Output:
Connected to SQLite Developer added successfully Mark joined on 2019-06-28 20:57:32.352790 joining date type is <class 'datetime.datetime'> sqlite connection is closed
As you can see when we retrieved the joining date from SQLite table and we got the result in datetime.datetime type.
Next Steps:
To practice what you learned in this article, Solve a Python SQLite Exercise project to practice database operations.
