OperationalError no such table in Flask with SQLAlchemy

Question

run.py

if __name__ == '__main__':
    config() 
    app.run()

main.py

import database

app = Flask(__name__)

def config():
    app.config.from_object('config.DevConfig')

    # Run SQLAlchemy _that uses app.config_ and add entities if in DEBUG mode
    database.init_db(app)

    import blueprints.auth
    app.register_blueprint(blueprints.auth.auth)

database.py

db = None

def init_db(app):
    global db
    db = SQLAlchemy(app)

    from models import User, Interest, Event

    if app.config['DEBUG']:
        print 'Recreating all db'
        db.create_all() # I DO create everything
        print 'Loading test data'
        ... (here I add some Users and etc. and everything works fine - tests pass)

models.py

from database import db

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)
...

blueprints/auth.py

from models import User

auth = Blueprint('auth', __name__)

@auth.route('/')
def index():
    return str(User.query.get(1).interests)

And so I get

OperationalError: (OperationalError) no such table: user u'SELECT user.id AS user_id, user.username AS user_username, user.email AS user_email, user.passhash AS user_passhash, user.vk_page AS user_vk_page \nFROM user \nWHERE user.id = ?' (1,)

What am I doing wrong?

Did the table user actually end up being created in the database? Have you checked with another program? — Mark Hildreth
– Mark Hildreth, Commented Feb 13, 2014 at 22:33
db.create_all() is only executed in debug mode. Is that intentional? — dnll
– dnll, Commented Feb 14, 2014 at 13:53

Harsha Laxman · Accepted Answer · 2020-04-07 17:20:29Z

26

For anyone trying to use an in memory database:

from sqlalchemy import create_engine
from sqlalchemy.pool import StaticPool

engine = create_engine(
    "sqlite://", 
    connect_args={"check_same_thread": False}, 
    poolclass=StaticPool
)

answered Apr 7, 2020 at 17:20

Harsha Laxman

5311 gold badge9 silver badges23 bronze badges

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

4 Comments

Carnifex Over a year ago

You're the man. This is I think the actual answer for having an in memory DB, instead of just switching to a file.

Matthias Fischer Over a year ago

Oh my god, you're the guy. poolclass=StaticPool made the difference for me. Thanks.

chrisinmtown Over a year ago

I know this is old, still would you please update your answer to explain the poolclass argument, or point to the doc? Thanks in adv.

PeterB Mar 21 at 23:28

Wonderful solution. Clarification: check_same_thread=false - a connection may be accessed in multiple threads, no ProgrammingError raised StaticPool: A pool of exactly one connection, used for all request As for the documentation: sqlite3 - check_same_thread: docs.python.org/3/library/sqlite3.html sqlalchemy - poolclass - docs.sqlalchemy.org/en/20/core/…

Ben Usman · Accepted Answer · 2014-02-17 11:33:26Z

16

There were few things I had to change to make everything work.

replace DATABASE_URI with SQLALCHEMY_DATABASE_URI parametr in config
replace :memory: sqlite address with /tmp/test.db

Now it works fine.

answered Feb 17, 2014 at 11:33

Ben Usman

8,4776 gold badges48 silver badges66 bronze badges

5 Comments

Skylar Saveland Over a year ago

+1000 ... geez, that was frustrating. db.create_all() is $@#&*^#* with in-memory sqlite ... ok ...

Dr. Jan-Philip Gehrcke Over a year ago

Explanation for this behavior: gehrcke.de/2015/05/…

sudosensei Over a year ago

I understand how to complete the first part of the this answer, but I'm not sure what you mean by the second part. I assume the :memory: address needs set equal to SQLALCHEMY_DATABASE_URI, but I am not sure how to do that. What do I need to append to my config.py file?

Ben Usman Over a year ago

@sudosensei it just means, don't use in-memory storage :) if you used :memory:before, don't. If you did not, you are good.

sudosensei Over a year ago

My issue was actually something different altogether. Thank you for the quick reply though!

roxton · Accepted Answer · 2025-07-04 13:21:35Z

To use an in-memory SQLite database without changing the create_engine parameters, use the following connection string in a URI format:

sqlite:///file::memory:?cache=shared&uri=true

Example:

from sqlalchemy import create_engine

engine = create_engine(
    "sqlite:///file::memory:?cache=shared&uri=true",
    echo=True,
    future=True
)

Notes:

See what SQLite documentation has to say about in-memory databases and shared cache. In short, in order for the same in-memory database to be accessible across multiple connections within the same process, the connection string must use the file: URI syntax - a simple :memory: connection string won't work, even if we make it :memory:?cache=shared.

Additionally, SQLAlchemy wants us to append uri=true in the end of such URI connection strings.

Collectives™ on Stack Overflow

OperationalError no such table in Flask with SQLAlchemy

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related