Letture dal Database

Il metodo query

Per leggere il database l'oggetto table offre il metodo query che costruisce l'oggetto SqlQuery usando i parametri

  • columns
  • where
  • order_by
  • group_by
  • limit
  • distinct

Che vanno spezzare il classico statement SQL SELECT in blocchi. In ognuno dei blocchi possiamo far riferimento ai nomi dei campi o a delle variabili. I nomi dei campi passati nel parametro columns iniziano con $ se sono campi della tabella o con @ se sono campi in relazione.

Le variabili utilizzate nel parametro where iniziano sempre con : e si riferiscono a ulteriori parametri passati al metodo query.

In [ ]:
from gnr.app.gnrapp import GnrApp

#istanzio oggetto GnrApp passando il nome dell'istanza di sandbox chiamata sandboxpg
app = GnrApp('sandboxpg')

#dell'oggetto GnrApp mi interessa al momento solo il puntatore all'oggetto GnrSqlDb  
#questo è il mapper corrispondente dal database dell'applicazione

mydb = app.db

clienti_tbl = mydb.table('fatt.cliente')

Il metodo query restituisce un oggetto SqlQuery che viene instanziato con tutti i parametri passati dal metodo.

In [10]:
myquery = clienti_tbl.query(columns="$ragione_sociale", 
                           where='$provincia = :prov',
                              order_by='$n_fatture',
                           prov='MI')

print (myquery.sqltext)

SELECT  t0."ragione_sociale" AS "ragione_sociale",
t0.id AS pkey
 FROM fatt."fatt_cliente" AS t0
WHERE (t0."provincia" = :prov) AND (t0."__del_ts" IS NULL)
ORDER BY (  ( SELECT  COUNT(*) AS "COUNT___"
 FROM fatt."fatt_fattura" AS t0_t0
WHERE (t0_t0."cliente_id"=t0."id") )  )

Il modo più rapido e semplice per ottenere il risultato della query è usando il metodo fetch che restituisce una lista di oggetti analoghi a dizionari.

In [11]:
myquery.fetch()
Out[11]:
[[ragione_sociale=Mario Rossi,pkey=syd9_cQZPm_AxELj3jJKxw],
 [ragione_sociale=Giardini e Giardini,pkey=ja14mgX2P2mRQWUNMWcdKg],
 [ragione_sociale=Porcari Francesco,pkey=g8pa8wDvMIeQW0R8Chn6ZA],
 [ragione_sociale=Rossi Mario,pkey=mf_6EJbWN1unRWkVjRRTgA],
 [ragione_sociale=Bianchi Luigino,pkey=pN1YrXqfPyinfCQBQ2qewQ],
 [ragione_sociale=Effelunga,pkey=eXTjroRqOLqqaP5Ez4vL0g]]
In [12]:
selezione = myquery.fetch()
selezione[0]['ragione_sociale']
Out[12]:
'Mario Rossi'

Colonne in relazione

Una delle peculiarità dell'ORM di Genropy è che propone di effettuare le query dal "punto di vista" di una table di partenza e di poter tuttavia accedere a tutti i campi del database che da quella tabella sono "raggiungibili" attraverso quello che chiamiamo path_relazionale.

Possiamo riferirci a colonne in relazione sia all'interno del parametro column che all'interno del parametro where. Il meccanismo di traduzione dell'ORM creerà le clausole di JOIN necessarie per leggere e testare queste colonne.

In [13]:
rel_query = clienti_tbl.query(columns='$ragione_sociale,@provincia.@regione.nome AS regione', 
                              limit=5)
rel_query.fetch()
Out[13]:
[[ragione_sociale=Camillozzi SPA,regione=Piemonte,pkey=huWxufwWNqeERsBSbHiXbw],
 [ragione_sociale=Effelunga,regione=Lombardia,pkey=eXTjroRqOLqqaP5Ez4vL0g],
 [ragione_sociale=Bianchi Luigino,regione=Lombardia,pkey=pN1YrXqfPyinfCQBQ2qewQ],
 [ragione_sociale=Giardini e Giardini,regione=Lombardia,pkey=ja14mgX2P2mRQWUNMWcdKg],
 [ragione_sociale=Mario Rossi,regione=Lombardia,pkey=syd9_cQZPm_AxELj3jJKxw]]
In [14]:
print (rel_query.sqltext)

SELECT  t0."ragione_sociale" AS "ragione_sociale",
t2."nome" AS regione,
t0.id AS pkey
 FROM fatt."fatt_cliente" AS t0
       LEFT JOIN glbl."glbl_provincia" AS t1 ON t1."sigla" = t0."provincia"
       LEFT JOIN glbl."glbl_regione" AS t2 ON t2."sigla" = t1."regione"
WHERE (t0."__del_ts" IS NULL)
LIMIT 5
In [15]:
rel_query2 = clienti_tbl.query(columns='$ragione_sociale, @provincia.@regione.nome AS regione', 
                            where='@fatture.@righe.@prodotto_id.prezzo_unitario > :soglia_prezzo',
                            soglia_prezzo=200)
rel_query2.fetch()
Out[15]:
[[ragione_sociale=Bianchi Roberto,regione=Lazio,pkey=hbKT6g9mPUGrWPAaPZ3kVg],
 [ragione_sociale=Settoralia srl,regione=Emilia-Romagna,pkey=Ixr_V71QMf6KNV7MUqs3Cw],
 [ragione_sociale=Effelunga,regione=Lombardia,pkey=eXTjroRqOLqqaP5Ez4vL0g],
 [ragione_sociale=Verdi Giuseppe,regione=Emilia-Romagna,pkey=mKHYlQVwNJqyrwbXZyAAtg],
 [ragione_sociale=Porcari Francesco,regione=Lombardia,pkey=g8pa8wDvMIeQW0R8Chn6ZA],
 [ragione_sociale=Rossini Gioacchino,regione=Marche,pkey=GLnTrBCJNbSDR_ksdSgCwA],
 [ragione_sociale=Rockerduck S.p.a.,regione=Veneto,pkey=OyEArIUTNdKfqOUNt9yHeA],
 [ragione_sociale=Puccini Giacomo,regione=Toscana,pkey=IFIE_rmrMOqIHaAj8HrEOg],
 [ragione_sociale=Bianchi Luigino,regione=Lombardia,pkey=pN1YrXqfPyinfCQBQ2qewQ],
 [ragione_sociale=Portacco Gesuino,regione=Sicilia,pkey=YtQO9zvkPtKxYNNAnndbbQ],
 [ragione_sociale=Galimberti srl,regione=Veneto,pkey=zWWqpVSiMASzjnkhyoBcEw],
 [ragione_sociale=Gottari Marisa,regione=Trentino-Alto Adige/S¸dtirol,pkey=M519McwOPZaBESOycDaTEQ],
 [ragione_sociale=Rossi Mario,regione=Lombardia,pkey=mf_6EJbWN1unRWkVjRRTgA],
 [ragione_sociale=Berlucchi Sandionigi Alberto,regione=Liguria,pkey=z5zL6h_hMNeZmVK6IRqpIA],
 [ragione_sociale=Giardini e Giardini,regione=Lombardia,pkey=ja14mgX2P2mRQWUNMWcdKg]]

Alternative alla semplice fetch

Dall'oggetto SqlQuery ho disposizione altri metodi

  • fetchAsDict
  • fetchGrouped
  • selection
In [16]:
second_query = clienti_tbl.query(columns='$ragione_sociale, $provincia, $email', 
                                where='$email IS NOT NULL',
                                limit=10, 
                                order_by='$ragione_sociale DESC')
dict_by_email = second_query.fetchAsDict(key='email')
dict_by_email
Out[16]:
{'giuseppe@verdi.it': [ragione_sociale=Verdi Giuseppe,provincia=PR,email=giuseppe@verdi.it,pkey=mKHYlQVwNJqyrwbXZyAAtg],
 'gioac@rossini.it': [ragione_sociale=Rossini Gioacchino,provincia=PU,email=gioac@rossini.it,pkey=GLnTrBCJNbSDR_ksdSgCwA],
 'info@rockerduck.it': [ragione_sociale=Rockerduck S.p.a.,provincia=PD,email=info@rockerduck.it,pkey=OyEArIUTNdKfqOUNt9yHeA],
 'giapu@hotmail.it': [ragione_sociale=Puccini Giacomo,provincia=LU,email=giapu@hotmail.it,pkey=IFIE_rmrMOqIHaAj8HrEOg],
 'ssss': [ragione_sociale=Gottari Marisa,provincia=TN,email=ssss,pkey=M519McwOPZaBESOycDaTEQ],
 'info@giardiniegiardini.it': [ragione_sociale=Giardini e Giardini,provincia=MI,email=info@giardiniegiardini.it,pkey=ja14mgX2P2mRQWUNMWcdKg],
 'galimberti@gmail.com': [ragione_sociale=Galimberti srl,provincia=VE,email=galimberti@gmail.com,pkey=zWWqpVSiMASzjnkhyoBcEw],
 'info@effelunga.it': [ragione_sociale=Effelunga,provincia=MI,email=info@effelunga.it,pkey=eXTjroRqOLqqaP5Ez4vL0g],
 'berlucchisandionigi@gmail.com': [ragione_sociale=Berlucchi Sandionigi Alberto,provincia=GE,email=berlucchisandionigi@gmail.com,pkey=z5zL6h_hMNeZmVK6IRqpIA]}
In [17]:
dict_by_email['giuseppe@verdi.it']
Out[17]:
[ragione_sociale=Verdi Giuseppe,provincia=PR,email=giuseppe@verdi.it,pkey=mKHYlQVwNJqyrwbXZyAAtg]
In [18]:
grouped_by_provincia = second_query.fetchGrouped(key='provincia')
grouped_by_provincia.keys()
Out[18]:
dict_keys(['PR', 'PU', 'PD', 'LU', 'TN', 'MI', 'VE', 'GE'])
In [19]:
grouped_by_provincia['LU']
Out[19]:
[[ragione_sociale=Puccini Giacomo,provincia=LU,email=giapu@hotmail.it,pkey=IFIE_rmrMOqIHaAj8HrEOg]]
In [20]:
grouped_by_provincia['MI']
Out[20]:
[[ragione_sociale=Giardini e Giardini,provincia=MI,email=info@giardiniegiardini.it,pkey=ja14mgX2P2mRQWUNMWcdKg],
 [ragione_sociale=Effelunga,provincia=MI,email=info@effelunga.it,pkey=eXTjroRqOLqqaP5Ez4vL0g]]

Lettura di un singolo record

In [23]:
tbl_cliente = mydb.table('fatt.cliente')
record_cliente = tbl_cliente.query(where='$id=:pkey',pkey='g8pa8wDvMIeQW0R8Chn6ZA').fetch()[0]
print (record_cliente)

[id=g8pa8wDvMIeQW0R8Chn6ZA,__ins_ts=2014-05-21 13:21:09.638792,__del_ts=None,__mod_ts=2020-02-17 19:53:36.247383,__ins_user=None,ragione_sociale=Porcari Francesco,indirizzo=Via Del Mare,provincia=MI,comune_id=skl2h-b9MUib21-T7X-uyA,cliente_tipo_codice=PV,pagamento_tipo_codice=RD,note=None,email=None,data_iscrizione_newsletter=None,data_disiscrizione_newsletter=None,pkey=g8pa8wDvMIeQW0R8Chn6ZA]
In [24]:
r = tbl_cliente.record(pkey='g8pa8wDvMIeQW0R8Chn6ZA', virtual_columns='$etichetta')
dict_cliente = r.output('dict')
print (dict_cliente)

{'id': 'g8pa8wDvMIeQW0R8Chn6ZA', '__ins_ts': datetime.datetime(2014, 5, 21, 13, 21, 9, 638792), '__del_ts': None, '__mod_ts': datetime.datetime(2020, 2, 17, 19, 53, 36, 247383), '__ins_user': None, 'ragione_sociale': 'Porcari Francesco', 'indirizzo': 'Via Del Mare', 'provincia': 'MI', 'comune_id': 'skl2h-b9MUib21-T7X-uyA', 'cliente_tipo_codice': 'PV', 'pagamento_tipo_codice': 'RD', 'note': None, 'email': None, 'data_iscrizione_newsletter': None, 'data_disiscrizione_newsletter': None}
In [26]:
dict_cliente['indirizzo']
Out[26]:
'Via Del Mare'
In [27]:
rec_cliente = tbl_cliente.record('ja14mgX2P2mRQWUNMWcdKg').output('bag')
rec_cliente.keys()
Out[27]:
['id',
 '__ins_ts',
 '__del_ts',
 '__mod_ts',
 '__ins_user',
 'ragione_sociale',
 'indirizzo',
 'provincia',
 '@provincia',
 'comune_id',
 '@comune_id',
 'cliente_tipo_codice',
 '@cliente_tipo_codice',
 'pagamento_tipo_codice',
 '@pagamento_tipo_codice',
 'note',
 'email',
 'data_iscrizione_newsletter',
 'data_disiscrizione_newsletter',
 '@fatture']
In [28]:
rec_cliente['@provincia.@comuni'].digest('#v.denominazione,#v.popolazione_residente')
Out[28]:
[('Milano', 1324110),
 ('Abbiategrasso', 32035),
 ('Albairate', 4680),
 ('Arconate', 6499),
 ('Arese', 19506),
 ('Arluno', 11601),
 ('Assago', 8168),
 ('Bareggio', 17254),
 ('Basiano', 3686),
 ('Basiglio', 8003),
 ('Bellinzago Lombardo', 3830),
 ('Bernate Ticino', 3117),
 ('Besate', 2032),
 ('Binasco', 7268),
 ('Boffalora sopra Ticino', 4290),
 ('Bollate', 36467),
 ('Bresso', 26399),
 ('Bubbiano', 2193),
 ('Buccinasco', 26937),
 ('Buscate', 4793),
 ('Bussero', 8645),
 ('Busto Garolfo', 13513),
 ('Calvignasco', 1196),
 ('Cambiago', 6498),
 ('Canegrate', 12431),
 ('Carpiano', 3916),
 ('Carugate', 14540),
 ('Casarile', 3915),
 ('Casorezzo', 5384),
 ("Cassano d'Adda", 18767),
 ("Cassina de' Pecchi", 13130),
 ('Cassinetta di Lugagnano', 1879),
 ('Castano Primo', 11026),
 ('Cernusco sul Naviglio', 31058),
 ('Cerro al Lambro', 4925),
 ('Cerro Maggiore', 14794),
 ('Cesano Boscone', 23935),
 ('Cesate', 13977),
 ('Cinisello Balsamo', 74150),
 ('Cisliano', 4057),
 ('Cologno Monzese', 47794),
 ('Colturano', 1975),
 ('Corbetta', 17365),
 ('Cormano', 20270),
 ('Cornaredo', 20546),
 ('Corsico', 34507),
 ('Cuggiono', 8227),
 ('Cusago', 3547),
 ('Cusano Milanino', 19547),
 ('Dairago', 5780),
 ('Dresano', 2992),
 ('Gaggiano', 8975),
 ('Garbagnate Milanese', 27193),
 ('Gessate', 8690),
 ('Gorgonzola', 19649),
 ('Grezzago', 2804),
 ('Gudo Visconti', 1720),
 ('Inveruno', 8686),
 ('Inzago', 10541),
 ('Lacchiarella', 8480),
 ('Lainate', 25343),
 ('Legnano', 59147),
 ('Liscate', 4060),
 ('Locate di Triulzi', 9707),
 ('Magenta', 23513),
 ('Magnago', 9090),
 ('Marcallo con Casone', 6048),
 ('Masate', 3363),
 ('Mediglia', 12187),
 ('Melegnano', 17260),
 ('Melzo', 18513),
 ('Mesero', 3915),
 ('Morimondo', 1211),
 ('Motta Visconti', 7665),
 ('Nerviano', 17499),
 ('Nosate', 689),
 ('Novate Milanese', 20201),
 ('Noviglio', 4459),
 ('Opera', 13840),
 ('Ossona', 4150),
 ('Ozzero', 1480),
 ('Paderno Dugnano', 47695),
 ('Pantigliate', 5983),
 ('Parabiago', 26952),
 ('Paullo', 11132),
 ('Pero', 10749),
 ('Peschiera Borromeo', 22774),
 ('Pessano con Bornago', 9121),
 ('Pieve Emanuele', 14887),
 ('Pioltello', 36369),
 ('Pogliano Milanese', 8257),
 ("Pozzo d'Adda", 5523),
 ('Pozzuolo Martesana', 7991),
 ('Pregnana Milanese', 6919),
 ('Rescaldina', 14103),
 ('Rho', 50686),
 ('Robecchetto con Induno', 4896),
 ('Robecco sul Naviglio', 6929),
 ('Rodano', 4457),
 ('Rosate', 5442),
 ('Rozzano', 41363),
 ('San Colombano al Lambro', 7468),
 ('San Donato Milanese', 32702),
 ('San Giorgio su Legnano', 6776),
 ('San Giuliano Milanese', 36871),
 ('Santo Stefano Ticino', 4731),
 ('San Vittore Olona', 8285),
 ('San Zenone al Lambro', 4193),
 ('Sedriano', 11277),
 ('Segrate', 34352),
 ('Senago', 21273),
 ('Sesto San Giovanni', 81130),
 ('Settala', 7465),
 ('Settimo Milanese', 19464),
 ('Solaro', 14203),
 ('Trezzano Rosa', 4804),
 ('Trezzano sul Naviglio', 19350),
 ("Trezzo sull'Adda", 12249),
 ('Tribiano', 3330),
 ('Truccazzano', 5982),
 ('Turbigo', 7485),
 ('Vanzago', 8894),
 ("Vaprio d'Adda", 8045),
 ('Vermezzo', 3897),
 ('Vernate', 3189),
 ('Vignate', 9064),
 ('Vimodrone', 16612),
 ('Vittuone', 9082),
 ('Vizzolo Predabissi', 3979),
 ('Zelo Surrigone', 1377),
 ('Zibido San Giacomo', 6734),
 ('Villa Cortese', 6216),
 ('Vanzaghello', 5307),
 ('Baranzate', 11448)]
In [29]:
bag_fatture = rec_cliente['@fatture']
bag_fatture.digest('#k,#a.totale_lordo,#v.totale_lordo')
Out[29]:
[('Q0Sfz9DkMIGo_P_MDwru4g', Decimal('18154.4'), Decimal('18154.4')),
 ('nF3aBejwP2Cv4_1Ew_kwhA', Decimal('9928'), Decimal('9928')),
 ('G6uTbsK5NgCdcLimkt8aOQ', Decimal('10100'), Decimal('10100')),
 ('IUjAbgUtPbuIf5APiIWFXQ', Decimal('3120'), Decimal('3120')),
 ('oHN9pn12P_CTOgr_LmpkUw', Decimal('200'), Decimal('200')),
 ('eK8LJaHJNqGKC_bYZt95uA', Decimal('8439'), Decimal('8439')),
 ('7vfUUrzfM4iGt4gufHQnTA', Decimal('8752'), Decimal('8752')),
 ('udHwRefIOmuUvn67Oo_Ehw', Decimal('525'), Decimal('525')),
 ('8yfLb6G5OWGCT_RS_71Vzg', Decimal('2433.6'), Decimal('2433.6')),
 ('NJhCJhOPNU2FMDWH6qHerg', Decimal('972'), Decimal('972')),
 ('6BRE41laMtGyTlSusjTSyA', Decimal('3521.6'), Decimal('3521.6')),
 ('lLhbSjvGP3209NnsahGxaw', Decimal('6382'), Decimal('6382')),
 ('TRpBryu8OCi8w1v4dKDDjg', Decimal('210'), Decimal('210')),
 ('BBLPVh16PlW6vsCYQBYgIA', Decimal('3013'), Decimal('3013')),
 ('zRwjUBefPMCKa98ID3lSHQ', Decimal('8947.5'), Decimal('8947.5')),
 ('jKKg7PqTO5aNZgG4gX9Zbg', Decimal('9504.6'), Decimal('9504.6')),
 ('IG86X922Mzq_LnsGRfIlFQ', Decimal('6250'), Decimal('6250')),
 ('i_ljnFG9NSuFxa8GeEKkug', Decimal('2388'), Decimal('2388')),
 ('4enBuKfAMaq6_Vyeb58S5w', Decimal('6544'), Decimal('6544'))]
In [31]:
missing = tbl_cliente.record('asodasda', ignoreMissing=True).output('dict')
print (missing)

{}
In [32]:
mytuple = tbl_cliente.readColumns(where='$n_fatture>0',limit=1,order_by='$n_fatture DESC',
                                      columns='$ragione_sociale,$n_fatture')
print (mytuple)

[ragione_sociale=Bianchi Roberto,n_fatture=31]
In [33]:
mytuple['ragione_sociale']
Out[33]:
'Bianchi Roberto'