SQLAlchemy: cómo agrupar por dos campos y filtrar por fecha

En este artículo, veremos cómo agrupar registros por dos campos y filtrar por fecha usando SQLAlchemy en Python.

Dado que vamos a utilizar MySQL en este artículo, también instalaremos un conector SQL para MySQL en Python. Sin embargo, ninguna de las implementaciones de código cambia con el cambio en la base de datos, excepto los conectores SQL.

pip install pymysql

Usaremos la base de datos sakila de muestra de MySQL. En esto, extraeremos algunos campos de la tabla de pagos dentro de la base de datos de sakila agrupándolos usando ` customer_id` y ` rent_id` y filtrando la consulta en el campo ` pago_fecha` . Si no tiene la base de datos sakila y desea seguir este artículo sin instalarlo, use el script SQL presente en el enlace que se menciona a continuación para crear el esquema requerido y la tabla de pagos junto con los registros.

Guión de tabla de pagos de Sakila

Como referencia, los primeros diez registros en la tabla de pagos se ven así:

La consulta SQL que estamos viendo en los dos ejemplos siguientes es:

SELECT 
    customer_id,
    rental_id,
    amount,
    payment_date
FROM payment 
WHERE payment_date > '2005-05-25'
GROUP BY customer_id, rental_id;

Método 1: usar SQLAlchemy Core para agrupar por dos campos y filtrar por fecha:

SQLAlchemy Core es un modelo centrado en el esquema que significa que todo se trata como parte de la base de datos, es decir, filas, columnas, tablas, etc. En el siguiente ejemplo, hemos creado el objeto de metadatos para acceder a los objetos de la base de datos como la tabla. Usando este objeto obtenemos los metadatos de la tabla `pago`. Esta información de metadatos se usa luego para consultar la tabla usando la sintaxis de SQLAlchemy que se menciona a continuación.

Sintaxis: sqlalchemy.engine.base.Engine(sqlalchemy.sql.selectable.Select(*entidades).filter(*criterio).group_by(*cláusulas)).all()

import sqlalchemy as db
  
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine("mysql+pymysql://root:password@localhost/sakila")
  
# CREATE THE METADATA OBJECT TO ACCESS THE TABLE
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)
  
# GET THE `payment` TABLE FROM THE METADATA OBJECT
payment = meta_data.tables['payment']
  
# PREPARING QUERY
query = db.select(
    payment.c.customer_id,
    payment.c.rental_id,
    payment.c.amount,
    payment.c.payment_date
).filter(payment.c.payment_date > '2005-05-25') \
    .group_by(payment.c.customer_id, payment.c.rental_id)
  
# EXTRACT THE RECORDS USING THE QUERY AND ENGINE
with engine.connect() as conn:
    result = conn.execute(query).all()
  
# PRINT FIRST 10 RECORDS
for i in range(10):
    r = result[i]
    print(r.customer_id, "|", r.rental_id, "|", r.amount, "|", r.payment_date)

Producción:

Método 2: usar SQLAlchemy ORM para agrupar por dos campos y filtrar por fecha:

SQLAlchemy Core utiliza una vista centrada en objetos que encapsula el esquema con objetos comerciales. Es una implementación más pythonica ya que podemos ver las tablas representadas en el formato de clase. Hemos usado este objeto de clase para consultar la tabla de pagos usando la sintaxis de SQLAlchemy que se menciona a continuación.

Sintaxis: sqlalchemy.orm.Query(*entidades).filter(*criterio).group_by(*cláusulas).all()

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
  
Base = declarative_base()
  
# DEFINE THE ENGINE (CONNECTIO OBJECT)
engine = db.create_engine("mysql+pymysql://root:password@localhost/sakila")
  
# AUTOMATICALLY MAP THE TABLE MODEL TO USE IT FOR QUERYING
class Payment(Base):
  
    __table__ = db.Table("payment", Base.metadata, autoload_with=engine)
  
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
  
# PREPARING QUERY USING SQLALCHEMY
result = session.query(
    Payment.customer_id,
    Payment.rental_id,
    Payment.amount,
    Payment.payment_date
).filter(Payment.payment_date > '2005-05-25') \
    .group_by(Payment.customer_id, Payment.rental_id) \
    .all()
  
# PRINT FIRST 10 RECORDS
for i in range(10):
    r = result[i]
    print(r.customer_id, "|", r.rental_id, "|", r.amount, "|", r.payment_date)

Producción: