Python para Desenvolvedores

2ª edição, revisada e ampliada

Capítulo 28: Banco de dados


Sistemas Gerenciadores de Banco de Dados (SGBDs) são reconhecidos por prover uma forma de acesso consistente e confiável para informações.

A maioria dos SGDB atuais são baseados no modelo relacional, no qual as informações são representadas na forma de tabelas. Geralmente, estas tabelas podem ser consultadas através de uma linguagem especializada para isso, chamada SQL (Structured Query Language).

Cliente Servidor

Geralmente, os SGBDs utilizam a arquitetura cliente-servidor. Os aplicativos usam a API cliente para poder se comunicar com o servidor, que é o responsável por receber as consultas dos clientes, interpretar as sentenças SQL e recuperar os dados com um tempo de resposta adequado.

Para fazer isso, o servidor precisa realizar uma série de outras tarefas, tais como: verificar credenciais, controlar o acesso, gerenciar conexões de rede, manter a integridade dos dados, otimizar as consultas e resolver questões de concorrência.

No Python, a integração com SGBDs é feita na maioria dos casos através de um módulo DBI., que usa a API cliente para se comunicar com o banco de dados.

DBI

Database Interface (DBI) é uma especificação que descreve como deve ser o comportamento de um módulo de acesso a sistemas de banco de dados.

Diagrama DBI

A DBI define que o módulo deve ter uma função connect(), retorna objetos de conexão. A partir do do objeto conexão, é possível obter um objeto cursor, que permite a execução de sentenças SQL e a recuperação dos dados (uma lista de tuplas com os resultados, por default).

MySQL

O MySQL é um SGBD cliente-servidor reconhecido pelo bom desempenho e é bastante usado como backend para aplicações Web.

Exemplo de acesso através de DBI com MySQL:

In [1]:
import MySQLdb

# Cria uma conexão
con = MySQLdb.connect(db='test', user='root', passwd='root123')

# Cria um cursor
cur = con.cursor()

# Executa um comando SQL
cur.execute('show databases')

# Recupera o resultado
recordset = cur.fetchall()

# Mostra o resultado
for record in recordset:
    print record

# Fecha a conexão
con.close()
('information_schema',)
('fouling',)
('mysql',)
('performance_schema',)
('s3p',)
('test',)
('test_s3p',)

O resultado é composto por uma lista de tuplas com as databases disponíveis no servidor.

SQLite

A partir da versão 2.5, o Python passou a incorporar em sua distribuição um módulo DBI para acessar o SQLite.

SQLite é uma biblioteca Open Source escrita em linguagem C, que implementa um interpretador SQL, e provê funcionalidades de banco de dados, usando arquivos, sem a necessidade de um processo servidor separado ou de configuração manual.

Exemplo:

In [2]:
import sqlite3

# Cria uma conexão e um cursor
con = sqlite3.connect('emails.db')
cur = con.cursor()

# Cria uma tabela
sql = 'create table emails '\
    '(id integer primary key, '\
    'nome varchar(100), '\
    'email varchar(100))'
cur.execute(sql)

# sentença SQL para inserir registros
sql = 'insert into emails values (null, ?, ?)'

# Dados
recset = [('jane doe', '[email protected]'),
    ('rock', '[email protected]')]

# Insere os registros
for rec in recset:
    cur.execute(sql, rec)

# Confirma a transação
con.commit()

# Seleciona todos os registros
cur.execute('select * from emails')

# Recupera os resultados
recset = cur.fetchall()

# Mostra
for rec in recset:
    print '%d: %s(%s)' % rec

# Fecha a conexão
con.close()
1: jane doe([email protected])
2: rock([email protected])

A vantagem mais significativa de usar o SQLite é a praticidade, principalmente no uso em aplicativos locais para desktops, aonde usar um SGBD convencional seria desnecessário e complicado de manter.

Firebird

Firebird é um SGBD cliente-servidor leve, porém com muitos recursos. Programas em Python podem se comunicar com ele através do driver DBI FDB.

Exemplo:

In [1]:
import fdb

#Para criar a base no Ubuntu com Firebird 2.5
# isql-fb
# create database '/tmp/cds.fdb';
#
# conecta o Firebird
con = fdb.connect(dsn='localhost:/tmp/cds.fdb',
    user='sysdba', password='sysdba')

# Cria um objeto cursor
cur = con.cursor()

sql = "create table cds("\
"nome varchar(20),"\
"artista varchar(20),"\
"ano integer,"\
"faixas integer,"\
"primary key(nome, artista, ano));"

# Cria uma tabela
cur.execute(sql)

# Grava as modificações
con.commit()

dados = [
    ('IV', 'Led Zeppelin', 1971, 8),
    ('Zenyattà Mondatta', 'The Police', 1980, 11),
    ('OK Computer', 'Radiohead', 1997, 12),
    ('In Absentia', 'Porcupine Tree', 2002, 12),
  ]

# Insere os registros e faz a interpolação
insert = "insert into cds"\
"(nome, artista, ano, faixas) values (?, ?, ?, ?)"
cur.executemany(insert, dados)
con.commit()

# Consulta os registros
cur.execute("select * from cds order by ano")

# Recupera os resultados
for reg in cur.fetchall():
        # Formata e imprime
        print ' - '.join(str(i) for i in reg)
---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
<ipython-input-1-d1aa47bed186> in <module>()
      7 # conecta o Firebird
      8 con = fdb.connect(dsn='localhost:/tmp/cds.fdb',
----> 9     user='sysdba', password='sysdba')
     10 
     11 # Cria um objeto cursor

/usr/local/lib/python2.7/dist-packages/fdb/fbcore.pyc in connect(dsn, user, password, host, port, database, sql_dialect, role, charset, buffers, force_write, no_reserve, db_key_scope, isolation_level, connection_class)
    691     if db_api_error(_isc_status):
    692         raise exception_from_status(DatabaseError, _isc_status,
--> 693                                     "Error while connecting to database:")
    694 
    695     return connection_class(_db_handle, dpb, sql_dialect, charset)

DatabaseError: ('Error while connecting to database:\n- SQLCODE: -923\n- connection rejected by remote interface', -923, 335544421)

Como o Firebird não requer muita potência e nem muito esforço para administração, ele pode ser usado tanto como servidor, quanto ser empacotado junto com um aplicativo desktop.

PostgreSQL

Para sistemas que demandam recursos mais sofisticados do SGBD, o PostgreSQL é a solução Open Source mais completa disponível. O software segue a arquitetura cliente-servidor e é distribuído sob a licença BSD.

Entre os recursos oferecidos pelo PostgreSQL, destacam-se:

  • Suporte a consultas complexas.
  • Transações.
  • Controle de concorrência multi-versão.
  • Tipos de objetos definidos pelo usuário.
  • Herança.
  • Views.
  • Stored Procedures.
  • Triggers.
  • Full text search.

Existem vários módulos que provêm acesso ao PostgreSQL para o Python, como o PygreSQL e o Psycopg.

O PygreSQL oferece duas interfaces distintas para acesso a servidores PostgreSQL:

  • pgdb: módulo compatível com DBI.
  • pg: módulo mais antigo, incompatível com DBI.

Exemplo com pgdb:

In [2]:
import pgdb

# Para bancos de dados locais (via Unix Domain Sockets)
#con = pgdb.connect(database='music')

# Via TCP/IP
con = pgdb.connect(host='tao', database='music', user='pg', password='pg123')
cur = con.cursor()

# Cria uma tabela
sql = 'create table tracks '\
   '(id serial primary key, '\
   'track varchar(100), '\
   'band varchar(100))'
cur.execute(sql)

# A interpolação usa uma notação semelhante a do Python
sql = 'insert into tracks values (default, %s, %s)'

# Dados
recset = [('Kashmir', 'Led Zeppelin'),
   ('Starless', 'King Crimson')]

# Insere os registros
for rec in recset:
   cur.execute(sql, rec)

con.commit()

# Recupera os registros
cur.execute('select * from tracks')

# Recupera os resultados
recset = cur.fetchall()
# Mostra
for rec in recset:
   print rec

con.close()
[1, 'Kashmir', 'Led Zeppelin']
[2, 'Starless', 'King Crimson']

Exemplo com pg:

In [4]:
import pg
# Para bancos de dados locais (via Unix Domain Sockets)
#con = pg.connect('music')

# Via TCP/IP
con = pg.connect(host='tao', dbname='music', user='pg', passwd='pg123')

# Realiza uma consulta no banco
qry = con.query('select * from tracks')

# Pega a lista de campos
flds = qry.listfields()

# Mostra os resultados
for rec in qry.dictresult():
   for fld in flds:
       print '%s: %s' % (fld, rec[fld])
   print

con.close()
id: 1
track: Kashmir
band: Led Zeppelin

id: 2
track: Starless
band: King Crimson

Exemplo usando o Psycopg:

In [1]:
import psycopg2

# Para bancos de dados locais (via Unix Domain Sockets)
#con = psycopg2.connect(database='music')

# Via TCP/IP
con = psycopg2.connect(host='tao', database='music',
   user='pg', password='pg123')
cur = con.cursor()

sql = 'insert into tracks values (default, %s, %s)'
recset = [('Siberian Khatru', 'Yes'),
   ("Supper's Ready", 'Genesis')]
for rec in recset:
   cur.execute(sql, rec)
con.commit()

cur.execute('select * from tracks')
recset = cur.fetchall()
for rec in recset:
   print rec

con.close()
(1, 'Kashmir', 'Led Zeppelin')
(2, 'Starless', 'King Crimson')
(3, 'Siberian Khatru', 'Yes')
(4, "Supper's Ready", 'Genesis')

Como o módulo segue fielmente a especificação DBI, o código é praticamente igual ao exemplo usando o módulo pg. O Psycopg foi projetado com o objetivo de suportar aplicações mais pesadas, com muitas inserções e atualizações.

Também é possível escrever funções para PostgreSQL usando Python. Para que isso seja possível, é preciso habilitar o suporte ao Python no banco, através do utilitário de linha de comando pelo administrador:

createlang plpythonu <banco>

As linguagens que podem usadas pelo PostgreSQL são chamadas Procedural Languages (PL) e o sufixo “u” significa untrusted.

Os tipos dos parâmetros e do retorno da função devem ser definidos durante a criação da função no PostgreSQL.

Exemplo de função:

create function pformat(band text, track text)
  returns text
as $$
  return '%s - %s' % (band, track)
$$ language plpythonu;

O código em Python está entre os símbolos $$.

Saída da função (através do psql):

music=> select pformat(track, band) from tracks;
         pformat         
-------------------------
 Kashmir - Led Zeppelin
 Starless - King Crimson
 Yes - Siberian Khatru
 Genesis - Supper's Ready
(4 registros)

O ambiente de execução de Python no PostgreSQL provê o módulo plpy (importado automaticamente) que é uma abstração para o acesso aos recursos do SGBD.

Exemplo com plpy:

create function inibands()
  returns setof text
as $$
  bands = plpy.execute('select distinct band from tracks order by 1')
  return [''.join(filter(lambda c: c == c.upper(), list(band['band']))) for band in bands]
$$ language plpythonu;

Saída da função (através do utilitário psql):

music=> select inibands();
 inibands 
----------
 KC
 LZ
 Y
 G
(4 registros)

Funções escritas em Python podem ser utilizadas tanto em Stored Procedures quanto Triggers no PostgreSQL.

Existem vários projetos que ampliam os recursos do PostgreSQL, como o PostGis, que provê suporte a informações espaciais, usadas em GIS (Geographic Information Systems).

Mapeamento objeto-relacional

Object-Relational Mapper (ORM) é uma camada que se posiciona entre o código com a lógica da aplicação e o módulo DBI, com o objetivo de reduzir as dificuldades geradas pelas diferenças entre a representação de objetos (da linguagem) e a representação relacional (do banco de dados).

Mapeamento objeto-relacional

Com o uso de um ORM:

  • A aplicação se torna independente do SGDB.
  • O desenvolvedor não precisa usar SQL diretamente.
  • A lógica para gerenciamento das conexões é realizada de forma transparente pelo ORM.

Exemplo de ORM (com SQLAlchemy):

In [1]:
from sqlalchemy import *

# URL => driver://username:[email protected]:port/database
# No SQLite:
#   sqlite:// (memória)
#   sqlite:///arquivo (arquivo em disco)
db = create_engine('sqlite:///progs.db')

# Torna acessível os metadados
metadata = MetaData(db)

# Ecoa o que SQLAlchemy está fazendo
metadata.bind.echo = True

# Tabela Prog
prog_table = Table('progs', metadata,
    Column('prog_id', Integer, primary_key=True),
    Column('name', String(80)))

# Cria a tabela
prog_table.create()

# Carrega a definição da tabela
prog_table = Table('progs', metadata, autoload=True)

# Insere dados
i = prog_table.insert()
i.execute({'name': 'Yes'}, {'name': 'Genesis'},
    {'name': 'Pink Floyd'}, {'name': 'King Crimson'})

# Seleciona
s = prog_table.select()
r = s.execute()

for row in r.fetchall():
    print row
2013-08-16 10:27:18,932 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE progs (
	prog_id INTEGER NOT NULL, 
	name VARCHAR(80), 
	PRIMARY KEY (prog_id)
)


2013-08-16 10:27:18,933 INFO sqlalchemy.engine.base.Engine ()
2013-08-16 10:27:19,126 INFO sqlalchemy.engine.base.Engine COMMIT
2013-08-16 10:27:19,141 INFO sqlalchemy.engine.base.Engine INSERT INTO progs (name) VALUES (?)
2013-08-16 10:27:19,141 INFO sqlalchemy.engine.base.Engine (('Yes',), ('Genesis',), ('Pink Floyd',), ('King Crimson',))
2013-08-16 10:27:19,143 INFO sqlalchemy.engine.base.Engine COMMIT
2013-08-16 10:27:19,253 INFO sqlalchemy.engine.base.Engine SELECT progs.prog_id, progs.name 
FROM progs
2013-08-16 10:27:19,254 INFO sqlalchemy.engine.base.Engine ()
(1, u'Yes')
(2, u'Genesis')
(3, u'Pink Floyd')
(4, u'King Crimson')

Além dos SQLAlchemy, também existem disponíveis para Python o SQLObject e ORMs que integram frameworks maiores, como o Django.

In [1]:
 
Out[1]: