The application I was writing required that I have models that were portable (not just for use with Flask), but I still wanted to be able to take advantage of the sessions that Flask-SQLAlchemy provided.

It would be nice if it were documented somewhere that, if you are willing to forego some of the convenience methods on the Model (like Model.query), then you can use standard SQLAlchemy models with the Flask-SQLAlchemy session.

Incidentally this approach also helps with avoiding circular imports when passing the SQLAlchemy() instance, db, around to each model.

Here is an example:

# models.py

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)
    email = Column(String(120), unique=True)

    def __init__(self, name=None, email=None):
        self.name = name
        self.email = email

    def __repr__(self):
        return '<User %r>' % (self.name)

# app.py

from flask import Flask
from models import Base, User
from flask_sqlalchemy import SQLAlchemy

app =  Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

@app.before_first_request
def setup():
    # Recreate database each time for demo
    Base.metadata.drop_all(bind=db.engine)
    Base.metadata.create_all(bind=db.engine)
    db.session.add(User('Bob Jones', '[email protected]'))
    db.session.add(User('Joe Quimby', '[email protected]'))
    db.session.commit()

@app.route('/')
def root():
    users = db.session.query(User).all()
    return u"<br>".join([u"{0}: {1}".format(user.name, user.email) for user in users])

if __name__ == '__main__':
    app.run('127.0.0.1', 5000) 

An InvalidRequestError occurs when subclassing db.Model with two objects that have the same __tablename__ but different __bind_key__.

sqlalchemy.exc.InvalidRequestError: Table 'users' is already defined for
this MetaData instance.  Specify 'extend_existing=True' to redefine
options and columns on an existing Table object.

This could happen, for example, if you had a users table in two different databases that you needed to access. This patch creates a unique MetaData object for each bind_key which stops this error from happening and allows access to tables with the same name in different databases. It also includes a test to verify the error is fixed.

Last release was Oct 15, 2015.

A few critical fixes are needed specifically 3fec753592.

Would it be possible to get a 2.2 release so our requirements file can use a version instead of a git version?

Hi,

I encountered the same error as this (scenario is also stated in the link): http://flask.pocoo.org/mailinglist/archive/2011/3/6/flask-sqlalchemy-and-fixtures/.

Thanks

I don't know where this comes from. I have a small project where all versions are pinned and the Flask app runs in a Docker container. I just made an unrelated changes with the same versions and get this error. Does this look familiar?

Expected Behavior

Just running without crashes

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

Actual Behavior

Crashes

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.8/site-packages/flask_sqlalchemy/__init__.py", line 716, in __init__
    self.Model = self.make_declarative_base(model_class, metadata)
  File "/usr/local/lib/python3.8/site-packages/flask_sqlalchemy/__init__.py", line 798, in make_declarative_base
    model.query_class = self.Query
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/api.py", line 79, in __setattr__
    _add_attribute(cls, key, value)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 802, in _add_attribute
    type.__setattr__(cls, key, value)
TypeError: can't apply this __setattr__ to DefaultMeta object

Environment

• Python version: Python 3.8.4 (Docker python:3.8-slim-buster)
• Flask-SQLAlchemy version: flask-sqlalchemy==2.4.1
• SQLAlchemy version: sqlalchemy==1.3.13

I found some SQLAlchemy parameters are helpful when we have unreliable network connectivity between applications and database.

• creator helps to create custom DBAPI connections.
• echo_pool will log all check-ins/outs of the pool.
• pool_pre_ping tests connections for linveness upon each checkout.
• pool_reset_on_return allows to override default behavior upon return.

By default, all those options are turned off (or following the default behavior in SQLAlchemy) for backward compatibility.

For more information about the parameters, please refer to the following official SQLAlchemy documentation: http://docs.sqlalchemy.org/en/latest/core/engines.html#engine-creation-api

I needed to pass in an isolation_level parameters to the create_engine call. I did it like this:

https://github.com/gromgull/flask-sqlalchemy/commit/689c90c48949dc4c020f16550576c517d2fb5230

(on top of the 1.0 tag since that's what we used, but I can make a pull-request with it applied to HEAD if you want)

Just updated and my full application, working previously, broke down on setting the model due to the update (https://github.com/mitsuhiko/flask-sqlalchemy/pull/541). I believe it might be a problem with this new primary key detection thing, which I'm not sure I get the point of. The previously-fine code is thus:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)
db.reflect()

class Country(db.Model):
    __tablename__ = 'd_country'

Resulting in:

InvalidRequestError: Class <class 'app.models.Country'> does not have a __table__ or __tablename__ specified and does not inherit from an existing table-mapped class.

I'd be willing to bet that the update didn't take db.reflect() into account. I'm rolling back unless there's something else I can do, but I'd rather not have to do a declared_attr on all my tables just for this thing.

I am using version 2.4.1. I am trying to set my poolclass to NullPool by passing that option into my engine options like so:

from sqlalchemy.pool import NullPool
SQLALCHEMY_ENGINE_OPTIONS = {"poolclass":NullPool}

That is working as expected and the poolclass is being set. However I am getting the following error when running:

TypeError: Invalid argument(s) 'pool_size' sent to create_engine(), using configuration AuroraMySQLDataAPIDialect/NullPool/Engine.  Please check that the keyword arguments are appropriate for this combination of components.

The pool_size needs to NOT be set in order to use NullPool. This seems to be happening because the pool_size is being set to 10. I did some digging and found it was being set in apply_driver_hacks:

        if sa_url.drivername.startswith('mysql'):
            sa_url.query.setdefault('charset', 'utf8')
            if sa_url.drivername != 'mysql+gaerdbms':
                options.setdefault('pool_size', 10)
                options.setdefault('pool_recycle', 7200)

If I comment out those last 3 lines and run my code, the pool size doesn't get set and everything works as expected.

But I would like to figure out a way to prevent the pool size from being set to 10 without having to fork the repo and make this change to do so. Is there any way to do this? I have tried passing in pool_size:0 and pool_size:None into my engine options but it is still getting set to 10 regardless.

FWIW I am using a custom dialect (https://github.com/chanzuckerberg/sqlalchemy-aurora-data-api), but the driver name starts with "mysql" so it's being caught by this logic regardless.

Hi! It looks like the latest release of Flask-SQLAlchemy ships with inline type annotations. This is awesome!

Would you consider adding a py.typed file to the library? This would mean that we could mark our typeshed stubs for Flask-SQLAlchemy as officially obsolete, and not have to worry about updating our stubs to reflect the changes in 3.0.* (cf. https://github.com/python/typeshed/pull/8849).

Registering a listener for 'before_commit' (and 'before_flush') like so, where:

db = SQLAlchemy(app)
...
event.listen(db.session, 'before_commit', before_commit)

Throws the following exception:

    @event.listens_for(db.session, "before_flush")
  File "/home/environments/python3/lib/python3.3/site-packages/sqlalchemy/event/api.py", line 68, in decorate
    listen(target, identifier, fn, *args, **kw)
  File "/home/environments/python3/lib/python3.3/site-packages/sqlalchemy/event/api.py", line 49, in listen
    _event_key(target, identifier, fn).listen(*args, **kw)
  File "/home/environments/python3/lib/python3.3/site-packages/sqlalchemy/event/api.py", line 22, in _event_key
    tgt = evt_cls._accept_with(target)
  File "/home/environments/python3/lib/python3.3/site-packages/sqlalchemy/orm/events.py", line 1142, in _accept_with
    "Session event listen on a scoped_session "
sqlalchemy.exc.ArgumentError: Session event listen on a scoped_session requires that its creation callable is associated with the Session class.

Per the comment in this thread from Mike Bayer, it seems the issue is with the factory being passed to scoped_session not being a sessionmaker(): http://stackoverflow.com/questions/21322158/event-listener-on-scoped-session

Stepping through the code, it barfs in this method in line 1132 of sqlalchemy/orm/events.py:

    @classmethod
    def _accept_with(cls, target):
        if isinstance(target, scoped_session):

            target = target.session_factory
            if not isinstance(target, sessionmaker) and \
                (
                    not isinstance(target, type) or
                    not issubclass(target, Session)
                ):
                raise exc.ArgumentError(
                            "Session event listen on a scoped_session "
                            "requires that its creation callable "
                            "is associated with the Session class.")

db.session.session_factory is of type functools.partial, which angers SQLAlchemy.

Bumps dessant/lock-threads from 3 to 4.

Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.

On this page https://flask-sqlalchemy.palletsprojects.com/en/3.0.x/queries/#select, the information for querying one object is slightly incorrect.

user = db.session.execute(db.select(User).filter_by(username=username)).one()

This yields something like:

(<User 1>,)

I believe this is because we are working with the ORM models, so we need to use ScalarResult instead of Result. See https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.ScalarResult

So I think the example should read:

user = db.session.execute(db.select(User).filter_by(username=username)).scalars().one()

This will yield the model with out the tuple.

<User 1>

Environment:

• Python version: 3.11.0
• Flask-SQLAlchemy version: 3.0.2
• SQLAlchemy version: 1.4.44

I'm happy to update the documentation. I was considering sending a pull request, but it's late, so ended up filing a bug instead.

Support passing kwargs into db.session.get via db.get_or_404.

• fixes #1149

Checklist:

• [x] Add tests that demonstrate the correct behavior of the change. Tests should fail without the change.
• [x] Add or update relevant docs, in the docs folder and in code.
• [x] Add an entry in CHANGES.rst summarizing the change and linking to the issue.
• [x] Add .. versionchanged:: entries in any relevant code docs.
• [x] Run pre-commit hooks and fix any issues.
• [x] Run pytest and tox, no tests failed.

SqlAlchemy supports passing in various kwargs to session.get(), such as options, which allows for joinloading relationships (as an example). Ideally we could do the same to flask-sqlalchemy's extension object's get_or_404(). This would be a simple, backwards compatible change to flask-sqlalchemy's implementation.

Example:

from sqlalchemy.dialects.postgresql import UUID
from uuid import uuid4
from sqlalchemy.orm import joinedload

class Organization(db.Model):
    __tablename__ = "organization"
    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid4)

class User(db.Model):
    __tablename__ = "user"
    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    organization_id = db.Column(
        UUID(as_uuid=True),
        db.ForeignKey(Organization.id),
    )
    organization = db.relationship("Organization")
   

# currently - emits 2 queries
my_user = session.get_or_404(User, my_user_id)
user_organization = my_user.organization

# proposed - emits 1 query
my_user = session.get_or_404(User, my_user_id, options=[joinedload(User.organization)])
user_organization = my_user.organization

If this sounds good, I can submit a PR

I've been playing with mapped_column and MappedAsDataclass (new stuff in SQLAlchemy 2.x):

Declarative Table with mapped_column() Declarative Dataclass Mapping

Example:

mkdir sqlaplayground
cd sqlaplayground
pyenv local 3.11 
python -m venv .venv 
source .venv/bin/activate
pip install -U pip wheel setuptools
cat requirements.txt
# sqlalchemy >= 2.0.0b3
# flask-sqlalchemy @ git+https://github.com/pallets-eco/[email protected]
# flask >= 2.0.0
pip install -r requirements.txt

from __future__ import annotations

from typing import Optional

from sqlalchemy import ForeignKey, String, create_engine, orm
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, MappedAsDataclass, mapped_column, sessionmaker
)

CONFIG = {
    "SQLALCHEMY_DATABASE_URI": "sqlite+pysqlite://",
}

Engine = create_engine(CONFIG["SQLALCHEMY_DATABASE_URI"], future=True)
Session = sessionmaker(bind=Engine, future=True)
session = Session()


class BaseModel(MappedAsDataclass, DeclarativeBase):
    """subclasses will be converted to dataclasses"""

    pass


class Book(BaseModel):
    __tablename__ = "books"

    id: Mapped[Optional[int]] = mapped_column(
        init=False, primary_key=True, autoincrement=True
    )

    title: Mapped[Optional[str]] = mapped_column(String(length=64), default=None)

    author_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("authors.id"), nullable=False, index=True, default=None
    )
    author: Mapped[Optional[Author]] = orm.relationship(
        "Author", uselist=False, back_populates="books", default=None
    )


class Author(BaseModel):
    __tablename__ = "authors"

    id: Mapped[Optional[int]] = mapped_column(
        init=False, primary_key=True, autoincrement=True
    )
    name: Mapped[Optional[str]] = mapped_column(String(length=64), default=None)
    books: Mapped[list[Book]] = orm.relationship(
        "Book", uselist=True, back_populates="author", default_factory=list
    )

BaseModel.metadata.create_all(Engine)

book = Book(title="42", author=Author(name="Name"))
session.add(book)
session.commit()

This rather verbose declaration of models gives us some nice things:

• more precise mypy and PyRight static typechecking.
• dataclass-like __init__

repr(book)
# "Book(id=1, title='42', author_id=1, author=Author(id=1, name='Name', books=[...]))"
?book.__init__
# book.__init__(title=None, author_id=None, author=None)

First try at using my BaseModel with Flask-SQLAlchemy gets me into trouble with metaclass inheritance:

import flask
import flask_sqlalchemy


app = flask.Flask(__name__)
app.config.from_mapping(CONFIG)

db = flask_sqlalchemy.SQLAlchemy(model_class=BaseModel)

# TypeError: metaclass conflict: the metaclass of a derived class must be a (non-strict) 
# subclass of the metaclasses of all its bases

which is fair enough...

I did try few things from Advanced Customization but so far came empty handed.

Can Flask-SQLAlchemy support this "new" declarative models? Should it? Maybe avoid this problem by somehow replacing metaclass magic with __init_subclass__ from PEP 487 - Simpler customisation of class creation