SQLAlchemy - Guía rápida

SQLAlchemy es un popular kit de herramientas SQL y Object Relational Mapper. Esta escrito enPythony brinda plena potencia y flexibilidad de SQL a un desarrollador de aplicaciones. Es unopen source y cross-platform software lanzado bajo licencia del MIT.

SQLAlchemy es famoso por su mapeador relacional de objetos (ORM), mediante el cual, las clases se pueden mapear a la base de datos, lo que permite que el modelo de objeto y el esquema de la base de datos se desarrollen de una manera limpiamente desacoplada desde el principio.

A medida que el tamaño y el rendimiento de las bases de datos SQL comienzan a importar, se comportan menos como colecciones de objetos. Por otro lado, a medida que la abstracción en las colecciones de objetos comienza a importar, se comportan menos como tablas y filas. SQLAlchemy tiene como objetivo adaptarse a ambos principios.

Por este motivo, ha adoptado la data mapper pattern (like Hibernate) rather than the active record pattern used by a number of other ORMs. Las bases de datos y SQL se verán en una perspectiva diferente usando SQLAlchemy.

Michael Bayer es el autor original de SQLAlchemy. Su versión inicial fue lanzada en febrero de 2006. La última versión está numerada como 1.2.7, lanzada tan recientemente como en abril de 2018.

¿Qué es ORM?

ORM (Object Relational Mapping) es una técnica de programación para convertir datos entre sistemas de tipos incompatibles en lenguajes de programación orientados a objetos. Por lo general, el sistema de tipos utilizado en un lenguaje orientado a objetos (OO) como Python contiene tipos no escalares. Estos no se pueden expresar como tipos primitivos como enteros y cadenas. Por lo tanto, el programador de OO tiene que convertir objetos en datos escalares para interactuar con la base de datos backend. Sin embargo, los tipos de datos en la mayoría de los productos de bases de datos, como Oracle, MySQL, etc., son primarios.

En un sistema ORM, cada clase se asigna a una tabla en la base de datos subyacente. En lugar de escribir usted mismo el tedioso código de interfaz de la base de datos, un ORM se encarga de estos problemas por usted mientras usted puede concentrarse en programar las lógicas del sistema.

SQLAlchemy - Configuración del entorno

Analicemos la configuración ambiental necesaria para utilizar SQLAlchemy.

Se necesita cualquier versión de Python superior a 2.7 para instalar SQLAlchemy. La forma más sencilla de instalar es utilizando Python Package Manager,pip. Esta utilidad se incluye con la distribución estándar de Python.

pip install sqlalchemy

Usando el comando anterior, podemos descargar el latest released versionde SQLAlchemy de python.org e instálelo en su sistema.

En caso de distribución anaconda de Python, SQLAlchemy se puede instalar desde conda terminal usando el siguiente comando -

conda install -c anaconda sqlalchemy

También es posible instalar SQLAlchemy desde el siguiente código fuente:

python setup.py install

SQLAlchemy está diseñado para operar con una implementación DBAPI construida para una base de datos en particular. Utiliza un sistema de dialecto para comunicarse con varios tipos de implementaciones y bases de datos DBAPI. Todos los dialectos requieren que esté instalado un controlador DBAPI apropiado.

Los siguientes son los dialectos incluidos:

• Firebird
• Microsoft SQL Server
• MySQL
• Oracle
• PostgreSQL
• SQLite
• Sybase

Para verificar si SQLAlchemy está instalado correctamente y conocer su versión, ingrese el siguiente comando en el indicador de Python:

>>> import sqlalchemy
>>>sqlalchemy.__version__
'1.2.7'

El núcleo de SQLAlchemy incluye SQL rendering engine, DBAPI integration, transaction integrationy schema description services. El núcleo de SQLAlchemy utiliza el lenguaje de expresión SQL que proporciona unaschema-centric usage paradigma mientras que SQLAlchemy ORM es un domain-centric mode of usage.

El lenguaje de expresión SQL presenta un sistema de representación de estructuras y expresiones de bases de datos relacionales utilizando construcciones de Python. Presenta un sistema de representación de las construcciones primitivas de la base de datos relacional directamente sin opinión, lo que contrasta con el ORM que presenta un patrón de uso abstracto y de alto nivel, que en sí mismo es un ejemplo de uso aplicado del lenguaje de expresión.

El lenguaje de expresión es uno de los componentes centrales de SQLAlchemy. Permite al programador especificar declaraciones SQL en código Python y usarlas directamente en consultas más complejas. El lenguaje de expresión es independiente del backend y cubre de manera integral todos los aspectos de SQL sin procesar. Está más cerca de SQL sin formato que cualquier otro componente de SQLAlchemy.

Expression Language representa directamente las construcciones primitivas de la base de datos relacional. Debido a que el ORM se basa en la parte superior del lenguaje de expresión, una aplicación de base de datos típica de Python puede tener un uso superpuesto de ambos. La aplicación puede utilizar únicamente el lenguaje de expresión, aunque tiene que definir su propio sistema para traducir los conceptos de la aplicación en consultas de bases de datos individuales.

El lenguaje de declaraciones de expresión se traducirá en las consultas SQL sin procesar correspondientes mediante el motor SQLAlchemy. Ahora aprenderemos cómo crear el motor y ejecutar varias consultas SQL con su ayuda.

En el capítulo anterior, discutimos sobre el lenguaje de expresión en SQLAlchemy. Ahora procedamos con los pasos necesarios para conectarse a una base de datos.

La clase de motor conecta un Pool and Dialect together para proporcionar una fuente de base de datos connectivity and behavior. Se crea una instancia de un objeto de la clase Engine utilizando elcreate_engine() función.

La función create_engine () toma la base de datos como un argumento. No es necesario definir la base de datos en ninguna parte. El formulario de llamada estándar debe enviar la URL como primer argumento posicional, generalmente una cadena que indica el dialecto de la base de datos y los argumentos de conexión. Usando el código que se proporciona a continuación, podemos crear una base de datos.

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///college.db', echo = True)

Para MySQL database, use el siguiente comando:

engine = create_engine("mysql://user:pwd@localhost/college",echo = True)

Para mencionar específicamente DB-API para ser utilizado para la conexión, el URL string toma la forma siguiente:

dialect[+driver]://user:password@host/dbname

Por ejemplo, si está utilizando PyMySQL driver with MySQL, use el siguiente comando -

mysql+pymysql://<username>:<password>@<host>/<dbname>

los echo flages un atajo para configurar el registro de SQLAlchemy, que se logra a través del módulo de registro estándar de Python. En los capítulos siguientes, aprenderemos todos los SQL generados. Para ocultar la salida detallada, establezca el atributo de eco enNone. Otros argumentos para la función create_engine () pueden ser específicos del dialecto.

La función create_engine () devuelve un Engine object. Algunos métodos importantes de la clase de motor son:

No Señor.	Método y descripción
1	connect() Devuelve el objeto de conexión
2	execute() Ejecuta una construcción de declaración SQL
3	begin() Devuelve un administrador de contexto que entrega una conexión con una transacción establecida. Tras una operación exitosa, la transacción se confirma, de lo contrario, se revierte
4	dispose() Elimina el grupo de conexiones utilizado por el motor
5	driver() Nombre del conductor del dialecto en uso por el motor
6	table_names() Devuelve una lista de todos los nombres de tablas disponibles en la base de datos.
7	transaction() Ejecuta la función dada dentro de un límite de transacción.

Analicemos ahora cómo utilizar la función de creación de tabla.

El lenguaje de expresión SQL construye sus expresiones contra las columnas de la tabla. El objeto Columna SQLAlchemy representa uncolumn en una tabla de base de datos que a su vez está representada por un Tableobject. Los metadatos contienen definiciones de tablas y objetos asociados como índice, vista, disparadores, etc.

Por lo tanto, un objeto de la clase MetaData de SQLAlchemy Metadata es una colección de objetos Table y sus construcciones de esquema asociadas. Contiene una colección de objetos de tabla, así como un enlace opcional a un motor o conexión.

from sqlalchemy import MetaData
meta = MetaData()

El constructor de la clase MetaData puede tener parámetros de enlace y esquema que son por defecto None.

A continuación, definimos nuestras tablas dentro del catálogo de metadatos anterior, usando the Table construct, que se asemeja a la sentencia CREATE TABLE de SQL normal.

Un objeto de la clase Table representa la tabla correspondiente en una base de datos. El constructor toma los siguientes parámetros:

Nombre	Nombre de la mesa
Metadatos	Objeto de metadatos que contendrá esta tabla
Columna (s)	Uno o más objetos de la clase de columna

El objeto de columna representa un column en un database table. El constructor toma el nombre, el tipo y otros parámetros como primary_key, autoincrement y otras restricciones.

SQLAlchemy compara los datos de Python con los mejores tipos de datos de columna genéricos definidos en él. Algunos de los tipos de datos genéricos son:

• BigInteger
• Boolean
• Date
• DateTime
• Float
• Integer
• Numeric
• SmallInteger
• String
• Text
• Time

Para crear un students table en la base de datos de la universidad, use el siguiente fragmento:

from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

La función create_all () utiliza el objeto del motor para crear todos los objetos de tabla definidos y almacena la información en metadatos.

meta.create_all(engine)

A continuación se proporciona el código completo que creará una base de datos SQLite college.db con una tabla de estudiantes en ella.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String),
)
meta.create_all(engine)

Debido a que el atributo de eco de la función create_engine () está establecido en True, la consola mostrará la consulta SQL real para la creación de la tabla de la siguiente manera:

CREATE TABLE students (
   id INTEGER NOT NULL,
   name VARCHAR,
   lastname VARCHAR,
   PRIMARY KEY (id)
)

El college.db se creará en el directorio de trabajo actual. Para verificar si se creó la tabla de estudiantes, puede abrir la base de datos utilizando cualquier herramienta de GUI de SQLite, comoSQLiteStudio.

La siguiente imagen muestra la tabla de estudiantes que se crea en la base de datos:

En este capítulo, nos centraremos brevemente en las expresiones SQL y sus funciones.

Las expresiones SQL se construyen utilizando los métodos correspondientes en relación con el objeto de la tabla de destino. Por ejemplo, la instrucción INSERT se crea ejecutando el método insert () de la siguiente manera:

ins = students.insert()

El resultado del método anterior es un objeto de inserción que se puede verificar usando str()función. El siguiente código inserta detalles como identificación del estudiante, nombre, apellido.

'INSERT INTO students (id, name, lastname) VALUES (:id, :name, :lastname)'

Es posible insertar un valor en un campo específico mediante values()método para insertar objeto. El código para el mismo se da a continuación:

>>> ins = users.insert().values(name = 'Karan')
>>> str(ins)
'INSERT INTO users (name) VALUES (:name)'

El SQL repetido en la consola de Python no muestra el valor real ('Karan' en este caso). En cambio, SQLALchemy genera un parámetro de vinculación que es visible en forma compilada de la declaración.

ins.compile().params
{'name': 'Karan'}

Del mismo modo, métodos como update(), delete() y select()crear expresiones UPDATE, DELETE y SELECT respectivamente. Aprenderemos sobre ellos en capítulos posteriores.

En el capítulo anterior, hemos aprendido Expresiones SQL. En este capítulo, veremos la ejecución de estas expresiones.

Para ejecutar las expresiones SQL resultantes, tenemos que obtain a connection object representing an actively checked out DBAPI connection resource y entonces feed the expression object como se muestra en el código siguiente.

conn = engine.connect()

El siguiente objeto insert () se puede utilizar para el método execute ():

ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
result = conn.execute(ins)

La consola muestra el resultado de la ejecución de la expresión SQL como se muestra a continuación:

INSERT INTO students (name, lastname) VALUES (?, ?)
('Ravi', 'Kapoor')
COMMIT

A continuación se muestra el fragmento completo que muestra la ejecución de la consulta INSERT utilizando la técnica principal de SQLAlchemy:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

ins = students.insert()
ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
conn = engine.connect()
result = conn.execute(ins)

El resultado se puede verificar abriendo la base de datos usando SQLite Studio como se muestra en la siguiente captura de pantalla:

La variable de resultado se conoce como ResultProxy object. Es análogo al objeto de cursor DBAPI. Podemos adquirir información sobre los valores de clave principal que se generaron a partir de nuestra declaración utilizandoResultProxy.inserted_primary_key como se muestra a continuación -

result.inserted_primary_key
[1]

Para emitir muchas inserciones utilizando el método execute many () de DBAPI, podemos enviar una lista de diccionarios, cada uno de los cuales contiene un conjunto distinto de parámetros para insertar.

conn.execute(students.insert(), [
   {'name':'Rajiv', 'lastname' : 'Khanna'},
   {'name':'Komal','lastname' : 'Bhandari'},
   {'name':'Abdul','lastname' : 'Sattar'},
   {'name':'Priya','lastname' : 'Rajhans'},
])

Esto se refleja en la vista de datos de la tabla como se muestra en la siguiente figura:

En este capítulo, discutiremos sobre el concepto de seleccionar filas en el objeto de tabla.

El método select () del objeto de tabla nos permite construct SELECT expression.

s = students.select()

El objeto seleccionado se traduce en SELECT query by str(s) function como se muestra a continuación -

'SELECT students.id, students.name, students.lastname FROM students'

Podemos usar este objeto de selección como parámetro para ejecutar el método () del objeto de conexión como se muestra en el código siguiente:

result = conn.execute(s)

Cuando se ejecuta la declaración anterior, el shell de Python se hace eco de la siguiente expresión SQL equivalente:

SELECT students.id, students.name, students.lastname
FROM students

La variable resultante es equivalente al cursor en DBAPI. Ahora podemos buscar registros usandofetchone() method.

row = result.fetchone()

Todas las filas seleccionadas en la tabla se pueden imprimir mediante un for loop como se indica a continuación -

for row in result:
   print (row)

El código completo para imprimir todas las filas de la tabla de estudiantes se muestra a continuación:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

s = students.select()
conn = engine.connect()
result = conn.execute(s)

for row in result:
   print (row)

La salida que se muestra en el shell de Python es la siguiente:

(1, 'Ravi', 'Kapoor')
(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')

La cláusula WHERE de la consulta SELECT se puede aplicar usando Select.where(). Por ejemplo, si queremos mostrar filas con id> 2

s = students.select().where(students.c.id>2)
result = conn.execute(s)

for row in result:
   print (row)

aquí c attribute is an alias for column. La siguiente salida se mostrará en el shell:

(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')

Aquí, tenemos que tener en cuenta que el objeto de selección también se puede obtener mediante la función select () en el módulo sqlalchemy.sql. La función select () requiere el objeto de tabla como argumento.

from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)

SQLAlchemy le permite usar cadenas, para aquellos casos en los que el SQL ya se conoce y no hay una gran necesidad de que la declaración admita características dinámicas. La construcción text () se usa para componer una declaración textual que se pasa a la base de datos casi sin cambios.

Construye un nuevo TextClause, que representa una cadena SQL textual directamente como se muestra en el siguiente código:

from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)

Las ventajas text() proporciona sobre una cadena simple son:

• soporte neutral de backend para parámetros de enlace
• opciones de ejecución por sentencia
• comportamiento de escritura de la columna de resultado

La función text () requiere parámetros enlazados en el formato de dos puntos con nombre. Son consistentes independientemente del backend de la base de datos. Para enviar valores para los parámetros, los pasamos al método execute () como argumentos adicionales.

El siguiente ejemplo usa parámetros enlazados en SQL textual:

from sqlalchemy.sql import text
s = text("select students.name, students.lastname from students where students.name between :x and :y")
conn.execute(s, x = 'A', y = 'L').fetchall()

La función text () construye una expresión SQL de la siguiente manera:

select students.name, students.lastname from students where students.name between ? and ?

Los valores de x = 'A' e y = 'L' se pasan como parámetros. El resultado es una lista de filas con nombres entre 'A' y 'L' -

[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]

La construcción text () admite valores enlazados preestablecidos mediante el método TextClause.bindparams (). Los parámetros también se pueden escribir explícitamente de la siguiente manera:

stmt = text("SELECT * FROM students WHERE students.name BETWEEN :x AND :y")

stmt = stmt.bindparams(
   bindparam("x", type_= String), 
   bindparam("y", type_= String)
)

result = conn.execute(stmt, {"x": "A", "y": "L"})

The text() function also be produces fragments of SQL within a select() object that 
accepts text() objects as an arguments. The “geometry” of the statement is provided by 
select() construct , and the textual content by text() construct. We can build a statement 
without the need to refer to any pre-established Table metadata. 

from sqlalchemy.sql import select
s = select([text("students.name, students.lastname from students")]).where(text("students.name between :x and :y"))
conn.execute(s, x = 'A', y = 'L').fetchall()

También puedes usar and_() función para combinar múltiples condiciones en la cláusula WHERE creada con la ayuda de la función text ().

from sqlalchemy import and_
from sqlalchemy.sql import select
s = select([text("* from students")]) \
.where(
   and_(
      text("students.name between :x and :y"),
      text("students.id>2")
   )
)
conn.execute(s, x = 'A', y = 'L').fetchall()

El código anterior obtiene filas con nombres entre "A" y "L" con id mayor que 2. La salida del código se da a continuación:

[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar')]

El alias en SQL corresponde a una versión "renombrada" de una tabla o instrucción SELECT, que ocurre cada vez que dice "SELECT * FROM table1 AS a". El AS crea un nuevo nombre para la tabla. Los alias permiten que cualquier tabla o subconsulta sea referenciada por un nombre único.

En el caso de una tabla, esto permite nombrar la misma tabla en la cláusula FROM varias veces. Proporciona un nombre principal para las columnas representadas por la declaración, lo que permite hacer referencia a ellas en relación con este nombre.

En SQLAlchemy, cualquier construcción Table, select () u otro objeto seleccionable se puede convertir en un alias usando el From Clause.alias()método, que produce una construcción Alias. La función alias () en el módulo sqlalchemy.sql representa un alias, como se aplica normalmente a cualquier tabla o sub-selección dentro de una declaración SQL utilizando la palabra clave AS.

from sqlalchemy.sql import alias
st = students.alias("a")

Este alias ahora se puede usar en la construcción select () para referirse a la tabla de estudiantes -

s = select([st]).where(st.c.id>2)

Esto se traduce en una expresión SQL de la siguiente manera:

SELECT a.id, a.name, a.lastname FROM students AS a WHERE a.id > 2

Ahora podemos ejecutar esta consulta SQL con el método execute () del objeto de conexión. El código completo es el siguiente:

from sqlalchemy.sql import alias, select
st = students.alias("a")
s = select([st]).where(st.c.id > 2)
conn.execute(s).fetchall()

Cuando se ejecuta la línea de código anterior, genera la siguiente salida:

[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar'), (5, 'Priya', 'Rajhans')]

los update() El método en el objeto de la tabla de destino construye una expresión SQL UPDATE equivalente.

table.update().where(conditions).values(SET expressions)

los values()El método en el objeto de actualización resultante se utiliza para especificar las condiciones SET de UPDATE. Si se deja como Ninguno, las condiciones SET se determinan a partir de los parámetros pasados ​​a la declaración durante la ejecución y / o compilación de la declaración.

La cláusula where es una expresión opcional que describe la condición WHERE de la instrucción UPDATE.

El siguiente fragmento de código cambia el valor de la columna 'apellido' de 'Khanna' a 'Kapoor' en la tabla de estudiantes:

stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')

El objeto stmt es un objeto de actualización que se traduce en:

'UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1'

El parámetro vinculado lastname_1 será sustituido cuando execute()se invoca el método. El código de actualización completo se proporciona a continuación:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', 
   meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

conn = engine.connect()
stmt=students.update().where(students.c.lastname=='Khanna').values(lastname='Kapoor')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

El código anterior muestra el siguiente resultado con la segunda fila que muestra el efecto de la operación de actualización como en la captura de pantalla dada:

[
   (1, 'Ravi', 'Kapoor'),
   (2, 'Rajiv', 'Kapoor'),
   (3, 'Komal', 'Bhandari'),
   (4, 'Abdul', 'Sattar'),
   (5, 'Priya', 'Rajhans')
]

Tenga en cuenta que también se puede lograr una funcionalidad similar utilizando update() función en el módulo sqlalchemy.sql.expression como se muestra a continuación -

from sqlalchemy.sql.expression import update
stmt = update(students).where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')

En el capítulo anterior, hemos entendido qué Updatela expresión lo hace. La siguiente expresión que vamos a aprender esDelete.

La operación de eliminación se puede lograr ejecutando el método delete () en el objeto de la tabla de destino como se indica en la siguiente declaración:

stmt = students.delete()

En el caso de la tabla de estudiantes, la línea de código anterior construye una expresión SQL de la siguiente manera:

'DELETE FROM students'

Sin embargo, esto eliminará todas las filas de la tabla de estudiantes. Por lo general, la consulta DELETE está asociada con una expresión lógica especificada por la cláusula WHERE. La siguiente declaración muestra dónde parámetro -

stmt = students.delete().where(students.c.id > 2)

La expresión SQL resultante tendrá un parámetro vinculado que se sustituirá en tiempo de ejecución cuando se ejecute la instrucción.

'DELETE FROM students WHERE students.id > :id_1'

El siguiente ejemplo de código eliminará esas filas de la tabla de estudiantes que tienen el apellido como 'Khanna' -

from sqlalchemy.sql.expression import update
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

conn = engine.connect()
stmt = students.delete().where(students.c.lastname == 'Khanna')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

Para verificar el resultado, actualice la vista de datos de la tabla de estudiantes en SQLiteStudio.

Una de las características importantes de RDBMS es establecer una relación entre tablas. Las operaciones SQL como SELECT, UPDATE y DELETE se pueden realizar en tablas relacionadas. Esta sección describe estas operaciones usando SQLAlchemy.

Para ello, se crean dos tablas en nuestra base de datos SQLite (college.db). La tabla de estudiantes tiene la misma estructura que la dada en la sección anterior; mientras que la tabla de direcciones tienest_id columna que se asigna a id column in students table usando restricción de clave externa.

El siguiente código creará dos tablas en college.db:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo=True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

addresses = Table(
   'addresses', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer, ForeignKey('students.id')), 
   Column('postal_add', String), 
   Column('email_add', String))

meta.create_all(engine)

El código anterior se traducirá en consultas CREATE TABLE para estudiantes y en la tabla de direcciones como se muestra a continuación:

CREATE TABLE students (
   id INTEGER NOT NULL,
   name VARCHAR,
   lastname VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE addresses (
   id INTEGER NOT NULL,
   st_id INTEGER,
   postal_add VARCHAR,
   email_add VARCHAR,
   PRIMARY KEY (id),
   FOREIGN KEY(st_id) REFERENCES students (id)
)

Las siguientes capturas de pantalla presentan el código anterior de manera muy clara:

Estas tablas se completan con datos ejecutando insert() methodde objetos de mesa. Para insertar 5 filas en la tabla de estudiantes, puede usar el código que se proporciona a continuación:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

conn = engine.connect()
students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

conn.execute(students.insert(), [
   {'name':'Ravi', 'lastname':'Kapoor'},
   {'name':'Rajiv', 'lastname' : 'Khanna'},
   {'name':'Komal','lastname' : 'Bhandari'},
   {'name':'Abdul','lastname' : 'Sattar'},
   {'name':'Priya','lastname' : 'Rajhans'},
])

Rows se agregan en la tabla de direcciones con la ayuda del siguiente código:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

addresses = Table(
   'addresses', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer), 
   Column('postal_add', String), 
   Column('email_add', String)
)

conn.execute(addresses.insert(), [
   {'st_id':1, 'postal_add':'Shivajinagar Pune', 'email_add':'[email protected]'},
   {'st_id':1, 'postal_add':'ChurchGate Mumbai', 'email_add':'[email protected]'},
   {'st_id':3, 'postal_add':'Jubilee Hills Hyderabad', 'email_add':'[email protected]'},
   {'st_id':5, 'postal_add':'MG Road Bangaluru', 'email_add':'[email protected]'},
   {'st_id':2, 'postal_add':'Cannought Place new Delhi', 'email_add':'[email protected]'},
])

Tenga en cuenta que la columna st_id en la tabla de direcciones se refiere a la columna de identificación en la tabla de estudiantes. Ahora podemos usar esta relación para obtener datos de ambas tablas. Queremos ir a buscarname y lastname de la tabla de estudiantes correspondiente a st_id en la tabla de direcciones.

from sqlalchemy.sql import select
s = select([students, addresses]).where(students.c.id == addresses.c.st_id)
result = conn.execute(s)

for row in result:
   print (row)

Los objetos seleccionados se traducirán efectivamente en la siguiente expresión SQL que une dos tablas en relación común:

SELECT students.id, 
   students.name, 
   students.lastname, 
   addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM students, addresses
WHERE students.id = addresses.st_id

Esto producirá una salida extrayendo los datos correspondientes de ambas tablas de la siguiente manera:

(1, 'Ravi', 'Kapoor', 1, 1, 'Shivajinagar Pune', '[email protected]')
(1, 'Ravi', 'Kapoor', 2, 1, 'ChurchGate Mumbai', '[email protected]')
(3, 'Komal', 'Bhandari', 3, 3, 'Jubilee Hills Hyderabad', '[email protected]')
(5, 'Priya', 'Rajhans', 4, 5, 'MG Road Bangaluru', '[email protected]')
(2, 'Rajiv', 'Khanna', 5, 2, 'Cannought Place new Delhi', '[email protected]')

En el capítulo anterior, analizamos cómo usar varias tablas. Así que avanzamos un paso más y aprendemosmultiple table updates en este capítulo.

Usando el objeto de tabla de SQLAlchemy, se puede especificar más de una tabla en la cláusula WHERE del método update (). PostgreSQL y Microsoft SQL Server admiten declaraciones UPDATE que hacen referencia a varias tablas. Esto implementa“UPDATE FROM”sintaxis, que actualiza una tabla a la vez. Sin embargo, se puede hacer referencia a tablas adicionales en una cláusula adicional "FROM" en la cláusula WHERE directamente. Las siguientes líneas de códigos explican el concepto demultiple table updates claramente.

stmt = students.update().\
values({
   students.c.name:'xyz',
   addresses.c.email_add:'[email protected]'
}).\
where(students.c.id == addresses.c.id)

El objeto de actualización es equivalente a la siguiente consulta UPDATE:

UPDATE students 
SET email_add = :addresses_email_add, name = :name 
FROM addresses 
WHERE students.id = addresses.id

En lo que respecta al dialecto de MySQL, se pueden incrustar varias tablas en una sola declaración UPDATE separadas por una coma como se indica a continuación:

stmt = students.update().\
   values(name = 'xyz').\
   where(students.c.id == addresses.c.id)

El siguiente código muestra la consulta UPDATE resultante:

'UPDATE students SET name = :name 
FROM addresses 
WHERE students.id = addresses.id'

Sin embargo, el dialecto SQLite no admite criterios de tablas múltiples dentro de ACTUALIZAR y muestra el siguiente error:

NotImplementedError: This backend does not support multiple-table criteria within UPDATE

La consulta UPDATE de SQL sin formato tiene una cláusula SET. Se representa mediante la construcción update () utilizando el orden de columna dado en el objeto Table de origen. Por lo tanto, una instrucción UPDATE particular con columnas particulares se representará de la misma manera cada vez. Dado que los parámetros en sí se pasan al método Update.values ​​() como claves de diccionario de Python, no hay ningún otro orden fijo disponible.

En algunos casos, el orden de los parámetros representados en la cláusula SET es significativo. En MySQL, proporcionar actualizaciones a los valores de columna se basa en el de otros valores de columna.

Resultado de la siguiente declaración:

UPDATE table1 SET x = y + 10, y = 20

tendrá un resultado diferente que -

UPDATE table1 SET y = 20, x = y + 10

La cláusula SET en MySQL se evalúa por valor y no por fila. Para ello, elpreserve_parameter_orderse utiliza. La lista de Python de 2 tuplas se proporciona como argumento a laUpdate.values() método -

stmt = table1.update(preserve_parameter_order = True).\
   values([(table1.c.y, 20), (table1.c.x, table1.c.y + 10)])

El objeto List es similar al diccionario, excepto que está ordenado. Esto asegura que la cláusula SET de la columna "y" se renderice primero, luego la cláusula SET de la columna "x".

En este capítulo, veremos la expresión Eliminación de varias tablas, que es similar al uso de la función Actualizaciones de varias tablas.

Se puede hacer referencia a más de una tabla en la cláusula WHERE de la instrucción DELETE en muchos dialectos DBMS. Para PG y MySQL, se usa la sintaxis “DELETE USING”; y para SQL Server, el uso de la expresión "DELETE FROM" se refiere a más de una tabla. La alquimia SQLAdelete() construct admite ambos modos implícitamente, al especificar varias tablas en la cláusula WHERE de la siguiente manera:

stmt = users.delete().\
   where(users.c.id == addresses.c.id).\
   where(addresses.c.email_address.startswith('xyz%'))
conn.execute(stmt)

En un backend de PostgreSQL, el SQL resultante de la declaración anterior se representaría como:

DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s || '%%')

Si este método se usa con una base de datos que no admite este comportamiento, el compilador generará NotImplementedError.

En este capítulo, aprenderemos cómo usar Joins en SQLAlchemy.

El efecto de unión se logra simplemente colocando dos tablas en el columns clause o la where clausede la construcción select (). Ahora usamos los métodos join () y outsidejoin ().

El método join () devuelve un objeto de unión de un objeto de tabla a otro.

join(right, onclause = None, isouter = False, full = False)

Las funciones de los parámetros mencionados en el código anterior son las siguientes:

• right- el lado derecho de la unión; este es cualquier objeto Table

• onclause- una expresión SQL que representa la cláusula ON de la combinación. Si se deja en Ninguno, intenta unir las dos tablas basándose en una relación de clave externa

• isouter - si es Verdadero, muestra una LEFT OUTER JOIN, en lugar de JOIN

• full - si es Verdadero, genera un COMPLETO EXTERIOR JOIN, en lugar de LEFT OUTER JOIN

Por ejemplo, el siguiente uso del método join () dará como resultado automáticamente una combinación basada en la clave externa.

>>> print(students.join(addresses))

Esto es equivalente a la siguiente expresión SQL:

students JOIN addresses ON students.id = addresses.st_id

Puede mencionar explícitamente los criterios para unirse de la siguiente manera:

j = students.join(addresses, students.c.id == addresses.c.st_id)

Si ahora construimos la construcción de selección a continuación usando esta combinación como:

stmt = select([students]).select_from(j)

Esto dará como resultado la siguiente expresión SQL:

SELECT students.id, students.name, students.lastname
FROM students JOIN addresses ON students.id = addresses.st_id

Si esta declaración se ejecuta utilizando la conexión que representa el motor, se mostrarán los datos que pertenecen a las columnas seleccionadas. El código completo es el siguiente:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()
conn = engine.connect()
students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

addresses = Table(
   'addresses', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer,ForeignKey('students.id')), 
   Column('postal_add', String), 
   Column('email_add', String)
)

from sqlalchemy import join
from sqlalchemy.sql import select
j = students.join(addresses, students.c.id == addresses.c.st_id)
stmt = select([students]).select_from(j)
result = conn.execute(stmt)
result.fetchall()

La siguiente es la salida del código anterior:

[
   (1, 'Ravi', 'Kapoor'),
   (1, 'Ravi', 'Kapoor'),
   (3, 'Komal', 'Bhandari'),
   (5, 'Priya', 'Rajhans'),
   (2, 'Rajiv', 'Khanna')
]

Las conjunciones son funciones en el módulo SQLAlchemy que implementan operadores relacionales usados ​​en la cláusula WHERE de las expresiones SQL. Los operadores Y, O, NO, etc., se utilizan para formar una expresión compuesta que combina dos expresiones lógicas individuales. Un ejemplo simple del uso de AND en la instrucción SELECT es el siguiente:

SELECT * from EMPLOYEE WHERE salary>10000 AND age>30

Las funciones de SQLAlchemy y_ (), or_ () y not_ () implementan operadores AND, OR y NOT respectivamente.

función and_ ()

Produce una conjunción de expresiones unidas por AND. A continuación se ofrece un ejemplo para una mejor comprensión:

from sqlalchemy import and_

print(
   and_(
      students.c.name == 'Ravi',
      students.c.id <3
   )
)

Esto se traduce en:

students.name = :name_1 AND students.id < :id_1

Para usar and_ () en una construcción select () en una tabla de estudiantes, use la siguiente línea de código:

stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))

Se construirá la declaración SELECT de la siguiente naturaleza:

SELECT students.id, 
   students.name, 
   students.lastname
FROM students
WHERE students.name = :name_1 AND students.id < :id_1

El código completo que muestra el resultado de la consulta SELECT anterior es el siguiente:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

from sqlalchemy import and_, or_
stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))
result = conn.execute(stmt)
print (result.fetchall())

La siguiente fila se seleccionará asumiendo que la tabla de estudiantes se completa con los datos utilizados en el ejemplo anterior:

[(1, 'Ravi', 'Kapoor')]

función or_ ()

Produce conjunción de expresiones unidas por OR. Reemplazaremos el objeto stmt en el ejemplo anterior con el siguiente usando or_ ()

stmt = select([students]).where(or_(students.c.name == 'Ravi', students.c.id <3))

Lo que será efectivamente equivalente a seguir la consulta SELECT -

SELECT students.id, 
   students.name, 
   students.lastname
FROM students
WHERE students.name = :name_1 
OR students.id < :id_1

Una vez que realice la sustitución y ejecute el código anterior, el resultado serán dos filas que caen en la condición OR:

[(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Khanna')]

función asc ()

Produce una cláusula ORDER BY ascendente. La función toma la columna para aplicar la función como parámetro.

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))

La declaración implementa la siguiente expresión SQL:

SELECT students.id, 
   students.name, 
   students.lastname
FROM students 
ORDER BY students.name ASC

El siguiente código enumera todos los registros en la tabla de estudiantes en orden ascendente de columna de nombre:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))
result = conn.execute(stmt)

for row in result:
   print (row)

El código anterior produce la siguiente salida:

(4, 'Abdul', 'Sattar')
(3, 'Komal', 'Bhandari')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')

función desc ()

De manera similar, la función desc () produce una cláusula ORDER BY descendente de la siguiente manera:

from sqlalchemy import desc
stmt = select([students]).order_by(desc(students.c.lastname))

La expresión SQL equivalente es -

SELECT students.id, 
   students.name, 
   students.lastname
FROM students 
ORDER BY students.lastname DESC

Y el resultado de las líneas de código anteriores es:

(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')
(3, 'Komal', 'Bhandari')

entre () función

Produce una cláusula de predicado BETWEEN. Esto se usa generalmente para validar si el valor de una determinada columna se encuentra entre un rango. Por ejemplo, el siguiente código selecciona filas para las que la columna de identificación está entre 2 y 4:

from sqlalchemy import between
stmt = select([students]).where(between(students.c.id,2,4))
print (stmt)

La expresión SQL resultante se parece a:

SELECT students.id, 
   students.name, 
   students.lastname
FROM students
WHERE students.id 
BETWEEN :id_1 AND :id_2

y el resultado es el siguiente:

(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')

Algunas de las funciones importantes utilizadas en SQLAlchemy se analizan en este capítulo.

SQL estándar ha recomendado muchas funciones que se implementan en la mayoría de los dialectos. Devuelven un valor único en función de los argumentos que se le pasan. Algunas funciones SQL toman columnas como argumentos, mientras que otras son genéricas.Thefunc keyword in SQLAlchemy API is used to generate these functions.

En SQL, now () es una función genérica. Las siguientes declaraciones representan la función now () usando func -

from sqlalchemy.sql import func
result = conn.execute(select([func.now()]))
print (result.fetchone())

El resultado de muestra del código anterior puede ser el que se muestra a continuación:

(datetime.datetime(2018, 6, 16, 6, 4, 40),)

Por otro lado, la función count () que devuelve el número de filas seleccionadas de una tabla, se representa siguiendo el uso de func:

from sqlalchemy.sql import func
result = conn.execute(select([func.count(students.c.id)]))
print (result.fetchone())

A partir del código anterior, se obtendrá el recuento del número de filas en la tabla de estudiantes.

Algunas funciones de SQL incorporadas se demuestran usando la tabla de empleados con los siguientes datos:

CARNÉ DE IDENTIDAD	Nombre	Marcas
1	Kamal	56
2	Fernandez	85
3	Sunil	62
4	Bhaskar	76

La función max () se implementa siguiendo el uso de func de SQLAlchemy que dará como resultado 85, las calificaciones máximas totales obtenidas:

from sqlalchemy.sql import func
result = conn.execute(select([func.max(employee.c.marks)]))
print (result.fetchone())

De manera similar, la función min () que devolverá 56, marcas mínimas, se representará con el siguiente código:

from sqlalchemy.sql import func
result = conn.execute(select([func.min(employee.c.marks)]))
print (result.fetchone())

Por lo tanto, la función AVG () también se puede implementar utilizando el siguiente código:

from sqlalchemy.sql import func
result = conn.execute(select([func.avg(employee.c.marks)]))
print (result.fetchone())

Functions are normally used in the columns clause of a select statement. 
They can also be given label as well as a type. A label to function allows the result 
to be targeted in a result row based on a string name, and a type is required when 
you need result-set processing to occur.from sqlalchemy.sql import func

result = conn.execute(select([func.max(students.c.lastname).label('Name')]))

print (result.fetchone())

En el último capítulo, hemos aprendido acerca de varias funciones como max (), min (), count (), etc., aquí aprenderemos sobre las operaciones de conjuntos y sus usos.

Las operaciones de conjuntos como UNION e INTERSECT son compatibles con SQL estándar y la mayor parte de su dialecto. SQLAlchemy los implementa con la ayuda de las siguientes funciones:

Unión()

Al combinar los resultados de dos o más sentencias SELECT, UNION elimina los duplicados del conjunto de resultados. El número de columnas y el tipo de datos deben ser iguales en ambas tablas.

La función union () devuelve un objeto CompoundSelect de varias tablas. El siguiente ejemplo demuestra su uso:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()
conn = engine.connect()
addresses = Table(
   'addresses', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer), 
   Column('postal_add', String), 
   Column('email_add', String)
)

u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))))

result = conn.execute(u)
result.fetchall()

La construcción de unión se traduce en la siguiente expresión SQL:

SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

De nuestra tabla de direcciones, las siguientes filas representan la operación de unión:

[
   (1, 1, 'Shivajinagar Pune', '[email protected]'),
   (2, 1, 'ChurchGate Mumbai', '[email protected]'),
   (3, 3, 'Jubilee Hills Hyderabad', '[email protected]'),
   (4, 5, 'MG Road Bangaluru', '[email protected]')
]

union_all ()

La operación UNION ALL no puede eliminar los duplicados y no puede ordenar los datos en el conjunto de resultados. Por ejemplo, en la consulta anterior, UNION se reemplaza por UNION ALL para ver el efecto.

u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))

La expresión SQL correspondiente es la siguiente:

SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION ALL SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

excepto_()

El SQL EXCEPTLa cláusula / operador se utiliza para combinar dos sentencias SELECT y devolver filas de la primera sentencia SELECT que no son devueltas por la segunda sentencia SELECT. La función except_ () genera una expresión SELECT con cláusula EXCEPT.

En el siguiente ejemplo, la función except_ () devuelve solo aquellos registros de la tabla de direcciones que tienen "gmail.com" en el campo email_add, pero excluye aquellos que tienen "Pune" como parte del campo postal_add.

u = except_(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

El resultado del código anterior es la siguiente expresión SQL:

SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? EXCEPT SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

Suponiendo que la tabla de direcciones contiene datos utilizados en ejemplos anteriores, mostrará la siguiente salida:

[(2, 1, 'ChurchGate Mumbai', '[email protected]'),
   (3, 3, 'Jubilee Hills Hyderabad', '[email protected]')]

intersecarse()

Usando el operador INTERSECT, SQL muestra filas comunes de ambas sentencias SELECT. La función intersect () implementa este comportamiento.

En los siguientes ejemplos, dos construcciones SELECT son parámetros para la función intersect (). Uno devuelve filas que contienen "gmail.com" como parte de la columna email_add, y otro devuelve filas que contienen "Pune" como parte de la columna postal_add. El resultado serán filas comunes de ambos conjuntos de resultados.

u = intersect(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

En efecto, esto es equivalente a la siguiente instrucción SQL:

SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? INTERSECT SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

Los dos parámetros enlazados '% gmail.com' y '% Pune' generan una sola fila a partir de los datos originales en la tabla de direcciones como se muestra a continuación:

[(1, 1, 'Shivajinagar Pune', '[email protected]')]

El objetivo principal de la API Object Relational Mapper de SQLAlchemy es facilitar la asociación de clases de Python definidas por el usuario con tablas de bases de datos, y objetos de esas clases con filas en sus tablas correspondientes. Los cambios en los estados de los objetos y las filas se emparejan de forma sincronizada entre sí. SQLAlchemy permite expresar consultas de base de datos en términos de clases definidas por el usuario y sus relaciones definidas.

El ORM se construye sobre el lenguaje de expresión SQL. Es un patrón de uso de alto nivel y abstracto. De hecho, ORM es un uso aplicado del lenguaje de expresión.

Aunque una aplicación exitosa se puede construir utilizando el Object Relational Mapper exclusivamente, a veces una aplicación construida con el ORM puede usar el Lenguaje de Expresión directamente donde se requieren interacciones específicas con la base de datos.

Declarar mapeo

En primer lugar, se llama a la función create_engine () para configurar un objeto de motor que se utiliza posteriormente para realizar operaciones SQL. La función tiene dos argumentos, uno es el nombre de la base de datos y otro es un parámetro de eco cuando se establece en True generará el registro de actividad. Si no existe, se creará la base de datos. En el siguiente ejemplo, se crea una base de datos SQLite.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)

El motor establece una conexión DBAPI real a la base de datos cuando se llama a un método como Engine.execute () o Engine.connect (). Luego se usa para emitir SQLORM que no usa el motor directamente; en cambio, el ORM lo utiliza entre bastidores.

En el caso de ORM, el proceso de configuración comienza describiendo las tablas de la base de datos y luego definiendo las clases que se asignarán a esas tablas. En SQLAlchemy, estas dos tareas se realizan juntas. Esto se hace mediante el sistema declarativo; las clases creadas incluyen directivas para describir la tabla de base de datos real a la que están asignadas.

Una clase base almacena un catálogo de clases y tablas mapeadas en el sistema declarativo. Esto se llama clase base declarativa. Por lo general, solo habrá una instancia de esta base en un módulo comúnmente importado. La función declarative_base () se usa para crear una clase base. Esta función está definida en el módulo declarativo sqlalchemy.ext.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

Una vez que se declara la clase base, se puede definir cualquier número de clases mapeadas en términos de ella. El siguiente código define la clase de un cliente. Contiene la tabla a la que se asignará y los nombres y tipos de datos de las columnas que contiene.

class Customers(Base):
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

Una clase en Declarativo debe tener un __tablename__ atributo, y al menos uno Columnque es parte de una clave primaria. Declarativo reemplaza todos losColumn objetos con accesos especiales de Python conocidos como descriptors. Este proceso se conoce como instrumentación, que proporciona los medios para hacer referencia a la tabla en un contexto SQL y permite conservar y cargar los valores de las columnas de la base de datos.

Esta clase mapeada como una clase Python normal tiene atributos y métodos según el requisito.

La información sobre la clase en el sistema declarativo se denomina metadatos de tabla. SQLAlchemy usa el objeto Table para representar esta información para una tabla específica creada por Declarative. El objeto Table se crea de acuerdo con las especificaciones y se asocia con la clase mediante la construcción de un objeto Mapper. Este objeto mapeador no se usa directamente, pero se usa internamente como interfaz entre la clase mapeada y la tabla.

Cada objeto Table es miembro de una colección más grande conocida como MetaData y este objeto está disponible usando el .metadataatributo de clase base declarativa. losMetaData.create_all()es pasar nuestro motor como fuente de conectividad de base de datos. Para todas las tablas que aún no se han creado, emite instrucciones CREATE TABLE a la base de datos.

Base.metadata.create_all(engine)

El script completo para crear una base de datos y una tabla, y para mapear la clase Python se da a continuación:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   id = Column(Integer, primary_key=True)

   name = Column(String)
   address = Column(String)
   email = Column(String)
Base.metadata.create_all(engine)

Cuando se ejecuta, la consola de Python se repetirá después de la ejecución de la expresión SQL:

CREATE TABLE customers (
   id INTEGER NOT NULL,
   name VARCHAR,
   address VARCHAR,
   email VARCHAR,
   PRIMARY KEY (id)
)

Si abrimos Sales.db usando la herramienta gráfica SQLiteStudio, muestra la tabla de clientes en su interior con la estructura mencionada anteriormente.

Para interactuar con la base de datos, necesitamos obtener su identificador. Un objeto de sesión es el identificador de la base de datos. La clase de sesión se define usando sessionmaker (), un método de fábrica de sesiones configurable que está vinculado al objeto del motor creado anteriormente.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)

Luego, el objeto de sesión se configura utilizando su constructor predeterminado de la siguiente manera:

session = Session()

Algunos de los métodos de clase de sesión que se requieren con frecuencia se enumeran a continuación:

No Señor.	Método y descripción
1	begin() comienza una transacción en esta sesión
2	add() coloca un objeto en la sesión. Su estado se conserva en la base de datos en la siguiente operación de descarga.
3	add_all() agrega una colección de objetos a la sesión
4	commit() descarga todos los artículos y cualquier transacción en curso
5	delete() marca una transacción como eliminada
6	execute() ejecuta una expresión SQL
7	expire() marca los atributos de una instancia como desactualizados
8	flush() vacía todos los cambios de objetos en la base de datos
9	invalidate() cierra la sesión usando la invalidación de la conexión
10	rollback() revierte la transacción actual en curso
11	close() Cierra la sesión actual borrando todos los elementos y finalizando cualquier transacción en curso

En los capítulos anteriores de SQLAlchemy ORM, hemos aprendido cómo declarar mapeo y crear sesiones. En este capítulo, aprenderemos cómo agregar objetos a la tabla.

Hemos declarado la clase de cliente que se ha asignado a la tabla de clientes. Tenemos que declarar un objeto de esta clase y agregarlo persistentemente a la tabla mediante el método add () del objeto de sesión.

c1 = Sales(name = 'Ravi Kumar', address = 'Station Road Nanded', email = '[email protected]')
session.add(c1)

Tenga en cuenta que esta transacción está pendiente hasta que se vacíe con el método commit ().

session.commit()

A continuación se muestra el script completo para agregar un registro en la tabla de clientes:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key=True)
   name = Column(String)
   address = Column(String)
   email = Column(String)
   
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

c1 = Customers(name = 'Ravi Kumar', address = 'Station Road Nanded', email = '[email protected]')

session.add(c1)
session.commit()

Para agregar varios registros, podemos usar add_all() método de la clase de sesión.

session.add_all([
   Customers(name = 'Komal Pande', address = 'Koti, Hyderabad', email = '[email protected]'), 
   Customers(name = 'Rajender Nath', address = 'Sector 40, Gurgaon', email = '[email protected]'), 
   Customers(name = 'S.M.Krishna', address = 'Budhwar Peth, Pune', email = '[email protected]')]
)

session.commit()

La vista de tabla de SQLiteStudio muestra que los registros se agregan de forma persistente en la tabla de clientes. La siguiente imagen muestra el resultado:

Todas las sentencias SELECT generadas por SQLAlchemy ORM son construidas por el objeto Query. Proporciona una interfaz generativa, por lo que las sucesivas llamadas devuelven un nuevo objeto de consulta, una copia del anterior con criterios y opciones adicionales asociados.

Los objetos de consulta se generan inicialmente utilizando el método query () de la sesión de la siguiente manera:

q = session.query(mapped class)

La siguiente declaración también es equivalente a la declaración dada anteriormente:

q = Query(mappedClass, session)

El objeto de consulta tiene el método all () que devuelve un conjunto de resultados en forma de lista de objetos. Si lo ejecutamos en la mesa de nuestros clientes -

result = session.query(Customers).all()

Esta declaración es efectivamente equivalente a la siguiente expresión SQL:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers

El objeto de resultado se puede recorrer utilizando el bucle For como se muestra a continuación para obtener todos los registros en la tabla de clientes subyacente. Aquí está el código completo para mostrar todos los registros en la tabla Clientes:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   id = Column(Integer, primary_key =  True)
   name = Column(String)

   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).all()

for row in result:
   print ("Name: ",row.name, "Address:",row.address, "Email:",row.email)

La consola de Python muestra la lista de registros a continuación:

Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
Name: Komal Pande Address: Koti, Hyderabad Email: [email protected]
Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]

El objeto de consulta también tiene los siguientes métodos útiles:

No Señor.	Método y descripción
1	add_columns() Agrega una o más expresiones de columna a la lista de columnas de resultado que se devolverán.
2	add_entity() Agrega una entidad mapeada a la lista de columnas de resultados que se devolverán.
3	count() Devuelve un recuento de filas que devolvería esta consulta.
4	delete() Realiza una consulta de eliminación masiva. Elimina de la base de datos las filas que coinciden con esta consulta.
5	distinct() Aplica una cláusula DISTINCT a la consulta y devuelve la consulta resultante.
6	filter() Aplica el criterio de filtrado dado a una copia de esta consulta, utilizando expresiones SQL.
7	first() Devuelve el primer resultado de esta Consulta o Ninguno si el resultado no contiene ninguna fila.
8	get() Devuelve una instancia basada en el identificador de clave principal proporcionado que proporciona acceso directo al mapa de identidad de la sesión propietaria.
9	group_by() Aplica uno o más criterios GROUP BY a la consulta y devuelve la Consulta recién resultante
10	join() Crea un SQL JOIN contra el criterio de este objeto de consulta y se aplica generativamente, devolviendo la consulta resultante.
11	one() Devuelve exactamente un resultado o genera una excepción.
12	order_by() Aplica uno o más criterios ORDER BY a la consulta y devuelve la consulta resultante.
13	update() Realiza una consulta de actualización masiva y actualiza las filas que coinciden con esta consulta en la base de datos.

En este capítulo, veremos cómo modificar o actualizar la tabla con los valores deseados.

Para modificar los datos de un determinado atributo de cualquier objeto, tenemos que asignarle un nuevo valor y confirmar los cambios para que el cambio sea persistente.

Busquemos un objeto de la tabla cuyo identificador de clave principal, en nuestra tabla Clientes con ID = 2. Podemos usar el método de sesión get () de la siguiente manera:

x = session.query(Customers).get(2)

Podemos mostrar el contenido del objeto seleccionado con el siguiente código:

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

De la tabla de nuestros clientes, se debe mostrar el siguiente resultado:

Name: Komal Pande Address: Koti, Hyderabad Email: [email protected]

Ahora necesitamos actualizar el campo Dirección asignando un nuevo valor como se indica a continuación:

x.address = 'Banjara Hills Secunderabad'
session.commit()

El cambio se reflejará de forma persistente en la base de datos. Ahora obtenemos el objeto correspondiente a la primera fila de la tabla usandofirst() method como sigue -

x = session.query(Customers).first()

Esto se ejecutará siguiendo la expresión SQL:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
LIMIT ? OFFSET ?

Los parámetros enlazados serán LIMIT = 1 y OFFSET = 0 respectivamente, lo que significa que se seleccionará la primera fila.

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

Ahora, la salida del código anterior que muestra la primera fila es la siguiente:

Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]

Ahora cambie el atributo de nombre y muestre el contenido usando el siguiente código:

x.name = 'Ravi Shrivastava'
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

La salida del código anterior es:

Name: Ravi Shrivastava Address: Station Road Nanded Email: [email protected]

Aunque se muestra el cambio, no se confirma. Puede retener la posición persistente anterior utilizandorollback() method con el código de abajo.

session.rollback()

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

Se mostrará el contenido original del primer registro.

Para actualizaciones masivas, usaremos el método update () del objeto Query. Intentemos dar un prefijo, 'Sr.' nombrar en cada fila (excepto ID = 2). La instrucción update () correspondiente es la siguiente:

session.query(Customers).filter(Customers.id! = 2).
update({Customers.name:"Mr."+Customers.name}, synchronize_session = False)

The update() method requires two parameters as follows −

• Un diccionario de valores-clave en el que la clave es el atributo a actualizar y el valor es el nuevo contenido del atributo.

• Atributo synchronize_session que menciona la estrategia para actualizar atributos en la sesión. Los valores válidos son falsos: para no sincronizar la sesión, buscar: realiza una consulta de selección antes de la actualización para encontrar objetos que coincidan con la consulta de actualización; y evaluar: evaluar criterios sobre objetos en la sesión.

Tres de las 4 filas de la tabla tendrán el nombre con el prefijo 'Sr.' Sin embargo, los cambios no se confirman y, por lo tanto, no se reflejarán en la vista de tabla de SQLiteStudio. Se actualizará solo cuando confirmemos la sesión.

En este capítulo, discutiremos cómo aplicar el filtro y también ciertas operaciones de filtro junto con sus códigos.

El conjunto de resultados representado por el objeto Query puede estar sujeto a ciertos criterios usando el método filter (). El uso general del método de filtro es el siguiente:

session.query(class).filter(criteria)

En el siguiente ejemplo, el conjunto de resultados obtenido por la consulta SELECT en la tabla Clientes se filtra por una condición, (ID> 2) -

result = session.query(Customers).filter(Customers.id>2)

Esta declaración se traducirá en la siguiente expresión SQL:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id > ?

Dado que el parámetro enlazado (?) Se da como 2, solo se mostrarán aquellas filas con columna de ID> 2. El código completo se proporciona a continuación:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key = True)
   name = Column(String)

   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).filter(Customers.id>2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

La salida que se muestra en la consola de Python es la siguiente:

ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]

Ahora, aprenderemos las operaciones de filtrado con sus respectivos códigos y salida.

Igual

El operador habitual utilizado es == y aplica los criterios para comprobar la igualdad.

result = session.query(Customers).filter(Customers.id == 2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

SQLAlchemy enviará la siguiente expresión SQL:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id = ?

La salida para el código anterior es la siguiente:

ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: [email protected]

No es igual

El operador utilizado para no es igual es! = Y proporciona criterios de no es igual.

result = session.query(Customers).filter(Customers.id! = 2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

La expresión SQL resultante es:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id != ?

El resultado de las líneas de código anteriores es el siguiente:

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]

Me gusta

El método like () en sí mismo produce los criterios LIKE para la cláusula WHERE en la expresión SELECT.

result = session.query(Customers).filter(Customers.name.like('Ra%'))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

El código anterior de SQLAlchemy es equivalente a la siguiente expresión SQL:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.name LIKE ?

Y la salida para el código anterior es:

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]

EN

Este operador comprueba si el valor de la columna pertenece a una colección de elementos de una lista. Es proporcionado por el método in_ ().

result = session.query(Customers).filter(Customers.id.in_([1,3]))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Aquí, la expresión SQL evaluada por el motor SQLite será la siguiente:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id IN (?, ?)

La salida para el código anterior es la siguiente:

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]

Y

Esta conjunción es generada por putting multiple commas separated criteria in the filter or using and_() method como se indica a continuación -

result = session.query(Customers).filter(Customers.id>2, Customers.name.like('Ra%'))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

from sqlalchemy import and_
result = session.query(Customers).filter(and_(Customers.id>2, Customers.name.like('Ra%')))

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Ambos enfoques anteriores dan como resultado una expresión SQL similar:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id > ? AND customers.name LIKE ?

El resultado de las líneas de código anteriores es:

ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]

O

Esta conjunción es implementada por or_() method.

from sqlalchemy import or_
result = session.query(Customers).filter(or_(Customers.id>2, Customers.name.like('Ra%')))

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Como resultado, el motor SQLite obtiene la siguiente expresión SQL equivalente:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id > ? OR customers.name LIKE ?

La salida para el código anterior es la siguiente:

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]

Hay una serie de métodos de objeto de consulta que emiten SQL inmediatamente y devuelven un valor que contiene los resultados de la base de datos cargada.

Aquí hay un breve resumen de la lista que regresa y los escalares:

todas()

Devuelve una lista. A continuación se muestra la línea de código para la función all ().

session.query(Customers).all()

La consola de Python muestra la siguiente expresión SQL emitida:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers

primero()

Aplica un límite de uno y devuelve el primer resultado como escalar.

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
LIMIT ? OFFSET ?

Los parámetros límite para LIMIT es 1 y para OFFSET es 0.

uno()

Este comando recupera por completo todas las filas y, si no hay exactamente una identidad de objeto o una fila compuesta presente en el resultado, genera un error.

session.query(Customers).one()

Con varias filas encontradas -

MultipleResultsFound: Multiple rows were found for one()

Sin filas encontradas -

NoResultFound: No row was found for one()

El método one () es útil para los sistemas que esperan manejar "no se encontraron elementos" versus "varios elementos encontrados" de manera diferente.

escalar()

Invoca el método one () y, si tiene éxito, devuelve la primera columna de la fila de la siguiente manera:

session.query(Customers).filter(Customers.id == 3).scalar()

Esto genera la siguiente declaración SQL:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id = ?

Anteriormente, SQL textual que usa la función text () se ha explicado desde la perspectiva del lenguaje de expresión central de SQLAlchemy. Ahora lo discutiremos desde el punto de vista ORM.

Las cadenas literales se pueden usar de manera flexible con el objeto Query especificando su uso con la construcción text (). La mayoría de los métodos aplicables lo aceptan. Por ejemplo, filter () y order_by ().

En el ejemplo que se muestra a continuación, el método filter () traduce la cadena "id <3" a WHERE id <3

from sqlalchemy import text
for cust in session.query(Customers).filter(text("id<3")):
   print(cust.name)

La expresión SQL sin procesar generada muestra la conversión del filtro a la cláusula WHERE con el código que se ilustra a continuación:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE id<3

De nuestros datos de muestra en la tabla Clientes, se seleccionarán dos filas y la columna de nombre se imprimirá de la siguiente manera:

Ravi Kumar
Komal Pande

Para especificar parámetros de vinculación con SQL basado en cadenas, utilice dos puntos y, para especificar los valores, utilice el método params ().

cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()

El SQL efectivo que se muestra en la consola de Python será el siguiente:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE id = ?

Para usar una declaración completamente basada en cadenas, una construcción text () que representa una declaración completa se puede pasar a from_statement ().

session.query(Customers).from_statement(text("SELECT * FROM customers")).all()

El resultado del código anterior será una instrucción SELECT básica como se indica a continuación:

SELECT * FROM customers

Obviamente, se seleccionarán todos los registros de la tabla de clientes.

La construcción text () nos permite vincular su SQL textual a expresiones de columna mapeadas en Core o ORM posicionalmente. Podemos lograr esto pasando expresiones de columna como argumentos posicionales al método TextClause.columns ().

stmt = text("SELECT name, id, name, address, email FROM customers")
stmt = stmt.columns(Customers.id, Customers.name)
session.query(Customers.id, Customers.name).from_statement(stmt).all()

Las columnas de identificación y nombre de todas las filas se seleccionarán aunque el motor SQLite ejecute la siguiente expresión generada por el código anterior que muestra todas las columnas en el método text ():

SELECT name, id, name, address, email FROM customers

Esta sesión describe la creación de otra tabla que está relacionada con una ya existente en nuestra base de datos. La tabla de clientes contiene datos maestros de los clientes. Ahora necesitamos crear una tabla de facturas que puede tener cualquier número de facturas pertenecientes a un cliente. Este es un caso de relaciones de una a muchas.

Utilizando declarativo, definimos esta tabla junto con su clase asignada, Facturas como se indica a continuación:

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship

class Customer(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

class Invoice(Base):
   __tablename__ = 'invoices'
   
   id = Column(Integer, primary_key = True)
   custid = Column(Integer, ForeignKey('customers.id'))
   invno = Column(Integer)
   amount = Column(Integer)
   customer = relationship("Customer", back_populates = "invoices")

Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")
Base.metadata.create_all(engine)

Esto enviará una consulta CREATE TABLE al motor SQLite como se muestra a continuación:

CREATE TABLE invoices (
   id INTEGER NOT NULL,
   custid INTEGER,
   invno INTEGER,
   amount INTEGER,
   PRIMARY KEY (id),
   FOREIGN KEY(custid) REFERENCES customers (id)
)

Podemos comprobar que se crea una nueva tabla en sales.db con la ayuda de la herramienta SQLiteStudio.

La clase Facturas aplica la construcción ForeignKey en el atributo custid. Esta directiva indica que los valores en esta columna deben restringirse para ser valores presentes en la columna id en la tabla de clientes. Esta es una característica central de las bases de datos relacionales y es el "pegamento" que transforma la colección de tablas no conectadas para que tengan relaciones superpuestas ricas.

Una segunda directiva, conocida como relación (), le dice al ORM que la clase Invoice debe estar vinculada a la clase Customer mediante el atributo Invoice.customer. La relación () utiliza las relaciones de clave externa entre las dos tablas para determinar la naturaleza de este vínculo, determinando que es de muchos a uno.

Se coloca una directiva de relación adicional () en la clase asignada por el cliente bajo el atributo Customer.invoices. El parámetro Relationship.back_poplates se asigna para hacer referencia a los nombres de atributos complementarios, de modo que cada relación () pueda tomar una decisión inteligente sobre la misma relación expresada al revés. Por un lado, Invoices.customer se refiere a la instancia de Invoices y, por otro lado, Customer.invoices se refiere a una lista de instancias de Clientes.

La función de relación es parte de la API de relación del paquete ORM de SQLAlchemy. Proporciona una relación entre dos clases mapeadas. Esto corresponde a una relación de tabla asociativa o padre-hijo.

Los siguientes son los patrones de relación básicos encontrados:

Uno a muchos

Una relación de uno a muchos se refiere al padre con la ayuda de una clave externa en la tabla secundaria. Entonces, la relación () se especifica en el padre, como referencia a una colección de elementos representados por el hijo. El parámetro Relationship.back_poplates se utiliza para establecer una relación bidireccional en uno a varios, donde el lado "inverso" es de varios a uno.

Muchos a uno

Por otro lado, la relación Many to One coloca una clave externa en la tabla principal para hacer referencia al secundario. La relación () se declara en el padre, donde se creará un nuevo atributo de retención de escalares. Aquí de nuevo, el parámetro Relationship.back_poplates se utiliza para Bidirectionalbehaviour.

Doce y cincuenta y nueve de la noche

La relación uno a uno es esencialmente una relación bidireccional por naturaleza. El indicador de lista de usuario indica la ubicación de un atributo escalar en lugar de una colección en el lado "muchos" de la relación. Para convertir uno a varios en un tipo de relación uno a uno, establezca el parámetro uselist en false.

Muchos a muchos

La relación Many to Many se establece agregando una tabla de asociación relacionada con dos clases definiendo atributos con sus claves externas. Está indicado por el argumento secundario de la relación (). Por lo general, la tabla usa el objeto MetaData asociado con la clase base declarativa, de modo que las directivas ForeignKey pueden ubicar las tablas remotas con las que enlazar. El parámetro Relationship.back_poplates para cada relación () establece una relación bidireccional. Ambos lados de la relación contienen una colección.

En este capítulo, nos centraremos en los objetos relacionados en SQLAlchemy ORM.

Ahora, cuando creamos un objeto Cliente, aparecerá una colección de facturas en blanco en forma de Lista de Python.

c1 = Customer(name = "Gopal Krishna", address = "Bank Street Hydarebad", email = "[email protected]")

El atributo de facturas de c1.invoices será una lista vacía. Podemos asignar elementos en la lista como:

c1.invoices = [Invoice(invno = 10, amount = 15000), Invoice(invno = 14, amount = 3850)]

Enviemos este objeto a la base de datos usando el objeto Session de la siguiente manera:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(c1)
session.commit()

Esto generará automáticamente consultas INSERT para clientes y tablas de facturas -

INSERT INTO customers (name, address, email) VALUES (?, ?, ?) 
('Gopal Krishna', 'Bank Street Hydarebad', '[email protected]')
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 10, 15000)
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 14, 3850)

Veamos ahora el contenido de la tabla de clientes y la tabla de facturas en la vista de tabla de SQLiteStudio -

Puede construir un objeto Cliente proporcionando un atributo mapeado de facturas en el propio constructor utilizando el siguiente comando:

c2 = [
   Customer(
      name = "Govind Pant", 
      address = "Gulmandi Aurangabad",
      email = "[email protected]",
      invoices = [Invoice(invno = 3, amount = 10000), 
      Invoice(invno = 4, amount = 5000)]
   )
]

O una lista de objetos que se agregarán usando la función add_all () del objeto de sesión como se muestra a continuación:

rows = [
   Customer(
      name = "Govind Kala", 
      address = "Gulmandi Aurangabad", 
      email = "[email protected]", 
      invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]),

   Customer(
      name = "Abdul Rahman", 
      address = "Rohtak", 
      email = "[email protected]",
      invoices = [Invoice(invno = 9, amount = 15000), 
      Invoice(invno = 11, amount = 6000)
   ])
]

session.add_all(rows)
session.commit()

Ahora que tenemos dos tablas, veremos cómo crear consultas en ambas tablas al mismo tiempo. Para construir una unión implícita simple entre Cliente y Factura, podemos usar Query.filter () para equiparar sus columnas relacionadas. A continuación, cargamos las entidades Cliente y Factura a la vez utilizando este método:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for c, i in session.query(Customer, Invoice).filter(Customer.id == Invoice.custid).all():
   print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id,c.name, i.invno, i.amount))

La expresión SQL emitida por SQLAlchemy es la siguiente:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email, invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM customers, invoices
WHERE customers.id = invoices.custid

Y el resultado de las líneas de código anteriores es el siguiente:

ID: 2 Name: Gopal Krishna Invoice No: 10 Amount: 15000
ID: 2 Name: Gopal Krishna Invoice No: 14 Amount: 3850
ID: 3 Name: Govind Pant Invoice No: 3 Amount: 10000
ID: 3 Name: Govind Pant Invoice No: 4 Amount: 5000
ID: 4 Name: Govind Kala Invoice No: 7 Amount: 12000
ID: 4 Name: Govind Kala Invoice No: 8 Amount: 8500
ID: 5 Name: Abdul Rahman Invoice No: 9 Amount: 15000
ID: 5 Name: Abdul Rahman Invoice No: 11 Amount: 6000

La sintaxis de SQL JOIN real se logra fácilmente usando el método Query.join () de la siguiente manera:

session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()

La expresión SQL para unión se mostrará en la consola:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers JOIN invoices ON customers.id = invoices.custid
WHERE invoices.amount = ?

Podemos iterar a través del resultado usando for loop -

result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500)
for row in result:
   for inv in row.invoices:
      print (row.id, row.name, inv.invno, inv.amount)

Con 8500 como parámetro de vinculación, se muestra la siguiente salida:

4 Govind Kala 8 8500

Query.join () sabe cómo unirse entre estas tablas porque solo hay una clave externa entre ellas. Si no hubiera claves externas, o más claves externas, Query.join () funciona mejor cuando se usa una de las siguientes formas:

query.join (Factura, id == Address.custid)	condición explícita
query.join (Customer.invoices)	especificar la relación de izquierda a derecha
query.join (Factura, Customer.invoices)	lo mismo, con objetivo explícito
query.join ('facturas')	lo mismo, usando una cuerda

De manera similar, la función outsidejoin () está disponible para lograr la unión externa izquierda.

query.outerjoin(Customer.invoices)

El método subquery () produce una expresión SQL que representa la instrucción SELECT incrustada dentro de un alias.

from sqlalchemy.sql import func

stmt = session.query(
   Invoice.custid, func.count('*').label('invoice_count')
).group_by(Invoice.custid).subquery()

El objeto stmt contendrá una declaración SQL como se muestra a continuación:

SELECT invoices.custid, count(:count_1) AS invoice_count FROM invoices GROUP BY invoices.custid

Una vez que tenemos nuestra declaración, se comporta como una construcción de tabla. Se puede acceder a las columnas de la declaración a través de un atributo llamado c, como se muestra en el siguiente código:

for u, count in session.query(Customer, stmt.c.invoice_count).outerjoin(stmt, Customer.id == stmt.c.custid).order_by(Customer.id):
   print(u.name, count)

El bucle for anterior muestra el recuento de facturas por nombre de la siguiente manera:

Arjun Pandit None
Gopal Krishna 2
Govind Pant 2
Govind Kala 2
Abdul Rahman 2

En este capítulo, discutiremos sobre los operadores que se basan en relaciones.

__eq __ ()

El operador anterior es una comparación "igual" de varios a uno. La línea de código para este operador es la que se muestra a continuación:

s = session.query(Customer).filter(Invoice.invno.__eq__(12))

La consulta SQL equivalente para la línea de código anterior es:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers, invoices
WHERE invoices.invno = ?

__nordeste__()

Este operador es una comparación "no es igual" de varios a uno. La línea de código para este operador es la que se muestra a continuación:

s = session.query(Customer).filter(Invoice.custid.__ne__(2))

La consulta SQL equivalente para la línea de código anterior se proporciona a continuación:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers, invoices
WHERE invoices.custid != ?

contiene ()

Este operador se usa para colecciones de uno a muchos y a continuación se muestra el código para contains () -

s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5]))

La consulta SQL equivalente para la línea de código anterior es:

SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM invoices
WHERE (invoices.invno LIKE '%' + ? || '%')

ninguna()

El operador any () se usa para colecciones como se muestra a continuación:

s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11))

La consulta SQL equivalente para la línea de código anterior se muestra a continuación:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE EXISTS (
   SELECT 1
   FROM invoices
   WHERE customers.id = invoices.custid 
   AND invoices.invno = ?)

tiene()

Este operador se utiliza para referencias escalares de la siguiente manera:

s = session.query(Invoice).filter(Invoice.customer.has(name = 'Arjun Pandit'))

La consulta SQL equivalente para la línea de código anterior es:

SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM invoices
WHERE EXISTS (
   SELECT 1
   FROM customers
   WHERE customers.id = invoices.custid 
   AND customers.name = ?)

La carga ansiosa reduce el número de consultas. SQLAlchemy ofrece funciones de carga ansiosas invocadas a través de opciones de consulta que dan instrucciones adicionales a la consulta. Estas opciones determinan cómo cargar varios atributos mediante el método Query.options ().

Carga de subconsultas

Queremos que las facturas del cliente se carguen con entusiasmo. La opción orm.subqueryload () proporciona una segunda instrucción SELECT que carga completamente las colecciones asociadas con los resultados recién cargados. El nombre "subconsulta" hace que la instrucción SELECT se construya directamente a través de la consulta reutilizada e incrustada como una subconsulta en un SELECT contra la tabla relacionada.

from sqlalchemy.orm import subqueryload
c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = 'Govind Pant').one()

Esto da como resultado las siguientes dos expresiones SQL:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.name = ?
('Govind Pant',)

SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount, anon_1.customers_id 
AS anon_1_customers_id
FROM (
   SELECT customers.id 
   AS customers_id
   FROM customers
   WHERE customers.name = ?) 
   
AS anon_1 
JOIN invoices 
ON anon_1.customers_id = invoices.custid 
ORDER BY anon_1.customers_id, invoices.id 2018-06-25 18:24:47,479 
INFO sqlalchemy.engine.base.Engine ('Govind Pant',)

Para acceder a los datos de dos tablas, podemos usar el siguiente programa:

print (c1.name, c1.address, c1.email)

for x in c1.invoices:
   print ("Invoice no : {}, Amount : {}".format(x.invno, x.amount))

El resultado del programa anterior es el siguiente:

Govind Pant Gulmandi Aurangabad [email protected]
Invoice no : 3, Amount : 10000
Invoice no : 4, Amount : 5000

Carga unida

La otra función se llama orm.joinedload (). Esto emite un LEFT OUTER JOIN. El objeto principal, así como el objeto o la colección relacionados, se cargan en un solo paso.

from sqlalchemy.orm import joinedload
c1 = session.query(Customer).options(joinedload(Customer.invoices)).filter_by(name='Govind Pant').one()

Esto emite la siguiente expresión dando la misma salida que la anterior:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email, invoices_1.id 
AS invoices_1_id, invoices_1.custid 
AS invoices_1_custid, invoices_1.invno 
AS invoices_1_invno, invoices_1.amount 
AS invoices_1_amount

FROM customers 
LEFT OUTER JOIN invoices 
AS invoices_1 
ON customers.id = invoices_1.custid

WHERE customers.name = ? ORDER BY invoices_1.id
('Govind Pant',)

OUTER JOIN resultó en dos filas, pero devuelve una instancia de Customer. Esto se debe a que Query aplica una estrategia de "uniquing", basada en la identidad del objeto, a las entidades devueltas. La carga ansiosa unida se puede aplicar sin afectar los resultados de la consulta.

El subqueryload () es más apropiado para cargar colecciones relacionadas mientras que joinload () es más adecuado para la relación de varios a uno.

Es fácil realizar la operación de eliminación en una sola tabla. Todo lo que tiene que hacer es eliminar un objeto de la clase asignada de una sesión y confirmar la acción. Sin embargo, la operación de eliminación en varias tablas relacionadas es un poco complicada.

En nuestra base de datos sales.db, las clases de clientes y facturas se asignan a la tabla de clientes y facturas con uno a varios tipos de relación. Intentaremos eliminar el objeto Cliente y veremos el resultado.

Como referencia rápida, a continuación se encuentran las definiciones de las clases de cliente y factura:

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship
class Customer(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)
   
class Invoice(Base):
   __tablename__ = 'invoices'

   id = Column(Integer, primary_key = True)
   custid = Column(Integer, ForeignKey('customers.id'))
   invno = Column(Integer)
   amount = Column(Integer)
   customer = relationship("Customer", back_populates = "invoices")
   
Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")

Configuramos una sesión y obtenemos un objeto Cliente al consultarlo con la identificación principal utilizando el programa a continuación:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
x = session.query(Customer).get(2)

En nuestra tabla de muestra, x.name resulta ser 'Gopal Krishna'. Eliminemos esta x de la sesión y contemos la aparición de este nombre.

session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()

La expresión SQL resultante devolverá 0.

SELECT count(*) 
AS count_1
FROM (
   SELECT customers.id 
   AS customers_id, customers.name 
   AS customers_name, customers.address 
   AS customers_address, customers.email 
   AS customers_email
   FROM customers
   WHERE customers.name = ?) 
AS anon_1('Gopal Krishna',) 0

Sin embargo, los objetos Factura relacionados de x todavía están allí. Se puede verificar con el siguiente código:

session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()

Aquí, 10 y 14 son números de factura que pertenecen al cliente Gopal Krishna. El resultado de la consulta anterior es 2, lo que significa que los objetos relacionados no se han eliminado.

SELECT count(*) 
AS count_1
FROM (
   SELECT invoices.id 
   AS invoices_id, invoices.custid 
   AS invoices_custid, invoices.invno 
   AS invoices_invno, invoices.amount 
   AS invoices_amount
   FROM invoices
   WHERE invoices.invno IN (?, ?)) 
AS anon_1(10, 14) 2

Esto se debe a que SQLAlchemy no asume la eliminación de cascada; tenemos que dar un comando para borrarlo.

Para cambiar el comportamiento, configuramos opciones en cascada en la relación User.addresses. Cerremos la sesión en curso, usemos new declarative_base () y redeclaremos la clase User, agregando la relación de direcciones, incluida la configuración en cascada.

El atributo de cascada en la función de relación es una lista separada por comas de reglas en cascada que determina cómo las operaciones de la sesión deben ser "en cascada" de padre a hijo. De forma predeterminada, es Falso, lo que significa que es "guardar-actualizar, fusionar".

Las cascadas disponibles son las siguientes:

• save-update
• merge
• expunge
• delete
• delete-orphan
• refresh-expire

La opción que se usa con frecuencia es "todo, eliminar-huérfano" para indicar que los objetos relacionados deben seguir al objeto principal en todos los casos y eliminarse cuando se desasocie.

Por lo tanto, la clase de cliente redeclarada se muestra a continuación:

class Customer(Base): 
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key = True) 
   name = Column(String) 
   address = Column(String) 
   email = Column(String) 
   invoices = relationship(
      "Invoice", 
      order_by = Invoice.id, 
      back_populates = "customer",
      cascade = "all, 
      delete, delete-orphan" 
   )

Eliminemos el Cliente con el nombre de Gopal Krishna utilizando el programa a continuación y veamos el recuento de sus objetos de Factura relacionados:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
x = session.query(Customer).get(2)
session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()
session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()

El recuento ahora es 0 con el siguiente SQL emitido por el script anterior:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id = ?
(2,)
SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE ? = invoices.custid 
ORDER BY invoices.id (2,)
DELETE FROM invoices 
WHERE invoices.id = ? ((1,), (2,))
DELETE FROM customers 
WHERE customers.id = ? (2,)
SELECT count(*) 
AS count_1
FROM (
   SELECT customers.id 
   AS customers_id, customers.name 
   AS customers_name, customers.address 
   AS customers_address, customers.email 
   AS customers_email
   FROM customers
   WHERE customers.name = ?) 
AS anon_1('Gopal Krishna',)
SELECT count(*) 
AS count_1
FROM (
   SELECT invoices.id 
   AS invoices_id, invoices.custid 
   AS invoices_custid, invoices.invno 
   AS invoices_invno, invoices.amount 
   AS invoices_amount
   FROM invoices
   WHERE invoices.invno IN (?, ?)) 
AS anon_1(10, 14)
0

Many to Many relationshipentre dos tablas se logra agregando una tabla de asociación de modo que tenga dos claves externas, una de la clave principal de cada tabla. Además, las clases que se asignan a las dos tablas tienen un atributo con una colección de objetos de otras tablas de asociación asignados como atributo secundario de la función de relación ().

Para ello, crearemos una base de datos SQLite (mycollege.db) con dos tablas: departamento y empleado. Aquí, asumimos que un empleado es parte de más de un departamento y un departamento tiene más de un empleado. Esto constituye una relación de muchos a muchos.

La definición de las clases de Empleado y Departamento asignadas a la tabla de departamento y empleado es la siguiente:

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///mycollege.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship

class Department(Base):
   __tablename__ = 'department'
   id = Column(Integer, primary_key = True)
   name = Column(String)
   employees = relationship('Employee', secondary = 'link')
   
class Employee(Base):
   __tablename__ = 'employee'
   id = Column(Integer, primary_key = True)
   name = Column(String)
   departments = relationship(Department,secondary='link')

Ahora definimos una clase Link. Está vinculado a la tabla de vínculos y contiene los atributos department_id y employee_id, respectivamente, que hacen referencia a las claves principales de la tabla de departamento y empleado.

class Link(Base):
   __tablename__ = 'link'
   department_id = Column(
      Integer, 
      ForeignKey('department.id'), 
      primary_key = True)

employee_id = Column(
   Integer, 
   ForeignKey('employee.id'), 
   primary_key = True)

Aquí, tenemos que tomar nota de que la clase Departamento tiene un atributo de empleados relacionado con la clase Empleado. Al atributo secundario de la función de relación se le asigna un vínculo como su valor.

De manera similar, la clase Empleado tiene un atributo de departamentos relacionado con la clase Departamento. Al atributo secundario de la función de relación se le asigna un vínculo como su valor.

Todas estas tres tablas se crean cuando se ejecuta la siguiente declaración:

Base.metadata.create_all(engine)

La consola de Python emite las siguientes consultas CREATE TABLE:

CREATE TABLE department (
   id INTEGER NOT NULL,
   name VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE employee (
   id INTEGER NOT NULL,
   name VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE link (
   department_id INTEGER NOT NULL,
   employee_id INTEGER NOT NULL,
   PRIMARY KEY (department_id, employee_id),
   FOREIGN KEY(department_id) REFERENCES department (id),
   FOREIGN KEY(employee_id) REFERENCES employee (id)
)

Podemos verificar esto abriendo mycollege.db usando SQLiteStudio como se muestra en las capturas de pantalla que se muestran a continuación:

A continuación, creamos tres objetos de la clase Departamento y tres objetos de la clase Empleado como se muestra a continuación:

d1 = Department(name = "Accounts")
d2 = Department(name = "Sales")
d3 = Department(name = "Marketing")

e1 = Employee(name = "John")
e2 = Employee(name = "Tony")
e3 = Employee(name = "Graham")

Cada tabla tiene un atributo de colección que tiene el método append (). Podemos agregar objetos Empleados a la colección Empleados del objeto Departamento. De manera similar, podemos agregar objetos Departamento al atributo de colección de departamentos de los objetos Empleado.

e1.departments.append(d1)
e2.departments.append(d3)
d1.employees.append(e3)
d2.employees.append(e2)
d3.employees.append(e1)
e3.departments.append(d2)

Todo lo que tenemos que hacer ahora es configurar un objeto de sesión, agregarle todos los objetos y confirmar los cambios como se muestra a continuación:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(e1)
session.add(e2)
session.add(d1)
session.add(d2)
session.add(d3)
session.add(e3)
session.commit()

Las siguientes declaraciones SQL se emitirán en la consola de Python:

INSERT INTO department (name) VALUES (?) ('Accounts',)
INSERT INTO department (name) VALUES (?) ('Sales',)
INSERT INTO department (name) VALUES (?) ('Marketing',)
INSERT INTO employee (name) VALUES (?) ('John',)
INSERT INTO employee (name) VALUES (?) ('Graham',)
INSERT INTO employee (name) VALUES (?) ('Tony',)
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 2), (3, 1), (2, 3))
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 1), (2, 2), (3, 3))

Para verificar el efecto de las operaciones anteriores, use SQLiteStudio y vea los datos en las tablas de departamentos, empleados y enlaces -

Para mostrar los datos, ejecute la siguiente declaración de consulta:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for x in session.query( Department, Employee).filter(Link.department_id == Department.id, 
   Link.employee_id == Employee.id).order_by(Link.department_id).all():
   print ("Department: {} Name: {}".format(x.Department.name, x.Employee.name))

Según los datos rellenados en nuestro ejemplo, la salida se mostrará como se muestra a continuación:

Department: Accounts Name: John
Department: Accounts Name: Graham
Department: Sales Name: Graham
Department: Sales Name: Tony
Department: Marketing Name: John
Department: Marketing Name: Tony

SQLAlchemy utiliza un sistema de dialectos para comunicarse con varios tipos de bases de datos. Cada base de datos tiene un contenedor DBAPI correspondiente. Todos los dialectos requieren que esté instalado un controlador DBAPI apropiado.

Los siguientes dialectos están incluidos en la API de SQLAlchemy:

• Firebird
• Microsoft SQL Server
• MySQL
• Oracle
• PostgreSQL
• SQL
• Sybase

Un objeto Engine basado en una URL es producido por la función create_engine (). Estas URL pueden incluir nombre de usuario, contraseña, nombre de host y nombre de la base de datos. Puede haber argumentos de palabras clave opcionales para una configuración adicional. En algunos casos, se acepta una ruta de archivo y, en otros, un "nombre de fuente de datos" reemplaza las partes "host" y "base de datos". La forma típica de una URL de base de datos es la siguiente:

dialect+driver://username:password@host:port/database

PostgreSQL

El dialecto de PostgreSQL usa psycopg2como DBAPI predeterminado. pg8000 también está disponible como un sustituto de Python puro, como se muestra a continuación:

# default
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')

# psycopg2
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')

# pg8000
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')

MySQL

El dialecto MySQL usa mysql-pythoncomo DBAPI predeterminado. Hay muchas DBAPI de MySQL disponibles, como MySQL-connector-python de la siguiente manera:

# default
engine = create_engine('mysql://scott:tiger@localhost/foo')

# mysql-python
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

# MySQL-connector-python
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

Oráculo

El dialecto de Oracle usa cx_oracle como DBAPI predeterminado de la siguiente manera:

engine = create_engine('oracle://scott:[email protected]:1521/sidname')
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')

Microsoft SQL Server

El dialecto de SQL Server utiliza pyodbccomo DBAPI predeterminado. pymssql también está disponible.

# pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

# pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

SQLite

SQLite se conecta a bases de datos basadas en archivos, utilizando el módulo integrado de Python sqlite3por defecto. Como SQLite se conecta a archivos locales, el formato de URL es ligeramente diferente. La parte de "archivo" de la URL es el nombre de archivo de la base de datos. Para una ruta de archivo relativa, esto requiere tres barras, como se muestra a continuación:

engine = create_engine('sqlite:///foo.db')

Y para una ruta de archivo absoluta, las tres barras son seguidas por la ruta absoluta como se indica a continuación:

engine = create_engine('sqlite:///C:\\path\\to\\foo.db')

Para usar una base de datos SQLite: memory:, especifique una URL vacía como se indica a continuación:

engine = create_engine('sqlite://')

Conclusión

En la primera parte de este tutorial, hemos aprendido cómo usar el lenguaje de expresión para ejecutar declaraciones SQL. El lenguaje de expresión incorpora construcciones SQL en código Python. En la segunda parte, hemos discutido la capacidad de mapeo de relaciones de objetos de SQLAlchemy. La API ORM mapea las tablas SQL con las clases de Python.