SQLite with Python
Tags: python, programming, beginners, database
Published: 2023-10-09 05:11:36

<p data-pm-slice="1 1 []">SQLite is a lightweight, serverless, self-contained relational database management system commonly used in many applications due to its simplicity, efficiency, and compact footprint.</p>
This comprehensive guide will explore how to fully leverage SQLite in Python projects to create, access, modify, and manage database information in-depth.
<h2>Setting Up SQLite in Python</h2>
To work with SQLite in Python, we first need to import the sqlite3 module. This comes built-in with Python:

<code>import sqlite3</code>

The sqlite3 module provides all the APIs and tools necessary to connect to, create, and interact with an SQLite database from Python.

To connect to an SQLite database (or create one if it doesn't exist), use the sqlite3.connect() method:
```python
import sqlite3
db = sqlite3.connect('database.db') 
```
This opens a connection to the database file database.db and returns a <em>connection object representing</em> the database. If the file does not exist, SQLite will automatically create it.

It's good practice to close the connection once we are done interacting with the database. This can be done with the close() method:

<code>db.close()</code>
<h2>Creating Tables</h2>
To create a table in SQLite, we must execute a CREATE TABLE SQL statement. This is done by first obtaining a <em>cursor object</em> from the database connection, which enables us to execute SQL:
```python
import sqlite3
db = sqlite3.connect('database.db')
cursor = db.cursor()
```
We can then execute a CREATE TABLE statement with the cursor's execute() method:
```python
cursor.execute("""
  CREATE TABLE inventory (
    id integer PRIMARY KEY,
    name text NOT NULL,
    quantity integer,
    price real
  )
""")
```
This creates an inventory table with ID, name, quantity, and price columns. Note that we define the data type for each column.

It's essential to commit changes to the database after executing SQL. This persists the changes:

<code>db.commit()</code>

Let's put this together into a function to encapsulate the table creation logic:
```python
def create_table():
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("""
    CREATE TABLE IF NOT EXISTS inventory (
      id integer PRIMARY KEY,
      name text NOT NULL,
      quantity integer,
      price real
    )
  """)
  db.commit()
  db.close()
```
We use CREATE TABLE IF NOT EXISTS, so attempting to create the table again won't result in an error if it already exists.
<h2>Inserting Data</h2>
To insert data into a table, we can again execute an SQL statement using the execute() method. SQLite has an INSERT INTO statement that allows inserting new rows:

<code>INSERT INTO inventory VALUES (1, 'Bananas', 150, 1.49)</code>

This would insert a new row with id 1, name Bananas, quantity 150, and price 1.49.

While we could insert data using string formatting, a better approach is to use <em>query parameters</em> that safely separate values from the query:
```python
data = (2, 'Oranges', 200, 2.49)
cursor.execute("INSERT INTO inventory VALUES (?, ?, ?, ?)", data)
```
They? act as placeholders for values. This helps prevent SQL injection attacks.

We can write a function to insert data:
```python
def insert_data(values):
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("INSERT INTO inventory VALUES (?, ?, ?, ?)", values)
  db.commit()
  db.close()
# Usage:
insert_data((3, 'Apples', 75, 1.25)) 
```
This encapsulates the insertion logic while allowing us to pass in data as a tuple cleanly.
<h2>Viewing Data</h2>
We can use the SELECT statement to query and fetch data from the database. For example, to get all rows:

<code>SELECT * FROM inventory</code>

We can execute this query and then use cursor.fetchall() to retrieve the result set as tuples:
```python
def get_all_data():
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("SELECT * FROM inventory")
  rows = cursor.fetchall()
  db.close()
  return rows
```
This will return the full result set as a list of tuples, with each tuple representing a row and its values.

We can pass parameters to a SELECT query to filter results:
```python
def get_by_name(name):
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("SELECT * FROM inventory WHERE name=?", (name,))
  row = cursor.fetchone()  
  db.close()
  return row
```
This allows us to retrieve a specific row by name. cursor.fetchone() returns just the first result.
<h2>Updating Data</h2>
To modify existing data, we can use an UPDATE statement:

<code>UPDATE inventory SET quantity = 200 WHERE name = 'Bananas'</code>

This updates the quantity of Bananas to 200.

We can write a function to handle the update logic:
```python
def update_data(new_quantity, name):
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("UPDATE inventory SET quantity =? WHERE name=?", 
                 (new_quantity, name))
  db.commit()
  db.close()
# Usage:  
update_data(350, 'Bananas')
```
Similar to insert, this uses query parameters to pass in values safely.
<h2>Deleting Data</h2>
Removing a row can be done with a DELETE statement:

<code>DELETE FROM inventory WHERE name = 'Oranges' </code>

This would delete the orange row.

Here is how we can implement a delete function:
```python
def delete_data(name):
  db = sqlite3.connect('database.db')
  cursor = db.cursor()
  cursor.execute("DELETE FROM inventory WHERE name=?", (name,))
  db.commit() 
  db.close()
# Usage:
delete_data('Oranges')
```
Again, using query parameters avoids vulnerabilities and makes the code easier to maintain.
<h2>Building with <strong>Sqlite3</strong></h2>
To get started, clone the repo here and follow the tutorial. You must have a little knowledge of SQL to follow through, although this is a beginner course.

The first step is importing the sqlite3 module:

<code>import sqlite3</code>

This gives us access to all the SQLite database functionality in Python.
<h2><strong>Creating a Table</strong></h2>
Next, a create_table() function is defined to create a table called store with columns for item, quantity, and price:
```python
def create_table():
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("CREATE TABLE IF NOT EXISTS store (item TEXT, quantity INTEGER, price REAL)")
  new_data.commit()
  new_data.close()
```
Breaking this down:
<ul>
 	<li>sqlite3.connect() opens a connection to the database file.</li>
 	<li>new_data.cursor() gets a cursor object to execute SQL.</li>
 	<li>execute() runs the CREATE TABLE query.</li>
 	<li>commit() saves the changes.</li>
 	<li>close() closes the connection.</li>
</ul>
<h2><strong>Inserting Data</strong></h2>
The item() function handles inserting data into the table:
```python
def item(item, quantity, price):
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("INSERT INTO store VALUES(?,?,?)", (item, quantity, price))
  new_data.commit()
  new_data.close()

```
It takes an item, quantity, and price as arguments. The INSERT query uses placeholders ? to safely insert those values into a new row.
<h2><strong>Viewing Data</strong></h2>
To query the data, view() function is defined:
```python
def view():
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("SELECT * FROM store")
  rows = change.fetchall()
  change.close()
  return rows
```
It selects all rows using SELECT *, fetches the results with fetchall(), and returns them.
<h2><strong>Deleting Data</strong></h2>
To delete a row by item name, the delete() function is used:
```python
def delete(item):
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("DELETE FROM store WHERE item =?", (item,))  
  new_data.commit()
  new_data.close()
```
The WHERE clause identifies the row to delete by the item name.
<h2><strong>Updating Data</strong></h2>
Finally, update() can modify quantity and price for a given item:
```python
def update(quantity, price, item):
  new_data = sqlite3.connect('data_base1')
  change = new_data.cursor()
  change.execute("UPDATE store SET quantity =?, price =? WHERE item=?", 
                (quantity, price, item))
  new_data.commit()
  new_data.close()
```
The SET clause specifies the columns to update, and WHERE identifies the row.
<h2>Wrapping Up</h2>
In summary, the key steps for working with SQLite databases in Python are:
<ol>
 	<li>Import the sqlite3 module into your project.</li>
 	<li>Create a database connection with sqlite3.connect().</li>
 	<li>Obtain a cursor from the connection to execute SQL statements.</li>
 	<li>Construct SQL queries and use the cursor to execute them.</li>
 	<li>For inserts, updates, and deletes - commit changes to persist them.</li>
 	<li>For queries, use cursor.fetchone() or cursor.fetchall() to retrieve results.</li>
 	<li>Close database connections when done.</li>
 	<li>Use query parameters instead of concatenating values to avoid SQL injection.</li>
 	<li>Encapsulate database logic and queries in functions for reusability.</li>
</ol>
With these concepts, you can build robust applications utilizing the power of SQLite and Python. The SQLite3 module and SQL give you full control to create, modify, and query database information.

SQLite supports more advanced features like transactions, isolation levels, backups, and encryption. Make sure to refer to the official SQLite documentation to leverage the full functionality.

I hope this comprehensive guide gives you a thorough understanding of integrating SQLite into your Python projects! Let me know if you have any other questions.

I hope you enjoyed reading this guide and feel motivated to start your Python programming journey.

If you like my work and want to help me continue dropping content like this, buy me a <a href="https://www.buymeacoffee.com/scofields1s" target="_blank" rel="noopener noreferrer nofollow">cup of coffee</a>.

If you find this post exciting, find more exciting posts on <a class="attrlink" href="http://blog.learnhub.africa/" target="_blank" rel="noopener noreferrer nofollow">Learnhub Blog</a>; we write everything tech from <a class="attrlink" href="https://blog.learnhub.africa/category/cloud-computing/" target="_blank" rel="noopener noreferrer nofollow">Cloud computing</a> to <a class="attrlink" href="https://blog.learnhub.africa/category/frontend/" target="_blank" rel="noopener noreferrer nofollow">Frontend Dev</a>, <a class="attrlink" href="https://blog.learnhub.africa/category/security/" target="_blank" rel="noopener noreferrer nofollow">Cybersecurity</a>, <a class="attrlink" href="https://blog.learnhub.africa/category/data-science/" target="_blank" rel="noopener noreferrer nofollow">AI</a>, and <a class="attrlink" href="https://blog.learnhub.africa/category/blockchain/" target="_blank" rel="noopener noreferrer nofollow">Blockchain</a>.
<h2><strong>Resource</strong></h2>
<ul>
 	<li><a class="attrlink" href="https://realpython.com/lessons/python-folium-get-started/" target="_blank" rel="noopener noreferrer nofollow">Getting started with Folium</a></li>
 	<li><a class="attrlink" href="https://blog.learnhub.africa/2023/05/26/20-essential-python-extensions-for-visual-studio-code/" target="_blank" rel="noopener noreferrer nofollow">20 Essential Python Extensions for Visual Studio Code</a></li>
 	<li><a class="attrlink" href="https://blog.learnhub.africa/2023/04/27/using-python-for-web-scraping-and-data-extraction/" target="_blank" rel="noopener noreferrer nofollow">Using Python for Web Scraping and Data Extraction</a></li>
 	<li><a class="attrlink" href="https://blog.learnhub.africa/2023/06/06/13-powerful-python-snippets-to-automate-tasks/" target="_blank" rel="noopener noreferrer nofollow">13 Powerful Python Snippets To Automate Tasks</a></li>
 	<li><a class="attrlink" href="https://www.python.org/about/gettingstarted/" target="_blank" rel="noopener noreferrer nofollow">Getting Started with Python</a></li>
</ul>