PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Python SQLite working with Date and DateTime

Python SQLite working with Date and DateTime

Updated on: March 9, 2021 | 9 Comments

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)

Table of contents

  • 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: –

  • Create SQLite table from Python.
  • Insert data into SQLite Table from Python

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.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python SQLite

  • Python SQLite Guide
  • Python SQLite Insert
  • Python SQLite Select
  • Python SQLite Update
  • Python SQLite Delete
  • Python SQLite Create Functions
  • Python Parameterized Query
  • Python SQLite BLOB
  • Python SQLite DateTime
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com