SqlAlchemy¶
Description:
- Work with data in MariaDB via SqlAlchemy
Resources:
- [GitHub] https://github.com/sqlalchemy/sqlalchemy
- [Examples] https://github.com/sqlalchemy/sqlalchemy/tree/main/examples
- [Docs] https://docs.sqlalchemy.org/en/20/
- [Docs - MariaDB] https://docs.sqlalchemy.org/en/20/dialects/mysql.html#module-sqlalchemy.dialects.mysql.mariadbconnector
- [Tutorial] https://docs.sqlalchemy.org/en/20/tutorial/index.html
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
MARIADB_HOST: hostname of your MariaDB serverMARIADB_USER: MariaDB user nameMARIADB_PASSWORD: password for that userMARIADB_DATABASE: database to connect to (must exist)
Example 1¶
Connect to a remote MariaDB database, insert data, and execute a query
In [ ]:
Copied!
import os
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import text
userpwd = os.environ["MARIADB_PASSWORD"]
host = os.environ["MARIADB_HOST"]
user = os.environ["MARIADB_USER"]
database = os.environ["MARIADB_DATABASE"]
dsn = f"mariadb+mariadbconnector://{user}:{userpwd}@{host}/{database}"
def example_mariadb_sqlalchemy() -> None:
m = MetaData(naming_convention={"ix": "ix_%(column_0N_name)s"})
t = Table(
"t",
m,
Column("some_column_name_1", Integer),
Column("some_column_name_2", Integer),
Column("some_column_name_3", Integer),
)
Index(
None,
t.c.some_column_name_1,
t.c.some_column_name_2,
t.c.some_column_name_3,
)
engine = create_engine(dsn, echo=True, echo_pool="debug")
# Create table "t" with its index, named by the naming convention above
m.create_all(engine)
with engine.connect() as conn:
print("Connected to db")
conn.execute(text("CREATE TABLE IF NOT EXISTS some_table (x int, y int)"))
conn.execute(
text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
[{"x": 1, "y": 1}, {"x": 2, "y": 4}],
)
conn.commit()
result = conn.execute(text("SELECT x, y FROM some_table"))
for row in result:
print(f"x: {row.x} y: {row.y}")
example_mariadb_sqlalchemy()
import os
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import text
userpwd = os.environ["MARIADB_PASSWORD"]
host = os.environ["MARIADB_HOST"]
user = os.environ["MARIADB_USER"]
database = os.environ["MARIADB_DATABASE"]
dsn = f"mariadb+mariadbconnector://{user}:{userpwd}@{host}/{database}"
def example_mariadb_sqlalchemy() -> None:
m = MetaData(naming_convention={"ix": "ix_%(column_0N_name)s"})
t = Table(
"t",
m,
Column("some_column_name_1", Integer),
Column("some_column_name_2", Integer),
Column("some_column_name_3", Integer),
)
Index(
None,
t.c.some_column_name_1,
t.c.some_column_name_2,
t.c.some_column_name_3,
)
engine = create_engine(dsn, echo=True, echo_pool="debug")
# Create table "t" with its index, named by the naming convention above
m.create_all(engine)
with engine.connect() as conn:
print("Connected to db")
conn.execute(text("CREATE TABLE IF NOT EXISTS some_table (x int, y int)"))
conn.execute(
text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
[{"x": 1, "y": 1}, {"x": 2, "y": 4}],
)
conn.commit()
result = conn.execute(text("SELECT x, y FROM some_table"))
for row in result:
print(f"x: {row.x} y: {row.y}")
example_mariadb_sqlalchemy()