Flask and SQLAlchemy db.session.commit not working

Question

When I add a test user to the User table, it adds fine and it is fine even when I execute db.session.add(u), but it throws error when I execute db.session.commit().

from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Integer(120), unique=True)
    email = db.Column(db.Integer(120), unique=True)
    configkey = db.Column(db.Integer(200), unique=True)
    dob = db.Column(db.DateTime)
    country = db.Column(db.Integer(120))
    friends = db.relationship('Friend', backref = 'friendof', lazy = 'dynamic')


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

class Friend(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Integer(120), unique=True)
    email = db.Column(db.Integer(120), unique=True)
    dob = db.Column(db.DateTime)
    country = db.Column(db.Integer(120))
    lastmessage = db.Column(db.Integer(1500))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))


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

--Edit, including the error message in Python console:

>>> u = models.User(name="sd", email="e")                                                                                                                      
>>> db.session.add(u)
>>> db.session.commit()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", line 149, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 721, in commit
    self.transaction.commit()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 354, in commit
    self._prepare_impl()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 334, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1818, in flush
    self._flush(objects)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1936, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 58, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1900, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 372, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 525, in execute
    uow
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 64, in save_obj
    table, insert)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 569, in _emit_insert_statements
    execute(statement, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) no such table: user u'INSERT INTO user (name, email, configkey, dob, country) VALUES (?, ?, ?, ?, ?)' ('sd'
, 'e', None, None, None)

you didn't do db.create_all() before running your app.

corvid
– corvid

2014-06-25 14:20:23 +00:00
Commented Jun 25, 2014 at 14:20 — corvid
– corvid, Commented Jun 25, 2014 at 14:20

pmccallum · Accepted Answer · 2014-06-25 09:31:50Z

5

I'm looking at your stack trace (specifically the last line) and it looks as though your table doesn't exist yet.

Before you can use your new models with your database the tables and columns must be present. The good news is that SQLAlchemy can do this for you.

Try calling the following from anywhere within your program:

# Creates the schema on the database
db.create_all()

Just be sure to remove this when you're done!

Ideally you'd have another file you call from the command line that imports your flask app and then runs this command. For example...

# Replace these with your main file and the name of your flask object
from myapp import app 
# And this one with the name of your database file and object
from database import db 

db.create_all()

print "Database ready!"

Edit:
The db.create_all() command won't modify existing tables, similarly tables won't automagically change themselves after you've run db.create_all(). You can however for example, create a new table and then re-run the function and have it appear.

To properly handle upgrades to your database though, I recommend looking into Flask-Migrate, especially if your app will eventually be used in a production environment.

The quick and dirty method I use during initial development is to just delete the database and create it again if the changes are too large.

edited Jun 25, 2014 at 9:31

answered Jun 25, 2014 at 7:52

pmccallum

8087 silver badges14 bronze badges

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

4 Comments

avinoth Over a year ago

Thanks a lot, It worked just flawlessly.. just a small doubt, DO I have to execute the command everytime I change the model, and migrate the database or once is enough??

pmccallum Over a year ago

Hi Vinoth, glad to hear it worked! I've updated my answer to give you some more information. Please mark it as accepted if you found it useful. Thanks!

doog abides Over a year ago

You should look into Alembic to handle your migrations. There is also a flask-alembic.

ChildhoodAndy Over a year ago

Damn it! I missed db.create_all() statement.

Raihan Raphy · Accepted Answer · 2025-06-28 08:30:36Z

0

First add these codes as you may remark:

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(debug=True)
    
#Better way to avoid powershell >>> $env:FLASK_APP = "application.py" 
# >>> flask shell
@app.shell_context_processor
def make_shell_context():
    return {'db': db, 'Drink': Drink}


Then Follow the commands with your own application: 
#flask shell
# >>> from application import db
# >>> db.create_all()
# >>> from application import Drink
# >>> db.session.add(Drink(name='mango', description="good"))
# >>> db.session.commit()

answered Jun 28 at 8:30

Raihan Raphy

92 bronze badges

Collectives™ on Stack Overflow

Flask and SQLAlchemy db.session.commit not working

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related