"""
https://docs.sqlalchemy.org/en/14/tutorial/index.html#unified-tutorial
https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#tutorial-working-with-transactions
"""

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy import text


def connection_hello(engine):
    with engine.connect() as conn:
        result = conn.execute(text("select 'hello world'"))
        print(result.all())


def commiting_changes(engine):
    # "commit as you go"
    with engine.connect() as conn:
        conn.execute(text("CREATE TABLE 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()


def explicit_transaction_begin(engine):
    # begin once
    with engine.begin() as conn:
        conn.execute(
            text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
            [{"x": 6, "y": 8}, {"x": 9, "y": 10}],
        )


def fetching_rows(engine):
    with engine.connect() as conn:
        result = conn.execute(text("SELECT x, y FROM some_table"))
        # The Row objects themselves are intended to act like Python named tuples.
        for row in result:
            print(f"x:{row.x} y: {row.y}")


def sending_parameter(engine):
    with engine.connect() as conn:
        # The Connection.execute() method therefore also accepts parameters,
        # which are referred towards as bound parameters (Prepared Statement).
        # https://use-the-index-luke.com/sql/where-clause/bind-parameters
        result = conn.execute(
            text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2}
        )
        for row in result:
            print(f"x:{row.x} y: {row.y}")


def sending_multiple_parameters(engine):
    with engine.connect() as conn:
        # For statements that operate upon data, but do not return result sets,
        # namely DML statements, we can send multi params to the Connection.execute()
        # method by passing a list of dictionaries instead of a single dictionary,
        # thus allowing the single SQL statement to be invoked against each parameter
        # set individually
        conn.execute(
            text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
            [{"x": 11, "y": 12}, {"x": 13, "y": 14}],
        )
        conn.commit()


def building_prepared_statement(engine):
    # Bundling the parameters with the statement is a primary feature of the SQL
    # Expression Language. The text() construct itself being part of the SQL
    # Expression Language supports this feature by using the TextClause.bindparams()
    # method.
    stmt = text(
        "SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y"
    ).bindparams(y=6)
    with engine.connect() as conn:
        result = conn.execute(stmt)
        for row in result:
            print(f"x:{row.x} y: {row.y}")


def executing_with_orm_session(engine):
    # The fundamental transactional / database interactive object when using
    # the ORM is called the Session. In modern SQLAlchemy, this object is
    # used in a manner very similar to that of the Connection, and in fact as
    # the Session is used, it refers to a Connection internally which it uses
    # to emit SQL.
    # When the Session is used with non-ORM constructs, it passes through the
    # SQL statements we give it and does not generally do things much differently
    # from how the Connection does directly, so we can illustrate it here in
    # terms of the simple textual SQL operations we’ve already learned.
    stmt = text(
        "SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y"
    ).bindparams(y=6)
    with Session(engine) as session:
        result = session.execute(stmt)
        for row in result:
            print(f"x:{row.x} y: {row.y}")


def session_commit_as_you_go(engine):
    with Session(engine) as session:
        result = session.execute(
            text("UPDATE some_table SET y=:y WHERE x=:x"),
            [{"x": 9, "y": 11}, {"x": 13, "y": 15}],
        )
        session.commit()


if __name__ == "__main__":
    # We’re using the name pysqlite, which in modern Python use is the sqlite3
    # standard library interface for SQLite. If omitted, SQLAlchemy will use a
    # default DBAPI for the particular database selected.

    # For metadata reflection demo write table to file
    # dbname = "sqlite+pysqlite:///some_table.db"

    dbname = "sqlite+pysqlite:///:memory:"

    engine = create_engine(dbname, echo=True, future=True)

    connection_hello(engine)

    commiting_changes(engine)
    explicit_transaction_begin(engine)

    fetching_rows(engine)
    sending_parameter(engine)

    sending_multiple_parameters(engine)

    building_prepared_statement(engine)

    executing_with_orm_session(engine)

    session_commit_as_you_go(engine)