News for hackers. Stuff that matters.
Re:Sqlite 3.30.0 by kragen on Saturday October 05, 2019 @12:57AM (#21163893)
I only learned this week that it supports recursive CTEs (although apparently you can't refer to them in subqueries yet?) and only yesterday that you can do this:

    >>> import sqlite3
    >>> con = sqlite3.connect(':memory:')
    >>> cur = con.cursor()
    >>> q = '''select x, sum(a * power(x, e))
    ... from (select -2 as x union select -1 union select 0 union select 1 union select 2) xt,
    ... (select 1 as a, 2 as e union select 4, 1 union select 2, 0) et
    ... group by x'''
    >>> cur.execute(q).fetchall()
    Traceback (most recent call last):
      File "<stdin>", line 4, in <module>
    sqlite3.OperationalError: no such function: power
    >>> con.create_function('power', 2, lambda base, power: base**power)
    >>> cur.execute(q).fetchall()
    [(-2, -2), (-1, -1), (0, 2), (1, 7), (2, 14)]
That is freaking cool. Not evaluating polynomials in SQL, I mean, but defining a user-defined function for SQL in one line of Python.
Re:Sqlite 3.30.0 by emj on Saturday October 05, 2019 @05:12AM (#21164541)
That is cool; Being able to call python code from SQL is very cool. Is it very slow?
Re:Sqlite 3.30.0 by oefrha on Saturday October 05, 2019 @09:52AM (#21165483)
It simply uses the sqlite3_create_function API, so it’s as fast/slow as your Python function. You can even use Cython if pure Python isn’t fast enough. I use UDFs quite a bit and the performance is fine without extra tweaks most of the time, but for something like pow I’d use the math extension instead which would be way more performant.

Btw, user-defined scalar functions aren’t remotely as sexy as user-defined window functions.

Re:Sqlite 3.30.0 by sevensor on Saturday October 05, 2019 @11:15AM (#21165967)
Beware that only recent versions of Python have a sqlite3 recent enough to support window functions. This bit me recently; I had to scrap a prototype, pull all the data out, and use numpy instead. Still, in general, SQLite3 is amazing and it's my automatic first choice when working with 100GiB or less.
Re:Sqlite 3.30.0 by oefrha on Saturday October 05, 2019 @11:30AM (#21166060)
Python links to sqlite3 dynamically so you just need to sub in a more recent sqlite3 (>=3.25.0). I don’t think it has anything to do with the Python version.
Re:Sqlite 3.30.0 by kyberias on Saturday October 05, 2019 @07:25AM (#21164942)
Being able to call python code from SQL is super scary!
Re:Sqlite 3.30.0 by sbierwagen on Saturday October 05, 2019 @12:04PM (#21166245)
What attacks do you believe it enables?
Re:Sqlite 3.30.0 by kragen on Saturday October 05, 2019 @06:53PM (#21168513)
I haven't benchmarked it.