[Tut] How to use a List as an SQLite Parameter in Python

How to use a List as an SQLite Parameter in Python

5/5 – (1 vote)

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') (30022192, 'Amy', 'Pullister', 'Beginner') (30022331, 'Peter', 'Dunn', 'Basic Knowledge') (30022345, 'Marcus', 'Williams', 'Experienced Learner') (30022359, 'Alice', 'Miller', 'Authority') (30022361, 'Craig', 'Driver', 'Autodidact') ...

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 a list of the selected Users’ FIDs to retrieve.
• fid_tuple: this converts fid_list into a tuple 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(f_query)), the following would display. Perfect! That’s exactly what we want.

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®

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') (30022450, 'Leon', 'Garcia', 'Authority') (30022475, 'Isla', 'Jackson', 'Scholar')

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“.

https://www.sickgaming.net/blog/2022/06/...in-python/