How to create a table in SQLAlchemy

SQLAlchemy allows us to define the database schema using Python classes, simplifying creating tables and specifying their structure. In this Answer, we will discuss creating a database table with SQLAlchemy.

Creating database tables

Here’s a step-by-step guide to creating database tables with SQLAlchemy.

1. Importing SQLAlchemy and defining a base class

We import SQLAlchemy and create a base class using declarative_base() function. This base class serves as the foundation for the table classes.

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
from sqlalchemy.ext.declarative import declarative_base
# Define Table Classes
Base = declarative_base()
Setting up a SQLAlchemy base class for database table definitions

2. Defining the table class

We create a Python class for the database table. Each class should inherit from the Base class we defined earlier. In the class, we define attributes corresponding to the table’s columns using SQLAlchemy’s column types.

class CustomTable1(Base):
    __tablename__ = 'Educative_Answers'
    custom_id = Column(Integer, primary_key=True)
    custom_name = Column(String, unique=True)
Defining a SQLAlchemy table class for the products

In this example, we’ve defined a Educative_Answers table with columns for id and name. We can adjust the table structure according to our specific requirements.

Now, we need to establish a connection to the database system using the create_engine() function of SQLAlchemy to create the database and tables, as shown below:

# Replace 'your_database_url_here' with your actual database URL
engine = create_engine('your_database_url_here')
# Create the tables in the database
Base.metadata.create_all(engine)
Establishing a database connection and creating tables with SQLAlchemy

Be sure to replace 'your_database_url_here' with the appropriate connection details for your database system (e.g., SQLite, PostgreSQL, MySQL).

3. Using the created tables

Now that the tables are created, we can interact with them using SQLAlchemy. We use the simple metadata.tables.keys() method to print all the tables in our database.

# Create the tables in the in-memory database
Base.metadata.create_all(engine)
# Print the names of all tables in the database
def print_all_tables(engine):
    # To load metdata and existing database schema
    metadata = MetaData()
    metadata.reflect(bind=engine)
    
    tables = metadata.tables.keys()
    
    print("List of tables:")
    for table in tables:
        print(table)
# Print all tables in the in-memory database
print_all_tables(engine)
Printing all the table names in SQLAlchemy

Complete code

The complete working code is given below. You can alter the code to explore the properties of SQLAlchemy and see the changes in real time.

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
from sqlalchemy.ext.declarative import declarative_base
# Create an in-memory SQLite database engine
engine = create_engine('sqlite:///:memory:')
# Define Table Classes
Base = declarative_base()
class CustomTable1(Base):
    __tablename__ = 'Educative_Answers'
    custom_id = Column(Integer, primary_key=True)
    custom_name = Column(String, unique=True)
class CustomTable2(Base):
    __tablename__ = 'Educative_CloudLabs'
    custom_id = Column(Integer, primary_key=True)
    custom_name = Column(String, unique=True)
# Create the tables in the in-memory database
Base.metadata.create_all(engine)
# Print the names of all tables in the database
def print_all_tables(engine):
    metadata = MetaData()
    metadata.reflect(bind=engine)
    
    tables = metadata.tables.keys()
    
    print("List of tables:")
    for table in tables:
        print(table)
# Print all tables in the in-memory database
print_all_tables(engine)

Conclusion

SQLAlchemy simplifies creating and managing database tables in Python straightforward and intuitive. We can easily create and maintain databases across various database systems by defining the table structures as Python classes. SQLAlchemy’s flexibility and ORM capabilities streamline the process of database table creation, allowing us to focus on building the application’s functionality.

Unlock your potential: SQLAlchemy fundamentals series, all in one place!

To deepen your understanding of SQLAlchemy, explore our series of Answers below:

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved