Error in add_new_order_for_customer()

[sarcanon]

Hello. I've been working my way through this very thorough and professionally presented tutorial, and have into a problem.

When attempting to call ./run.sh neworder, I get a Bad Request error. Here is the log output:

2024-12-03 15:54:58,295 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-12-03 15:54:58,295 INFO sqlalchemy.engine.Engine 
            INSERT INTO orders
                (customer_id, order_time)
            VALUES
                (?, Date('now'))
            RETURNING id
            
2024-12-03 15:54:58,295 INFO sqlalchemy.engine.Engine [generated in 0.00032s] (1,)
2024-12-03 15:54:58,295 INFO sqlalchemy.engine.Engine ROLLBACK
ERROR:root:Failed to add new order
Traceback (most recent call last):
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "RETURNING": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/workspaces/sqlalchemy-workshop/marketsvc/db_accessor.py", line 104, in add_new_order_for_customer
    new_order_id = execute_insert_query(
                   ^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/sqlalchemy-workshop/marketsvc/db_accessor.py", line 14, in execute_insert_query
    cursor = conn.execute(text(query), params)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/workspaces/sqlalchemy-workshop/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "RETURNING": syntax error
[SQL: 
            INSERT INTO orders
                (customer_id, order_time)
            VALUES
                (?, Date('now'))
            RETURNING id
            ]
[parameters: (1,)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
INFO:     127.0.0.1:55306 - "POST /api/add_new_order HTTP/1.1" 400 Bad Request

At first, I suspected the edits I was making to the code while following the tutorial were the problem, but I don't think so, since when I do a fresh checkout of the step-2-sqlalchemy branch, the same error occurs.

Python is 3.12.1, as in the tutorial. Not sure whether it's a dependency issue. Here is the output of pip list:

(venv) @sarcanon ➜ /workspaces/sqlalchemy-workshop (step-2-sqlalchemy) $ pip list
Package           Version
----------------- -----------
annotated-types   0.7.0
anyio             4.6.2.post1
click             8.1.7
fastapi           0.115.5
greenlet          3.1.1
h11               0.14.0
httptools         0.6.4
idna              3.10
pip               24.3.1
pydantic          2.10.2
pydantic_core     2.27.1
python-dotenv     1.0.1
PyYAML            6.0.2
ruff              0.8.1
sniffio           1.3.1
SQLAlchemy        2.0.36
starlette         0.41.3
typing_extensions 4.12.2
uvicorn           0.32.1
uvloop            0.21.0
watchfiles        1.0.0
websockets        14.1

I would be most grateful if you could point what I'm doing wrong, so I can continue with the tutorial.

Cheers.

[rhythm-patel]

Hello, thanks for raising this and creating an issue!

Let me start by saying that you're doing nothing wrong. It seems like you've followed the tutorial correctly. The issue lies in SQLite configuration.

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "RETURNING": syntax error

The above error you're getting is not because of SQLAlchemy or the code itself, but due to Python picking up the older SQLite version, where RETURNING is not supported. Support for the RETURNING clause came out in SQLite version 3.35.0 (source) (similar error).

When we built this tutorial with Python 3.12 (3.12.x) few months ago, it picked up SQLite version > 3.35.0 and the code worked (we had tested this multiple times end-to-end and delivered the tutorial to a few conferences).
I tested it out on GitHub Codespaces again, and I now get the same error as you. For some reason, Python 3.12.1 in my Codespaces is picking up v2.6.0 of sqlite3. You can test this too by typing:

>>> import sqlite3
>>> sqlite3.version
>>> '2.6.0'
>>> sqlite3.sqlite_version
>>> '3.31.1'

Since SQLite 3.31.1 doesn't have the support for RETURNING, it's throwing an error while adding a new order.

Now you could try installing the newer version of SQLite (within the same Python), but it's going to be hard since sqlite3 gets shipped natively with Python.
In my opinion, the best and easiest way is to follow the tutorial with a newer version of Python.

You could either install a different version of Python with pyenv, if running it on your machine. If you're in Codespaces, they have a guide on using Dev Containers to work with multiple versions of Python.

From the VSCode command palette, click on "Codespaces: Add Dev Container Configuration Files", then "Add New Configuration", and "Show All Definitions". Choose "Python 3 devcontainers" and 3.12-bookworm version (important), and skip the features. On the bottom right, Codespaces will ask you to rebuild the container. Click on "Rebuild Now" & "Rebuild".

Now you should have Python 3.12.7, and once you check the sqlite version, you should have one > 3.35.0, that supports RETURNING clause.

>>> sqlite3.sqlite_version
>>> '3.40.1'

Now, you can finally create a virtual environment, install dependencies and run the server. While running ./run.sh neworder, you will now see

127.0.0.1:58120 - "POST /api/add_new_order HTTP/1.1" 201 Created

I hope this helps, hoping you learn something new from the tutorial! I think I might add an info/warning in our tutorial docs about this as well.