twisted adbapi: nomes de colunas em query

Quem já usou o twisted.enterprise.adbapi deve ter notado a falta de uma funcionalidade muitas vezes necessária na execução de queries (SELECT) no banco, seja ele qual for: colocar o nome das colunas no resultado, de forma que cada linha seja um dicionário e não um simples set.

Considerando que o adbapi é apenas um wrapper do Python Database API v2.0 (PEP-249), obviamente existem motivos pra essa funcionalidade não estar lá (além da preguiça de alguns). No documento, o primeiro item do FAQ:

Question: 

       How can I construct a dictionary out of the tuples returned by
       .fetch*():

    Answer:

       There are several existing tools available which provide
       helpers for this task. Most of them use the approach of using
       the column names defined in the cursor attribute .description
       as basis for the keys in the row dictionary.

       Note that the reason for not extending the DB API specification
       to also support dictionary return values for the .fetch*()
       methods is that this approach has several drawbacks:

       * Some databases don't support case-sensitive column names or
         auto-convert them to all lowercase or all uppercase
         characters.

       * Columns in the result set which are generated by the query
         (e.g.  using SQL functions) don't map to table column names
         and databases usually generate names for these columns in a
         very database specific way.

       As a result, accessing the columns through dictionary keys
       varies between databases and makes writing portable code
       impossible.

Na busca por uma solução, até encontrei um patch pro twisted, que adiciona um método runQueryMapped e retorna uma lista de dicionários, como eu queria. Porém, aplicar patch no twisted é furada, pois o código só funcionaria nas máquinas cujo twisted tem o tal patch. Fora de cogitação.

A solução mais simples (e tosca) que encontrei foi a que funcionou melhor:

# coding: utf-8
# hack for twisted.enterprise.adbapi.ConnectionPool class, providing
# a new method mapQuery (just like runQuery) whose return value
# is a list of dictionaries that map column names to values.

from twisted.enterprise import adbapi

class hackPool(adbapi.ConnectionPool):
    def _mapQuery(self, trans, *args, **kw):
        trans.execute(*args, **kw)
        rs, new_rs = trans.fetchall(), []
        names = [d[0] for d in trans.description]
        for values in rs:
            row = dict()
            for k, v in zip(names, values):
                row[k] = v
            new_rs.append(row)
        return new_rs

    def mapQuery(self, *args, **kw):
        return self.runInteraction(self._mapQuery, *args, **kw)

Dessa maneira, o hack fica no próprio código e não requer nenhum patch. Dá-lhe gambi.


mongodb e twisted

Há algum tempo venho fazendo testes com o MongoDB pra casos específicos onde um RDBM tradicional como MySQL ou PostgreSQL não se encaixa muito bem.

Um dos casos onde um banco de dados baseado em objetos como o MongoDB se encaixa perfeitamente, é em um dos meus sistemas comerciais de telefonia, o Nuswit.

Lá, o usuário pode criar uma campanha de tele mensagem e colocar variáveis, que serão usadas para ligar para as pessoas e falar algumas coisas dinâmicas, sintetizando o texto em voz. Hoje, cada vez que o usuário cria uma campanha, pode importar uma planilha ou arquivo CSV, e então o sistema uma cria nova tabela no Sqlite com os campos que o usuário definiu na campanha, de acordo com essas tais variáveis.

Por isso, não é possível ter uma tabela estática, muito menos fazer relacionamentos pra usar a tabela no estilo chave=valor, pois essa mesma tabela é usada pros relatórios que o usuário baixa após o término da campanha.

Nesse caso, o MongoDB se encaixa perfeitamente. É muito mais simples criar uma coleção de dados com o mesmo nome da campanha, e importar documentos tipo JSON (nome=x, telefone=y, cpf=z) do que criar uma nova tabela com esses campos.

Além do mais, a API do pymongo é muito mais decente do que qualquer coisa parecida com SQL, pois os databases e collections são objetos do Python.

O único problema com o pymongo é que ele foi feito pra controlar e manter um pool de conexões com o banco, totalmente síncrono. Pra usar o pymongo em servidores como os que tenho feito ultimamente, assíncronos, baseados em Twisted, é necessário mandar todas as chamadas do banco pra um thread (usando callInThread ou deferToThread).

Pra solucionar esse problema, passei a frequentar o #mongodb na freenode, e em contato com o autor do pymongo, acabei criando uma versão assíncrona do driver, baseado em Twisted, que mantém o mesmo estilo da API original.

Agora, a integração entre Twisted e MongoDB está muito mais decente, usando pymonga.


postgresql: fast full text search

Atendendo a pedidos por comentários e e-mails que recebi sobre este assunto, decidi colocar aqui um esquema simples para demonstrar o que tenho usado para realizar busca em texto com postgresql.

O resultado é impressionante! Quando há muitos dados na base, é necessário particionar a tabela para diminuir o tamanho dos índices e manter a velocidade na busca – porém, este é um outro assunto que vou guardar para outro artigo.

O lance agora é apenas mostrar de maneira bem básica a construção de uma tabela, função plpgsql e trigger para usar o fast full text search.

Primeiro, alguns dados: crie um arquivo chamado data.txt com apenas duas colunas, separadas por ponto-e-vírgula, contendo nomes e endereços. Exemplo:

maria josé;rua um s/n
josé maria;rua dois s/n
pedro silva;rua um 5782
joaquim silva;rua dois 3030
maria pereira;rua 5, número 10
josé pereira;rua 5, número 15

Depois, é necessário criar uma nova base no postgresql e já importar esses dados nela. O arquivo que preparei pro exemplo já faz todo o trabalho, e provavelmente deverá ser ajustado para as suas necessidades.

Ai vai:


-- zz.sql 20090310 AF
-- fast full text search

-- create table for the data
DROP TABLE IF EXISTS data CASCADE;
CREATE TABLE data (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(32),
    addr    VARCHAR(64),

    name_ts TSVECTOR,
    addr_ts TSVECTOR
);
CREATE INDEX name_ts_idx ON data USING gin(name_ts);
CREATE INDEX addr_ts_idx ON data USING gin(addr_ts);

-- create function and trigger for setting tsvectors
DROP LANGUAGE IF EXISTS plpgsql CASCADE;
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION tsv_update()
RETURNS TRIGGER AS $$
BEGIN
    NEW.name_ts := to_tsvector('portuguese', NEW.name);
    NEW.addr_ts := to_tsvector('portuguese', NEW.addr);
    RETURN NEW;
END
$$
LANGUAGE plpgsql;
CREATE TRIGGER tsv_update_trigger BEFORE INSERT OR UPDATE ON data
    FOR EACH ROW EXECUTE PROCEDURE tsv_update();

-- import data from csv-like into the database
\COPY data (name, addr) FROM 'data.txt' WITH DELIMITER AS E';'

Ao executar este arquivo pelo postgresql, toda a estrutura será automaticamente criada e os dados devidamente importados, inclusive preenchendo as colunas name_ts e addr_ts, que são do tipo tsvector e possibilitam o fast full text search.

Depois disso, basta se conectar no banco e executar a busca. Veja aí (ubuntu):

$ sudo su postgres -c psql
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# CREATE USER zz WITH PASSWORD 'zz';
postgres=# CREATE DATABASE zz OWNER zz;
postgres=# \c zz
You are now connected to database "zz".
zz=# \i zz.sql
...

zz=# SELECT * FROM data WHERE plainto_tsquery('portuguese', 'maria') @@ name_ts;
id |     name      |       addr       |      name_ts       |            addr_ts
----+---------------+------------------+--------------------+--------------------------------
1 | maria josé    | rua um s/n       | 'jos':2 'mar':1    | 'rua':1 's/n':3
2 | josé maria    | rua dois s/n     | 'jos':1 'mar':2    | 'rua':1 's/n':3 'dois':2
5 | maria pereira | rua 5, número 10 | 'mar':1 'pereir':2 | '5':2 '10':4 'rua':1 'númer':3
(3 rows)

zz=# \q

O grande lance é que na busca comum por texto (full text search), o tsvector seria criado em tempo de execução usando a função to_tsvector. Nesse exemplo, já deixei uma função e um trigger para executar esse trabalho e armazenar o resultado na própria tabela, nos campos previamente mencionados, name_ts e addr_ts, que são indexados por um algoritmo diferente, especial para busca em texto, chamado gin.

Assim, a busca é feita direto neles, o que possibilita o fast full text search.