Connecting Python to Databases

Databases are essential for storing and retrieving data in web applications. Python provides libraries like sqlite3, SQLAlchemy, and PyMySQL to connect and interact with databases.

In this lesson, you’ll learn how to connect Python to a database and perform basic CRUD (Create, Read, Update, Delete) operations.

Using SQLite with Python

SQLite is a lightweight, file-based database that comes bundled with Python. It’s ideal for small applications or testing purposes.

# Connecting to SQLite
import sqlite3

conn = sqlite3.connect('example.db')  # Creates or connects to a database file
print("Connected to SQLite")

Creating a Table

To create a table, use the CREATE TABLE SQL statement:

# Creating a table
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
conn.commit()
print("Table created")

Inserting Data

You can insert data into a table using the INSERT INTO SQL statement:

# Inserting data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))
conn.commit()
print("Data inserted")

Reading Data

You can retrieve data from a table using the SELECT SQL statement:

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

Updating Data

You can update existing records using the UPDATE SQL statement:

# Updating data
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (30, "Alice"))
conn.commit()
print("Data updated")

Deleting Data

To delete records, use the DELETE SQL statement:

# Deleting data
cursor.execute("DELETE FROM users WHERE name = ?", ("Alice",))
conn.commit()
print("Data deleted")

Using SQLAlchemy

SQLAlchemy is a powerful library for database interactions in Python. It provides an Object Relational Mapper (ORM) for working with databases more easily.

# Installing SQLAlchemy
# pip install sqlalchemy

from sqlalchemy import create_engine
engine = create_engine('sqlite:///example.db')
print("Connected to SQLite with SQLAlchemy")

Common Mistakes with Databases

Here are some mistakes to avoid:

• Forgetting to commit changes after executing write operations.
• Not using parameterized queries to prevent SQL injection.
• Hardcoding database connection strings in code.

What Did We Learn?

In this lesson, you learned:

• How to connect Python to a SQLite database.
• How to create, read, update, and delete records using SQL.
• How to use SQLAlchemy for database interactions.

Great job! Let’s move to the next topic.