Python - Accesso al database MySQL

Lo standard Python per le interfacce di database è l'API DB Python. La maggior parte delle interfacce di database Python aderisce a questo standard.

Puoi scegliere il database giusto per la tua applicazione. Python Database API supporta un'ampia gamma di server database come:

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

Ecco l'elenco delle interfacce di database Python disponibili: Interfacce di database Python e API . Devi scaricare un modulo API DB separato per ogni database a cui devi accedere. Ad esempio, se è necessario accedere a un database Oracle nonché a un database MySQL, è necessario scaricare sia i moduli di database Oracle che MySQL.

L'API DB fornisce uno standard minimo per lavorare con i database utilizzando strutture e sintassi Python ove possibile. Questa API include quanto segue:

Importazione del modulo API.
Acquisizione di una connessione con il database.
Emissione di istruzioni SQL e stored procedure.
Chiusura della connessione

Impareremmo tutti i concetti usando MySQL, quindi parliamo del modulo MySQLdb.

Cos'è MySQLdb?

MySQLdb è un'interfaccia per la connessione a un server di database MySQL da Python. Implementa l'API del database Python v2.0 ed è basata sull'API C di MySQL.

Come installo MySQLdb?

Prima di procedere, assicurati di avere MySQLdb installato sulla tua macchina. Basta digitare quanto segue nel tuo script Python ed eseguirlo -

#!/usr/bin/python

import MySQLdb

Se produce il seguente risultato, significa che il modulo MySQLdb non è installato -

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

Per installare il modulo MySQLdb, usa il seguente comando:

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 - Assicurati di avere i privilegi di root per installare il modulo sopra.

Connessione al database

Prima di connetterti a un database MySQL, assicurati di quanto segue:

Hai creato un database TESTDB.
Hai creato una tabella EMPLOYEE in TESTDB.
Questa tabella ha i campi FIRST_NAME, LAST_NAME, AGE, SEX e INCOME.
L'ID utente "testuser" e la password "test123" sono impostati per accedere a TESTDB.
Il modulo Python MySQLdb è installato correttamente sulla tua macchina.
Hai seguito il tutorial di MySQL per comprendere le basi di MySQL.

Esempio

Di seguito è riportato l'esempio di connessione con il database 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()

Durante l'esecuzione di questo script, produce il seguente risultato nella mia macchina Linux.

Database version : 5.0.45

Se viene stabilita una connessione con l'origine dati, viene restituito un oggetto connessione e salvato in db per un ulteriore utilizzo, altrimenti dbè impostato su Nessuno. Il prossimo,db oggetto viene utilizzato per creare un file cursoroggetto, che a sua volta viene utilizzato per eseguire query SQL. Infine, prima di uscire, assicura che la connessione al database sia chiusa e le risorse vengano rilasciate.

Creazione della tabella del database

Una volta stabilita una connessione al database, siamo pronti per creare tabelle o record nelle tabelle del database utilizzando execute metodo del cursore creato.

Esempio

Creiamo la tabella del database DIPENDENTE -

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

Operazione INSERT

È necessario quando si desidera creare i record in una tabella di database.

Esempio

Il seguente esempio, esegue l' istruzione SQL INSERT per creare un record nella tabella 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()

L'esempio sopra può essere scritto come segue per creare query SQL dinamicamente:

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

Esempio

Il seguente segmento di codice è un'altra forma di esecuzione in cui è possibile passare direttamente i parametri -

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

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

Operazione READ

READ Operare su qualsiasi database significa prelevare alcune informazioni utili dal database.

Una volta stabilita la connessione al database, sei pronto per eseguire una query in questo database. Puoi usare entrambifetchone() metodo per recuperare un singolo record o fetchall() metodo per fetech più valori da una tabella di database.

fetchone()- Recupera la riga successiva di un set di risultati di query. Un set di risultati è un oggetto restituito quando un oggetto cursore viene utilizzato per interrogare una tabella.
fetchall()- Recupera tutte le righe in un set di risultati. Se alcune righe sono già state estratte dal set di risultati, recupera le righe rimanenti dal set di risultati.
rowcount - Questo è un attributo di sola lettura e restituisce il numero di righe che sono state influenzate da un metodo execute ().

Esempio

La seguente procedura interroga tutti i record dalla tabella DIPENDENTE con stipendio superiore a 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()

Questo produrrà il seguente risultato:

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

Operazione di aggiornamento

AGGIORNAMENTO Operare su un qualsiasi database significa aggiornare uno o più record, già disponibili nel database.

La seguente procedura aggiorna tutti i record con SEX come 'M'. Qui, aumentiamo l'ETÀ di tutti i maschi di un anno.

Esempio

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

Operazione DELETE

L'operazione DELETE è necessaria quando si desidera eliminare alcuni record dal database. Di seguito è riportata la procedura per eliminare tutti i record da DIPENDENTE in cui ETÀ è superiore a 20 -

Esempio

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

Esecuzione di transazioni

Le transazioni sono un meccanismo che garantisce la coerenza dei dati. Le transazioni hanno le seguenti quattro proprietà:

Atomicity - O una transazione viene completata o non accade nulla.
Consistency - Una transazione deve iniziare in uno stato coerente e lasciare il sistema in uno stato coerente.
Isolation - I risultati intermedi di una transazione non sono visibili al di fuori della transazione corrente.
Durability - Una volta eseguito il commit di una transazione, gli effetti sono persistenti, anche dopo un errore di sistema.

Python DB API 2.0 fornisce due metodi per eseguire il commit o il rollback di una transazione.

Esempio

Sai già come implementare le transazioni. Ecco di nuovo un esempio simile:

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

Operazione COMMIT

Commit è l'operazione, che dà un segnale verde al database per finalizzare le modifiche e dopo questa operazione nessuna modifica può essere ripristinata.

Ecco un semplice esempio da chiamare commit metodo.

db.commit()

Operazione ROLLBACK

Se non sei soddisfatto di una o più modifiche e desideri ripristinarle completamente, utilizza rollback() metodo.

Ecco un semplice esempio da chiamare rollback() metodo.

db.rollback()

Disconnessione del database

Per disconnettere la connessione al database, utilizzare il metodo close ().

db.close()

Se la connessione a un database viene chiusa dall'utente con il metodo close (), tutte le transazioni in sospeso vengono annullate dal DB. Tuttavia, invece di dipendere da uno qualsiasi dei dettagli di implementazione di livello inferiore del DB, l'applicazione farebbe meglio a chiamare esplicitamente il commit o il rollback.

Gestione degli errori

Esistono molte fonti di errore. Alcuni esempi sono un errore di sintassi in un'istruzione SQL eseguita, un errore di connessione o la chiamata del metodo fetch per un handle di istruzione già annullato o finito.

L'API DB definisce una serie di errori che devono esistere in ogni modulo di database. La tabella seguente elenca queste eccezioni.

Sr.No.	Eccezione e descrizione
1	Warning Utilizzato per problemi non fatali. Deve essere una sottoclasse StandardError.
2	Error Classe base per errori. Deve essere una sottoclasse StandardError.
3	InterfaceError Utilizzato per errori nel modulo database, non nel database stesso. Errore di sottoclasse.
4	DatabaseError Utilizzato per errori nel database. Errore di sottoclasse.
5	DataError Sottoclasse di DatabaseError che fa riferimento a errori nei dati.
6	OperationalError Sottoclasse di DatabaseError che fa riferimento a errori come la perdita di una connessione al database. Questi errori sono generalmente fuori dal controllo dello scripter Python.
7	IntegrityError Sottoclasse di DatabaseError per situazioni che danneggerebbero l'integrità relazionale, come vincoli di unicità o chiavi esterne.
8	InternalError Sottoclasse di DatabaseError che fa riferimento a errori interni al modulo database, come ad esempio un cursore non più attivo.
9	ProgrammingError Sottoclasse di DatabaseError che fa riferimento a errori come un nome di tabella errato e altre cose che possono essere tranquillamente incolpate su di te.
10	NotSupportedError Sottoclasse di DatabaseError che fa riferimento al tentativo di chiamare funzionalità non supportate.

I tuoi script Python dovrebbero gestire questi errori, ma prima di usare una qualsiasi delle eccezioni precedenti, assicurati che MySQLdb abbia il supporto per quell'eccezione. Puoi ottenere maggiori informazioni su di loro leggendo la specifica DB API 2.0.

↰ Previous page Next page ↱

Python - Accesso al database MySQL

Cos'è MySQLdb?

Come installo MySQLdb?

Connessione al database

Esempio

Creazione della tabella del database

Esempio

Operazione INSERT

Esempio

Esempio

Operazione READ

Esempio

Operazione di aggiornamento

Esempio

Operazione DELETE

Esempio

Esecuzione di transazioni

Esempio

Operazione COMMIT

Operazione ROLLBACK

Disconnessione del database

Gestione degli errori

Tutorial avanzato di Python

Tutorial di base su Python

Risorse utili di Python