A Hitchhiker's Guide to SQLite with Python

To explore SQLite along with Python, which is a user-friendly and no-nonsense language, we are going to build a simple tic-tac-toe game. So buckle up and get your machines ready!

Introduction to SQLite and Embedded Databases

SQLite is a small, self-contained, client/embedded database written in the C Programming language and can be summarized using the following points:

• Application library.
• Directly writes to disk.
• Cross-platform, stable, and the most widely deployed and used database engine.
• Zero configuration.
• Automatic recovery.
• Public domain license.

In this article, we are going to see many of these features in action. Features such as data replication, network access, etc., are intentionally absent from SQLite since it was built for a much more lightweight use case. SQLite databases are very useful in constrained environments such as mobile devices, lightweight desktop apps, IoT devices, and games.

Setting Up SQLite with Python

Let's get our learning environment up and running by installing:

• Python 3;
• any IDE for Python coding—I recommend PyCharm;
• Arctype SQL Client;
• and SQLite tools installed on your OS for CLI (Mac OS comes with it by default).

Library Setup and Database Creation

Sqlite3 comes packaged with Python by default and there is no need to install any additional libraries. Let's go ahead and import Sqlite3 and then create our database:

import sqlite3

conn = sqlite3.connect('sqlite.db')

That's it. The database is now created and we can create tables and insert data now. Isn't this a breeze when compared to setting up a full-blown RDBMS?

Where Are SQLite Databases Stored?

Once you run the above application, a sqlite.db file is automatically created at the project root level. As we will see later, this file will be re-used whenever the application is started. We can customize the location of this file by just connecting to the database with its path.

conn = sqlite3.connect('data/sqlite.db')

This will create the sqlite.db file inside the data folder. Keep in mind that the directory data needs to be created beforehand. The file extension .db is also optional—SQLite will just create a binary file with the name we provide it with.

Configuring and Managing an SQLite Database

As we just saw, there is no configuration required to get our database set up. Since it is an application library, there is no server and the database comes up and goes down with the connection initiated by the application running behind it.

Connecting to an SQLite Database from the Command Line

SQLite (version 3) comes pre-installed on Mac OS operating systems. For other platforms, we can download the CLI and other tools from the SQLite website itself. The command line can be invoked by typing the command sqlite3.

Notice that I have connected directly to the database that I used from the Python script. We can do so by using sqlite3 /path/to/file.

Creating Sample Tables and Running Basic Queries

Let's create a table for testing purposes.

create table testing(id int, name text);

And then insert some rows into the table.

insert into testing values(100, 'Name1');
insert into testing values(101, 'Name2');

We can see the records by doing a simple select * statement.

There are a ton of helper functions that SQLite provides to learn more about tables and the schema. Below are some of the functions.

.tables gives me information about all of the tables. .schema gives the information about a specific table. pragma table_info is a function that gives more information about the table (i.e. the data type and column name).

Now, let's connect to this database using our Python script and try to read these tables.

import sqlite3

conn = sqlite3.connect('data/sqlite.db')

cursor = conn.cursor()

cursor.execute('SELECT * FROM testing')

rows = cursor.fetchall()

for row in rows:
    print(row)

Above code will print.

(100, 'Name1')
(101, 'Name2')

An Example Tic-Tac-Toe Game Using Python and SQLite

Let's consider a real-world use case—games. Games in mobile applications make heavy use of SQLite for storing their game status and then sync to a backend server periodically/based on a user trigger. In this example, we will see a Tic-Tac-Toe game developed in Python that uses SQLite to store game data and player information. We can then query this database offline to retrieve game-related information.

Database Table Design for Tic-Tac-Toe

Before we attempt to write the game, let's create our database ER diagram that is going to represent our tables.

First, we are going to create our Player table which has the id, name, and created_date which is going to be used to store the player data. Next, we are going to create our Game table which will have the player details, game status, and winner details. The Entity-Relationship diagram for these tables is given below.

We could further normalize this by introducing a game status entity, but we are looking for a simple model to understand SQLite rather than a perfect design. Based on this spec, tables can be created accordingly:

CREATE TABLE player(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  created DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE game(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_player_id INTEGER,
  second_player_id INTEGER,
  created DATETIME DEFAULT CURRENT_TIMESTAMP,
  game_status TEXT,
  winner_id INTEGER,
  FOREIGN KEY (first_player_id) REFERENCES player (id),
  FOREIGN KEY (second_player_id) REFERENCES player (id),
  FOREIGN KEY (winner_id) REFERENCES player (id)
);

The database is now set up, so let's code our game!

Coding the Tic-Tac-Toe Game in Python

Before starting to write everything out in Python, let's first determine our game's logic. For this simple example, I would recommend something like this:

Initialize game board   
  Initialize users 
    Game logic (While loop based on exit conditions) 
      Begin game logic 
  End

We are going to a simple two-dimensional array to hold the game board. Instead of walking through every element of the code, I am going to paste the whole code below and give a high-level walkthrough since the code is pretty self-explanatory.

"""
The Game board is a two D array emulating the X*Y Tic Tac Toe Game
"""
global game_board
# Constants
place_already_filled = "Given place is already filled. Provide an empty place from the board"
invalid_move = "Invalid move. Enter a value within the board dimension."
invalid_value = "Invalid value. Enter an integer value within the board dimension."
game_over_tie = "Game Over. It is a tie."


def initialise_gameboard(dimensions):
    global game_board
    dimensions = int(dimensions)
    # Fill with empty strings
    game_board = [[' ' for _ in range(dimensions)] for _ in range(dimensions)]


def print_board():
    print("**************************")
    for item in game_board:
        print(item)
    print("**************************")


def check_game_status(x_pos, y_pos, player):
    row_match = True
    column_match = True
    left_diagonal_match = True
    right_diagonal_match = True

    for i in range(0, len(game_board)):
        if game_board[x_pos][i] != game_board[x_pos][y_pos]:
            row_match = False

    for i in range(0, len(game_board)):
        if game_board[i][y_pos] != game_board[x_pos][y_pos]:
            column_match = False

    for i in range(0, len(game_board)):
        if game_board[i][i] != game_board[x_pos][y_pos]:
            left_diagonal_match = False
        if game_board[i][len(game_board) - i - 1] != game_board[x_pos][y_pos]:
            right_diagonal_match = False

    print_board()

    if row_match or column_match or left_diagonal_match or right_diagonal_match:
        return f"Player {player} has won"
    else:
        return "Playing"


def game():
    dimensions = input("Enter board dimensions (3 for a 3x3 board): ")
    first_user = input("Enter first user name : ")
    second_user = input("Enter second user name: ")
    initialise_gameboard(dimensions)
    player = first_user
    count = 1

    while True:
        print(f"It's user : {player}'s turn")
        positions = input("Enter move position (x,y) : ").split(",")

        try:
            x_pos = int(positions[0]) - 1
            y_pos = int(positions[1]) - 1
        except ValueError:
            print(invalid_value)
            continue

        if x_pos < 0 or x_pos >= len(game_board) or y_pos < 0 or y_pos >= len(game_board):
            print(invalid_move)
            continue

        if game_board[x_pos][y_pos] != ' ':
            print(place_already_filled)
            continue

        if player is first_user:
            game_board[x_pos][y_pos] = 'X'
        else:
            game_board[x_pos][y_pos] = 'O'

        count = count + 1

        game_status = check_game_status(x_pos, y_pos, player)

        if game_status != "Playing":
            print(game_status)
            break

        if count == len(game_board) * len(game_board) + 1:
            print_board()
            print(game_over_tie)
            break

        # Switch players after
        if player is first_user:
            player = second_user
        else:
            player = first_user

    another_game = input("Do you want to play another game (Y/N) : ")

    if another_game.lower() == 'y' or another_game == 'Y':
        game()


game()

The game is nothing fancy. As you can see, the game method is where the core logic begins.

Go ahead and try it out. Below is a sample game output on the console.

Enter board dimensions (3 for a 3x3 board): 3
Enter first user name : p1
Enter second user name: p2
It's user : p1's turn
Enter move position (x,y) : 1,1
**************************
['X', ' ', ' ']
[' ', ' ', ' ']
[' ', ' ', ' ']
**************************
It's user : p2's turn
Enter move position (x,y) : 2,2
**************************
['X', ' ', ' ']
[' ', 'O', ' ']
[' ', ' ', ' ']
**************************
It's user : p1's turn
Enter move position (x,y) : 1,3
**************************
['X', ' ', 'X']
[' ', 'O', ' ']
[' ', ' ', ' ']
**************************
It's user : p2's turn
Enter move position (x,y) : 2,3
**************************
['X', ' ', 'X']
[' ', 'O', 'O']
[' ', ' ', ' ']
**************************
It's user : p1's turn
Enter move position (x,y) : 1,2
**************************
['X', 'X', 'X']
[' ', 'O', 'O']
[' ', ' ', ' ']
**************************
Player p1 has won
Do you want to play another game (Y/N) : n

Process finished with exit code 0

You can put the above code in a separate game.py file for clarity. Now to the next part, integrating this game with our database.

SQLite Integration

Now, we have to make the user entries whenever the users have been created and make game entries when the game is won or a draw. Before we create our queries, let's make the actual Data Access layer methods (i.e. creating a player entry and a game entry).

import sqlite3

conn = sqlite3.connect('data/sqlite.db')


def create_player(name):
    query = f"INSERT INTO player(name) VALUES ('{name}')"
    cursor = conn.cursor()
    cursor.execute(query)
    conn.commit()
    user_id = cursor.lastrowid
    cursor.close()
    return user_id


def create_game(first_player_id, second_player_id, game_status, winner_id):
    query = f"INSERT INTO game(first_player_id, second_player_id, game_status, winner_id) " \
            f"VALUES ({first_player_id}, {second_player_id}, '{game_status}', {winner_id})"

    cursor = conn.cursor()
    cursor.execute(query)
    conn.commit()
    cursor.close()

Now, we can use these simple methods from our game:

# Paste below code anywhere after getting the user details
first_user_id = dao.create_player(first_user)
second_user_id = dao.create_player(second_user)

# Paste below code when checking for game status i.e 
# game_status != "Playing"
winner_id = second_user_id

if player == first_user:
   winner_id = first_user_id

dao.create_game(first_user_id, second_user_id, game_status, winner_id)

We can do a similar code for storing game status during draw as well. Now we can play the game and the data should be recorded in our tables.

Importing and Exporting Data from SQLite

Having now played a few test games, it's now time to examine the data. As everyone knows, Arctype makes querying, analyzing, and visualizing data better than ever, so before we continue, we need to learn about importing and exporting data from SQLite. Data can be exported/imported either at the table or database level. The table level is typically used to export to other databases/applications and the database level is typically used for backup.

Importing and Exporting SQLite Tables

Apart from programmatic integration, once can easily export data from an SQLite database using data export facility. The most commonly used db agnostic standard is a CSV format. Let's export our game table.

sqlite3 -header -csv /path/to/sqlite.db/ 'select * from game;' > game.csv

game.csv would contain the below content:

id,first_player_id,second_player_id,created,game_status,winner_id
1,1,2,"2021-04-25 18:19:07","Player p1 has won",1
2,3,4,"2021-04-25 18:23:23","Player p2 has won",4
3,7,8,"2021-04-26 05:20:04","Player p1 has won",7
4,9,10,"2021-04-26 05:25:44",Draw

This is very similar to other database systems.

Import is quite easy, first we need to create a .sql file with the below content.

.import /path/to/csv game

And then from the command line,

sqlite3 /path/to/database < /path/to/sql/

Importing and Exporting Databases

Database backup is actually pretty easy, just copy the SQLite file to back up system. But this method is usually difficult since users/application might be running simultaneously and the data is stored in a binary format. To do a point-in-time snapshot/human readable snapshot when the database is running/being used then we can dump the database content to a file format of our choice.

sqlite3 /path/to/db .dump > dump.sql

The dump.sql content will look like below.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE player(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  created DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO player VALUES(1,'p1','2021-04-25 18:18:55');
INSERT INTO player VALUES(2,'p2','2021-04-25 18:18:55');
INSERT INTO player VALUES(3,'p1','2021-04-25 18:23:05');
INSERT INTO player VALUES(4,'p2','2021-04-25 18:23:05');
INSERT INTO player VALUES(5,'p1','2021-04-26 05:16:56');
INSERT INTO player VALUES(6,'p2','2021-04-26 05:16:56');
INSERT INTO player VALUES(7,'p1','2021-04-26 05:19:04');
INSERT INTO player VALUES(8,'p2','2021-04-26 05:19:04');
INSERT INTO player VALUES(9,'p1','2021-04-26 05:24:44');
INSERT INTO player VALUES(10,'p2','2021-04-26 05:24:44');
CREATE TABLE game(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_player_id INTEGER,
  second_player_id INTEGER,
  created DATETIME DEFAULT CURRENT_TIMESTAMP,
  game_status TEXT,
  winner_id INTEGER,
  FOREIGN KEY (first_player_id) REFERENCES player (id),
  FOREIGN KEY (second_player_id) REFERENCES player (id),
  FOREIGN KEY (winner_id) REFERENCES player (id)
);
INSERT INTO game VALUES(1,1,2,'2021-04-25 18:19:07','Player p1 has won',1);
INSERT INTO game VALUES(2,3,4,'2021-04-25 18:23:23','Player p2 has won',4);
INSERT INTO game VALUES(3,7,8,'2021-04-26 05:20:04','Player p1 has won',7);
INSERT INTO game VALUES(4,9,10,'2021-04-26 05:25:44','Draw',NULL);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('player',10);
INSERT INTO sqlite_sequence VALUES('game',4);
COMMIT;

Depending on what data is there, the above SQL file content might change but the backup/format is just plain SQL.

If we are not bothered about human readability then we can just dump it in a native format like below.

sqlite3 /path/to/db ".backup db_backup.db"

Import for binary files are quite similar -

sqlite3 /path/to/db ".restore db_backup.db"

Import for SQL file -

sqlite3 /path/to/db < dump.sql

Querying Our SQLite Game Data in Arctype

Our SQLite data is now ready to be loaded into Arctype. This can be achieved in one of two ways—by creating and populating new tables using the SQLite-generated database file or by importing CSV data into existing tables. First, let's create a new SQLite database by adding a new connection:

Next, you can choose a name for your new database. For this example, tictactoe seems fitting enough:

Now, it's time to import our data.

Importing SQLite Tables into Arctype

We can create and populate tables in our new SQLite database using the SQL table dump from above. Simply copy and paste the contents of the dump.sql file into a query, remove the manual BEGIN TRANSACTION; and COMMIT; lines (Arctype automatically wraps all of your statements in transactions) and click 'Run':

And that's it! Your tables should be successfully created and filled with data. This method is fairly simple, but is only recommended for your initial data import—deleting and re-creating tables with increasingly large datasets is unsustainable, so ultimately, you'll want to simply import new data into your existing tables.

Importing .CSV Data into Existing Arctype SQLite Tables

Let's say we have already created our game and player tables, rather than dropping them and creating new tables using the SQL from our dump.sql file, we can instead simply import the table data from our CSV files. As you remember from above, we exported our tables into game.csv and player.csv respectively. First, select the table into which you want to import CSV data, and then click the "Import CSV" button:

Then, simply review the data preview and click 'Accept' if everything looks correct:

Viewing and Querying Game Data in Arctype

Now that all of our data has been imported into Arctype, you can view it by simply selecting the table of your choosing. Here is our game table:

Quite similarly, this is what our player table looks like:

Now for the fun part, let's select players who have won the most games. Simply open a new tab and select "query":

Now, let's run this query:

SELECT
  player.name,
  count(*) AS count
FROM
  player
  JOIN game ON player.id = game.winner_id
GROUP BY
  player.name
ORDER BY
  COUNT DESC;

Your results should look something like this:

As you can see, in terms of its querying capabilities, SQLite is as powerful as any SQL database. We can run more queries like:

• Which players were involved in most games that ended up in a draw?
• Which players lost the most?
• Which player got scores more than the average wins?

What Are The Trade-Offs of Using SQLite?

SQLite is often misunderstood and not properly utilized. Yes, it comes under the category of client/embedded database but is essentially trying to solve a very different problem. So, comparing SQLite with MySQL/PostgreSQL/Other RDBMS is definitely not the right way. In most environments, SQLite actually works in tandem with such client-server databases as we just saw in the examples above.

In some situations, SQLite can be used without any second thoughts, while in others, a more careful analysis of the project requirements may be necessary. The SQLite webpage on when to use SQLite is pretty comprehensive. Below are some of the key highlights from that page:

• SQLite only supports one writer at a time per database file. So it is not suited for heavy concurrent writes. Readers can be N where N is decided by various factors such as OS, hardware, and other apps running on the system.

• You will first run into hardware issues before running into database size issues with SQLite as it supports 281TB of data storage. Good luck hitting that limit!

• SQLite can handle traffic very well—in fact, the website https://www.sqlite.org/ itself is hosted on SQLite and it handles close to 400K to 500K hits per day. So unless you are on a fairly high traffic website (queries per second/qps measure), then SQLite should serve you very well.

The key thing with any software is to use the right tool for the right use case. At the end of the day, that's what Software craftsmanship/Software Engineering is all about.

Closing Thoughts
Let's pause and observe how widely SQLite is used.

• Mac OSX comes with SQLite by default.
• Python 3 comes with SQLite by default.
• Windows 10 uses SQLite internally—it cannot operate without it.
• Built into PHP and Ruby as well.
• There are tons of others places where it is used. The well-known users page gives a good list.

SQLite is literally everywhere. It is very widely used and extremely well tested and almost a drop-in replacement for fopen in Linux. Learning it and befriending SQLite has a lot of advantages and it is a must-have tool for every programmer. Hopefully, this article along with its examples gave a good introduction to SQLite. So go ahead and play with it and before jumping to the conclusion of using a client-server RDBMS, give SQLite a try and you will be surprised at how it simplifies the tech stack and operations around it.