SQLAlchemy Many-To-Many: Quatre façons de sélectionner des données
Voici un court article sur la sélection Many-To-Many avec SQLAlchemy. Dans le passé, j'ai utilisé la table d'association (lien) dans les requêtes ORM , car je pensais qu'elle devait être la plus rapide. Cette fois, j'ai fait un petit test pour comparer les différentes façons de sélectionner les données.
Spoiler : La méthode de la table d'association (lien) est (bien sûr) la plus rapide.
Le modèle
Nous avons une relation Many-To-Many entre les commandes et les produits.
# link table: order - product
order_mtm_product_table = sa.Table(
'order_mtm_product',
Base.metadata,
sa.Column('order_id', sa.ForeignKey('order.id')),
sa.Column('product_id', sa.ForeignKey('product.id')),
)
class Order(Base):
__tablename__ = 'order'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
products = relationship(
'Product',
secondary=order_mtm_product_table,
back_populates='orders',
)
class Product(Base):
__tablename__ = 'product'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
orders = relationship(
'Order',
secondary=order_mtm_product_table,
back_populates="products",
)
Quatre façons de sélectionner les données
Les quatre façons de sélectionner des données pour une relation de type Many-To-Many :
- Marche
- Orm
- Rejoindre
- Lien
La méthode "Walk" consiste à sélectionner la commande puis à parcourir les produits.
stmt = sa.select(Order).\
where(
Order.name == order_name,
)
order_01 = session.execute(stmt).scalars().first()
products = []
for product in order_01.products:
products.append(product)
Orm" est la façon triviale de sélectionner les produits.
stmt = sa.select(Product).\
where(
Product.orders.any(Order.name == order_name),
)
products = session.execute(stmt).scalars().all()
Join" utilise une "jointure" pour sélectionner les produits.
stmt = sa.select(Product).\
join(Product.orders).\
where(
Order.name == order_name,
)
products = session.execute(stmt).scalars().all()
Link' utilise la table de liens pour sélectionner les produits.
stmt = sa.select(Product).\
where(sa.and_(
Order.name == order_name,
# mtm
Order.id == order_mtm_product_table.c.order_id,
Product.id == order_mtm_product_table.c.product_id,
))
products = session.execute(stmt).scalars().all()
Les résultats
Nous mesurons le nombre de requêtes et le temps total pour obtenir les produits.
+------------+-------+------+----------+---------+---------
| order_name | howto | type | products | queries | msecs
+------------+-------+------+----------+---------+---------
| order_01 | 1 | Walk | 18 | 2 | 4.315
| order_01 | 2 | Orm | 18 | 1 | 1.991
| order_01 | 3 | Join | 18 | 1 | 1.681
| order_01 | 4 | Link | 18 | 1 | 1.242
+------------+-------+------+----------+---------+---------
La méthode "Walk" est la plus lente car elle utilise deux requêtes. Nous pouvons réduire ce temps à une seule requête en utilisant le chargement rapide. Si vous avez besoin d'une performance maximale, utilisez la méthode "Link".
Résultats supplémentaires après l'ajout de la recherche, du classement et de la limitation
Nous étendons les requêtes de "Orm", "Join" et "Link" pour permettre la recherche, le classement et la limitation des éléments retournés. Les résultats :
+------------+-------+------+----------+---------+---------
| order_name | howto | type | products | queries | msecs
+------------+-------+------+----------+---------+---------
| order_01 | 1 | Walk | 18 | 2 | 4.356
| order_01 | 2 | Orm | 18 | 1 | 2.686
| order_01 | 3 | Join | 18 | 1 | 2.380
| order_01 | 4 | Link | 18 | 1 | 1.657
| order_01 | 5 | Orm | 2 | 1 | 3.328
| order_01 | 6 | Join | 2 | 1 | 2.647
| order_01 | 7 | Link | 2 | 1 | 1.664
+------------+-------+------+----------+---------+---------
Aucun changement dans l'ordre de performance.
Le code
Au cas où vous voudriez essayer vous-même :
# query_mtm.py
import datetime
import sys
import sqlalchemy as sa
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
# query counter - start
# see:
# How to get SQL execution count for a query? #5709
# https://github.com/sqlalchemy/sqlalchemy/issues/5709
from sqlalchemy import event
import contextlib
@contextlib.contextmanager
def count_queries(conn):
queries = []
def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
queries.append(statement)
event.listen(conn, "before_cursor_execute", before_cursor_execute)
try:
yield queries
finally:
event.remove(conn, "before_cursor_execute", before_cursor_execute)
# query counter - end
connection_uri = 'sqlite:///query_mtm.sqlite'
engine = sa.create_engine(connection_uri, echo = True)
Base = declarative_base()
# link table: order - product
order_mtm_product_table = sa.Table(
'order_mtm_product',
Base.metadata,
sa.Column('order_id', sa.ForeignKey('order.id')),
sa.Column('product_id', sa.ForeignKey('product.id')),
)
class Order(Base):
__tablename__ = 'order'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
products = relationship(
'Product',
secondary=order_mtm_product_table,
back_populates='orders',
)
class Product(Base):
__tablename__ = 'product'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100), nullable=False)
orders = relationship(
'Order',
secondary=order_mtm_product_table,
back_populates="products",
)
Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)
# ### create products and orders ###
print('create session')
Session = sessionmaker(bind=engine)
session = Session()
n_products = 20
print('create products ...')
products = []
for i in range(n_products):
products.append(
Product(
name='name_{:02d}'.format(i)
)
)
session.add_all(products)
session.commit()
print('create orders ...')
order_00 = Order(
name='order_00',
products=[
products.pop(3),
products.pop(3),
]
)
order_01 = Order(
name='order_01',
products=products,
)
session.add_all([order_00, order_01])
session.commit()
print('close session and close all connections of the connection pool')
session.close()
engine.dispose()
# ### query products and orders ###
print('create session')
engine = sa.create_engine(connection_uri, echo=True)
Session = sessionmaker(bind=engine)
session = Session()
print('dummy query to start sqlalchemy ...')
stmt = sa.select(Order).where(Order.name == 'order_00')
order_00 = session.execute(stmt).scalars().first()
class OrderOps:
def __init__(self):
self.summary = []
def get(self, howto, howto_type, order_name, search=None):
if howto == 1:
self.ftitle('1. [{}] get order, then get products ...'.format(howto_type))
stmt = sa.select(Order).\
where(
Order.name == order_name,
)
order_01 = session.execute(stmt).scalars().first()
products = []
for product in order_01.products:
products.append(product)
return products
elif howto == 2:
self.ftitle('2. [{}] select all products from order ...'.format(howto_type))
stmt = sa.select(Product).\
where(
Product.orders.any(Order.name == order_name),
)
return session.execute(stmt).scalars().all()
elif howto == 3:
self.ftitle('3. [{}] select all products from order using join ...'.format(howto_type))
stmt = sa.select(Product).\
join(Product.orders).\
where(
Order.name == order_name,
)
return session.execute(stmt).scalars().all()
elif howto == 4:
self.ftitle('4. [{}] select all products from order using the link table ourselves ...'.format(howto_type))
stmt = sa.select(Product).\
where(sa.and_(
Order.name == order_name,
# mtm
Order.id == order_mtm_product_table.c.order_id,
Product.id == order_mtm_product_table.c.product_id,
))
return session.execute(stmt).scalars().all()
# with search, order and limit
elif howto == 5:
self.ftitle('5. as 2. but with search, ordering and limit ...')
stmt = sa.select(Product).\
where(sa.and_(
Product.orders.any(Order.name == order_name),
Product.name.like(search),
)).\
order_by(Product.name.desc()).\
limit(3)
return session.execute(stmt).scalars().all()
elif howto == 6:
self.ftitle('6. as 3. but with search, ordering and limit ...')
stmt = sa.select(Product).\
join(Product.orders).\
where(sa.and_(
Order.name == order_name,
Product.name.like(search),
)).\
order_by(Product.name.desc()).\
limit(3)
return session.execute(stmt).scalars().all()
elif howto == 7:
self.ftitle('7. as 4. but with search, ordering and limit ...')
stmt = sa.select(Product).\
where(sa.and_(
Order.name == order_name,
Product.name.like(search),
# mtm
Order.id == order_mtm_product_table.c.order_id,
Product.id == order_mtm_product_table.c.product_id,
)).\
order_by(Product.name.desc()).\
limit(3)
return session.execute(stmt).scalars().all()
def get_products(self, howto, howto_type, order_name, search=None):
dt_start = datetime.datetime.now()
with count_queries(session.connection()) as queries:
products = self.get(howto, howto_type, order_name, search)
print("total number of queries: %s" % len(queries))
products_len = len(products)
msecs = (datetime.datetime.now() - dt_start).total_seconds()*1000
self.summary.append(dict(
order_name=order_name,
howto=howto,
howto_type=howto_type,
products_cnt=products_len,
queries_cnt=len(queries),
msecs=msecs,
))
print('{} products: total {}, executed in {:.2f} msecs'.format(order_name, products_len, msecs))
print(*('{:02d}: {}'.format(i, x.name) for i, x in enumerate(products)), sep='\n')
def ftitle(self, title):
print('+-{}'.format('-'*60))
print('| {}'.format(title))
print('+-{}'.format('-'*60))
def print_summary(self):
hs = '+-{}-+-{}-+-{}-+-{}-+-{}-+-{}'.format('-'*10, '-'*5, '-'*4, '-'*8, '-'*7, '-'*8)
print(hs)
print('| {} | {} | {} | {} | {} | {}'.format('order_name', 'howto', 'type', 'products', 'queries', 'msecs'))
print(hs)
for s in self.summary:
print('| {: <10} | {: >5} | {: <4} | {: >8} | {: >7} | {}'.format(s['order_name'], s['howto'], s['howto_type'], s['products_cnt'], s['queries_cnt'], s['msecs']))
print(hs)
order_ops = OrderOps()
order_ops.get_products(1, 'Walk', 'order_01')
order_ops.get_products(2, 'Orm ', 'order_01')
order_ops.get_products(3, 'Join', 'order_01')
order_ops.get_products(4, 'Link', 'order_01')
order_ops.print_summary()
order_ops.get_products(5, 'Orm ', 'order_01', '%2%')
order_ops.get_products(6, 'Join', 'order_01', '%2%')
order_ops.get_products(7, 'Link', 'order_01', '%2%')
order_ops.print_summary()
Résumé
Bien sûr, ce test est très limité. Mais je pense qu'il est très important que vous sachiez ce que vous faites lorsque vous choisissez une méthode. Si vous avez besoin d'une performance maximale, utilisez la méthode 'Link',
elle n'est pas plus difficile que les autres méthodes.
Liens / crédits
How to get SQL execution count for a query? #5709
https://github.com/sqlalchemy/sqlalchemy/issues/5709
SQLAlchemy - Basic Relationship Patterns
https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html
En savoir plus...
SQLAlchemy
Récent
- Don't Repeat Yourself (DRY) avec Jinja2
- SQLAlchemy, PostgreSQL, nombre maximal de lignes par user
- Afficher les valeurs des filtres dynamiques SQLAlchemy
- Transfert de données sécurisé grâce au cryptage à Public Key et à pyNaCl
- rqlite : une alternative à haute disponibilité et dist distribuée SQLite
- Dois-je migrer mon Docker Swarm vers Kubernetes ?
Les plus consultés
- Utilisation des Python's pyOpenSSL pour vérifier les certificats SSL téléchargés d'un hôte
- Utiliser UUIDs au lieu de Integer Autoincrement Primary Keys avec SQLAlchemy et MariaDb
- Connexion à un service sur un hôte Docker à partir d'un conteneur Docker
- SQLAlchemy : Utilisation de Cascade Deletes pour supprimer des objets connexes
- Utiliser PyInstaller et Cython pour créer un exécutable Python
- Flask RESTful API validation des paramètres de la requête avec les schémas Marshmallow