Python Friday #232: Update SQLAlchemy to Version 2.x

Before we can move ahead and add a database to our to-do API, we take a little detour and update SQLAlchemy to version 2. Much changed since I wrote about SQLAlchemy three years ago, but thanks to the early published guidelines, the largest part of the examples I used in my post can stay the same. Nevertheless, there are changes we need to know about, and we must update minor details to run the examples with the current version of SQLAlchemy.

This post is part of my journey to learn Python. You find the code for this post in my PythonFriday repository on GitHub.

 

The full list of changes

The SQLAlchemy 2.0 Major Migration Guide covers all the changes in great details. When you want to migrate your application, you best keep a browser tab open with this guide.

For this blog post I only fix the SQLAlchemy examples I posted 3 years ago. That list is not complete, but it gives you a good outlook on what to expect when you update your code.

 

A word of warning

If you fix a problem, run your tests and you end up with the same error, check that the error is from the same line as you currently try to fix. It may have multiple similar problems in your code and the error you attempted to fix is indeed gone, but another place now throws the same error. You can save a lot of time if you carefully read the line numbers in the exceptions you get.

 

Execute() returns tuples and not dictionaries

TypeError: tuple indices must be integers or slices, not str

Instead of using a name to access the values we get back from the database, we need to use the index (0 for the first field, 1 for the second and so on):

 

No more plain strings for execute()

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: ‘pragma foreign_keys=on’

Errors like that (to turn on foreign keys in SQLite) may give you the wrong idea of the problem. The reason for the error is that we can no longer use plain strings and must wrap them with text() instead:

 

Joins with classes and not strings

sqlalchemy.exc.ArgumentError: Strings are not accepted for attribute names in loader options; please use class-bound attributes directly.

For joinedload() and selectinload() we can no longer use a string to specify the matching column. Instead, we need to use the class and the field directly:

 

Orm.Relation is now relationship

The function orm.relation() got a new name, and we now need to use relationship() to define the relation between our tables:

 

Replace imp with importlib

This is a change in Python itself, but you may run into it when you use Alembic with SQLAlchemy. To access the helper file, we can no longer write this code:

Instead, we need to switch to importlib:

 

Automap and SQL Server: connection string format changes

The connection string in the URL format did not work to connect to SQL Server with the current ODBC driver from Microsoft. I had to change the way I put in the parameters for the connection string to this code:

 

Changes to reflection with automap

SADeprecationWarning: The AutomapBase.prepare.reflect parameter is deprecated and will be removed in a future release. Reflection is enabled when AutomapBase.prepare.autoload_with is passed.

We can fix this warning by changing the parameters to the Base.prepare() method:

 

Next

With this little update guide for SQLAlchemy 2, the examples from my little blog series are up to date. We can now use them when we run into a problem or need a hint on how to build our queries. Next week we are back at FastAPI and add SQLAlchemy to our to-do application.