SQLAlchemy ORM – Creating Session

In this article, we will see how to create a session for SQLAlchemy ORM queries.

Before we begin, let us install the required dependencies using pip:

pip install sqlalchemy

Since we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the code implementations changes with change in the database except for the SQL connectors.

pip install pymysql

Session in SQLAlchemy ORM

The Session establishes all conversations with the database. The session is a regular Python class which can be directly instantiated. However, to standardize how sessions are configured and acquired, the sessionmaker class is normally used to create a top-level Session configuration which can then be used throughout an application without the need to repeat the configurational arguments.

Syntax: sqlalchemy.orm.session.sessionmaker(bind=None, **kwargs)

Parameters:

• bind: sqlalchemy.engine.Engine object specifying the database to perform conversations with.

Example 1: Creating a configured session class

Python

import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine("mysql+pymysql://root:password@\
localhost/Geeks4Geeks")

# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Students(Base):

    __tablename__ = 'students'

    first_name = db.Column(db.String(50),
                           primary_key=True)
    last_name  = db.Column(db.String(50),
                           primary_key=True)
    course     = db.Column(db.String(50))
    score      = db.Column(db.Float)

# CREATE THE SESSION OBJECT
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

# TESTING OUR SESSION OBJECT
result = session.query(Students).all()

for r in result:
    print(r.first_name, r.last_name)

Output:

Example 1 Output

For all the examples, we create a mandatory engine object so that we can bind the engine with the session. In this example, we have created a configured session object. The engine is defined at the time of the session maker call. However, there are other ways we can do it as per our requirements. The code uses a demo table for students which has 4 fields and 6 records as seen in the code and the output.

Example 2: Adding configurations to existing sessionmaker()

Python

import sqlalchemy as db

# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine("mysql+pymysql:/\
/root:password@localhost/Geeks4Geeks")

# CREATE THE SESSION OBJECT
from sqlalchemy.orm import sessionmaker

Session = sessionmaker()

# session is configured later
Session.configure(bind=engine)
session = Session()

In example 2, the session maker creates a session object. This session object is configured later as we can see in the program. This configure method comes in handy especially when we want to use multiple database instances or if the engine object is defined at a later stage in the program.

Example 3: Using multiple sessions in SQLAlchemy

Python

import sqlalchemy as db
from sqlalchemy.orm import sessionmaker

# DEFINE THE ENGINE FOR DATABASE 1
engine_1 = db.create_engine("mysql+pymysql://\
root:password@localhost/Geeks4Geeks")

# DEFINE THE ENGINE FOR DATABASE 2
engine_2 = db.create_engine("mysql+pymysql://\
root:password@localhost/Geeks4Geeks2")

# CREATING A CONFIGURED SESSION
# CLASS FOR DATABASE 1
Session_1 = sessionmaker(bind=engine_1)
session_1 = Session_1()

# CREATING A SESSION CLASS FOR DATABASE
# 2 AND CONFIGURING IT LATER
Session_2 = sessionmaker()
Session_2.configure(bind=engine_2)
session_2 = Session_2()

In the third example, we have created 2 different sessions using the sessionmaker() method. Both these sessions are pointing to different database connections. In use cases like master-slave connection or multi-database architecture, this will prove to be a lot useful.