SQLAlchemy is a massive database toolkit that you can use in various ways. Let us do a recap on the options you have and look for a good starting point for your situation.
This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.
You need a database but do not want to write SQL?
In this case start with SQLAlchemy ORM and use Alembic to manage the tables for you. You need a few extra classes before you can write your code like this:
|
1 2 3 4 5 6 7 8 9 10 |
def add_employee(): employee = Employee() employee.last_name = "King" employee.first_name = "Robert" employee.birth_date = '1990-05-29' print(employee) session = db_session.factory() session.add(employee) session.commit() |
You want to use a database from someone else?
Automap creates for you all the mapping needed by SQLAlchemy ORM. If the database changes you only need to restart your application and Automap works with the new schema.
You want to use SQLAlchemy ORM but don’t want to write the mapping yourself?
In this case you need to write the database migration scripts by yourself and then you can use Automap and SQLAlchemy ORM . You can use any database migration tool in any language you want (like DbUp, Flyway or Alembic in Offline Mode).
You want SQLAlchemy but don’t trust the ORM?
For this case SQLAlchemy Core is a good fit. You need a minimalistic setup to map tables to Python objects. From there you can work on a SQL like style without the need to write the SQL statements completely by yourself:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
def do_insert(): stmt = insert(employees).values( LastName='Collins', FirstName='Arnold', BirthDate='2000-01-31') new_id = 0 connection_string = "sqlite:///Northwind_small.sqlite" engine = create_engine(connection_string, echo=False) with engine.begin() as con: result = con.execute(stmt) new_id = result.inserted_primary_key['Id'] print(f"New Id: {new_id}") return new_id |
You can use Alembic and its offline mode to change the database schema or any other database migration tool.
Complex queries or database specific features needed?
If you have complex queries that use database specific features, then you can use Textual SQL (or raw SQL) in SQLAlchemy Core. You write your queries by yourself and use SQLAlchemy only to send them to the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
def create(): insert = text(""" INSERT INTO "main"."Employee" ("LastName", "FirstName", "Title", "BirthDate") VALUES (:last, :first, :title, :dob); """) connection_string = "sqlite:///Northwind_small.sqlite" engine = create_engine(connection_string, echo=False) with engine.begin() as con: con.execute(insert, {"last":"Davolio","first":"Nancy","title":"Sales","dob":"1980-05-29"}) new_id = con.execute(text("SELECT last_insert_rowid() AS Id;")).first() print(new_id) return new_id["Id"] |
I suggest you take this approach as a last resort. You better start with SQLAlchemy Core or ORM and only go down to this level for the few queries that need it.
Conclusion
SQLAlchemy supports all your database needs. The trick is to find the way that matches your requirements. I hope this post helps you with this task.
As soon as you know what you want, focus on learning that part. You can always come back later and look at the other features of SQLAlchemy. If you try to learn all at once it will take a lot of time and makes everything a lot harder.