Connect SQLite with Python

Connecting SQLite with Python is relatively easy since SQLite is an integral part of Python standard library. Below is step-by-step explanation of how to connect SQLite with Python and do some basic operations.

Step 1: Import the sqlite3 Module

SQLite support in Python is provided through the sqlite3 module. You need to import this module to use SQLite functionalities.

import sqlite3

Step 2: Connect to a Database

Use the sqlite3.connect() method to create a connection to an SQLite database file. If the file does not exist, SQLite will automatically create it.

# Connect to the database (or create it if it doesn't exist)
connection = sqlite3.connect('example.db')

# For in-memory database (temporary, disappears when program ends)
# connection = sqlite3.connect(':memory:')

Step 3: Create a Cursor Object

A cursor is used to execute SQL commands. Create a cursor object using the connection.cursor() method.

# Create a cursor object
cursor = connection.cursor()

Step 4: Create a Table

You can create a table using an SQL CREATE TABLE statement executed through the cursor.

# Create a table (if it doesn't already exist)
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
)
''')

Step 5: Insert Data

To insert data into a table, use the INSERT INTO SQL command. You can insert data dynamically by passing parameters.

# Insert data into the table
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 25))
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 30))

For multiple records, use executemany():

# Insert multiple records
data = [('Charlie', 35), ('Diana', 28)]
cursor.executemany('INSERT INTO users (name, age) VALUES (?, ?)', data)

Step 6: Retrieve Data

To fetch data from the database, use SELECT queries. The fetchall() or fetchone() methods can retrieve data.

# Retrieve all data
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

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

Step 7: Update or Delete Data

You can update or delete records using the UPDATE or DELETE SQL commands.

# Update a record
cursor.execute('UPDATE users SET age = ? WHERE name = ?', (26, 'Alice'))

# Delete a record
cursor.execute('DELETE FROM users WHERE name = ?', ('Bob',))

Step 8: Commit Changes

After executing commands that modify the database (like INSERT, UPDATE, or DELETE), you need to commit the changes using connection.commit().

# Commit the changes
connection.commit()

Step 9: Close the Connection

Always close the connection when you’re done to free up resources.

# Close the connection
connection.close()

Full Example Code

Here’s a complete example putting everything together:

import sqlite3

# Step 1: Connect to the database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

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

# Step 3: Insert data
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 25))
cursor.executemany('INSERT INTO users (name, age) VALUES (?, ?)', [('Bob', 30), ('Charlie', 35)])

# Step 4: Fetch and display data
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Step 5: Update data
cursor.execute('UPDATE users SET age = ? WHERE name = ?', (26, 'Alice'))

# Step 6: Delete data
cursor.execute('DELETE FROM users WHERE name = ?', ('Bob',))

# Commit and close
connection.commit()
connection.close()

Key Points to Remember

• Parameterized Queries: Always use placeholders (?) in queries to prevent SQL injection.
• Error Handling: Use try-except blocks to handle potential errors (e.g., file permission issues or syntax errors).
• Database File: SQLite stores all of this in a single file- example.db.
• Concurrency: SQLite allows multiple connections but has limitations on concurrent writes.