Python: Just Write SQL
joaodlf.com
Python: Just Write SQL
1–10 of 288 posts
Re: Python: Just Write SQL
#2With SQLAlchemy, I come for the type checking. I stay for the Alembic migrations.
Re: Python: Just Write SQL
#3Congrats, you just wrote your own ORM. Please mind that ORM doesn’t necessarily mean ActiveRecord, which could be considered an anti pattern.
Re: Python: Just Write SQL
#4Congrats, you just wrote your own ORM. Please mind that ORM doesn’t necessarily mean ActiveRecord, which could be considered an anti pattern.
What's Active Record and why is it an anti pattern?
Re: Python: Just Write SQL
#5So far all my projects have targeted a specific database with no reason to change it.
So what I do is write SQL commands, but keep all inside a specific file or module of the project. So that I can decide later to refactor it into an ORM.
I think ORMs are great if you write libraries that target more than one database. Or situations, where you have more than one database and need a proper migration part.
If you don't need migration, but in the worst case can start with a fresh, empty database, then write SQL.
But for production system, the no/manual migration might get old quickly. Writing migration code that just adds fields, indexes or tables is easy. But writing code that changes fields or table structures? Not do much.
Still, you don't need an ORM at the beginning of a project, just don't put SQL everywhere.
Re: Python: Just Write SQL
#6Congrats, you just wrote your own ORM. Please mind that ORM doesn’t necessarily mean ActiveRecord, which could be considered an anti pattern.
What's Active Record and why is it an anti pattern?
The active record pattern is an approach to accessing data in a database. A database table or view is wrapped into a class. Thus, an object instance is tied to a single row in the table. After creation of an object, a new row is added to the table upon save. Any object loaded gets its information from the database. When an object is updated, the corresponding row in the table is also updated. The wrapper class implements accessor methods or properties for each column in the table or view.
https://en.wikipedia.org/wiki/Active_record_pattern
It was fashionable for a while to say it was an anti-pattern because that was a contrary view and ActiveRecord is very tied into building Rails applications.
Re: Python: Just Write SQL
#7If you're going to end up querying all the fields and putting them into a model like this dataclass anyways... Django can do that for you. If you're going to later pick and choose what fields you query on the first load, and defer other data til later.... Django can do that for you. If you're going to have some foreign relations you want to easily query.... Django can do that for you. If you're doing a bunch of joins and are using some custom postgres extensions for certain fields and filtering... Django can help you organize the integration code cleanly.
I totally understand people having issues with Django's ORM due to the query laziness making performance tricky to reason about (since an expression might or might not trigger a query depending on the context). In some specialized cases there are some real performance hits from the model creation. But Django is very good at avoiding weird SQL issues, does a lot of things correctly the first time around, and also includes wonderful things like a migration layer.
You might have a database that is _really_ unamenable to something like an ORM (like if most of your tables don't have ID rows), but I wonder how much of the wisdom around ORMs is due to people being burned by half-baked ORMs over the years.
I am curious as to what a larger codebase with "just SQL queries all over" ends up looking like. I have to imagine they all end up with some (granted, specialized) query builder pattern. But I think my bias is influenced by always working on software where there are just so many columns per table that it would be way too much busywork to not use something.
Re: Python: Just Write SQL
#8I always write SQL code directly, but that's mostly because I actually enjoy writing SQL queries :).
Re: Python: Just Write SQL
#9Earlier quoted context omitted.
What's Active Record and why is it an anti pattern?
The active record pattern is an approach to accessing data in a database. A database table or view is wrapped into a class. Thus, an object instance is tied to a single row in the table. After creation of an object, a new row is added to the table upon save. Any object loaded gets its information from the database. When an object is updated, the corresponding row in the table is also updated. The wrapper class implem…
It's not about fashion. Observations about fashion are no deeper than fashion itself.
It scales badly with table size, I think by design. That's why SQLAlchemy's and Hibernate's Data Mapper pattern is slightly more cumbersome to write, but works out much better.
Re: Python: Just Write SQL
#10Congrats, you just wrote your own ORM. Please mind that ORM doesn’t necessarily mean ActiveRecord, which could be considered an anti pattern.
He didn't really. The SQL is right there, and this is important.
What I've experienced (unfortunately) across multiple projects is that people who understand databases will write SQL with a nice collection of helper and wrapper functions as needed, and the people that think that databases are mysterious black boxes will reach for ORM. I've seen the ORM-happy teams getting scared at the idea of a million (1,000,000!) rows in a table, and they always neglect to set up even basic indexes or to think through what their JOINs are really doing.
YMMV but that's the pattern I see again and again.