Python 3 - 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
SQLite

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

Python ha un supporto integrato per SQLite. In questa sezione, impareremo tutti i concetti usando MySQL. Il modulo MySQLdb, una popolare interfaccia con MySQL non è compatibile con Python 3. Invece, useremo il modulo PyMySQL .

Cos'è PyMySQL?

PyMySQL è un'interfaccia per la connessione a un server di database MySQL da Python. Implementa l'API del database Python v2.0 e contiene una libreria client MySQL Python puro. L'obiettivo di PyMySQL è quello di essere un sostituto immediato di MySQLdb.

Come installo PyMySQL?

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

#!/usr/bin/python3

import pymysql

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 pymysql
ImportError: No module named pymysql

L'ultima versione stabile è disponibile su PyPI e può essere installata con pip -

pip install pymysql

In alternativa (ad esempio se pip non è disponibile), è possibile scaricare un tarball da GitHub e installarlo con Setuptools come segue:

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

Connessione al database

Prima di connetterti a un database MySQL, assicurati dei seguenti punti:

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 PyMySQL è installato correttamente sulla tua macchina.
Hai seguito il tutorial su MySQL per comprendere le basi di MySQL.

Esempio

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

Durante l'esecuzione di questo script, produce il seguente risultato.

Database version : 5.5.20-log

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 una tabella database DIPENDENTE -

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

Operazione INSERT

L'operazione INSERT è richiesta 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/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()

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

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

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 recuperare 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/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()

Produzione

Questo produrrà il seguente risultato:

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

Operazione di aggiornamento

AGGIORNAMENTO Operare su 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 di un anno l'ETÀ di tutti i maschi.

Esempio

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

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

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 che una transazione è stata confermata, 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 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

Il commit è un'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 per chiamare il file commit metodo.

db.commit()

Operazione ROLLBACK

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

Ecco un semplice esempio per chiamare il file 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 database, 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 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 3 - Accesso al database MySQL

Cos'è PyMySQL?

Come installo PyMySQL?

Connessione al database

Esempio

Creazione della tabella del database

Esempio

Operazione INSERT

Esempio

Esempio

Operazione READ

Esempio

Produzione

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 3

Tutorial di base su Python 3

Risorse utili di Python 3