How to Use SQLAlchemy

SQLAlchemy is a powerful Python toolkit and Object-Relational Mapping (ORM) library widely used for interacting with relational databases. It allows you to work with databases using SQL expressions or an ORM that maps database tables to Python classes, enabling object-oriented database operations.

1. Install SQLAlchemy

First, ensure that SQLAlchemy is installed. You can install it using pip:

pip install sqlalchemy

2. Configure Database Connection

Use the create_engine() method to create a database engine.

from sqlalchemy import create_engine

# Example: Connecting to an SQLite database
engine = create_engine('sqlite:///example.db')

# Example: Connecting to a MySQL database
# engine = create_engine('mysql+pymysql://username:password@localhost/db_name')
    

3. Define Table Structures (Using ORM)

With SQLAlchemy's ORM, you define Python classes and map them to database tables.

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# Define a table mapped to a class
class User(Base):
    __tablename__ = 'users'  # Table name
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Create the table in the database
Base.metadata.create_all(engine)
    

4. Create a Session and Perform Database Operations

Use a Session to interact with the database.

from sqlalchemy.orm import sessionmaker

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Add data
new_user = User(name='Alice', age=25)
session.add(new_user)
session.commit()

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

# Update data
user_to_update = session.query(User).filter_by(name='Alice').first()
user_to_update.age = 26
session.commit()

# Delete data
user_to_delete = session.query(User).filter_by(name='Alice').first()
session.delete(user_to_delete)
session.commit()
    

5. Use SQL Expression Language

SQLAlchemy also supports working with SQL expressions directly, instead of using ORM.

from sqlalchemy import Table, MetaData

# Load the table using metadata
metadata = MetaData()
users_table = Table('users', metadata, autoload_with=engine)

# Insert data
with engine.connect() as conn:
    conn.execute(users_table.insert().values(name='Bob', age=30))

# Query data
with engine.connect() as conn:
    result = conn.execute(users_table.select())
    for row in result:
        print(row)
    

6. Connect to Different Databases

SQLAlchemy supports various databases, including SQLite, PostgreSQL, MySQL, Oracle, and more. Adjust the connection string accordingly:

• SQLite: sqlite:///example.db
• PostgreSQL: postgresql://username:password@localhost/db_name
• MySQL: mysql+pymysql://username:password@localhost/db_name

7. Common Tips and Debugging

• Explicit Commit: By default, SQLAlchemy ORM sessions require an explicit commit() to persist changes to the database.
• Connection Pooling: SQLAlchemy includes built-in connection pooling, which can be configured for performance optimization.
• Debugging: Pass echo=True to create_engine() to print all executed SQL statements in the console for debugging purposes.

engine = create_engine('sqlite:///example.db', echo=True)
    

By following these steps, you can use SQLAlchemy to interact with databases easily, whether through its ORM or direct SQL expression language. Happy coding!