Python MySQL Database Toegang: Standaard DB-API Gebruik

De Python-standaard voor database-interfaces is de Python DB-API. De meeste Python-database-interfaces voldoen aan deze standaard.

U kunt de juiste database voor uw applicatie kiezen. Python Database API ondersteunt een breed scala aan databaseservers zoals:

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase

Hier is de lijst met beschikbare Python-database-interfaces: Python Database Interfaces and APIs. U moet een afzonderlijke DB API-module downloaden voor elke database die u wilt benaderen. Als u bijvoorbeeld toegang nodig hebt tot een Oracle-database en een MySQL-database, moet u zowel de Oracle- als de MySQL-databasemodules downloaden.

De DB API biedt een minimale standaard voor het werken met databases met Python-structuren en -syntaxis waar mogelijk. Deze API omvat het volgende:

• De API-module importeren.
• Een verbinding met de database verkrijgen.
• SQL-statements en opgeslagen procedures uitvoeren.
• De verbinding sluiten.

We leren alle concepten met MySQL, dus laten we praten over de MySQLdb-module.

Wat is MySQLdb?

MySQLdb is een interface voor het verbinden met een MySQL-databaseserver vanuit Python. Het implementeert de Python Database API v2.0 en is gebouwd op de MySQL C API.

Hoe installeer ik MySQLdb?

Voordat u doorgaat, zorg ervoor dat MySQLdb op uw machine geïnstalleerd is. Typ het volgende in uw Python-script en voer het uit:

#!/usr/bin/python
import MySQLdb

Als het het volgende resultaat produceert, betekent dat dat de MySQLdb-module niet geïnstalleerd is:

Traceback (most recent call last):
  File "test.py", line 3, in 
    import MySQLdb
ImportError: No module named MySQLdb

Om de MySQLdb-module te installeren, gebruik het volgende commando:

Voor Ubuntu, gebruik het volgende commando - $ sudo apt-get install python-pip python-dev libmysqlclient-dev
Voor Fedora, gebruik het volgende commando - $ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
Voor Python command prompt, gebruik het volgende commando - pip install MySQL-python

Let op - Zorg ervoor dat u root-rechten hebt om de bovenstaande module te installeren.

Databaseverbinding

Voordat u verbinding maakt met een MySQL-database, zorg ervoor dat:

• U een database TESTDB heeft aangemaakt.

• U een tabel EMPLOYEE in TESTDB heeft aangemaakt.

• Deze tabel de velden FIRST_NAME, LAST_NAME, AGE, SEX en INCOME heeft.

• Gebruikers-ID "testuser" en wachtwoord "test123" zijn ingesteld voor toegang tot TESTDB.

• Python-module MySQLdb correct op uw machine geïnstalleerd is.

• U de MySQL-tutorial heeft doorlopen om MySQL-basics te begrijpen.

Voorbeeld

Hieronder volgt het voorbeeld van het verbinden met de MySQL-database "TESTDB":

#!/usr/bin/python
import MySQLdb

# Databaseverbinding openen
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Een cursor-object voorbereiden met cursor() methode
cursor = db.cursor()

# SQL-query uitvoeren met execute() methode.
cursor.execute("SELECT VERSION()")

# Een enkele rij ophalen met fetchone() methode.
data = cursor.fetchone()
print "Database version : %s " % data

# Verbinding met server verbreken
db.close()

Tijdens het uitvoeren van dit script, produceert het het volgende resultaat op mijn Linux-machine:

Database version : 5.0.45

Als er een verbinding tot stand komt met de datasource, wordt een Connection Object geretourneerd en opgeslagen in db voor verder gebruik, anders wordt db op None gezet. Vervolgens wordt het db-object gebruikt om een cursor-object aan te maken, dat op zijn beurt gebruikt wordt om SQL-query's uit te voeren. Ten slotte, voordat het script eindigt, zorgt het ervoor dat de databaseverbinding wordt gesloten en bronnen worden vrijgegeven.

Databasetabel aanmaken

Zodra een databaseverbinding tot stand is gebracht, zijn we klaar om tabellen of records in de databasetabellen aan te maken met de execute-methode van de aangemaakte cursor.

Voorbeeld

Laten we de databasetabel EMPLOYEE aanmaken:

#!/usr/bin/python
import MySQLdb

# Databaseverbinding openen
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Een cursor-object voorbereiden met cursor() methode
cursor = db.cursor()

# Tabel verwijderen als deze al bestaat met execute() methode.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Tabel aanmaken conform eisen
sql = """CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT )"""
cursor.execute(sql)

# Verbinding met server verbreken
db.close()

INSERT-bewerking

Dit is vereist wanneer u records in een databasetabel wilt aanmaken.

Voorbeeld

Het volgende voorbeeld voert een SQL INSERT-statement uit om een record in de EMPLOYEE-tabel aan te maken:

#!/usr/bin/python
import MySQLdb

# Databaseverbinding openen
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Een cursor-object voorbereiden met cursor() methode
cursor = db.cursor()

# SQL-query voorbereiden om een record in de database te INSERTEN.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
    # Het SQL-commando uitvoeren
    cursor.execute(sql)
    # Uw wijzigingen in de database committen
    db.commit()
except:
    # Rollback in geval van fout
    db.rollback()

# Verbinding met server verbreken
db.close()

Bovenstaand voorbeeld kan als volgt geschreven worden om SQL-query's dynamisch te maken:

#!/usr/bin/python
import MySQLdb

# Databaseverbinding openen
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Een cursor-object voorbereiden met cursor() methode
cursor = db.cursor()

# SQL-query voorbereiden om een record in de database te INSERTEN.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
    # Het SQL-commando uitvoeren
    cursor.execute(sql)
    # Uw wijzigingen in de database committen
    db.commit()
except:
    # Rollback in geval van fout
    db.rollback()

# Verbinding met server verbreken
db.close()

Voorbeeld

Het volgende codesegment is een andere vorm van uitvoering waarbij u parameters direct kunt doorgeven:

..................................
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % \
(user_id, password))
..................................

READ-bewerking

READ-bewerking op een database betekent dat u nuttige informatie uit de database haalt.

Zodra onze databaseverbinding tot stand is gebracht, bent u klaar om een query op deze database uit te voeren. U kunt de fetchone()-methode gebruiken om een enkel record op te halen of de fetchall()-methode om meerdere waarden uit een databasetabel op te halen.

• fetchone() - Haalt de volgende rij van een queryresultaatset op. Een resultaatset is een object dat wordt geretourneerd wanneer een cursor-object een tabel bevraagt.

• fetchall() - Haalt alle rijen in een resultaatset op. Als er al rijen uit de resultaatset zijn gehaald, haalt het de overige rijen op.

• rowcount - Dit is een alleen-lezen attribuut en geeft het aantal rijen terug dat beïnvloed is door een execute()-methode.

Voorbeeld

De volgende procedure bevraagt alle records uit de EMPLOYEE-tabel met een salaris meer dan 1000:

#!/usr/bin/python
import MySQLdb

# Databaseverbinding openen
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Een cursor-object voorbereiden met cursor() methode
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
    # Het SQL-commando uitvoeren
    cursor.execute(sql)
    # Alle rijen ophalen in een lijst van lijsten.
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # Nu het opgehaalde resultaat printen
        print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
              (fname, lname, age, sex, income)
except:
    print "Error: unable to fetch data"

# Verbinding met server verbreken
db.close()

Dit produceert het volgende resultaat:

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

UPDATE-bewerking

UPDATE-bewerking op een database betekent dat u een of meer records, die al in de database beschikbaar zijn, bijwerkt.

De volgende procedure werkt alle records bij die SEX als 'M' hebben. Hier verhogen we de LEEFTIJD van alle mannen met één jaar.

Voorbeeld

#!/usr/bin/python
import MySQLdb

# Databaseverbinding openen
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Een cursor-object voorbereiden met cursor() methode
cursor = db.cursor()

# SQL-query voorbereiden om benodigde records te UPDATE-n
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
    # Het SQL-commando uitvoeren
    cursor.execute(sql)
    # Uw wijzigingen in de database committen
    db.commit()
except:
    # Rollback in geval van fout
    db.rollback()

# Verbinding met server verbreken
db.close()

DELETE-bewerking

DELETE-bewerking is vereist wanneer u enkele records uit uw database wilt verwijderen. Hieronder volgt de procedure om alle records uit EMPLOYEE te verwijderen waar LEEFTIJD meer dan 20 is:

Voorbeeld

#!/usr/bin/python
import MySQLdb

# Databaseverbinding openen
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Een cursor-object voorbereiden met cursor() methode
cursor = db.cursor()

# SQL-query voorbereiden om benodigde records te DELETE-n
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    # Het SQL-commando uitvoeren
    cursor.execute(sql)
    # Uw wijzigingen in de database committen
    db.commit()
except:
    # Rollback in geval van fout
    db.rollback()

# Verbinding met server verbreken
db.close()

Transacties uitvoeren

Transacties zijn een mechanisme dat gegevensconsistentie garandeert. Transacties hebben de volgende vier eigenschappen:

• Atomiciteit - Of een transactie voltooid of er gebeurt helemaal niets.

• Consistentie - Een transactie moet beginnen in een consistente toestand en het systeem in een consistente toestand laten.

• Isolatie - Tussenresultaten van een transactie zijn niet zichtbaar buiten de huidige transactie.

• Duurzaamheid - Eenmaal gecommit, zijn de effecten persistent, zelfs na een systeemstoring.

De Python DB API 2.0 biedt twee methoden om een transactie te committen of te rollback-en.

Voorbeeld

U weet al hoe u transacties implementeert. Hier is weer een vergelijkbaar voorbeeld:

# SQL-query voorbereiden om benodigde records te DELETE-n
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    # Het SQL-commando uitvoeren
    cursor.execute(sql)
    # Uw wijzigingen in de database committen
    db.commit()
except:
    # Rollback in geval van fout
    db.rollback()

COMMIT-bewerking

Commit is de bewerking die aan de database het groene licht geeft om de wijzigingen te finaliseren, en na deze bewerking kan geen wijziging meer ongedaan worden gemaakt.

Hier is een eenvoudig voorbeeld om de commit-methode aan te roepen:

db.commit()

ROLLBACK-bewerking

Als u niet tevreden bent met een of meer van de wijzigingen en u deze volledig wilt ongedaan maken, gebruik dan de rollback()-methode.

Hier is een eenvoudig voorbeeld om de rollback()-methode aan te roepen:

db.rollback()

Databaseverbinding verbreken

Om de databaseverbinding te verbreken, gebruikt u de close()-methode.

db.close()

Als de verbinding met een database door de gebruiker met de close()-methode wordt gesloten, worden eventuele openstaande transacties door de DB teruggedraaid. In plaats van te vertrouwen op een van de lagere implementatiedetails van de DB, zou uw applicatie beter commit of rollback expliciet aanroepen.

Foutafhandeling

Er zijn veel bronnen van fouten. Een paar voorbeelden zijn een syntactisfout in een uitgevoerde SQL-statement, een verbindingsfout, of het aanroepen van de fetch-methode voor een al geannuleerde of voltooide statement-handle.

De DB API definieert een aantal fouten die in elke databasemodule moeten bestaan. De volgende tabel lijst deze uitzonderingen.

Uw Python-scripts zouden deze fouten moeten afhandelen, maar voordat u een van de bovenstaande uitzonderingen gebruikt, zorg ervoor dat uw MySQLdb ondersteuning heeft voor die uitzondering. U kunt meer informatie over hen verkrijgen door de DB API 2.0-specificatie te lezen.