[Python] Psycopg2 e serializzazione delle SELECT in JSON

[Daniele Varrazzo]

On 2014-10-16 13:59, Alberto Granzotto wrote:
> Ciao a tutt*,
> sto giocando un po' con Flask e Psycopg2.

+1

> Dopo tanto tempo passato su MongoDB da una parte e Django + ORM 
> dall'altra,
> mi son rimesso per vari motivi a mettere le mani su SQL e a fare le 
> cose a
> manina. (Sì, son ben consapevole di tutto quello che comporta, ma non 
> ho
> assolutamente voglia di lavorare con ORM e company per questo progetto
> personale).

+1

> Quello che voglio fare è estrarre dati dal DB con SELECT e serializzare 
> i
> record in JSON, perché fornisco i dati da un endpoint della API.
> Sto usando RealDictCursor

Che versione di postgres stai usando? In quelle piu' moderne puoi farti 
aggregare le cose direttamente in json. Vedi se c'e' qualcosa di utile 
in http://www.postgresql.org/docs/9.3/static/functions-json.html

> <http://initd.org/psycopg/docs/extras.html#real-dictionary-cursor> e va
> bene per la tupla corrente che mi ritorna la SELECT. Nel caso però 
> volessi
> fare una JOIN con una o più tabelle le cose cambiano.
> 
> Esempio micro:
> test=> create table users (id serial primary key, name text);
> test=> create table emails (id serial primary key, user_id integer
> references users, email text);
> test=> insert into users (name) values ('alberto');
> test=> insert into emails (user_id, email) values (1, 
> 'alberto a example.org
> ');
> test=> insert into emails (user_id, email) values (1, '
> alberto a lemonparty.org');
> test=> select * from users inner join emails on (users.id = 
> emails.user_id);
> 
>  id |  name   | id | user_id |         email
> ----+---------+----+---------+------------------------
>   1 | alberto |  1 |       1 | alberto a example.org
>   1 | alberto |  2 |       1 | alberto a lemonparty.org
> 
> Ora, lato applicativo Python, quello che faccio è creare un cursore e
> iterare sulle tuple. Sapendo che un utente ha da 0 a N email, a mano 
> creo
> una struttura tipo:
> {
>   'id': 1,
>   'name': 'alberto',
>   'emails': [{
>     'id': 1,
>     'alberto a example.org'
>   }, {
>     'id': 2,
>     'alberto a lemonparty.org' }
>   ]
> }
> 
> Onestamente, fare a mano questa operazione mi sembra abbastanza idiota 
> e
> macchinoso. Tempo fa avevo fatto un iteratore che iterava su dati di 
> tipo
> tabella e automaticamente raggruppava quello che era possibile 
> raggruppare
> (per ragioni di semplicità l'iteratore si aspetta dati ordinati). Mi
> chiedo, rispolvero l'iteratore che mi ero fatto o ci sono modi più 
> eleganti
> e intelligenti per farlo? Insisto sul fatto che non ho molta voglia di
> usare un ORM.
> 
> Best practices? Idee?

Senza scomodare json in postgres, puoi usare array_agg e un composite in 
postgres, registrare quel composite in psycopg ed ottenere e.g. una 
lista di namedtuple per ogni utente, oppure forse anche meglio nel tuo 
caso, una lista di dizionari.

Primo passo: lato postgres, ottenere un record per ogni utente con una 
lista di email. Definisci un tipo coi soli campi che ti servono:

     create type t_email as (id integer, email text);

e riscrivi la tua query in maniera da resitiuire un array di questi 
tipi:
test=> select u.id, u.name, array_agg((e.id, e.email)::t_email) from 
users u join emails e on u.id = e.user_id group by 1,2;

      id |  name   |                        array_agg
     
----+---------+----------------------------------------------------------
       1 | alberto | 
{"(1,alberto a example.org)","(2,alberto a lemonparty.org)"}
     (1 row)

Secondo passo, lato Python, insegna a psycopg che questo tipo di dati 
esiste:

     In [1]: import psycopg2
     In [2]: import psycopg2.extras
     In [3]: cnn=psycopg2.connect('dbname=test')
     In [6]: psycopg2.extras.register_composite('t_email', cnn);

Ora puoi gia' ottenere oggetti distinti: di default sono named tuple:

     In [9]: cur.execute("select u.id, u.name, array_agg((e.id, 
e.email)::t_email) from users u join emails e on u.id = e.user_id group 
by 1,2")

     In [10]: cur.fetchone()
     Out[10]:
     (1,
      'alberto',
      [t_email(id=1, email='alberto a example.org'),
       t_email(id=3, email='alberto a lemonparty.org')])

Terzo passo, come spiegato in 
<http://initd.org/psycopg/docs/extras.html#composite-types-casting> puoi 
personalizzare il modo in cui il tipo viene trasformato in un oggetto 
Python. L'esempio tra l'altro riporta esattamente come trasformare un 
tipo in un dizionario:

     In [11]: class DictComposite(psycopg2.extras.CompositeCaster):
        ....:     def make(self, values):
        ....:         return dict(zip(self.attnames, values))
        ....:

     In [12]: psycopg2.extras.register_composite('t_email', cnn, 
factory=DictComposite)

Mettendo tutto insieme con un RealDictCursor:

     In [13]: dcur = 
cnn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
     In [18]: dcur.execute("select u.id, u.name, array_agg((e.id, 
e.email)::t_email) as emails from users u join emails e on u.id = 
e.user_id group by 1,2")

     In [19]: dcur.fetchone()
     Out[19]:
     {'emails': [
         {'email': 'alberto a example.org', 'id': 1},
         {'email': 'alberto a lemonparty.org', 'id': 2}],
      'id': 1,
      'name': 'alberto'}


-- Daniele