neuralib.sqlp.stat.SqlSelectStat

class neuralib.sqlp.stat.SqlSelectStat[source]

Bases: SqlStat[T], SqlWhereStat, SqlLimitStat, Generic[T]

SELECT statement.

__init__(table)[source]
Parameters:

table (type[T] | None)

fetchall()[source]

submit and fetch all result.

Return type:

list[T]

fetchone()[source]

submit and fetch the first result.

Return type:

T | None

fetch_polars()[source]

submit and fetch all as a polar dataframe.

Return type:

DataFrame

windows(*windows, **window_ks)[source]

define windows.

Parameters:
Return type:

Self

BY

alias of Literal[‘left’, ‘right’, ‘inner’, ‘full outer’, ‘cross’]

join(constraint: Callable | ForeignConstraint, *, by: BY = None) → SqlSelectStat[tuple][source]
join(table: type[S] | SqlAlias[S], constraint: Callable | ForeignConstraint, *, by: BY = None) → SqlSelectStat[tuple]
join(table: type[S] | SqlSelectStat[S] | SqlAlias[S] | SqlCteExpr, *field: bool | Any, by: BY = None) → SqlSelectStat[tuple]
join(*field: bool | Any, by: BY = None) → SqlSelectStat[tuple]

JOIN https://www.sqlite.org/lang_select.html#strange_join_names

>>> select_from(A.a, B.b).join(A.a == B.a) 
SELECT A.a, B.b FROM A
JOIN B ON A.a = B.a
group_by(*by)[source]

GROUP BY https://www.sqlite.org/lang_select.html#resultset

Return type:

Self

having(*exprs)[source]

HAVING https://www.sqlite.org/lang_select.html#resultset

Parameters:

exprs (bool | SqlExpr)

Return type:

Self

intersect(stat)[source]

INTERSECT https://www.sqlite.org/lang_select.html#compound_select_statements

Parameters:

stat (SqlStat)

Return type:

Self

union(stat, all=False)[source]

UNION https://www.sqlite.org/lang_select.html#compound_select_statements

Parameters:

stat (SqlStat)

Return type:

Self

except_(stat)[source]

EXCEPT https://www.sqlite.org/lang_select.html#compound_select_statements

Parameters:

stat (SqlStat)

Return type:

Self

add(stat)

Add SQL token.

Return type:

Self

build()

build a SQL statement.

Return type:

tuple[str, list]

drop()
limit(*args)

LIMIT: https://www.sqlite.org/lang_select.html#limitoffset

>>> select_from(A).limit(10).build() 
SELECT * FROM A LIMIT 10
>>> select_from(A).limit(10, 10).build() 
SELECT * FROM A LIMIT 10 OFFSET 10

NOTE

LIMIT on UPDATE/DELETE need compile flag SQLITE_ENABLE_UPDATE_DELETE_LIMIT.

>>> assert Connection().sqlite_compileoption_used('SQLITE_ENABLE_UPDATE_DELETE_LIMIT')
Parameters:

args (int)

Return type:

Self

order_by(*by)

ORDER BY: https://www.sqlite.org/lang_select.html#orderby

>>> select_from(A).order_by(A.a).build() 
SELECT * FROM A ORDER BY A.

possible ordering

>>> select_from(A).order_by( 
...     asc(A.a), desc(A.b), nulls_first(A.c), asc(A.d).nulls_last(),
... )
SELECT * FROM A ORDER BY
A.a ASC, A.b DESC, A.c NULLS FIRST, A.d ASC NULLS LAST

NOTE

ORDER BY on UPDATE/DELETE need compile flag SQLITE_ENABLE_UPDATE_DELETE_LIMIT.

>>> assert Connection().sqlite_compileoption_used('SQLITE_ENABLE_UPDATE_DELETE_LIMIT')
Parameters:

by (int | str | SqlExpr | Any)

Return type:

Self

submit()

build the SQL statement and execute.

Returns:

a cursor

Raises:

RuntimeError – current statement does not bind with a connection.

Return type:

Cursor[T]

where(*expr)

WHERE clause: https://www.sqlite.org/lang_select.html#whereclause

>>> select_from(A).where( 
...     A.a == 1, A.b == 2
... ).build()
SELECT * FROM A
WHERE (A.a = 1) AND (A.b = 2)
Parameters:

expr (bool | SqlCompareOper | SqlExpr | None)

Returns:

Return type:

Self