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

[xSicKxBot]

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, clickhereto 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.

Code:

import sqlite3

Note: The SQLite library is built into Python and does not need to be installed but must be referenced.

Overview

The

Code:

Finxter

database file contains 25 records in

Code:

tuple

format. Below is a snippet from this file.

Code:

(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

Code:

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.

Code:

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

Code:

try

statement executes first and attempts to connect to

Code:

finxter_users.db

. A Connection Object (

Code:

conn

), similar to below, is produced, if successful.

Code:

<sqlite3.Connection object at 0x00000194FFBC2140>

Next, the Connection Object created above (

Code:

conn

) is used in conjunction with the

Code:

cursor()

to create a Cursor Object. A Cursor Object (

Code:

cur

), similar to below, is produced, if successful.

Code:

<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

Code:

except

capturing the error (

Code:

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.

Code:

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:

• Code:

  fid_list
  : this contains a
  Code:

  list
  of the selected Users’ FIDs to retrieve.
  
  
• Code:

  fid_tuple
  : this converts
  Code:

  fid_list
  into a
  Code:

  tuple
  format. This is done to match the database format (see above).
  
  
• Code:

  f_query
  : this constructs an SQLite query that returns all matching records when executed.
  

Query String Output

If

Code:

f_query

was output to the terminal (

Code:

print(f_query)

), the following would display. Perfect! That’s exactly what we want.

Code:

SELECT * FROM users WHERE FID IN (30022192, 30022450, 30022475)

Executing the SQLite Query

Let’s execute the query created above and save the results.

Code:

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

Code:

execute()

method to the Cursor Object and passes the

Code:

f_query

string as an argument.

If the execution was successful, an iterable Cursor Object is produced, similar to below.

Code:

<sqlite3.Cursor object at 0x00000224FF987A40>

Displaying the Query Results

The standard way to display the query results is by using a

Code:

for

a loop.
We could add this loop inside/outside thetry/except statement.

Code:

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

Code:

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