SQLAlchemy ORM

Op deze pagina:

SQLAlchemy is een SQL-toolkit en Object Relational Mapper (ORM). Met de object-relationele mapper (ORM) kunnen Python classes aan een database worden gekoppeld.

SQLAlchemy installeren

Je kunt SQLAlchemy installeren met pip:

pip install sqlalchemy

Base

Het belangrijkste doel van SQLAlchemy ORM is om het werken met een database in Python eenvoudiger te maken door te zorgen dat je zonder SQL-code te gebruiken met een database kan werken via Python opdrachten. De databasetabellen worden gekoppeld aan Python classes en worden automatisch synchroon gehouden. Mocht het nodig zijn om toch SQL-code te gebruiken, dan kan dat ook.

Allereerst wordt de functie create_engine() aangeroepen om een ​​engine-object in te stellen dat vervolgens wordt gebruikt om SQL-bewerkingen uit te voeren. De functie heeft twee argumenten, de ene is de naam van de database en de andere is de parameter 'echo'. Wanneer deze wordt ingesteld op True zal het activiteitenlogboek gegenereerd worden. Als de database nog niet bestaat, dan zal deze worden aangemaakt. In het volgende voorbeeld wordt een SQLite-database gemaakt met de naam 'database.db'.

from sqlalchemy import create_engine

engine = create_engine('sqlite:///databse.db', echo = True)

Je begint met het definiëren van de Base class met declarative_base() waarop alle andere database classes gebaseerd zullen worden.

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

Zodra de Base class is gedeclareerd, kan een willekeurig aantal classes op basis daarvan worden gedefiniëerd. De volgende code definiëert de class van 'klanten'. Het bevat de tabel waaraan moet worden toegewezen, namen en datatypes van de kolommen.

class Klanten(Base):
   __tablename__ = 'klanten'
   
   id = Column(Integer, primary_key = True)
   naam = Column(String)
   adres = Column(String)
   email = Column(String)

Een database class moet een __tablename__ attribuut hebben, en minstens één kolom die deel uitmaakt van een primaire sleutel.

Met Base.metadata.create_all(engine) worden de tabellen aangemaakt. De ORM doet dit op de achtergrond met CREATE TABLE opdrachten.

Base.metadata.create_all(engine)

Het complete script voor het maken van een database en een tabel en deze verbinden met een Python class:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///database.db', echo = True)

Base = declarative_base()

class Klanten(Base):
   __tablename__ = 'klanten'
   id = Column(Integer, primary_key=True)

   naam = Column(String)
   adres = Column(String)
   email = Column(String)

Base.metadata.create_all(engine)

Session

Om met de database te kunnen communiceren, moeten we de 'handle' verkrijgen. Een sessieobject is de ingang naar de database. De sessie class wordt gedefiniëerd met behulp van sessionmaker():

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

Het sessieobject wordt vervolgens ingesteld met behulp van de standaardconstructor Session():

session = Session()

Enkele vaak gebruikte methoden van sessie class:

begin()

Begint een transactie in deze sessie.

add()

Plaatst een object in de sessie. Deze wordt doorgezet in de database bij de volgende flush operatie.

add_all()

Voegt een verzameling van objecten toe aan de sessie.

commit()

Stuurt alle items en elke voortgaande transactie definitief naar de database, hierbij wordt eerst flush() uitgevoerd.

delete()

Markeert een transactie als gewist.

execute()

Voert een SQL expressie uit.

expire()

Markeert attributen van een instantie als verlopen.

flush()

Stuurt alle object veranderingen naar de database zonder deze definitief nog toe te voegen, dat gebeurt pas met een commit().

invalidate()

Sluit de sessie via het ongeldig maken van de verbinding.

rollback()

Draait de transactie die nu bezig is terug.

close()

Sluit de huidige sessie

Objecten toevoegen

Een rij toevoegen aan de klantentabel met session.add():

k1 = Verkoop(naam = 'Jan Jansen', adres = 'Fietsenmakerstraat', email = 'jan@jansen.nl')
session.add(k1)

Deze transactie is in behandeling totdat deze wordt ge-flushed met behulp van de commit() methode.

session.commit()

Een compleet script om een record toe te voegen in de klanten tabel:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///database.db', echo = True)

Base = declarative_base()

class Klanten(Base):
   __tablename__ = 'klanten'
   
   id = Column(Integer, primary_key=True)
   naam = Column(String)
   adres = Column(String)
   email = Column(String)
   
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind = engine)
session = Session()

k1 = Klanten(naam = 'Jan Jansen', adres = 'Fietsenmakerstraat 34', email = 'jan@jansen.nl')

session.add(k1)
session.commit()

Om meerdere records toe te voegen, is de methode add_all() van de sessie class beschikbaar:

session.add_all([
   Klanten(naam = 'Jan Jansen', adres = 'Fietsenmakerstraat 34', email = 'jan@jansen.nl'),
   Klanten(naam = 'Maria Jansen', adres = 'Fietsenmakerstraat 34', email = 'maria@jansen.nl'),
   Klanten(naam = 'Jan Zwart', adres = 'Oliestraat 7', email = 'jan@zwart.nl')]
)

session.commit()

Query gebruiken

Alle SELECT instructies die SQLAlchemy ORM genereert, worden samengesteld door een Query-object. Opeenvolgende aanroepen retourneren steeds een nieuw Query-object, een kopie van het vorige met aanvullende criteria en bijbehorende opties.

Query-objecten worden aanvankelijk gegenereerd met behulp van de methode query() van de sessie:

q = session.query(mapped class)

Dit is gelijkwaardig met:

q = Query(mappedClass, session)

Het query object heeft een all() method die een resultaatset teruggeeft in de vorm van een lijst (list) met objecten. Voorbeeld:

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

Dit is gelijkwardig met de volgende SQL uitdrukking:

SELECT
klanten.id AS klanten_id,
klanten.name AS klanten_name,
klanten.address AS klanten_address,
klanten.email AS klanten_email
FROM klanten

Het resultaatobject kan worden doorlopen met behulp van de for-loop zoals hieronder om alle records in de onderliggende klantentabel te verkrijgen.

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///database.db', echo = True)

Base = declarative_base()

class Klanten(Base):
   __tablename__ = 'klanten'
   id = Column(Integer, primary_key =  True)
   naam = Column(String)
   adres = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Klanten).all()

for row in result:
   print ("Naam: ",row.naam, "Adres:",row.adres, "Email:",row.email)

Het Query object heeft deze vaak gebruikte methods:

add_columns()

Dit voegt één of meer kolom uitdrukkingen toe aan de lijst met met resultaatkolommen die moeten worden teruggegeven.

add_entity()

Het voegt een toegewezen entiteit toe aan de lijst met resultaatkolommen die moeten worden teruggegeven.

count()

Het geeft een telling van rijen die deze Query zou teruggeven.

delete()

Het verwijderd rijen volgens de query uit de dataabse.

distinct()

Het past een DISTINCT-clausule toe op de query en geeft de nieuw resulterende Query terug.

filter()

Het past de opgegeven filtercriteria toe op een kopie van de Query, met gebruik van SQL uitdrukkingen.

first()

Het geeft het eerste resultaat van de Query of None als het resultaat geen rij bevat.

get()

Het geeft een instantie terug op basis van de gegeven primaire sleutel-ID die directe toegang biedt tot de identity map van de sessie die eigenaar is.

group_by()

Het past één of meer GROUP BY criteria toe op de query en geeft de nieuwe resulterende Query.

join()

Het maakt een SQL JOIN op deze Query object’s criteria en past het generatief toe, de resulterende query wordt teruggegeven.

one()

Het geeft precies één resultaat terug of er volgt een exception.

order_by()

Het past een of meer ORDER BY criteria toe op de query en geeft de nieuw resulterende Query terug.

update()

Het voert een bulk updatequery uit en werkt rijen bij die overeenkomen met deze query in de database.

Objecten wijzigen

Om gegevens van een bepaald attribuut van een object te wijzigen, moeten we er een nieuwe waarde aan toekennen en de wijzigingen doorvoeren om de wijziging blijvend te maken.

Een object ophalen uit de tabel waarvan de primaire sleutel ID 2 is met de sessie methode get():

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

De inhoud van het geselecteerde object tonen:

print ("Naam: ", x.naam, "Adres:", x.adres, "Email:", x.email)

Een nieuw adres toewijzen:

x.address = 'Leeuwenhoekstraat 15'
session.commit()

De wijziging wordt toegepast in de database door de commit().

Het object ophalen dat overeenkomt met de eerste rij in de tabel met de methode first():

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

Voor bulkupdates is de update() methode van het query-object beschikbaar.

De methode update() vereist de volgende twee parameters:

• Een dictionary waarbij een sleutel het attribuut is dat moet worden bijgewerkt en een waarde de nieuwe inhoud van het attribuut is.
• Een synchronize_session attribuut met vermelding van de strategie om attributen in de sessie bij te werken. Geldige waarden zijn false: voor het niet synchroniseren van de sessie, fetch: voert een selectiequery uit vóór de update om objecten te vinden die overeenkomen met de updatequery; en evaluate: evalueer criteria op objecten in de sessie.

De wijzigingen worden pas definitief na een commit.

Filter toepassen

Een resultatenset vertegenwoordigd door een Query-object kan aan bepaalde criteria worden onderworpen met behulp van de filter()-methode. Het algemene gebruik van de filtermethode is als volgt:

session.query(class).filter(criteria)

In het kort als voorbeeld:

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

Een compleet voorbeeld:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///database.db', echo = True)

Base = declarative_base()

class Klanten(Base):
   __tablename__ = 'klanten'
   
   id = Column(Integer, primary_key = True)
   naam = Column(String)
   adres = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Klanten).filter(Klanten.id>2)

for row in result:
   print ("ID:", row.id, "Naam: ",row.naam, "Adres:",row.adres, "Email:",row.email)

Filter Operators

Gelijkheid

De operator is == en past de criteria toe om gelijkheid te controleren.

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

Ongelijkheid

De operator voor 'niet gelijk aan' is != en levert 'niet gelijk aan' criteria.

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

Like

like() produceert de LIKE-criteria voor de WHERE-component in de SELECT uitdrukking.

result = session.query(Klanten).filter(Klanten.naam.like('Ja%'))
for row in result:
   print ("ID:", row.id, "Naam: ",row.naam, "Adres:",row.adres, "Email:",row.email)

IN

Deze operator controleert of de kolomwaarde behoort tot een verzameling items in een lijst. Het wordt geleverd door de methode in_().

result = session.query(Klanten).filter(Klanten.id.in_([1,3]))
for row in result:
   print ("ID:", row.id, "Naam: ",row.naam, "Adres:",row.adres, "Email:",row.email)

AND

Deze combinatie wordt gegenereerd door ofwel meerdere door komma's gescheiden criteria in het filter te plaatsen of door de methode and_() te gebruiken, zoals hieronder weergegeven:

result = session.query(Klanten).filter(Klanten.id>2, Klanten.naam.like('Ja%'))
for row in result:
   print ("ID:", row.id, "Naam: ",row.naam, "Adres:",row.adres, "Email:",row.email)

from sqlalchemy import and_

result = session.query(Klanten).filter(and_(Klanten.id>2, Klanten.naam.like('Ja%')))
for row in result:
   print ("ID:", row.id, "Naam: ",row.naam, "Adres:",row.adres, "Email:",row.email)

OR

Deze combinatie wordt geïmplementeerd door de methode or_().

from sqlalchemy import or_

result = session.query(Klanten).filter(or_(Klanten.id>2, Klanten.naam.like('Ja%')))

for row in result:
   print ("ID:", row.id, "Naam: ",row.naam, "Adres:",row.adres, "Email:",row.email)

List en Scalars teruggeven

all()

Geeft een lijst (list) terug

session.query(Klanten).all()

first()

Past een limiet van één toe en geeft het eerste resultaat als een scalar terug.

session.query(Klanten).first()

one()

Deze opdracht haalt alle rijen volledig op en als er niet precies één objectidentiteit of samengestelde rij in het resultaat aanwezig is, wordt er een fout gegenereerd.

session.query(Klanten).one()

Als er meerdere rijen worden gevonden: MultipleResultsFound: Multiple rows were found for one().

Als er geen rijen worden gevonden: NoResultFound: No row was found for one().

De methode one() is handig voor systemen die verwachten dat ze "geen items gevonden" anders behandelen dan "meerdere items gevonden".

scalar()

Het roept de methode one() aan en geeft bij succes de eerste kolom van de rij terug.

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

Rechtstreeks SQL queries gebruiken

Letterlijke SQL tekenreeksen kunnen flexibel worden gebruikt met het Query-object met de text()-constructie.

Een text()-constructie die een volledige instructie vertegenwoordigt, kan worden doorgegeven aan een from_statement().

session.query(Klanten).from_statement(text("SELECT * FROM klanten")).all()

Relatie opbouwen

De klantentabel bevat stamgegevens van klanten. We moeten nu een factuurtabel maken die een willekeurig aantal facturen van een klant kan bevatten. Dit is een voorbeeld van een-op-veel-relaties.

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///verkopen.db', echo = True)

Base = declarative_base()

from sqlalchemy.orm import relationship

class Klanten(Base):
   __tablename__ = 'klanten'

   id = Column(Integer, primary_key = True)
   naam = Column(String)
   adres = Column(String)
   email = Column(String)

class Facturen(Base):
   __tablename__ = 'facturen'
   
   id = Column(Integer, primary_key = True)
   klantnr = Column(Integer, ForeignKey('klanten.id'))
   factnr = Column(Integer)
   bedrag = Column(Integer)
   klant = relationship("Klant", back_populates = "facturen")

Klanten.facturen = relationship("Facturen", order_by = Facturen.id, back_populates = "klanten")
Base.metadata.create_all(engine)

De class Facturen past de ForeignKey-constructie toe op het kenmerk klantnr. Dit betekent dat waarden in deze kolom moeten worden beperkt tot waarden die aanwezig zijn in de id-kolom in de klantentabel.

Via relationship() wordt de class Facturen gekoppeld aan de class Klanten met behulp van het kenmerk Facturen.klanten. Via relationship() wordt de aard van de koppeling ingesteld: veel op één.

De parameter relationship.back_populates wordt gebruikt om de verwijzing voor Facturen.klanten tussen Facturen en Klanten in te stellen.

De basis relationship patronen:

One To Many : één op veel

Een één-op-veel-relatie verwijst naar ouder met behulp van een externe sleutel op de onderliggende tabel. relationship() wordt dan gespecificeerd op het bovenliggende item, als verwijzing naar een verzameling items die door het onderliggende item worden vertegenwoordigd. De parameter relationship.back_populates wordt gebruikt om een relatie in twee richtingen tot stand te brengen in één-op-veel, waarbij de "omgekeerde" kant een veel-op-één is.

Many To One : veel op één

Aan de andere kant plaatst een veel-op-één-relatie een externe sleutel in de bovenliggende tabel om naar het kind te verwijzen. relationship() wordt gedeclareerd op de ouder, waar een nieuw scalar-houdend attribuut wordt gemaakt. Ook hier wordt de parameter relationship.back_populates gebruikt voor gedrag in twee richtingen.

One To One : één op één

Eén-op-één-relatie is in wezen een relatie in twee richtingen. De uselist-vlag geeft de plaatsing aan van een scalar attribuut in plaats van een verzameling aan de "veel"-zijde van de relatie. Om één-op-veel-relaties om te zetten in één-op-één-relaties, stel je de uselist-parameter in op false.

Many To Many : veel op veel

Een veel-op-veel-relatie wordt tot stand gebracht door een associatietabel toe te voegen die is gerelateerd aan twee classes door attributen te definiëren met hun externe sleutels. Het wordt aangegeven door het tweede argument van relationship(). Gewoonlijk gebruikt de tabel het MetaData-object dat is gekoppeld aan de declaratieve basis class, zodat de ForeignKey-richtlijnen de externe tabellen kunnen vinden waarmee moet worden gelinkt. De parameter relationship.back_populates voor elke relationship() brengt een relatie in twee richtingen tot stand. Beide kanten van de relatie bevatten een collectie.

Werken met Relatie Objecten

Wanneer je nu een klantobject maakt, is er een lege factuurverzameling aanwezig in de vorm van een Python-lijst.

k1 = Klant(naam = "Gerrit Koper", adres = "Watervalstraat 45", email = "gk@gmail.com")

Het facturen attribute van k1.facturen zal een lege lijst zijn. Je kunt de lijst vullen:

k1.facturen = [Factuur(factnr = 10, bedrag = 15000), Factuur(factnr = 14, bedrag = 3850)]

je kunt dit object via een commit aan de database toevoegen via het Session object:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind = engine)
session = Session()
session.add(k1)
session.commit()

Je kunt een klantobject maken door de facturen in de constructor zelf op te geven:

k2 = [
   Klant(
      naam = "Frits Putter",
      adres = "Zwartewoudstraat 35",
      email = "fp@hotmail.com",
      facturen = [Factuur(factnr = 3, bedrag = 10000),
      Factuur(factnr = 4, bedrag = 5000)]
   )
]

Of via een lijst met toe te voegen objecten met de functie add_all() van het sessieobject:

rows = [
   Klant(
      naam = "Frits Putter",
      adres = "Zwartewoudstraat 35",
      email = "fp@hotmail.com",
      facturen = [Factuur(factnr = 3, bedrag = 10000),
      Factuur(factnr = 4, bedrag = 5000)]),

   Klant(
      naam = "Simone Putter",
      adres = "Zwartewoudstraat 35",
      email = "sp@hotmail.com",
      facturen = [Factuur(factnr = 7, bedrag = 1000),
      Factuur(factnr = 9, bedrag = 2000)
   ])
]

session.add_all(rows)
session.commit()

Werken met Joins

Om een ​​eenvoudige impliciete join tussen 'Klanten' en 'Facturen' te maken, kun je Query.filter() gebruiken om hun gerelateerde kolommen aan elkaar gelijk te stellen.

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind = engine)
session = Session()

for k, f in session.query(Klanten, Facturen).filter(Klanten.id == Facturen.klantnr).all():
   print ("ID: {} Naam: {} Factuurnummer: {} Bedrag: {}".format(k.id,k.naam, f.factnr, f.bedrag))

Relationship Operators

__eq__()

gelijk aan

Voorbeeld:

s = session.query(Klanten).filter(Facturen.factnr.__eq__(10))

__ne__()

ongelijk aan

Voorbeeld:

s = session.query(Klanten).filter(Fcaturen.fsctnr.__ne__(4))

contains()

bevat

Voorbeeld:

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

any()

enig

Voorbeeld:

s = session.query(Klanten).filter(Klanten.facturen.any(Facturen.factnr==8))

has()

heeft

Voorbeeld:

s = session.query(Facturen).filter(Facturen.klanten.has(naam = 'Jan Jansen'))

Eager Loading

Eager loading vermindert het aantal queries. Dit worden aangeroepen via query-opties die aanvullende instructies aan de Query geven. Deze opties bepalen hoe verschillende attributen moeten worden geladen via de method Query.options().

Subquery Load

Eager loading voor Klanten.facturen: De optie orm.subqueryload() geeft een tweede SELECT-instructie die de verzamelingen die zijn gekoppeld aan de zojuist geladen resultaten volledig laadt. De naam "subquery" zorgt ervoor dat de SELECT-instructie rechtstreeks wordt geconstrueerd via de query die opnieuw wordt gebruikt en wordt ingesloten als een subquery in een SELECT tegen de gerelateerde tabel.

from sqlalchemy.orm import subqueryload
k1 = session.query(Klanten).options(subqueryload(Klanten.facturen)).filter_by(naam = 'Jan Jansen').one()

Om toegang te krijgen tot de gegevens uit de twee tabellen, kunnen we het volgende gebruiken:

print (k1.naam, k1.adres, k1.email)

for x in c1.facturen:
   print ("Factuurnummer : {}, Bedrag : {}".format(x.factnr, x.bedrag))

Joined Load

De andere functie heet orm.joinedload(). Dit gebruikt een LEFT OUTER JOIN. Zowel het hoofdobject als het gerelateerde object of de verzameling wordt in één stap geladen.

from sqlalchemy.orm import joinedload

k1 = session.query(Klanten).options(joinedload(Klanten.facturen)).filter_by(naam='Jan Jansen').one()

De OUTER JOIN resulteerde in twee rijen, maar geeft één exemplaar van Klanten terug. Dit komt omdat Query een "unieke" strategie toepast, gebaseerd op objectidentiteit, op de geretourneerde entiteiten. Joined eager loading kan worden toegepast zonder de queryresultaten te beïnvloeden.

De subqueryload() is geschikter voor het laden van gerelateerde verzamelingen, terwijl joinedload() beter geschikt is voor veel-op-een-relaties.

Related Objects wissen

Het is gemakkelijk om een ​​wisbewerking uit te voeren op een enkele tabel. Het enige dat je hoeft te doen, is een object van de toegewezen class uit een sessie te verwijderen en de actie vast te leggen via een commit. Het verwijderen van meerdere gerelateerde tabellen is echter minder eenvoudig.

In onze database worden klant- en factuur-classes toegewezen aan een klant- en een factuurtabel met één-op-veel-relatietypes.

Als je een klant verwijderd, dan moeten ook de facturen van die klant verdwijnen. SQLAlchemy gaat niet automatisch uit van het verwijderen van gerelateerde items, we moeten een opdracht geven om deze te verwijderen. Dit kan met de cascade optie.

Het cascade attribuut in de relationship functie is een door komma's gescheiden lijst met cascade regels die bepaalt hoe sessiebewerkingen moeten worden "getrapt" van ouder naar kind. Standaard is dit False, wat betekent dat het "save-update, merge" is.

De beschikbare cascades zijn als volgt: save-update, merge, expunge, delete, delete-orphan en refresh-expire

De vaak gebruikte optie is "all, delete-orphan" om aan te geven dat gerelateerde objecten in alle gevallen samen met het bovenliggende object moeten worden gevolgd en moeten worden verwijderd wanneer ze worden losgekoppeld.

Voorbeeld:

class Klanten(Base):
   __tablename__ = 'klanten'
   
   id = Column(Integer, primary_key = True)
   naam = Column(String)
   adres = Column(String)
   email = Column(String)
   Fcaturen = relationship(
      "Facturen",
      order_by = Fcaturen.id,
      back_populates = "klanten",
      cascade = "all,
      delete, delete-orphan"
   )

Veel naar veel Relationships

Een veel-op-veel-relatie tussen twee tabellen wordt bereikt door een associatietabel toe te voegen zodat deze twee externe sleutels heeft - één van de primaire sleutel van elke tabel. Bovendien hebben classes die zijn toegewezen aan de twee tabellen een attribuut met een verzameling objecten van andere associatietabellen die zijn toegewezen als secundair attribuut van de functie relationship().

 

Verwante artikelen