create database and engine

from sqlalchemy import create_engine, MetaData
from passlib.context import CryptContext
import databases

database = databases.Database("sqlite:///example.db")
metadata = MetaData()

...

engine = create_engine("sqlite:///example.db")
metadata.create_all(engine)
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

...

@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

Table Construction

SQLAlchemy Core: A SQL expression language toolkit. You work more directly with schema constructs like Table, Column, and functions like select, insert, update, delete. This feels closer to SQL but provides Pythonic expression building and database abstraction. Methods are often associated with the Table object or are functions that operate on Table objects.
SQLAlchemy ORM (Object Relational Mapper): Builds on Core. You define Python classes (models) that map to database tables. You interact with tables primarily by manipulating instances of these classes and using a Session object to coordinate database operations.

SQLAlchemy Core: Working with Table Objects

In Core, you typically define or load a Table object first.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select, insert, update, delete

# Define metadata
metadata_obj = MetaData()

# Define a Table object (or load it using autoload_with=engine)
users_table = Table(
    "users",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("name", String(50)),
    Column("email", String(100)),
)

# Connect to a database (example using SQLite in-memory)
engine = create_engine("sqlite:///:memory:")
metadata_obj.create_all(engine) # Create the table in the database

Now, let's look at the primary ways to build SQL statements using this Table object, replacing raw SQL:

1. SELECT Queries

SQL: SELECT id, name FROM users WHERE name = 'Alice' ORDER BY id LIMIT 1;
SQLAlchemy Core (using select() function):
```
stmt = select(users_table.c.id, users_table.c.name)\
    .where(users_table.c.name == 'Alice')\
    .order_by(users_table.c.id)\
    .limit(1)

# To execute:
with engine.connect() as connection:
    result = connection.execute(stmt)
    for row in result:
        print(row) # Access columns like row.id, row.name or by index row[0]
        # Example Output: (1, 'Alice') if found
```
- Key Constructs:
  - select(...): Creates a SELECT statement object. You pass the columns (users_table.c.column_name) you want. select(users_table) selects all columns (SELECT *).
  - .where(...): Adds a WHERE clause. You use standard Python comparison operators (==, !=, >, <, etc.) with Table.c.column_name. Combine conditions with and_(), or_(), not_().
  - .order_by(...): Adds an ORDER BY clause. Use column.asc() or column.desc().
  - .limit(...): Adds a LIMIT clause.
  - .offset(...): Adds an OFFSET clause.
  - .group_by(...): Adds a GROUP BY clause.
  - .join(...): Adds a JOIN clause (e.g., users_table.join(addresses_table, users_table.c.id == addresses_table.c.user_id)).

2. INSERT Statements

SQL: INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

SQLAlchemy Core (using insert() function or Table.insert() method):

# Using the insert() function
stmt = insert(users_table).values(name='Bob', email='bob@example.com')

# Or using the Table.insert() method (less common in modern SQLAlchemy)
# stmt = users_table.insert().values(name='Bob', email='bob@example.com')

# To execute:
with engine.connect() as connection:
    result = connection.execute(stmt)
    connection.commit() # Important: commit changes for INSERT/UPDATE/DELETE
    print(f"Inserted PK: {result.inserted_primary_key}") # Get the new primary key

Key Constructs:
- insert(table): Creates an INSERT statement object.
- .values(...): Specifies the data to insert, using keyword arguments (column names) or a list of dictionaries for multiple rows.

3. UPDATE Statements

SQL: UPDATE users SET email = 'robert@example.com' WHERE name = 'Bob';

SQLAlchemy Core (using update() function or Table.update() method):

# Using the update() function
stmt = update(users_table)\
    .where(users_table.c.name == 'Bob')\
    .values(email='robert@example.com')

# Or using the Table.update() method
# stmt = users_table.update()\
#     .where(users_table.c.name == 'Bob')\
#     .values(email='robert@example.com')

# To execute:
with engine.connect() as connection:
    result = connection.execute(stmt)
    connection.commit() # Commit changes
    print(f"Rows updated: {result.rowcount}")

Key Constructs:
- update(table): Creates an UPDATE statement object.
- .where(...): Specifies which rows to update (like SELECT). If omitted, all rows will be updated (usually not desired!).
- .values(...): Specifies the column(s) and their new value(s).

4. DELETE Statements

SQL: DELETE FROM users WHERE name = 'Bob';

SQLAlchemy Core (using delete() function or Table.delete() method):

# Using the delete() function
stmt = delete(users_table).where(users_table.c.name == 'Bob')

# Or using the Table.delete() method
# stmt = users_table.delete().where(users_table.c.name == 'Bob')

# To execute:
with engine.connect() as connection:
    result = connection.execute(stmt)
    connection.commit() # Commit changes
    print(f"Rows deleted: {result.rowcount}")

Key Constructs:
- delete(table): Creates a DELETE statement object.
- .where(...): Specifies which rows to delete. If omitted, all rows will be deleted (use with extreme caution!).

SQLAlchemy ORM: Working with Mapped Classes and Sessions

In the ORM, you interact with Python objects, and the Session translates these actions into SQL.

from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy import create_engine, Column, Integer, String, select, update, delete # Need select, update, delete for 2.0 style ORM queries

# Define a Base class
Base = declarative_base()

# Define a mapped class (Model)
class User(Base):
    __tablename__ = 'users_orm' # Optional if class name matches table name convention
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    email = Column(String(100))

    def __repr__(self):
       return f"<User(id={self.id}, name='{self.name}', email='{self.email}')>"

# Engine and create table
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

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

1. SELECT Queries (Fetching Objects)

SQL: SELECT id, name, email FROM users_orm WHERE name = 'Charlie';

SQLAlchemy ORM:

with Session() as session:
    # --- Modern (2.0) Style ---
    stmt = select(User).where(User.name == 'Charlie')
    results = session.scalars(stmt).all() # scalars() gets first column (the User object)
    # Or: results = session.execute(stmt).scalars().all()

    # --- Older (1.x) Style (still works but 2.0 is preferred) ---
    # results = session.query(User).filter(User.name == 'Charlie').all()

    for user in results:
        print(user)
        # Example Output: <User(id=1, name='Charlie', email='charlie@example.com')>

2. INSERT Statements (Adding Objects)

SQL: INSERT INTO users_orm (name, email) VALUES ('David', 'david@example.com');

SQLAlchemy ORM:

new_user = User(name='David', email='david@example.com')
with Session() as session:
    session.add(new_user)
    session.commit() # Persists the change to the database
    print(f"Added user with ID: {new_user.id}") # ID is populated after commit

3. UPDATE Statements (Modifying Objects)

SQL: UPDATE users_orm SET email = 'dave@example.com' WHERE name = 'David';

SQLAlchemy ORM:

with Session() as session:
    # --- Option 1: Fetch, Modify, Commit ---
    user_to_update = session.scalars(select(User).where(User.name == 'David')).first()
    # Or: user_to_update = session.query(User).filter(User.name == 'David').first()

    if user_to_update:
        user_to_update.email = 'dave@example.com'
        session.commit() # The Session tracks the change and issues an UPDATE
        print("User updated via object modification.")
    else:
        print("User not found.")

    # --- Option 2: Bulk Update (Modern 2.0 Style) ---
    # Efficient for updating multiple rows without loading them into memory
    stmt = update(User)\
        .where(User.name == 'David')\
        .values(email='dave.new@example.com')
    result = session.execute(stmt)
    session.commit()
    print(f"Rows updated via bulk statement: {result.rowcount}")

4. DELETE Statements (Deleting Objects)

SQL: DELETE FROM users_orm WHERE name = 'David';

SQLAlchemy ORM:

with Session() as session:
    # --- Option 1: Fetch, Delete, Commit ---
    user_to_delete = session.scalars(select(User).where(User.name == 'David')).first()
    # Or: user_to_delete = session.query(User).filter(User.name == 'David').first()

    if user_to_delete:
        session.delete(user_to_delete)
        session.commit() # Issues a DELETE statement
        print("User deleted via object.")
    else:
        print("User not found.")

    # --- Option 2: Bulk Delete (Modern 2.0 Style) ---
    # Efficient for deleting multiple rows without loading them
    stmt = delete(User).where(User.name == 'SomeOtherUser')
    result = session.execute(stmt)
    session.commit()
    print(f"Rows deleted via bulk statement: {result.rowcount}")

In summary, SQLAlchemy (both Core and ORM) provides Pythonic ways to construct and execute SQL statements. You replace raw SQL strings with Python functions, methods, and object interactions, gaining benefits like:

Database Agnosticism: Write code once, run against different SQL databases (PostgreSQL, MySQL, SQLite, etc.) with minimal changes.
Type Safety & Autocompletion: Work with Python objects and methods, catching errors earlier.
Security: Automatically handles parameter escaping, helping prevent SQL injection vulnerabilities when used correctly.
Composability: Build complex queries step-by-step.
ORM Features: (With ORM) Relationship management, unit of work pattern (session), lazy loading, etc.