Working with Databases: Python and SQL

Working with SQL databases from Python. SQLite for local files and MySQL for bigger setups.

Why databases matter

Databases store, retrieve, and update data at scale. SQL (Structured Query Language) is the standard way to talk to relational databases – create records, read them back, update them, and delete them.

Python and databases

Python has several libraries for SQL work. The two you will see most often are sqlite3 (built in) and mysql-connector-python.

Working with SQLite

SQLite is a lightweight, file-based database. No separate server process – just a .db file on disk. The sqlite3 module gives you an interface to create and manage it.

Creating a database in SQLite

import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('mydatabase.db')

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

# Commit the transaction
conn.commit()

# Close the connection
conn.close()

Creating a table

Once connected, define tables to hold your data:

cursor.execute('''CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')

Inserting data

cursor.execute('''INSERT INTO users (name, age) VALUES ('John Doe', 28)''')

Querying data

Use SELECT to read rows back:

cursor.execute('''SELECT * FROM users''')
print(cursor.fetchall())

Updating and deleting data

# Updating records
cursor.execute('''UPDATE users SET age = 29 WHERE name = 'John Doe' ''')

# Deleting records
cursor.execute('''DELETE FROM users WHERE name = 'John Doe' ''')

Using MySQL with Python

For larger or multi-user setups, MySQL is a common choice. Install the connector first:

pip install mysql-connector-python

Then connect:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    passwd="yourpassword",
    database="mydatabase"
)

cursor = db.cursor()

Performing SQL operations

Creating tables, inserting, querying, updating, and deleting work the same way as SQLite – just with MySQL syntax and connection details.

Best practices

1. Use parameterized queries to prevent SQL injection:

   cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Jane Doe", 25))

2. Close connections when you are done – open connections can lock the database or leave transactions hanging.
3. Handle errors with try/except around database calls.
4. Normalise your schema to cut redundant data and keep things consistent.

SQLite suits prototypes and small apps. MySQL (or PostgreSQL) suits production. Either way, learn the SQL first – the Python side is mostly connect, cursor, execute, fetch.