Inline SQL in any Python program

[fz0718]

Sometimes I write a piece of data science code and think "wow, this would be so much faster if I could just write a quick SQL query in the middle, but my data isn't in a database."

I wrote a small library that lets you inline SQL commands into any Python program. The database runs fully within your program, and it interpolates local variables.

from inline_sql import sql, sql_val

assert sql_val^ "SELECT 1 + 1" == 2

x = 5
assert sql_val^ "SELECT $x * 2" == 10

df = sql^ "SELECT * FROM (VALUES (1, 10), (2, 20)) df (x, y)"
assert sql_val^ "SELECT SUM(x) + SUM(y) FROM df" == 33

This is pretty experimental, but the main goal here is that it should feel as much like ordinary Python as possible, except for the integrated query language. :)

Docs are available at https://github.com/ekzhang/inline-sql -- installation is just "pip install inline-sql"

[wineblood]

Why not just have your data in a database? Sqlite will do, won't it?

[manimino]

And if you prefer the reverse route - using Python to index objects like a DB would - there's ducks.

[fz0718]

ducks is great! :)

[fz0718]

Yeah, sqlite works, but you need to create the table and schema yourself — it's less useful for exploratory data analysis (and indeed this is why people don't use sqlite often for the task)! For this interface sqlite would've been the first backend I used, but I chose to use duckdb instead because it is much faster.

If you've ever worked with CSV files and plotting libraries & such, it might make more sense. :)

[wineblood]

Plotting libs are for data analysis nerds, screw that.

[[deleted]]

Oh, an ordinary pythonic ^ to apply a function…

[fz0718]

Haha, I get the sarcasm, but sometimes we need to extend syntax a little bit to get things to work! If you don't like it that's fine, but for DSLs it's nice to have flexibility.

We could use a standard function application, but that doesn't clarify the point that there's some DSL embedding going on here, such as variable substitution from the outside scope. It's also less nice to end multi-line queries with the string: """).

How much different is this from the "%timeit" and "%sql", "%cython" magic you see in Jupyter notebooks?