SQL and Python have quickly become quintessential skills for anyone taking on serious data analysis! This Python SQLite tutorial is the only guide you need to get up and running with SQLite in Python. In this post, we’ll cover off: loading the library, creating and connecting to your database, creating database tables, adding data, querying data, deleting data, and so much more!
SQLite3 (what we’ll just call SQLite) is part of the standard Python 3 package, so you won’t need to install anything.
If you’re new to SQL or want a refresher, check out our complete Beginner’s Guide to SQL here and download a ton of free content!
Table of Contents
What You’ll Create
After following this tutorial, you’ll have created a database in SQLite using Python. Specifically, this post will guide you through all the steps to create a database that covers off the following table, including all relationships:
Data Types Available in SQLite for Python
SQLite for Python offers fewer data types than other SQL implementations. This can be a bit restricting. However, as you’ll see, SQLite makes a lot of other things easier. Let’s take a quick look at the data types that are available:
- NULL – Includes a NULL value
- INTEGER – Includes an integer
- REAL – Includes a floating point (decimal) value
- TEXT. – Includes text
- BLOB. – Includes a binary large object that is stored exactly as input
From this list, you may notice a number of missing data types such as dates. Unfortunately, when using SQLite, you’re restricted to these data types.
Getting Started with SQLite in Python
Let’s start off the tutorial by loading in the library. We can do this by using the following command:
import sqlite3Let’s move into actually creating our database.
Creating a SQLite Database in Python
In this section of the Python SQLite tutorial, we’ll explore the different ways in which you can create a database in Python with SQLite. In order to do this, we’ll create a Connection object that will represent the database. This object is created using SQLite’s connect() function.
Let’s first create a .db file, as this is a very standard way of actually maintaining a SQLite database. We’ll represent the connection using a variable named conn. We’ll create a file called orders.db.
conn = sqlite3.connect('orders.db')With this line of code, we’ve created a new connection object, as well as a new file called orders.db in the directory in which you’re working. If you wanted to specify a specific directory, you could write:
conn = sqlite3.connect(r'PATH-TO-YOUR-DIRECTORY/orders.db')If the file already exists, the connect function will simply connect to that file.
Note: Notice that we included the letter “r” before the string that contains this path. This lets Python know that we’re working with a raw string, meaning that the “/” won’t be used to escape characters. You can learn more about raw strings by checking out this link.
The connect function creates a connection to the SQLite database and returns an object to represent it.
In-Memory Databases
Another way of generating databases using SQLite in Python is to create them in memory. This is a great way to generate databases that can be used for testing purposes, as they exist only in RAM.
conn = sqlite3.connect(:memory:)However, for the purposes of this tutorial, and for most use cases you’ll run into, you’ll use the method we described earlier.
Creating a Cursor Object
Now that we’ve created a database connection object, our next task is to create a cursor object. Simply put, a cursor object allows us to execute SQL queries against a database. We’ll create a variable cur to hold our cursor object:
cur = conn.cursor()
Now that we have a cursor object, we can use it to run SQL queries in the following style:
cur.execute("YOUR-SQL-QUERY-HERE;")Notice that we wrapped our SQL query in quotes – this is important. It doesn’t matter if we use single, double, or triple quotes. For longer queries, it’s often best to use triple quotes, as they allow us to write multi-line queries.
Creating our Tables in SQLite for Python
At this point in the Python SQLite tutorial, let’s create our first table using SQLite in Python! Now that we have a connection object (conn) and a cursor object (cur), we can create our first table. Following our database schema that we showed earlier:
We’ll start off with the users table.
cur.execute("""CREATE TABLE IF NOT EXISTS users(
userid INT PRIMARY KEY,
fname TEXT,
lname TEXT,
gender TEXT);
""")
conn.commit()In the code above, we’re doing a number of things:
- Using the execute function on the cursor object to execute a SQL query
- Using SQL to generate a table called users
- The IF NOT EXISTS will help us when reconnecting to the database. The query will allow us to check if the table exists, and if it does, nothing is changed.
- We create four columns: userid, fname, lname, and gender. userid is assigned to be the primary key.
- We committed the changes by using the commit function on the connection object.
To create our other table, we can follow a similar pattern and write the following commands:
cur.execute("""CREATE TABLE IF NOT EXISTS orders(
orderid INT PRIMARY KEY,
date TEXT,
userid TEXT,
total TEXT);
""")
conn.commit()After executing these two scripts, your database will have two tables. We’re now ready to begin adding in data!
Adding Data with SQLite in Python
Let’s take a look at how to add data with SQLite in Python to the database we just created. Similar to the table generation query, the query to add data uses the cursor object to execute the query.
cur.execute("""INSERT INTO users(userid, fname, lname, gender)
VALUES('00001', 'Nik', 'Piepenbreier', 'male');""")
conn.commit()Often, when we’re working within Python, we’ll have variables that hold values for us. For example, we may have a tuple that contains that information about a user which might look like this:
user = ('00002', 'Lois', 'Lane', 'Female')If we wanted to load this data into our database, we would use a different convention:
cur.execute("INSERT INTO users VALUES(?, ?, ?, ?);", user)
conn.commit()What we did here was replace all the values with question marks and add an additional parameters which contains the values we’re hoping to add.
It’s important to note here that the SQLite expects the values to be in tuple-format. However, the variable can contain a list, as long as the list items are tuples. For example, we could add more users using the variable:
more_users = [('00003', 'Peter', 'Parker', 'Male'), ('00004', 'Bruce', 'Wayne', 'male')]In this case, instead of using the execute function, we’ll want to use the executemany function:
cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", more_users)
conn.commit()If we had used the execute function on the cursor object here, the function would have assumed we were passing two items into the table directly (the two tuples), rather than two sets of four items each! Thankfully, the function would have failed in this instance, but be careful about which function you use!
SQLite and Preventing Injection Attacks
Incidentally, using the (?, ?, …) method we noted above also helps protect against SQL injection attacks. Because of this, it’s recommended to use this method over the previously noted method. It’s also easier to type out, so it’s a win-win!
Some Scripts to Load More Data
If you’re following along on your own in the Python SQLite tutorial, let’s load some more data to make the following sections more meaningful. Below are some scripts you can copy and paste to insert some sample data into both tables:
customers = [('00005', 'Stephanie', 'Stewart', 'female'), ('00006', 'Sincere', 'Sherman', 'female'), ('00007', 'Sidney', 'Horn', 'male'), ('00008', 'Litzy', 'Yates', 'female'), ('00009', 'Jaxon', 'Mills', 'male'), ('00010', 'Paul', 'Richard', 'male'), ('00011', 'Kamari', 'Holden', 'female'), ('00012', 'Gaige', 'Summers', 'female'), ('00013', 'Andrea', 'Snow', 'female'), ('00014', 'Angelica', 'Barnes', 'female'), ('00015', 'Leah', 'Pitts', 'female'), ('00016', 'Dillan', 'Olsen', 'male'), ('00017', 'Joe', 'Walsh', 'male'), ('00018', 'Reagan', 'Cooper', 'male'), ('00019', 'Aubree', 'Hogan', 'female'), ('00020', 'Avery', 'Floyd', 'male'), ('00021', 'Elianna', 'Simmons', 'female'), ('00022', 'Rodney', 'Stout', 'male'), ('00023', 'Elaine', 'Mcintosh', 'female'), ('00024', 'Myla', 'Mckenzie', 'female'), ('00025', 'Alijah', 'Horn', 'female'), ('00026', 'Rohan', 'Peterson', 'male'), ('00027', 'Irene', 'Walters', 'female'), ('00028', 'Lilia', 'Sellers', 'female'), ('00029', 'Perla', 'Jefferson', 'female'), ('00030', 'Ashley', 'Klein', 'female')]
orders = [('00001', '2020-01-01', '00025', '178'), ('00002', '2020-01-03', '00025', '39'), ('00003', '2020-01-07', '00016', '153'), ('00004', '2020-01-10', '00015', '110'), ('00005', '2020-01-11', '00024', '219'), ('00006', '2020-01-12', '00029', '37'), ('00007', '2020-01-14', '00028', '227'), ('00008', '2020-01-18', '00010', '232'), ('00009', '2020-01-22', '00016', '236'), ('00010', '2020-01-26', '00017', '116'), ('00011', '2020-01-28', '00028', '221'), ('00012', '2020-01-31', '00021', '238'), ('00013', '2020-02-02', '00015', '177'), ('00014', '2020-02-05', '00025', '76'), ('00015', '2020-02-08', '00022', '245'), ('00016', '2020-02-12', '00008', '180'), ('00017', '2020-02-14', '00020', '190'), ('00018', '2020-02-18', '00030', '166'), ('00019', '2020-02-22', '00002', '168'), ('00020', '2020-02-26', '00021', '174'), ('00021', '2020-02-29', '00017', '126'), ('00022', '2020-03-02', '00019', '211'), ('00023', '2020-03-05', '00030', '144'), ('00024', '2020-03-09', '00012', '112'), ('00025', '2020-03-10', '00006', '45'), ('00026', '2020-03-11', '00004', '200'), ('00027', '2020-03-14', '00015', '226'), ('00028', '2020-03-17', '00030', '189'), ('00029', '2020-03-20', '00004', '152'), ('00030', '2020-03-22', '00026', '239'), ('00031', '2020-03-23', '00012', '135'), ('00032', '2020-03-24', '00013', '211'), ('00033', '2020-03-27', '00030', '226'), ('00034', '2020-03-28', '00007', '173'), ('00035', '2020-03-30', '00010', '144'), ('00036', '2020-04-01', '00017', '185'), ('00037', '2020-04-03', '00009', '95'), ('00038', '2020-04-06', '00009', '138'), ('00039', '2020-04-10', '00025', '223'), ('00040', '2020-04-12', '00019', '118'), ('00041', '2020-04-15', '00024', '132'), ('00042', '2020-04-18', '00008', '238'), ('00043', '2020-04-21', '00003', '50'), ('00044', '2020-04-25', '00019', '98'), ('00045', '2020-04-26', '00017', '167'), ('00046', '2020-04-28', '00009', '215'), ('00047', '2020-05-01', '00014', '142'), ('00048', '2020-05-05', '00022', '173'), ('00049', '2020-05-06', '00015', '80'), ('00050', '2020-05-07', '00017', '37'), ('00051', '2020-05-08', '00002', '36'), ('00052', '2020-05-10', '00022', '65'), ('00053', '2020-05-14', '00019', '110'), ('00054', '2020-05-18', '00017', '36'), ('00055', '2020-05-21', '00008', '163'), ('00056', '2020-05-24', '00024', '91'), ('00057', '2020-05-26', '00028', '154'), ('00058', '2020-05-30', '00022', '130'), ('00059', '2020-05-31', '00017', '119'), ('00060', '2020-06-01', '00024', '137'), ('00061', '2020-06-03', '00017', '206'), ('00062', '2020-06-04', '00013', '100'), ('00063', '2020-06-05', '00021', '187'), ('00064', '2020-06-09', '00025', '170'), ('00065', '2020-06-11', '00011', '149'), ('00066', '2020-06-12', '00007', '195'), ('00067', '2020-06-14', '00015', '30'), ('00068', '2020-06-16', '00002', '246'), ('00069', '2020-06-20', '00028', '163'), ('00070', '2020-06-22', '00005', '184'), ('00071', '2020-06-23', '00022', '68'), ('00072', '2020-06-27', '00013', '92'), ('00073', '2020-06-30', '00022', '149'), ('00074', '2020-07-04', '00002', '65'), ('00075', '2020-07-05', '00017', '88'), ('00076', '2020-07-09', '00007', '156'), ('00077', '2020-07-13', '00010', '26'), ('00078', '2020-07-16', '00008', '55'), ('00079', '2020-07-20', '00019', '81'), ('00080', '2020-07-22', '00011', '78'), ('00081', '2020-07-23', '00026', '166'), ('00082', '2020-07-27', '00014', '65'), ('00083', '2020-07-30', '00021', '205'), ('00084', '2020-08-01', '00026', '140'), ('00085', '2020-08-05', '00006', '236'), ('00086', '2020-08-06', '00021', '208'), ('00087', '2020-08-07', '00021', '169'), ('00088', '2020-08-08', '00004', '157'), ('00089', '2020-08-11', '00017', '71'), ('00090', '2020-08-13', '00025', '89'), ('00091', '2020-08-16', '00014', '249'), ('00092', '2020-08-18', '00012', '59'), ('00093', '2020-08-19', '00013', '121'), ('00094', '2020-08-20', '00025', '179'), ('00095', '2020-08-22', '00017', '208'), ('00096', '2020-08-26', '00024', '217'), ('00097', '2020-08-28', '00004', '206'), ('00098', '2020-08-30', '00017', '114'), ('00099', '2020-08-31', '00017', '169'), ('00100', '2020-09-02', '00022', '226')]You can load this data in by using the following queries:
cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", customers)
cur.executemany("INSERT INTO orders VALUES(?, ?, ?, ?);", orders)
conn.commit()Selecting Data in SQLite with Python
Next in this Python SQLite tutorial , we’ll take a look at how to select data with SQLite in Python! We’ll follow a similar structure as we did to execute queries above, but we’ll add another element to it as well.
Using fetchone() in SQLite with Python
Let’s begin by using the fetchone() function. We create a variable one_result to pull only result
cur.execute("SELECT * FROM users;")
one_result = cur.fetchone()
print(one_result)This returns:
[(1, 'Nik', 'Piepenbreier', 'male')]Using fetchmany() in SQLite with Python
Say we wanted to return more than only one result, we could use the fetchmany() function. Let’s run a different script to generate 3 results:
cur.execute("SELECT * FROM users;")
three_results = cur.fetchmany(3)
print(three_results)This would return the following:
[(1, 'Nik', 'Piepenbreier', 'male'), (2, 'Lois', 'Lane', 'Female'), (3, 'Peter', 'Parker', 'Male')]Using fetchall() in SQLite with Python
Similarly, we could use the fetchall() function to return all the results. If we ran the following, all results would be returned:
cur.execute("SELECT * FROM users;")
all_results = cur.fetchall()
print(all_results)Deleting Data in SQLite with Python
Now, we’ll take a look at how to delete data using SQLite in Python. We can accomplish this using a similar structure to above. Say we wanted to delete any user with the last name ‘Parker’, we could write:
cur.execute("DELETE FROM users WHERE lname='Parker';")
conn.commit()When we then run the query below:
cur.execute("select * from users where lname='Parker'")
print(cur.fetchall())This prints out an empty list, confirming that the record has been deleted.
Joining Tables with SQLite in Python
Finally, let’s take a look at how to join data with a more complex query. Let’s say we wanted to generate a query that includes the first and last name for each customer on each order.
To accomplish this we let’s write the following:
cur.execute("""SELECT *, users.fname, users.lname FROM orders
LEFT JOIN users ON users.userid=orders.userid;""")
print(cur.fetchall())If you’re new to SQL or want a refresher, check out our complete Beginner’s Guide to SQL here and download a ton of free content!
Conclusion: Python SQLite Tutorial
In this Python SQLite tutorial, we explored everything you need to know to get started with SQLite in Python. We started off with how to load the library, explored how to create a database and tables, how to add data, how to query the tables, and how to delete data.
Pingback: Exploring the Pandas Style API • Conditional Formatting and More • datagy