Basic Usage Example

This example shows how to make an engine, and run a basic query.

import polars as pl
from sqlalchemy import select, text

from polars_hist_db.core import DataframeOps, TableOps, make_engine

engine = make_engine(
    backend="mariadb",
    hostname="127.0.0.1",
    port=3306,
    username="root",
    password="admin",
)

with engine.begin() as connection:
    tbl = TableOps("information_schema", "users", connection).get_table_metadata()
    query = select(tbl).limit(5)
    df = DataframeOps(connection).from_selectable(
        query, {"PASSWORD_EXPIRATION_TIME": pl.String}
    )

print(df)

shape: (5, 3)
┌───────────────────────────┬─────────────────┬──────────────────────────┐
│ USER                      ┆ PASSWORD_ERRORS ┆ PASSWORD_EXPIRATION_TIME │
│ ---                       ┆ ---             ┆ ---                      │
│ cat                       ┆ i64             ┆ str                      │
╞═══════════════════════════╪═════════════════╪══════════════════════════╡
│ 'admin'@'%'               ┆ 1               ┆ null                     │
│ 'healthcheck'@'localhost' ┆ 0               ┆ null                     │
│ 'healthcheck'@'::1'       ┆ 0               ┆ null                     │
│ 'healthcheck'@'127.0.0.1' ┆ 0               ┆ null                     │
│ 'mariadb.sys'@'localhost' ┆ 0               ┆ 0000-00-00 00:00:00      │
└───────────────────────────┴─────────────────┴──────────────────────────┘

An equivalent command using only SQLAlchemy and Polars is below. Note type types of the columns.

with engine.begin() as connection:
    res = connection.execute(text("select * from information_schema.users limit 5"))
    rows = res.fetchall()
    df = pl.from_records(rows)

print(df)

shape: (5, 3)
┌───────────────────────────┬─────────────────┬──────────────────────────┐
│ USER                      ┆ PASSWORD_ERRORS ┆ PASSWORD_EXPIRATION_TIME │
│ ---                       ┆ ---             ┆ ---                      │
│ str                       ┆ i64             ┆ str                      │
╞═══════════════════════════╪═════════════════╪══════════════════════════╡
│ 'admin'@'%'               ┆ 1               ┆ null                     │
│ 'healthcheck'@'localhost' ┆ 0               ┆ null                     │
│ 'healthcheck'@'::1'       ┆ 0               ┆ null                     │
│ 'healthcheck'@'127.0.0.1' ┆ 0               ┆ null                     │
│ 'mariadb.sys'@'localhost' ┆ 0               ┆ 0000-00-00 00:00:00      │
└───────────────────────────┴─────────────────┴──────────────────────────┘