Blending SQL and Python with Sqlorm

Summary

Blending SQL and Python with SQLORM At $dayjob, I use SQLAlchemy as an ORM. It's an amazing project, powerful and flexible. However, I've always felt some of the design choices didn't fit how I like to use an ORM. Notably: I'm not a big fan of the Unit of Work pattern and the fact that you do not control when DML queries are issued. I like queries to be executed as soon as they are called in code. I do not want my objects to be "attached" to a session or a specific database. I want to be able to fetch from one database and insert into another using the same object. I mostly want plain objects to map a table row. I want to write SQL by hand for complex queries. I do not want to use a query builder or DSL, I prefer writing actual SQL. I do not care about abstracting the database. I usually choose a database server when starting a project and optimize for it. Stay as close to DB-API as possible. With these ideas in mind, SQLORM was born. (The name isn't great — I'm bad at finding names for projects like this.) It's inspired by many ORMs while bringing some unique features. (As a side note, I know many other Python ORMs exist, but to me SQLAlchemy is the best. I don't like the API or codebase of the others.) The main feature of SQLORM is that SQL is front and center. You can create SQL queries as standard Python functions, using the docblock to write the templated SQL statement: from sqlorm import sqlfunc @sqlfunc def tasks_completion_report(start_date, end_date): """SELECT done_at, COUNT(*) count FROM tasks WHERE done_at >= %(start_date)s AND done_at <= %(end_date)s GROUP BY done_at""" In this example, start_date and end_date are parameters and will be properly escaped. Executing the function will run the SQL query in the active transaction. Connections and transactions are used via context managers. The Engine class manages DB-API connections. from sqlorm import Engine import datetime engine = Engine.from_uri("sqlite://app.db") with engine: report = tasks_completion_r...