Mini Tutorial #01 Python 3 SQLite3 Basics

In this tutorial we are going to build a small class structure to access a to-do table. Python is batteries included language. So obviously it has the necessary tools and APIs built in.

Please note that SQLite3 is a small SQL implementation. It may not have the performance requirements for a large-scale application. I am not using SQLAlchemy because it is out of scope for this tutorial. (Nevertheless, it is a very good library).

How do we use SQLite3?

Simply import it and viola it’s there.

import sqlite3

We are going to build a single table application to capture a list of To-Dos.
First let’s create a small class to represent our database. We are creating the database in memory.

Using SQLite3 to Create ToDo Table

class Database:
    def __init__(self):
        self.connection = sqlite3.connect(":memory:")
        self.connection.row_factory = sqlite3.Row

    def setup(self):
        self.connection.execute('''
        CREATE TABLE todo (id INTEGER PRIMARY KEY,
            created TEXT, due TEXT, todo TEXT, state TINYINT)
        ''')

    def close(self):
        self.connection.close()

Let’s put the ‘id’ as primary key. We will also have ‘created’ and ‘due’ dates. To-do and a state. For this application, we are going with the simplest to-do states available. TODO, DOING and DONE.

ToDo Entity

STATE_TODO = 1
STATE_DOING = 2
STATE_DONE = 3

Let’s define the states as above. Now let’s define the ToDo entity. This class will only have the single responsibility of holding and representing attributes of a to-do task.

NOT_CREATED = None

class ToDo:
    state_map = {STATE_TODO: 'todo', STATE_DOING: 'doing', STATE_DONE: 'done'}

    def __init__(self, created, due, todo, state, identifier=NOT_CREATED):
        self.created = created
        self.due = due
        self.todo = todo
        self.state = state
        self.identifier = identifier

    def __str__(self):
        todo = "Todo - [{}] [{}] {} [{}]".format(self.created, self.due,
                                                 self.todo, ToDo.state_map[self.state])
        if self.identifier != NOT_CREATED:
            todo = "{} [{}]".format(todo, self.identifier)

        return todo

I have added the __str__ function to ensure that objects can be printed in a readable manner.
Let’s create a simple method to get current time. Which we can use when we are creating a new to-do.

def now():
    return datetime.datetime.now().strftime("%B %d, %Y")

Accessing the SQLite3 database using Python 3

Now let’s sink our teeth into accessing the database. We have the ToDo class. Are we going to modify it? Hell NO! Let’s define a repository.

class Repository:
    def __init__(self, database):
        self.database = database
        self.connection = database.connection

    def read(self, query, *args, one=False):
        cursor = self.connection.cursor()

        cursor.execute(query, *args)
        if one:
            value = cursor.fetchone()
        else:
            value = cursor.fetchall()

        cursor.close()

        return value

We will inject the Database object through the constructor. We will create a cursor. Execute the query, get value and close the cursor. You wipe your butt after pooping, don’t you? The same way, free your resources to avoid a code smell.

Close the SQLite3 cursors, connections.

Enough butt talks. Let’s get on with it. Here’s the ToDoRepository code:

class ToDoRepository(Repository):
    def __init__(self, database):
        super().__init__(database)

    def upsert(self, todo):
        if todo.identifier == NOT_CREATED:
            self.connection.execute("INSERT INTO todo(created, due, todo, state) VALUES (?, ?, ?, ?)",
                                    (todo.created, todo.due, todo.todo, todo.state))
        else:
            self.connection.execute("UPDATE todo SET created = ?, due = ?, todo = ?, state = ? WHERE id = ?",
                                    (todo.created, todo.due, todo.todo, todo.state, todo.identifier))
        self.connection.commit()

    def all(self):
        elements = super().read("SELECT * FROM todo")
        return self._map_array(elements)

    def search(self, todo, one=False):
        elements = super().read("SELECT * FROM todo WHERE todo LIKE ? COLLATE NOCASE",
                                ('%' + todo.strip() + '%',), one=one)
        if one:
            return self._map(elements)

        return self._map_array(elements)

    def _map_array(self, elements):
        todos = []
        for element in elements:
            todos.append(self._map(element))
        return todos

    @staticmethod
    def _map(element):
        return ToDo(element['created'],
                    element['due'], element['todo'],
                    element['state'], identifier=element['id'])

    def delete(self, identifier):
        self.connection.execute("DELETE FROM TABLE todo WHERE id = ?", identifier)
        self.connection.commit()

Let’s break the upsert to two methods. Click here for a GIF.

    def upsert(self, todo):
        if todo.identifier == NOT_CREATED:
            self.insert(todo)
        else:
            self.update(todo)

    def insert(self, todo):
        self.connection.execute("INSERT INTO todo(created, due, todo, state) VALUES (?, ?, ?, ?)",
                                (todo.created, todo.due, todo.todo, todo.state))
        self.connection.commit()

    def update(self, todo):
        self.connection.execute("UPDATE todo SET created = ?, due = ?, todo = ?, state = ? WHERE id = ?",
                                (todo.created, todo.due, todo.todo, todo.state, todo.identifier))
        self.connection.commit()

Here we have methods to perform upsert, insert, update, delete and search. ‘all’ method returns all the ToDos.
This is how the final class diagram looks like:
Class diagram for ToDo app

Complete Code for a Python 3 SQLite3 ToDo Object Structure

Now let’s see how it works. Here’s the full code. You can see how the classes are used below.

#!/usr/bin/env python3

import sqlite3
import datetime

STATE_TODO = 1
STATE_DOING = 2
STATE_DONE = 3

NOT_CREATED = None


class Database:
    def __init__(self):
        self.connection = sqlite3.connect(":memory:")
        self.connection.row_factory = sqlite3.Row

    def setup(self):
        self.connection.execute('''
        CREATE TABLE todo (id INTEGER PRIMARY KEY,
            created TEXT, due TEXT, todo TEXT, state TINYINT)
        ''')

    def close(self):
        self.connection.close()


class ToDo:
    state_map = {STATE_TODO: 'todo', STATE_DOING: 'doing', STATE_DONE: 'done'}

    def __init__(self, created, due, todo, state, identifier=NOT_CREATED):
        self.created = created
        self.due = due
        self.todo = todo
        self.state = state
        self.identifier = identifier

    def __str__(self):
        todo = "Todo - [{}] [{}] {} [{}]".format(self.created, self.due,
                                                 self.todo, ToDo.state_map[self.state])
        if self.identifier != NOT_CREATED:
            todo = "{} [{}]".format(todo, self.identifier)

        return todo


class Repository:
    def __init__(self, database):
        self.database = database
        self.connection = database.connection

    def read(self, query, *args, one=False):
        cursor = self.connection.cursor()

        cursor.execute(query, *args)
        if one:
            value = cursor.fetchone()
        else:
            value = cursor.fetchall()

        cursor.close()

        return value


class ToDoRepository(Repository):
    def __init__(self, database):
        super().__init__(database)

    def upsert(self, todo):
        if todo.identifier == NOT_CREATED:
            self.insert(todo)
        else:
            self.update(todo)

    def insert(self, todo):
        self.connection.execute("INSERT INTO todo(created, due, todo, state) VALUES (?, ?, ?, ?)",
                                (todo.created, todo.due, todo.todo, todo.state))
        self.connection.commit()

    def update(self, todo):
        self.connection.execute("UPDATE todo SET created = ?, due = ?, todo = ?, state = ? WHERE id = ?",
                                (todo.created, todo.due, todo.todo, todo.state, todo.identifier))
        self.connection.commit()

    def all(self):
        elements = super().read("SELECT * FROM todo")
        return self._map_array(elements)

    def search(self, todo, one=False):
        elements = super().read("SELECT * FROM todo WHERE todo LIKE ? COLLATE NOCASE",
                                ('%' + todo.strip() + '%',), one=one)
        if one:
            return self._map(elements)

        return self._map_array(elements)

    def _map_array(self, elements):
        todos = []
        for element in elements:
            todos.append(self._map(element))
        return todos

    @staticmethod
    def _map(element):
        return ToDo(element['created'],
                    element['due'], element['todo'],
                    element['state'], identifier=element['id'])

    def delete(self, identifier):
        self.connection.execute("DELETE FROM TABLE todo WHERE id = ?", identifier)
        self.connection.commit()


def now():
    return datetime.datetime.now().strftime("%B %d, %Y")


def main():
    db = Database()
    db.setup()

    # Create repo
    todos = ToDoRepository(db)

    # Insert data
    todos.upsert(ToDo(now(), now(), 'Create a new todo', STATE_TODO))
    todos.upsert(ToDo(now(), now(), 'Test the app', STATE_TODO))

    # Display all
    print("=============")

    for todo in todos.all():
        print(todo)

    print("=============")

    # Search
    todo = todos.search("test", one=True)
    print(todo)

    # Update
    todo.state = STATE_DONE
    todos.upsert(todo)

    # Display all
    print("=============")

    for todo in todos.all():
        print(todo)

    print("=============")

    # Close connection
    db.close()


if __name__ == '__main__':
    main()

Here’s the output:

C:\Users\Bhathiya\Anaconda3\python.exe D:/PROJECTS/PandaBunnyPython/sqlite3_basics.py
=============
Todo - [August 26, 2017] [August 26, 2017] Create a new todo [todo] [1]
Todo - [August 26, 2017] [August 26, 2017] Test the app [todo] [2]
=============
Todo - [August 26, 2017] [August 26, 2017] Test the app [todo] [2]
=============
Todo - [August 26, 2017] [August 26, 2017] Create a new todo [todo] [1]
Todo - [August 26, 2017] [August 26, 2017] Test the app [done] [2]
=============

That’s it folks. Thanks for reading. I’ve attempted to do my best for this tutorial. I suggest that you read good code to improve your skills. If you have any questions or if you find any problems please comment below.

Want to take it easy? Well, we’ve got a whole bunch of Python tutorials for beginners! We’ve also got more tutorials on using Irfan View to crop and resize images. Don’t forget to check out our pick of the latest trending tech news while you’re at it!

Like what you see? Subscribe to our email list and hit us with a like on our Facebook page to get the latest news updates and tutorials straight to your newsfeed!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.