Python SQLite working with DateTime and timestamp

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:

Solve Python SQLite Exercise
Read Python SQLite Tutorial (Complete Guide)

Prerequisites
Python example to insert/Retrieve DateTime from SQLite table
Next Steps:

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())
Code language: Python (python)

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())
Code language: Python (python)

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.

Python SQLite working with Date and DateTime

Table of contents

Prerequisites

Python example to insert/Retrieve DateTime from SQLite table

Next Steps:

About Vishal

Related Tutorial Topics:

All Coding Exercises:

Python Exercises and Quizzes

About PYnative

Follow Us

Explore Python

Coding Exercises

Legal Stuff