Python - acesso ao banco de dados MySQL

O padrão Python para interfaces de banco de dados é o Python DB-API. A maioria das interfaces de banco de dados Python adere a esse padrão.

Você pode escolher o banco de dados certo para seu aplicativo. A API de banco de dados Python oferece suporte a uma ampla variedade de servidores de banco de dados, como -

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

Aqui está a lista de interfaces de banco de dados Python disponíveis: Interfaces de banco de dados Python e APIs . Você deve baixar um módulo de API de banco de dados separado para cada banco de dados que você precisa acessar. Por exemplo, se você precisa acessar um banco de dados Oracle, bem como um banco de dados MySQL, você deve baixar os módulos de banco de dados Oracle e MySQL.

A API DB fornece um padrão mínimo para trabalhar com bancos de dados usando estruturas e sintaxe Python sempre que possível. Esta API inclui o seguinte -

Importando o módulo API.
Adquirindo uma conexão com o banco de dados.
Emissão de instruções SQL e procedimentos armazenados.
Fechando a conexão

Aprenderíamos todos os conceitos usando MySQL, então vamos falar sobre o módulo MySQLdb.

O que é MySQLdb?

MySQLdb é uma interface para conexão com um servidor de banco de dados MySQL do Python. Ele implementa a API de banco de dados Python v2.0 e é baseado na API C do MySQL.

Como faço para instalar o MySQLdb?

Antes de continuar, certifique-se de ter o MySQLdb instalado em sua máquina. Basta digitar o seguinte em seu script Python e executá-lo -

#!/usr/bin/python

import MySQLdb

Se produzir o seguinte resultado, significa que o módulo MySQLdb não está instalado -

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

Para instalar o módulo MySQLdb, use o seguinte 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 - Certifique-se de ter privilégios de root para instalar o módulo acima.

Conexão de banco de dados

Antes de se conectar a um banco de dados MySQL, certifique-se do seguinte -

Você criou um banco de dados TESTDB.
Você criou uma tabela EMPLOYEE em TESTDB.
Esta tabela contém os campos FIRST_NAME, LAST_NAME, AGE, SEX e INCOME.
O ID do usuário "testuser" e a senha "test123" são definidos para acessar o TESTDB.
O módulo Python MySQLdb está instalado corretamente em sua máquina.
Você passou pelo tutorial do MySQL para entender os fundamentos do MySQL.

Exemplo

A seguir está o exemplo de conexão com o banco de dados 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()

Ao executar este script, ele está produzindo o seguinte resultado em minha máquina Linux.

Database version : 5.0.45

Se uma conexão for estabelecida com a fonte de dados, um objeto de conexão é retornado e salvo em db para uso posterior, caso contrário dbestá definido como Nenhum. Próximo,db objeto é usado para criar um cursorobjeto, que por sua vez é usado para executar consultas SQL. Por fim, antes de sair, ele garante que a conexão com o banco de dados seja fechada e os recursos liberados.

Criando Tabela de Banco de Dados

Uma vez que uma conexão de banco de dados é estabelecida, estamos prontos para criar tabelas ou registros nas tabelas de banco de dados usando execute método do cursor criado.

Exemplo

Vamos criar a tabela de banco de dados 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()

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

Operação INSERT

É necessário quando você deseja criar seus registros em uma tabela de banco de dados.

Exemplo

O exemplo a seguir executa a instrução SQL INSERT para criar um registro na tabela 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()

O exemplo acima pode ser escrito da seguinte forma para criar consultas 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()

Exemplo

Seguir o segmento de código é outra forma de execução onde você pode passar parâmetros diretamente -

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

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

Operação READ

A operação READ em qualquer banco de dados significa buscar algumas informações úteis do banco de dados.

Assim que nossa conexão com o banco de dados for estabelecida, você estará pronto para fazer uma consulta a este banco de dados. Você pode usar qualquerfetchone() método para buscar um único registro ou fetchall() método para obter vários valores de uma tabela de banco de dados.

fetchone()- Ele busca a próxima linha de um conjunto de resultados de consulta. Um conjunto de resultados é um objeto retornado quando um objeto cursor é usado para consultar uma tabela.
fetchall()- Ele busca todas as linhas em um conjunto de resultados. Se algumas linhas já foram extraídas do conjunto de resultados, ele recupera as linhas restantes do conjunto de resultados.
rowcount - Este é um atributo somente leitura e retorna o número de linhas que foram afetadas por um método execute ().

Exemplo

O procedimento a seguir consulta todos os registros da tabela EMPLOYEE com salário superior 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()

Isso produzirá o seguinte resultado -

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

Operação de atualização

ATUALIZAR Operação em qualquer banco de dados significa atualizar um ou mais registros, que já estão disponíveis no banco de dados.

O procedimento a seguir atualiza todos os registros tendo SEXO como 'M'. Aqui, aumentamos a IDADE de todos os homens em um ano.

Exemplo

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

Operação DELETE

A operação DELETE é necessária quando você deseja excluir alguns registros de seu banco de dados. A seguir está o procedimento para deletar todos os registros do FUNCIONÁRIO onde AGE é maior que 20 -

Exemplo

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

Realizando transações

As transações são um mecanismo que garante a consistência dos dados. As transações têm as quatro propriedades a seguir -

Atomicity - Ou uma transação é concluída ou nada acontece.
Consistency - Uma transação deve começar em um estado consistente e deixar o sistema em um estado consistente.
Isolation - Os resultados intermediários de uma transação não são visíveis fora da transação atual.
Durability - Depois que uma transação foi confirmada, os efeitos são persistentes, mesmo após uma falha do sistema.

O Python DB API 2.0 fornece dois métodos para confirmar ou reverter uma transação.

Exemplo

Você já sabe como implementar transações. Aqui está novamente um exemplo semelhante -

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

Operação COMMIT

Commit é a operação, que dá um sinal verde ao banco de dados para finalizar as alterações e, após esta operação, nenhuma alteração pode ser revertida.

Aqui está um exemplo simples para chamar commit método.

db.commit()

Operação ROLLBACK

Se você não estiver satisfeito com uma ou mais das mudanças e quiser reverter essas mudanças completamente, use rollback() método.

Aqui está um exemplo simples para chamar rollback() método.

db.rollback()

Desconectando banco de dados

Para desconectar a conexão do banco de dados, use o método close ().

db.close()

Se a conexão com um banco de dados for fechada pelo usuário com o método close (), todas as transações pendentes serão revertidas pelo banco de dados. No entanto, em vez de depender de qualquer um dos detalhes de implementação de nível inferior do banco de dados, seu aplicativo ficaria melhor chamando commit ou rollback explicitamente.

Tratamento de erros

Existem muitas fontes de erros. Alguns exemplos são um erro de sintaxe em uma instrução SQL executada, uma falha de conexão ou chamada do método fetch para um identificador de instrução já cancelado ou concluído.

A API DB define uma série de erros que devem existir em cada módulo de banco de dados. A tabela a seguir lista essas exceções.

Sr. Não.	Exceção e descrição
1	Warning Usado para problemas não fatais. Deve subclasse StandardError.
2	Error Classe base para erros. Deve subclasse StandardError.
3	InterfaceError Usado para erros no módulo de banco de dados, não no próprio banco de dados. Deve subclasse Error.
4	DatabaseError Usado para erros no banco de dados. Deve subclasse Error.
5	DataError Subclasse de DatabaseError que se refere a erros nos dados.
6	OperationalError Subclasse de DatabaseError que se refere a erros como a perda de uma conexão com o banco de dados. Esses erros geralmente estão fora do controle do criador de scripts Python.
7	IntegrityError Subclasse de DatabaseError para situações que danificariam a integridade relacional, como restrições de exclusividade ou chaves estrangeiras.
8	InternalError Subclasse de DatabaseError que se refere a erros internos ao módulo de banco de dados, como um cursor não estar mais ativo.
9	ProgrammingError Subclasse de DatabaseError que se refere a erros como um nome de tabela incorreto e outras coisas que podem ser atribuídas a você com segurança.
10	NotSupportedError Subclasse de DatabaseError que se refere à tentativa de chamar uma funcionalidade sem suporte.

Seus scripts Python devem lidar com esses erros, mas antes de usar qualquer uma das exceções acima, certifique-se de que seu MySQLdb tem suporte para essa exceção. Você pode obter mais informações sobre eles lendo a especificação DB API 2.0.