quarta-feira, 9 de julho de 2008

Python com SQLite

Uma das primeiras coisas que eu senti falta quando vim para o Linux foi de uma linguagem de programação robusta o suficiente para que eu pudesse trabalhar tanto em meus projetos pessoais quanto em ocasionais projetos que por via das dúvidas aparecessem por aí. Advindo do Delphi e de sua poderosa IDE de desenvolvimento, passei um bom tempo experimentando linguagens de programação e ambientes de desenvolvimento até chegar ao Python e ao seu simplésimo ambiente Idle. Quanto à bancos de dados, sempre usei o M$ SQL Server ou quando a portabilidade era necessária, o Firebird Embedded.

Hoje falarei sobre bancos de dados. Como agora oficialmente eu programo em Python, minhas duas soluções são outras: PostgreSQL e SQLite. Do PostgreSQL eu não tenho nada a comentar, somente que é um excelente banco. A pauta de hoje é sobre o SQLite, que no começo não me cativou muito, mas depois de fuçar um pouco (por ajuda de meu amigo Jimmy James Patrick Van Höllghan Darth Skull) eu passei a apreciar a simplicidade do mesmo.

Bom, vamos direto ao assunto principal: O que é o SQLite, como funciona, e como usá-lo em conjunto com o Python.

O SQLite é um Sgbd pequeno que ganhou bastante popularidade, popularidade esta mais do que merecida. O principal motivo disso se deve porque ele funciona sozinho, sem necessidade de dependências externas. Todas a interação com os dados é feita sem a necessidade de um programa mediador para acessar a base de dados, que fica armazenada em um único arquivo. Por conta disto, não há a necessidade de configurações externas. O SQLite é bem compacto e extremamente rápido (claro, dependendo da carga necessária).

Algumas das vantagens do SQLite são:


* Instalação e configuração muito simples.
* Implementa boa parte do SQL-92.
* Transações ACID - Atomicidade, Consistência, Isolamento e Durabilidade.
* Dados ficam guardados em um único arquivo. Fácil de transportar, fazer backup e manusear.
* Suporta base de dados acima de 2 terabytes.
* Não depende de outras bibliotecas, é escrito em C puro.
* É um software livre e esta disponível sobre domínio público.

Sua implementação para várias linguagens não é segredo para ninguém, no entanto muitas pessoas não sabem quem existem algumas implementações para Python. Uma delas (e a que explicarei mais abaixo) é a pysqlite.

Instalando

Bom, a instalação do SQLite variará do sistema operacional que você estiver usando e, no caso de sistemas linux-like, da forma como sua distribuição efetua instalação.

A instalação dele no Ubuntu ou em outro sistema debian-like é só:
sudo apt-get install sqlite

Uma vez instalado o SQLite, você pode chamar o prompt do sistema pelo comando sqlite3 (ou somente sqlite, se você instalou uma versão anterior). Chamando o sqlite seguido de um nome de arquivo ele gravará o banco naquele arquivo.
$ sqlite teste.db
SQLite version 2.8.17
Enter “.help” for instructions
sqlite>

Dentro do console você pode usar comandos SQL para criar e manipular as entidades do banco ou usar os comandos que começam com um ponto para acessar comandos do SQLite.

Os comandos de uso mais são:

* .database - lista os bancos e arquivos abertos.
* .tables - lista as tabelas do banco.
* .read coisas.sql - lê comandos de SQL dentro da arquivo chamado coisas.sql.
* .mode column - formata a exibição padrão para colunas.
* .headers on - formata a exibição padrão para exibir os nomes dos campos.
* .help - Ajuda

Criando uma pequena agenda de telefones

Vamos agora brincar um pouquinho com o SQLite fazendo alguns testes práticos dentro do próprio SQLite.

Vamos passar para o SQLite esse comando de SQL para criarmos uma simples agenda de números telefónicos.
CREATE TABLE agenda(
id INTEGER PRIMARY KEY,
nome VARCHAR(30),
tel VARCHAR(20)
);

Podemos fazer isso de dentro do SQLite simplesmente digitando linha a linha do comando SQL:
$ sqlite telefones.db
SQLite version 2.8.17
Enter “.help” for instructions
sqlite> CREATE TABLE agenda(
…> id INTEGER PRIMARY KEY,
…> nome VARCHAR(30),
…> tel VARCHAR(20)
…> );
sqlite>

Um jeito prático de práticar SQL é guardar os comandos dentro de um arquivo, por exemplo telefones.sql. Depois você entra no SQLite e abre o arquivo com .read telefones.sql.

Vamos fazer isso com um comando de SQL maior, que vai destruir qualquer tabela que se chame agenda, criar uma tabela agenda e inserir alguns dados nela.
DROP TABLE agenda;
CREATE TABLE agenda(
id INTEGER PRIMARY KEY,
nome VARCHAR(30),
tel VARCHAR(20)
);
INSERT INTO agenda(nome,tel) VALUES ('Escola Técnica Federal de Palmas', '63-3225-1205');
INSERT INTO agenda(nome,tel) VALUES ('Aeroporto de Palmas', '63-3219-3700');
INSERT INTO agenda(nome,tel) VALUES ('Água e Esgoto', '0800-644-0195');
INSERT INTO agenda(nome,tel) VALUES ('Energia Elétrica', '0800-646-4196');
INSERT INTO agenda(nome,tel) VALUES ('Disque-Denúncia', '0800-63-1190');
INSERT INTO agenda(nome,tel) VALUES ('Polícia Civil', '63-3218-5000');
INSERT INTO agenda(nome,tel) VALUES ('Prefeitura Municipal', '63-3218-5000');
INSERT INTO agenda(nome,tel) VALUES ('Alcoólicos Anônimos','63-3215-4165');

Depois abrimos o arquivo pelo SQLite e já podemos fazer algumas consultas:
rolim@sorvete:~$ sqlite telefones.db
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> .read telefones.sql
sqlite> SELECT * FROM agenda;
1|Escola Técnica Federal de Palmas|63-3225-1205
2|Aeroporto de Palmas|63-3219-3700
3|Água e Esgoto|0800-644-0195
4|Energia Elétrica|0800-646-4196
5|Disque-Denúncia|0800-63-1190
6|Polícia Civil|63-3218-5000
7|Prefeitura Municipal|63-3218-5000
8|Alcoólicos Anônimos|63-3215-4165

Isso é o básico do SQLite que você precisa saber agora. Ele têm suporte à maioria dos recursos definidos na SQL-92 (Wikipédia). Não há suporte a Stored Procedures, Triggers Recursivas, Transações Aninhadas. Left Outer Join está implementado, mas Right Outer Join e Full Outer Join não estão. As Views são somente-leitura e a sintaxe para integridade referencial é suportada, mas ignorada pelo SQLite. É um banco simples, e para aplicações de pequeno porte, não há solução melhor.

SQLite no Python

Usar o SQLite no Python não é muito difícil. O pysqlite pode ser baixado no endereço http://initd.org/tracker/pysqlite e instalado usando dois comandos simples: "python setup.py build" e "python setup.py install".

Conectando ao Banco de Dados

Depois de instalada a biblioteca, usar o SQLite não chega a ser difícil. Para iniciarmos o uso, devemos importar o módulo dbapi2, dentro do módulo pysqlite2, da seguinte maneira:
from pysqlite2 import dbapi2 as sqlite

Para conectarmos ao banco, precisamos simplesmente informar o caminho do arquivo do banco:
conn = sqlite.connect('teste.db')

É possível também criar um banco de dados que resida em memoria:
conn = sqlite.connect(':memory:')

Depois de criada a conexão, é necessária a criação de um cursor que irá fazer a interação com o banco:
cursor = conn.cursor()

Escrevendo dados no Banco

Bom, a pouco fizemos um exemplo de agenda no prompt do SQLite. Fazer o mesmo aqui não é algo difícil. Pelo contrário, é tão facil quando usar o prompt interativo:
cursor.execute('CREATE TABLE agenda(id INTEGER PRIMARY KEY, nome VARCHAR(30), tel VARCHAR(20));')

Para inserir dados, da mesma forma:
cursor.execute("INSERT INTO agenda(nome,tel) VALUES ('Guarda Metropolitana', '63-3218-5162');")
cursor.execute("INSERT INTO agenda(nome,tel) VALUES ('Agência Municipal de Trânsito','63-2111-4203');")

Lembrando que como o Python suporta dois tipos de aspas de string.

Nem sempre iremos executar instruções SQL montando strings. Há duas formas de fazê-lo. Uma usando o próprio Python para formatar a string e outra que o SQLite implementa:
nome = 'Secretaria Municipal de Meio Ambiente Ciência e Tecnologia'
tel = '63-3218-5457'

Via SQLite: cursor.execute('INSERT INTO agenda(nome,tel) VALUES (?, ?)', (nome, tel))
Via Python: cursor.execute('INSERT INTO agenda(nome,tel) VALUES (%s, %s)' % (nome, tel))

Para salvar nossas alterações, usa-se o comando:
conn.commit()

Se você tentar fechar a conexão com o banco sem ter salvado informações pendentes no banco, então o pysqlite irá lançar uma exceção. Este comportamento pode vir a trazer problemas. No entanto, se você não quer salvar as informações que foram lançadas desde o último commit(), você pode usar o comando:
conn.rollback()

Estes dois comandos são na realidade, os grandes responsáveis pela atomicidade das operações realizadas com bancos de dados, evitando que dados incompletos sejam salvos no banco.

Lendo dados do Banco

Ler dados no banco é tão simples quando escrever. Vamos pegar o nosso banco de dados criado anteriormente
cursor.execute('SELECT * FROM agenda')

Agora o cursor contêm os dados referentes à consulta efetuada.
>>> cursor.fetchall()
[(1, u'Escola Técnica Federal de Palmas', u'63-3225-1205'), (2, u'Aeroporto de Palmas', u'63-3219-3700'), (3, u'Água e Esgoto', u'0800-644-0195'), (4, u'Energia Elétrica', u'0800-646-4196'), (5, u'Disque-Denúncia', u'0800-63-1190'), (6, u'Polícia Civil', u'63-3218-5000'), (7, u'Prefeitura Municipal', u'63-3218-5000'), (8, u'Alcoólicos Anônimos', u'63-3215-4165')]

Este comando nos dá todos os dados da tabela agenda na forma de uma lista de tuplas, com cada tupla representando uma linha da pesquisa. Obviamente, podemos fazer mais que isso:
>>> cursor.execute('SELECT * from agenda')
<pysqlite2.dbapi2.Cursor object at 0x83357d0>
>>> for linha in cursor:
... print '-'*10
... print 'ID: %s' % (linha[0],)
... print 'Nome: %s' % (linha[1],)
... print 'Tel: %s' % (linha[2],)
...
----------
ID: 1
Nome: Escola Técnica Federal de Palmas
Tel: 63-3225-1205
----------
PK: 2
Nome: Aeroporto de Palmas
Tel: 63-3219-3700
----------

Interagir com o cursor funciona muito bem se você não precisar usar os dados após a iteração. Se você pretende trabalhar com os dados, é melhor recebermos a lista.

O método fetchall() é uma maneira fácil de se trabalhar com os registros, já que você recebe todos os registros retornados pela instrução. No entanto, nem sempre esta abordagem é a mais eficiente. Para isto, você pode usar o comando fetchmany():
>>> cursor.execute('SELECT * from agenda')
<pysqlite2.dbapi2.Cursor object at 0x83357d0>
>>> cursor.fetchmany(2)
[(1, u'Escola Técnica Federal de Palmas', u'63-3225-1205'), (2, u'Aeroporto de Palmas', u'63-3219-3700')]
>>> cursor.fetchmany(3)
[(3, u'Água e Esgoto', u'0800-644-0195'), (4, u'Energia Elétrica', u'0800-646-4196'), (5, u'Disque-Denúncia', u'0800-63-1190')]
>>> cursor.fetchmany()
[(6, u'Polícia Civil', u'63-3218-5000')]

Como você pôde ver, o fetchmany retorna uma lista de tuplas de no máximo a quantidade informada no parâmetro da função. Quando nenhum parâmetro é informado, a função retorna uma lista com somente uma tupla.

Por último, temos o metodo fetchone(). Este método retorna uma linha do cursor. Mas ao contrário de fetchmany(), fetchone() não retorna uma lista com uma única tupla dentro. Ela retorna únicamente a tupla. A vantagem deste método está em quando uma instrução SQL irá retornar somente um unico registro (por exemplo, SELECT * from agenda WHERE id = 1).
>>> cursor.fetchone()
(7, u'Prefeitura Municipal', u'63-3218-5000')

Algumas pessoas me perguntaram porque existirem duas funções diferentes que fazem a mesma coisa. No caso, fetchone() e next(). Bom, antes de explicar, devemos lembrar que quando fazemos algum fetch no cursor, as linhas envolvidas no processo são retiradas deste. next() também faz isto, só que ao contrário de fetchone(), quando você chama next() estando no final do cursor, o mesmo retorna uma exceção do tipo StopIteration, enquanto as funções de fetch retornam None.
>>> cursor.next()
(8, u'Alcoólicos Anônimos', u'63-3215-4165')
>>> cursor.next()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
StopIteration
>>> cursor.fetchone()
>>>

Adaptando e Convertendo

Um recurso muito interessante do pysqlite é a sua habilidade de armazenar representações de outros tipos de informação dentro do banco de dados. Por exemplo, suponhamos que nós tenhamos uma classe cujas instâncias nós queiramos armazenar dentro do banco de dados. SQLite só irá armazenar alguns poucos tipos de informação, mas podemos forçar as instâncias de nossa classe a se adaptar à um dos tipos do SQLite.

Antes de qualquer coisa, precisamos fechar o cursor e a conexão com o banco e reabrir com algumas instruções para reconhecer novos tipos de dados:
>>> cursor.close()
>>> conn.close()
>>> conn = sqlite.connect('teste.db', detect_types = sqlite.PARSE_DECLTYPES)
>>> cursor = conn.cursor()

Agora, vamos criar uma classe para nosso novo tipo de dado. Continuando com nomes e telefones, vamos criar uma classe Contato, que irá armazenar o nome do contato e seu telefone.
>>> class Contato (object):
... def __init__(self, nome, telefone):
... self.nome = nome
... self.telefone = telefone

Agora vamos criar uma tabela que irá armazenar um identificador e um campo de informação, que será utilizado pelo nosso novo tipo de dado:
>>> cursor.execute('CREATE TABLE Contatos (id INTEGER PRIMARY KEY, information Contato)')
>>> cursor.commit()

Agora, nós precisamos criar uma forma de que nosso seja convertido em texto e vice-versa:
>>> def adaptaContato (contato):
return contato.nome + "\n" + contato.telefone
>>> def converteContato (texto):
texto = texto.split("n")
return Contato(texto[0], texto[1])

A primeira função irá colocar uma quebra de linha entre o nome e o telefone, que será utilizado na função de conversão para separar as duas informações. Agora, nós precisamos registrar estas duas funções, uma como adapter e a outra como converter:
>>> sqlite.register_adapter(Contato, adaptaContato)
>>> sqlite.register_converter("Contato", converteContato)

Agora que tudo está setado corretamente, Nós temos condições de inserir diretamente as instâncias dentro do banco:
>>> Policia = Contato('Polícia Civil', '190')
>>> cursor.execute('INSERT INTO Contatos VALUES (null, ?)', (Policia,))
>>> conn.commit()

E podemos recuperar o objeto:
>>> cursor.execute('SELECT * FROM Contatos')
>>> Contato = cursor.fetchone()
>>> print Contato[1].nome, Contato[1].telefone
Polícia Civil 190

Conclusões

O SQLite é realmente um Sgbd incrível. Ao contrário de outros Sgbds que requerem um servidor de banco de dados (mesmo o Firebird Embedded requer), SQLite funciona como uma biblioteca que usa arquivos como banco de dados. Além disso, não necessita de nenhuma configuração externa, tornando-o extremamente portável e, combinado com o tamanho do arquivo, o torna ideal para projetos de vários tamanhos. A biblioteca pysqlite aproveita estes benefícios e permite que programadores Python façam uso fácil deste banco de dados.

Espero que vocês tenham gostado do artigo. Ele é um pouco longo, mas prefiro assim, informações centralizadas do que espalhadas.

Até um próximo post.

P.S.: Estes telefones são todos de Palmas, Tocantins. Façam bom uso hehehe