Scofield Idehen

Posted on Oct 9, 2023 • Originally published at blog.learnhub.africa

SQLite with Python

#python #programming #beginners #database

SQLite is a lightweight, serverless, self-contained relational database management system commonly used in many applications due to its simplicity, efficiency, and compact footprint.

This comprehensive guide will explore how to fully leverage SQLite in Python projects to create, access, modify, and manage database information in-depth.

Setting Up SQLite in Python

To work with SQLite in Python, we first need to import the sqlite3 module. This comes built-in with Python:

import sqlite3

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:

import sqlite3
db = sqlite3.connect('database.db')

This opens a connection to the database file database.db and returns a connection object representing 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:

db.close()

Creating Tables

To create a table in SQLite, we must execute a CREATE TABLE SQL statement. This is done by first obtaining a cursor object from the database connection, which enables us to execute SQL:

import sqlite3
db = sqlite3.connect('database.db')
cursor = db.cursor()

We can then execute a CREATE TABLE statement with the cursor's execute() method:

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:

db.commit()

Let's put this together into a function to encapsulate the table creation logic:

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.

Inserting Data

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:

INSERT INTO inventory VALUES (1, 'Bananas', 150, 1.49)

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 query parameters that safely separate values from the query:

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:

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.

Viewing Data

We can use the SELECT statement to query and fetch data from the database. For example, to get all rows:

SELECT * FROM inventory

We can execute this query and then use cursor.fetchall() to retrieve the result set as tuples:

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:

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.

Updating Data

To modify existing data, we can use an UPDATE statement:

UPDATE inventory SET quantity = 200 WHERE name = 'Bananas'

This updates the quantity of Bananas to 200.

We can write a function to handle the update logic:

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.

Deleting Data

Removing a row can be done with a DELETE statement:

DELETE FROM inventory WHERE name = 'Oranges'

This would delete the orange row.

Here is how we can implement a delete function:

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.

Building with Sqlite3

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:

import sqlite3

This gives us access to all the SQLite database functionality in Python.

Creating a Table

Next, a create_table() function is defined to create a table called store with columns for item, quantity, and price:

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:

sqlite3.connect() opens a connection to the database file.
new_data.cursor() gets a cursor object to execute SQL.
execute() runs the CREATE TABLE query.
commit() saves the changes.
close() closes the connection.

Inserting Data

The item() function handles inserting data into the table:

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.

Viewing Data

To query the data, view() function is defined:

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.

Deleting Data

To delete a row by item name, the delete() function is used:

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.

Updating Data

Finally, update() can modify quantity and price for a given item:

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.

Wrapping Up

In summary, the key steps for working with SQLite databases in Python are:

Import the sqlite3 module into your project.
Create a database connection with sqlite3.connect().
Obtain a cursor from the connection to execute SQL statements.
Construct SQL queries and use the cursor to execute them.
For inserts, updates, and deletes - commit changes to persist them.
For queries, use cursor.fetchone() or cursor.fetchall() to retrieve results.
Close database connections when done.
Use query parameters instead of concatenating values to avoid SQL injection.
Encapsulate database logic and queries in functions for reusability.

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 cup of coffee.

If you find this post exciting, find more exciting posts on Learnhub Blog; we write everything tech from Cloud computing to Frontend Dev, Cybersecurity, AI, and Blockchain.