Sentiments on this is that sticking close to native as possible reduces coherency issues between anything. Adding layers of abstraction on top of layers of abstraction often reduces contextual understandings and further dilludes the problem solving technique. If the abstraction is truly needed a thurough way to evaluate executions is needed and a proper way to contextualize which that is not. In-line comments or even very easy to navitage documentation but the former thing or even both is superior to the latter.
Python: Just Write SQL
161–170 of 288 posts
Re: Python: Just Write SQL
#162Technologists have a hard time accepting an established standard. Email is a perfect corollary to this conversation. There is a graveyard of companies that have attempted to "Solve email", yet it is still ubiquitous and attempts to 'improve' it continue to fizzle out. I'm not saying that progress, or an attempt at progress, is pointless, but to argue that writing vanilla SQL is somehow antiquated or archaic is false and OP makes several valid points highlighting why it is a perfectly valid approach.
Re: Python: Just Write SQL
#163Recently I was wondering myself whether I should just write SQL as I didn't particularly enjoy working with SQLAlchemy.
Then I discovered peewee. I am happy now.
Re: Python: Just Write SQL
#164It's fine advice... if you don't ever need to build queries programmatically (you will, probably) and don't care about type checks (you should, it's 2023). If you don't know what you're doing on the DB-app interface, you're still better off with an ORM most of the time. If you don't know if you know, you don't know (especially if you think you know but details are fuzzy); please go read sqlalchemy docs, no, skimming…
It's fine advice - if you can type check your queries. My colleague wrote a mypy plugin for parsing SQL statements and doing type checking against a database schema file, which helps to identify typos and type errors early: https://github.com/antialize/py-mysql-type-plugin
Re: Python: Just Write SQL
#165If 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…
> 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. You end up writing a query per usecase, rather than writing…
Have this right now. Fortunately no custom query builder. It's enough of a hell as is. Cleanup will take years.
Re: Python: Just Write SQL
#166The next logical step after writing the code given in the article is to abstract common boilerplate SQL into a library so you're not spending 50% of your time writing and re-writing basic SQL insert, update, and select statements every time your models need to be updated. At which point all you've done is write your own ORM.
If you want to go full-blown SQL you can use something like PostGraphile, which allows you to create all of your business entities as tables or views and then write all your business logic as SQL stored procedures, which get automatically translated into a GraphQL API for your clients to consume, but once you move beyond basic CRUD operations and reporting it becomes incredibly difficult to work with since there aren't really any good IDEs that help you manage and navigate huge pure-SQL code bases.
If you're really dead set against using a powerful ORM, it's probably still a good idea to find and use a lightweight one--something that handles the tedious CRUD operations between your tables and objects, but lets you break out and write your own raw queries when you need to do something more complex. I think there's a sweet spot between writing every line of SQL your application executes and having an ORM take care of boilerplate for you that will probably be different in every case but will never be 100% at one end or the other.
Re: Python: Just Write SQL
#167ORMs do much more than "write SQL". This is about 40% of the value they add. As this argument comes up over, and over, and over, and over again, writers of the "bah ORM" club continuously thinking, well I'm not sure, that ORMs are just going to go "poof" one day? I wrote some years back the "SQL is Just As Easy as an ORM Challenge" which demonstrates maybe a few little things that ORMs do for you besides "write SQL",…
> The SQL is not really the point. It's about the rows and objects, moving the data from the objects to the INSERT statement, moving the data from the rows you SELECTed back to the objects.
That's easy. It's a database mapping library. Write the query, give it an object and it fills it in or reads from it. You can do this with annotations, or struct tags in the Go world. There's no need to introduce abstractions over SQL joins which I find very off-putting, because the abstractions are never perfect and suddenly you have to learn invented concepts just because you didn't want to write a JOIN in an SQL query but rather have some clever framework introspect some classes and automagically write the JOINs for you.
Re: Python: Just Write SQL
#168Question to the SQL-only people, how would you handle something dynamic? If I have a database of shoes and want people to be able to find them by brand, size, style, etc., what does that look like?
If you don't want to maintain several queries, you could write something like SELECT * FROM shoes WHERE (CASE WHEN :brand_id IS NOT NULL THEN brand_id = :brand_id ELSE TRUE END) AND (CASE WHEN :size IS NOT NULL THEN size = :size ELSE TRUE END) AND (CASE WHEN :style IS NOT NULL THEN style = :style ELSE TRUE END)
That is pretty good. Much more readable than gluing a bunch of strings together.
Re: Python: Just Write SQL
#169The next logical step after writing the code given in the article is to abstract common boilerplate SQL into a library so you're not spending 50% of your time writing and re-writing basic SQL insert, update, and select statements every time your models need to be updated. At which point all you've done is write your own ORM. If you want to go full-blown SQL you can use something like PostGraphile, which allows you to…
The SQL inserts/updates have never felt tedious for me even in large projects, partially owed to careful use of jsonb for big objects where it makes sense (e.g. user settings dicts). Other than that, keeping a tight schema design.
Re: Python: Just Write SQL
#170One challenge working with SQL from statically typed languages (including Python + Mypy) is that you have to convert the query inputs/outputs to/from types and it's a lot of boilerplate. I started an experiment to generate this from annotated queries. [1] Python support is still incomplete, but I'm using it somewhat successfully for using SQLite from Rust so far.