====== SQLAlchemy ======

  * Site web : [[https://www.sqlalchemy.org/]]
  * Documentation : [[https://docs.sqlalchemy.org/en/20/index.html]] (version 2.0)
  * Tutorial : [[https://docs.sqlalchemy.org/en/20/tutorial/index.html]]
  * Firebird : [[https://docs.sqlalchemy.org/en/20/dialects/firebird.html]]
  * Doc de SQLAlchemy-Firebird : [[https://pypi.org/project/sqlalchemy-firebird/]]

===== Installation =====

Avec le support de Firebird :

<code bash>
pip install sqlalchemy fdb sqlalchemy-firebird
</code>

Tester la version :

<code python>
import sqlalchemy
sqlalchemy.__version__
</code>

===== Configuration de la connexion =====

<code python>
from sqlalchemy import create_engine
import sys

# Paramètres de connexion
user_name = "sysdba"
password  = "masterkey"
server    = "localhost"
dbpath    = "c:/path/to/database.fdb"   # sous Linux : "/path/to/database.fdb"

# Chemin vers la librairie cliente
fb_library_name = sys.path[0] + '\\fbclient.dll'

engine = create_engine("firebird+fdb://%s:%s@%s/%s?fb_library_name=%s" % (
    user_name,
    password,
    server,
    dbpath,
    fb_library_name
))
</code>

===== Requêtes SQL =====

Requête Select :

<code python>
from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(text("select id, nom from clients order by nom"))
    
    # Afficher tous les résultats dans une liste de tuples :
    print(result.all())
    
    # Parcourir le résultat et afficher les lignes individuellement :
    for row in result:
        print(f"{row.id} : {row.nom.strip()}")
</code>

Requêtes transactionnelles avec un **commit()** à la fin :

<code python>
from sqlalchemy import text

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()
</code>

Requêtes transactionnelles dans un bloc **begin()** (le commit se fait implicitement) :

<code python>
from sqlalchemy import text

with engine.begin() as conn:
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 1, "y": 1}, {"x": 2, "y": 4}],
    )
</code>

===== Modèle =====

<code python>
from sqlalchemy     import Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "mytable"
    id   = Column(Integer, primary_key=True)
    name = Column(String(100))
    
    def __repr__(self):
        return u"<User (%d)%s>" % (self.id, self.name.strip())
</code>

===== Requête =====

<code python>
from sqlalchemy import select

stmt = select(User).where(User.id < 20)
users = session.scalars(stmt).all()

for user in users:
    print(user)
</code>