Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentes Révision précédente Prochaine révision | Révision précédente | ||
python:sqlalchemy [2022/03/18 07:17] marclebrun |
python:sqlalchemy [2023/12/22 08:34] (Version actuelle) marclebrun [Configuration de la connexion] |
||
---|---|---|---|
Ligne 2: | Ligne 2: | ||
* Site web : [[https://www.sqlalchemy.org/]] | * Site web : [[https://www.sqlalchemy.org/]] | ||
- | * Documentation : [[https://docs.sqlalchemy.org/en/14/index.html]] (version 1.4) | + | * Documentation : [[https://docs.sqlalchemy.org/en/20/index.html]] (version 2.0) |
- | * Firebird : [[https://docs.sqlalchemy.org/en/14/dialects/firebird.html]] | + | * 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 ===== | ===== Installation ===== | ||
+ | |||
+ | Avec le support de Firebird : | ||
<code bash> | <code bash> | ||
- | pip install sqlalchemy | + | pip install sqlalchemy fdb sqlalchemy-firebird |
</code> | </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> | ||