8000 [Python] Add the SQLExpression method to the Expression API by Tishj · Pull Request #16424 · duckdb/duckdb · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

[Python] Add the SQLExpression method to the Expression API #16424

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 10 commits into from
Feb 27, 2025
Merged

[Python] Add the SQLExpression method to the Expression API #16424

merged 10 commits into from
Feb 27, 2025

Conversation

Tishj
Copy link
Contributor
@Tishj Tishj commented Feb 26, 2025 •
edited
Loading

This PR addresses some issues mentioned here:
#16418
#14696
#8469

With the SQLExpression method it's possible to create any arbitrary expression that would be possible through SQL.
This is identical to what happens under the hood with rel.select('<some arbitrary expression list>') only the result is wrapped in an Expression and can be used with the rest of the Expression API.

@duckdb-draftbot duckdb-draftbot marked this pull request as draft February 26, 2025 12:07
@Tishj Tishj force-pushed the python_sql_expression branch from a3f3a08 to ea2f27f Compare February 26, 2025 13:20
Tishj added 2 commits February 26, 2025 14:27
@Tishj
is the issue that it's not 'nullptr' but it's 'py::none()' and doesn'…
3c2e298 
…t get turned into nullptr like I was expecting? - in which case adding this include would fix it
@Tishj
alright, I give up, no optional 'connection' argument
fdc60a6
@Tishj Tishj marked this pull request as ready for review February 26, 2025 13:35
@duckdb-draftbot duckdb-draftbot marked this pull request as draft February 26, 2025 14:06
@Tishj Tishj marked this pull request as ready for review February 26, 2025 14:07
@Mytherin Mytherin merged commit 0e0d66b into duckdb:main Feb 27, 2025
21 checks passed
@Mytherin
Copy link
Collaborator

Thanks!

@Tishj Tishj added the Needs Documentation Use for issues or PRs that require changes in the documentation label Feb 27, 2025
@duckdblabs-bot duckdblabs-bot mentioned this pull request Feb 27, 2025
Closed
@MarcoGorelli
Copy link
Contributor
MarcoGorelli commented Mar 18, 2025 •
edited
Loading

Amazing, most exciting tech news of the month!

If I wanted to create a SQLExpression using another duckdb expression, would str be a reliable way to do so?

E.g. would it be safe for users to do

import duckdb

expr = duckdb.FunctionExpression('mean', duckdb.ColumnExpression('a'))

new_expr = duckdb.SQLExpression(f'{expr} over (partition by b)')

?

For example:

In [15]: rel
Out[15]:
┌───────┬───────┬───────┐
│   a   │   b   │   i   │
│ int32 │ int32 │ int32 │
├───────┼───────┼───────┤
│     1 │     4 │     0 │
│     2 │     5 │     1 │
│     1 │     6 │     2 │
└───────┴───────┴───────┘

In [16]: agg = duckdb.FunctionExpression('sum', duckdb.ColumnExpression('b'))

In [17]: rel.select('*', duckdb.SQLExpression(f"{agg} over (partition by a order by i rows between unbounded preceding and current row)").alias('b_cum_sum')).sort('a', 'i')
Out[17]:
┌───────┬───────┬───────┬───────────┐
│   a   │   b   │   i   │ b_cum_sum │
│ int32 │ int32 │ int32 │  int128   │
├───────┼───────┼───────┼───────────┤
│     1 │     4 │     0 │         4 │
│     1 │     6 │     2 │        10 │
│     2 │     5 │     1 │         5 │
└───────┴───────┴───────┴───────────┘

seems to work fine, is this valid usage?

krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 15, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@d768632
7314e6e 
[Dev] Mention the problematic type in UNNEST BinderException (duckdb/duckdb#16429)
[Python] Add the SQLExpression method to the Expression API (duckdb/duckdb#16424)
krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 15, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@d768632
4e960a7 
[Dev] Mention the problematic type in UNNEST BinderException (duckdb/duckdb#16429)
[Python] Add the SQLExpression method to the Expression API (duckdb/duckdb#16424)
krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 16, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@d768632
34ef8fe 
[Dev] Mention the problematic type in UNNEST BinderException (duckdb/duckdb#16429)
[Python] Add the SQLExpression method to the Expression API (duckdb/duckdb#16424)
krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 17, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@d768632
f1057e9 
[Dev] Mention the problematic type in UNNEST BinderException (duckdb/duckdb#16429)
[Python] Add the SQLExpression method to the Expression API (duckdb/duckdb#16424)
krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 18, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@d768632
42a4402 
[Dev] Mention the problematic type in UNNEST BinderException (duckdb/duckdb#16429)
[Python] Add the SQLExpression method to the Expression API (duckdb/duckdb#16424)
krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 18, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@d768632
3e7c3e8 
[Dev] Mention the problematic type in UNNEST BinderException (duckdb/duckdb#16429)
[Python] Add the SQLExpression method to the Expression API (duckdb/duckdb#16424)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees
No one assigned
Labels
Needs Documentation Use for issues or PRs that require changes in the documentation
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
3 participants
@Tishj @Mytherin @MarcoGorelli
0