Problem Formulation and Solution Overview
This article works with the fictitious Finxter database to retrieve three (3) specific users, via a SQLite query using the IN command.
To follow along, click here to download this file and move it into the current working directory.
Preparation
Add the following code to the top of the code snippet. This snippet will allow the code in this article to run error-free.
import sqlite3
π‘Note: The SQLite library is built into Python and does not need to be installed but must be referenced.
Overview
The Finxter
database file contains 25 records in tuple
format. Below is a snippet from this file.
(30022145, 'Steve', 'Hamilton', 'Authority') |
The structure of the users
table is as follows:
DATA TYPE | FIELD NAME |
INTEGER | FID |
TEXT | First_Name |
TEXT | Last_Name |
TEXT | Rank |
Now that the overview is complete, let’s connect to the database, filter, and output the results.
Connect to a SQLite Database
This code connects to an SQLite database and is placed inside a try/except statement to catch any possible errors.
try: conn = sqlite3.connect('finxter_users.db') cur = conn.cursor() except Exception as e: print(f'An error occurred: {e}.') exit()
The code inside the try
statement executes first and attempts to connect to finxter_users.db
. A Connection Object (conn
), similar to below, is produced, if successful.
<sqlite3.Connection object at 0x00000194FFBC2140> |
Next, the Connection Object created above (conn
) is used in conjunction with the cursor()
to create a Cursor Object. A Cursor Object (cur
), similar to below, is produced, if successful.
<sqlite3.Cursor object at 0x0000022750E5CCC0> |
π‘Note: The Cursor Object allows interaction with database specifics, such as executing queries.
If the above line(s) fail, the code falls inside except
capturing the error (e
) and outputs this to the terminal. Code execution halts.
Prepare the SQLite Query
Before executing any query, you must decide the expected results and how to achieve this.
try: conn = sqlite3.connect('finxter_users.db') cur = conn.cursor() fid_list = [30022192, 30022450, 30022475] fid_tuple = tuple(fid_list) f_query = f'SELECT * FROM users WHERE FID IN {format(fid_tuple)}' except Exception as e: print(f'An error occurred: {e}.') exit()
In this example, the three (3) highlighted lines create, configure and save the following variables:
fid_list
: this contains alist
of the selected Users’ FIDs to retrieve.fid_tuple
: this convertsfid_list
into atuple
format. This is done to match the database format (see above).f_query
: this constructs an SQLite query that returns all matching records when executed.
Query String Output
If f_query
was output to the terminal (print(
), the following would display. Perfect! That’s exactly what we want.f_query
)
SELECT * FROM users WHERE FID IN (30022192, 30022450, 30022475) |
Executing the SQLite Query
Let’s execute the query created above and save the results.
try: conn = sqlite3.connect('finxter_users.db') cur = conn.cursor() fid_list = [30022192, 30022450, 30022475] fid_tuple = tuple(fid_list) f_query = f'SELECT * FROM users WHERE FID IN {format(fid_tuple)}' results = cur.execute(f_query) except Exception as e: print(f'An error occurred: {e}.') exit()
The highlighted line appends the execute()
method to the Cursor Object and passes the f_query
string as an argument.
If the execution was successful, an iterable Cursor Object is produced, similar to below.
<sqlite3.Cursor object at 0x00000224FF987A40> |
Displaying the Query Results
The standard way to display the query results is by using a for
a loop.
We could add this loop inside/outside the try/except statement.
try: conn = sqlite3.connect('finxter_users.db') cur = conn.cursor() fid_list = [30022192, 30022450, 30022475] fid_tuple = tuple(fid_list) f_query = f'SELECT * FROM users WHERE FID IN {format(fid_tuple)}' results = cur.execute(f_query) except Exception as e: print(f'An error occurred: {e}.') exit() for r in results: print(r) conn.close()
The highlighted lines instantiate a for loop to navigate the query results one record at a time and output them to the terminal.
Query Results
(30022192, 'Amy', 'Pullister', 'Beginner') |
Finally, the Connection Object created earlier needs to be closed.
Summary
In this article you learned how to:
- Create a Connection Object.
- Create a Cursor Object.
- Construct and Execute a SQLite Query.
- Output the results to the terminal.
We hope you enjoyed this article.
Happy Coding!
Programmer Humor
π±ββοΈ Programmer 1: We have a problem
π§ββοΈ Programmer 2: Letβs use RegEx!
π±ββοΈ Programmer 1: Now we have two problems
… yet – you can easily reduce the two problems to zero as you polish your “RegEx Superpower in Python“. π