I am using SQlAlchemy in my web project. What should I use - `scoped_session(sessionmaker())` or plain `sessionmaker()` - and why? Or should I use something else? 

    ## model.py
    from sqlalchemy import *
    from sqlalchemy.orm import *
    
    engine = create_engine(&#39;mysql://dbUser:dbPassword@dbServer:dbPort/dbName&#39;,
    pool_recycle=3600, echo=False)
    metadata = MetaData(engine)
    Session = scoped_session(sessionmaker())
    Session.configure(bind=engine)
    user = Table(&#39;user&#39;, metadata, autoload=True)

    class User(object):
	pass

    usermapper = mapper(User, user)

    ## some other python file called abc.py
    from models import *

    def getalluser():
       session = Session()	
       session.query(User).all()
       session.flush()
       session.close()

    ## onemore file defg.py
    from models import *

    def updateuser():
       session = Session()	
       session.query(User).filter(User.user_id == &#39;4&#39;).update({User.user_lname: &#39;villkoo&#39;})
       session.commit()
       session.flush()
       session.close()

I create a `session = Session()` object for each request and I close it. Am I doing the right thing or is there a better way to do it?



scoped_session(sessionmaker()) or plain sessionmaker() in sqlalchemy?

I am trying to write a bulk upsert in python using the SQLAlchemy module (not in SQL!).

I am getting the following error on a SQLAlchemy add:

    sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint &quot;posts_pkey&quot;
    DETAIL:  Key (id)=(TEST1234) already exists.

I have a table called `posts` with a primary key on the `id` column.

In this example, I already have a row in the db with `id=TEST1234`. When I attempt to `db.session.add()` a new posts object with the `id` set to `TEST1234`, I get the error above. I was under the impression that if the primary key already exists, the record would get updated. 

**How can I upsert with Flask-SQLAlchemy based on primary key alone? Is there a simple solution?**

If there is not, I can always check for and delete any record with a matching id, and then insert the new record, but that seems expensive for my situation, where I do not expect many updates.




SQLAlchemy - performing a bulk upsert (if exists, update, else insert) in postgresql

I have 3 tables: User, Community, community_members (for relationship many2many of users and community). 

I create this tables using Flask-SQLAlchemy:

    community_members = db.Table(&#39;community_members&#39;,
                    db.Column(&#39;user_id&#39;, db.Integer, db.ForeignKey(&#39;user.id&#39;)),
                    db.Column(&#39;community_id&#39;, db.Integer, db.ForeignKey(&#39;community.id&#39;)),
                    )


    class Community(db.Model):
        __tablename__ = &#39;community&#39;
    
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(100), nullable=False, unique=True)
        members = db.relationship(User, secondary=community_members,
                                backref=db.backref(&#39;community_members&#39;, lazy=&#39;dynamic&#39;))


Now I want add additional field to community_members like this:

    community_members = db.Table(&#39;community_members&#39;,
                    db.Column(&#39;id&#39;, db.Integer, primary_key=True),
                    db.Column(&#39;user_id&#39;, db.Integer, db.ForeignKey(&#39;user.id&#39;)),
                    db.Column(&#39;community_id&#39;, db.Integer, db.ForeignKey(&#39;community.id&#39;)),
                    db.Column(&#39;time_create&#39;, db.DateTime, nullable=False, default=func.now()),
                    )


And now in python shell I can do this:

create community:

    &gt; c = Community()
    &gt; c.name = &#39;c1&#39;
    &gt; db.session.add(c)
    &gt; db.session.commit()

add members to community:

    &gt; u1 = User.query.get(1)
    &gt; u2 = User.query.get(2)
    &gt; c.members.append(u1)
    &gt; c.members.append(u2)
    &gt; db.session.commit()

    &gt; c.members
    [&lt;User 1&gt;, &lt;User 2&gt;]


Ok, this works.

But how now I can get time_create of community_members table?

SQLAlchemy ManyToMany secondary table with additional fields

How do you execute raw SQL in SQLAlchemy?

I have a python web app that runs on flask and interfaces to the database through SQLAlchemy. 

I need a way to run the raw SQL. The query involves multiple table joins along with Inline views. 

I&#39;ve tried:

    connection = db.session.connection()
    connection.execute( &lt;sql here&gt; )

But I keep getting gateway errors.

How to execute raw SQL in Flask-SQLAlchemy app

Many examples for Flask apps that I have seen have the models stored directly in the main app file (http://pythonhosted.org/Flask-SQLAlchemy/quickstart.html, http://maximebf.com/blog/2012/10/building-websites-in-python-with-flask/). Other ones (http://flask.pocoo.org/docs/patterns/sqlalchemy/) have a &quot;models.py&quot; file in which models are placed.

How can I have my Flask app import models from separate files, e.x. &quot;User.py&quot;? When I try creating a User.py file with these contents:

    from app import db

    class User(db.Model):
        [...]

I get the following error:

    File &quot;/Users/stackoverflow/myapp/models/User.py&quot;, line 1, in &lt;module&gt;
    from app import db
    ImportError: No module named app

When I insert `from models import User` in my module file.

Separate SQLAlchemy models by file in Flask

I am using Flask-SQLAlchemy and Blueprints and I cannot help myself from using circular imports. 
I know I can write imports inside functions and make it work but it sounds nasty, I&#39;d like to confirm with the community if there is a better way to do this.

The problem is I have a module (blueprints.py) where I declare the database and import the blueprints but those blueprints need to import the database declaration at the same time.

This is the code (excerpt of the important parts):

##application.apps.people.views.py
    from application.blueprints import db
    
    people = Blueprint(&#39;people&#39;, __name__,
                     template_folder=&#39;templates&#39;,
                     static_folder=&#39;static&#39;)

    class User(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        username = db.Column(db.String(80), unique=True)

    @people.route(&#39;/all&#39;)
    def all():
        users = User.query.all()

##application.blueprints.py
    from application.apps.people.views import people

    app = Flask(__name__)
    app.config[&#39;SQLALCHEMY_DATABASE_URI&#39;] = &#39;sqlite:////tmp/test.db&#39;
    db = SQLAlchemy(app)
    app.register_blueprint(people, url_prefix=&#39;/people&#39;)



I have read the documentation and the questions I found on this topic, but I still cannot find the answer I am looking for.
I have found this chapter (https://pythonhosted.org/Flask-SQLAlchemy/contexts.html) where it suggest to put the initialization code inside a method but the circular import still persist.

**Edit**
I fixed the problem using the pattern [Application Factory][1]


  [1]: http://flask.pocoo.org/docs/patterns/appfactories/

Circular import of db reference using Flask-SQLAlchemy and Blueprints

I recently switch to Celery 3.0. Before that I was using [Flask-Celery][1] in order to integrate Celery with Flask. Although it had many issues like hiding some powerful Celery functionalities but it allowed me to use the full context of Flask app and especially Flask-SQLAlchemy.

In my background tasks I am processing data and the SQLAlchemy ORM to store the data. The maintainer of Flask-Celery has dropped support of the plugin. The plugin was pickling the Flask instance in the task so I could have full access to SQLAlchemy.

I am trying to replicate this behavior in my tasks.py file but with no success. Do you have any hints on how to achieve this?


  [1]: https://github.com/ask/flask-celery/

How to use Flask-SQLAlchemy in a Celery task

Assume table has three columns: `username`, `password` and `no_of_logins`.

When user tries to login, it&#39;s checked for an entry with a query like

    user = User.query.filter_by(username=form.username.data).first()

If password matches, he proceeds further. What I would like to do is count how many times the user logged in. Thus whenever he successfully logs in, I would like to increment the `no_of_logins` field and store it back to the user table. I&#39;m not sure how to run update query with SqlAlchemy.

How to update SQLAlchemy row entry?

I have `users` table in my MySql database. This table has `id`, `name` and `age` fields.

How can I delete some record by `id`?

Now I use the following code:

    user = User.query.get(id)
    db.session.delete(user)
    db.session.commit()

But I don&#39;t want to make any query before delete operation. Is there any way to do this? I know, I can use `db.engine.execute(&quot;delete from users where id=...&quot;)`, but I would like to use `delete()` method.

How to delete a record by id in Flask-SQLAlchemy

Every time I run my app that uses Flask-SQLAlchemy I get the following warning that the `SQLALCHEMY_TRACK_MODIFICATIONS` option will be disabled.

    /home/david/.virtualenvs/flask-sqlalchemy/lib/python3.5/site-packages/flask_sqlalchemy/__init__.py:800: UserWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True to suppress this warning.
      warnings.warn(&#39;SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True to suppress this warning.&#39;)

I tried to find out what this option does, but the Flask-SQLAlchemy documentation isn&#39;t clear about what uses this tracking.

&gt; `SQLALCHEMY_TRACK_MODIFICATIONS`
&gt; 
&gt; If set to True (the default) Flask-SQLAlchemy will track modifications of objects and emit signals. This requires extra memory and can be disabled if not needed.

How do I find out if my project requires `SQLALCHEMY_TRACK_MODIFICATIONS = True` or if I can safely disable this feature and save memory on my server?

How do I know if I can disable SQLALCHEMY_TRACK_MODIFICATIONS?

How do I specify the column that I want in my query using a model (it selects all columns by default)? I know how to do this with the sqlalchmey session: `session.query(self.col1)`, but how do I do it with with models?  I can&#39;t do `SomeModel.query()`. Is there a way?

Flask SQLAlchemy query, specify column names

How can I update a row&#39;s information? 

For example I&#39;d like to alter the name column of the row that has the id 5. 


Flask-SQLalchemy update a row&#39;s information

Is there a solution converting a SQLAlchemy `&lt;Query object&gt;` to a pandas DataFrame?

Pandas has the capability to use `pandas.read_sql` but this requires use of raw SQL. I have two reasons for wanting to avoid it: 
1) I already have everything using the ORM (a good reason in and of itself) and 
2) I&#39;m using python lists as part of the query, e.g.: 
&gt; `db.session.query(Item).filter(Item.symbol.in_(add_symbols)` where `Item` is my model class and `add_symbols` is a list). This is the equivalent of SQL `SELECT ... from ... WHERE ... IN`. 

Is anything possible?

SQLAlchemy ORM conversion to pandas DataFrame

I want to structure my Flask app something like:

    ./site.py
    ./apps/members/__init__.py
    ./apps/members/models.py

`apps.members` is a Flask Blueprint.

Now, in order to create the model classes I need to have a hold of the app, something like:

    # apps.members.models
    from flask import current_app
    from flaskext.sqlalchemy import SQLAlchemy

    db = SQLAlchemy(current_app)

    class Member(db.Model):
        # fields here
        pass

But if I try and import that model into my Blueprint app, I get the dreaded `RuntimeError: working outside of request context`. How can I get a hold of my app correctly here? Relative imports might work but they&#39;re pretty ugly and have their own context issues, e.g:

    from ...site import app

    # ValueError: Attempted relative import beyond toplevel package

    

Flask-SQLAlchemy import/context issue

I&#39;m trying to jsonify a SQLAlchemy result set in Flask/Python.

The Flask mailing list suggested the following method http://librelist.com/browser//flask/2011/2/16/jsonify-sqlalchemy-pagination-collection-result/#04a0754b63387f87e59dda564bde426e :

    return jsonify(json_list = qryresult)

However I&#39;m getting the following error back:

    TypeError: &lt;flaskext.sqlalchemy.BaseQuery object at 0x102c2df90&gt; 
    is not JSON serializable

What am I overlooking here? 

I have found this question: https://stackoverflow.com/questions/5022066/how-to-serialize-sqlalchemy-result-to-json which seems very similar however I didn&#39;t know whether Flask had some magic to make it easier as the mailing list post suggested.



Edit: for clarification, this is what my model looks like

    class Rating(db.Model):
    
        __tablename__ = &#39;rating&#39;
    
        id = db.Column(db.Integer, primary_key=True)
        fullurl = db.Column(db.String())
        url = db.Column(db.String())
        comments = db.Column(db.Text)
        overall = db.Column(db.Integer)
        shipping = db.Column(db.Integer)
        cost = db.Column(db.Integer)
        honesty = db.Column(db.Integer)
        communication = db.Column(db.Integer)
        name = db.Column(db.String())
        ipaddr = db.Column(db.String())
        date = db.Column(db.String())
    
        def __init__(self, fullurl, url, comments, overall, shipping, cost, honesty, communication, name, ipaddr, date):
            self.fullurl = fullurl
            self.url = url
            self.comments = comments
            self.overall = overall
            self.shipping = shipping
            self.cost = cost
            self.honesty = honesty
            self.communication = communication
            self.name = name
            self.ipaddr = ipaddr
            self.date = date


jsonify a SQLAlchemy result set in Flask

I&#39;m trying to connect to a Postgres database with SQLAlchemy. I&#39;ve installed psycopg2. However, I get the error `sqlalchemy.exc.NoSuchModuleError: Can&#39;t load plugin: sqlalchemy.dialects:postgres`. How do I configure SQLAlchemy to connect to PostgreSQL?

```python
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config[&quot;SQLALCHEMY_DATABASE_URI&quot;] = &quot;postgres://username@localhost:5432/template1&quot;

db = SQLAlchemy(app)
```

sqlalchemy.exc.NoSuchModuleError: Can&#39;t load plugin: sqlalchemy.dialects:postgres

I am new in both flask and sqlalchemy, I just start working on a flask app, and I am using sqlalchemy for now. I was wondering if there is any significant benefit I can get from using flask-sqlalchemy vs sqlalchemy. I could not find enough motivations in &lt;http://packages.python.org/Flask-SQLAlchemy/index.html&gt; or maybe I did not understand the value!! I would appreciate your clarifications.

flask-sqlalchemy or sqlalchemy

I&#39;m using Flask-SQLAlchemy to query from a database of users; however, while

    user = models.User.query.filter_by(username=&quot;ganye&quot;).first()

will return

    &lt;User u&#39;ganye&#39;&gt;

doing

    user = models.User.query.filter_by(username=&quot;GANYE&quot;).first()

returns

    None

I&#39;m wondering if there&#39;s a way to query the database in a case insensitive way, so that the second example will still return

    &lt;User u&#39;ganye&#39;&gt;

Case Insensitive Flask-SQLAlchemy Query

How do I delete all rows in a single table using Flask-SQLAlchemy?

Looking for something like this: 

    &gt;&gt;&gt; users = models.User.query.all()
    &gt;&gt;&gt; models.db.session.delete(users)
    
    # but it errs out: UnmappedInstanceError: Class &#39;__builtin__.list&#39; is not mapped

Flask-SQLAlchemy how to delete all rows in a single table

The [quickstart tutorial][1] for the Flask-SQLAlchemy plugin instructs users to create table models inheriting the `db.Model` class, e.g.
    
    app = Flask(__main__)
    db = SQLAlchemy(app)
    class Users(db.Model):
        __tablename__ = &#39;users&#39;
        ...

However, the [SQLAlchemy tutorial][2] and the bottle-SQLAlchemy [README][3] both suggest that table models inherit a `Base` instantiated from `declarative_base()`.

    Base = declarative_base()
    class Users(Base):
        __tablename__ = &#39;users&#39;
        ...

What is the difference between these two approaches?

  [1]: http://pythonhosted.org/Flask-SQLAlchemy/quickstart.html
  [2]: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html
  [3]: https://github.com/iurisilvio/bottle-sqlalchemy

What is the difference between the declarative_base() and db.Model?

I am trying to make a many to many relation here in **Flask-SQLAlchemy**, but it seems that I don&#39;t know how to fill the *&quot;many to many identifier database&quot;*. Could you please help me understand what I am doing wrong and how it is supposed to look?

&lt;!-- language: lang-python --&gt;

    class User(db.Model):
        __tablename__ = &#39;users&#39;
        user_id = db.Column(db.Integer, primary_key=True)
        user_fistName = db.Column(db.String(64))
        user_lastName = db.Column(db.String(64))
        user_email = db.Column(db.String(128), unique=True)


    class Class(db.Model):
        __tablename__ = &#39;classes&#39;
        class_id = db.Column(db.Integer, primary_key=True)
        class_name = db.Column(db.String(128), unique=True)

and then my identifier database:

    student_identifier = db.Table(&#39;student_identifier&#39;,
        db.Column(&#39;class_id&#39;, db.Integer, db.ForeignKey(&#39;classes.class_id&#39;)),
        db.Column(&#39;user_id&#39;, db.Integer, db.ForeignKey(&#39;users.user_id&#39;))
    )

so far it looks like this when I try to insert the data into the database. 


    # User
    user1 = User(
                user_fistName=&#39;John&#39;,
                user_lastName=&#39;Doe&#39;,
                user_email=&#39;john@doe.es&#39;)
    
    user2 = User(
                user_fistName=&#39;Jack&#39;,
                user_lastName=&#39;Doe&#39;,
                user_email=&#39;jack@doe.es&#39;)
    
    user3 = User(
                user_fistName=&#39;Jane&#39;,
                user_lastName=&#39;Doe&#39;,
                user_email=&#39;jane@doe.es&#39;)

    db.session.add_all([user1, user2, user3])
    db.session.commit()

    # Class
    cl1 = Class(class_name=&#39;0A&#39;)
    cl2 = Class(class_name=&#39;0B&#39;)
    cl3 = Class(class_name=&#39;0C&#39;)
    cl4 = Class(class_name=&#39;Math&#39;)
    cl5 = Class(class_name=&#39;Spanish&#39;)
    db.session.add_all([cl1, cl2, cl3, cl4, cl5])
    db.session.commit()

Now my problem is, how do I add to the many to many database, since I really can&#39;t create a &#39;student_identifier&#39; object? If I could it could perhaps have looked like this:

    # Student Identifier
    sti1  = StiClass(class_id=cl1.class_id, class_name=user1.user_id)
    sti2  = StiClass(class_id=cl3.class_id, class_name=user1.user_id)
    sti3  = StiClass(class_id=cl4.class_id, class_name=user1.user_id)
    sti4  = StiClass(class_id=cl2.class_id, class_name=user2.user_id)
    db.session.add_all([sti1, sti2, sti3, sti4])
    db.session.commit()

How I am supposed to insert into a many to many table with ORM?
    

Flask sqlalchemy many-to-many insert data

I&#39;m trying to create a web app with Flask that lets a user upload a file and serve them to another user. Right now, I can upload the file to the *upload_folder* correctly. But I can&#39;t seem to find a way to let the user download it back.

I&#39;m storing the name of the filename into a database. 

I have a view serving the database objects. I can delete them too.

    @app.route(&#39;/dashboard&#39;, methods=[&#39;GET&#39;, &#39;POST&#39;])
    def dashboard():

        problemes = Probleme.query.all()

        if &#39;user&#39; not in session:
            return redirect(url_for(&#39;login&#39;))

        if request.method == &#39;POST&#39;:
            delete = Probleme.query.filter_by(id=request.form[&#39;del_button&#39;]).first()
            db.session.delete(delete)
            db.session.commit()
            return redirect(url_for(&#39;dashboard&#39;))

        return render_template(&#39;dashboard.html&#39;, problemes=problemes)


In my HTML I have:

    &lt;td&gt;&lt;a href=&quot;{{ url_for(&#39;download&#39;, filename=probleme.facture) }}&quot;&gt;Facture&lt;/a&gt;&lt;/td&gt;

and a download view :

    @app.route(&#39;/uploads/&lt;path:filename&gt;&#39;, methods=[&#39;GET&#39;, &#39;POST&#39;])
    def download(filename):
        return send_from_directory(directory=app.config[&#39;UPLOAD_FOLDER&#39;], filename=filename)

But it&#39;s returning : 

&gt; Not Found

The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again.

I just want to link the filename to the object and let the user download it (For every object in the same view) 

Flask Download a File

I&#39;m trying to separate my Flask-SQLAlchemy models into separate files. When I try to run `db.create_all()` I get `No application found. Either work inside a view function or push an application context.`

`shared/db.py`:

    from flask_sqlalchemy import SQLAlchemy
    
    db = SQLAlchemy()

`app.py`:

    from flask import Flask
    from flask_sqlalchemy import SQLAlchemy
    from shared.db import db
    
    app = Flask(__name__)
    app.config[&#39;SQLALCHEMY_DATABASE_URI&#39;] = &#39;My connection string&#39;
    db.init_app(app)

`user.py`:

    from shared.db import db
    
    class User(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        email_address = db.Column(db.String(300), unique=True, nullable=False)
        password = db.Column(db.Text, nullable=False)

&#39;No application found. Either work inside a view function or push an application context.&#39;

I already have an existing Database that has a lot of tables and a lot of data in `MySQL`. I intend to create a `Flask` app and use sqlalchemy along with it. Now I asked out on irc and looked around on google and tried the following ideas:

**First** I used [sqlacodegen][1] to generate the models from my `DB`. But then I was confused about it a little and looked some more. And I found [this][2].

This looked like an elegant solution.

So **Second**, I rewrote my `models.py` according to the solution there and now I am even more confused. I am looking for the best approach to build this flask app along with the already existing DB. 

I looked into the flask documentation but didnt really get any help for a project with an already existing db. There is a lot of good stuff for creating something from scratch, creating the db and all. But I am really confused.

Please Note that its my first day with `Flask`, but I have experience with `Django`, so the basic concepts are not a hurdle. I need some guidance in choosing the best approach for this usecase. A detailed explanation would be greatly appreciated. By detailed I definitely do not expect someone to write all the code and spoon feed me on this, but just enough to get me started, that is integrate this db seamlessly into `flask` via `sqlalchemy`. Note my DB is in `MySQL`.


  [1]: https://pypi.python.org/pypi/sqlacodegen
  [2]: http://flask.pocoo.org/mailinglist/archive/2012/5/23/flask-sqlalchemy-with-pre-existing-database/#9974419419ee1960c88c98689f6aee9d

How to build a flask application around an already existing database?

I have the following query:

    profiles = session.query(profile.name).filter(and_(profile.email == email, profile.password == password_hash))

How do I check if there is a row and how do I just return the first (should only be one if there is a match)?

Getting first row from sqlalchemy

I&#39;m trying to create a &quot;modular application&quot; in Flask using Blueprints.

When creating models, however, I&#39;m running into the problem of having to reference the app in order to get the `db`-object provided by Flask-SQLAlchemy. I&#39;d like to be able to use some blueprints with more than one app (similar to how Django apps can be used), so this is not a good solution.*

* It&#39;s possible to do a switcharoo, and have the Blueprint create the `db` instance, which the app then imports together with the rest of the blueprint. But then, any other blueprint wishing to create models need to import from **that** blueprint instead of the app.

My questions are thus:

* Is there a way to let Blueprints define models without any awareness of the app they&#39;re being used in later -- and have several Blueprints come together? By this, I mean having to import the app module/package from your Blueprint.
* Am I wrong from the outset? Are Blueprints not meant to be independent of the app and be redistributable (&#224; la Django apps)?
  * If not, then what pattern *should* you use to create something like that? Flask extensions? Should you simply not do it -- and maybe centralize all models/schemas &#224; la Ruby on Rails?

&gt; _Edit_: I&#39;ve been thinking about this myself now, and this might be more related to SQLAlchemy than Flask because you have to have the ``declarative_base()`` when declaring models. And *that&#39;s* got to come from somewhere, anyway!
&gt;
&gt; Perhaps the best solution is to have your project&#39;s schema defined in one place and spread it around, like Ruby on Rails does. Declarative SQLAlchemy class definitions are really more like schema.rb than Django&#39;s models.py. I imagine this would also make it easier to use migrations (from [alembic](http://pypi.python.org/pypi/alembic/) or [sqlalchemy-migrate](http://code.google.com/p/sqlalchemy-migrate/)).

---

I was asked to provide an example, so let&#39;s do something simple: Say I have a blueprint describing &quot;flatpages&quot; -- simple, &quot;static&quot; content stored in the database. It uses a table with just shortname (for URLs), a title and a body. This is ``simple_pages/__init__.py``:

    from flask import Blueprint, render_template
    from .models import Page

    flat_pages = Blueprint(&#39;flat_pages&#39;, __name__, template_folder=&#39;templates&#39;)

    @flat_pages.route(&#39;/&lt;page&gt;&#39;)
    def show(page):
        page_object = Page.query.filter_by(name=page).first()
        return render_template(&#39;pages/{}.html&#39;.format(page), page=page_object)

Then, it would be nice to let this blueprint define its own model (this in ``simple_page/models.py``):

    # TODO Somehow get ahold of a `db` instance without referencing the app
    # I might get used in!

    class Page(db.Model):
        name = db.Column(db.String(255), primary_key=True)
        title = db.Column(db.String(255))
        content = db.Column(db.String(255))

        def __init__(self, name, title, content):
            self.name = name
            self.title = title
            self.content = content

---

This question is related to:

* https://stackoverflow.com/questions/9692962/flask-sqlalchemy-import-context-issue
* https://stackoverflow.com/questions/6089020/whats-your-folder-layout-for-a-flask-app-divided-in-modules?rq=1

And various others, but all replies seem to rely on import the app&#39;s `db` instance, or doing the reverse. The [&quot;Large app how to&quot;](https://github.com/mitsuhiko/flask/wiki/Large-app-how-to) wiki page also uses the &quot;import your app in your blueprint&quot; pattern.

&lt;sub&gt;* Since the official documentation shows how to create routes, views, templates and assets in a Blueprint without caring about what app it&#39;s &quot;in&quot;, I&#39;ve assumed that Blueprints should, in general, be reusable across apps. However, this modularity doesn&#39;t seem *that* useful without also having independent models.&lt;/sub&gt;

&lt;sub&gt;Since Blueprints can be hooked into an app more than once, it might simply be the wrong approach to have models in Blueprints?&lt;/sub&gt;

Using Flask-SQLAlchemy in Blueprint models without reference to the app

I have a site that I&#39;ve built with Flask SQLAlchemy and SQLite, and need to switch to MySQL. I have migrated the database itself and have it running under MySQL, but 

 1. Can&#39;t figure out how to connect to the MySQL database (that is, what the `SQLALCHEMY_DATABASE_URI` should be) and
 2. Am unclear if any of my existing SQLAlchemy SQLite code will work with MySQL.

I suspect that (1) is fairly simple and just a matter of being shown how to map, for example, the contents of the connection dialog I use in my MySQL database tool to an appropriately formatted URL. But I&#39;m worried about (2), I had assumed that SQLAlchemy provided an abstraction layer so that [simple SQLAlchemy code such as][1] 

    from flask import Flask
    from flask.ext.sqlalchemy import SQLAlchemy
    
    app = Flask(__name__)
    app.config[&#39;SQLALCHEMY_DATABASE_URI&#39;] = &#39;sqlite:////tmp/test.db&#39;
    db = SQLAlchemy(app)
    
    
    class User(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        username = db.Column(db.String(80), unique=True)
        email = db.Column(db.String(120), unique=True)
    
        def __init__(self, username, email):
            self.username = username
            self.email = email
    
        def __repr__(self):
            return &#39;&lt;User %r&gt;&#39; % self.username
    
    admin = User(&#39;admin&#39;, &#39;admin@example.com&#39;)
    
    db.session.add(admin)
    
    User.query.all()
    
    User.query.filter_by(username=&#39;admin&#39;).first()

wold work without any modifications other than an appropriate change to the database URI; but the [examples I&#39;ve found][2] for using SQLAlchemy with MySQL seem to use a completely different API.

Can I (2) migrate my Flask SQLAlchemy code to work with a MySQL database by simply changing the database URI and if so (1) what should that URI be?


  [1]: https://pythonhosted.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application
  [2]: http://pylonsdevelopment.blogspot.com/2010/07/connecting-to-mysql-database-using.html

Switching from SQLite to MySQL with Flask SQLAlchemy

I&#39;m unable to find a module in python ,though easy_install says its already installed.
Any idea how to resolve this isseue? 

    $ python -c &quot;from flaskext.sqlalchemy import SQLAlchemy&quot;
    Traceback (most recent call last):
      File &quot;&lt;string&gt;&quot;, line 1, in &lt;module&gt;
    ImportError: No module named sqlalchemy


    $ python -V
    Python 2.7
    
    
    $ sudo easy_install sqlalchemy
    Searching for sqlalchemy
    Best match: SQLAlchemy 0.7.7
    Adding SQLAlchemy 0.7.7 to easy-install.pth file
    
    Using /usr/lib/python2.7/site-packages
    Processing dependencies for sqlalchemy
    Finished processing dependencies for sqlalchemy

&gt; $ sudo pip install SQLAlchemy --upgrade Requirement already
&gt; up-to-date: SQLAlchemy in /usr/lib/python2.7/site-packages Cleaning
&gt; up...

Though pip says it&#39;s installed.But I can&#39;t find them in sys.path output.

    
    $ sudo python -c &quot;import sys;print sys.path&quot; [&#39;&#39;,
    &#39;/usr/lib/python2.7/site-packages/Flask_SQLAlchemy-0.15-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/Flask-0.8-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/Jinja2-2.6-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/Werkzeug-0.8.3-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/Flask_WTF-0.5.2-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/WTForms-0.6.3-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/Flask_Mail-0.6.1-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/blinker-1.2-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/lamson-1.1-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/python_daemon-1.6-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/nose-1.1.2-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/mock-0.8.0-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/chardet-1.0.1-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/lockfile-0.9.1-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/Flask_FlatPages-0.2-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/Markdown-2.1.1-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/PyYAML-3.10-py2.7-linux-i686.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/uWSGI-1.0.3-py2.7.egg&#39;,
    &#39;/usr/lib/python2.7/site-packages/MySQL_python-1.2.3-py2.7-linux-i686.egg&#39;,
    &#39;/usr/lib/python27.zip&#39;, &#39;/usr/lib/python2.7&#39;,
    &#39;/usr/lib/python2.7/plat-linux2&#39;, &#39;/usr/lib/python2.7/lib-tk&#39;,
    &#39;/usr/lib/python2.7/lib-old&#39;, &#39;/usr/lib/python2.7/lib-dynload&#39;,
    &#39;/usr/lib/python2.7/site-packages&#39;,
    &#39;/usr/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg-info&#39;]


   





ImportError: No module named sqlalchemy

I&#39;m having trouble figuring out how to do a &quot;greater than or equal to&quot; comparison in a query.

I have a model field:

    invoicedate = db.Column(db.Date(), nullable=True, key=&#39;InvoiceDate&#39;)

And i&#39;m trying to do the following filter:

    Invoice.query.filter_by(invoicedate &gt;= date.today()).count()

When I run the view, it keeps throwing the following error:

    NameError: global name &#39;invoicedate&#39; is not defined

What is the correct syntax for a greater than or equal filter in sqlalchemy or flask-sqlalchemy?

Flask-SQLAlchemy - Greater than or equal to

I want my model&#39;s primary key to be an autoincrementing integer. Here is how my model looks like

    class Region(db.Model):
        __tablename__ = &#39;regions&#39;
        id = db.Column(db.Integer, primary_key=True, autoincrement=True)
        name = db.Column(db.String(100))
        parent_id = db.Column(db.Integer, db.ForeignKey(&#39;regions.id&#39;))
        parent = db.relationship(&#39;Region&#39;, remote_side=id, primaryjoin=(&#39;Region.parent_id==Region.id&#39;), backref=&#39;sub-regions&#39;)
        created_at = db.Column(db.DateTime, default=db.func.now())
        deleted_at = db.Column(db.DateTime)

The above code creates my table but does not make `id` autoincrementing. So if in my insert query I miss the `id` field it gives me this error 

&gt; ERROR:  null value in column &quot;id&quot; violates not-null constraint

So I changed the `id` declaration to look like this

    id = db.Column(db.Integer, db.Sequence(&#39;seq_reg_id&#39;, start=1, increment=1),
                   primary_key=True)

Still the same error. What is wrong with the code above?

unable to create autoincrementing primary key with flask-sqlalchemy

Let&#39;s say I have a `User` model with fields `popularity` and `date_created`. I want to do the following query:

    SELECT * FROM user ORDER BY popularity DESC, date_created DESC LIMIT 10

In SQLAlchemy, for a single one this works:

    User.query.order_by(User.popularity.desc()).limit(10).all()

Should I just add another `order_by()`? Or put both `popularity` and `date_created` in my current `order_by()`?

I want `popularity` to have priority on `date_created` for ordering.

How can I do multiple &quot;order_by&quot; in Flask-SQLAlchemy?

When I&#39;m trying to create a database schema migration, I&#39;m getting this weird error. Can you please help me to figure out what&#39;s wrong?    

    $ python app.py db upgrade
    [skipped]
    sqlalchemy.exc.ArgumentError: Mapper Mapper|EssayStateAssociations|essay_associations could not assemble any primary key columns for mapped table &#39;essay_associations&#39;

**My model:**

    class EssayStateAssociations(db.Model):
        __tablename__ = &#39;essay_associations&#39;
       
        application_essay_id = db.Column(
            db.Integer,
            db.ForeignKey(&quot;application_essay.id&quot;),
            primary_key=True),
        theme_essay_id = db.Column(
            db.Integer,
            db.ForeignKey(&quot;theme_essay.id&quot;),
            primary_key=True),
        state = db.Column(db.String, default=&quot;pending&quot;)


Could not assemble any primary key columns for mapped table

I can query my `Seat` table for all seats where there is no invite assigned:

    seats = Seat.query.filter_by(invite=None).all()

However, when querying for all seats that have an invite assigned, I get a `NameError`:

    seats = Seat.query.filter_by(invite!=None).all()

&lt;!-- language: lang-none --&gt;

    NameError: name &#39;invite&#39; is not defined

Here is my `Seat` class:

    class Seat(db.Model):
        id = db.Column(db.Integer, primary_key=True)

        invite_id = db.Column(db.Integer, db.ForeignKey(&#39;invite.id&#39;))
        invite = db.relationship(&#39;Invite&#39;,
            backref=db.backref(&#39;folks&#39;, lazy=&#39;dynamic&#39;))

How can I query for all seats where the owner is not blank?

Flask SQLAlchemy querying a column with &quot;not equals&quot;

I&#39;m working on forum template using Flask. When I attempt creating a new thread in the browser using forms, SQLAlchemy throws an AttributeError. The problem showed up when I tried implementing a one-to-many relationship with Forum-to-Thread and a one-to-many relationship with Thread-to-User.

models.py

    class User(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      username = db.Column(db.String(32), index=True, unique=True)
      password = db.Column(db.String(32), index=True)
      email = db.Column(db.String(120), index=True, unique=True)
      role = db.Column(db.SmallInteger, default=ROLE_USER)
    
      posts = db.relationship(&#39;Post&#39;, backref=&#39;author&#39;, lazy=&#39;dynamic&#39;)

    class Forum(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      title = db.Column(db.String(128))
      description = db.Column(db.Text)
    
      threads = db.relationship(&#39;Thread&#39;, backref=&#39;forum&#39;, lazy=&#39;dynamic&#39;)
    	  
    class Thread(db.Model):
      
      id = db.Column(db.Integer, primary_key=True)
      title = db.Column(db.String(128))
      author= db.Column(db.String(32))
      timestamp = db.Column(db.DateTime)
      forum_id = db.Column(db.Integer, db.ForeignKey(&#39;forum.id&#39;))
    
      posts = db.relationship(&#39;Post&#39;, backref=&#39;thread&#39;, lazy=&#39;dynamic&#39;)
    	
    class Post(db.Model):
      
      id = db.Column(db.Integer, primary_key=True)
      body = db.Column(db.Text)
      timestamp = db.Column(db.DateTime)
      thread_id = db.Column(db.Integer, db.ForeignKey(&#39;thread.id&#39;))
      user_id = db.Column(db.Integer, db.ForeignKey(&#39;user.id&#39;))

All the new posts/threads and handled within views.py

views.py

    @app.route(&#39;/forum/id=&lt;id&gt;/submit&#39;, methods=[&#39;GET&#39;,&#39;POST&#39;])
    @login_required
    def new_thread(id):
      form = ThreadForm()
      forum = Forum.query.filter_by(id=id).first()
      if form.validate_on_submit():
        thread = Thread(title=form.title.data,
                        author=g.user.username,
                        timestamp=datetime.utcnow())
        db.session.add(thread)
        db.session.flush()
        post = Post(body=form.body.data,
                    timestamp=datetime.utcnow(),
                    thread=thread.id,
                    author=g.user.id)
        db.session.add(post)
        db.session.commit()
        flash(&#39;Post successful.&#39;)
        return redirect(url_for(&#39;forum_index&#39;, id=id))
      return render_template(&#39;forum/thread_submit.html&#39;, title=forum.title, form=form) 

AttributeError: &#39;int&#39; object has no attribute &#39;_sa_instance_state&#39;

I have a Flask (v0.10.1) application using Flask-SQLAlchemy (v2.0) and I&#39;m trying to configure Pylint to check it. Running with Python 3.4.2.

First error was:

     Instance of &#39;SQLAlchemy&#39; has no &#39;Table&#39; member (no-member)

And I fixed this one ignoring the check for member attributes on SQLAlchemy:

    ignored-classes=SQLAlchemy

But I&#39;m having a problem with the query member on entities:

    Class &#39;UserToken&#39; has no &#39;query&#39; member (no-member)

Is there any way to fix this issue without having to ignore no-member errors on every query call?

---

Flask bootstrap:

    from flask import Flask
    from flask_sqlalchemy import SQLAlchemy

    db = SQLAlchemy()
    app = Flask(__name__)
    db.init_app(app)
    app.run()

UserToken entity:

    from app import db
    
    class UserToken(db.Model):
        user_token_id = db.Column(db.Integer, primary_key=True, index=True)
        token_auth = db.Column(db.String(64), unique=True, nullable=False, index=True)

The controller:

    from entities import UserToken

    token = UserToken.query.filter(
        UserToken.token_auth == token_hash,
    ).first()

Pylint can&#39;t find SQLAlchemy query member

I&#39;m trying to use alembic to handle local migrations on my project. It worked the first time, but then I needed to delete the folder and restart.(don&#39;t ask why, I just had to) I&#39;m following [this tutorial][1] and I run the command

    python manage.py db init

And it was ok. But when I try to run

    python manage.py db migrate

I&#39;m getting this error:

    alembic.util.CommandError: Can&#39;t locate revision identified by &#39;31b8ab83c7d&#39;

Now, it seems that alembic is looking for a revision that doesn&#39;t exists anymore. There is anyway to make alembic forget that file? Or like restart the comparison from None to -&gt; auto-generated again?  

  [1]: https://realpython.com/blog/python/flask-by-example-part-2-postgres-sqlalchemy-and-alembic/

alembic util command error can&#39;t find identifier

What is the difference between `one` and `first` methods in SQLAlchemy

What is the difference between .one() and .first()

How can I insert some seed data in my first migration? If the migration is not the best place for this, then what is the best practice?

    &quot;&quot;&quot;empty message
    
    Revision ID: 384cfaaaa0be
    Revises: None
    Create Date: 2013-10-11 16:36:34.696069
    
    &quot;&quot;&quot;
    
    # revision identifiers, used by Alembic.
    revision = &#39;384cfaaaa0be&#39;
    down_revision = None
    
    from alembic import op
    import sqlalchemy as sa
    
    
    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_table(&#39;list_type&#39;,
        sa.Column(&#39;id&#39;, sa.Integer(), nullable=False),
        sa.Column(&#39;name&#39;, sa.String(length=80), nullable=False),
        sa.PrimaryKeyConstraint(&#39;id&#39;),
        sa.UniqueConstraint(&#39;name&#39;)
        )
        op.create_table(&#39;job&#39;,
        sa.Column(&#39;id&#39;, sa.Integer(), nullable=False),
        sa.Column(&#39;list_type_id&#39;, sa.Integer(), nullable=False),
        sa.Column(&#39;record_count&#39;, sa.Integer(), nullable=False),
        sa.Column(&#39;status&#39;, sa.Integer(), nullable=False),
        sa.Column(&#39;sf_job_id&#39;, sa.Integer(), nullable=False),
        sa.Column(&#39;created_at&#39;, sa.DateTime(), nullable=False),
        sa.Column(&#39;compressed_csv&#39;, sa.LargeBinary(), nullable=True),
        sa.ForeignKeyConstraint([&#39;list_type_id&#39;], [&#39;list_type.id&#39;], ),
        sa.PrimaryKeyConstraint(&#39;id&#39;)
        )
        ### end Alembic commands ###
    
        # ==&gt; INSERT SEED DATA HERE &lt;==
    
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.drop_table(&#39;job&#39;)
        op.drop_table(&#39;list_type&#39;)
        ### end Alembic commands ###



Creating seed data in a flask-migrate or alembic migration

I have a Flask application which uses Flask-SQLAlchemy to connect to a MySQL database.

I would like to be able to check whether a row is present in a table. How would I modify a query like so to check the row exists:

    db.session.query(User).filter_by(name=&#39;John Smith&#39;)


I found a solution on [this question][1] which uses SQLAlchemy but does not seem to fit with the way Flask-SQLAlchemy works:

    from sqlalchemy.sql import exists    
    print session.query(exists().where(User.email == &#39;...&#39;)).scalar()

Thanks.


  [1]: https://stackoverflow.com/a/13336408/2961662

Flask-SQLAlchemy check if row exists in table

I&#39;m following the flask-sqlalchemy tutorial on [declaring models][1] regarding one-to-many relationship. The example code is as follows:

    class Person(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(50))
        addresses = db.relationship(&#39;Address&#39;, backref=&#39;person&#39;,
                                    lazy=&#39;dynamic&#39;)
    
    class Address(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        email = db.Column(db.String(50))
        person_id = db.Column(db.Integer, db.ForeignKey(&#39;person.id&#39;))

Now I&#39;m wondering how to insert new records into DB using such model. I assume I need a constructor __init__, but I have difficulties to understand how it should be implemented and used. The main problem for me here is that Person depends on Address and Address has ForeignKey to Person, so it should know about the Person in advance.

Plase help me to understand how it should be performed.

Thank you in advance.

  [1]: http://flask-sqlalchemy.pocoo.org/2.1/models/

All Flask Sqlalchemy Solutions on Gang of Coders

Total of 40 Flask Sqlalchemy Solutions