How to Connect a Database to Python: SQLite & JSON Guide

Database Connection Basics in Python

Working with databases is a crucial part of any modern application. Storing, retrieving, and managing data allows you to build full-featured programs, from simple scripts to complex web services.

Python offers many ways to work with databases. In this article, we'll dive deep into connecting to a database using the sqlite3 module. We'll also cover using JSON format for data persistence with json.dump. You'll learn the key aspects of working with both technologies.

Why You Need Databases in Your Projects

If your programs handle data that needs to persist between runs, a database is essential. Python's sqlite3 module provides a reliable solution for storing information right inside your application.

Benefits of Using Databases

Using databases in Python projects gives you these advantages:

• Easy storage of structured data • Fast access and filtering • Centralized data management • Data integrity enforcement • Simple backup creation • Scalable solutions

Working with SQLite Database in Python

SQLite is an ideal choice for small to medium projects. It's embedded directly into your application, requires no separate server, and works with simple .db files.

Connecting to a SQLite Database

The sqlite3 module is part of Python's standard library, so no extra installation is needed.

import sqlite3

# Connect to database (creates the file if it doesn't exist)
conn = sqlite3.connect('example.db')

# Create a cursor to execute SQL queries
cursor = conn.cursor()

When you connect to a non-existent database, the file is created automatically. This makes it easy to start new projects.

Creating a Database Table

Before adding data, you need to define the table structure. Use the CREATE TABLE SQL command:

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
''')
conn.commit()

The IF NOT EXISTS clause prevents errors if the table already exists. PRIMARY KEY AUTOINCREMENT automatically assigns unique IDs to each new record.

Inserting Data into the Table

Use the INSERT INTO command to add data. Always use parameterized queries to protect against SQL injection attacks:

cursor.execute('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', ('Alice', 30))

# Insert multiple records at once
users_data = [
    ('Bob', 25),
    ('Carol', 35),
    ('David', 40)
]

cursor.executemany('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', users_data)

conn.commit()

The executemany method lets you efficiently add multiple records in a single operation.

Reading and Retrieving Data

Different methods are available for fetching data, depending on how many records you need:

# Get all records
cursor.execute('SELECT * FROM users')
all_rows = cursor.fetchall()
for row in all_rows:
    print(row)

# Get a single record
cursor.execute('SELECT * FROM users WHERE age > ?', (30,))
one_row = cursor.fetchone()
print(one_row)

# Get a limited number of records
cursor.execute('SELECT name, age FROM users ORDER BY age')
some_rows = cursor.fetchmany(2)
for row in some_rows:
    print(f"Name: {row[0]}, Age: {row[1]}")

Updating and Deleting Data

Modify existing records using the UPDATE and DELETE commands: