Posts tagged sql, python
Filters: sql × python × Sorted by date
TIL: SQLite triggers. I've been doing some work with SQLite triggers recently while working on sqlite-chronicle, and I decided I needed a single reference to exactly which triggers are executed for which SQLite actions and what data is available within those triggers.
I wrote this triggers.py script to output as much information about triggers as possible, then wired it into a TIL article using Cog. The Cog-powered source code for the TIL article can be seen here.
SQLModel. A new project by FastAPI creator Sebastián Ramírez: SQLModel builds on top of both SQLAlchemy and Sebastián’s Pydantic validation library to provide a new ORM that’s designed around Python 3’s optional typing. The real brilliance here is that a SQLModel subclass is simultaneously a valid SQLAlchemy ORM model AND a valid Pydantic validation model, saving on duplicate code by allowing the same class to be used both for form/API validation and for interacting with the database.
PugSQL. Interesting new twist on a definitely-not-an-ORM library for Python. With PugSQL you define SQL queries in files, give them names and then load them into a module which allows you to execute them as Python methods with keyword arguments. You can mark statements as only returning a single row (or a single scalar value) with a comment at the top of their file.
How to rewrite your SQL queries in Pandas, and more (via) I still haven’t fully internalized the idioms needed to manipulate DataFrames in pandas. This tutorial helps a great deal—it shows the Pandas equivalents for a host of common SQL queries.
Appending the request URL to SQL statements in Django. A clever frame-walking monkey-patch which pulls the most recent HttpRequest object out of the Python stack and adds the current request.path to each SQL query as an SQL comment, so you can see it in debugging tools such as slow query logs and the PostgreSQL “select * from pg_stat_activity” query.
django-batch-select (via) A smart attempt at solving select_related for many-to-many relationships in Django. Add a custom manager to your model and call e.g. Entry.objects.all()[:10].batch_select(“tags”) to execute two queries—one pulling back the first ten entries and another using an “IN” query against the tags table to pull back all of the tags for those entries in one go.
python-sqlparse (via) Python library for re-identing SQL statements. This could make debugging Django’s generated SQL a whole lot easier. You can try the library out using an App Engine hosted application (complete with an API).
Secrets of the Django ORM. An undocumented (and unsupported) method of poking a Django QuerySet’s internal query to add group_by and having clauses to a SQL query.
IronPython, MS SQL, and PEP 249. How Dino Viehland got Django’s ORM to talk to the .NET database layer.
Queryset Implementation. Malcolm explains the work that has gone in to the queryset-refactor branch. Executive summary: Python’s ORM is probably a lot better at SQL than you are.
django-mptt (via) Jonathan Buchanan’s simple utility for performing Modified Preorder Tree Traversal (efficient tree operations in SQL) on Django models.
DbMigration—a schema migration tool for Django. Nice and simple tool for adding schema migrations to a Django application.
Finding Lookup Items that Are Not Used. How to do left outer joins (and other custom SQL) using the Django ORM.
Storm. New Python ORM from Canonical, emphasising multiple database support, intelligent local cache invalidation and a thin layer over the underlying SQL.