Um Tutorial SQLite com Python

introdução

Este tutorial irá cobrir usando SQLite em combinação com a interface sqlite3 do Python. SQLite é um banco de dados relacional de um único arquivo, junto com a maioria das instalações padrão em Python. SQLite é muitas vezes a tecnologia de escolha para pequenas aplicações, particularmente as de sistemas incorporados e dispositivos como telefones e tablets, aparelhos inteligentes e instrumentos. No entanto, não é incomum ouvi-lo sendo usado para aplicações web e desktop pequenas a médias.,

criar uma base de dados e fazer uma ligação

criar uma nova base de dados SQLite é tão simples como criar uma ligação usando o módulo sqlite3 na biblioteca padrão Python. Para estabelecer uma ligação, tudo o que precisa de fazer é passar uma localização de ficheiro para o ID

no módulo sqlite3, e se a base de dados representada pelo ficheiro não existir, será criada uma nessa localização.,

import sqlite3con = sqlite3.connect('/path/to/file/db.sqlite3')

irá descobrir que na programação diária de bases de dados estará constantemente a criar ligações à sua base de dados, por isso é uma boa ideia envolver esta simples declaração de ligação numa função generalizada reutilizável.

criando tabelas

para criar tabelas de banco de dados você precisa ter uma idéia da estrutura dos dados que você está interessado em armazenar. Há muitas considerações de design que entram na definição das tabelas de um banco de dados relacional, sobre as quais livros inteiros foram escritos., Não vou entrar em pormenores desta prática e, em vez disso, deixo ao leitor a tarefa de investigar mais aprofundadamente.

no entanto, para ajudar em nossa discussão de programação de banco de dados SQLite com Python I estará trabalhando fora da premissa de que um banco de dados precisa ser criado para uma loja fictícia de livros que tem os dados abaixo já coletados sobre vendas de livros.,

customer	date	product	price
Alan Turing	2/22/1944	Introduction to Combinatorics	7.99
Donald Knuth	7/3/1967	A Guide to Writing Short Stories	17.99
Donald Knuth	7/3/1967	Data Structures and Algorithms	11.99
Edgar Codd	1/12/1969	Advanced Set Theory	16.,99

Ao inspecionar esse dados é evidente que ele contém informações sobre clientes, produtos e pedidos. Um padrão comum no projeto de banco de dados para sistemas transacionais deste tipo são quebrar as ordens em duas tabelas adicionais, ordens e itens de linha (às vezes referidos como detalhes de ordem) para alcançar maior normalização.

num interpretador Python, na mesma pasta que os DB_ utils.,módulo py definido anteriormente, indique a SQL para criar as tabelas de clientes e produtos segue:

o código acima cria um objecto de ligação e depois usa-o para instanciar um objecto cursor. O objecto do cursor é usado para executar declarações SQL na base de dados SQLite.

com o cursor criado, eu então escrevi o SQL para criar a tabela clientes, dando-lhe uma chave primária, juntamente com um campo de texto de primeiro e último nome e atribuí-lo a uma variável chamada customers_sql., I then call the execute(...) method of the cursor object passing it the customers_sql variable. Eu então crio uma tabela de produtos de uma maneira similar.

pode consultar a tabelasqlite_master, uma tabela incorporada de metadados SQLite, para verificar se os comandos acima foram bem sucedidos.

Para ver todas as tabelas do banco de dados conectado consulta name coluna sqlite_master tabela onde o type é igual a “tabela”.,

Para obter um olhar para o esquema das tabelas de consulta sql coluna da mesma tabela, onde o type ainda está “tabela” e o name é igual a “clientes” e/ou “produtos”.

a tabela seguinte a definir será a tabela de encomendas que associa os clientes às encomendas através de uma chave estrangeira e a data da sua compra. Uma vez que o SQLite não suporta um tipo de dados de data/hora real (ou classe de dados para ser consistente com o vernáculo SQLite) todas as datas serão representadas como valores de texto.,

A tabela final a definir será a tabela de itens de linha que dá uma contabilidade detalhada dos produtos em cada ordem.

ao carregar os dados

nesta secção, vou demonstrar como inserir os nossos dados de amostra nas tabelas que acabamos de criar. Um ponto de partida natural seria povoar a tabela de produtos primeiro porque sem produtos não podemos ter uma venda e, portanto, não teria as chaves estrangeiras para se relacionar com os itens de linha e encomendas., Olhando para os dados de exemplo que eu veja que há quatro produtos:

O fluxo de trabalho para executar instruções INSERT é simples:

1. Conectar ao banco de dados
2. Criar um objeto de cursor
3. Escrever um parametrizadas instrução SQL insert e armazenar como uma variável
4. Chamar o método execute do objeto de cursor passando o sql variável e os valores, como uma tupla, para ser inserido na tabela

Dada esta descrição geral, vamos escrever um pouco mais de código.,

o código acima provavelmente parece bastante óbvio, mas deixe-me discuti-lo um pouco como há algumas coisas importantes acontecendo aqui. A instrução inserir segue a sintaxe padrão SQL, exceto para o id

bit. The?‘s are actually placeholders in what is known as a “parameterized query”.

consultas parametrizadas são uma característica importante de essencialmente todas as interfaces de banco de dados para linguagens de programação modernas de alto nível, como o módulo sqlite3 em Python., Este tipo de consulta serve para melhorar a eficiência das consultas que são repetidas várias vezes. Talvez mais importante, eles também sanitizam entradas que tomam o lugar do ? substituições que são passadas durante a chamada para o método de execução do objeto cursor para evitar entradas nefastas levando à injeção de SQL. A seguir, uma banda desenhada do popular xkcd.com blog describing the dangers of SQL injection.

para povoar as restantes tabelas vamos seguir um padrão ligeiramente diferente para mudar as coisas um pouco., O fluxo de trabalho para cada ordem, identificado por uma combinação de cliente primeiro e último nome e a data de compra, será:

1. Insira o novo cliente na tabela clientes e obter sua chave primária id
2. Criar uma ordem de entrada, de acordo com a identificação do cliente e a data de compra, em seguida, recuperar a sua chave primária id
3. Para cada produto a fim de determinar sua chave primária id e criar um item de linha de entrada associando a ordem e o produto

Para tornar as coisas mais simples em nós mesmos vamos fazer um rápido olhar de todos os nossos produtos., Por agora, não se preocupe muito com a mecânica da declaração SQL seleto, pois vamos Dedicar uma seção a ela em breve.

A primeira ordem foi colocada em 22 de Fevereiro de 1944 por Alan Turing, que comprou a introdução à Combinatória por $7.99.

comece por fazer um novo registro de clientes para o Sr. Turing, em seguida, determinar o seu id de chave primária, acessando o lastrowid campo do objeto cursor.

agora podemos criar uma entrada de ordem, coletar o valor de id de nova ordem e associá-lo a uma entrada de item de linha junto com o produto que o Sr. Turing ordenou.,

os registros restantes são carregados exatamente o mesmo, exceto para a ordem feita para Donald Knuth, que receberá duas entradas de itens de linha. No entanto, a natureza repetitiva de tal tarefa está clamando pela necessidade de envolver essas funcionalidades em funções reutilizáveis. Na db_utils.py módulo adicionar o seguinte código:

Awh, agora podemos trabalhar com alguma eficiência!

terá de exit() o seu interpretador de Python e relê-lo para que as suas novas funções fiquem acessíveis no interpretador.,sinto-me compelido a dar mais um conselho como estudante de artesanato de software. Quando você se encontra fazendo várias manipulações de banco de dados (Inserções neste caso), a fim de realizar o que é de fato uma tarefa cumulativa (ie, criando uma ordem), é melhor embrulhar as sub-tarefas (criando cliente, ordem, em seguida, itens de linha) em uma única transação de banco de dados, de modo que você pode cometer no sucesso ou rollback se um erro ocorrer ao longo do caminho.,

isto seria algo parecido com isto:

quero terminar esta secção com uma demonstração rápida de como actualizar um registo existente na base de dados. Vamos atualizar o guia para escrever o preço dos contos para 10.99 (indo à venda).

>>> update_sql = "UPDATE products SET price = ? WHERE id = ?">>> cur.execute(update_sql, (10.99, 2))

questionando a base de dados

geralmente a ação mais comum realizada em uma base de dados é a recuperação de alguns dos dados armazenados nela através de uma declaração selecionada. Para esta seção I estará demonstrando como usar a interface sqlite3 para realizar consultas selecionadas simples.,

para realizar uma consulta multi -row básica da tabela clientes, você passa uma declaração selecionada para o método execute(...) do objeto cursor. Depois disso você pode iterar sobre os resultados da consulta chamando o id

método do mesmo objeto cursor.

permite dizer que você gostaria de, em vez disso, apenas recuperar um registro da base de dados. Você pode fazer isso escrevendo uma consulta mais específica, digamos para o id de Donald Knuth de 2, e seguindo isso chamando fetchone() método do objeto cursor.,

>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")>>> result = cur.fetchone()>>> print(result)(2, 'Donald', 'Knuth')

veja como a linha individual de cada resultado é na forma de uma tupla? Bem, enquanto tuplas são uma estrutura de dados Pythonic muito útil para alguns casos de uso de programação muitas pessoas encontrá-los um pouco prejudicando quando se trata da tarefa de recuperação de dados. Acontece que existe uma forma de representar os dados de uma forma que talvez seja mais flexível para alguns. Tudo que você precisa fazer é definir o método row_factory do objeto de conexão para algo mais adequado, como sqlite3.Row., Isto lhe dará a capacidade de acessar os itens individuais de uma linha por posição ou valor da palavra-chave.

Conclusão

neste artigo eu dei uma breve demonstração do que eu sinto são as mais importantes características e funcionalidades do sqlite3 Python interface to the leve único arquivo de banco de dados SQLite que vem pré-empacotado com mais de Python instalada., Eu também tentei dar alguns pedaços de conselhos sobre as melhores práticas quando se trata de programação de banco de dados, mas eu faço o cuidado nova-comer, que os meandros de programação de banco de dados é, geralmente, um dos mais propenso a falhas de segurança em nível corporativo e aprofundar o conhecimento é necessário, antes de tal empreendimento.