polars.LazyFrame.sql#
- LazyFrame.sql(query: str, *, table_name: str | None = None) Self [source]#
Execute a SQL query against the LazyFrame.
New in version 0.20.23.
Warning
This functionality is considered unstable, although it is close to being considered stable. It may be changed at any point without it being considered a breaking change.
- Parameters:
- query
SQL query to execute.
- table_name
Optionally provide an explicit name for the table that represents the calling frame (the alias “self” will always be registered/available).
See also
Notes
The calling frame is automatically registered as a table in the SQL context under the name “self”. All DataFrames and LazyFrames found in the current set of global variables are also registered, using their variable name.
More control over registration and execution behaviour is available by using the
SQLContext
object.
Examples
>>> lf1 = pl.LazyFrame({"a": [1, 2, 3], "b": [6, 7, 8], "c": ["z", "y", "x"]}) >>> lf2 = pl.LazyFrame({"a": [3, 2, 1], "d": [125, -654, 888]})
Query the LazyFrame using SQL:
>>> lf1.sql("SELECT c, b FROM self WHERE a > 1").collect() shape: (2, 2) ┌─────┬─────┐ │ c ┆ b │ │ --- ┆ --- │ │ str ┆ i64 │ ╞═════╪═════╡ │ y ┆ 7 │ │ x ┆ 8 │ └─────┴─────┘
Join two LazyFrames:
>>> lf1.sql( ... ''' ... SELECT self.*, d ... FROM self ... INNER JOIN lf2 USING (a) ... WHERE a > 1 AND b < 8 ... ''' ... ).collect() shape: (1, 4) ┌─────┬─────┬─────┬──────┐ │ a ┆ b ┆ c ┆ d │ │ --- ┆ --- ┆ --- ┆ --- │ │ i64 ┆ i64 ┆ str ┆ i64 │ ╞═════╪═════╪═════╪══════╡ │ 2 ┆ 7 ┆ y ┆ -654 │ └─────┴─────┴─────┴──────┘
Apply SQL transforms (aliasing “self” to “frame”) and subsequently filter natively (you can freely mix SQL and native operations):
>>> lf1.sql( ... query=''' ... SELECT ... a, ... (a % 2 == 0) AS a_is_even, ... (b::float4 / 2) AS "b/2", ... CONCAT_WS(':', c, c, c) AS c_c_c ... FROM frame ... ORDER BY a ... ''', ... table_name="frame", ... ).filter(~pl.col("c_c_c").str.starts_with("x")).collect() shape: (2, 4) ┌─────┬───────────┬─────┬───────┐ │ a ┆ a_is_even ┆ b/2 ┆ c_c_c │ │ --- ┆ --- ┆ --- ┆ --- │ │ i64 ┆ bool ┆ f32 ┆ str │ ╞═════╪═══════════╪═════╪═══════╡ │ 1 ┆ false ┆ 3.0 ┆ z:z:z │ │ 2 ┆ true ┆ 3.5 ┆ y:y:y │ └─────┴───────────┴─────┴───────┘