21 ms·
Simple SQL in Python
- deleted 6y ago[deleted]
- hprotagonist 6y agoSeems similar in inspiration to https://pugsql.org/ https://pugsql.org/ which is a python reimplementation of hugSQL, more or less. I'd say an advantage of pugSQL is that it's sqla-core under the hood, so lots of stuff will Just Work and you can drop down to core to commit war crimes if you have to.
- config_yml 6y agois there something like this for ruby?
- ntrz 6y ago[hp]ugSQL seems to have been inspired by[1] Yesql, a library with ``a similar take on using SQL'' that has also inspired this[2] rom-rb adapter; not a Ruby guy so there may be something a lot better/closer, but this is what I came up with after a couple minutes of searching. [1] https://www.hugsql.org/#faq-yesql https://www.hugsql.org/#faq-yesql [2] https://github.com/rom-rb/rom-yesql https://github.com/rom-rb/rom-yesql (documentation: https://www.rubydoc.info/gems/rom-yesql https://www.rubydoc.info/gems/rom-yesql)
- TedDoesntTalk 6y ago> I'd say an advantage of pugSQL is that Plus it’s got a cool name
- ggregoire 6y agoExactly my first thought. Happy user of PugSQL here. I just wish there was a VSCode extension for autocompletion.
- nackjicholson 6y agoI haven't used pug, but I read through it a bit and like their approach. I think they have a more intricate way of parsing SQL than we have in aiosql. We're doing it quick and dirty with some regular expressions.
- stasa 6y agoI'm relatively new to Python and I was looking for something like this. I do wonder if there is a well-known Python equivalent of Dapper: https://github.com/StackExchange/Dapper https://github.com/StackExchange/Dapper
- aikah 6y agoYou don't need Dapper for Python. Python is a dynamic language. If your problem is hydration[0] it can be done easily, even with Python classes. https://stackoverflow.com/questions/13337629/create-an-instance-of-a-class-from-sql-query https://stackoverflow.com/questions/13337629/create-an-insta... For query building SQLAlchemy can do that and more. I think that, with JSON support in most RDBMS, ORM as a concept has become way easier to handle. I come to think that this is the promise of the 90's Object oriented databases being fulfilled somehow. [0]:https://stackoverflow.com/questions/6991135/what-does-it-mean-to-hydrate-an-object https://stackoverflow.com/questions/6991135/what-does-it-mea...
- stasa 6y agoHydration is not a big problem. However, Dapper does much more than that: it handles multi-mapping (eg mapping users and posts from a single query), query parameterization etc. Surely, you can do it all ad hoc, but Dapper provides very convenient set of primitives out of the box.
- aikah 6y ago> it handles multi-mapping (eg mapping users and posts from a single query) You can achieve all that with JSON support in SQL, which most RDBMS have. You can then de-serialize JSON rows into Python objects. No need for an ORM.
- emre 6y agoIbis Project is also similar: https://ibis-project.org/ https://ibis-project.org/
- grncdr 6y agoI've always wanted to build a larger app based on this approach. I quite like Massive, and 5 years (!) ago I wrote https://gist.github.com/grncdr/4555208#file-sss-py https://gist.github.com/grncdr/4555208#file-sss-py as a one-off response to a tweet from Ted Dziuba (is he still around?)
- ThePhysicist 6y agoSmall nitpick: SQLAlchemy is not just an ORM, it provides a lot of functionality to interact with SQL databases without mapping results to objects.
- np_tedious 6y agoThey call the non-ORM part "core". https://docs.sqlalchemy.org/en/13/core/ https://docs.sqlalchemy.org/en/13/core/
- nine_k 6y agoIndeed, to me the whole point of SQLAlchemy is the composable SQL DSL; I never use the ORM part.
- jessedhillon 6y agoAnyone who is attracted to this approach should look into stored procedures instead: https://www.postgresqltutorial.com/postgresql-create-procedure/ https://www.postgresqltutorial.com/postgresql-create-procedu...
- progre 6y agoStored procedures are not available for SQLite. For Postgres the syntax for SPs are so horrible that I'd rather use this. Edit: It's quite telling that the example in the link don't show how to return a resultset from a SP in Postgres.
- deleted 6y ago[deleted]
- welder 6y agoNice idea, but this pattern hides your queries away from the developer. In practice, developers need the query transparent and not hidden in a file somewhere away from the code they're reading. Otherwise you're building a pattern for using queries without knowing the query's cost. If you need raw SQL, use SQLAlchemy Core and just don't use the ORM features.
- ibejoeb 6y agoIt feels just like an include to me. Why can't the developer look in the file?
- Scarbutt 6y agoSome developers just put some hard mental restrictions on themselves.
- HumblyTossed 6y ago> If you need raw SQL, use SQLAlchemy Core and just don't use the ORM features. How heavy is that?
- memco 6y agoYes, this feels like it reinvents prepared statements in a less portable way. It kind of goes against the spirit of their opening justification for using it: "SQL is code, you should be able to write it, version control it, comment it, and run it using files." If that's the goal encourage the use of a way to leverage native SQL that could run on any platform or engine.
- paulie_a 6y agoOr as a developer use the orm for ease and ignore learning a new language. Saving a couple miliseconds is really not that important to most devs.
- HumblyTossed 6y ago> Saving a couple miliseconds is really not that important to most devs. Users care.
- kristaps 6y agoNo interest in the tool itself, but love the neutral outline of use cases it's a good/bad fit for - more software should do this.
- deleted 6y ago[deleted]
- qaq 6y agoFunny to see on HN just started building a version of this that actually generates functions from sql queries so you get all the IDE goodness and sightly smaller runtime overhead
- vii 6y agoThe syntax follows https://github.com/krisajenkins/yesql/ https://github.com/krisajenkins/yesql/ which works with Clojure. So then the DB queries can be defined in a language agnostic way, so you don't need a gateway API to access the DB :) There are many opportunities to grow this, e.g. transforming the SQL files into stored procedures, and being able to lint and check against a DB schema definition. However, I'm not sure that the indirection step of hiding the SQL away from the code actually makes sense in the long run. A tight coupling allows filters and other optimisations to be easily added to the end of a query, and saves one lookup that is not yet supported in your editor.
- nackjicholson 6y agoHey, thanks for the opportunity ideas! You're totally right about the editor lookup problem. I wrote this project, I use this project in a production setting and not being able to jump to the SQL from code does bother me.
- icholy 6y agoGo has something similar to this https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc
- didip 6y agoI like this idea a lot actually. I've been pondering of doing the same thing in Go. There are some edges though... for example what if you want to do further composition based on if/else clause.
- zwack 6y agoYou could have a look at our attempt, it allows dynamic SQL using templating. https://github.com/ebenefuenf/quma https://github.com/ebenefuenf/quma
- tracnar 6y agoThis could go one step further by implementing an import hook, then you could do import users; users.get_all_users(conn) It's something I've been meaning to try with other languages.
- zwack 6y agoWe wrote a similar lib and use namespaces: https://quma.readthedocs.io/en/latest/usage.html#running-queries https://quma.readthedocs.io/en/latest/usage.html#running-que...
- jdnier 6y agoMy initial thought is I'd rather just have a module that would find project-related .sql files and parse into named blocks based on the comments, treating them as named strings. No db entanglement required, just a simple way to organize queries in sql files. You would use it more like this: import sqlite3 import project_queries as queries # a module that loads queries from local .sql files as strings in module namespace conn = sqlite3.connect('myapp.db') cursor = conn.cursor(queries.get_all_users) # ^^^^^^^^^^^^^^^^^^^^^ users = cursor.fetchall() # >>> [(1, "nackjicholson", "William", "Vaughn"), (2, "johndoe", "John", "Doe"), ...] aiosql may add some features to help with passing values to the query. I see `^` used with `:users` in an example but don't quite get it. -- name: get-user-by-username^ I often need to generate DDL details like table and column names in the query, which you don't want escaped, along with data like values and ids where you would want escaping (:users or %(users)s). It works nicely to use string formatting (`CREATE TABLE {table_name} ...`) for DDL along with interpolation (`WHERE user_id IN %(user_ids)s`) in the same query.
- kristjansson 6y agoI’ll unhelpfully note that I could swear I’ve seen something that does what you describe, and thought that this project was what I recalled - alas.
- deleted 6y ago[deleted]
- wswope 6y agoAnoSQL is a project along these lines I’ve seen on here before.
- nackjicholson 6y agoI forked aiosql from anosql in order to support asyncio based sql drivers. Huge thanks to Honza Pokorny for starting this adventure.
- kristjansson 6y ago
- krick 6y agoLooks kinda nice, but can it handle "IN (...)" statements correctly? Or conditionally adding some WHERE param? If not, this is rather simplistic and couldn't replace writing inline queries, and then I think I'd rather not mix together 2 approaches.
- munk-a 6y agoI've built pre-parsing layers for queries that can explode out params properly for IN() handling - though depending on your SQL variant you can also get away with string passing an array to get around that. Conditional WHERE seems unapproachable with this tool - which is one of my doubts about the need of a tool like this. If you instead approach your SQL by having each query wrapped in a function in an isolated part of your code base you, as a company/team/whatever, can choose how much logic to let reside inside of those functions - building up conditional WHERE clauses is a very common thing to need to do.
- ksri 6y agoTake a look at JinjaSQL (https://github.com/hashedin/jinjasql https://github.com/hashedin/jinjasql). Supports conditional where clauses as well as in statements. It uses Jinja templates, so you get a complete template language to create the queries.
- delduca 6y agohttps://github.com/mcfunley/pugsql https://github.com/mcfunley/pugsql
- stdohm 6y agoI'm a fan of using raw sql like this but curious if anyone has solved deduplicating common statements and dynamic queries in a good way. For example: 1) Imagine lots of queries having to show the results in the context of a user and therefore use the same JOIN and WHERE clause all over. Not being DRY, this breaks down when having to change the clause at all. 2) Imagine a reporting page that allows for filtering and ordering by different columns and therefore need some way to compose the final sql.
- bokwoon 6y agoDon't query builders already let you do that? By making the WHERE clause map to a data structure, you can just duplicate the data structure to duplicate the WHERE clause e.g. https://bokwoon95.github.io/sq/query-building/reusing-where.html https://bokwoon95.github.io/sq/query-building/reusing-where.... (full disclosure: I'm the author).
- stdohm 6y agoTotal agree and I use query builders all the time. (Yours definitely looks nice.) I was curious though how others might of handled these issues when using libraries like the one posted here which is just raw sql.
- nackjicholson 6y ago+1 for query builders being an excellent choice for a lot of applications and uses. Keeping things DRY being one. We haven't solved the repetition in SQL with aiosql. We also haven't tried. :)
- iwebdevfromhome 6y agoLooks like a cool idea. I wonder if I can make it to work easily with this other lovely python library https://github.com/pudo/dataset https://github.com/pudo/dataset
- darwinwhy 6y agoLooks like we had the same idea at the exact same time! https://news.ycombinator.com/item?id=24148552 https://news.ycombinator.com/item?id=24148552
- darwinwhy 6y agohttps://github.com/pudo/dataset https://github.com/pudo/dataset
- devxpy 6y agoNice approach with annotations in the comments. I wrote something a little more tounge in cheek a while ago -- I modified SQL syntax to include functions with arguments https://github.com/scientifichackers/sql2code https://github.com/scientifichackers/sql2code
- elamje 6y agoI really like this approach because as you said, it gets really messy to do string replacements inline in a python file. I'm curious if any people here are comfortable using stored procedures as an alternative to this. Stored procs give you the benefit of your sql being easy to change in a sql editor, but you also get query planner caching of the results which means it will likely execute faster than string replacement inline sql.
- rrrrrrrrrrrryan 6y agoI think it really depends on the project. Stored procs are really nice for a lot of different use-cases, but one major downside is that they can be challenging to version control. Storing this sort of stuff outside of the database with the Python project itself would make version control trivially easy.
- elamje 6y agoThat's true. I've never attempted to keep procs in VC, but I think it would be pretty easy if you wanted to.
- tuatoru 6y agoWhy are stored procs hard to version control? They're just text in a text file.
- yellowstuff 6y agoAlmost, but not exactly. If you import a Python file you get exactly what's in that file. A stored proc is not tied to a file- you can change the file and forget to update the proc, or change the proc without updating the file.
- bbsimonbb 6y agoSQL should have been in .sql files from the start. SQL in string literals is an industry-wide decades long aberration. I fixed this problem for C# with QueryFirst. I'm delighted to see all the support here the approach, which is clearly the way forward for data access. https://marketplace.visualstudio.com/items?itemName=bbsimonbb.QueryFirst&ssr=false#overview https://marketplace.visualstudio.com/items?itemName=bbsimonb...