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.

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")

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")

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")

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)

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")

To delete records, use the DELETE SQL statement:

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

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")

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.

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.