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

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

Python tem um suporte embutido para SQLite. Nesta seção, aprenderíamos todos os conceitos usando MySQL. O módulo MySQLdb, uma interface popular com MySQL, não é compatível com Python 3. Em vez disso, devemos usar o módulo PyMySQL .

O que é PyMySQL?

PyMySQL é uma interface para conexão a um servidor de banco de dados MySQL do Python. Ele implementa a API de banco de dados Python v2.0 e contém uma biblioteca de cliente MySQL em Python puro. O objetivo do PyMySQL é ser um substituto imediato para o MySQLdb.

Como faço para instalar o PyMySQL?

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

#!/usr/bin/python3

import pymysql

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

A última versão estável está disponível no PyPI e pode ser instalada com pip -

pip install pymysql

Alternativamente (por exemplo, se o pip não estiver disponível), um tarball pode ser baixado do GitHub e instalado com Setuptools da seguinte maneira -

$ # 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 - 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 dos seguintes pontos -

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.
Módulo Python PyMySQL está instalado corretamente em sua máquina.
Você passou pelo tutorial do MySQL para entender os fundamentos do MySQL.

Exemplo

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

Ao executar este script, ele produz o seguinte resultado.

Database version : 5.5.20-log

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. Finalmente, antes de sair, ele garante que a conexão com o banco de dados seja fechada e os recursos sejam 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 uma tabela de banco de dados 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()

# 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

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

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

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

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

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

Assim que a conexão com o banco de dados for estabelecida, você estará pronto para fazer uma consulta a esse banco de dados. Você pode usar qualquerfetchone() método para buscar um único registro ou fetchall() método para buscar 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/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()

Resultado

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

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

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á 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 é uma 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 o 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 o rollback() método.

Aqui está um exemplo simples para chamar o 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 seria melhor chamar 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.

↰ Previous page Next page ↱