Create an account


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[-]
Welcome
You have to register before you can post on our site.

Username:


Password:





[-]
Discord Widget

[-]
Twitter: SickGaming

[-]
Latest Threads
[Tut] 3 Simple Steps to Convert calendar...
Last Post: xSicKxBot
Today 12:58 AM
» Replies: 0
» Views: 0
[Tut] Add Google reCaptcha V3 Example wi...
Last Post: xSicKxBot
Today 12:58 AM
» Replies: 0
» Views: 0
(Free Game Key) Cook, Serve, Delicious! ...
Last Post: xSicKxBot
Today 12:58 AM
» Replies: 0
» Views: 0
PC - Frogun
Last Post: xSicKxBot
Today 12:57 AM
» Replies: 0
» Views: 0
[Tut] How to Sort Words Alphabetically i...
Last Post: xSicKxBot
Yesterday 05:51 AM
» Replies: 0
» Views: 0
PC - The Sims 4: High School Years
Last Post: xSicKxBot
Yesterday 05:51 AM
» Replies: 0
» Views: 0
News - Edgar Allan Poe Horror Biopic Rav...
Last Post: xSicKxBot
Yesterday 05:51 AM
» Replies: 0
» Views: 0
[Oracle Blog] Announcing GraalVM Enterpr...
Last Post: xSicKxBot
08-10-2022 12:05 PM
» Replies: 0
» Views: 0
[Tut] Best Solidity Linter
Last Post: xSicKxBot
08-10-2022 12:05 PM
» Replies: 0
» Views: 0
[Tut] JavaScript Autocomplete TextBox (a...
Last Post: xSicKxBot
08-10-2022 12:05 PM
» Replies: 0
» Views: 0

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

#1
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/
Reply





Users browsing this thread:
1 Guest(s)

πš‚Μ·πš’Μ·πšŒΜ·πš”Μ·π™ΆΜ·πšŠΜ·πš–Μ·πš’Μ·πš—Μ·πšΜ· π™·Μ·πš˜Μ·πš–Μ·πšŽΜ·

Copyright Β© SickGaming.net 2022