Getting Started with SQLite in Python: A Beginner’s Guide

Certainly! Here’s a detailed, step-by-step guide on Getting Started with SQLite in Python aimed at beginners. This guide will help you understand how to set up SQLite, interact with databases, and perform basic operations using Python.

What is SQLite?

SQLite is a lightweight, serverless, self-contained SQL database engine. Unlike other database systems, SQLite doesn’t require a separate server and stores the database as a single file, making it perfect for small to medium applications, prototyping, or learning SQL.

Step 1: Ensure Python is Installed

SQLite support is built into Python’s standard library (via the sqlite3 module), so you do not need to install SQLite separately if you already have Python installed.

• To check if Python is installed, open your terminal (Command Prompt on Windows, Terminal on macOS/Linux) and run:

bash
python –version

• If Python is not installed, download and install it from python.org.

Step 2: Create or Open an SQLite Database Using Python

Create a Python file, e.g., sqlite_intro.py, and write the following code to create a connection to a new or existing SQLite database.

python
import sqlite3

conn = sqlite3.connect(‘my_database.db’)

cursor = conn.cursor()

print("Database connection successful!")

• Running this script will create a file my_database.db in your working directory if it does not already exist.

Step 3: Create a Table

Let’s create a simple table to store user data.

python
cursor.execute(”’
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
email TEXT UNIQUE NOT NULL
)
”’)

print("Table created successfully!")

conn.commit()

• Explanation:

  • IF NOT EXISTS prevents errors if the table already exists.
  • The id column is an auto-incrementing primary key.
  • Email is marked UNIQUE to prevent duplicate email entries.

Step 4: Insert Data into the Table

You can add user entries using the INSERT statement.

python
cursor.execute(”’
INSERT INTO users (name, age, email) VALUES (?, ?, ?)
”’, ("Alice", 30, "alice@example.com"))

conn.commit()

print("Inserted data successfully!")

• The ? placeholders are a way to prevent SQL injection and handle data safely.

Step 5: Insert Multiple Rows (Optional)

You can insert multiple records at once using executemany():

python
users_to_insert = [
("Bob", 25, "bob@example.com"),
("Charlie", 35, "charlie@example.com"),
]

cursor.executemany(”’
INSERT INTO users (name, age, email) VALUES (?, ?, ?)
”’, users_to_insert)

conn.commit()

print("Multiple users added successfully!")

Step 6: Query Data from the Table

Retrieve and view data with a SELECT query.

python
cursor.execute(‘SELECT * FROM users’)

rows = cursor.fetchall() # Fetch all results

for row in rows:
print(row)

• fetchall() returns a list of tuples, each representing a row.

Step 7: Update Data in the Table

Change existing records, for example, update Alice’s age.

python
cursor.execute(”’
UPDATE users SET age = ? WHERE name = ?
”’, (31, "Alice"))

conn.commit()

print("User updated successfully!")

Step 8: Delete Data from the Table

Remove a user from the table.

python
cursor.execute(”’
DELETE FROM users WHERE name = ?
”’, ("Bob",))

conn.commit()

print("User deleted successfully!")

Step 9: Close the Database Connection

Always close your connection when done.

python
conn.close()
print("Connection closed.")

python
import sqlite3

def main():

conn = sqlite3.connect('my_database.db')

cursor = conn.cursor()
# Create table

cursor.execute('''

CREATE TABLE IF NOT EXISTS users (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL,

    age INTEGER,

    email TEXT UNIQUE NOT NULL

)

''')
# Insert data

cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', ("Alice", 30, "alice@example.com"))

users_to_insert = [

    ("Bob", 25, "bob@example.com"),

    ("Charlie", 35, "charlie@example.com"),

]

cursor.executemany('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', users_to_insert)
conn.commit()
# Query data

cursor.execute('SELECT * FROM users')

rows = cursor.fetchall()

for row in rows:

    print(row)
# Update data

cursor.execute('UPDATE users SET age = ? WHERE name = ?', (31, "Alice"))

conn.commit()
# Delete a user

cursor.execute('DELETE FROM users WHERE name = ?', ("Bob",))

conn.commit()
# Close connection

conn.close()

if name == "main":
main()

1. ModuleNotFoundError: No module named ‘sqlite3’

• Ensure you are using Python 2.5+ (sqlite3 is included in standard library since Python 2.5).
• If using a very old Python version, upgrade Python.
• Using a virtual environment is recommended; make sure it has access to standard libraries.

2. Database File Not Found or Permissions Issues

• SQLite creates a database file automatically if it does not exist, but you need write permissions in the directory.
• Make sure the working directory isn’t read-only.
• Provide a full absolute path when connecting if you want the database created in a specific location.

Example:

python
conn = sqlite3.connect(‘/full/path/to/my_database.db’)

3. Data Types & Constraints

• SQLite is dynamically typed but declared types help in constraint enforcement.
• Type mismatches usually don’t cause errors but check your database schema for data consistency.

• You can use GUI tools like DB Browser for SQLite to visually inspect and manage your SQLite database files.

• Python’s built-in sqlite3 module makes working with SQLite easy.
• Steps to get started: connect → create table → insert data → query data → update/delete → close.
• Use parameterized queries (? placeholders) to prevent SQL injection.
• Commit transactions after write operations.
• Close connections properly.

If you want me to include how to handle advanced topics such as transactions, error handling, or ORM usage with SQLite (like using SQLAlchemy), just ask!