conexão de um banco de dados

Conectando e desconectando do banco
Podemos criar o banco de dados de duas formas: na memória RAM

# conectando...
conn = sqlite3.connect(':memory:')
ou persistindo em um banco de dados, vamos usar sempre este caso.

# conectando...
conn = sqlite3.connect('clientes.db')
Uma sintaxe mínima para se conectar a um banco de dados é:

# connect_db.py
# 01_create_db.py
import sqlite3

conn = sqlite3.connect('clientes.db')
conn.close()
O último método desconecta do banco.

Considere um arquivo para cada operação.

Nota: Os arquivos estão numerados apenas para sugerir uma sequência.

Criando um banco de dados
O código para criar um banco de dados é o mesmo mencionado anteriormente.

Para rodar este programa abra o terminal e digite:

$ python3 01_create_db.py
$ ls *.db
Digitando ls você verá que o banco foi criado.

Criando uma tabela
Para criar uma tabela no banco de dados usamos dois métodos fundamentais:

cursor: é um interador que permite navegar e manipular os registros do bd.
execute: lê e executa comandos SQL puro diretamente no bd.
# 02_create_schema.py
import sqlite3

# conectando...
conn = sqlite3.connect('clientes.db')
# definindo um cursor
cursor = conn.cursor()

# criando a tabela (schema)
cursor.execute("""
CREATE TABLE clientes (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        nome TEXT NOT NULL,
        idade INTEGER,
        cpf     VARCHAR(11) NOT NULL,
        email TEXT NOT NULL,
        fone TEXT,
        cidade TEXT,
        uf VARCHAR(2) NOT NULL,
        criado_em DATE NOT NULL
);
""")

print('Tabela criada com sucesso.')
# desconectando...
conn.close()
Para executar digite no terminal:

$ python3 02_create_schema.py
$ sqlite3 clientes.db '.tables'
$ sqlite3 clientes.db 'PRAGMA table_info(clientes)'
Digitando sqlite3 clientes.db '.tables' você verá que a tabela foi criada.

E o comando sqlite3 clientes.db 'PRAGMA table_info(clientes)' retorna os campos da tabela.

Nota: A única diferença, caso você use Python 2 é no print, onde você deve tirar os parênteses. E no início do arquivo é recomendável que se defina a codificação utf-8, que no caso do Python 3 já é padrão.

# 02_create_schema.py
# -*- coding: utf-8 -*-
# usando Python 2
import sqlite3
...
print 'Tabela criada com sucesso.'
Agora vamos fazer o CRUD. Começando com a letra

Create - Inserindo um registro com comando SQL
A única novidade aqui é o método commit(). É ele que grava de fato as alterações na tabela. Lembrando que uma tabela é alterada com as instruções SQL ``INSERT, UPDATE`` e ``DELETE``.

# 03_create_data_sql.py
import sqlite3

conn = sqlite3.connect('clientes.db')
cursor = conn.cursor()

# inserindo dados na tabela
cursor.execute("""
INSERT INTO clientes (nome, idade, cpf, email, fone, cidade, uf, criado_em)
VALUES ('Regis', 35, '00000000000', 'regis@email.com', '11-98765-4321', 'Sao Paulo', 'SP', '2014-06-08')
""")

cursor.execute("""
INSERT INTO clientes (nome, idade, cpf, email, fone, cidade, uf, criado_em)
VALUES ('Aloisio', 87, '11111111111', 'aloisio@email.com', '98765-4322', 'Porto Alegre', 'RS', '2014-06-09')
""")

cursor.execute("""
INSERT INTO clientes (nome, idade, cpf, email, fone, cidade, uf, criado_em)
VALUES ('Bruna', 21, '22222222222', 'bruna@email.com', '21-98765-4323', 'Rio de Janeiro', 'RJ', '2014-06-09')
""")

cursor.execute("""
INSERT INTO clientes (nome, idade, cpf, email, fone, cidade, uf, criado_em)
VALUES ('Matheus', 19, '33333333333', 'matheus@email.com', '11-98765-4324', 'Campinas', 'SP', '2014-06-08')
""")

# gravando no bd
conn.commit()

print('Dados inseridos com sucesso.')

conn.close()
Para executar digite no terminal:

$ python3 03_create_data_sql.py
Inserindo n registros com uma tupla de dados
Usando uma lista podemos inserir vários registros de uma vez, e o método executemany faz essa ação.

# 04_create_data_nrecords.py
import sqlite3

conn = sqlite3.connect('clientes.db')
cursor = conn.cursor()

# criando uma lista de dados
lista = [(
    'Fabio', 23, '44444444444', 'fabio@email.com', '1234-5678', 'Belo Horizonte', 'MG', '2014-06-09'),
    ('Joao', 21, '55555555555', 'joao@email.com',
     '11-1234-5600', 'Sao Paulo', 'SP', '2014-06-09'),
    ('Xavier', 24, '66666666666', 'xavier@email.com', '12-1234-5601', 'Campinas', 'SP', '2014-06-10')]

# inserindo dados na tabela
cursor.executemany("""
INSERT INTO clientes (nome, idade, cpf, email, fone, cidade, uf, criado_em)
VALUES (?,?,?,?,?,?,?,?)
""", lista)

conn.commit()

print('Dados inseridos com sucesso.')

conn.close()
Observe o uso de ? isto significa que no lugar de cada ? entrará os valores da lista na sua posição respectiva. É o que nós chamamos de parâmetros de entrada.

Para executar digite no terminal:

$ python3 04_create_data_nrecords.py
Inserindo um registro com parâmetros de entrada definido pelo usuário
Neste exemplo usaremos parâmetros de entrada, que deverá ser digitado pelo usuário. Esta é a forma mais desejável de entrada de dados porque o usuário pode digitar os dados em tempo de execução.

# 05_create_data_param.py
import sqlite3

conn = sqlite3.connect('clientes.db')
cursor = conn.cursor()

# solicitando os dados ao usuário
p_nome = input('Nome: ')
p_idade = input('Idade: ')
p_cpf = input('CPF: ')
p_email = input('Email: ')
p_fone = input('Fone: ')
p_cidade = input('Cidade: ')
p_uf = input('UF: ')
p_criado_em = input('Criado em (yyyy-mm-dd): ')

# inserindo dados na tabela
cursor.execute("""
INSERT INTO clientes (nome, idade, cpf, email, fone, cidade, uf, criado_em)
VALUES (?,?,?,?,?,?,?,?)
""", (p_nome, p_idade, p_cpf, p_email, p_fone, p_cidade, p_uf, p_criado_em))

conn.commit()

print('Dados inseridos com sucesso.')

conn.close()
Nota: Caso use Python 2 use o método raw_input() em

# python 2
p_nome = raw_input('Nome: ')
...
print 'Dados inseridos com sucesso.'
Para executar digite no terminal:

$ python3 05_create_data_param.py
Veja a interação do programa:

Nome: Regis
Idade: 35
CPF: 30020030011
Email: regis@email.com
Fone: 11 9537-0000
Cidade: Sao Paulo
UF: SP
Criado em (yyyy-mm-dd): 2014-06-15
Dados inseridos com sucesso.
Read - Lendo os dados
Aqui nós usamos o famoso SELECT. O método fetchall() retorna o resultado do SELECT.

# 06_read_data.py
import sqlite3

conn = sqlite3.connect('clientes.db')
cursor = conn.cursor()

# lendo os dados
cursor.execute("""
SELECT * FROM clientes;
""")

for linha in cursor.fetchall():
    print(linha)

conn.close()
Para executar digite no terminal:

$ python3 06_read_data.py
Eis o resultado:

(1, 'Regis', 35, '00000000000', 'regis@email.com', '11-98765-4321', 'Sao Paulo', 'SP', '2014-06-08')
(2, 'Aloisio', 87, '11111111111', 'aloisio@email.com', '98765-4322', 'Porto Alegre', 'RS', '2014-06-09')
(3, 'Bruna', 21, '22222222222', 'bruna@email.com', '21-98765-4323', 'Rio de Janeiro', 'RJ', '2014-06-09')
(4, 'Matheus', 19, '33333333333', 'matheus@email.com', '11-98765-4324', 'Campinas', 'SP', '2014-06-08')
(5, 'Fabio', 23, '44444444444', 'fabio@email.com', '1234-5678', 'Belo Horizonte', 'MG', '2014-06-09')
(6, 'Joao', 21, '55555555555', 'joao@email.com', '11-1234-5600', 'Sao Paulo', 'SP', '2014-06-09')
(7, 'Xavier', 24, '66666666666', 'xavier@email.com', '12-1234-5601', 'Campinas', 'SP', '2014-06-10')
(8, 'Regis', 35, '30020030011', 'regis@email.com', '11 9750-0000', 'Sao Paulo', 'SP', '2014-06-15')
Update - Alterando os dados
Observe o uso das variáveis id_cliente onde definimos o id a ser alterado, novo_fone e novo_criado_em usados como parâmetro para alterar os dados. Neste caso, salvamos as alterações com o método commit().

# 07_update_data.py
import sqlite3

conn = sqlite3.connect('clientes.db')
cursor = conn.cursor()

id_cliente = 1
novo_fone = '11-1000-2014'
novo_criado_em = '2014-06-11'

# alterando os dados da tabela
cursor.execute("""
UPDATE clientes
SET fone = ?, criado_em = ?
WHERE id = ?
""", (novo_fone, novo_criado_em, id_cliente))

conn.commit()

print('Dados atualizados com sucesso.')

conn.close()
Para executar digite no terminal:

$ python3 07_update_data.py
Delete - Deletando os dados
Vamos excluir um registro pelo seu id.

# 08_delete_data.py
import sqlite3

conn = sqlite3.connect('clientes.db')
cursor = conn.cursor()

id_cliente = 8

# excluindo um registro da tabela
cursor.execute("""
DELETE FROM clientes
WHERE id = ?
""", (id_cliente,))

conn.commit()

print('Registro excluido com sucesso.')

conn.close()
Para executar digite no terminal:

$ python3 08_delete_data.py
Adicionando uma nova coluna
Para inserir uma nova coluna na tabela usamos o comando SQL ALTER TABLE.

# 09_alter_table.py
import sqlite3

conn = sqlite3.connect('clientes.db')
cursor = conn.cursor()

# adicionando uma nova coluna na tabela clientes
cursor.execute("""
ALTER TABLE clientes
ADD COLUMN bloqueado BOOLEAN;
""")

conn.commit()

print('Novo campo adicionado com sucesso.')

conn.close()
Para executar digite no terminal:

$ python3 09_alter_table.py
Lendo as informações do banco de dados
Para ler as informações da tabela usamos o comando PRAGMA.

Para listar as tabelas do banco usamos o comando SELECT name FROM sqlite_master ....

Para ler o schema da tabela usamos o comando SELECT sql FROM sqlite_master ....

# 10_view_table_info.py
import sqlite3

conn = sqlite3.connect('clientes.db')
cursor = conn.cursor()
nome_tabela = 'clientes'

# obtendo informações da tabela
cursor.execute('PRAGMA table_info({})'.format(nome_tabela))

colunas = [tupla[1] for tupla in cursor.fetchall()]
print('Colunas:', colunas)
# ou
# for coluna in colunas:
#    print(coluna)

# listando as tabelas do bd
cursor.execute("""
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
""")

print('Tabelas:')
for tabela in cursor.fetchall():
    print("%s" % (tabela))

# obtendo o schema da tabela
cursor.execute("""
SELECT sql FROM sqlite_master WHERE type='table' AND name=?
""", (nome_tabela,))

print('Schema:')
for schema in cursor.fetchall():
    print("%s" % (schema))

conn.close()
Para executar digite no terminal:

$ python3 10_view_table_info.py
Eis o resultado:

Colunas: ['id', 'nome', 'idade', 'cpf', 'email', 'fone', 'cidade', 'uf', 'criado_em', 'bloqueado']
Tabelas:
clientes
sqlite_sequence
Schema:
CREATE TABLE clientes (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        nome TEXT NOT NULL,
        idade INTEGER,
        cpf     VARCHAR(11) NOT NULL,
        email TEXT NOT NULL,
        fone TEXT,
        cidade TEXT,
        uf VARCHAR(2) NOT NULL,
        criado_em DATE NOT NULL
, bloqueado BOOLEAN)
Fazendo backup do banco de dados (exportando dados)
Talvez seja este o item mais importante: backup. Observe o uso da biblioteca io que salva os dados num arquivo externo através do método write, e o método iterdump() que exporta a estrutura e dados da tabela para o arquivo externo.

# 11_backup.py
import sqlite3
import io

conn = sqlite3.connect('clientes.db')

with io.open('clientes_dump.sql', 'w') as f:
    for linha in conn.iterdump():
        f.write('%s\n' % linha)

print('Backup realizado com sucesso.')
print('Salvo como clientes_dump.sql')

conn.close()
Para executar digite no terminal:

$ python3 11_backup.py
$ cat clientes_dump.sql
Com o comando cat você poderá ler a estrutura da tabela salva.

Recuperando o banco de dados (importando dados)
Criaremos um novo banco de dados e iremos reconstruir a tabela e os dados com o arquivo clientes_dump.sql. O método read() lê o conteúdo do arquivo clientes_dump.sql e o método executescript() executa as instruções SQL escritas neste arquivo.

# 12_read_sql.py
import sqlite3
import io

conn = sqlite3.connect('clientes_recuperado.db')
cursor = conn.cursor()

f = io.open('clientes_dump.sql', 'r')
sql = f.read()
cursor.executescript(sql)

print('Banco de dados recuperado com sucesso.')
print('Salvo como clientes_recuperado.db')

conn.close()
Para executar digite no terminal:

$ python3 12_read_sql.py
Banco de dados recuperado com sucesso.
Salvo como clientes_recuperado.db
$ sqlite3 clientes_recuperado.db 'SELECT * FROM clientes;'
Com o último comando você verá que os dados estão lá. São e salvo!!!

Leia a continuação deste artigo em Gerenciando banco de dados SQLite3 com Python - Parte 2.