Making SQLite easy for Python

Outline

Why use a database

Getting into databases can seem confusing. How do I create one? How do I add data to it? How do I modify data inside of it? The list goes on and often we end up using a simple file format to save our data. While this works well enough for small applications, you will start to run into issues as your data grows in complexity and your needs to use it grow as well.

Creating and using a database in Python shouldn't be hard! Through this post, I will introduce a Python class I wrote called DB which facilitates easy use of an SQLite3 database. An SQLite database is sufficient for most projects and speedy enough to handle demanding applications. It is also the most used database in the world.

The DB Class

The DB class was designed to eliminate the boilerplate code need to interact with an SQLite database. It includes a constructor which will create the database and folder structure if they do not already exist and a destructor to automatically close the connection when the program finishes. The database connection and cursor references are also neatly tucked inside the class to automatically execute and commit SQL when needed. In addition, all select queries automatically return a dictionary including their column names.

Example usage

Below is an example demonstrating how in 15 lines of code (including comments) we can:
- Create a database
- Create a table
- Insert data
- Query it back out

from DB import DB

# Connect to DB
myDB = DB("db/db.sqlite3")

# Create a table
dbCols = ["id integer PRIMARY KEY", "name text NOT NULL", "priority integer"]
myDB.create_table("Users", dbCols)

# Insert data
myDB.iud("INSERT INTO Users (name) VALUES (?)", ("Joe",))

# Query data
for row in myDB.select("SELECT * FROM Users"):
    print(row)

FILE STRUCTURE:
> run.py
> DB.py

CONSOLE:
IN:  $> python run.py
OUT: $> {'id': 1, 'name': 'Joe', 'priority': None}

We just created a database, inserted data, and queried it back out in only 7 lines of code!

Special functions

To help facilitate common 'complex' tasks like adding a column to your database, there are a few special functions.

• add_column(self, table, new_col)

Below is an example use case where we are adding in a new column called age with type float.

myDB.add_column('Users', 'age float')

Next, we have the iud method which allows us to perform insert, update, and delete all with optional arguments. If you decide to use arguments, place a ? where they should go. This method will commit the cursor results to the database after executing.

• iud(self, statement, args=None)

In this example, we will delete all of the users with the name John.

myDB.iud("DELETE FROM Users WHERE name=?", ("John",))

Last is the sql method which allows us to execute an arbitrary SQL command. You could use this if you wanted to add a trigger, view or a similar non-standard command.

• sql(self, statement, args=None, commit=False)

Here is an example of adding a simple view using the sql method.

myDB.sql("""
        CREATE VIEW names
        AS
        SELECT name from Users
        """, 
        commit=True)

Add to your project

The examples above imported the DB class and instantiated an object called myDB. To enable this import and start using the class, you will want to create a new file in your code folder called DB.py. Next, go to the GitHub repository here and copy the class's code.

Once you have copied the code, paste it into the new file you have created. You are now ready to start using the DB class by adding from DB import DB to your main file! For further documentation, please refer to the comments inside of the class, and for general SQLite usage please refer to the post here.