SQLAlchemy ORM – Consulta

En este artículo, veremos cómo consultar usando SQLAlchemy ORM en Python.

Para continuar con este artículo, necesitamos tener sqlalchemy y cualquier base de datos instalada en nuestro sistema. Hemos utilizado la base de datos MySQL para la comprensión de este artículo.

Creó una tabla de perfil y una tabla de estudiantes:

Aquí vamos a cubrir los siguientes métodos:

• añadir_columnas()
• añadir_entidad()
• contar()
• Eliminar()
• distinto()
• filtrar()
• obtener()
• primero()
• agrupar por()
• unirse()
• una()

añadir_columnas()

El método add_columns() ayuda a agregar columnas o entidades adicionales a la consulta existente. Como se puede ver en el resultado, la consulta inicial solo constaba de la columna first_name, pero más tarde, cuando usamos el método add_columns(), también pudimos agregar las columnas last_name y curso. Tenga en cuenta que add_column() está en desuso y debe evitarse; en su lugar, podemos utilizar el método add_columns() (como se muestra arriba) o with_entities().

Sintaxis: sqlalchemy.orm.Query.add_columns(*columna)

Dónde: Agregue una o más expresiones de columna a la lista de columnas de resultados que se devolverán.

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Students(Base):
 
    __tablename__ = 'students'
 
    first_name = db.Column(db.String(50),
                           primary_key=True)
    last_name = db.Column(db.String(50),
                          primary_key=True)
    course = db.Column(db.String(50),
                       primary_key=True)
    score = db.Column(db.Float)
 
 
# CREATE A SESSION OBJECT TO INITIATE QUERY
# IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
 
# SELECT first_name FROM students
result = session.query(Students.first_name)
print("Query 1:", result)
 
# SELECT first_name, last_name, course
# FROM students
result = result.add_columns(Students.last_name,
                            Students.course)
print("Query 2:", result)
 
# VIEW THE ENTRIES IN THE RESULT
for r in result:
    print(r.first_name, "|", r.last_name, "|", r.course)

Producción:

contar()

El método count() es un sinónimo del COUNT que usamos en las consultas SQL. Devuelve el número de registros presentes en la tabla. En nuestro caso, la tabla de estudiantes consta de 12 registros, lo mismo se puede verificar en la captura de pantalla de la tabla de estudiantes que se muestra al principio.

Sintaxis: sqlalchemy.orm.Query.count()

Devuelve un recuento de filas que devolvería el SQL formado por esta consulta.

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Students(Base):
 
    __tablename__ = 'students'
 
    first_name = db.Column(db.String(50), primary_key=True)
    last_name = db.Column(db.String(50), primary_key=True)
    course = db.Column(db.String(50), primary_key=True)
    score = db.Column(db.Float)
 
 
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
 
# SELECT * FROM PROFILE
result = session.query(Students).count()
 
# VIEW THE RESULT
print("Count:", result)

Producción:

Count: 12

distinto()

El método distinto() de sqlalchemy es un sinónimo del DISTINCT utilizado en SQL. Devolverá los distintos registros en función de los nombres de columna proporcionados como referencia. En el ejemplo anterior, hemos tomado los distintos registros presentes en el campo first_name. De las 12 entradas, obtenemos 5 nombres únicos.

Sintaxis: sqlalchemy.orm.Query.distinct(*expr)

Aplique DISTINCT a la consulta y devuelva la consulta recién resultante.

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Students(Base):
 
    __tablename__ = 'students'
 
    first_name = db.Column(db.String(50), primary_key=True)
    last_name = db.Column(db.String(50), primary_key=True)
    course = db.Column(db.String(50))
    score = db.Column(db.Float)
 
 
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
 
# SELECT DISTINCT(first_name) FROM students;
result = session.query(Students) \
    .with_entities(db.distinct(Students.first_name)).all()
 
 
# VIEW THE ENTRIES IN THE RESULT
for r in result:
    print(r)

Producción:

Eliminar()

El método delete() eliminará el registro presente en la tabla según alguna condición. En nuestro caso, hemos proporcionado la condición de que el correo electrónico sea ravipandey@zmail.com. El método devuelve el recuento de la cantidad de registros que se vieron afectados. Después de eliminar, lo mismo se verifica utilizando el método count(). Antes de eliminar cualquier entrada, la tabla de perfiles constaba de 3 registros (como se muestra al principio). Dado que un registro se vio afectado, ahora nos quedan 2 registros en la tabla. 

Sintaxis: sqlalchemy.orm.Query.delete(synchronize_session=’evaluate’)

Realice un DELETE con una cláusula WHERE arbitraria. Elimina las filas que coinciden con esta consulta de la base de datos y devuelve el recuento de filas coincidentes según lo devuelto por la característica de «recuento de filas» de la base de datos.

import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine("mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Profile(Base):
 
    __tablename__ = 'profile'
 
    email   = db.Column(db.String(50), primary_key=True)
    name    = db.Column(db.String(100))
    contact = db.Column(db.Integer)
 
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
 
# DELETE FROM profile WHERE email = 'ravipandey@zmail.com'
result = session.query(Profile) \
    .filter(Profile.email == 'ravipandey@zmail.com') \
        .delete(synchronize_session=False)
print("Rows deleted:", result)
 
result = session.query(Profile).count()
print("Total records:", result)

Producción:

filtrar()

El método filter() funciona como la cláusula WHERE en SQL. Toma una expresión y devuelve solo aquellos registros que satisfacen la expresión proporcionada. Puede haber una o más expresiones separadas por ‘ & ‘. En el ejemplo, proporcionamos la condición LIKE para la columna de nombre en la tabla de perfil.

Sintaxis: sqlalchemy.orm.Query.filter(*criterio)

Aplicar el criterio de filtrado dado a una copia de esta Consulta, utilizando expresiones SQL.

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Profile(Base):
 
    __tablename__ = 'profile'
 
    email = db.Column(db.String(50), primary_key=True)
    name = db.Column(db.String(100))
    contact = db.Column(db.Integer)
 
 
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
 
# SELECT email FROM student WHERE name LIKE 'Amit%';
result = session.query(Profile) \
    .with_entities(Profile.email) \
    .filter(Profile.name.like('Amit%')).all()
 
# VIEW THE ENTRIES IN THE RESULT
for r in result:
    print("\n", r.email)

Producción:

agrupar por()

La cláusula GROUP BY en SQL se puede escribir en SQLAlchemy usando el método group_by(). Toma los nombres de la entidad o columna como parámetro y realiza la agrupación en función de estas columnas. El resultado que vemos arriba es el grupo por operación realizado en las columnas first_name y last_name de la tabla de estudiantes donde el puntaje se agrega mediante la función SUM.

Sintaxis: sqlalchemy.orm.Query.group_by(*cláusulas)

Aplique uno o más criterios GROUP BY a la consulta y devuelva la consulta recién resultante.

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Students(Base):
 
    __tablename__ = 'students'
 
    first_name = db.Column(db.String(50), primary_key=True)
    last_name = db.Column(db.String(50), primary_key=True)
    course = db.Column(db.String(50))
    score = db.Column(db.Float)
 
 
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
 
# SELECT first_name, last_name, SUM(score)
# AS total FROM students GROUP BY first_name, last_name;
result = session.query(Students) \
    .with_entities(
        Students.first_name,
        Students.last_name,
        db.func.sum(Students.score).label('total')
).group_by(
        Students.first_name,
        Students.last_name
).all()
 
# VIEW THE ENTRIES IN THE RESULT
for r in result:
    print(r.first_name, r.last_name, "| Score =", r[2])

Producción:

ordenar_por()

En el ejemplo anterior, hemos ordenado los registros de la tabla de estudiantes en función de la columna de puntuación. La cláusula order_by() toma los nombres de las columnas como parámetros. De forma predeterminada, se supone que se ordena en orden ascendente a menos que los objetos de columna se pasen a través del método desc().

Sintaxis: sqlalchemy.orm.Query.order_by(*cláusulas)

Aplique uno o más criterios ORDER BY a la consulta y devuelva la consulta recién resultante.

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Students(Base):
 
    __tablename__ = 'students'
 
    first_name = db.Column(db.String(50), primary_key=True)
    last_name = db.Column(db.String(50), primary_key=True)
    course = db.Column(db.String(50), primary_key=True)
    score = db.Column(db.Float)
 
 
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
 
# SELECT * FROM students ORDER BY score DESC, course;
result = session.query(Students) \
    .order_by(
        Students.score.desc(),
        Students.course
).all()
 
# VIEW THE ENTRIES IN THE RESULT
for r in result:
    print(r.first_name, r.last_name, r.course, r.score)

Producción:

primero()

El método first() devuelve el primer registro de la consulta. Es similar a aplicar LIMIT 1 al final de la consulta SQL. En el resultado, podemos ver que pudimos obtener un registro de la tabla. Sin embargo, si tratamos de iterar a través del resultado, obtenemos un error de que el objeto no es iterable, debido a que solo hay un registro presente en el resultado.

Sintaxis: sqlalchemy.engine.Result.first()

Obtener la primera fila o Ninguno si no hay ninguna fila presente. Cierra el conjunto de resultados y descarta las filas restantes.

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Profile(Base):
 
    __tablename__ = 'profile'
 
    email = db.Column(db.String(50), primary_key=True)
    name = db.Column(db.String(100))
    contact = db.Column(db.Integer)
 
 
class Students(Base):
 
    __tablename__ = 'students'
 
    first_name = db.Column(db.String(50), primary_key=True)
    last_name = db.Column(db.String(50), primary_key=True)
    course = db.Column(db.String(50), primary_key=True)
    score = db.Column(db.Float)
 
 
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
 
# SELECT * FROM profile LIMIT 1
result = session.query(Profile).first()
 
print(result.email, "|", result.name, "|", result.contact)
 
# VIEW THE ENTRIES IN THE RESULT
for r in result:
    print(r.email, r.name, r.contact)

Producción:

una()

Los métodos one() y first() pueden sonar similares, pero no lo son. En el ejemplo anterior, vemos un seguimiento de error porque el método one() intenta obtener registros de una tabla que tienen una sola entrada. Si la tabla tiene 0, 2 o más entradas, genera una excepción. Su uso es bastante raro.

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Profile(Base):
 
    __tablename__ = 'profile'
 
    email = db.Column(db.String(50), primary_key=True)
    name = db.Column(db.String(100))
    contact = db.Column(db.Integer)
 
 
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
 
# profile table should contain only one row, else error
result = session.query(Profile).one()

Producción:

unirse()

Para comprender el método join(), hemos creado una tabla adicional llamada ubicación que tiene dos columnas, a saber, correo electrónico y ubicación. Hemos insertado una entrada en esta tabla con correo electrónico como ‘amitpathak@zmail.com’ y ubicación como ‘Mumbai’. Ya tenemos una entrada con este ID de correo electrónico en la tabla de perfil, así que intentemos unir estas dos tablas en la columna de correo electrónico. dentro del método join(), el primer argumento es la otra tabla (perfil) a la que necesitamos unirnos, y el segundo argumento en la condición (ON en SQL). 

Sintaxis: sqlalchemy.orm.Query.join(target, *props, **kwargs)

Cree un SQL JOIN contra el criterio de este objeto de consulta y aplíquelo generativamente, devolviendo la consulta recién resultante.

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
 
# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine(
    "mysql+pymysql://root:password@localhost/Geeks4Geeks")
 
# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Profile(Base):
 
    __tablename__ = 'profile'
 
    email = db.Column(db.String(50), primary_key=True)
    name = db.Column(db.String(100))
    contact = db.Column(db.Integer)
 
 
class Location(Base):
 
    __tablename__ = 'location'
 
    email = db.Column(db.String(50), primary_key=True)
    location = db.Column(db.String(100))
 
 
# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()
 
# SELECT * FROM PROFILE
result = session.query(
    Profile.email,
    Profile.name,
    Profile.contact,
    Location.location
).join(Location, Profile.email == Location.email)
 
print("Query:", result)
print()
 
# VIEW THE ENTRIES IN THE RESULT
for r in result:
    print(r.email, "|", r.name, "|", r.contact, "|", r.location)

Producción: