Acesso a Banco de Dados MySQL com Python: Usando DB-API Padrão

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 este padrão.

Você pode escolher o banco de dados certo para sua aplicação. A Python Database API suporta uma ampla gama de servidores de banco de dados, tais 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: Python Database Interfaces and APIs. Você deve baixar um módulo DB API separado para cada banco de dados que precisa acessar. Por exemplo, se você precisa acessar um banco de dados Oracle e também um MySQL, deve baixar tanto o módulo Oracle quanto o MySQL.

A DB API 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:

Importar o módulo da API.
Adquirir uma conexão com o banco de dados.
Emitir instruções SQL e stored procedures.
Fechar a conexão.

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

O que é MySQLdb?

MySQLdb é uma interface para conectar a um servidor de banco de dados MySQL a partir do Python. Ele implementa a Python Database API v2.0 e é construído sobre a MySQL C API.

Como Instalar o MySQLdb?

Antes de prosseguir, certifique-se de ter o MySQLdb instalado em sua máquina. Digite o seguinte em seu script Python e execute-o:

#!/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 
    import MySQLdb
ImportError: No module named MySQLdb

Para instalar o módulo MySQLdb, use o seguinte comando:

Para Ubuntu, use o seguinte comando:
$ sudo apt-get install python-pip python-dev libmysqlclient-dev

Para Fedora, use o seguinte comando:
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc

Para prompt de comando Python, use o seguinte comando:
pip install MySQL-python

Nota - Certifique-se de ter privilégios de root para instalar o módulo acima.

Conexão com Banco de Dados

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

Você criou um banco de dados TESTDB.
Você criou uma tabela EMPLOYEE no TESTDB.
Esta tabela tem campos FIRST_NAME, LAST_NAME, AGE, SEX e INCOME.
User ID "testuser" e senha "test123" estão definidos para acessar TESTDB.
Módulo Python MySQLdb está instalado corretamente em sua máquina.
Você passou pelo tutorial MySQL para entender o básico do MySQL.

Exemplo

Segue o exemplo de conexão com o banco de dados MySQL "TESTDB":

#!/usr/bin/python
import MySQLdb

# Abre conexão com banco de dados
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Prepara um objeto cursor usando o método cursor()
cursor = db.cursor()

# Executa consulta SQL usando o método execute()
cursor.execute("SELECT VERSION()")

# Busca uma única linha usando o método fetchone()
data = cursor.fetchone()
print("Database version : %s" % data)

# Desconecta do servidor
db.close()

Ao executar este script, ele produz o seguinte resultado na minha máquina Linux:

Database version : 5.0.45

Se uma conexão for estabelecida com a fonte de dados, um Connection Object é retornado e salvo em db para uso posterior; caso contrário, db é definido como None. Em seguida, o objeto db é usado para criar um objeto cursor, que por sua vez é usado para executar consultas SQL. Finalmente, antes de sair, garante que a conexão com o banco de dados seja fechada e os recursos liberados.

Criando Tabela no Banco de Dados

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

Exemplo

Vamos criar a tabela de banco de dados EMPLOYEE:

#!/usr/bin/python
import MySQLdb

# Abre conexão com banco de dados
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Prepara um objeto cursor usando o método cursor()
cursor = db.cursor()

# Remove tabela se já existir usando o método execute()
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Cria tabela conforme necessário
sql = """CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT )"""

cursor.execute(sql)

# Desconecta do servidor
db.close()

Operação INSERT

É necessária 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

# Abre conexão com banco de dados
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Prepara um objeto cursor usando o método cursor()
cursor = db.cursor()

# Prepara consulta SQL para INSERIR um registro no banco de dados
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
    # Executa o comando SQL
    cursor.execute(sql)
    # Confirma suas alterações no banco de dados
    db.commit()
except:
    # Rollback em caso de erro
    db.rollback()

# Desconecta do servidor
db.close()

O exemplo acima pode ser escrito da seguinte forma para criar consultas SQL dinamicamente:

#!/usr/bin/python
import MySQLdb

# Abre conexão com banco de dados
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Prepara um objeto cursor usando o método cursor()
cursor = db.cursor()

# Prepara consulta SQL para INSERIR um registro no banco de dados
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('%s', '%s', '%d', '%c', '%d')" % ('Mac', 'Mohan', 20, 'M', 2000)

try:
    # Executa o comando SQL
    cursor.execute(sql)
    # Confirma suas alterações no banco de dados
    db.commit()
except:
    # Rollback em caso de erro
    db.rollback()

# Desconecta do servidor
db.close()

Exemplo

O segmento de código a seguir é 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

Operação READ em qualquer banco de dados significa buscar alguma informação útil do banco de dados.

Uma vez que nossa conexão com o banco de dados está estabelecida, você está pronto para fazer uma consulta neste banco de dados. Você pode usar o método fetchone() para buscar um único registro ou o método fetchall() para buscar múltiplos valores de uma tabela de banco de dados.

fetchone() - 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() - 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 afetadas por um método execute().

Exemplo

O procedimento a seguir consulta todos os registros da tabela EMPLOYEE com salário maior que 1000:

#!/usr/bin/python
import MySQLdb

# Abre conexão com banco de dados
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Prepara um objeto cursor usando o método cursor()
cursor = db.cursor()

sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)

try:
    # Executa o comando SQL
    cursor.execute(sql)
    # Busca todas as linhas em uma lista de listas
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # Agora imprime o resultado buscado
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")

# Desconecta do servidor
db.close()

Isso produzirá o seguinte resultado:

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

Operação UPDATE

Operação UPDATE 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 com SEX como 'M'. Aqui, aumentamos a AGE de todos os homens em um ano.

Exemplo

#!/usr/bin/python
import MySQLdb

# Abre conexão com banco de dados
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Prepara um objeto cursor usando o método cursor()
cursor = db.cursor()

# Prepara consulta SQL para ATUALIZAR registros necessários
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')

try:
    # Executa o comando SQL
    cursor.execute(sql)
    # Confirma suas alterações no banco de dados
    db.commit()
except:
    # Rollback em caso de erro
    db.rollback()

# Desconecta do servidor
db.close()

Operação DELETE

Operação DELETE é necessária quando você deseja excluir alguns registros do seu banco de dados. Segue o procedimento para excluir todos os registros de EMPLOYEE onde AGE é maior que 20:

Exemplo

#!/usr/bin/python
import MySQLdb

# Abre conexão com banco de dados
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Prepara um objeto cursor usando o método cursor()
cursor = db.cursor()

# Prepara consulta SQL para EXCLUIR registros necessários
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)

try:
    # Executa o comando SQL
    cursor.execute(sql)
    # Confirma suas alterações no banco de dados
    db.commit()
except:
    # Rollback em caso de erro
    db.rollback()

# Desconecta do servidor
db.close()

Realizando Transações

Transações são um mecanismo que garante consistência de dados. Transações têm as seguintes quatro propriedades:

Atomicidade - Ou uma transação completa ou nada acontece.
Consistência - Uma transação deve começar em um estado consistente e deixar o sistema em um estado consistente.
Isolamento - Resultados intermediários de uma transação não são visíveis fora da transação atual.
Durabilidade - Uma vez que uma transação foi confirmada, os efeitos são persistentes, mesmo após uma falha do sistema.

A Python DB API 2.0 fornece dois métodos para commit ou rollback de uma transação.

Exemplo

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

# Prepara consulta SQL para EXCLUIR registros necessários
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)

try:
    # Executa o comando SQL
    cursor.execute(sql)
    # Confirma suas alterações no banco de dados
    db.commit()
except:
    # Rollback em caso de erro
    db.rollback()

Operação COMMIT

Commit é a operação que dá 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 o método commit:

db.commit()

Operação ROLLBACK

Se você não está satisfeito com uma ou mais das alterações e deseja revertê-las completamente, use o método rollback().

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

db.rollback()

Desconectando Banco de Dados

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

db.close()

Se a conexão com um banco de dados é fechada pelo usuário com o método close(), quaisquer transações pendentes são desfeitas pelo DB. No entanto, em vez de depender de quaisquer detalhes de implementação de nível inferior do DB, sua aplicação seria melhor servida chamando commit ou rollback explicitamente.

Tratamento de Erros

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

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

Nº	Exceção e Descrição
1	Warning Usado para problemas não fatais. Deve ser subclasse de StandardError.
2	Error Classe base para erros. Deve ser subclasse de StandardError.
3	InterfaceError Usado para erros no módulo de banco de dados, não no próprio banco de dados. Deve ser subclasse de Error.
4	DatabaseError Usado para erros no banco de dados. Deve ser subclasse de Error.
5	DataError Subclasse de DatabaseError que se refere a erros nos dados.
6	OperationalError Subclasse de DatabaseError que se refere a erros como perda de conexão com o banco de dados. Esses erros estão geralmente fora do controle do scripter Python.
7	IntegrityError Subclasse de DatabaseError para situações que danificariam a integridade relacional, como restrições de unicidade 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 mais ativo.
9	ProgrammingError Subclasse de DatabaseError que se refere a erros como nome de tabela ruim e outras coisas que podem ser atribuídas a você.
10	NotSupportedError Subclasse de DatabaseError que se refere a tentar chamar funcionalidade não suportada.

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

Expressões Regulares em Python: Guia Prático Programação de Rede em Python: Sockets e Protocolos Avançados

python