What is the difference between Session and db.session in SQLAlchemy?

Question

In the event mapper level docs it says that Session.add() is not supported, but when I tried to do db.session.add(some_object) inside after_insert event it worked, example:

def after_insert_listener(mapper, connection, user):
    global_group = Group.query.filter_by(groupname='global').first()
    a = Association(user,global_group)
    db.session.add(a)

event.listen(User, 'after_insert', after_insert_listener)

Basically any new user should be part of global_group, so I added it in the after_insert event. I tried to insert a user, and then checked into my database and I found the user record, and the association record.

Could you describe a bit more about the example you used? For example, you said "it worked". What do you mean by "worked"? — Mark Hildreth
– Mark Hildreth, Commented Nov 12, 2015 at 18:03
please check the question again, I have updated it with an example. — Ghooo
– Ghooo, Commented Nov 12, 2015 at 18:11

Andre Araujo · Accepted Answer · 2015-11-13 13:10:41Z

23

Let's check the diferences:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://///Users/dedeco/Documents/tmp/testDb.db'
db = SQLAlchemy(app)

>>>type(db.session)
<class 'sqlalchemy.orm.scoping.scoped_session'>

or

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

some_engine = create_engine('sqlite://///Users/dedeco/Documents/tmp/testDb.db')
Session = sessionmaker(bind=some_engine)
session = Session()

Base = declarative_base()

>>> type(session)
<class 'sqlalchemy.orm.session.Session'>

Basically the difference is:

In the first way you are using a API developed for the Flask framework, called Flask-SQLAlchemy. It's the option if you are creating a Flask application, because the scope of the Session can be managed automatically by your application. You have many benefits like a infrastructure to establish a single Session, associated with the request, which is correctly constructed and torn down corresponding torn down at the end of a request.
In the second way is a pure SQLAlchemy app, so if you are using a library to connect a particular database, you can use just a SQLAlchemy API, for example, for a command-line script, background daemon, GUI interface-driven application, etc.

So, in a both way you can add, like:

Using a Flask-SQLAlchemy:

class User(db.Model):
    __tablename__ = 'users'
    user_id = db.Column(db.Integer(), primary_key = True)
    user_name = db.Column(db.String(80), unique=True)
    def __init__(self, user_name):
        self.user_name = user_name

>>> db.create_all()
>>> u = User('user1')
>>> db.session.add(u)
>>> db.session.commit()
>>> users = db.session.query(User).all()
>>> for u in users:
...     print u.user_name
... 
user1

Using just SQLAlchemy:

class User(Base):
    __tablename__ = 'users'
    user_id = Column(Integer(), primary_key = True)
    user_name = Column(String(80), unique=True)

>>> u = User()
>>> u.user_name = 'user2'
>>> session.add(u)
>>> session.commit()
>>> users = session.query(User).all()
>>> for u in users:
...     print u.user_name
... 
user1
user2

Realize that I am connecting in the same database just for show that you can add using many ways.

edited Nov 13, 2015 at 13:10

answered Nov 13, 2015 at 3:19

Andre Araujo

2,3982 gold badges30 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GabrielChu Over a year ago

Does that mean we don't need to manually close the session in try except for the case of Flask-SQLAlchemy, since flask will handle it for us?

Andre Araujo Over a year ago

No, you need close. Here is a doc about docs.sqlalchemy.org/en/13/orm/session_basics.html in FAQ you can see exaclty your doubt: When do I construct a Session, when do I commit it, and when do I close it?

Oliver_Fragoso · Accepted Answer · 2021-11-24 18:29:33Z

server = Flask(__name__)
app = dash.Dash(__name__,server=server,external_stylesheets=[dbc.themes.LITERA], suppress_callback_exceptions = True)

app.server.config["SQLALCHEMY_DATABASE_URI"] = f'postgresql://postgres:.../...'
db = SQLAlchemy(app.server)

I have the same problem of not knowing at what point I should close the session of the database in my web application. I found this in the link that @GabrielChu shared so what I understood was if you are dealing with a web app the session is closed when the user close their tab

A web application is the easiest case because such an application is already constructed around a single, consistent scope - this is the request, which represents an incoming request from a browser, the processing of that request to formulate a response, and finally the delivery of that response back to the client. Integrating web applications with the Session is then the straightforward task of linking the scope of the Session to that of the request. The Session can be established as the request begins, or using a lazy initialization pattern which establishes one as soon as it is needed. The request then proceeds, with some system in place where application logic can access the current Session in a manner associated with how the actual request object is accessed. As the request ends, the Session is torn down as well, usually through the usage of event hooks provided by the web framework. The transaction used by the Session may also be committed at this point, or alternatively the application may opt for an explicit commit pattern, only committing for those requests where one is warranted, but still always tearing down the Session unconditionally at the end.

Some web frameworks include infrastructure to assist in the task of aligning the lifespan of a Session with that of a web request. This includes products such as Flask-SQLAlchemy, for usage in conjunction with the Flask web framework, and Zope-SQLAlchemy, typically used with the Pyramid framework. SQLAlchemy recommends that these products be used as available

Collectives™ on Stack Overflow

What is the difference between Session and db.session in SQLAlchemy?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related