SQLAlchemy - Guida rapida

SQLAlchemy è un popolare toolkit SQL e Object Relational Mapper. È scritto in formatoPythone offre piena potenza e flessibilità di SQL a uno sviluppatore di applicazioni. È unopen source e cross-platform software rilasciato con licenza MIT.

SQLAlchemy è famoso per il suo mapper relazionale a oggetti (ORM), utilizzando il quale le classi possono essere mappate sul database, consentendo così al modello a oggetti e allo schema del database di svilupparsi in modo pulito e disaccoppiato dall'inizio.

Poiché le dimensioni e le prestazioni dei database SQL iniziano a essere importanti, si comportano meno come raccolte di oggetti. D'altra parte, poiché l'astrazione nelle raccolte di oggetti inizia a essere importante, si comportano meno come tabelle e righe. SQLAlchemy mira a soddisfare entrambi questi principi.

Per questo motivo ha adottato il data mapper pattern (like Hibernate) rather than the active record pattern used by a number of other ORMs. I database e SQL verranno visualizzati in una prospettiva diversa utilizzando SQLAlchemy.

Michael Bayer è l'autore originale di SQLAlchemy. La sua versione iniziale è stata rilasciata nel febbraio 2006. L'ultima versione è numerata come 1.2.7, rilasciata solo nell'aprile 2018.

Cos'è ORM?

ORM (Object Relational Mapping) è una tecnica di programmazione per convertire i dati tra sistemi di tipi incompatibili in linguaggi di programmazione orientati agli oggetti. Di solito, il sistema di tipi utilizzato in un linguaggio OO (Object Oriented) come Python contiene tipi non scalari. Questi non possono essere espressi come tipi primitivi come numeri interi e stringhe. Quindi, il programmatore OO deve convertire gli oggetti in dati scalari per interagire con il database di backend. Tuttavia, i tipi di dati nella maggior parte dei prodotti di database come Oracle, MySQL e così via sono primari.

In un sistema ORM, ogni classe viene mappata a una tabella nel database sottostante. Invece di scrivere tu stesso il noioso codice di interfacciamento del database, un ORM si prende cura di questi problemi per te mentre tu puoi concentrarti sulla programmazione delle logiche del sistema.

SQLAlchemy: configurazione dell'ambiente

Parliamo della configurazione ambientale richiesta per utilizzare SQLAlchemy.

Qualsiasi versione di Python superiore alla 2.7 è necessaria per installare SQLAlchemy. Il modo più semplice per installare è utilizzare Python Package Manager,pip. Questa utility è inclusa nella distribuzione standard di Python.

pip install sqlalchemy

Usando il comando sopra, possiamo scaricare il file latest released versiondi SQLAlchemy da python.org e installalo sul tuo sistema.

In caso di distribuzione anaconda di Python, SQLAlchemy può essere installato da conda terminal utilizzando il comando seguente -

conda install -c anaconda sqlalchemy

È anche possibile installare SQLAlchemy dal codice sorgente sottostante -

python setup.py install

SQLAlchemy è progettato per funzionare con un'implementazione DBAPI creata per un database particolare. Utilizza il sistema dialettale per comunicare con vari tipi di implementazioni e database DBAPI. Tutti i dialetti richiedono che sia installato un driver DBAPI appropriato.

I seguenti sono i dialetti inclusi:

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

Per verificare se SQLAlchemy è installato correttamente e per conoscere la sua versione, inserisci il seguente comando nel prompt di Python:

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

Il nucleo di SQLAlchemy include SQL rendering engine, DBAPI integration, transaction integration, e schema description services. SQLAlchemy core utilizza SQL Expression Language che fornisce un fileschema-centric usage paradigma mentre SQLAlchemy ORM è un domain-centric mode of usage.

SQL Expression Language presenta un sistema per rappresentare strutture ed espressioni di database relazionali utilizzando costrutti Python. Presenta un sistema per rappresentare i costrutti primitivi del database relazionale direttamente senza opinione, che è in contrasto con ORM che presenta un modello di utilizzo di alto livello e astratto, che a sua volta è un esempio di utilizzo applicato del linguaggio di espressione.

Expression Language è uno dei componenti principali di SQLAlchemy. Consente al programmatore di specificare istruzioni SQL nel codice Python e di utilizzarlo direttamente in query più complesse. Il linguaggio di espressione è indipendente dal backend e copre in modo completo ogni aspetto dell'SQL grezzo. È più vicino all'SQL grezzo rispetto a qualsiasi altro componente in SQLAlchemy.

Expression Language rappresenta direttamente i costrutti primitivi del database relazionale. Poiché l'ORM è basato sul linguaggio Expression, una tipica applicazione di database Python potrebbe avere un utilizzo sovrapposto di entrambi. L'applicazione può utilizzare solo il linguaggio delle espressioni, sebbene debba definire il proprio sistema di traduzione dei concetti dell'applicazione in singole query di database.

Le dichiarazioni del linguaggio Expression verranno tradotte in query SQL non elaborate corrispondenti dal motore SQLAlchemy. Ora impareremo come creare il motore ed eseguire varie query SQL con il suo aiuto.

Nel capitolo precedente, abbiamo discusso del linguaggio delle espressioni in SQLAlchemy. Ora procediamo verso i passaggi coinvolti nella connessione a un database.

La classe del motore collega a Pool and Dialect together per fornire una fonte di database connectivity and behavior. Un oggetto della classe Engine viene istanziato utilizzando ilcreate_engine() funzione.

La funzione create_engine () accetta il database come un argomento. Il database non è necessario per essere definito da nessuna parte. Il modulo di chiamata standard deve inviare l'URL come primo argomento posizionale, di solito una stringa che indica il dialetto del database e gli argomenti di connessione. Utilizzando il codice fornito di seguito, possiamo creare un database.

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

Per un MySQL database, usa il comando seguente -

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

Per menzionare specificamente DB-API da utilizzare per la connessione, il URL string assume la forma come segue:

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

Ad esempio, se stai usando PyMySQL driver with MySQL, usa il seguente comando:

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

Il echo flagè una scorciatoia per impostare la registrazione di SQLAlchemy, che viene eseguita tramite il modulo di registrazione standard di Python. Nei capitoli successivi impareremo tutti gli SQL generati. Per nascondere l'output dettagliato, impostare l'attributo echo suNone. Altri argomenti per la funzione create_engine () possono essere specifici del dialetto.

La funzione create_engine () restituisce un file Engine object. Alcuni metodi importanti della classe Engine sono:

Sr.No.	Metodo e descrizione
1	connect() Restituisce l'oggetto di connessione
2	execute() Esegue un costrutto di istruzione SQL
3	begin() Restituisce un gestore di contesto che consegna una connessione con una transazione stabilita. In caso di operazione riuscita, la Transazione viene confermata, altrimenti viene annullata
4	dispose() Elimina il pool di connessioni utilizzato dall'Engine
5	driver() Nome driver del dialetto utilizzato dal motore
6	table_names() Restituisce un elenco di tutti i nomi di tabella disponibili nel database
7	transaction() Esegue la funzione data entro un limite di transazione

Vediamo ora come utilizzare la funzione di creazione tabella.

Il linguaggio delle espressioni SQL costruisce le sue espressioni in base alle colonne della tabella. L'oggetto Colonna SQLAlchemy rappresenta un filecolumn in una tabella di database che è a sua volta rappresentata da un file Tableobject. I metadati contengono definizioni di tabelle e oggetti associati come indice, vista, trigger, ecc.

Quindi un oggetto della classe MetaData da SQLAlchemy Metadata è una raccolta di oggetti Table e dei loro costrutti di schema associati. Contiene una raccolta di oggetti Table e un'associazione facoltativa a un motore o una connessione.

from sqlalchemy import MetaData
meta = MetaData()

Il costruttore della classe MetaData può avere parametri di bind e schema che sono per impostazione predefinita None.

Successivamente, definiamo le nostre tabelle tutte all'interno del catalogo di metadati sopra, utilizzando the Table construct, che assomiglia alla normale istruzione SQL CREATE TABLE.

Un oggetto della classe Table rappresenta la tabella corrispondente in un database. Il costruttore accetta i seguenti parametri:

Nome	Nome della tabella
Metadati	Oggetto MetaData che conterrà questa tabella
Colonna (e)	Uno o più oggetti della classe di colonna

L'oggetto Colonna rappresenta un file column in un database table. Constructor prende nome, tipo e altri parametri come primary_key, autoincrement e altri vincoli.

SQLAlchemy abbina i dati Python ai migliori tipi di dati di colonna generici possibili definiti in esso. Alcuni dei tipi di dati generici sono:

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

Per creare un file students table nel database del college, utilizza il seguente frammento:

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 funzione create_all () utilizza l'oggetto motore per creare tutti gli oggetti tabella definiti e memorizza le informazioni nei metadati.

meta.create_all(engine)

Di seguito viene fornito il codice completo che creerà un database SQLite college.db con una tabella studenti al suo interno.

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)

Perché l'attributo echo della funzione create_engine () è impostato su True, la console visualizzerà la query SQL effettiva per la creazione della tabella come segue:

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

Il college.db verrà creato nella directory di lavoro corrente. Per verificare se la tabella degli studenti è stata creata, è possibile aprire il database utilizzando qualsiasi strumento della GUI di SQLite comeSQLiteStudio.

L'immagine sotto mostra la tabella degli studenti creata nel database -

In questo capitolo ci concentreremo brevemente sulle espressioni SQL e sulle loro funzioni.

Le espressioni SQL vengono costruite utilizzando metodi corrispondenti relativi all'oggetto tabella di destinazione. Ad esempio, l'istruzione INSERT viene creata eseguendo il metodo insert () come segue:

ins = students.insert()

Il risultato del metodo precedente è un oggetto di inserimento che può essere verificato utilizzando str()funzione. Il codice seguente inserisce dettagli come ID studente, nome, cognome.

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

È possibile inserire un valore in un campo specifico tramite values()metodo per inserire l'oggetto. Il codice per lo stesso è dato di seguito -

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

L'SQL visualizzato sulla console Python non mostra il valore effettivo ("Karan" in questo caso). SQLALchemy genera invece un parametro bind visibile nella forma compilata dell'istruzione.

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

Allo stesso modo, metodi come update(), delete() e select()creare rispettivamente le espressioni UPDATE, DELETE e SELECT. Li impareremo nei capitoli successivi.

Nel capitolo precedente, abbiamo imparato le espressioni SQL. In questo capitolo esamineremo l'esecuzione di queste espressioni.

Per eseguire le espressioni SQL risultanti, dobbiamo obtain a connection object representing an actively checked out DBAPI connection resource e poi feed the expression object come mostrato nel codice sottostante.

conn = engine.connect()

Il seguente oggetto insert () può essere utilizzato per il metodo execute ():

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

La console mostra il risultato dell'esecuzione dell'espressione SQL come di seguito:

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

Di seguito è riportato l'intero frammento che mostra l'esecuzione della query INSERT utilizzando la tecnica principale di 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)

Il risultato può essere verificato aprendo il database utilizzando SQLite Studio come mostrato nello screenshot qui sotto -

La variabile di risultato è nota come ResultProxy object. È analogo all'oggetto cursore DBAPI. Possiamo acquisire informazioni sui valori della chiave primaria che sono stati generati dalla nostra dichiarazione utilizzandoResultProxy.inserted_primary_key come mostrato di seguito -

result.inserted_primary_key
[1]

Per emettere molti inserimenti utilizzando il metodo execute many () di DBAPI, possiamo inviare un elenco di dizionari ciascuno contenente un set distinto di parametri da inserire.

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

Ciò si riflette nella visualizzazione dati della tabella come mostrato nella figura seguente:

In questo capitolo, discuteremo del concetto di selezione di righe nell'oggetto tabella.

Il metodo select () dell'oggetto table ci consente di farlo construct SELECT expression.

s = students.select()

L'oggetto selezionato si traduce in SELECT query by str(s) function come mostrato di seguito -

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

Possiamo usare questo oggetto di selezione come parametro per eseguire () il metodo dell'oggetto di connessione come mostrato nel codice seguente -

result = conn.execute(s)

Quando viene eseguita l'istruzione di cui sopra, la shell Python echeggia dopo un'espressione SQL equivalente:

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

La variabile risultante è un equivalente del cursore in DBAPI. Ora possiamo recuperare i record usandofetchone() method.

row = result.fetchone()

Tutte le righe selezionate nella tabella possono essere stampate da a for loop come indicato di seguito -

for row in result:
   print (row)

Di seguito è mostrato il codice completo per stampare tutte le righe dalla tabella degli studenti:

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)

L'output mostrato nella shell Python è il seguente:

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

La clausola WHERE della query SELECT può essere applicata utilizzando Select.where(). Ad esempio, se vogliamo visualizzare righe con id> 2

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

for row in result:
   print (row)

Qui c attribute is an alias for column. Il seguente output verrà visualizzato sulla shell:

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

Qui, dobbiamo notare che select object può anche essere ottenuto dalla funzione select () nel modulo sqlalchemy.sql. La funzione select () richiede l'oggetto table come argomento.

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

SQLAlchemy ti consente di usare solo stringhe, per quei casi in cui l'SQL è già noto e non c'è una forte necessità per l'istruzione di supportare le funzionalità dinamiche. Il costrutto text () viene utilizzato per comporre un'istruzione testuale che viene passata al database per lo più invariata.

Costruisce un nuovo TextClause, che rappresenta una stringa SQL testuale direttamente come mostrato nel codice seguente -

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

I vantaggi text() fornisce su una semplice stringa sono -

• supporto indipendente dal backend per i parametri di binding
• opzioni di esecuzione per istruzione
• comportamento di digitazione nella colonna dei risultati

La funzione text () richiede parametri Bound nel formato due punti denominato. Sono coerenti indipendentemente dal backend del database. Per inviare valori per i parametri, li passiamo al metodo execute () come argomenti aggiuntivi.

L'esempio seguente utilizza parametri associati in SQL testuale:

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 funzione text () costruisce l'espressione SQL come segue:

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

I valori di x = "A" e y = "L" vengono passati come parametri. Il risultato è un elenco di righe con nomi compresi tra "A" e "L" -

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

Il costrutto text () supporta valori associati prestabiliti utilizzando il metodo TextClause.bindparams (). I parametri possono anche essere digitati esplicitamente come segue:

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()

Puoi anche usare and_() funzione per combinare più condizioni nella clausola WHERE creata con l'aiuto della funzione 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()

Il codice precedente recupera le righe con nomi compresi tra "A" e "L" con ID maggiore di 2. L'output del codice è fornito di seguito:

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

L'alias in SQL corrisponde a una versione "rinominata" di una tabella o di un'istruzione SELECT, che si verifica ogni volta che si pronuncia "SELECT * FROM table1 AS a". L'AS crea un nuovo nome per la tabella. Gli alias consentono di fare riferimento a qualsiasi tabella o sottoquery con un nome univoco.

Nel caso di una tabella, ciò consente di denominare più volte la stessa tabella nella clausola FROM. Fornisce un nome padre per le colonne rappresentate dall'istruzione, consentendo loro di fare riferimento in relazione a questo nome.

In SQLAlchemy, qualsiasi tabella, costrutto select () o altro oggetto selezionabile può essere trasformato in un alias utilizzando il From Clause.alias()metodo, che produce un costrutto Alias. La funzione alias () nel modulo sqlalchemy.sql rappresenta un alias, tipicamente applicato a qualsiasi tabella o sotto-selezione all'interno di un'istruzione SQL utilizzando la parola chiave AS.

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

Questo alias può ora essere utilizzato nel costrutto select () per fare riferimento alla tabella degli studenti -

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

Questo si traduce in un'espressione SQL come segue:

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

Ora possiamo eseguire questa query SQL con il metodo execute () dell'oggetto connessione. Il codice completo è il seguente:

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

Quando viene eseguita la riga di codice sopra, genera il seguente output:

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

Il update() Il metodo sull'oggetto tabella di destinazione costruisce un'espressione SQL UPDATE equivalente.

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

Il values()Il metodo sull'oggetto di aggiornamento risultante viene utilizzato per specificare le condizioni SET di UPDATE. Se lasciato come Nessuno, le condizioni SET sono determinate da quei parametri passati all'istruzione durante l'esecuzione e / o la compilazione dell'istruzione.

La clausola where è un'espressione facoltativa che descrive la condizione WHERE dell'istruzione UPDATE.

Il seguente frammento di codice cambia il valore della colonna "lastname" da "Khanna" a "Kapoor" nella tabella degli studenti -

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

L'oggetto stmt è un oggetto di aggiornamento che si traduce in -

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

Il parametro associato lastname_1 verrà sostituito quando execute()viene richiamato il metodo. Il codice di aggiornamento completo è fornito di seguito:

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()

Il codice sopra mostra il seguente output con la seconda riga che mostra l'effetto dell'operazione di aggiornamento come nello screenshot fornito -

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

Si noti che funzionalità simili possono essere ottenute anche utilizzando update() funzione nel modulo sqlalchemy.sql.expression come mostrato di seguito -

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

Nel capitolo precedente, abbiamo capito cosa un Updatel'espressione fa. La prossima espressione che impareremo èDelete.

L'operazione di eliminazione può essere ottenuta eseguendo il metodo delete () sull'oggetto tabella di destinazione come indicato nella seguente istruzione:

stmt = students.delete()

Nel caso della tabella degli studenti, la riga di codice precedente costruisce un'espressione SQL come segue:

'DELETE FROM students'

Tuttavia, questo eliminerà tutte le righe nella tabella degli studenti. Di solito la query DELETE è associata a un'espressione logica specificata dalla clausola WHERE. La seguente dichiarazione mostra dove parametro -

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

The resultant SQL expression will have a bound parameter which will be substituted at runtime when the statement is executed.

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

Following code example will delete those rows from students table having lastname as ‘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()

To verify the result, refresh the data view of students table in SQLiteStudio.

One of the important features of RDBMS is establishing relation between tables. SQL operations like SELECT, UPDATE and DELETE can be performed on related tables. This section describes these operations using SQLAlchemy.

For this purpose, two tables are created in our SQLite database (college.db). The students table has the same structure as given in the previous section; whereas the addresses table has st_id column which is mapped to id column in students table using foreign key constraint.

The following code will create two tables in 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)

Above code will translate to CREATE TABLE queries for students and addresses table as below −

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)
)

The following screenshots present the above code very clearly −

These tables are populated with data by executing insert() method of table objects. To insert 5 rows in students table, you can use the code given below −

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 are added in addresses table with the help of the following code −

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]'},
])

Note that the st_id column in addresses table refers to id column in students table. We can now use this relation to fetch data from both the tables. We want to fetch name and lastname from students table corresponding to st_id in the addresses table.

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)

The select objects will effectively translate into following SQL expression joining two tables on common relation −

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

This will produce output extracting corresponding data from both tables as follows −

(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]')

In the previous chapter, we have discussed about how to use multiple tables. So we proceed a step further and learn multiple table updates in this chapter.

Using SQLAlchemy’s table object, more than one table can be specified in WHERE clause of update() method. The PostgreSQL and Microsoft SQL Server support UPDATE statements that refer to multiple tables. This implements “UPDATE FROM” syntax, which updates one table at a time. However, additional tables can be referenced in an additional “FROM” clause in the WHERE clause directly. The following lines of codes explain the concept of multiple table updates clearly.

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

The update object is equivalent to the following UPDATE query −

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

As far as MySQL dialect is concerned, multiple tables can be embedded into a single UPDATE statement separated by a comma as given below −

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

The following code depicts the resulting UPDATE query −

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

SQLite dialect however doesn’t support multiple-table criteria within UPDATE and shows following error −

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

The UPDATE query of raw SQL has SET clause. It is rendered by the update() construct using the column ordering given in the originating Table object. Therefore, a particular UPDATE statement with particular columns will be rendered the same each time. Since the parameters themselves are passed to the Update.values() method as Python dictionary keys, there is no other fixed ordering available.

In some cases, the order of parameters rendered in the SET clause are significant. In MySQL, providing updates to column values is based on that of other column values.

Following statement’s result −

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

will have a different result than −

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

SET clause in MySQL is evaluated on a per-value basis and not on per-row basis. For this purpose, the preserve_parameter_order is used. Python list of 2-tuples is given as argument to the Update.values() method −

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

The List object is similar to dictionary except that it is ordered. This ensures that the “y” column’s SET clause will render first, then the “x” column’s SET clause.

In this chapter, we will look into the Multiple Table Deletes expression which is similar to using Multiple Table Updates function.

More than one table can be referred in WHERE clause of DELETE statement in many DBMS dialects. For PG and MySQL, “DELETE USING” syntax is used; and for SQL Server, using “DELETE FROM” expression refers to more than one table. The SQLAlchemy delete() construct supports both of these modes implicitly, by specifying multiple tables in the WHERE clause as follows −

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

On a PostgreSQL backend, the resulting SQL from the above statement would render as −

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

If this method is used with a database that doesn’t support this behaviour, the compiler will raise NotImplementedError.

In this chapter, we will learn how to use Joins in SQLAlchemy.

Effect of joining is achieved by just placing two tables in either the columns clause or the where clause of the select() construct. Now we use the join() and outerjoin() methods.

The join() method returns a join object from one table object to another.

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

The functions of the parameters mentioned in the above code are as follows −

• right − the right side of the join; this is any Table object

• onclause − a SQL expression representing the ON clause of the join. If left at None, it attempts to join the two tables based on a foreign key relationship

• isouter − if True, renders a LEFT OUTER JOIN, instead of JOIN

• full − if True, renders a FULL OUTER JOIN, instead of LEFT OUTER JOIN

For example, following use of join() method will automatically result in join based on the foreign key.

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

This is equivalent to following SQL expression −

students JOIN addresses ON students.id = addresses.st_id

You can explicitly mention joining criteria as follows −

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

If we now build the below select construct using this join as −

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

This will result in following SQL expression −

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

If this statement is executed using the connection representing engine, data belonging to selected columns will be displayed. The complete code is as follows −

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()

The following is the output of the above code −

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

Conjunctions are functions in SQLAlchemy module that implement relational operators used in WHERE clause of SQL expressions. The operators AND, OR, NOT, etc., are used to form a compound expression combining two individual logical expressions. A simple example of using AND in SELECT statement is as follows −

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

SQLAlchemy functions and_(), or_() and not_() respectively implement AND, OR and NOT operators.

and_() function

It produces a conjunction of expressions joined by AND. An example is given below for better understanding −

from sqlalchemy import and_

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

This translates to −

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

To use and_() in a select() construct on a students table, use the following line of code −

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

SELECT statement of the following nature will be constructed −

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

The complete code that displays output of the above SELECT query is as follows −

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())

Following row will be selected assuming that students table is populated with data used in previous example −

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

or_() function

It produces conjunction of expressions joined by OR. We shall replace the stmt object in the above example with the following one using or_()

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

Which will be effectively equivalent to following SELECT query −

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

Once you make the substitution and run the above code, the result will be two rows falling in the OR condition −

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

asc() function

It produces an ascending ORDER BY clause. The function takes the column to apply the function as a parameter.

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

The statement implements following SQL expression −

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

Following code lists out all records in students table in ascending order of name column −

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)

Above code produces following output −

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

desc() function

Similarly desc() function produces descending ORDER BY clause as follows −

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

The equivalent SQL expression is −

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

And the output for the above lines of code is −

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

between() function

It produces a BETWEEN predicate clause. This is generally used to validate if value of a certain column falls between a range. For example, following code selects rows for which id column is between 2 and 4 −

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

The resulting SQL expression resembles −

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

and the result is as follows −

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

Some of the important functions used in SQLAlchemy are discussed in this chapter.

Standard SQL has recommended many functions which are implemented by most dialects. They return a single value based on the arguments passed to it. Some SQL functions take columns as arguments whereas some are generic. Thefunc keyword in SQLAlchemy API is used to generate these functions.

In SQL, now() is a generic function. Following statements renders the now() function using func −

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

Sample result of above code may be as shown below −

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

On the other hand, count() function which returns number of rows selected from a table, is rendered by following usage of func −

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

From the above code, count of number of rows in students table will be fetched.

Some built-in SQL functions are demonstrated using Employee table with following data −

ID	Nome	Marks
1	Kamal	56
2	Fernandez	85
3	Sunil	62
4	Bhaskar	76

La funzione max () viene implementata seguendo l'uso di func da SQLAlchemy che risulterà in 85, il punteggio massimo totale ottenuto -

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

Allo stesso modo, la funzione min () che restituirà 56, segni minimi, sarà resa dal codice seguente:

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

Pertanto, la funzione AVG () può essere implementata anche utilizzando il codice seguente:

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())

Nell'ultimo capitolo, abbiamo imparato a conoscere varie funzioni come max (), min (), count (), ecc., Qui impareremo le operazioni sugli insiemi e il loro utilizzo.

Le operazioni di impostazione come UNION e INTERSECT sono supportate dall'SQL standard e dalla maggior parte del suo dialetto. SQLAlchemy li implementa con l'aiuto delle seguenti funzioni:

unione()

Combinando i risultati di due o più istruzioni SELECT, UNION elimina i duplicati dal gruppo di risultati. Il numero di colonne e il tipo di dati devono essere gli stessi in entrambe le tabelle.

La funzione union () restituisce un oggetto CompoundSelect da più tabelle. L'esempio seguente ne dimostra l'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()

Il costrutto di unione si traduce nella seguente espressione 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 ?

Dalla nostra tabella degli indirizzi, le seguenti righe rappresentano l'operazione di unione -

[
   (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 ()

L'operazione UNION ALL non può rimuovere i duplicati e non può ordinare i dati nel gruppo di risultati. Ad esempio, nella query precedente, UNION è sostituito da UNION ALL per vedere l'effetto.

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

L'espressione SQL corrispondente è la seguente:

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 ?

tranne_ ()

L'SQL EXCEPTla clausola / operatore viene utilizzata per combinare due istruzioni SELECT e restituire righe dalla prima istruzione SELECT che non vengono restituite dalla seconda istruzione SELECT. La funzione tranne_ () genera un'espressione SELECT con la clausola EXCEPT.

Nell'esempio seguente, la funzione tranne_ () restituisce solo i record dalla tabella degli indirizzi che hanno "gmail.com" nel campo email_add ma esclude quelli che hanno "Pune" come parte del campo postale_add.

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

Il risultato del codice precedente è la seguente espressione 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 ?

Supponendo che la tabella degli indirizzi contenga i dati utilizzati negli esempi precedenti, verrà visualizzato il seguente output:

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

intersecare ()

Utilizzando l'operatore INTERSECT, SQL visualizza righe comuni da entrambe le istruzioni SELECT. La funzione intersect () implementa questo comportamento.

Negli esempi seguenti, due costrutti SELECT sono parametri per la funzione intersect (). Uno restituisce le righe che contengono "gmail.com" come parte della colonna email_add e l'altro restituisce le righe che contengono "Pune" come parte della colonna Postal_add. Il risultato sarà righe comuni da entrambi i set di risultati.

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

In effetti, questo è equivalente alla seguente istruzione 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 ?

I due parametri associati "% gmail.com" e "% Pune" generano una singola riga dai dati originali nella tabella degli indirizzi come mostrato di seguito:

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

L'obiettivo principale dell'API Object Relational Mapper di SQLAlchemy è facilitare l'associazione di classi Python definite dall'utente con tabelle di database e oggetti di tali classi con righe nelle tabelle corrispondenti. Le modifiche negli stati degli oggetti e delle righe vengono sincronizzate tra loro in modo sincrono. SQLAlchemy consente di esprimere query di database in termini di classi definite dall'utente e le loro relazioni definite.

L'ORM è costruito sopra il linguaggio di espressione SQL. È un modello di utilizzo di alto livello e astratto. In effetti, ORM è un uso applicato del linguaggio delle espressioni.

Sebbene un'applicazione di successo possa essere costruita utilizzando esclusivamente Object Relational Mapper, a volte un'applicazione costruita con ORM può utilizzare Expression Language direttamente dove sono richieste specifiche interazioni di database.

Dichiara mappatura

Prima di tutto, viene chiamata la funzione create_engine () per impostare un oggetto motore che viene successivamente utilizzato per eseguire operazioni SQL. La funzione ha due argomenti, uno è il nome del database e l'altro è un parametro echo quando impostato su True genererà il registro delle attività. Se non esiste, verrà creato il database. Nell'esempio seguente, viene creato un database SQLite.

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

Il motore stabilisce una connessione DBAPI reale al database quando viene chiamato un metodo come Engine.execute () o Engine.connect (). Viene quindi utilizzato per emettere SQLORM che non utilizza direttamente l'Engine; invece, è usato dietro le quinte dall'ORM.

In caso di ORM, il processo di configurazione inizia descrivendo le tabelle del database e quindi definendo le classi che verranno mappate su quelle tabelle. In SQLAlchemy, queste due attività vengono eseguite insieme. Questo viene fatto utilizzando il sistema dichiarativo; le classi create includono direttive per descrivere la tabella di database effettiva a cui sono mappate.

Una classe base memorizza un catlog di classi e tabelle mappate nel sistema dichiarativo. Questa è chiamata come classe base dichiarativa. Di solito ci sarà solo un'istanza di questa base in un modulo importato comunemente. La funzione dichiarative_base () viene utilizzata per creare la classe base. Questa funzione è definita nel modulo sqlalchemy.ext.declarative.

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

Una volta dichiarata la classe di base, è possibile definire un numero qualsiasi di classi mappate in termini di essa. Il codice seguente definisce la classe di un cliente. Contiene la tabella su cui eseguire il mapping e i nomi e i tipi di dati delle colonne.

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

Una classe in Dichiarativo deve avere un'estensione __tablename__ attributo e almeno uno Columnche fa parte di una chiave primaria. Dichiarativo sostituisce tutti iColumn oggetti con speciali funzioni di accesso Python note come descriptors. Questo processo è noto come strumentazione che fornisce i mezzi per fare riferimento alla tabella in un contesto SQL e consente la persistenza e il caricamento dei valori delle colonne dal database.

Questa classe mappata come una normale classe Python ha attributi e metodi secondo il requisito.

Le informazioni sulla classe nel sistema dichiarativo vengono chiamate metadati di tabella. SQLAlchemy utilizza l'oggetto Table per rappresentare queste informazioni per una tabella specifica creata da Dichiarative. L'oggetto Table viene creato secondo le specifiche ed è associato alla classe costruendo un oggetto Mapper. Questo oggetto mappatore non viene utilizzato direttamente ma viene utilizzato internamente come interfaccia tra la classe mappata e la tabella.

Ogni oggetto Table è un membro di una raccolta più ampia nota come MetaData e questo oggetto è disponibile utilizzando l'estensione .metadataattributo della classe base dichiarativa. IlMetaData.create_all()metodo è passare nel nostro motore come fonte di connettività del database. Per tutte le tabelle che non sono state ancora create, invia le istruzioni CREATE TABLE al database.

Base.metadata.create_all(engine)

Lo script completo per creare un database e una tabella e per mappare la classe Python è fornito di seguito:

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)

Quando viene eseguita, la console Python echeggerà dopo l'esecuzione dell'espressione SQL:

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

Se apriamo Sales.db utilizzando lo strumento grafico SQLiteStudio, mostra al suo interno la tabella dei clienti con la struttura sopra menzionata.

Per interagire con il database, dobbiamo ottenere il suo handle. Un oggetto sessione è l'handle del database. La classe di sessione viene definita utilizzando sessionmaker (), un metodo di factory di sessione configurabile che è associato all'oggetto motore creato in precedenza.

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

L'oggetto sessione viene quindi impostato utilizzando il suo costruttore predefinito come segue:

session = Session()

Alcuni dei metodi frequentemente richiesti per la classe di sessione sono elencati di seguito:

Sr.No.	Metodo e descrizione
1	begin() inizia una transazione in questa sessione
2	add() inserisce un oggetto nella sessione. Il suo stato viene mantenuto nel database alla successiva operazione di svuotamento
3	add_all() aggiunge una raccolta di oggetti alla sessione
4	commit() svuota tutti gli elementi e qualsiasi transazione in corso
5	delete() contrassegna una transazione come eliminata
6	execute() esegue un'espressione SQL
7	expire() contrassegna gli attributi di un'istanza come non aggiornati
8	flush() scarica tutte le modifiche agli oggetti nel database
9	invalidate() chiude la sessione utilizzando l'annullamento della connessione
10	rollback() ripristina la transazione corrente in corso
11	close() Chiude la sessione corrente cancellando tutti gli elementi e terminando qualsiasi transazione in corso

Nei capitoli precedenti di SQLAlchemy ORM, abbiamo appreso come dichiarare la mappatura e creare sessioni. In questo capitolo impareremo come aggiungere oggetti alla tabella.

Abbiamo dichiarato la classe Customer che è stata mappata alla tabella dei clienti. Dobbiamo dichiarare un oggetto di questa classe e aggiungerlo in modo persistente alla tabella con il metodo add () dell'oggetto sessione.

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

Notare che questa transazione è in sospeso fino a quando la stessa non viene cancellata utilizzando il metodo commit ().

session.commit()

Di seguito è riportato lo script completo per aggiungere un record nella tabella dei clienti:

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()

Per aggiungere più record, possiamo usare add_all() metodo della classe di sessione.

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 visualizzazione tabella di SQLiteStudio mostra che i record vengono aggiunti in modo persistente nella tabella dei clienti. L'immagine seguente mostra il risultato:

Tutte le istruzioni SELECT generate da SQLAlchemy ORM sono costruite dall'oggetto Query. Fornisce un'interfaccia generativa, quindi le chiamate successive restituiscono un nuovo oggetto Query, una copia del primo con criteri e opzioni aggiuntivi ad esso associati.

Gli oggetti query vengono inizialmente generati utilizzando il metodo query () della sessione come segue:

q = session.query(mapped class)

La seguente dichiarazione è anche equivalente alla dichiarazione di cui sopra -

q = Query(mappedClass, session)

L'oggetto query ha il metodo all () che restituisce un gruppo di risultati sotto forma di elenco di oggetti. Se lo eseguiamo sulla tabella dei nostri clienti -

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

Questa istruzione è effettivamente equivalente alla seguente espressione SQL:

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

L'oggetto risultato può essere attraversato utilizzando il ciclo For come di seguito per ottenere tutti i record nella tabella dei clienti sottostante. Ecco il codice completo per visualizzare tutti i record nella tabella Clienti -

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 console Python mostra l'elenco dei record come di seguito:

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]

L'oggetto Query ha anche i seguenti metodi utili:

Sr.No.	Metodo e descrizione
1	add_columns() Aggiunge una o più espressioni di colonna all'elenco delle colonne dei risultati da restituire.
2	add_entity() Aggiunge un'entità mappata all'elenco delle colonne dei risultati da restituire.
3	count() Restituisce un conteggio delle righe che questa query restituirebbe.
4	delete() Esegue una query di eliminazione in blocco. Elimina le righe corrispondenti a questa query dal database.
5	distinct() Applica una clausola DISTINCT alla query e restituisce la query appena risultante.
6	filter() Applica il criterio di filtro dato a una copia di questa query, utilizzando espressioni SQL.
7	first() Restituisce il primo risultato di questa query o Nessuno se il risultato non contiene alcuna riga.
8	get() Restituisce un'istanza basata sull'identificatore di chiave primaria specificato fornendo accesso diretto alla mappa di identità della sessione proprietaria.
9	group_by() Applica uno o più criteri GROUP BY alla query e restituisce la query appena risultante
10	join() Crea un SQL JOIN in base al criterio di questo oggetto Query e si applica in modo generativo, restituendo la Query appena risultante.
11	one() Restituisce esattamente un risultato o solleva un'eccezione.
12	order_by() Applica uno o più criteri ORDER BY alla query e restituisce la query appena risultante.
13	update() Esegue una query di aggiornamento in blocco e aggiorna le righe corrispondenti a questa query nel database.

In questo capitolo vedremo come modificare o aggiornare la tabella con i valori desiderati.

Per modificare i dati di un determinato attributo di qualsiasi oggetto, dobbiamo assegnargli un nuovo valore e confermare le modifiche per rendere la modifica persistente.

Recuperiamo un oggetto dalla tabella il cui identificatore di chiave primaria, nella nostra tabella Clienti con ID = 2. Possiamo usare il metodo di sessione get () come segue:

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

Possiamo visualizzare il contenuto dell'oggetto selezionato con il codice indicato di seguito -

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

Dalla nostra tabella clienti, dovrebbe essere visualizzato il seguente output:

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

Ora dobbiamo aggiornare il campo Indirizzo assegnando un nuovo valore come indicato di seguito -

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

La modifica si rifletterà in modo persistente nel database. Ora recuperiamo l'oggetto corrispondente alla prima riga della tabella utilizzandofirst() method come segue -

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

Questo eseguirà la seguente espressione 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 ?

I parametri vincolati saranno rispettivamente LIMIT = 1 e OFFSET = 0, il che significa che verrà selezionata la prima riga.

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

Ora, l'output per il codice sopra che mostra la prima riga è il seguente:

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

Ora cambia l'attributo del nome e visualizza il contenuto utilizzando il codice seguente:

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

L'output del codice sopra è -

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

Anche se la modifica viene visualizzata, non viene salvata. È possibile mantenere la precedente posizione persistente utilizzandorollback() method con il codice sottostante.

session.rollback()

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

Verrà visualizzato il contenuto originale del primo record.

Per gli aggiornamenti in blocco, utilizzeremo il metodo update () dell'oggetto Query. Proviamo a dare un prefisso, "Mr." da nominare in ogni riga (tranne ID = 2). L'istruzione update () corrispondente è la seguente:

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 dizionario di valori-chiave in cui la chiave è l'attributo da aggiornare e il valore è il nuovo contenuto dell'attributo.

• Attributo synchronize_session che menziona la strategia per aggiornare gli attributi nella sessione. I valori validi sono falsi: per non sincronizzare la sessione, fetch: esegue una query di selezione prima dell'aggiornamento per trovare gli oggetti che corrispondono alla query di aggiornamento; e valuta: valuta i criteri sugli oggetti nella sessione.

Tre righe su 4 nella tabella avranno il prefisso "Mr." Tuttavia, le modifiche non vengono salvate e quindi non si rifletteranno nella visualizzazione tabella di SQLiteStudio. Verrà aggiornato solo quando eseguiremo il commit della sessione.

In questo capitolo, discuteremo come applicare il filtro e anche alcune operazioni di filtro insieme ai loro codici.

Il set di risultati rappresentato dall'oggetto Query può essere soggetto a determinati criteri utilizzando il metodo filter (). L'utilizzo generale del metodo di filtro è il seguente:

session.query(class).filter(criteria)

Nell'esempio seguente, il gruppo di risultati ottenuto dalla query SELEZIONA sulla tabella Clienti viene filtrato in base a una condizione, (ID> 2) -

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

Questa dichiarazione si tradurrà nella seguente espressione 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 > ?

Poiché il parametro associato (?) È dato come 2, verranno visualizzate solo le righe con colonna ID> 2. Il codice completo è dato di seguito -

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)

L'output visualizzato nella console Python è il seguente:

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]

Ora impareremo le operazioni di filtro con i rispettivi codici e output.

Equivale

Il solito operatore utilizzato è == e applica i criteri per verificare l'uguaglianza.

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 invierà la seguente espressione 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 = ?

L'output per il codice precedente è il seguente:

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

Non uguale

L'operatore utilizzato per non è uguale a! = E fornisce criteri non uguale.

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)

L'espressione SQL risultante è -

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 != ?

L'output per le righe di codice precedenti è il seguente:

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]

Piace

Il metodo like () stesso produce i criteri LIKE per la clausola WHERE nell'espressione 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)

Il codice sopra SQLAlchemy è equivalente alla seguente espressione 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 ?

E l'output per il codice sopra è -

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

IN

Questo operatore controlla se il valore della colonna appartiene a una raccolta di elementi in un elenco. È fornito dal metodo 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)

Qui, l'espressione SQL valutata dal motore SQLite sarà la seguente:

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 (?, ?)

L'output per il codice precedente è il seguente:

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

E

Questa congiunzione è generata da entrambi putting multiple commas separated criteria in the filter or using and_() method come indicato di seguito -

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)

Entrambi gli approcci sopra danno come risultato un'espressione SQL simile -

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 ?

L'output per le righe di codice precedenti è:

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

O

Questa congiunzione è implementata da 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)

Di conseguenza, il motore SQLite ottiene la seguente espressione 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 ?

L'output per il codice precedente è il seguente:

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]

Esistono numerosi metodi dell'oggetto Query che emettono immediatamente SQL e restituiscono un valore contenente i risultati del database caricato.

Ecco un breve riepilogo della lista di restituzione e degli scalari:

tutti()

Restituisce un elenco. Di seguito è riportata la riga di codice per la funzione all ().

session.query(Customers).all()

La console Python mostra la seguente espressione SQL emessa:

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

primo()

Applica un limite di uno e restituisce il primo risultato come uno scalare.

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 ?

Il parametro associato per LIMIT è 1 e per OFFSET è 0.

uno()

Questo comando recupera completamente tutte le righe e, se non è presente esattamente un'identità di oggetto o una riga composta nel risultato, genera un errore.

session.query(Customers).one()

Con più righe trovate -

MultipleResultsFound: Multiple rows were found for one()

Senza righe trovate -

NoResultFound: No row was found for one()

Il metodo one () è utile per i sistemi che prevedono di gestire in modo diverso "nessun elemento trovato" rispetto a "più elementi trovati".

scalare()

Invoca il metodo one () e in caso di successo restituisce la prima colonna della riga come segue:

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

Questo genera la seguente istruzione 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 = ?

In precedenza, l'SQL testuale che utilizza la funzione text () è stato spiegato dal punto di vista del linguaggio di espressione principale di SQLAlchemy. Ora ne discuteremo dal punto di vista ORM.

Le stringhe letterali possono essere usate in modo flessibile con l'oggetto Query specificando il loro utilizzo con il costrutto text (). La maggior parte dei metodi applicabili lo accetta. Ad esempio, filter () e order_by ().

Nell'esempio fornito di seguito, il metodo filter () traduce la stringa "id <3" in WHERE id <3

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

L'espressione SQL non elaborata generata mostra la conversione del filtro nella clausola WHERE con il codice illustrato di seguito -

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

Dai nostri dati di esempio nella tabella Clienti, verranno selezionate due righe e la colonna del nome verrà stampata come segue:

Ravi Kumar
Komal Pande

Per specificare i parametri di bind con SQL basato su stringa, utilizzare i due punti e per specificare i valori, utilizzare il metodo params ().

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

L'effettivo SQL visualizzato sulla console Python sarà il seguente:

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 = ?

Per usare un'istruzione interamente basata su stringhe, un costrutto text () che rappresenta un'istruzione completa può essere passato a from_statement ().

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

Il risultato del codice precedente sarà un'istruzione SELECT di base come indicato di seguito:

SELECT * FROM customers

Ovviamente verranno selezionati tutti i record nella tabella dei clienti.

Il costrutto text () ci consente di collegare il suo SQL testuale alle espressioni di colonna Core o ORM mappate in modo posizionale. Possiamo ottenere ciò passando le espressioni di colonna come argomenti posizionali al metodo 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()

Le colonne id e nome di tutte le righe verranno selezionate anche se il motore SQLite esegue la seguente espressione generata dal codice sopra mostra tutte le colonne nel metodo text () -

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

Questa sessione descrive la creazione di un'altra tabella correlata a una già esistente nel nostro database. La tabella dei clienti contiene i dati anagrafici dei clienti. Ora dobbiamo creare una tabella delle fatture che può contenere un numero qualsiasi di fatture appartenenti a un cliente. Questo è un caso di relazioni uno a molti.

Usando dichiarativo, definiamo questa tabella insieme alla sua classe mappata, Fatture come indicato di seguito -

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)

Questo invierà una query CREATE TABLE al motore SQLite come di seguito -

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

Possiamo verificare che la nuova tabella venga creata in sales.db con l'aiuto dello strumento SQLiteStudio.

La classe Invoices applica il costrutto ForeignKey sull'attributo custid. Questa direttiva indica che i valori in questa colonna devono essere vincolati ai valori presenti nella colonna id nella tabella dei clienti. Questa è una caratteristica fondamentale dei database relazionali ed è il "collante" che trasforma una raccolta di tabelle non connessa per avere ricche relazioni sovrapposte.

Una seconda direttiva, nota come relationship (), indica all'ORM che la classe Invoice deve essere collegata alla classe Customer utilizzando l'attributo Invoice.customer. La relazione () utilizza le relazioni di chiave esterna tra le due tabelle per determinare la natura di questo collegamento, determinando che è molti a uno.

Un'ulteriore direttiva relationship () viene inserita nella classe mappata Customer sotto l'attributo Customer.invoices. Il parametro relationship.back_populate è assegnato per fare riferimento ai nomi degli attributi complementari, in modo che ogni relazione () possa prendere una decisione intelligente sulla stessa relazione espressa al contrario. Da un lato, Invoices.customer si riferisce all'istanza di Invoices e, dall'altro lato, Customer.invoices si riferisce a un elenco di istanze dei clienti.

La funzione di relazione fa parte dell'API Relationship del pacchetto ORM di SQLAlchemy. Fornisce una relazione tra due classi mappate. Ciò corrisponde a una relazione padre-figlio o tabella associativa.

Di seguito sono riportati i modelli di relazione di base trovati:

Uno a molti

Una relazione Uno a molti si riferisce al genitore con l'aiuto di una chiave esterna nella tabella figlio. relationship () viene quindi specificato sul genitore, come riferimento a una raccolta di elementi rappresentati dal figlio. Il parametro relationship.back_populate viene utilizzato per stabilire una relazione bidirezionale in uno-a-molti, dove il lato "inverso" è un molti a uno.

Molti a uno

D'altra parte, la relazione molti a uno inserisce una chiave esterna nella tabella padre per fare riferimento al figlio. relazione () è dichiarata sul genitore, dove verrà creato un nuovo attributo di mantenimento scalare. Anche in questo caso il parametro relationship.back_populate viene utilizzato per Bidirectionalbehaviour.

Uno a uno

La relazione One To One è essenzialmente una relazione bidirezionale in natura. Il flag uselist indica il posizionamento di un attributo scalare invece di una raccolta sul lato "molti" della relazione. Per convertire uno-a-molti in un tipo di relazione uno-a-uno, impostare il parametro uselist su false.

Molti a molti

La relazione molti a molti viene stabilita aggiungendo una tabella di associazione relativa a due classi definendo gli attributi con le loro chiavi esterne. È indicato dall'argomento secondario di relationship (). Di solito, la tabella utilizza l'oggetto MetaData associato alla classe base dichiarativa, in modo che le direttive ForeignKey possano individuare le tabelle remote con cui collegarsi. Il parametro relationship.back_populated per ogni relationship () stabilisce una relazione bidirezionale. Entrambi i lati della relazione contengono una raccolta.

In questo capitolo, ci concentreremo sugli oggetti correlati in SQLAlchemy ORM.

Ora, quando creiamo un oggetto Cliente, sarà presente una raccolta di fatture vuota sotto forma di Python List.

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

L'attributo fatture di c1.invoices sarà un elenco vuoto. Possiamo assegnare elementi nell'elenco come:

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

Eseguiamo il commit di questo oggetto nel database utilizzando l'oggetto Session come segue:

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

Questo genererà automaticamente query INSERT per clienti e tabelle fatture -

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)

Esaminiamo ora il contenuto della tabella dei clienti e della tabella delle fatture nella visualizzazione tabella di SQLiteStudio -

È possibile costruire l'oggetto Cliente fornendo l'attributo mappato delle fatture nel costruttore stesso utilizzando il comando seguente:

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

O un elenco di oggetti da aggiungere utilizzando la funzione add_all () dell'oggetto sessione come mostrato di seguito -

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()

Ora che abbiamo due tabelle, vedremo come creare query su entrambe le tabelle contemporaneamente. Per costruire un semplice join implicito tra Customer e Invoice, possiamo usare Query.filter () per equiparare tra loro le colonne correlate. Di seguito, carichiamo le entità Cliente e Fattura contemporaneamente utilizzando questo metodo:

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))

L'espressione SQL emessa da SQLAlchemy è la seguente:

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

E il risultato delle righe di codice precedenti è il seguente:

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

L'attuale sintassi SQL JOIN è facilmente ottenibile utilizzando il metodo Query.join () come segue:

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

L'espressione SQL per il join verrà visualizzata sulla console:

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 = ?

Possiamo iterare attraverso il risultato usando il ciclo for -

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 come parametro bind, viene visualizzato il seguente output:

4 Govind Kala 8 8500

Query.join () sa come unire queste tabelle perché tra di loro c'è solo una chiave esterna. Se non ci fossero chiavi esterne, o più chiavi esterne, Query.join () funziona meglio quando viene utilizzata una delle seguenti forme:

query.join (Fattura, id == Address.custid)	condizione esplicita
query.join (Customer.invoices)	specificare la relazione da sinistra a destra
query.join (Fattura, Customer.invoices)	stesso, con target esplicito
query.join ("fatture")	stesso, usando una stringa

Allo stesso modo la funzione outerjoin () è disponibile per ottenere il join esterno sinistro.

query.outerjoin(Customer.invoices)

Il metodo subquery () produce un'espressione SQL che rappresenta l'istruzione SELECT incorporata in un alias.

from sqlalchemy.sql import func

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

L'oggetto stmt conterrà un'istruzione SQL come di seguito:

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

Una volta che abbiamo la nostra dichiarazione, si comporta come un costrutto di tabella. Le colonne dell'istruzione sono accessibili tramite un attributo chiamato c come mostrato nel codice seguente -

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)

Il ciclo for precedente mostra il conteggio delle fatture in base al nome come segue:

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

In questo capitolo discuteremo degli operatori che si basano sulle relazioni.

__eq __ ()

L'operatore precedente è un confronto "uguale" molti a uno. La riga di codice per questo operatore è come mostrato di seguito:

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

La query SQL equivalente per la riga di codice precedente è:

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 = ?

__ne __ ()

Questo operatore è un confronto molti a uno "non uguale". La riga di codice per questo operatore è come mostrato di seguito:

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

La query SQL equivalente per la riga di codice precedente è fornita di seguito:

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 ()

Questo operatore viene utilizzato per raccolte uno-a-molti e di seguito viene fornito il codice per contiene () -

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

La query SQL equivalente per la riga di codice precedente è:

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 '%' + ? || '%')

qualunque()

L'operatore any () viene utilizzato per le raccolte come mostrato di seguito:

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

La query SQL equivalente per la riga di codice precedente è mostrata di seguito:

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 = ?)

ha ()

Questo operatore viene utilizzato per i riferimenti scalari come segue:

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

La query SQL equivalente per la riga di codice precedente è:

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 = ?)

Il carico desideroso riduce il numero di query. SQLAlchemy offre funzioni di caricamento impazienti richiamate tramite opzioni di query che forniscono istruzioni aggiuntive alla query. Queste opzioni determinano come caricare vari attributi tramite il metodo Query.options ().

Sottoquery Load

Vogliamo che Customer.invoices venga caricato con entusiasmo. L'opzione orm.subqueryload () fornisce una seconda istruzione SELECT che carica completamente le raccolte associate ai risultati appena caricati. Il nome "sottoquery" fa sì che l'istruzione SELECT venga costruita direttamente tramite la query riutilizzata e incorporata come sottoquery in una SELECT sulla tabella correlata.

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

Ciò si traduce nelle seguenti due espressioni 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',)

Per accedere ai dati da due tabelle, possiamo utilizzare il programma seguente:

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

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

L'output del programma di cui sopra è il seguente:

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

Carico unito

L'altra funzione si chiama orm.joinedload (). Questo emette un LEFT OUTER JOIN. L'oggetto principale e l'oggetto o la raccolta correlati vengono caricati in un unico passaggio.

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

Questo emette la seguente espressione dando lo stesso output di sopra -

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 ha prodotto due righe, ma restituisce un'istanza di Customer. Questo perché Query applica una strategia "univoca", basata sull'identità dell'oggetto, alle entità restituite. È possibile applicare il caricamento ansioso unito senza influire sui risultati della query.

Il subqueryload () è più appropriato per il caricamento di raccolte correlate mentre joinload () è più adatto per relazioni molti-a-uno.

È facile eseguire l'operazione di cancellazione su una singola tabella. Tutto quello che devi fare è eliminare un oggetto della classe mappata da una sessione e eseguire il commit dell'azione. Tuttavia, l'operazione di eliminazione su più tabelle correlate è un po 'complicata.

Nel nostro database sales.db, le classi Customer e Invoice sono mappate alla tabella cliente e fattura con un tipo di relazione uno a molti. Cercheremo di eliminare l'oggetto Cliente e vedere il risultato.

Come riferimento rapido, di seguito sono riportate le definizioni delle classi Cliente e Fattura:

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")

Impostiamo una sessione e otteniamo un oggetto Cliente interrogandolo con l'ID principale utilizzando il programma seguente:

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

Nella nostra tabella di esempio, x.name sembra essere "Gopal Krishna". Cancelliamo questa x dalla sessione e contiamo l'occorrenza di questo nome.

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

L'espressione SQL risultante restituirà 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

Tuttavia, gli oggetti Fattura correlati di x sono ancora presenti. Può essere verificato dal seguente codice:

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

Qui, 10 e 14 sono i numeri di fattura appartenenti al cliente Gopal Krishna. Il risultato della query precedente è 2, il che significa che gli oggetti correlati non sono stati eliminati.

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

Questo perché SQLAlchemy non presuppone l'eliminazione di cascade; dobbiamo dare un comando per cancellarlo.

Per modificare il comportamento, configuriamo le opzioni a cascata nella relazione User.addresses. Chiudiamo la sessione in corso, usiamo new dichiarative_base () e dichiariamo nuovamente la classe User, aggiungendo nella relazione degli indirizzi inclusa la configurazione a cascata.

L'attributo cascade nella funzione di relazione è un elenco separato da virgole di regole a cascata che determina il modo in cui le operazioni di sessione devono essere "a cascata" da genitore a figlio. Per impostazione predefinita, è False, il che significa che è "salva-aggiorna, unisci".

Le cascate disponibili sono le seguenti:

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

L'opzione utilizzata spesso è "all, delete-orphan" per indicare che gli oggetti correlati devono seguire in tutti i casi insieme all'oggetto padre e devono essere eliminati quando vengono rimossi.

Quindi la classe cliente ridichiarata è mostrata di seguito:

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" 
   )

Eliminiamo il cliente con il nome Gopal Krishna utilizzando il programma seguente e vediamo il conteggio dei suoi oggetti Fattura correlati -

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()

Il conteggio è ora 0 con il seguente SQL emesso dallo script sopra -

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 relationshiptra due tabelle si ottiene aggiungendo una tabella di associazione in modo che abbia due chiavi esterne, una dalla chiave primaria di ciascuna tabella. Inoltre, le classi che mappano alle due tabelle hanno un attributo con una raccolta di oggetti di altre tabelle di associazione assegnati come attributo secondario della funzione relationship ().

A tal fine, creeremo un database SQLite (mycollege.db) con due tabelle: reparto e dipendente. In questo caso, si presume che un dipendente faccia parte di più di un dipartimento e che un dipartimento abbia più di un dipendente. Ciò costituisce una relazione molti-a-molti.

La definizione delle classi Employee e Department mappate alla tabella Department e Employee è la seguente:

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')

Definiamo ora una classe Link. È collegato alla tabella dei collegamenti e contiene gli attributi department_id e employee_id che fanno rispettivamente riferimento alle chiavi primarie della tabella del reparto e dell'impiegato.

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)

Qui, dobbiamo prendere nota che la classe Department ha l'attributo Employee correlato alla classe Employee. All'attributo secondario della funzione di relazione viene assegnato un collegamento come valore.

Allo stesso modo, la classe Employee ha l'attributo departments correlato alla classe Department. All'attributo secondario della funzione di relazione viene assegnato un collegamento come valore.

Tutte queste tre tabelle vengono create quando viene eseguita la seguente istruzione:

Base.metadata.create_all(engine)

La console Python emette le seguenti query 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)
)

Possiamo verificarlo aprendo mycollege.db usando SQLiteStudio come mostrato negli screenshot riportati di seguito -

Successivamente creiamo tre oggetti della classe Department e tre oggetti della classe Employee come mostrato di seguito:

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

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

Ogni tabella ha un attributo di raccolta con il metodo append (). Possiamo aggiungere oggetti Employee alla raccolta Employees dell'oggetto Department. Allo stesso modo, possiamo aggiungere oggetti Department all'attributo di raccolta departments degli oggetti Employee.

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

Tutto quello che dobbiamo fare ora è impostare un oggetto di sessione, aggiungervi tutti gli oggetti e confermare le modifiche come mostrato di seguito -

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()

Le seguenti istruzioni SQL verranno emesse sulla console 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))

Per verificare l'effetto delle operazioni di cui sopra, utilizzare SQLiteStudio e visualizzare i dati nelle tabelle di reparto, dipendente e collegamento -

Per visualizzare i dati, eseguire la seguente istruzione di query:

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))

In base ai dati inseriti nel nostro esempio, l'output verrà visualizzato come di seguito:

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 utilizza il sistema di dialetti per comunicare con vari tipi di database. Ogni database ha un wrapper DBAPI corrispondente. Tutti i dialetti richiedono che sia installato un driver DBAPI appropriato.

I seguenti dialetti sono inclusi nell'API SQLAlchemy:

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

Un oggetto Engine basato su un URL viene prodotto dalla funzione create_engine (). Questi URL possono includere nome utente, password, nome host e nome del database. Potrebbero esserci argomenti di parole chiave opzionali per una configurazione aggiuntiva. In alcuni casi, viene accettato un percorso di file e in altri un "nome origine dati" sostituisce le parti "host" e "database". La forma tipica di un URL di database è la seguente:

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

PostgreSQL

Il dialetto PostgreSQL utilizza psycopg2come DBAPI predefinito. pg8000 è anche disponibile come sostituto puro di Python come mostrato di seguito:

# 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

Il dialetto MySQL utilizza mysql-pythoncome DBAPI predefinito. Sono disponibili molti DBAPI MySQL, come MySQL-connector-python come segue:

# 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')

Oracolo

Il dialetto Oracle utilizza cx_oracle come DBAPI predefinito come segue:

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

Microsoft SQL Server

Il dialetto di SQL Server utilizza pyodbccome DBAPI predefinito. pymssql è anche disponibile.

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

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

SQLite

SQLite si connette a database basati su file, utilizzando il modulo integrato Python sqlite3per impostazione predefinita. Poiché SQLite si connette ai file locali, il formato dell'URL è leggermente diverso. La parte "file" dell'URL è il nome del file del database. Per un percorso file relativo, questo richiede tre barre come mostrato di seguito:

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

E per un percorso di file assoluto, le tre barre sono seguite dal percorso assoluto come indicato di seguito:

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

Per utilizzare un database SQLite: memory:, specificare un URL vuoto come indicato di seguito -

engine = create_engine('sqlite://')

Conclusione

Nella prima parte di questo tutorial, abbiamo appreso come utilizzare Expression Language per eseguire istruzioni SQL. Il linguaggio di espressione incorpora costrutti SQL nel codice Python. Nella seconda parte, abbiamo discusso la capacità di mappatura delle relazioni tra oggetti di SQLAlchemy. L'API ORM mappa le tabelle SQL con le classi Python.