4 ms·
Writing a Python SQL engine from scratch
- marsupialtail_2 4y agoOK I'll admit: https://news.ycombinator.com/item?id=34189422 https://news.ycombinator.com/item?id=34189422 is not a real pure Python SQL engine, this one is.
- captaintobs 4y agoHaha, well they are two very different projects with different goals :)
- habibur 4y agoGood work. Here are my use cases of a SQL parser at application level. - enumerate, add, delete conditions from WHERE clause. - change ORDER BY. - paging, LIMIT. - turn an aggregate query into crosstab query.
- eatonphil 4y agoI love that you linked to all the places in code for each section. Helps folks jump into the code.
- captaintobs 4y agoI believe one of the main benefits of it being in Python is that it's easy to follow and jump in.
- conaclos 4y ago> The main reason why I ended up building a SQL engine was...just for entertainment. It's been fun learning about all the things I also wrote an SQL parser [1] for SQLite schema. This was mainly for fun, but also to support the specificities of SQLite schema. I was not happy with the result of other parsers (including sqlglot). [1] https://github.com/coast-team/sqlschm https://github.com/coast-team/sqlschm
- captaintobs 4y agoCool, sorry that SQLGlot didn't satisfy your needs. But if you file issues, we fix them quickly.
- nicolaslem 4y agoVery cool! A while ago I wrote a B+ tree in Python[0] to learn how databases work under the hood. I left it there but it seems that this project could allow me to quickly add full SQL support for it. [0] https://github.com/NicolasLM/bplustree https://github.com/NicolasLM/bplustree
- bob1029 4y agoThis is very interesting work. I've been debating writing a SQLite-compatible parser/generator for purposes of manipulating arbitrary queries in AST form. The biggest reason would be to quickly enumerate statistics about any given query. This would allow for us to run reports about which tables are used from certain areas, how often related tables are joined, etc. This would make refactor decisions substantially easier, since most of our business logic is defined as SQL queries now. Any given install of our product could have well over 10k SQL queries to deal with. Adding the generator bit would also give us an ability to automatically rewrite queries as needed. This would predominantly be used to standardize the text formatting of any given query, but would also be extremely useful for things like renaming tables or columns.
- jeroen79 4y agoNice there is something like this, pitty its in python.
- collinc777 4y agosqlglot is awesome! Thanks for the contribution Toby!
- captaintobs 4y agothanks!
- thelastbender12 4y agoSqlglot is fantastic, and a super readable codebase to learn from! Thanks @captaintobs. With the comparisons to calcite, I was curious if you've considered implementing sqlglot in a native language too? Something compiling down to a small wasm extension would make it accessible to web apps.
- captaintobs 4y agoI've run SQLGlot on the browser using Pyodide or something before. Since it has no deps, it didn't have any issues.
- adam_gyroscope 4y agoWe (bit.io) use SQLGlot and love it! We use it in our open-source sqlite->postgres tool, pgsqlite:https://github.com/bitdotioinc/pgsqlite https://github.com/bitdotioinc/pgsqlite, and in our general SQL parser where we can translate between dialects (https://docs.bit.io/docs/query-translation https://docs.bit.io/docs/query-translation): #!translate:sqlite select \* from [foo] That will translate the given sql (which is in SQLite syntax) into PostgreSQL SQL and then run that query. Also, Toby & team may be the most responsive open-source maintainers I've ever seen. Open issues are often closed within a day. It's awesome.