[FIXED] Flask SQLAlquemy db.commit breaks stuff

Issue

I am learning about Rest APIs and Flask. Following a simple tutorial, something doesn’t work and I can’t get it fixed. I create a database and I want to add something to it. My file is called "API.py" and I am using PyCharm. After running the file, I type into the Python Console in Python:

  1. from API import db
  2. from API import Drink
  3. Drink.query.all() (returns an empty list, all good)
  4. drink = Drink(name="x", description="y")
  5. Drink.query.all() (returns an empty list, all good)
  6. db.session.add(drink)
  7. Drink.query.all() (returns the drink, all good)
  8. db.session.commit()
  9. Drink.query.all() – ERROR

I tried several things, but I can’t figure it out. In the tutorial, everything is working just fine. Would you please help me fix this? Below you will find the error message and the code:

Error message:

Traceback (most recent call last):
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\base.py", line 1800, in _execute_context
    context = constructor(
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\default.py", line 1015, in _init_compiled
    self.cursor = self.create_cursor()
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\default.py", line 1386, in create_cursor
    return self.create_default_cursor()
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\default.py", line 1389, in create_default_cursor
    return self._dbapi_connection.cursor()
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\pool\base.py", line 1083, in cursor
    return self.dbapi_connection.cursor(*args, **kwargs)
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 5244 and this is thread id 9008.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\IPython\core\interactiveshell.py", line 3397, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-7-87e83f2944ed>", line 1, in <cell line: 1>
    Drink.query.all()
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\orm\query.py", line 2772, in all
    return self._iter().all()
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\orm\query.py", line 2907, in _iter
    result = self.session.execute(
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\orm\session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\sql\elements.py", line 333, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\base.py", line 1806, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
    raise exception
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\base.py", line 1800, in _execute_context
    context = constructor(
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\default.py", line 1015, in _init_compiled
    self.cursor = self.create_cursor()
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\default.py", line 1386, in create_cursor
    return self.create_default_cursor()
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\engine\default.py", line 1389, in create_default_cursor
    return self._dbapi_connection.cursor()
  File "C:\Users\larsw\anaconda3\envs\useenv\lib\site-packages\sqlalchemy\pool\base.py", line 1083, in cursor
    return self.dbapi_connection.cursor(*args, **kwargs)
sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 5244 and this is thread id 9008.
[SQL: SELECT drink.iden AS drink_iden, drink.name AS drink_name, drink.description AS drink_description 
FROM drink]
[parameters: [{}]]
(Background on this error at: https://sqlalche.me/e/14/f405)

Code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///data.db"
db = SQLAlchemy(app)

class Drink(db.Model):
    iden = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True, nullable=False)
    description = db.Column(db.String(120))

    def __repr__(self):
        return f"{self.name} - {self.description}"


db.create_all()


@app.route('/')
def index():
    return "Hey"


@app.route('/drinks')
def get_drinks():
    return {"drinks": "drink data"}


if __name__ == "__main__":
    app.run(debug=True, port=8000)

Solution

Thanks to snakecharmerb’s comment, I replaced

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///data.db"

by

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///data.db?check_same_thread=False"

and it works just fine now.

Answered By – Waschbrettwade

Answer Checked By – David Goodson (Easybugfix Volunteer)

Leave a Reply

(*) Required, Your email will not be published