il Pitone e l'Elefante

• Potente e veloce

• Ricco di librerie

Marco Nenciarini

• Ben documentato

• Facile da imparare

CTO 2ndQuadrant Italia

PostgreSQL Consultant

PostgreSQL Community Member

Debian/Ubuntu PostgreSQL Packager

marco.nenciarini@2ndquadrant.it

@mnencia

• Potente e veloce

• Ricco di funzionalità

• Ben documentato

• Facile da usare

il pitone e l'elefante

Integrazione con Django

Perché PL/Python?

Accesso Client

E se non fosse abbastanza?

Con Multicorn è possibile rendere Python una sorgente di dati SQL MED!

• Avviciniamo Python ai dati
• Parliamo lo stesso linguaggio dell'applicazione client
• Batterie incluse

Il core di Django è indipendente dal database:

• django.contrib.postgres

Permette di sfruttare a pieno le particolarità di postgres

• Python DB API 2.0 (PEP-0249)
• Molte implementazioni
• Psycopg la più completa
• libpq
• LGPLv3+
• http://initd.org/psycopg/
• Versione 2.7.1 (Aprile 2017)

http://multicorn.org/

https://docs.djangoproject.com/en/1.10/ref/contrib/postgres/

Iniziamo

Multicorn: unicorni e elefanti

Tipi di dato specifici

Connessione a PostgreSQL

CREATE EXTENSION plpython3u;

import psycopg2

conn = psycopg2.connect('dbname=pycon')

Tipi aggiuntivi

• ArrayField
• HStoreField
• JSONField
• Range Fields

Ogni tipo di dati ha il suo widget di input

Installa il linguaggio dentro a Postgres

• La 'u' finale vuol dire 'untrusted'
• Superuser only
• Chi accede a un linguaggio untrusted può fare qulsiesi cosa possa fare il proprietario del server Postgres

Giulio Calacoci

• Hardcoded dbdriver?
• Generico solo se serve!
• No feature avanzate
• KISS

https://prezi.com/af884v1yx8lc/multicorn/

Lookup specifici

Connessione a PostgreSQL

Esempio Base

# libpq connection string

conn = psycopg2.connect(

'dbname=pycon host=localhost port=5432')

# keyword arguments

conn = psycopg2.connect(

database='pycon',

host='localhost',

port=5432)

CREATE FUNCTION pymax (a integer, b integer)

RETURNS integer

AS $$

if (a is None) or (b is None):

return None

if a > b:

return a

return b

$$ LANGUAGE plpython3u;

• Trigram similarity
• ricerca parole simili
• estensione: pg_trgm
• indici: GIN/GiST
• Django 1.10

• Unaccent
• Effettua ricerche ignorando gli accenti
• estensione: unaccent

https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING

L'oggetto plpy

Full Text Search

Cursori

• A partire da Django 1.10
• Richiede PostgreSQL 9.6
• Permette di effettuare ricerche full tex precise e veloci
• Indici GIN/GiST

# Non sono veri cursori nel database.

# Usati solo come astrazione nell API

cursor = conn.cursor()

# Cursore nel database

cursor = conn.cursor(name='nome_cursore')

# Cursore con caratteristiche personalizzate

cursor = conn.cursor(

cursor_factory=NamedTupleCursor)

L'oggetto plpy è sempre definito e si può usare per accedere ai dati

• plpy.execute(sql)
• pstm = plpy.prepare(sql, [data_type, ...])
• plpy.execute(pstm, [variable1, ...])

È usato anche per inviare messaggi al log:

ad esempio con plpy.error(msg) o plpy.warning(msg)

http://initd.org/psycopg/docs/extras.html#cursor-subclasses

Funzioni di aggregazione

Eseguire l'SQL

Esempio accesso ai dati

• Postgres implementa moltissime funzioni aggregate
• Djiango espone direttamente queste funzioni

# Query con che ritornano risultati

cursor.execute("""

SELECT id, name, tagline

FROM pycon_attendees

""")

# Query di modifica

cursor.execute(

"INSERT INTO test (id, x, y) "

"VALUES (101, 1, 11)")

# DDL

cursor.execute(

"ALTER TABLE test ADD COLUMN md5 text")

CREATE FUNCTION set_passwords(

user text, pass text)

RETURNS integer

AS $$

rv = plpy.execute(

"UPDATE user SET password = $1 "

"WHERE username = $2", [pass, user])

return rv.nrows

$$ LANGUAGE plpython3u;

TestModel.objects.aggregate(

result=StringAgg('field1', delimiter=';'))

{'result': 'foo;bar;test'}

TestModel.objects.aggregate(

avgx=RegrAvgX(y='field3', x='field2'),

avgy=RegrAvgY(y='field3', x='field2'))

{'avgx': 2, 'avgy': 13}

Esempio accesso ai dati

C'è di più

Ottenere i risultati

• Il risultato di execute é una lista di dizionari

• Funzioni di controllo transazioni
• Controllo estensioni
• Validatori

# I cursori possono essere usati nei for

cursor.execute(

"SELECT id, name FROM pycon_attendees")

for id, name in cursor:

print("%s: %s" % (name, id))

# Oppure si può usare uno dei metodi di fetch

cursor.execute(

"SELECT id, x, y FROM test LIMIT 2")

pprint.pprint(cursor.fetchall(), width=20)

[(101, 1, 11),

(202, 2, 22)]

CREATE FUNCTION count_passwords()

RETURNS integer

AS $$

count = 0

for row in plpy.execute(

"SELECT password FROM users"):

if row['password'] is None:

count += 1

return count

$$ LANGUAGE plpython3u;

https://docs.djangoproject.com/en/1.10/ref/contrib/postgres/

Ottenere i risultati

C'è di più

• Cursori
• Set returning function
• Supporto trigger
• Dati condivisi
• Funzioni di utilità

# Cursore costruito con

# cursor_factory=DictCursor

cursor.execute(

"SELECT id, x FROM test LIMIT 2")

for record in cursor:

print("%s: %s" % (

record['id'], record['x']))

101: 1

202: 2

https://www.postgresql.org/docs/current/static/plpython.html

Parametri

# Metodo sbagliato!

cursor.execute(

"SELECT id FROM users "

"WHERE username = '%s' and password = '%s'" %

(username, password)) # SQL Injection

Parametri

# Metodo corretto

cursor.execute(

"SELECT id FROM users "

"WHERE username = %s AND password = %s",

(username, password))

# Anche parametri con nome

cursor.execute(

"SELECT id FROM users "

"WHERE username = %(name)s "

"AND password = %(pass)s",

dict(name=username, pass=password))

Usare sempre '%s', anche per i numeri

Tipi di dato

• Conversione automatica sia in ingresso che in uscita
• Booleani
• Tipi numerici
• Date e orari
• Null
• UUID
• ARRAY
• JSON
• Binari
• Possibilità di conversioni custom

Transazioni

Default: autocommit disabilitato!

• conn.commit()
• conn.rollback()

Se la connessione è chiusa senza `commit` tutte le modifiche sono annullate.

Per impostare autocommit

• conn.set_isolation_level(

ISOLATION_LEVEL_AUTOCOMMIT)

Caratteristiche avanzate

• supporto COPY
• supporto large object
• supporto 2PC
• cancellazione di query
• notify
• logical decoding
• comunicazione asincrona
• multithreading
• SQL string composition
• connection pooling

http://initd.org/psycopg/docs/