Getting started with SQLAlchemy

SQLAlchemy is a Python package that helps with object relational mapping. It is also a python library which means we’ll get access to many classes and methods when we install it on our environment. To use SQLAlchemy ORM for table creation, you need to define classes that have the following four important characteristics:

1. Inheritance from a declarative_base object. A declarative_base is a combination of a metadata container for tables and a set of methods that act as mappers between Python and SQL databases. By inheriting the classes we create from the declarative_base object, which is called Base, we can avoid the need to rewrite code.
2. A class attribute called tablename. The tablename attribute serves as the name for the corresponding SQL database table.
3. One or more class attributes defining the table’s columns. The table’s columns are specified as attributes using Column objects.
4. A column defined as the primary key for the table. The optional primary_key argument is used to indicate that the ‘id’ attribute will serve as the primary key for the ‘students’ table.

See an example below:

Persisting the Schema

Now we have all the data we need to generate our table, but it won’t happen until we execute a series of Python statements to persist our schema.

First, we must import create_engine from the SQLAlchemy package. By wrapping certain code blocks in the “name == ‘main’” condition, they will only be executed when the script is run as the main program, and not when it is imported as a module. This is useful for tasks such as database initialization or running standalone scripts that are not meant to be imported.

If it is being run as the main program, it then creates an engine object using the create_engine function to connect to a SQLite database named ‘students.db’. The create_engine function returns an instance of Engine class, which represents the core interface to the database. The Engine object provides a low-level API for executing SQL commands and managing transactions.

The next line of code uses metadata from the Base class to create tables in the database by calling the create_all method on the engine object. Base.metadata is a property of the Base class that provides access to the metadata for the database tables associated with the declarative data models defined in the Python code. We then create the tables in the database based on the data models defined in the student class.

Next, we must run chmod +x lib/sqlalchemy_sandbox.py (or / the name of your file) to make the script executable. The chmod command stands for “change mode” and is used to modify the read, write, and execute permissions of files or directories. The +x option tells the command to add the execute permission to the file. After running your file one more time, you should see that a students.db pop up in your SQLlite explorer with a students table.

Sessionmaker

In SQLAlchemy, sessions are used to interact with the database. These sessions wrap around engine objects, which are similar to the one we included in our sqlalchemy_sandbox.py script. Each session contains an identity map, which is like an empty dictionary with keys for the table name, columns, and primary keys. When the session fetches data from students.db, it populates the identity map and uses it to create a Student object with specific attribute values. Conversely, when data is committed to the database, the identity map is filled in the same manner, but the resulting data is unpacked into a row in the students table.

To create a session, we need to use SQLAlchemy’s sessionmaker class.

First, we start with importing sessionmaker from sqlalchemy.orm. This allows us to create a session that is customized to our specific needs and use it to generate new session objects as needed. This makes it easy to manage transactions and other database operations in your SQLAlchemy code.

Session = sessionmaker(bind=engine) creates a session that is bound to our specific database engine. The bind parameter specifies the engine to be used by the session to interact with the database. In this case, the engine object represents a connection to a SQLite database file, as specified by the connection string sqlite:///students.db.

Next, session = Session() generates a new Session object that can be used to perform various database operations, such as querying for data, adding new records, updating existing records, and deleting records. For example, you might query for all students in the students table by calling session.query(Student).all(), or add a new student to the table by creating a new Student object and calling session.add(student). When you’re finished working with the database, you can commit your changes by calling session.commit().