Persistência de dados Python - Módulo Sqlite3

Uma grande desvantagem dos arquivos CSV, JSON, XML, etc., é que eles não são muito úteis para acesso aleatório e processamento de transações porque são amplamente desestruturados por natureza. Portanto, torna-se muito difícil modificar o conteúdo.

Esses arquivos simples não são adequados para o ambiente cliente-servidor, pois não têm capacidade de processamento assíncrono. O uso de arquivos de dados não estruturados leva à redundância e inconsistência de dados.

Esses problemas podem ser superados usando um banco de dados relacional. Um banco de dados é uma coleção organizada de dados para remover redundância e inconsistência e manter a integridade dos dados. O modelo de banco de dados relacional é muito popular.

Seu conceito básico é organizar os dados em uma tabela de entidades (chamada de relação). A estrutura da tabela de entidades fornece um atributo cujo valor é exclusivo para cada linha. Esse atributo é chamado'primary key'.

Quando a chave primária de uma tabela aparece na estrutura de outras tabelas, ela é chamada 'Foreign key'e isso forma a base do relacionamento entre os dois. Com base neste modelo, existem muitos produtos RDBMS populares atualmente disponíveis -

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

SQLite é um banco de dados relacional leve usado em uma ampla variedade de aplicativos. É um mecanismo de banco de dados SQL transacional independente, sem servidor e com configuração zero. Todo o banco de dados é um único arquivo, que pode ser colocado em qualquer lugar do sistema de arquivos. É um software de código aberto, com pegada muito pequena e configuração zero. É popularmente usado em dispositivos embutidos, IOT e aplicativos móveis.

Todos os bancos de dados relacionais usam SQL para lidar com dados em tabelas. No entanto, anteriormente, cada um desses bancos de dados costumava ser conectado ao aplicativo Python com a ajuda do módulo Python específico para o tipo de banco de dados.

Portanto, havia falta de compatibilidade entre eles. Se um usuário quisesse mudar para um produto de banco de dados diferente, seria difícil. Este problema de incompatibilidade foi solucionado levantando 'Python Enhancement Proposal (PEP 248)' para recomendar uma interface consistente para bancos de dados relacionais conhecidos como DB-API. As últimas recomendações são chamadasDB-APIVersão 2.0. (PEP 249)

A biblioteca padrão do Python consiste no módulo sqlite3, que é um módulo compatível com DB-API para lidar com o banco de dados SQLite através do programa Python. Este capítulo explica a conectividade do Python com o banco de dados SQLite.

Conforme mencionado anteriormente, Python tem suporte embutido para banco de dados SQLite na forma de módulo sqlite3. Para outros bancos de dados, o respectivo módulo Python compatível com DB-API terá que ser instalado com a ajuda do utilitário pip. Por exemplo, para usar o banco de dados MySQL, precisamos instalar o módulo PyMySQL.

pip install pymysql

As etapas a seguir são recomendadas no DB-API -

Estabeleça conexão com o banco de dados usando connect() função e obter objeto de conexão.
Ligar cursor() método do objeto de conexão para obter o objeto cursor.
Forme uma string de consulta composta de uma instrução SQL a ser executada.
Execute a consulta desejada chamando execute() método.
Feche a conexão.

import sqlite3
db=sqlite3.connect('test.db')

Aqui, db é o objeto de conexão que representa test.db. Observe que esse banco de dados será criado se ainda não existir. O objeto de conexão db tem os seguintes métodos -

Sr. Não.	Métodos e Descrição
1	cursor(): Retorna um objeto Cursor que usa esta conexão.
2	commit(): Confirma explicitamente todas as transações pendentes no banco de dados.
3	rollback(): Este método opcional faz com que uma transação seja revertida para o ponto inicial.
4	close(): Fecha a conexão com o banco de dados permanentemente.

Um cursor atua como um identificador para uma determinada consulta SQL, permitindo a recuperação de uma ou mais linhas do resultado. O objeto cursor é obtido a partir da conexão para executar consultas SQL usando a seguinte instrução -

cur=db.cursor()

O objeto cursor tem os seguintes métodos definidos -

Sr. Não	Métodos e Descrição
1	execute() Executa a consulta SQL em um parâmetro de string.
2	executemany() Executa a consulta SQL usando um conjunto de parâmetros na lista de tuplas.
3	fetchone() Busca a próxima linha do conjunto de resultados da consulta.
4	fetchall() Busca todas as linhas restantes do conjunto de resultados da consulta.
5	callproc() Chama um procedimento armazenado.
6	close() Fecha o objeto cursor.

O código a seguir cria uma tabela em test.db: -

import sqlite3
db=sqlite3.connect('test.db')
cur =db.cursor()
cur.execute('''CREATE TABLE student (
StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT (20) NOT NULL,
age INTEGER,
marks REAL);''')
print ('table created successfully')
db.close()

A integridade de dados desejada em um banco de dados é alcançada por commit() e rollback()métodos do objeto de conexão. A string de consulta SQL pode ter uma consulta SQL incorreta que pode gerar uma exceção, que deve ser tratada adequadamente. Para isso, a instrução execute () é colocada dentro do bloco try. Se for bem-sucedida, o resultado é salvo de forma persistente usando o método commit (). Se a consulta falhar, a transação será desfeita usando o método rollback ().

O código a seguir executa a consulta INSERT na tabela do aluno em test.db.

import sqlite3
db=sqlite3.connect('test.db')
qry="insert into student (name, age, marks) values('Abbas', 20, 80);"
try:
   cur=db.cursor()
   cur.execute(qry)
   db.commit()
print ("record added successfully")
except:
   print ("error in query")
   db.rollback()
db.close()

Se você deseja que os dados na cláusula de valores da consulta INSERT sejam fornecidos dinamicamente pela entrada do usuário, use a substituição de parâmetro conforme recomendado no Python DB-API. O ? caractere é usado como um espaço reservado na string de consulta e fornece os valores na forma de uma tupla no método execute (). O exemplo a seguir insere um registro usando o método de substituição de parâmetro. Nome, idade e marcas são considerados dados de entrada.

import sqlite3
db=sqlite3.connect('test.db')
nm=input('enter name')
a=int(input('enter age'))
m=int(input('enter marks'))
qry="insert into student (name, age, marks) values(?,?,?);"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,a,m))
   db.commit()
   print ("one record added successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

O módulo sqlite3 define o executemany()método que é capaz de adicionar vários registros de uma vez. Os dados a serem adicionados devem ser fornecidos em uma lista de tuplas, com cada tupla contendo um registro. O objeto de lista é o parâmetro do método executemany (), junto com a string de consulta. No entanto, o método executemany () não é suportado por alguns dos outros módulos.

o UPDATEa consulta geralmente contém uma expressão lógica especificada pela cláusula WHERE. A string de consulta no método execute () deve conter uma sintaxe de consulta UPDATE. Para atualizar o valor de 'idade' para 23 para name = 'Anil', defina a string como abaixo:

qry="update student set age=23 where name='Anil';"

Para tornar o processo de atualização mais dinâmico, usamos o método de substituição de parâmetros conforme descrito acima.

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
a=int(input(‘enter age’))
qry="update student set age=? where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (a, nm))
   db.commit()
   print("record updated successfully")
except:
   print("error in query")
   db.rollback()
db.close()

Da mesma forma, a operação DELETE é realizada chamando o método execute () com uma string com a sintaxe de consulta DELETE do SQL. Aliás,DELETE consulta também geralmente contém um WHERE cláusula.

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
qry="DELETE from student where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,))
   db.commit()
   print("record deleted successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

Uma das operações importantes em uma tabela de banco de dados é a recuperação de registros dela. SQL forneceSELECTconsulta para o efeito. Quando uma string contendo sintaxe de consulta SELECT é fornecida ao método execute (), um objeto de conjunto de resultados é retornado. Existem dois métodos importantes com um objeto cursor, usando os quais um ou vários registros do conjunto de resultados podem ser recuperados.

fetchone ()

Busca o próximo registro disponível no conjunto de resultados. É uma tupla que consiste em valores de cada coluna do registro obtido.

fetchall ()

Busca todos os registros restantes na forma de uma lista de tuplas. Cada tupla corresponde a um registro e contém os valores de cada coluna da tabela.

O exemplo a seguir lista todos os registros na tabela do aluno

import sqlite3
db=sqlite3.connect('test.db')
37
sql="SELECT * from student;"
cur=db.cursor()
cur.execute(sql)
while True:
   record=cur.fetchone()
   if record==None:
      break
   print (record)
db.close()

Se você planeja usar um banco de dados MySQL em vez de banco de dados SQLite, você precisa instalar PyMySQLmódulo conforme descrito acima. Todas as etapas do processo de conectividade do banco de dados são iguais, como o banco de dados MySQL está instalado em um servidor, a função connect () precisa da URL e das credenciais de login.

import pymysql
con=pymysql.connect('localhost', 'root', '***')

A única coisa que pode diferir com o SQLite são os tipos de dados específicos do MySQL. Da mesma forma, qualquer banco de dados compatível com ODBC pode ser usado com Python instalando o módulo pyodbc.