Is SQLAlchemy still the most popular/best regarded object/relational mapper for Python? I’ve got a Flask app which currently uses SQLite. I’d like to move to a server-based database like MariaDB/MySQL, but think that converting to use an ORM is probably the correct approach.
I think it is. But it might be worth checking out sqlmodel (https://sqlmodel.tiangolo.com/) which connects sqlalchemy with pydantic models. If it fits your need, it might simplify your code alot. If not, use all the power of sqlalchemy by using it directly.
I recently released Flask-SQLAlchemy-Lite as a simpler alternative to Flask-SQLAlchemy. It provides the engine configuration and session management without all the other “magic” in the old extension, which was starting to cause issues with modern SQLAlchemy, typing, async, etc.
You can also use SQLModel with it, if you do want to use that to define your models. But I think SQLAlchemy’s modern support for typing or dataclasses is good as-is.
1 Like
I know there are people who swear by Advanced Alchemy.
Sorry, that link doesn’t work for me (404). You mean this, I suspect:
2 Likes
SQLAlchemy is very well regarded. (I haven’t used it directly in a few years but hope my notes here are still accurate.) It is built in layers: SQL abstraction, query engine (writing python function calls for queries), tables represented by python classes, and on top of all the ORM.
For the ORM you can use “active record” style or not – meaning the python classes/objects that you work with are also connected to DB tables/rows, or not (so you have one class for a table and one class for your working objects). SQLAlchemy calls this “declarative base”.
I think that the Django ORM is also very popular. You don’t need a full django project to use it, you could pass DB connection settings as a simple dict in a call django.configure({ ... }). There are fully standalone projects that implement something that looks like the Django ORM with a smaller codebase.
These two have different philosophies.
Django requires you to know (or develop knowledge) about entity-relational mappings to write your classes but then lets you think in terms of the Django API and mostly ignore the SQL generated. But sometimes you will have to understand what code is generated and turn your code around to have the right joins or references happening.
SQLAlchemy expects you to know a bit more about database design and query writing, then lets you abstract that using one of the layers, so you write Python code (low-level table queries or high-level ORM calls) in full knowledge of how you would have written the corresponding SQL.
3 Likes