- One of the core concepts in SQLAlchemy is the Session.
- A Session establishes and maintains all conversations between your program and the databases.
- It represents an intermediary zone for all the Python model objects you have loaded in it.
- It is one of the entry points to initiate a query against the database, whose results are populated and mapped into unique objects within the Session.
- A unique object is the only object in the Session with a particular primary key.
- A Session is constructed, at which point it is not associated with any model objects.
- The Session receives query requests, whose results are persisted / associated with the Session.
- Arbitrary number of model objects are constructed and then added to the Session, after which point the Session starts to maintain and manage those objects.
- Once all the changes are made against the objects in the Session, we may decide to commit the changes from the Session to the database or rollback those changes in the Session. Session.commit() means that the changes made to the objects in the Session so far will be persisted into the database while Session.rollback() means those changes will be discarded.
- Session.close() will close the Session and its corresponding connections, which means we are done with the Session and want to release the connection object associated with it.
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
from sqlalchemy import create_engine
engine = create_engine('sqlite:///')
from sqlalchemy.orm import sessionmaker
# Construct a sessionmaker object
session = sessionmaker()
# Bind the sessionmaker to engine
session.configure(bind=engine)
# Create all the tables in the database which are
# defined by Base's subclasses such as User
Base.metadata.create_all(engine)
# Make a new Session object
s = session()
john = User(name='John')
# Add User john to the Session object
s.add(john)
# Commit the new User John to the database
s.commit()
Inspect the new object's id at each step during the insertion process.
>>> mary = User(name='Mary')
>>> print(mary.id, mary.name)
(None, 'Mary')
>>> s.add(mary)
>>> print(mary.id, mary.name)
(None, 'Mary')
>>> s.commit()
>>> print(mary.id, mary.name)
(1, u'Mary')