Python SQLite: Basics of Working with SQLite Databases

Last update on August 31 2024 17:21:20 (UTC/GMT +8 hours)

Introduction to Python SQLite

SQLite is a lightweight, disk-based database that doesn't require a separate server process. Python comes with built-in support for SQLite through the sqlite3 module, which allows us to create, manipulate, and query SQLite databases easily.

Following tutorial provides a foundational understanding of SQLite in Python, which is useful for developing lightweight, database-driven applications.

Example 1: Connecting to an SQLite Database

This example demonstrates how to connect to an SQLite database. If the database does not exist, it will be created.

Code:

import sqlite3
# Connect to an SQLite database (or create it if it doesn't exist)
connection = sqlite3.connect('sql_data.db')  # Creates or opens a file named 'sql_data.db'

# Create a cursor object to interact with the database
cursor = connection.cursor()

# Close the connection
connection.close()

Explanation:

Connection: 'sqlite3.connect('example.db')' opens a connection to the SQLite database file named 'example.db'. If the file does not exist, it creates a new one.
Cursor: The 'cursor' object is used to execute SQL queries and retrieve data.
Closing the Connection: 'connection.close()' closes the connection to the database, which is a good practice to free up resources.

Example 2: Creating a Table

This example shows how to create a table named 'users' in the SQLite database.

Code:

import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()

# Create a table named 'users'
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    age INTEGER NOT NULL
                )''')

# Commit the changes to save them
connection.commit()

# Close the connection
connection.close()

Explanation:

SQL Command: The 'CREATE TABLE SQL' command is used to create a new table named users with three columns: 'id', 'name', and 'age'.
IF NOT EXISTS: Ensures the table is only created if it does not already exist, preventing errors.
Commit: 'connection.commit()' saves the changes made to the database.

Example 3: Inserting Data into a Table

This example demonstrates how to insert data into the 'users' table.

Code:

import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()

# Insert data into the 'users' table
cursor.execute("INSERT INTO users (name, age) VALUES ('Martyn Shyam', 30)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Praveen Juliet', 25)")
# Commit the changes to save them
connection.commit()
# Close the connection
connection.close()

Explanation:

INSERT Statement: The ‘INSERT INTO’ SQL statement is used to insert new rows of data into the ‘users’ table.
Values: Data values are directly provided in the SQL query for simplicity.
Multiple Inserts: Multiple ‘execute’ calls can be used to insert several rows.

Example 4: Querying Data from a Table

This example shows how to retrieve data from the 'users' table.

Code:

import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()

# Query data from the 'users' table
cursor.execute("SELECT * FROM users")

# Fetch all rows from the executed query
rows = cursor.fetchall()

# Print the results
for row in rows:
    print(row)

# Close the connection
connection.close()

Output:

(1, 'Martyn Shyam', 30)
(2, 'Praveen Juliet', 25)

Explanation:

SELECT Statement: The 'SELECT * FROM users' SQL command retrieves all columns ('*') from the 'users' table.
Fetching Data: 'cursor.fetchall()' fetches all the results of the executed query as a list of tuples.
Iteration: A 'for' loop is used to print each row retrieved from the table.

Example 5: Updating Data in a Table

This example demonstrates how to update existing data in the 'users' table.

Code:

import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()

# Update data in the 'users' table
cursor.execute("UPDATE users SET age = 34 WHERE name = 'Praveen Juliet'")

# Commit the changes to save them
connection.commit()

# Close the connection
connection.close()

Explanation:

UPDATE Statement: The 'UPDATE' SQL command modifies the data in the table. It changes the age of the user named "Praveen Juliet" to 34.
WHERE Clause: The ‘WHERE’ clause specifies which rows should be updated.

Example 6: Deleting Data from a Table

This example shows how to delete data from the 'users' table.

Code:

import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()

# Delete data from the 'users' table
cursor.execute("DELETE FROM users WHERE name = 'Praveen Juliet'")

# Commit the changes to save them
connection.commit()

# Close the connection
connection.close()

Explanation:

DELETE Statement: The 'DELETE' SQL command removes rows from the table. Here, it deletes the row where the name is "Praveen Juliet".
WHERE Clause: Ensures only the specified rows are deleted.

Example 7: Using Parameters to Prevent SQL Injection

This example shows how to use parameters to prevent SQL injection attacks.

Code:

import sqlite3
# Connect to the SQLite database
connection = sqlite3.connect('sql_data.db')
cursor = connection.cursor()

# Insert data using parameters to prevent SQL injection
name = "Gurutz Irene"
age = 40
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))

# Commit the changes to save them
connection.commit()

# Close the connection
connection.close()

Explanation:

Parameters: Use placeholders ('?') and pass data as a tuple to safely insert data into the table.
SQL Injection Prevention: This method prevents malicious input from executing harmful SQL commands.