Python 3 - MySQL-Datenbankzugriff

Der Python-Standard für Datenbankschnittstellen ist die Python-DB-API. Die meisten Python-Datenbankschnittstellen entsprechen diesem Standard.

Sie können die richtige Datenbank für Ihre Anwendung auswählen. Die Python-Datenbank-API unterstützt eine Vielzahl von Datenbankservern wie -

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

Hier ist die Liste der verfügbaren Python-Datenbankschnittstellen - Python-Datenbankschnittstellen und APIs . Sie müssen für jede Datenbank, auf die Sie zugreifen müssen, ein separates DB-API-Modul herunterladen. Wenn Sie beispielsweise sowohl auf eine Oracle-Datenbank als auch auf eine MySQL-Datenbank zugreifen müssen, müssen Sie sowohl das Oracle- als auch das MySQL-Datenbankmodul herunterladen.

Die DB-API bietet einen Mindeststandard für die Arbeit mit Datenbanken unter Verwendung von Python-Strukturen und -Syntax, wo immer dies möglich ist. Diese API enthält Folgendes:

• API-Modul importieren.
• Herstellen einer Verbindung mit der Datenbank.
• Ausgabe von SQL-Anweisungen und gespeicherten Prozeduren.
• Verbindung schließen

Python bietet eine integrierte Unterstützung für SQLite. In diesem Abschnitt lernen wir alle Konzepte mit MySQL kennen. Das MySQLdb-Modul, eine beliebte Schnittstelle zu MySQL, ist nicht mit Python 3 kompatibel. Stattdessen verwenden wir das PyMySQL- Modul.

Was ist PyMySQL?

PyMySQL ist eine Schnittstelle zum Herstellen einer Verbindung zu einem MySQL-Datenbankserver von Python. Es implementiert die Python-Datenbank-API v2.0 und enthält eine reine Python-MySQL-Clientbibliothek. Das Ziel von PyMySQL ist es, MySQLdb zu ersetzen.

Wie installiere ich PyMySQL?

Bevor Sie fortfahren, stellen Sie sicher, dass PyMySQL auf Ihrem Computer installiert ist. Geben Sie einfach Folgendes in Ihr Python-Skript ein und führen Sie es aus:

#!/usr/bin/python3

import pymysql

Wenn das folgende Ergebnis angezeigt wird, bedeutet dies, dass das MySQLdb-Modul nicht installiert ist.

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      Import pymysql
ImportError: No module named pymysql

Die letzte stabile Version ist auf PyPI verfügbar und kann mit pip - installiert werden.

pip install pymysql

Alternativ (z. B. wenn pip nicht verfügbar ist) kann ein Tarball von GitHub heruntergeladen und wie folgt mit Setuptools installiert werden:

$ # X.X is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

Note - Stellen Sie sicher, dass Sie über Root-Rechte verfügen, um das oben genannte Modul zu installieren.

Datenbankverbindung

Stellen Sie vor dem Herstellen einer Verbindung zu einer MySQL-Datenbank die folgenden Punkte sicher:

• Sie haben eine Datenbank TESTDB erstellt.

• Sie haben in TESTDB eine Tabelle EMPLOYEE erstellt.

• Diese Tabelle enthält die Felder FIRST_NAME, LAST_NAME, AGE, SEX und INCOME.

• Die Benutzer-ID "testuser" und das Kennwort "test123" sind für den Zugriff auf TESTDB festgelegt.

• Das Python-Modul PyMySQL ist ordnungsgemäß auf Ihrem Computer installiert.

• Sie haben das MySQL-Tutorial durchgearbeitet, um die MySQL-Grundlagen zu verstehen .

Beispiel

Es folgt ein Beispiel für die Verbindung mit der MySQL-Datenbank "TESTDB" -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " % data)

# disconnect from server
db.close()

Beim Ausführen dieses Skripts wird das folgende Ergebnis erzielt.

Database version : 5.5.20-log

Wenn eine Verbindung mit der Datenquelle hergestellt wird, wird ein Verbindungsobjekt zurückgegeben und in gespeichert db zur weiteren Verwendung, sonst dbist auf Keine gesetzt. Nächster,db Objekt wird verwendet, um ein zu erstellen cursorObjekt, das wiederum zum Ausführen von SQL-Abfragen verwendet wird. Schließlich wird vor dem Herauskommen sichergestellt, dass die Datenbankverbindung geschlossen und Ressourcen freigegeben werden.

Erstellen einer Datenbanktabelle

Sobald eine Datenbankverbindung hergestellt ist, können wir mithilfe von Tabellen oder Datensätzen in die Datenbanktabellen erstellen execute Methode des erstellten Cursors.

Beispiel

Lassen Sie uns eine Datenbanktabelle erstellen. MITARBEITER -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

INSERT-Betrieb

Die INSERT-Operation ist erforderlich, wenn Sie Ihre Datensätze in einer Datenbanktabelle erstellen möchten.

Beispiel

Im folgenden Beispiel wird die SQL INSERT- Anweisung ausgeführt, um einen Datensatz in der Tabelle EMPLOYEE zu erstellen.

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Das obige Beispiel kann wie folgt geschrieben werden, um SQL-Abfragen dynamisch zu erstellen:

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
   LAST_NAME, AGE, SEX, INCOME) \
   VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Beispiel

Das folgende Codesegment ist eine andere Form der Ausführung, bei der Sie Parameter direkt übergeben können:

..................................
user_id = "test123"
password = "password"

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

LESEN Betrieb

READ Operation für eine beliebige Datenbank bedeutet, einige nützliche Informationen aus der Datenbank abzurufen.

Sobald die Datenbankverbindung hergestellt ist, können Sie eine Abfrage in dieser Datenbank durchführen. Sie können entweder verwendenfetchone() Methode zum Abrufen eines einzelnen Datensatzes oder fetchall() Methode zum Abrufen mehrerer Werte aus einer Datenbanktabelle.

• fetchone()- Es ruft die nächste Zeile einer Abfrageergebnismenge ab. Eine Ergebnismenge ist ein Objekt, das zurückgegeben wird, wenn ein Cursorobjekt zum Abfragen einer Tabelle verwendet wird.

• fetchall()- Es werden alle Zeilen in einer Ergebnismenge abgerufen. Wenn bereits einige Zeilen aus der Ergebnismenge extrahiert wurden, werden die verbleibenden Zeilen aus der Ergebnismenge abgerufen.

• rowcount - Dies ist ein schreibgeschütztes Attribut und gibt die Anzahl der Zeilen zurück, die von einer execute () -Methode betroffen waren.

Beispiel

Das folgende Verfahren fragt alle Datensätze aus der EMPLOYEE-Tabelle mit einem Gehalt von mehr als 1000 ab.

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE \
      WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
         (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# disconnect from server
db.close()

Ausgabe

Dies führt zu folgendem Ergebnis:

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

Vorgang aktualisieren

UPDATE Operation für eine beliebige Datenbank bedeutet, einen oder mehrere Datensätze zu aktualisieren, die bereits in der Datenbank verfügbar sind.

Das folgende Verfahren aktualisiert alle Datensätze mit SEX als 'M'. Hier erhöhen wir das ALTER aller Männchen um ein Jahr.

Beispiel

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

DELETE-Betrieb

Der Vorgang LÖSCHEN ist erforderlich, wenn Sie einige Datensätze aus Ihrer Datenbank löschen möchten. Im Folgenden wird beschrieben, wie Sie alle Datensätze von EMPLOYEE löschen, wenn das Alter mehr als 20 Jahre beträgt.

Beispiel

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Transaktionen durchführen

Transaktionen sind ein Mechanismus, der die Datenkonsistenz gewährleistet. Transaktionen haben die folgenden vier Eigenschaften:

• Atomicity - Entweder wird eine Transaktion abgeschlossen oder es passiert überhaupt nichts.

• Consistency - Eine Transaktion muss in einem konsistenten Zustand beginnen und das System in einem konsistenten Zustand belassen.

• Isolation - Zwischenergebnisse einer Transaktion sind außerhalb der aktuellen Transaktion nicht sichtbar.

• Durability - Sobald eine Transaktion festgeschrieben wurde, bleiben die Auswirkungen auch nach einem Systemausfall bestehen.

Die Python DB API 2.0 bietet zwei Methoden zum Festschreiben oder Zurücksetzen einer Transaktion.

Beispiel

Sie wissen bereits, wie Sie Transaktionen implementieren. Hier ist ein ähnliches Beispiel -

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

COMMIT-Betrieb

Commit ist eine Operation, die der Datenbank ein grünes Signal gibt, um die Änderungen abzuschließen. Nach dieser Operation kann keine Änderung zurückgesetzt werden.

Hier ist ein einfaches Beispiel, um das aufzurufen commit Methode.

db.commit()

ROLLBACK-Betrieb

Wenn Sie mit einer oder mehreren Änderungen nicht zufrieden sind und diese Änderungen vollständig zurücksetzen möchten, verwenden Sie die Option rollback() Methode.

Hier ist ein einfaches Beispiel, um das aufzurufen rollback() Methode.

db.rollback()

Datenbank trennen

Verwenden Sie die Methode close (), um die Datenbankverbindung zu trennen.

db.close()

Wenn die Verbindung zu einer Datenbank vom Benutzer mit der Methode close () geschlossen wird, werden alle ausstehenden Transaktionen von der Datenbank zurückgesetzt. Anstatt jedoch von den Implementierungsdetails der DB-Ebene abhängig zu sein, ist es besser, wenn Ihre Anwendung Commit oder Rollback explizit aufruft.

Fehler behandeln

Es gibt viele Fehlerquellen. Einige Beispiele sind ein Syntaxfehler in einer ausgeführten SQL-Anweisung, ein Verbindungsfehler oder das Aufrufen der Abrufmethode für ein bereits abgebrochenes oder abgeschlossenes Anweisungshandle.

Die DB-API definiert eine Reihe von Fehlern, die in jedem Datenbankmodul vorhanden sein müssen. In der folgenden Tabelle sind diese Ausnahmen aufgeführt.

Sr.Nr.	Ausnahme & Beschreibung
1	Warning Wird für nicht schwerwiegende Probleme verwendet. Muss StandardError unterordnen.
2	Error Basisklasse für Fehler. Muss StandardError unterordnen.
3	InterfaceError Wird für Fehler im Datenbankmodul verwendet, nicht in der Datenbank selbst. Muss Fehler der Unterklasse.
4	DatabaseError Wird für Fehler in der Datenbank verwendet. Muss Fehler der Unterklasse.
5	DataError Unterklasse von DatabaseError, die auf Fehler in den Daten verweist.
6	OperationalError Unterklasse von DatabaseError, die auf Fehler wie den Verlust einer Verbindung zur Datenbank verweist. Diese Fehler liegen im Allgemeinen außerhalb der Kontrolle des Python-Scripters.
7	IntegrityError Unterklasse von DatabaseError für Situationen, die die relationale Integrität beeinträchtigen würden, z. B. Eindeutigkeitsbeschränkungen oder Fremdschlüssel.
8	InternalError Unterklasse von DatabaseError, die sich auf Fehler innerhalb des Datenbankmoduls bezieht, z. B. wenn ein Cursor nicht mehr aktiv ist.
9	ProgrammingError Unterklasse von DatabaseError, die sich auf Fehler wie einen falschen Tabellennamen und andere Dinge bezieht, die Ihnen sicher angelastet werden können.
10	NotSupportedError Unterklasse von DatabaseError, die sich auf den Versuch bezieht, nicht unterstützte Funktionen aufzurufen.

Ihre Python-Skripte sollten diese Fehler behandeln. Bevor Sie jedoch eine der oben genannten Ausnahmen verwenden, stellen Sie sicher, dass Ihre MySQLdb diese Ausnahme unterstützt. Weitere Informationen dazu erhalten Sie in der DB API 2.0-Spezifikation.