Dominar o banco de dados SQLite em Python

SQLite é um sistema de gerenciamento de banco de dados relacional baseado na linguagem SQL; é um mecanismo de banco de dados de configuração zero sem servidor. É um dos motores de banco de dados mais populares e muito fácil de ser usado em pequenas aplicações. Ele cria apenas um arquivo em disco para armazenar todo o banco de dados, o que torna o arquivo portátil. Ele é usado no sistema operacional Android como a fonte primária para armazenar dados. Ele também é usado pelo Google Chrome para armazenar dados do site e dados do usuário, incluindo senhas na máquina local.

Trabalho avançado com banco de dados SQLite em Python

Neste tutorial, os tópicos que serão abordados são: inserir imagens em uma tabela SQLite, Listar as tabelas presentes em um banco de dados, Identificar mudanças totais desde que o banco de dados está conectado, Fazer backup de um banco de dados, Dumping de um banco de dados SQLite, Rollback no SQLite, Deletar registros de uma tabela, descartando uma tabela e exceções de banco de dados SQLite.

Você também pode querer ver a primeira parte deste tutorial, que apresenta o noções básicas de SQLite, Vantagens de usá-lo, Conectando a um arquivo de banco de dados, Criando uma tabela no banco de dados, Inserindo dados na tabela, Consultando dados da tabela, atualizando a tabela e muito mais.

Arquivos e imagens no banco de dados SQLite

Ao trabalhar com bancos de dados, existem situações em que você precisa inserir imagens ou arquivos em um banco de dados ou exportar dele. Por exemplo, se você estiver criando um banco de dados para armazenar dados de funcionários, também pode precisar inserir fotos de cada funcionário no banco de dados.

Para adicionar imagens em um banco de dados SQLite, precisamos usar o tipo de dados BLOB do SQLite. O tipo de dados BLOB () é usado para armazenar objetos grandes, geralmente arquivos grandes, como imagens, músicas, vídeos, documentos, PDF, etc. A primeira etapa é converter os dados e imagens no objeto byte do Python, que é semelhante ao tipo de dados BLOB do SQLite. Antes de continuar, crie uma tabela chamada aluna no banco de dados com os campos id, nome, imagens, marcas. Execute o seguinte código para criar a tabela.

import sqlite3 conn = sqlite3.conectar ("amostra.db ") print (" \ n [+] Conectado com sucesso ao banco de dados ") cur = conn.cursor () print ("\ n [+] Cursor foi configurado com sucesso") table = cur.execute ("" "CRIAR TABELA aluno (id INT PRIMARY KEY, nome TEXT, imagens BLOB, marcas TEXT);" "") print ("\ n [+] A tabela foi criada com sucesso") cur.close () conn.commit () conn.perto()

Este programa irá criar uma nova tabela com o nome aluna. Você verá a seguinte saída no terminal.

Inserindo uma imagem

Para inserir uma imagem em um banco de dados SQLite, transforme a imagem em um objeto Python byte e, em seguida, insira-o na coluna de imagens, que aceita dados BLOB. Execute o seguinte código para adicionar uma imagem img.png no banco de dados usando Python.

import sqlite3 conn = sqlite3.conectar ("amostra.db ") conn.text_factory = str print ("\ n [+] Conectado com sucesso ao banco de dados") cur = conn.cursor () print ("\ n [+] Cursor foi configurado com sucesso") com open ("img.png "," rb ") como arquivo: dados = arquivo.read () python_tuple = (101, "robin", data, "90") print ("\ n [+] A imagem foi importada com sucesso") print ("\ n [+] Inserindo agora no banco de dados") cur.execute ("INSERT INTO aluno (id, nome, imagens, marcas) VALORES (?,?,?,?) ", python_tuple) print (" \ n [+] Os dados foram inseridos com sucesso ") cur.close () conn.commit () conn.perto()

Este programa irá inserir a imagem no banco de dados do aluno que você criou. Você verá a seguinte saída.

No programa acima, abrimos o arquivo no modo binário e lemos cada byte e o armazenamos na variável dados. Então usamos essa variável na instrução INSERT para inserir a imagem no banco de dados.

Recuperando uma imagem

Para recuperar uma imagem de um banco de dados, busque a linha usando uma instrução select e, em seguida, acesse os dados binários da imagem em uma variável python, que será armazenada em um arquivo de imagem. Veja o seguinte código para ilustração.

import sqlite3 conn = sqlite3.conectar ("amostra.db ") conn.text_factory = str print ("\ n [+] Conectado com sucesso ao banco de dados") cur = conn.cursor () print ("\ n [+] Cursor foi configurado com sucesso") print ("\ n [+] Recuperando a imagem") cur.execute ("SELECT * FROM student") ret = cur.fetchall () for i in ret: data = i [2] com open ("img2.png "," wb ") como arquivo: arquivo.write (data) print ("\ n [+] A imagem foi salva") cur.close () conn.commit () conn.perto()

Este programa simples irá recuperar a imagem do banco de dados e salvá-la no disco chamado img2.png. Você também pode escolher outro nome para o arquivo de imagem. O resultado do programa é mostrado abaixo.

Liste todas as tabelas de um banco de dados

Em um banco de dados, podemos criar várias tabelas. Portanto, também é necessário listar todas as tabelas presentes em um banco de dados. Para listar as tabelas presentes em um banco de dados, consulte a tabela sqlite_master usando a instrução SELECT do SQL. A sintaxe da consulta será:

SELECT nome FROM sqlite_master WHERE type = "tabela"

Aqui está como usamos esta consulta para listar todas as tabelas presentes em nosso banco de dados.

import sqlite3 conn = sqlite3.conectar ("amostra.db ") print (" \ n [+] Conectado com sucesso ao banco de dados ") cur = conn.cursor () print ("\ n [+] Cursor foi configurado com sucesso") cur.execute ("SELECT nome de sqlite_master onde type =" tabela "") rows = cur.fetchall () imprimir (linhas) cur.close () conn.commit () conn.perto()

O código acima irá listar todas as tabelas presentes em nosso banco de dados. A saída produzida pelo código quando executado é a seguinte. Você pode ver alguma outra saída dependendo das tabelas que você criou no banco de dados.

Identificar mudanças totais desde que conectado ao banco de dados

Em qualquer situação, é útil identificar o número de linhas que foram modificadas, inseridas ou excluídas desde que o banco de dados foi conectado. Para isso, use o total_changes () método do objeto de conexão, que retornará o número total de linhas do banco de dados que foram afetadas desde a conexão. Vamos ver um exemplo de demonstração para saber como funciona.

import sqlite3 conn = sqlite3.conectar ("amostra.db ") conn.text_factory = str print ("\ n [+] Conectado com sucesso ao banco de dados") cur = conn.cursor () print ("\ n [+] Ambos os Cursores foram configurados com sucesso") cur.execute ("INSERT INTO aluno (id, nome, imagens, marcas) VALUES (140, 'David',", 99) ") cur.execute ("INSERT INTO aluno (id, nome, imagens, marcas) VALUES (150, 'Sam',", 97) ") changes = conn.total_changes print ("\ n [+] Total agora de mudanças de linhas é:", mudanças) conn.commit () cur.close () conn.perto()

O programa acima irá imprimir o número de mudanças de linhas na conexão atual. Você verá a seguinte saída.

Rollback em SQLite

Quando se trata de desfazer algumas tarefas, você pode usar a função rollback (). Este método pode ser usado para desfazer tarefas que foram feitas após o último commit. Veja o exemplo abaixo para uma ilustração.

import sqlite3 conn = sqlite3.conectar ("amostra.db ") conn.text_factory = str print ("\ n [+] Conectado com sucesso ao banco de dados") cur = conn.cursor () print ("\ n [+] Ambos os Cursores foram configurados com sucesso") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (100001, 'David',", 99) ") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (100002, 'Sam',", 97) ") conn.commit () print ("\ n [+] As duas linhas foram inseridas com sucesso") cur.execute ("SELECT * FROM student") primeiro = cur.fetchall () print ("\ n [+] Os novos registros no banco de dados são:") for i in first: print (i) cur.execute ("INSERT INTO aluno (id, nome, imagens, marcas) VALUES (10003, 'Kishan',", 100) ") cur.execute ("INSERT INTO aluno (id, nome, imagens, marcas) VALUES (10004, 'Ankit',", 100) ") print (" \ n [+] As duas linhas foram inseridas com sucesso, mas não confirmadas ") conn.rollback () print ("\ n [+] Reverter os comandos anteriores para que os novos dados não sejam inseridos") conn.commit () cur.execute ("SELECT * FROM student") segundo = cur.fetchall () print ("\ n [+] Os novos registros no banco de dados são:") para i em segundo: print (i) cur.close () conn.perto()

No exemplo acima, as duas primeiras instruções de inserção irão inserir os dados conforme fornecidos, mas as duas últimas instruções de inserção serão revertidas, de modo que não adicionarão nenhum dado à tabela. A saída será como mostrado abaixo.

Backup de um banco de dados

Ao trabalhar com o banco de dados, é essencial fazer um backup do banco de dados. O módulo sqlite3 fornece uma função para fazer o backup do banco de dados. Usando o método backup () do objeto de conexão, podemos fazer o backup do banco de dados SQLite. A sintaxe básica do método de backup é:

backup (destino, *, páginas = 0, progresso = Nenhum, nome = "principal", suspensão = 0.250)

Por padrão, ou quando Páginas são ambos 0 ou um número inteiro negativo, todo o banco de dados é copiado em uma única etapa, o que é preferível para um banco de dados pequeno; caso contrário, o método executa um loop copiando até Páginas em um momento que poderia ser feito com o extenso banco de dados. O nome argumento mostra o nome do banco de dados que será copiado: deve ser uma string contendo o padrão, para indicar o banco de dados principal ou para indicar o banco de dados temporário. O dormir argumento especifica o tempo em segundos para hibernar entre as tentativas de backup das páginas restantes. Pode ser um número inteiro ou um valor de ponto flutuante.

Vamos fazer um backup do base de dados.db banco de dados que estamos usando no tutorial.

import sqlite3 conn_main = sqlite3.conectar ("amostra.db ") conn_backup = sqlite3.conectar ("sample_backup.db ") print (" \ n [+] Conectado com sucesso ao banco de dados ") cur_main = conn_main.cursor () cur_backup = conn_backup.cursor () print ("\ n [+] Ambos os Cursores foram configurados com sucesso") conn_main.backup (conn_backup, pages = 0, progress = None, name = "main") print ("O banco de dados foi backup com sucesso") cur_main.close () cur_backup.close () conn_main.commit () conn_backup.commit () conn_main.close () conn_backup.perto()

No código acima, os dois bancos de dados estão conectados, um é o banco de dados do qual queremos fazer o backup e o segundo é o banco de dados do qual faremos o backup. Use o cópia de segurança() método do primeiro objeto de conexão de banco de dados para fazer um backup. Esta função aceita o objeto de conexão do segundo banco de dados como o destino para criar um backup no outro banco de dados. Use o páginas = 0 argumentos, então o processo ocorrerá em uma etapa, o que é recomendado para pequenos bancos de dados. Este programa irá criar uma nova amostra de nome de banco de dados_cópia de segurança.db e preencha com o backup do primeiro banco de dados. Você pode ver que um novo banco de dados foi criado na pasta atual com o mesmo tamanho de arquivo do anterior.

Descarregando um banco de dados SQLite

Despejar bancos de dados é uma tarefa importante. Normalmente, um arquivo de despejo é um conjunto de instruções SQL para os dados, que geralmente é usado para backup. Podemos despejar um banco de dados usando o método dump (). Veja o exemplo abaixo para saber como descartar um banco de dados SQLite.

import sqlite3 con = sqlite3.conectar ("banco de dados.db ") com aberto ('dump.sql ',' w ') como f: para linha em con.iterdump (): f.escrever ('% s \ n'% linha)

O programa acima irá despejar a amostra do banco de dados.db, e salvará os dados despejados em um arquivo chamado dump.sql. Você pode ver os dados presentes no diretório onde os arquivos python são atuais e abri-los usando qualquer editor de texto.

método executemany () de SQLite3

O executemany () método executa um comando SQL contra todas as sequências de parâmetros ou mapeamentos encontrados na sequência seq_of_parameters. Para simplificar, este método pode ser usado para executar a maioria dos comandos SQL em uma linha. E.g., Podemos inserir qualquer número de linhas por meio de uma lista Python usando este comando. Veja o exemplo abaixo para a ilustração.

import sqlite3 conn = sqlite3.conectar ("amostra.db ") print (" \ n [+] Conectado com sucesso ao banco de dados ") cur = conn.cursor () print ("\ n [+] Cursor foi configurado com sucesso") python_list = [(10000000, 'vivek', ", '10'), (100000001, 'rose',", '21'), (100000002, 'robin', ", '31'), (100000003, 'Dev',", '4'), (100000004, 'michael', ", '52')] cur.executemany ("INSERT INTO aluno (id, nome, imagens, marcas) VALORES (?,?,?,?) ", python_list) print (" \ n [+] Todos os dados foram inseridos com sucesso ") cur.close () conn.commit () conn.perto()

O programa acima irá inserir todos os dados fornecidos na lista python. A saída produzida pelo programa é mostrada abaixo.

Excluir registros de uma tabela

Podemos usar a operação DELETE para excluir registros de uma tabela. Podemos remover rapidamente uma linha utilizando a operação DELETE com a cláusula WHERE. A sintaxe básica para a instrução DELETE é:

DELETE from table_name WHERE some_condition;

Vamos ver um exemplo. Vamos deletar a linha com id 1001 da tabela de funcionários de nosso banco de dados.

import sqlite3 conn = sqlite3.conectar ("amostra.db ") conn.text_factory = str print ("\ n [+] Conectado com sucesso ao banco de dados") cur = conn.cursor () print ("\ n [+] Cursor foi configurado com sucesso") cur.execute ("DELETE FROM student WHERE id = 1001") print ("\ n [+] A linha foi excluída com sucesso") cur.execute ("SELECT * FROM student") data = cur.fetchall () para linha nos dados: print (linha) cur.close () conn.commit () conn.perto()

O código acima excluirá a linha com ID 1001. Você pode ver no retorno da instrução SELECT que a linha foi removida. O resultado do programa é mostrado abaixo.

Solte uma mesa

Podemos descartar ou excluir rapidamente uma tabela usando a instrução SQLite DROP. A sintaxe da instrução DROP é mostrada abaixo:

Tabela DROP nome_tabela 

Se a tabela não existir, o SQLite lançará um erro, então, para evitar isso, podemos usar o se existe tag com a instrução DROP. Veja a sintaxe abaixo:

Tabela DROP se existir nome_tabela

Vamos ver como podemos usar essa instrução com o python sqlite3 módulo para deletar uma mesa. Neste programa, iremos remover o aluna mesa que criamos anteriormente.

import sqlite3 conn = sqlite3.conectar ("amostra.db ") conn.text_factory = str print ("\ n [+] Conectado com sucesso ao banco de dados") cur = conn.cursor () print ("\ n [+] Cursor foi configurado com sucesso") cur.execute ("DROP TABLE IF EXISTS student") print ("\ n [+] A tabela foi descartada com sucesso") cur.close () conn.commit () conn.perto()

O programa acima irá deletar a tabela aluna de amostra base de dados. Podemos usar o comando list table que vimos anteriormente para ver se a tabela foi excluída. O resultado do programa é mostrado abaixo.

Exceções de banco de dados SQLite

Algumas exceções do banco de dados SQLite podem ser levantadas devido a algum erro. Vamos ver um pouco de quando esses erros foram levantados.

• sqlite3.Aviso: é uma subclasse de exceção. Esta exceção mostra alguns avisos, e eles podem ser ignorados em muitos casos.
• sqlite3.Erro: também é uma subclasse de exceção. É a classe base de todas as outras exceções no sqlite3 módulo.
• sqlite3.DatabaseError: Estes são os erros que são gerados devido a alguns erros em bancos de dados. Por exemplo: - Se tentarmos conectar a um banco de dados criptografado ou um arquivo de banco de dados errado, ele mostrará um DatabaseError que os dados estão criptografados ou não é um arquivo de banco de dados válido.
• sqlite3.IntegrityError: esta exceção é uma subclasse de um DatabaseError. Obteremos essa exceção quando a integridade relacional de um banco de dados for afetada, e.g., Uma falha na verificação da chave estrangeira.
• sqlite3.ProgrammingError: esta exceção também é uma subclasse de DatabaseError. Esta exceção é gerada por causa de erros de programação, e.g., criar uma tabela com o mesmo nome que já existe, erro de sintaxe nas consultas SQL, etc.
• sqlite3.OperationalError: também é uma subclasse de DatabaseError. Esta exceção foi levantada para erros relacionados à operação do banco de dados e que não estão sob nosso controle. Por exemplo, uma desconexão acidental com o sistema, servidor desligado, ocorre um tempo limite, problemas de fonte de dados, desligamento da máquina, etc.
• sqlite3.NotSupportedError: Esta exceção é gerada quando o banco de dados não oferece suporte a uma API de banco de dados usada.

Esta é a lista de todas as exceções do SQLite; podemos lidar com essas exceções em nossos programas usando o método de tratamento de erros básico try / except do Python.

Conclusão

Isso nos leva ao final do guia abrangente sobre métodos avançados de trabalho com SQLite usando Python. Espero que você tenha aprendido todos os aspectos do SQLite3 usando Python, o que nos ajudará a construir projetos Python fantásticos.