Working with databases is a fundamental skill in software development. In this article we will look at how to work with databases in Python using SQLite. SQLite is a library that provides a lightweight, self-contained database that stores data as a simple file without the need for a a client-server architecture. This means you can easily write programs which require a database with minimal setup.

SQL stands for Structured Query Language and is used to communicate with databases. Many flavours of SQL exist, yet there is a great deal of similarity between the most common versions. The commands used by SQLite are very similar to those used by other versions and it will be easy to adapt if you need to later on.

You can get started with SQLite in Python without any setup, as SQLite comes as as standard with a Python installation (at the time of writing, current version 3.8). However, in order to really understand what is happening as you learn SQL, is helps to have a way to visualise and interact with your data using a Graphical User Interface (GUI). A great one is available here: db browser for SQLite. You shroud download and install it now if you want to follow along with this article.

In order to work with SQL in Python, you need to learn two different topics. The first is the basics of databases and SQL. Then there is how to work with these in Python.

In this article we will take a look at some basic SQL to set the scene for interacting with a database using Python. We will then make a start at working with SQL via Python.

Creating a Table with SQLite

Using DB Browser for SQLite, create a new database from the file menu, and call it cards.db. Save it anywhere you find convenient.

Now goto the the tab labelled Execute SQL and type/paste in the following SQL commands (typing will make you learn better):

CREATE TABLE cards (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT,
 suit TEXT,
 suit_symbol TEXT,
 score INTEGER
);

This will create the structure for a table where will store a representation of a deck of playing cards.

You should see the following message:

Adding data to an SQLite Table

Now we will add the data for a deck of playing cards into the table we just created. Go back to the Execute SQL tab, select and delete what is there already and instead paste the following SQL commands:

INSERT INTO cards (name, suit, suit_symbol, score) VALUES

    -- Spades
    ('A', 'spades', '♠', 1), 
    ('2', 'spades', '♠', 2), 
    ('3', 'spades', '♠', 3), 
    ('4', 'spades', '♠', 4), 
    ('5', 'spades', '♠', 5), 
    ('6', 'spades', '♠', 6), 
    ('7', 'spades', '♠', 7), 
    ('8', 'spades', '♠', 8), 
    ('9', 'spades', '♠', 9), 
    ('10', 'spades', '♠', 10), 
    ('J', 'spades', '♠', 11), 
    ('Q', 'spades', '♠', 12), 
    ('K', 'spades', '♠', 13), 

    -- Hearts
    ('A', 'hearts', '♥', 1), 
    ('2', 'hearts', '♥', 2), 
    ('3', 'hearts', '♥', 3), 
    ('4', 'hearts', '♥', 4), 
    ('5', 'hearts', '♥', 5), 
    ('6', 'hearts', '♥', 6), 
    ('7', 'hearts', '♥', 7), 
    ('8', 'hearts', '♥', 8), 
    ('9', 'hearts', '♥', 9), 
    ('10', 'hearts', '♥', 10), 
    ('J', 'hearts', '♥', 11), 
    ('Q', 'hearts', '♥', 12), 
    ('K', 'hearts', '♥', 13), 

    -- Clubs
    ('A', 'clubs', '♣', 1), 
    ('2', 'clubs', '♣', 2), 
    ('3', 'clubs', '♣', 3), 
    ('4', 'clubs', '♣', 4), 
    ('5', 'clubs', '♣', 5), 
    ('6', 'clubs', '♣', 6), 
    ('7', 'clubs', '♣', 7), 
    ('8', 'clubs', '♣', 8), 
    ('9', 'clubs', '♣', 9), 
    ('10', 'clubs', '♣', 10), 
    ('J', 'clubs', '♣', 11), 
    ('Q', 'clubs', '♣', 12), 
    ('K', 'clubs', '♣', 13), 

    -- Diamonds
    ('A', 'diamonds', '♦', 1), 
    ('2', 'diamonds', '♦', 2), 
    ('3', 'diamonds', '♦', 3), 
    ('4', 'diamonds', '♦', 4), 
    ('5', 'diamonds', '♦', 5), 
    ('6', 'diamonds', '♦', 6), 
    ('7', 'diamonds', '♦', 7), 
    ('8', 'diamonds', '♦', 8), 
    ('9', 'diamonds', '♦', 9), 
    ('10', 'diamonds', '♦', 10), 
    ('J', 'diamonds', '♦', 11), 
    ('Q', 'diamonds', '♦', 12), 
    ('K', 'diamonds', '♦', 13);

This used the INSERT SQL command to put data into the cards table. At this stage you needn’t worry too much about the syntax, but just get a feel for the big picture.

A few points to observe are:

• INSERT INTO cards (name, suit, suit_symbol, score) VALUES ... does more or less what it says on the tin. The name, suit etc inside the brackets are the attributes into which the values will be stored, so A for name, diamonds for suit etc.
• SQL statements should end with a ;
• Comments in SQL are created using --, so -- Diamonds is a comment, for the benefit of humans.

Browsing Data in DB Browser for SQLite

Now that you have some data in your database, you can explore it using the Browse Data tab.

Selecting Data from a Database with SQLite

Now that you have a database with some data in it, you can use the SELECT statement to query your database to obtain results based on specific criteria. For example, in the “Execute SQL” tab, run the following command:

SELECT * FROM cards WHERE suit = "hearts";

As you can see, this selects all the columns from the cards table for each row in which the suit is hearts.

You can be more specific, and instead of selecting all fields, you can select specific fields. For example

SELECT suit_symbol, score FROM cards WHERE suit = "hearts";

gives just the suit_symbol and score for each of the rows it finds where the suit is hearts.

One thing that can cause confusion when first learning about databases is the terminology. There are quite a few terms and concepts to learn, but for now a couple of crucial ones you need are:

• Attribute – a property or aspect of a thing stored in a database table
• Field – the space (think of a cell in a spreadsheet) inside a database table where an attribute is stored
• Record – represented by a row in a database table, and consisting of fields representing attributes of that record

Note that records are represented by full rows, while attributes are represented by “cells” (if we visualize a database tables as similar to spreadsheets), and the name of the attribute is like the column heading.

You will need to get lots of practice with basic SQL statements in order to become proficient. One great place to get this practice is sqlbolt. I strongly recommend that you work through the first few pages on that site.

Using Python to Interact with an SQLite Database

OK, now let’s write some Python code. First, delete the database you just made, called cards.db. Obviously when you are working with important data you will want to be less aggressive, and also get serious about backing up your database, but for now we are just learning so go ahead…

We are now going to look at how to do everything we just did using Python to interact with the database. So start up your favourite Python development environment, create a new file called cards.py, and type/paste (probably some judicious combination of the two is best here) the following code, and then run it.

import sqlite3


con = sqlite3.connect("cards.db")
cur = con.cursor()

cur.execute("""
CREATE TABLE IF NOT EXISTS cards (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    suit TEXT,
    suit_symbol TEXT,
    score INTEGER
);""")

cur.execute("""
INSERT INTO cards (name, suit, suit_symbol, score) VALUES

    -- Spades
    ('A', 'spades', '♠', 1), 
    ('2', 'spades', '♠', 2), 
    ('3', 'spades', '♠', 3), 
    ('4', 'spades', '♠', 4), 
    ('5', 'spades', '♠', 5), 
    ('6', 'spades', '♠', 6), 
    ('7', 'spades', '♠', 7), 
    ('8', 'spades', '♠', 8), 
    ('9', 'spades', '♠', 9), 
    ('10', 'spades', '♠', 10), 
    ('J', 'spades', '♠', 11), 
    ('Q', 'spades', '♠', 12), 
    ('K', 'spades', '♠', 13), 

    -- Hearts
    ('A', 'hearts', '♥', 1), 
    ('2', 'hearts', '♥', 2), 
    ('3', 'hearts', '♥', 3), 
    ('4', 'hearts', '♥', 4), 
    ('5', 'hearts', '♥', 5), 
    ('6', 'hearts', '♥', 6), 
    ('7', 'hearts', '♥', 7), 
    ('8', 'hearts', '♥', 8), 
    ('9', 'hearts', '♥', 9), 
    ('10', 'hearts', '♥', 10), 
    ('J', 'hearts', '♥', 11), 
    ('Q', 'hearts', '♥', 12), 
    ('K', 'hearts', '♥', 13), 

    -- Clubs
    ('A', 'clubs', '♣', 1), 
    ('2', 'clubs', '♣', 2), 
    ('3', 'clubs', '♣', 3), 
    ('4', 'clubs', '♣', 4), 
    ('5', 'clubs', '♣', 5), 
    ('6', 'clubs', '♣', 6), 
    ('7', 'clubs', '♣', 7), 
    ('8', 'clubs', '♣', 8), 
    ('9', 'clubs', '♣', 9), 
    ('10', 'clubs', '♣', 10), 
    ('J', 'clubs', '♣', 11), 
    ('Q', 'clubs', '♣', 12), 
    ('K', 'clubs', '♣', 13), 

    -- Diamonds
    ('A', 'diamonds', '♦', 1), 
    ('2', 'diamonds', '♦', 2), 
    ('3', 'diamonds', '♦', 3), 
    ('4', 'diamonds', '♦', 4), 
    ('5', 'diamonds', '♦', 5), 
    ('6', 'diamonds', '♦', 6), 
    ('7', 'diamonds', '♦', 7), 
    ('8', 'diamonds', '♦', 8), 
    ('9', 'diamonds', '♦', 9), 
    ('10', 'diamonds', '♦', 10), 
    ('J', 'diamonds', '♦', 11), 
    ('Q', 'diamonds', '♦', 12), 
    ('K', 'diamonds', '♦', 13);""")

cur.execute("SELECT * FROM cards WHERE suit = 'hearts'")
print(cur.fetchone())  # Print just the first row
print()
print(cur.fetchall())  # Print all rows

con.commit()
con.close()

Output:

(14, 'A', 'hearts', '♥', 1)

[(15, '2', 'hearts', '♥', 2), (16, '3', 'hearts', '♥', 3), (17, '4', 'hearts', '♥', 4), (18, '5', 'hearts', '♥', 5), (19, '6', 'hearts', '♥', 6), (20, '7', 'hearts', '♥', 7), (21, '8', 'hearts', '♥', 8), (22, '9', 'hearts', '♥', 9), (23, '10', 'hearts', '♥', 10), (24, 'J', 'hearts', '♥', 11), (25, 'Q', 'hearts', '♥', 12), (26, 'K', 'hearts', '♥', 13), (66, 'A', 'hearts', '♥', 1), (67, '2', 'hearts', '♥', 2), (68, '3', 'hearts', '♥', 3), (69, '4', 'hearts', '♥', 4), (70, '5', 'hearts', '♥', 5), (71, '6', 'hearts', '♥', 6), (72, '7', 'hearts', '♥', 7), (73, '8', 'hearts', '♥', 8), (74, '9', 'hearts', '♥', 9), (75, '10', 'hearts', '♥', 10), (76, 'J', 'hearts', '♥', 11), (77, 'Q', 'hearts', '♥', 12), (78, 'K', 'hearts', '♥', 13), (118, 'A', 'hearts', '♥', 1), (119, '2', 'hearts', '♥', 2), (120, '3', 'hearts', '♥', 3), (121, '4', 'hearts', '♥', 4), (122, '5', 'hearts', '♥', 5), (123, '6', 'hearts', '♥', 6), (124, '7', 'hearts', '♥', 7), (125, '8', 'hearts', '♥', 8), (126, '9', 'hearts', '♥', 9), (127, '10', 'hearts', '♥', 10), (128, 'J', 'hearts', '♥', 11), (129, 'Q', 'hearts', '♥', 12), (130, 'K', 'hearts', '♥', 13), (170, 'A', 'hearts', '♥', 1), (171, '2', 'hearts', '♥', 2), (172, '3', 'hearts', '♥', 3), (173, '4', 'hearts', '♥', 4), (174, '5', 'hearts', '♥', 5), (175, '6', 'hearts', '♥', 6), (176, '7', 'hearts', '♥', 7), (177, '8', 'hearts', '♥', 8), (178, '9', 'hearts', '♥', 9), (179, '10', 'hearts', '♥', 10), (180, 'J', 'hearts', '♥', 11), (181, 'Q', 'hearts', '♥', 12), (182, 'K', 'hearts', '♥', 13)]

A few notes about the above code:

• con = sqlite3.connect("cards.db") creates the database in the current folder, if it doesn’t already exist, and established a connection.
• cur = con.cursor() creates a cursor, which can be compared to a read/write head on a CD reader/writer
• Multi-line comments (""" """) are used to enter blocks of SQL
• cur.fetchone() and cur.fetchall() are very useful commands to retrieve your data from a previous SELECT statement
• con.commit() commits any changes to your data. Often this is omitted as there is a default auto-commit option.
• We tidy up after ourselves by closing the connection to the database with con.close().

This has been an introduction to SQLite and how to use it with Python. There is more to learn, but we have covered some of the essential ingredients. If you have followed along, you should now be in good shape to do basic operations with SQLite and Python, and to take your studies of working with databases in Python to the next level. Look out for the next article in this series.

Happy computing!

Sharing is caring!