Building Database Tables with SQLAlchemy: Choosing the Right Approach

1. Using declarative_base: This is the more common and recommended approach. Here's how it works:

   • You create a base class using declarative_base() from the sqlalchemy.orm module.
   • You define your table structure by creating a class that inherits from this base class.
   • Within the inheriting class, you define attributes that represent the columns in your table. These attributes use the Column class from sqlalchemy.

2. Using the Table class: This is the older and less common approach. Here's what you do:

   • You create a Table object directly, specifying the table name and column definitions using the Column class.
   • You then use the mapper function to associate this table with a separate Python class that represents your model.

The Key Differences:

• Conciseness: The declarative_base approach is more concise. You define everything within the class that represents your model.
• Readability: It also improves readability because the table structure and model logic are in the same place.
• Automatic mapping: The declarative_base approach automatically creates the table schema based on your class definition. You don't need a separate mapper step.

Here's an example to illustrate the difference:

Using declarative_base:

from sqlalchemy import Column, Integer, String, declarative_base

Base = declarative_base()

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

Using Table:

from sqlalchemy import Column, Integer, String, Table, MetaData

metadata = MetaData()

user = Table('users', metadata,
             Column('id', Integer, primary_key=True),
             Column('name', String))

class User(object):
  pass

# Use mapper to associate the table with the User class
mapper(User, user)

from sqlalchemy import Column, Integer, String, declarative_base

# Create a declarative base class
Base = declarative_base()

class User(Base):
  # Define the table name
  __tablename__ = 'users'

  # Define table columns using Column class
  id = Column(Integer, primary_key=True)
  name = Column(String)

from sqlalchemy import Column, Integer, String, Table, MetaData

# Create metadata object to hold table information
metadata = MetaData()

# Define the table structure directly
user = Table('users', metadata,
             Column('id', Integer, primary_key=True),
             Column('name', String))

# Create a plain Python class for the model
class User(object):
  pass

# Use mapper to associate the table with the User class 
mapper(User, user)

1. Using MetaData and Reflection:

This approach uses the MetaData class to introspect an existing database schema. It allows you to create SQLAlchemy objects that reflect the existing tables and their columns. This can be useful if you're working with a pre-existing database that you don't want to modify the structure of.

Here's a basic example:

from sqlalchemy import MetaData, Table, Column, Integer, String

# Create metadata object
metadata = MetaData()

# Define a table object by reflecting an existing table named 'users'
users = Table('users', metadata, autoload=True, autoload_with=engine)

# Access column information
id_column = users.c.id
name_column = users.c.name

# Use the reflected table and columns in your queries
# ...

1. Declarative with Custom Table Arguments:

While declarative_base offers automatic table creation, you can still customize the table definition within the class. You can achieve this by using arguments like __table_args__ within your class definition. This allows you to specify options like constraints, indexes, or foreign keys directly in the class.

from sqlalchemy import Column, Integer, String, declarative_base

Base = declarative_base()

class User(Base):
  __tablename__ = 'users'

  id = Column(Integer, primary_key=True)
  name = Column(String, unique=True)

  # Define custom table arguments (e.g., index)
  __table_args__ = (
      {"schema": "my_schema"},  # Specify schema name
      {"mysql_engine": "InnoDB"},  # Engine specific options
  )