When working with SQLAlchemy, there are three main components to work with:

• The table, which functions as a normal DB table
• The mapper, which maps a Python class to a table
• The class object, which handles how a DB record maps to a Python object

One beneficial aspect of SQLAlchemy is that all of these items can be placed within the same location, rather than having separate files. The SQLAlchemy declarative contains the definitions of these objects.

To convert the previous SQLite database into a SQLAlchemy database, we first create the declarative program, as shown here:

# sqlalchemy_declarative.py
1 from sqlalchemy import Column, Integer, String, create_engine
2 from sqlalchemy.ext.declarative import declarative_base
3 ​
4 Base = declarative_base()
5​ 
6 class Tools(Base):
7     __tablename__ = "tools"
8     id = Column(Integer, primary_key=True)
9     name = Column(String(length=250), nullable=False)
10     size = Column(String(length=25))
11     price = Column(Integer)
12 ​
13 engine = create_engine("sqlite:///sqlalchemy_example.db")
14​ 
15 Base.metadata.create_all(engine)

Line 1 imports all the important creation items from the base SQLAlchemy module; these are used to create the database schema.

Line 2 imports the declarative base class, which our program will inherit from, after we create an instance of it in line 4.

The core of the declaration definitions is in lines 7-12. Each table in SQLAlchemy is defined as a class, and the parameters of the class are the individual columns that will make up the table. The columns are defined similar to the SQLite table, such as deciding the column that will be the primary key and the type of data contained within a column. When nullable=False is present, it means the column cannot be empty; otherwise, the column will assume no data is necessary.

Line 15 creates the DB engine that will handle the interaction with the actual DB file, and line 17 creates the tables from the defined classes. In this case, we only have one table, but large databases may have multiple ones that reference others.

When this program is run, a new database file (sqlalchemy_example.db) should appear within the current directory.