Python Database Programming: SQLite and MySQL Basics

In Python development, database operations are essential, especially relational databases such as SQLite and MySQL, which are widely used for data storage, web development, and data analysis. This article introduces basic operations using Python for SQLite and MySQL, including connecting to databases, executing SQL statements, and transaction management.

1. SQLite Database Operations

SQLite is a lightweight embedded database that runs without a server, making it suitable for small applications and local development. Python provides the built‑in sqlite3 module for direct SQLite access.

1.1 Connect and Create Table

import sqlite3

# Connect to database (creates file if not exists)
conn = sqlite3.connect('example.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
    )
''')
conn.commit()
conn.close()

1.2 Insert Data

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Insert a single row
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
               ('Alice', 25, '[email protected]'))

# Insert multiple rows
users_data = [
    ('Bob', 30, '[email protected]'),
    ('Charlie', 22, '[email protected]')
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users_data)

conn.commit()
conn.close()

1.3 Query Data

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Query all rows
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Conditional query
cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
filtered_rows = cursor.fetchall()
print(filtered_rows)

conn.close()

1.4 Update & Delete Data

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

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

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

conn.commit()
conn.close()

2. MySQL Database Operations

MySQL is a widely used relational database management system suitable for medium to large applications. Python typically accesses MySQL via mysql-connector-python or pymysql libraries.

2.1 Install MySQL Driver

pip install mysql-connector-python
# or
pip install pymysql

2.2 Connect to MySQL

import mysql.connector

# Connect to MySQL
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="testdb"
)
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        age INT,
        email VARCHAR(100) UNIQUE
    )
''')
conn.commit()

2.3 Insert, Query, Update, Delete (similar to SQLite)

# Insert
cursor.execute("INSERT INTO users (name, age, email) VALUES (%s, %s, %s)",
               ('David', 28, '[email protected]'))

# Query
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Update
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (29, 'David'))

# Delete
cursor.execute("DELETE FROM users WHERE name = %s", ('Charlie',))

conn.commit()
conn.close()

3. Transaction Management

try:
    conn = sqlite3.connect('example.db')  # or MySQL connection
    cursor = conn.cursor()

    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Eve', 35))
    cursor.execute("UPDATE users SET age = ? WHERE name = ?", (36, 'Eve'))

    conn.commit()
except Exception as e:
    print("Error occurred, rolling back:", e)
    conn.rollback()
finally:
    conn.close()

4. Summary

Operation

SQLite (

sqlite3

)

MySQL (

mysql-connector

/

pymysql

)

Connection method

sqlite3.connect()

mysql.connector.connect()

Parameter placeholder

?

(question mark)

%s

(percent s)

Typical scenario

Small apps, local development

Medium‑large apps, web back‑ends

SQLite: lightweight, no extra installation, ideal for local development and small projects.

MySQL: powerful, supports high concurrency, suitable for enterprise‑level applications.

Mastering Python database programming enables efficient data storage and management, a fundamental skill for web development and data analysis.