Python & PostgreSQL - A Wonderful Wedding

Transcript

1. Python & Postgresql A Wonderful Wedding Stéphane Wirtel @PyConFR 2014 - Lyon http://goo.gl/UsPLwh
2. Stéphane Wirtel #fellow @ThePSF #member EPS #member AFPy @PythonFOSDEM @matrixise stephane@wirtel.be http://wirtel.be
3. Agenda Qu’allons-nous apprendre ?
5. Python
6. Python, c’est quoi ? Langage de Programmation Syntaxe simple & lisible Interprété Multi-plateformes Typage Dynamique Fort Multi-paradigmes Garbage Collector
7. Postgresql
8. PostgreSQL - Story Développée à Berkeley Réécriture de Ingres 1985 -> Postgres (vient de Post-Ingres) 1995 -> Postgres95 (0.01) (Ajout d’un interpréter SQL) 1997 -> PostgreSQL 6.0 2013 -> PostgreSQL 9.3 2014 -> PostgreSQL 9.4 (b4)
9. PostgreSQL - TL;DR ORDBMS, ACID SQL:2011 DataTypes Transactional DDL Concurrent Index Extensions Common Table Expression MultiVersion Concurrency Control Cross-Platform
10. PostgreSQL - TL;DR Replication Foreign Data Wrappers Procedural Languages Triggers Full text Search Views (Materialized) Table Inheritance Listen/Notify
11. PostgreSQL, FDW ? Utiliser une source externe à PostgreSQL Twitter, RSS, CSV, XML FileSystem Processes
12. PostgreSQL, PL ? Étend la base de données Fonctions stockées Safe (sandbox, SQL, PL/pgSQL) / Unsafe (C) PL/Python, PL/V8, PL/PERL
13. DB-API 2.0 Commençons par le commencement… http://python.org/peps/pep-0249
14. DB-API 2.0 aka PEP-249 API pour les connecteurs de Base de Données Facile à utiliser, et à comprendre Utilise deux concepts: • Connection • Curseur http://legacy.python.org/dev/peps/pep-0249/
15. Connection Emballe une connection vers la base de données Gère les transactions et leur état (Commit/ Rollback) N’exécute pas de requêtes SQL.
16. Connection - API • connect(parameters=None) • close() • commit() • rollback() • cursor([name=None])
17. Connection - Exemple import driver conn = driver.connect(database='database', host='localhost', port=5432) try: # cree le curseur # utilise le curseur except Exception: conn.rollback() else: conn.commit() conn.close()
18. Curseur Créé via une instance d’une connection Utilisé pour la manipulation et interrogation de la Base de Données cursor = conn.cursor() cursor.execute(""" SELECT column1, column2 FROM tableA """) for column1, column2 in cursor.fetchall(): print(column1, column2)
19. Curseur - API • callproc(procname[, parameters]) • execute(query[, parameters]) • fetchone(), fetchmany([size=cursor.arraysize]), fetchall() • close()
20. Curseur - execute execute(query[, parameters]) Performance et Sécurité JAMAIS utiliser l’interpolation (%) et la concaténation (+) => SQL Injection
21. Curseur - execute (2) Accepte un formatage de la requête. qmark Question mark WHERE field = ? numeric Numeric positional WHERE field = :1 named Named WHERE field = :code format ANSI C print format WHERE field = %s pyformat Python format WHERE field = %(name)s
22. Exemple plus complet ;-) import driver conn = driver.connect(database='database', host='localhost', port=5432) cursor = conn.cursor() try: cursor.execute("SELECT column1, column2 FROM tableA") for column1, column2 in cursor.fetchall(): print(column1, column2) except Exception: conn.rollback() else: conn.commit() finally: cursor.close() conn.close()
23. psycopg2 La Base… pip install psycopg2
24. Introduction PostgreSQL Adaptor, basé sur libpq DB-API 2.0 Compliant Multi-thread Pool de Connections Full Asynchronous, Coroutines Supporte TOUS les types de données de PostgreSQL (Json, Hstore, …) Python 2.5+, 3.1+, PostgreSQL 7.4+
25. Curseurs cursor = conn.cursor() cursor = conn.cursor(name=“pycon_cursor”) • Curseur coté PostgreSQL • Interrogation, manipulation des données de la base. • Nommé => Utilisé pour les gros ensembles de données
26. Requêtes SQL cursor.execute("SELECT * FROM table") cursor.execute("INSERT INTO table (field1, field2) VALUES (%s, %s)", (field1, field2)) cursor.execute("DELETE FROM table") cursor.execute("UPDATE table SET field1=%s", ('value',)) cursor.execute("CREATE DATABASE database") cursor.execute("DROP DATABASE database")
27. Exemple import psycopg2 conn = psycopg2.connect(host='localhost', port=5432, user='username', password='password', database='database') cursor = conn.cursor() try: cursor.execute("SELECT column1, column2 FROM tableA") for column1, column2 in cursor.fetchall(): print(column1, column2) except Exception: conn.rollback() else: conn.commit() finally: cursor.close() conn.close()
28. Exemple - Context Manager import psycopg2 DSN = dict(host='localhost', port=5432, user='username', password='password', database='database') with psycopg2.connect(**DSN) as conn: with conn.cursor() as cursor: cursor.execute("SELECT column1, column2 FROM tableA") for column1, column2 in cursor.fetchall(): print(column1, column2) conn.commit()
29. Object Relational Mapping
30. Introduction “Technique de Programmation créant l’illusion d’une base de données orientée objet à partir d’une base de données relationnelle en définissant des correspondances entre cette base de données et les objets du langage utilisé.” Wikipedia
31. En bref • Mapper une classe Business sur une table ! • Méthodes pour ajouter, modifier, supprimer et rechercher
32. Peewee-ORM Object Relational Mapping pip install peewee
33. Peewee ORM Simple, facile à comprendre et extensible Python 2.6+ et 3.2+ Extensions 3200 lignes de code (MySQL, Sqlite3 et PostgreSQL) Très bonne documentation
34. Connection import peewee database = peewee.PostgresqlDatabase('demo')
35. Modèles class BaseModel(peewee.Model): id = peewee.PrimaryKeyField() class Meta: database = database class Address(BaseModel): street = peewee.CharField() zipcode = peewee.CharField() country = peewee.CharField() class Contact(BaseModel): firstname = peewee.CharField(null=False) lastname = peewee.CharField(null=False) email = peewee.CharField(unique=True, null=False, index=True) address = peewee.ForeignKeyField(Address, null=False, related_name=‘contacts')
36. Création des tables database.create_tables([Address, Contact])
37. Peewee - T ransactions tx = database.transaction() tx.commit() tx.rollback() with database.transaction(): pass
38. Create with database.transaction(): address = Address.create( street='Rue de Lyon', zipcode='90001', country='France') contact = Contact.create( firstname='Stephane', lastname='Wirtel', email='stephane@wirtel.be', address=address ) print(contact.id)
39. Read contact = Contact.get(Contact.email == 'stephane@wirtel.be') print(contact.firstname) contacts = Contact.select() for contact in contacts: print(contact.firstname) for contact in contacts.where(Contact.email == 'stephane@wirtel.be'): print(contact.firstname)
40. Update with database.transaction(): contact = Contact.get(Contact.email == 'stephane@wirtel.be') contact.firstname = 'Speaker' contact.save()
41. Delete # Suppression pour une collection contacts = Contact.delete().where(Contact.email == 'stephane@wirtel.be') contacts.execute() # Suppression d'un seul enregistrement contact = Contact.get(Contact.email == 'stephane@wirtel.be') contact.delete_instance()
42. SQLAlchemy Object Relational Mapping pip install sqlalchemy
43. SQLAlchemy • Gestionnaire de connections • Abstraction des connecteurs SQL • Langage d’Expression SQL • ORM
44. Connection from sqlalchemy import create_engine engine = create_engine('postgresql:///demo', echo=True)
45. Modèles from sqlalchemy import Column, Integer, String, ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship Base = declarative_base() class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) street = Column(String) zipcode = Column(String) country = Column(String, nullable=False) class Contact(Base): __tablename__ = 'contact' id = Column(Integer, primary_key=True) firstname = Column(String, nullable=False) lastname = Column(String, nullable=False) email = Column(String, nullable=False) address_id = Column(Integer, ForeignKey(Address.id), nullable=False) address = relationship('Address')
46. Création des tables Base.metadata.create_all(engine)
47. Sessions Wraps les transactions session = Session(engine) session.commit()
48. Create session = Session(engine) address = Address(street='SQLAlchemy Street', zipcode='5432', country='Belgique') contact = Contact( firstname='Stephane', lastname='Wirtel', email='stephane@wirtel.be', address=address ) session.add(contact) session.commit()
49. Read contact = session.query(Contact).filter_by(email='stephane@wirtel.be').first() print(contact.firstname) contacts = session.query(Contact).all() for contact in contacts: print(contact.firstname) contacts = session.query(Contact).filter_by(email='stephane@wirtel.be').all() for contact in contacts: print(contact.firstname)
50. Update contact = session.query(Contact) \ .filter_by(email='stephane@wirtel.be').first() contact.email = 'sw@mgx.io' session.add(contact) session.commit()
51. Delete contact = session.query(Contact) \ .filter_by(email='stephane@wirtel.be').first() session.delete(contact) session.commit()
52. Alembic La Migration Facile pip install alembic
53. Alembic Basé sur SQLAlchemy Utilise un environnement de Migration Fichier de configuration INI Scripts de migration http://alembic.readthedocs.org/
54. Alembic cd votreproject alembic init migrations > tree migrations/ !"" README !"" alembic.ini !"" env.py !"" script.py.mako #"" versions
55. Alembic revision = '22630db6f519' down_revision = None from alembic import op import sqlalchemy as sa def upgrade(): op.create_table('user', sa.Column('id', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('name', sa.String(length=255), nullable=False), sa.Column('email', sa.String(length=255), nullable=True), sa.Column('password', sa.String(length=255), nullable=True), sa.Column('active', sa.Boolean(), nullable=True), sa.Column('confirmed_at', sa.DateTime(), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('email') ) def downgrade(): op.drop_table('user') alembic revision -m “bootstrap" alembic upgrade head
56. Alembic revision = '7e4b6a43e6c' down_revision = '22630db6f519' from alembic import op import sqlalchemy as sa def upgrade(): op.add_column('user', sa.Column('slug', sa.Unicode(length=255), nullable=False, server_default='')) def downgrade(): op.drop_column('user', 'slug') alembic revision -m “add_slug” alembic upgrade head
57. Alembic > tree migrations/ !"" README !"" alembic.ini !"" env.py !"" script.py.mako #"" versions !"" 22630db6f519_bootstrap.py #"" 7e4b6a43e6c_add_slug.py
58. Alembic from alembic import op from sqlalchemy.orm import Session from youproject import models def upgrade(): engine = op.get_bind() session = Session(bind=engine) for contact in session.query(models.Contact).all(): session.add( models.WifiUser( login=contact.name.lower(), password=random_string() ) ) session.commit() alembic upgrade head
59. Alembic alembic revision alembic upgrade head alembic upgrade +2 alembic downgrade -1 alembic downgrade base alembic current alembic history
60. Zone Dangereuse !!!
61. Multicorn Où comment se connecter au monde ! http://multicorn.org
62. Rappel: Foreign Data Wrappers Utiliser une source externe à PostgreSQL Twitter, RSS, CSV, XML FileSystem Processes PostgreSQL, Oracle, MongoDB
63. declarations for dynamic loading */ _MODULE_MAGIC; G_FUNCTION_INFO_V1(mongo_fdw_handler); PG_FUNCTION_INFO_V1(mongo_fdw_validator); /* * mongo_fdw_handler creates and returns a struct with pointers to foreign table * callback functions. */ Datum mongo_fdw_handler(PG_FUNCTION_ARGS) { FdwRoutine *fdwRoutine = makeNode(FdwRoutine); fdwRoutine->GetForeignRelSize = MongoGetForeignRelSize; fdwRoutine->GetForeignPaths = MongoGetForeignPaths; fdwRoutine->GetForeignPlan = MongoGetForeignPlan; fdwRoutine->ExplainForeignScan = MongoExplainForeignScan; fdwRoutine->BeginForeignScan = MongoBeginForeignScan; fdwRoutine->IterateForeignScan = MongoIterateForeignScan; fdwRoutine->ReScanForeignScan = MongoReScanForeignScan; fdwRoutine->EndForeignScan = MongoEndForeignScan; fdwRoutine->AnalyzeForeignTable = MongoAnalyzeForeignTable; PG_RETURN_POINTER(fdwRoutine);
64. Multicorn • Extension PostgreSQL • Permet d’écrire un Foreign Data Wrapper • Wrapper Python de l’API C de PostgreSQL • Tous les outils utilisant SQL peuvent utiliser un FDW • Support FULL SQL ;-)
65. FDW de base • RSS, CSV, XML • LDAP • Gmail, IMAP • Google Search • SQLAlchemy (mysql, sqlite, oracle, …)
66. Simple exemple CREATE EXTENSION multicorn;
67. Simple exemple (2) CREATE SERVER wirtel_be_srv FOREIGN DATA WRAPPER multicorn OPTIONS ( wrapper 'multicorn.rssfdw.RssFdw' ); CREATE FOREIGN TABLE wirtel_be_rss ( "pubDate" TIMESTAMP, description CHARACTER VARYING, title CHARACTER VARYING, link CHARACTER VARYING ) SERVER wirtel_be_srv OPTIONS( url 'http://wirtel.be/feeds/python.rss.xml' );
68. Simple exemple (3) SELECT "pubDate", title, link FROM wirtel_be_rss LIMIT 1; -[ RECORD 1 ]------------------------------------------------------------------------ pubDate | 2014-10-19 00:00:00 title | Python @ FOSDEM 2015 - Call For Proposals link | http://wirtel.be/posts/en/2014/10/19/python-fosdem-2015-call-for-proposals/
69. avec OpenERP from multicorn import ForeignDataWrapper import erppeek class OpenERPForeignDataWrapper(ForeignDataWrapper): def __init__(self, options, columns): super(OpenERPForeignDataWrapper, self).__init__(options, columns) url = 'http://{hostname}:{password}'.format(**options) self.client = erppeek.Client(url, options['database'], options['username'], options['password']) self.object_name = options['object'] def execute(self, quals, columns): proxy = self.client.model(self.object_name) item = {} for record in proxy.browse([]): for column in columns: item[column] = record[column] yield item
70. avec OpenERP (2) CREATE EXTENSION multicorn; CREATE SERVER multicorn_openerp FOREIGN DATA WRAPPER multicorn OPTIONS (wrapper 'multicorn.openerpfdw.OpenERPForeignDataWrapper'); CREATE FOREIGN TABLE oe_users ( login character varying, name character varying) SERVER multicorn_openerp OPTIONS ( hostname 'localhost', port '8069', database 'openerp', username 'admin', password 'admin', object 'res.users' ); SELECT id, login, name, active FROM oe_users WHERE login = 'admin';
71. PL/Python Etendre PostgreSQL
72. PL/Python Python 2 ou Python 3 Utilise toutes les librairies Python (PyPI) Apprentissage plus rapide que PL/pgSQL
73. PL/Python apt-get install postgresql-plpython-9.3 CREATE EXTENSION IF NOT EXISTS plpythonu;
74. PL/Python CREATE OR REPLACE FUNCTION str_title(s VARCHAR) RETURNS VARCHAR AS $$ return s.title() $$ LANGUAGE plpythonu; demo=# select str_title('hello world'); -[ RECORD 1 ]---------- str_title | Hello World
75. PL/Python - DataT ypes PostgreSQL Python integer, bigint int, long boolean bool text types str SQL Array list Custom Types dict
76. PL/Python - Debug Fonctions de base pour afficher les messages • plpy.notice(“<msg>”) • plpy.debug(“<msg>”) • plpy.error(“<msg>”) • plpy.fatal(“<msg>”) Oublier le ”print”
77. PL/Python CREATE OR REPLACE FUNCTION get_pid_cpu_mem(pid INT) RETURNS TABLE(pid INT, cpu_perc FLOAT, mem_perc FLOAT) AS $$ import psutil process = psutil.Process(pid) return [ { 'pid': pid, 'cpu_perc': process.get_cpu_percent(interval=0), 'mem_perc': process.get_memory_percent() } ] $$ LANGUAGE plpythonu;
78. PL/Python -[ RECORD 1 ]----+--------------------------------- datid | 16416 datname | demo pid | 14680 usesysid | 16384 usename | stephane application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2014-10-25 04:22:08.235532+02 xact_start | 2014-10-25 04:25:28.712812+02 query_start | 2014-10-25 04:25:28.712812+02 state_change | 2014-10-25 04:25:28.712815+02 waiting | f state | active query | select * from pg_stat_activity ; SELECT * FROM pg_stat_activity;
79. PL/Python pid | cpu_perc | mem_perc -------+----------+-------------- 14680 | 0 | 1.4454081372 (1 row) SELECT * FROM get_pid_cpu_mem(14680);
80. PL/Python pid | cpu_perc | mem_perc | application_name -------+----------+---------------+------------------ 14680 | 0 | 1.50435626678 | psql (1 row) WITH stats AS ( SELECT psa.*, get_pid_cpu_mem(psa.pid) as attrs from pg_stat_activity psa ) SELECT (stats.attrs).pid, (stats.attrs).cpu_perc, (stats.attrs).mem_perc, stats.application_name FROM stats;
81. PL/Python - Attention Unsafe -> pas de sandbox Difficile à maintenir et à debugger Comment rendre fou votre DBA ;-) Pas de virtualenv et demande les privilèges superuser.
82. PL/Python - Pistes ;-) Traitement sur grosses masses de données Ajouter des contraintes fortes et logique dans le système. Ajouter de nouvelles fonctionnalités dans PostgreSQL “str_title” Triggers ? Utilisation des libs de PyPI (requests, redis, zmq, smtp…) Accès complet à la base de données !!!
84. Questions ? @matrixise https://speakerdeck.com/matrixise