SQLAlchemy
De Banane Atomic
Aller à la navigationAller à la recherche
Liens
Engine
|
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+mysqlconnector://user:password@localhost/dbname', echo=True)
# echo=True pour loguer toutes les opération sur la bdd
|
Mapping
|
|
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, SMALLINT, DATE, NVARCHAR
from sqlalchemy.dialects.mysql import TINYINT
Base = declarative_base()
class MyMappingClass(Base):
__tablename__ = 'my_table'
# Valeurs par défaut
__table_args__ = { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_ci' }
# on hérite de la méthode __init__ de Base qui permet de passer en paramètre les colonnes
id = Column(SMALLINT, primary_key=True)
column1 = Column(NVARCHAR(50), unique=True)
column2 = Column(TINYINT(unsigned=True), nullable=False)
|
Création de la table
|
|
Base.metadata.create_all(engine) |
|
CREATE TABLE my_table (
id SMALLINT NOT NULL,
column1 NATIONAL VARCHAR(50),
column2 TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (id),
UNIQUE (column1)
)ENGINE=InnoDB COLLATE utf8mb4_unicode_ci CHARSET=utf8mb4
|
Ajouter et mettre à jour des données
|
|
Session = sessionmaker(bind=engine)
session = Session()
mmc = MyMappingClass(column1 = '1', column2 = 1)
session.add(mmc)
session.add_all([
MyMappingClass(column1 = '2', column2 = 2),
MyMappingClass(column1 = '3', column2 = 3)])
mmc = session.query(MyMappingClass).filter_by(id=1).first()
mmc.column1 = '0'
# envoie à la bdd les modifications en attente dans session
session.commit()
|
Requêtes
|
|
from sqlalchemy import exists session.query(exists().where(my_object.id == MyClass.id)).scalar() # True or False |
|
|
SELECT EXISTS (
SELECT *
FROM my_table
WHERE my_table.id = %(id_1)s
) AS anon_1
|
Exécuter du code SQL
|
|
engine.execute('TRUNCATE TABLE my_table')
|
Installation
|
pacman -S python-sqlalchemy python-mysql-connector |
|
|
# test import sqlalchemy sqlalchemy.__version__ # '1.1.12' |
