{"id":126099,"date":"2022-06-29T16:28:54","date_gmt":"2022-06-29T16:28:54","guid":{"rendered":"https:\/\/blog.finxter.com\/?p=439312"},"modified":"2022-06-29T16:28:54","modified_gmt":"2022-06-29T16:28:54","slug":"how-to-use-a-list-as-an-sqlite-parameter-in-python","status":"publish","type":"post","link":"https:\/\/sickgaming.net\/blog\/2022\/06\/29\/how-to-use-a-list-as-an-sqlite-parameter-in-python\/","title":{"rendered":"How to use a List as an SQLite Parameter in Python"},"content":{"rendered":"
\n
\n
\n
\n
<\/div>\n<\/p><\/div>\n
\n
<\/div>\n<\/p><\/div>\n
\n
<\/div>\n<\/p><\/div>\n
\n
<\/div>\n<\/p><\/div>\n
\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n
\n
\n
<\/div>\n<\/p><\/div>\n
\n
<\/div>\n<\/p><\/div>\n
\n
<\/div>\n<\/p><\/div>\n
\n
<\/div>\n<\/p><\/div>\n
\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/div>\n
5\/5 – (1 vote) <\/div>\n<\/div>\n

Problem Formulation and Solution Overview<\/h2>\n

This article works with the fictitious Finxter database to retrieve three (3) specific users, via a SQLite query using the IN command. <\/em><\/p>\n

To follow along, click <\/a>here <\/a>to download this file and move it into the current working directory.<\/em><\/p>\n

\n

Preparation<\/h2>\n

Add the following code to the top of the code snippet. This snippet will allow the code in this article to run error-free.<\/p>\n

import sqlite3<\/pre>\n
\"\ud83d\udca1\"Note:<\/strong> The S<\/a>Q<\/a>L<\/a>ite<\/a> library is built into Python and does not need to be installed but must be referenced.<\/p>\n
\n
Overview<\/h2>\n
The Finxter<\/code> database file contains 25 records in tuple<\/a><\/code> format. Below is a snippet from this file.<\/p>\n
\n\n\n\n
(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')
...<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
The structure of the users <\/code>table is as follows:<\/p>\n
\n\n\n\n\n\n\n\n
DATA TYPE<\/strong><\/td>\nFIELD NAME<\/strong><\/td>\n<\/tr>\n
INTEGER<\/td>\nFID<\/td>\n<\/tr>\n
TEXT<\/td>\nFirst_Name<\/td>\n<\/tr>\n
TEXT<\/td>\nLast_Name<\/td>\n<\/tr>\n
TEXT<\/td>\nRank<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
Now that the overview is complete, let’s connect to the database, filter, and output the results.<\/p>\n
\n
Connect to a SQLite Database <\/h2>\n
This code connects to an SQLite database and is placed inside a try\/except<\/a> statement to catch any possible errors.<\/p>\n
try: conn = sqlite3.connect('finxter_users.db') cur = conn.cursor() except Exception as e: print(f'An error occurred: {e}.') exit()<\/pre>\n
The code inside the try<\/code> statement executes first and attempts to connect to finxter_users.db<\/code>. A Connection Object<\/em> (conn<\/code>), similar to below, is produced, if successful.<\/p>\n
\n\n\n\n
<sqlite3.Connection object at 0x00000194FFBC2140><\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
Next, the Connection Object<\/em> created above (conn<\/code>) is used in conjunction with the cursor()<\/code> to create a Cursor Object<\/em>. A Cursor Object (cur<\/code>), similar to below, is produced, if successful.<\/p>\n
\n\n\n\n
<sqlite3.Cursor object at 0x0000022750E5CCC0><\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
\"\ud83d\udca1\"Note: <\/strong>The Cursor Object<\/em> allows interaction with database specifics, such as executing queries.<\/p>\n
If the above line(s) fail, the code falls inside except<\/code> capturing the error (e<\/code>) and outputs this to the terminal. Code execution halts.<\/p>\n
\n
Prepare the SQLite Query<\/h2>\n
Before executing any query, you must decide the expected results and how to achieve this.<\/p>\n
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()<\/pre>\n
In this example, the three (3) highlighted lines create, configure and save the following variables:<\/p>\n