Working with Databases

Python offers robust support for working with various database systems, including lightweight options like SQLite for local development and powerful relational databases like MySQL for production environments. We can interact with databases using Python’s standard library or specialized database libraries.

# Example of SQLite integration
import sqlite3

# Connect to a database or create if it doesn’t exist
connection = sqlite3.connect(“example.db”)

# Create a table and insert data
with connection:
cursor = connection.cursor()
cursor.execute(“CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)”)
cursor.execute(“INSERT INTO users (name, age) VALUES (?, ?)”, (“Alice”, 25))
cursor.execute(“INSERT INTO users (name, age) VALUES (?, ?)”, (“Bob”, 30))

# Query data from the table
with connection:
cursor = connection.cursor()
cursor.execute(“SELECT * FROM users”)
users = cursor.fetchall()
for user in users:
print(user)

ORMs like SQLAlchemy abstract the database interactions, allowing you to work with databases using Python objects. They simplify database management and make it easier to handle complex relationships between tables.

# Example of using SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Define the database model
Base = declarative_base()

class User(Base):
__tablename__ = ‘users’
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)

# Connect to the database
engine = create_engine(“sqlite:///example.db”)
Base.metadata.create_all(engine)

# Insert data into the database
Session = sessionmaker(bind=engine)
session = Session()

user1 = User(name=”Alice”, age=25)
user2 = User(name=”Bob”, age=30)

session.add_all([user1, user2])
session.commit()

# Query data from the database
users = session.query(User).all()
for user in users:
print(user.name, user.age)