[HN Gopher] Writing a Python SQL engine from scratch
___________________________________________________________________
Writing a Python SQL engine from scratch
Author : captaintobs
Score : 87 points
Date : 2023-01-03 16:35 UTC (6 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| eatonphil wrote:
| I love that you linked to all the places in code for each
| section. Helps folks jump into the code.
| captaintobs wrote:
| I believe one of the main benefits of it being in Python is
| that it's easy to follow and jump in.
| jeroen79 wrote:
| Nice there is something like this, pitty its in python.
| collinc777 wrote:
| sqlglot is awesome! Thanks for the contribution Toby!
| captaintobs wrote:
| thanks!
| marsupialtail_2 wrote:
| OK I'll admit: https://news.ycombinator.com/item?id=34189422 is
| not a real pure Python SQL engine, this one is.
| captaintobs wrote:
| Haha, well they are two very different projects with different
| goals :)
| conaclos wrote:
| > 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
| captaintobs wrote:
| Cool, sorry that SQLGlot didn't satisfy your needs. But if you
| file issues, we fix them quickly.
| adam_gyroscope wrote:
| We (bit.io) use SQLGlot and love it! We use it in our open-source
| sqlite->postgres tool,
| 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):
| #!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.
| nicolaslem wrote:
| Very 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
| habibur wrote:
| Good 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.
| bob1029 wrote:
| This 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.
| thelastbender12 wrote:
| Sqlglot 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 wrote:
| I've run SQLGlot on the browser using Pyodide or something
| before. Since it has no deps, it didn't have any issues.
___________________________________________________________________
(page generated 2023-01-03 23:02 UTC)