07-02-2022, 10:03 AM
How to use a List as an SQLite Parameter in Python
<div><div class="kk-star-ratings kksr-valign-top kksr-align-left " data-payload="{"align":"left","id":"439312","slug":"default","valign":"top","reference":"auto","count":"1","readonly":"","score":"5","best":"5","gap":"5","greet":"Rate this post","legend":"5\/5 - (1 vote)","size":"24","width":"142.5","_legend":"{score}\/{best} - ({count} {votes})"}">
<div class="kksr-stars">
<div class="kksr-stars-inactive">
<div class="kksr-star" data-star="1" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="2" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="3" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="4" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="5" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
</p></div>
<div class="kksr-stars-active" style="width: 142.5px;">
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
</p></div>
</div>
<div class="kksr-legend"> 5/5 – (1 vote) </div>
</div>
<h2 class="wp-embed-aspect-16-9 wp-has-aspect-ratio">Problem Formulation and Solution Overview</h2>
<p><em>This article works with the fictitious Finxter database to retrieve three (3) specific users, via a SQLite query using the IN command. </em></p>
<p><em>To follow along, <a rel="noreferrer noopener" href="https://drive.google.com/file/d/1Gf1bJfuUNdbupYM06QU1zSezTZinxvp0/view?usp=sharing" data-type="URL" data-id="https://drive.google.com/file/d/1Gf1bJfuUNdbupYM06QU1zSezTZinxvp0/view?usp=sharing" target="_blank">click </a><a href="https://drive.google.com/file/d/1Gf1bJfuUNdbupYM06QU1zSezTZinxvp0/view?usp=sharing" data-type="URL" data-id="https://drive.google.com/file/d/1Gf1bJfuUNdbupYM06QU1zSezTZinxvp0/view?usp=sharing" target="_blank" rel="noreferrer noopener">here </a>to download this file and move it into the current working directory.</em></p>
<hr class="wp-block-separator has-css-opacity" />
<h2>Preparation</h2>
<p>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>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import sqlite3</pre>
<p class="has-global-color-8-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f4a1.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /><strong>Note:</strong> The <a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank">S</a><a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank">Q</a><a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank">L</a><a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank">ite</a> library is built into Python and does not need to be installed but must be referenced.</p>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Overview</h2>
<p>The <code>Finxter</code> database file contains 25 records in <code><a href="https://blog.finxter.com/the-ultimate-guide-to-python-tuples/" data-type="URL" data-id="https://blog.finxter.com/the-ultimate-guide-to-python-tuples/">tuple</a></code> format. Below is a snippet from this file.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code>(30022145, 'Steve', 'Hamilton', 'Authority')<br />(30022192, 'Amy', 'Pullister', 'Beginner')<br />(30022331, 'Peter', 'Dunn', 'Basic Knowledge')<br />(30022345, 'Marcus', 'Williams', 'Experienced Learner')<br />(30022359, 'Alice', 'Miller', 'Authority')<br />(30022361, 'Craig', 'Driver', 'Autodidact')<br />...</code></td>
</tr>
</tbody>
</table>
</figure>
<p>The structure of the <code>users </code>table is as follows:</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><strong>DATA TYPE</strong></td>
<td><strong>FIELD NAME</strong></td>
</tr>
<tr>
<td>INTEGER</td>
<td>FID</td>
</tr>
<tr>
<td>TEXT</td>
<td>First_Name</td>
</tr>
<tr>
<td>TEXT</td>
<td>Last_Name</td>
</tr>
<tr>
<td>TEXT</td>
<td>Rank</td>
</tr>
</tbody>
</table>
</figure>
<p>Now that the overview is complete, let’s connect to the database, filter, and output the results.</p>
<hr class="wp-block-separator has-css-opacity" />
<h2>Connect to a SQLite Database </h2>
<p class="has-global-color-8-background-color has-background">This code connects to an SQLite database and is placed inside a <a rel="noreferrer noopener" href="https://blog.finxter.com/how-to-catch-and-print-exception-messages-in-python/" data-type="URL" data-id="https://blog.finxter.com/how-to-catch-and-print-exception-messages-in-python/" target="_blank">try/except</a> statement to catch any possible errors.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="2-3" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">try: conn = sqlite3.connect('finxter_users.db') cur = conn.cursor() except Exception as e: print(f'An error occurred: {e}.') exit()</pre>
<p>The code inside the <code>try</code> statement executes first and attempts to connect to <code>finxter_users.db</code>. A <em>Connection Object</em> (<code>conn</code>), similar to below, is produced, if successful.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code><sqlite3.Connection object at 0x00000194FFBC2140></code></td>
</tr>
</tbody>
</table>
</figure>
<p>Next, the <em>Connection Object</em> created above (<code>conn</code>) is used in conjunction with the <code>cursor()</code> to create a <em>Cursor Object</em>. A Cursor Object (<code>cur</code>), similar to below, is produced, if successful.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code><sqlite3.Cursor object at 0x0000022750E5CCC0></code></td>
</tr>
</tbody>
</table>
</figure>
<p class="has-global-color-8-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f4a1.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /><strong>Note: </strong>The <em>Cursor Object</em> allows interaction with database specifics, such as executing queries.</p>
<p>If the above line(s) fail, the code falls inside <code>except</code> capturing the error (<code>e</code>) and outputs this to the terminal. Code execution halts.</p>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Prepare the SQLite Query</h2>
<p class="has-global-color-8-background-color has-background">Before executing any query, you must decide the expected results and how to achieve this.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="5-7" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">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>
<p>In this example, the three (3) highlighted lines create, configure and save the following variables:</p>
<ul>
<li><code><strong>fid_list</strong></code>: this contains a <a rel="noreferrer noopener" href="https://blog.finxter.com/python-lists/" data-type="URL" data-id="https://blog.finxter.com/python-lists/" target="_blank"><code>list</code></a> of the selected <em>Users’</em> <em>FIDs </em>to retrieve.</li>
<li><code><strong>fid_tuple</strong></code>: this converts <code>fid_list </code>into a <code><a rel="noreferrer noopener" href="https://blog.finxter.com/the-ultimate-guide-to-python-tuples/" data-type="URL" data-id="https://blog.finxter.com/the-ultimate-guide-to-python-tuples/" target="_blank">tuple</a></code> format. This is done to match the database format (see above).</li>
<li><code><strong>f_query</strong></code>: this constructs an SQLite query that returns all matching records when executed.</li>
</ul>
<p><strong>Query String Output</strong></p>
<p>If <code>f_query</code> was output to the terminal (<code>print(<code>f_query</code>)</code>), the following would display. Perfect! That’s exactly what we want.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code> SELECT * FROM users WHERE FID IN (30022192, 30022450, 30022475)</code></td>
</tr>
</tbody>
</table>
</figure>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Executing the SQLite Query</h2>
<p class="has-global-color-8-background-color has-background">Let’s execute the query created above and save the results.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="8" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">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()</pre>
<p>The highlighted line appends the <a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank"><code>execute()</code></a> method to the <em>Cursor Objec</em>t and passes the <code>f_query</code> string as an argument.</p>
<p>If the execution was successful, an <a rel="noreferrer noopener" href="https://blog.finxter.com/iterators-iterables-and-itertools/" data-type="URL" data-id="https://blog.finxter.com/iterators-iterables-and-itertools/" target="_blank">iterable</a> <em>Cursor Object</em> is produced, similar to below.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code> <sqlite3.Cursor object at 0x00000224FF987A40></code></td>
</tr>
</tbody>
</table>
</figure>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Displaying the Query Results</h2>
<p class="has-global-color-8-background-color has-background">The standard way to display the query results is by using a <a rel="noreferrer noopener" href="https://blog.finxter.com/python-loops/" data-type="URL" data-id="https://blog.finxter.com/python-loops/" target="_blank"><code>for</code></a> a loop.<br />We could add this loop inside/outside the<a rel="noreferrer noopener" href="https://blog.finxter.com/how-to-catch-and-print-exception-messages-in-python/" data-type="URL" data-id="https://blog.finxter.com/how-to-catch-and-print-exception-messages-in-python/" target="_blank"> try/except</a> statement.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="13-15" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">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()</pre>
<p>The highlighted lines instantiate a for loop to navigate the query results one record at a time and output them to the terminal.</p>
<p><strong>Query Results</strong></p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code>(30022192, 'Amy', 'Pullister', 'Beginner')<br />(30022450, 'Leon', 'Garcia', 'Authority')<br />(30022475, 'Isla', 'Jackson', 'Scholar')</code></td>
</tr>
</tbody>
</table>
</figure>
<p>Finally, the <em>Connection Object</em> created earlier needs to be closed.</p>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Summary</h2>
<p>In this article you learned how to:</p>
<ul>
<li>Create a Connection Object.</li>
<li>Create a Cursor Object.</li>
<li>Construct and Execute a SQLite Query.</li>
<li>Output the results to the terminal.</li>
</ul>
<p>We hope you enjoyed this article.</p>
<p>Happy Coding!</p>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Programmer Humor</h2>
<p class="has-global-color-8-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f471-200d-2640-fe0f.png" alt="?♀️" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Programmer 1</strong>: We have a problem<br /><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f9d4-200d-2642-fe0f.png" alt="?♂️" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Programmer 2</strong>: Let’s use RegEx!<br /><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f471-200d-2640-fe0f.png" alt="?♀️" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Programmer 1</strong>: Now we have two problems</p>
<p>… yet – you can easily reduce the two problems to zero as you polish your “<a rel="noreferrer noopener" href="https://blog.finxter.com/python-regex/" data-type="post" data-id="6210" target="_blank">RegEx Superpower in Python</a>“. <img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f642.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /></p>
</div>
https://www.sickgaming.net/blog/2022/06/...in-python/
<div><div class="kk-star-ratings kksr-valign-top kksr-align-left " data-payload="{"align":"left","id":"439312","slug":"default","valign":"top","reference":"auto","count":"1","readonly":"","score":"5","best":"5","gap":"5","greet":"Rate this post","legend":"5\/5 - (1 vote)","size":"24","width":"142.5","_legend":"{score}\/{best} - ({count} {votes})"}">
<div class="kksr-stars">
<div class="kksr-stars-inactive">
<div class="kksr-star" data-star="1" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="2" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="3" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="4" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" data-star="5" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
</p></div>
<div class="kksr-stars-active" style="width: 142.5px;">
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
<div class="kksr-star" style="padding-right: 5px">
<div class="kksr-icon" style="width: 24px; height: 24px;"></div>
</p></div>
</p></div>
</div>
<div class="kksr-legend"> 5/5 – (1 vote) </div>
</div>
<h2 class="wp-embed-aspect-16-9 wp-has-aspect-ratio">Problem Formulation and Solution Overview</h2>
<p><em>This article works with the fictitious Finxter database to retrieve three (3) specific users, via a SQLite query using the IN command. </em></p>
<p><em>To follow along, <a rel="noreferrer noopener" href="https://drive.google.com/file/d/1Gf1bJfuUNdbupYM06QU1zSezTZinxvp0/view?usp=sharing" data-type="URL" data-id="https://drive.google.com/file/d/1Gf1bJfuUNdbupYM06QU1zSezTZinxvp0/view?usp=sharing" target="_blank">click </a><a href="https://drive.google.com/file/d/1Gf1bJfuUNdbupYM06QU1zSezTZinxvp0/view?usp=sharing" data-type="URL" data-id="https://drive.google.com/file/d/1Gf1bJfuUNdbupYM06QU1zSezTZinxvp0/view?usp=sharing" target="_blank" rel="noreferrer noopener">here </a>to download this file and move it into the current working directory.</em></p>
<hr class="wp-block-separator has-css-opacity" />
<h2>Preparation</h2>
<p>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>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import sqlite3</pre>
<p class="has-global-color-8-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f4a1.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /><strong>Note:</strong> The <a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank">S</a><a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank">Q</a><a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank">L</a><a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank">ite</a> library is built into Python and does not need to be installed but must be referenced.</p>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Overview</h2>
<p>The <code>Finxter</code> database file contains 25 records in <code><a href="https://blog.finxter.com/the-ultimate-guide-to-python-tuples/" data-type="URL" data-id="https://blog.finxter.com/the-ultimate-guide-to-python-tuples/">tuple</a></code> format. Below is a snippet from this file.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code>(30022145, 'Steve', 'Hamilton', 'Authority')<br />(30022192, 'Amy', 'Pullister', 'Beginner')<br />(30022331, 'Peter', 'Dunn', 'Basic Knowledge')<br />(30022345, 'Marcus', 'Williams', 'Experienced Learner')<br />(30022359, 'Alice', 'Miller', 'Authority')<br />(30022361, 'Craig', 'Driver', 'Autodidact')<br />...</code></td>
</tr>
</tbody>
</table>
</figure>
<p>The structure of the <code>users </code>table is as follows:</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><strong>DATA TYPE</strong></td>
<td><strong>FIELD NAME</strong></td>
</tr>
<tr>
<td>INTEGER</td>
<td>FID</td>
</tr>
<tr>
<td>TEXT</td>
<td>First_Name</td>
</tr>
<tr>
<td>TEXT</td>
<td>Last_Name</td>
</tr>
<tr>
<td>TEXT</td>
<td>Rank</td>
</tr>
</tbody>
</table>
</figure>
<p>Now that the overview is complete, let’s connect to the database, filter, and output the results.</p>
<hr class="wp-block-separator has-css-opacity" />
<h2>Connect to a SQLite Database </h2>
<p class="has-global-color-8-background-color has-background">This code connects to an SQLite database and is placed inside a <a rel="noreferrer noopener" href="https://blog.finxter.com/how-to-catch-and-print-exception-messages-in-python/" data-type="URL" data-id="https://blog.finxter.com/how-to-catch-and-print-exception-messages-in-python/" target="_blank">try/except</a> statement to catch any possible errors.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="2-3" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">try: conn = sqlite3.connect('finxter_users.db') cur = conn.cursor() except Exception as e: print(f'An error occurred: {e}.') exit()</pre>
<p>The code inside the <code>try</code> statement executes first and attempts to connect to <code>finxter_users.db</code>. A <em>Connection Object</em> (<code>conn</code>), similar to below, is produced, if successful.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code><sqlite3.Connection object at 0x00000194FFBC2140></code></td>
</tr>
</tbody>
</table>
</figure>
<p>Next, the <em>Connection Object</em> created above (<code>conn</code>) is used in conjunction with the <code>cursor()</code> to create a <em>Cursor Object</em>. A Cursor Object (<code>cur</code>), similar to below, is produced, if successful.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code><sqlite3.Cursor object at 0x0000022750E5CCC0></code></td>
</tr>
</tbody>
</table>
</figure>
<p class="has-global-color-8-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f4a1.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /><strong>Note: </strong>The <em>Cursor Object</em> allows interaction with database specifics, such as executing queries.</p>
<p>If the above line(s) fail, the code falls inside <code>except</code> capturing the error (<code>e</code>) and outputs this to the terminal. Code execution halts.</p>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Prepare the SQLite Query</h2>
<p class="has-global-color-8-background-color has-background">Before executing any query, you must decide the expected results and how to achieve this.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="5-7" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">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>
<p>In this example, the three (3) highlighted lines create, configure and save the following variables:</p>
<ul>
<li><code><strong>fid_list</strong></code>: this contains a <a rel="noreferrer noopener" href="https://blog.finxter.com/python-lists/" data-type="URL" data-id="https://blog.finxter.com/python-lists/" target="_blank"><code>list</code></a> of the selected <em>Users’</em> <em>FIDs </em>to retrieve.</li>
<li><code><strong>fid_tuple</strong></code>: this converts <code>fid_list </code>into a <code><a rel="noreferrer noopener" href="https://blog.finxter.com/the-ultimate-guide-to-python-tuples/" data-type="URL" data-id="https://blog.finxter.com/the-ultimate-guide-to-python-tuples/" target="_blank">tuple</a></code> format. This is done to match the database format (see above).</li>
<li><code><strong>f_query</strong></code>: this constructs an SQLite query that returns all matching records when executed.</li>
</ul>
<p><strong>Query String Output</strong></p>
<p>If <code>f_query</code> was output to the terminal (<code>print(<code>f_query</code>)</code>), the following would display. Perfect! That’s exactly what we want.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code> SELECT * FROM users WHERE FID IN (30022192, 30022450, 30022475)</code></td>
</tr>
</tbody>
</table>
</figure>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Executing the SQLite Query</h2>
<p class="has-global-color-8-background-color has-background">Let’s execute the query created above and save the results.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="8" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">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()</pre>
<p>The highlighted line appends the <a rel="noreferrer noopener" href="https://docs.python.org/3/library/sqlite3.html" data-type="URL" data-id="https://docs.python.org/3/library/sqlite3.html" target="_blank"><code>execute()</code></a> method to the <em>Cursor Objec</em>t and passes the <code>f_query</code> string as an argument.</p>
<p>If the execution was successful, an <a rel="noreferrer noopener" href="https://blog.finxter.com/iterators-iterables-and-itertools/" data-type="URL" data-id="https://blog.finxter.com/iterators-iterables-and-itertools/" target="_blank">iterable</a> <em>Cursor Object</em> is produced, similar to below.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code> <sqlite3.Cursor object at 0x00000224FF987A40></code></td>
</tr>
</tbody>
</table>
</figure>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Displaying the Query Results</h2>
<p class="has-global-color-8-background-color has-background">The standard way to display the query results is by using a <a rel="noreferrer noopener" href="https://blog.finxter.com/python-loops/" data-type="URL" data-id="https://blog.finxter.com/python-loops/" target="_blank"><code>for</code></a> a loop.<br />We could add this loop inside/outside the<a rel="noreferrer noopener" href="https://blog.finxter.com/how-to-catch-and-print-exception-messages-in-python/" data-type="URL" data-id="https://blog.finxter.com/how-to-catch-and-print-exception-messages-in-python/" target="_blank"> try/except</a> statement.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="13-15" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">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()</pre>
<p>The highlighted lines instantiate a for loop to navigate the query results one record at a time and output them to the terminal.</p>
<p><strong>Query Results</strong></p>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td><code>(30022192, 'Amy', 'Pullister', 'Beginner')<br />(30022450, 'Leon', 'Garcia', 'Authority')<br />(30022475, 'Isla', 'Jackson', 'Scholar')</code></td>
</tr>
</tbody>
</table>
</figure>
<p>Finally, the <em>Connection Object</em> created earlier needs to be closed.</p>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Summary</h2>
<p>In this article you learned how to:</p>
<ul>
<li>Create a Connection Object.</li>
<li>Create a Cursor Object.</li>
<li>Construct and Execute a SQLite Query.</li>
<li>Output the results to the terminal.</li>
</ul>
<p>We hope you enjoyed this article.</p>
<p>Happy Coding!</p>
<hr class="wp-block-separator has-alpha-channel-opacity" />
<h2>Programmer Humor</h2>
<p class="has-global-color-8-background-color has-background"><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f471-200d-2640-fe0f.png" alt="?♀️" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Programmer 1</strong>: We have a problem<br /><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f9d4-200d-2642-fe0f.png" alt="?♂️" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Programmer 2</strong>: Let’s use RegEx!<br /><img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f471-200d-2640-fe0f.png" alt="?♀️" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Programmer 1</strong>: Now we have two problems</p>
<p>… yet – you can easily reduce the two problems to zero as you polish your “<a rel="noreferrer noopener" href="https://blog.finxter.com/python-regex/" data-type="post" data-id="6210" target="_blank">RegEx Superpower in Python</a>“. <img src="https://s.w.org/images/core/emoji/14.0.0/72x72/1f642.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /></p>
</div>
https://www.sickgaming.net/blog/2022/06/...in-python/