ICE-B
16 - Sqlite e Python
Sqlite + Python
Resumo
■
Usar Sqlite a partir de Python
■
Módulo sqlite3
■
Ligação à base de dados
■
Cursor para executar comandos SQL
Sqlite + Python
Sqlite + Python
Porquê usar um motor de bases de dados?
■
A linguagem SQL facilita muito algumas operações
•
Criar tabelas, inserir registos, consultar dados
■
O motor de bases de dados está optimizado para gerir os dados
•
Garante integridade da informação, mesmo em caso de falhas
•
Está preparado para gerir grandes volumes de dados
Sqlite + Python
Como usar o Sqlite em Python com o módulo sqlite3
■
Criar uma ligação à base de dados: conn = connect(db_name)
■
Obter um cursor, que executa SQL: cursor = conn.cursor()
■
Dar o comando ou commandos SQL: cursor.execute(sql)
■
Actualizar a base de dados: conn.commit()
•
Isto garante atomicidade das transacções
•
Até ao commit() a BD não é alterada.
•
Apenas parece alterada da perspectiva desta ligação
•
No commit() são feitas todas as alterações.
Sqlite + Python
Exemplo
Temos um ficheiro de texto com dados de projectos
■
Ficheiro praias.txt
•
Concelho, nome, custo e prioridade.
Marinha Grande;Esporao da Praia da Vieira;720000.00;Media Lourinha;Arribas junto ao forte do Paimogo;591000.00;Media Lourinha;Arribas em Porto das Barcas;691000.00;Media
Lourinha;Arribas em Porto Dinheiro;606500.00;Media
Sintra;Arribas da praia Grande do Rodizio;39951.63;Media Sintra;Arribas da praia das Azenhas do Mar;326560.08;Media Sintra;Arribas da praia Pequena;428015.58;Media
Santiago do Cacem;Arriba da Praia Fonte do Cortico;23000.00;Media Sines;Relocalizacao de acessos das praias de Morgavel, Franquia ... Setubal;Arribas da praia de Galapos;100000.00;Baixa
Vila do Bispo;Alimentacao artificial da praia da Mareta;2500000.00;Elevada Portimao;Saneamento da arriba na praia do Vau;30000.00;Elevada
Exemplo
Plano do programa:
■
Criar a tabela Projectos com os campos necessários:
•
concelho, nome, custo, prioridade
■
Carregar os dados do ficheiro para a tabela Projectos
■
Criar uma função que liste os projectos com a prioridade
especificada
•
Consultando a tabela na BD
import sqlite3
def create_table(db_name):
"""create the Projects table in the database"""
def load_projects(file_name, db_name):
"""load projects from file into projects table"""
def with_priority(db_name,priority):
Exemplo
Criar a tabela
def create_table(db_name):
"""create the projects table in the database"""
conn = sqlite3.connect(db_name) cursor = conn.cursor()
cursor.execute('CREATE TABLE Projects ( Concelho TEXT, Nome TEXT,'+ 'Custo FLOAT, Prioridade TEXT, PRIMARY KEY(Nome));') conn.commit()
conn.close()
■
Ligar à base de dados (connect)
■
Obter o cursor para essa ligação
■
Executar o código SQL para criar a tabela e fazer commit
Exemplo
Criar a tabela
def create_table(db_name):
"""create the projects table in the database"""
conn = sqlite3.connect(db_name) cursor = conn.cursor()
cursor.execute('CREATE TABLE Projects ( Concelho TEXT, Nome TEXT,'+ 'Custo FLOAT, Prioridade TEXT, PRIMARY KEY(Nome));') conn.commit()
conn.close()
In : create_table('teste.db') In :
Exemplo
Como verificar o resultado
■
Opção 1: usar o sqlite3 directamente:
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database. sqlite> .open teste.db
sqlite> .tables Projects
sqlite> .schema
CREATE TABLE Projects ( Concelho TEXT, Nome TEXT,Custo FLOAT, Prioridade TEXT, PRIMARY KEY(Nome));
Exemplo
Como verificar o resultado
■
Opção 2: usar um gestor para Sqlite. E.g. sqliteman
Exemplo
■
Problema: se a tabela já existe ocorre uma exception
In : create_table('teste.db') ...
OperationalError: table Projects already exists
■
Isto é inconveniente porque bloqueia a execução
■
Para evitar que pare vamos usar um bloco try ... except
try: ...
except: ...
Exemplo
Criar a tabela
def create_table(db_name):
"""create the projects table in the database"""
conn = sqlite3.connect(db_name) cursor = conn.cursor()
try:
cursor.execute('CREATE TABLE Projects ( Concelho TEXT, Nome TEXT,'+ 'Custo FLOAT, Prioridade TEXT, PRIMARY KEY(nome));') conn.commit()
except:
print('Error: could not create table.') conn.close()
In : create_table('teste.db') In : create_table('teste.db') Error: could not create table. In :
Exemplo
Inserir os dados do ficheiro
Marinha Grande;Esporao da Praia da Vieira;720000.00;Media Lourinha;Arribas junto ao forte do Paimogo;591000.00;Media Lourinha;Arribas em Porto das Barcas;691000.00;Media
Lourinha;Arribas em Porto Dinheiro;606500.00;Media
Sintra;Arribas da praia Grande do Rodizio;39951.63;Media Sintra;Arribas da praia das Azenhas do Mar;326560.08;Media Sintra;Arribas da praia Pequena;428015.58;Media
Santiago do Cacem;Arriba da Praia Fonte do Cortico;23000.00;Media Sines;Relocalizacao de acessos das praias de Morgavel, Franquia ... Setubal;Arribas da praia de Galapos;100000.00;Baixa
Vila do Bispo;Alimentacao artificial da praia da Mareta;2500000.00;Elevada Portimao;Saneamento da arriba na praia do Vau;30000.00;Elevada
Exemplo
Inserir os dados do ficheiro
def load_projects(file_name, db_name):
"""load projects from file into projects table"""
lines = open(file_name).readlines() conn = sqlite3.connect(db_name)
cursor = conn.cursor() for line in lines:
try:
cells = line.strip().split(';')
sql = 'INSERT INTO Projects VALUES ( "{0}", "{1}", {2}, "{3}");'
sql = sql.format(cells[0],cells[1],cells[2],cells[3]) cursor.execute(sql)
conn.commit() except:
print('Error on ',line) conn.close()
■
Tentamos inserir cada registo e fazer logo o commit
•
Se alguma coisa correr mal, avisamos que houve um erro naquela linha
Exemplo
Inserir os dados do ficheiro
■
Testar:
In : load_projects('praias.txt','teste.db')
■
Verificar com a consola do sqlite3
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database. sqlite> .open teste.db
sqlite> select * from projects;
Marinha Grande|Esporao da Praia da Vieira|720000.0|Media Lourinha|Arribas junto ao forte do Paimogo|591000.0|Media Lourinha|Arribas em Porto das Barcas|691000.0|Media
Exemplo
Inserir os dados do ficheiro
■
Testar:
In : load_projects('praias.txt','teste.db')
Exemplo
Com a tabela no sqlite3 podemos:
■
Ver os custos dos projectos de nome começado por Arriba:
sqlite> SELECT Nome, Custo FROM Projects WHERE Nome LIKE "Arriba%"; Arribas junto ao forte do Paimogo|591000.0
Arribas em Porto das Barcas|691000.0
Arribas em Porto Dinheiro|606500.0
Arribas da praia Grande do Rodizio|39951.63
Arribas da praia das Azenhas do Mar|326560.08
Arribas da praia Pequena|428015.58
Arriba da Praia Fonte do Cortico|23000.0
Arribas da praia de Galapos|100000.0
sqlite>
■
O operador LIKE compara strings admitindo wildcards
Exemplo
Com a tabela no sqlite3 podemos:
■
Ver os custos dos projectos com praia no nome:
sqlite> SELECT Nome, Custo FROM Projects WHERE Nome LIKE "%praia%"; Esporao da Praia da Vieira|720000.0
Arribas da praia Grande do Rodizio|39951.63
Arribas da praia das Azenhas do Mar|326560.08
Arribas da praia Pequena|428015.58
Arriba da Praia Fonte do Cortico|23000.0
Relocalizacao de acessos das praias de Morgavel, Franquia e Farol|100100.0
Arribas da praia de Galapos|100000.0
Alimentacao artificial da praia da Mareta|2500000.0
Saneamento da arriba na praia do Vau|30000.0
sqlite>
■
O operador LIKE compara strings admitindo wildcards
•
O sinal % na string do LIKE substitui qualquer substring
Exemplo
Com a tabela no sqlite3 podemos:
■
Ver os concelhos diferentes onde há projectos (DISTINCT)
sqlite> SELECT DISTINCT Concelho FROM Projects; Marinha Grande Lourinha Sintra Santiago do Cacem Sines Setubal Vila do Bispo Portimao
■
Ou prioridade:
Exemplo
Consultas usando Python
■
Com o objecto cursor podemos executar o SELECT
•
Neste caso não há
commit porque não alteramos a base de dados
■
Depois de executar o SELECT, podemos obter todos os resultados
com cursor.fetchall()
•
Uma lista de tuplos com os valores dos campos
In : conn = sqlite3.connect('teste.db') In : cursor = conn.cursor()
In : cursor.execute('SELECT * FROM Projects WHERE Nome LIKE "%praia%";') In : cursor.fetchall()
Out:
[('Marinha Grande', 'Esporao da Praia da Vieira', 720000.0, 'Media'), ('Sintra', 'Arribas da praia Grande do Rodizio', 39951.63, 'Media'), ('Sintra', 'Arribas da praia das Azenhas do Mar', 326560.08, 'Media'), ('Sintra', 'Arribas da praia Pequena', 428015.58, 'Media'),
...
('Portimao', 'Saneamento da arriba na praia do Vau', 30000.0, 'Elevada')] In : conn.close()
Exemplo
Consultas usando Python
■
Vamos criar uma função auxiliar para simplificar isto
•
E organizar os dados como uma lista de dicionários, usando
cursor.description
•
Tem um tuplo de tuplos com o nome da coluna como primeiro elemento
•
Podemos usar isto para criar uma lista de dicionários
In : conn = sqlite3.connect('teste.db') In : cursor = conn.cursor()
In : cursor.execute('SELECT * FROM Projects WHERE Nome LIKE "%praia%";') In : cursor.description
Out:
(('Concelho', None, None, None, None, None, None), ('Nome', None, None, None, None, None, None), ('Custo', None, None, None, None, None, None),
Exemplo
Consultas usando Python
■
Função auxiliar para simplificar consultas
def query_db(db_name,query):
"return dictionary with result of SELECT"
conn = sqlite3.connect(db_name) cursor = conn.cursor() cursor.execute(query) records = cursor.fetchall() conn.close() result = []
for record in records: current = {}
for ix in range(len(record)):
current[cursor.description[ix][0]] = record[ix] result.append(current)
Exemplo
Consultas usando Python
■
Função auxiliar para simplificar consultas
In : query_db('teste.db','SELECT * FROM Projects;') Out[36]:
[{'Concelho': 'Marinha Grande', 'Custo': 720000.0,
'Nome': 'Esporao da Praia da Vieira', 'Prioridade': 'Media'},
{'Concelho': 'Lourinha', 'Custo': 591000.0,
'Nome': 'Arribas junto ao forte do Paimogo', 'Prioridade': 'Media'},
{'Concelho': 'Lourinha', 'Custo': 691000.0,
'Nome': 'Arribas em Porto das Barcas', 'Prioridade': 'Media'},
Exemplo
Consultas usando Python
■
Função auxiliar para simplificar consultas
In : query_db('teste.db','SELECT * FROM Projects WHERE Prioridade="Baixa"') Out:
[{'Concelho': 'Setubal', 'Custo': 100000.0,
'Nome': 'Arribas da praia de Galapos', 'Prioridade': 'Baixa'}]
In : query_db('teste.db','SELECT * FROM Projects WHERE Prioridade="Elevada"') Out:
[{'Concelho': 'Vila do Bispo', 'Custo': 2500000.0,
'Nome': 'Alimentacao artificial da praia da Mareta', 'Prioridade': 'Elevada'},
{'Concelho': 'Portimao', 'Custo': 30000.0,
'Nome': 'Saneamento da arriba na praia do Vau', 'Prioridade': 'Elevada'}]
Exemplo
Função para obter projectos dada a prioridade
■
Esta agora é fácil:
def with_priority(db_name,priority):
"return dictionary with projects of given priority"
query = 'SELECT * FROM Projects WHERE Prioridade = "{0}";'
query = query.format(priority) return query_db(db_name,query)
In : with_priority('teste.db','Baixa') Out:
[{'Concelho': 'Setubal', 'Custo': 100000.0,
'Nome': 'Arribas da praia de Galapos', 'Prioridade': 'Baixa'}]
Exemplo
Função para organizar projectos por prioridade
■
Devolve um dicionário com todas as prioridades na tabela
■
Cada entrada desse dicionário tem a lista de dicionários com os
projectos correspondentes
def projects_by_priority(db_name):
"return dictionary with priorities and all projects in list"
projects = {}
priorities = query_db(db_name,'SELECT DISTINCT Prioridade from Projects;') for entry in priorities:
priority = entry['Prioridade']
projects[priority] = with_priority(db_name,priority) return projects
■
Usamos SELECT DISTINCT Prioridade para listar prioridades
Exemplo
Função para organizar projectos por prioridade
In : projects = projects_by_priority('teste.db') In : projects.keys()
Out: dict_keys(['Media', 'Baixa', 'Elevada']) In : projects['Elevada']
Out:
[{'Concelho': 'Vila do Bispo', 'Custo': 2500000.0,
'Nome': 'Alimentacao artificial da praia da Mareta', 'Prioridade': 'Elevada'},
{'Concelho': 'Portimao', 'Custo': 30000.0,
'Nome': 'Saneamento da arriba na praia do Vau', 'Prioridade': 'Elevada'}]
In : projects['Baixa'] Out:
[{'Concelho': 'Setubal', 'Custo': 100000.0,
