jupysql + duckdb breaks if passing :NUMBER

[edublancas]

Jupysql thinks that :NUMBER is a parameter in the query, but duckdb uses this syntax for slicing strings (just like Python), we need to fix and escape it so it works just like when we're using duckdb (I think we're already escaping some parameters so we can use that code as a reference).

I can escape manually to get it working:

but we should do the escaping automatically

Notebook:

import duckdb

conn = duckdb.connect()

conn.execute("select 'hello'[:2]").fetchall()

[('he',)]

%load_ext sql

%sql duckdb://

Connecting to 'duckdb://'

Set named_parameters to False (this is actual the default so shouldn't make a difference)

%config SqlMagic.named_parameters=False

This breaks, jupysql thinks [:2] is a parameter:

%%sql
select 'hello'[:2]

Running query in 'duckdb://'

RuntimeError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter '2'
[SQL: select 'hello'[?]]
[parameters: [{}]]
(Background on this error at: https://sqlalche.me/e/20/cd3x)
If you need help solving this issue, send us a message: https://ploomber.io/community

[edublancas]

@bryannho what's the status here?

[bryannho]

@bryannho what's the status here?

Started working on this today, barring issues should be done by early next week