Python - dostęp do bazy danych MySQL

Standardem Pythona dla interfejsów baz danych jest Python DB-API. Większość interfejsów baz danych Pythona jest zgodnych z tym standardem.

Możesz wybrać odpowiednią bazę danych dla swojej aplikacji. Python Database API obsługuje szeroką gamę serwerów baz danych, takich jak -

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

Oto lista dostępnych interfejsów baz danych Pythona : Python Database Interfaces and APIs . Musisz pobrać oddzielny moduł DB API dla każdej bazy danych, do której chcesz uzyskać dostęp. Na przykład, jeśli potrzebujesz dostępu do bazy danych Oracle, a także do bazy danych MySQL, musisz pobrać zarówno moduły bazy danych Oracle, jak i MySQL.

DB API zapewnia minimalny standard pracy z bazami danych przy użyciu struktur i składni Pythona, jeśli tylko jest to możliwe. Ten interfejs API obejmuje następujące -

• Importowanie modułu API.
• Uzyskanie połączenia z bazą danych.
• Wydawanie instrukcji SQL i procedur składowanych.
• Zamykanie połączenia

Uczylibyśmy się wszystkich pojęć używając MySQL, więc porozmawiajmy o module MySQLdb.

Co to jest MySQLdb?

MySQLdb to interfejs do łączenia się z serwerem bazy danych MySQL z języka Python. Implementuje Python Database API v2.0 i jest zbudowany na bazie MySQL C. API.

Jak zainstalować MySQLdb?

Zanim przejdziesz dalej, upewnij się, że masz zainstalowaną MySQLdb na swoim komputerze. Po prostu wpisz następujące polecenie w swoim skrypcie Pythona i uruchom go -

#!/usr/bin/python

import MySQLdb

Jeśli daje następujący wynik, oznacza to, że moduł MySQLdb nie jest zainstalowany -

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

Aby zainstalować moduł MySQLdb, użyj następującego polecenia -

For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev For Fedora, use the following command - $ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

Note - Upewnij się, że masz uprawnienia roota do zainstalowania powyższego modułu.

Połączenie z bazą danych

Przed połączeniem się z bazą danych MySQL upewnij się, że:

• Utworzyłeś bazę danych TESTDB.

• Utworzyłeś tabelę PRACOWNIK w TESTDB.

• Ta tabela zawiera pola FIRST_NAME, LAST_NAME, AGE, SEX i INCOME.

• ID użytkownika „testuser” i hasło „test123” są ustawione na dostęp do bazy danych TESTDB.

• Moduł Pythona MySQLdb jest poprawnie zainstalowany na Twoim komputerze.

• Przeszedłeś przez samouczek MySQL, aby zrozumieć podstawy MySQL.

Przykład

Poniżej znajduje się przykład połączenia z bazą danych MySQL „TESTDB”

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

Podczas uruchamiania tego skryptu generuje następujący wynik na moim komputerze z systemem Linux.

Database version : 5.0.45

Jeśli połączenie zostanie nawiązane ze źródłem danych, obiekt połączenia jest zwracany i zapisywany w db do dalszego użytku, w przeciwnym razie dbjest ustawiona na Brak. Kolejny,db obiekt służy do tworzenia pliku cursorobiekt, który z kolei służy do wykonywania zapytań SQL. Wreszcie, przed wyjściem, zapewnia zamknięcie połączenia z bazą danych i zwolnienie zasobów.

Tworzenie tabeli bazy danych

Po nawiązaniu połączenia z bazą danych jesteśmy gotowi do tworzenia tabel lub rekordów w tabelach bazy danych za pomocą execute metoda utworzonego kursora.

Przykład

Stwórzmy tabelę bazy danych PRACOWNIK -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

Operacja INSERT

Jest to wymagane, gdy chcesz utworzyć rekordy w tabeli bazy danych.

Przykład

Poniższy przykład wykonuje instrukcję SQL INSERT , aby utworzyć rekord w tabeli EMPLOYEE -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

Powyższy przykład można zapisać w następujący sposób, aby dynamicznie tworzyć zapytania SQL -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

Przykład

Następny segment kodu to kolejna forma wykonania, w której można bezpośrednio przekazywać parametry -

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

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

Operacja READ

READ Operacja na dowolnej bazie danych oznacza pobranie przydatnych informacji z bazy danych.

Po nawiązaniu połączenia z bazą danych możesz wykonać zapytanie do tej bazy danych. Możesz użyć jednego z nichfetchone() metoda pobierania pojedynczego rekordu lub fetchall() metoda pobierania wielu wartości z tabeli bazy danych.

• fetchone()- Pobiera następny wiersz zestawu wyników zapytania. Zestaw wyników to obiekt, który jest zwracany, gdy obiekt kursora jest używany do zapytania tabeli.

• fetchall()- Pobiera wszystkie wiersze w zestawie wyników. Jeśli niektóre wiersze zostały już wyodrębnione z zestawu wyników, pobiera pozostałe wiersze z zestawu wyników.

• rowcount - To jest atrybut tylko do odczytu i zwraca liczbę wierszy, na które wpłynęła metoda execute ().

Przykład

Poniższa procedura odpytuje wszystkie rekordy z tabeli PRACOWNIK z wynagrodzeniem powyżej 1000 -

#!/usr/bin/python

import MySQLdb

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

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

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 fecth data"

# disconnect from server
db.close()

To da następujący wynik -

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

Operacja aktualizacji

UPDATE Operacja na dowolnej bazie danych oznacza aktualizację jednego lub więcej rekordów, które są już dostępne w bazie danych.

Poniższa procedura aktualizuje wszystkie rekordy zawierające PŁEĆ jako 'M'. Tutaj zwiększamy WIEK wszystkich samców o jeden rok.

Przykład

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

Operacja USUŃ

Operacja DELETE jest wymagana, gdy chcesz usunąć niektóre rekordy z bazy danych. Poniżej znajduje się procedura usuwania wszystkich zapisów od PRACOWNIKA w wieku powyżej 20 lat -

Przykład

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

Wykonywanie transakcji

Transakcje to mechanizm zapewniający spójność danych. Transakcje mają następujące cztery właściwości -

• Atomicity - Albo transakcja zostaje zakończona, albo nic się nie dzieje.

• Consistency - Transakcja musi rozpocząć się w stanie zgodnym i pozostawić system w stanie zgodnym.

• Isolation - Pośrednie wyniki transakcji nie są widoczne poza bieżącą transakcją.

• Durability - Po zatwierdzeniu transakcji efekty są trwałe, nawet po awarii systemu.

Python DB API 2.0 zapewnia dwie metody zatwierdzania lub wycofywania transakcji.

Przykład

Wiesz już, jak realizować transakcje. Oto znowu podobny przykład -

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

Operacja COMMIT

Commit to operacja, która daje zielony sygnał do bazy danych w celu sfinalizowania zmian, a po tej operacji nie można cofnąć żadnej zmiany.

Oto prosty przykład do zadzwonienia commit metoda.

db.commit()

Operacja ROLLBACK

Jeśli nie jesteś zadowolony z co najmniej jednej zmiany i chcesz całkowicie cofnąć te zmiany, użyj rollback() metoda.

Oto prosty przykład do zadzwonienia rollback() metoda.

db.rollback()

Odłączanie bazy danych

Aby rozłączyć połączenie z bazą danych, użyj metody close ().

db.close()

Jeśli połączenie z bazą danych zostanie zamknięte przez użytkownika metodą close (), wszystkie oczekujące transakcje są wycofywane przez bazę danych. Jednak zamiast polegać na jakichkolwiek szczegółach implementacji DB niższego poziomu, twoja aplikacja będzie lepiej wywoływać jawne zatwierdzanie lub wycofywanie.

Obsługa błędów

Istnieje wiele źródeł błędów. Kilka przykładów to błąd składni w wykonywanej instrukcji SQL, błąd połączenia lub wywołanie metody pobierania dla już anulowanego lub zakończonego uchwytu instrukcji.

Interfejs API bazy danych definiuje liczbę błędów, które muszą występować w każdym module bazy danych. W poniższej tabeli wymieniono te wyjątki.

Sr.No.	Wyjątek i opis
1	Warning Używany w przypadku problemów, które nie są śmiertelne. Musi podklasę StandardError.
2	Error Klasa bazowa dla błędów. Musi podklasę StandardError.
3	InterfaceError Używane w przypadku błędów w module bazy danych, a nie w samej bazie danych. Musi podklasa Błąd.
4	DatabaseError Używane w przypadku błędów w bazie danych. Musi podklasa Błąd.
5	DataError Podklasa DatabaseError, która odnosi się do błędów w danych.
6	OperationalError Podklasa DatabaseError, która odnosi się do błędów, takich jak utrata połączenia z bazą danych. Te błędy są na ogół poza kontrolą skryptera języka Python.
7	IntegrityError Podklasa DatabaseError dla sytuacji, które mogłyby uszkodzić integralność relacji, takich jak ograniczenia unikalności lub klucze obce.
8	InternalError Podklasa DatabaseError, która odnosi się do błędów wewnętrznych modułu bazy danych, takich jak kursor nie jest już aktywny.
9	ProgrammingError Podklasa DatabaseError, która odnosi się do błędów, takich jak zła nazwa tabeli i inne rzeczy, za które można bezpiecznie obwiniać Ciebie.
10	NotSupportedError Podklasa DatabaseError, która odnosi się do próby wywołania nieobsługiwanej funkcjonalności.

Twoje skrypty Pythona powinny obsługiwać te błędy, ale przed użyciem któregokolwiek z powyższych wyjątków upewnij się, że Twoja baza danych MySQLdb obsługuje ten wyjątek. Możesz uzyskać więcej informacji na ich temat, czytając specyfikację DB API 2.0.