Downloaded 19 times
![PostgreSQL speaks Python! CREATE FUNCTION greet(name TEXT) RETURNS TEXT AS $$ import random attr = random.choice([ 'great', 'fabulous', 'bright', 'brave']) return 'Hello {} {}!'.format(attr, name) $$ LANGUAGE plpythonu; SELECT greet('Python fan');](https://image.slidesharecdn.com/europython2014-lightningtalk-postgresql-python-150529143502-lva1-app6892/75/PostgreSQL-and-Python-Lightning-Talk-EuroPython2014-3-2048.jpg)
Uploaded byHenning Jacobs
1,170 views
"PostgreSQL and Python" Lightning Talk @EuroPython2014
PL/Python allows users to write PostgreSQL functions and procedures using Python. It enables accessing PostgreSQL data and running Python code from within SQL queries. For example, a function could query a database table, process the results in Python by accessing modules, and return a value to the SQL query. This opens up possibilities to leverage Python's extensive libraries and expressiveness to expose data and perform complex validation from PostgreSQL.
More Related Content
Viewers also liked
Similar to "PostgreSQL and Python" Lightning Talk @EuroPython2014
More from Henning Jacobs
Recently uploaded
"PostgreSQL and Python" Lightning Talk @EuroPython2014
- 1.
- 2. Well known... import psycopg2 conn= psycopg2.connect('dbname=mydb user=myusr') cur = conn.cursor() cur.execute('SELECT * FROM mytable') row = cur.fetchone()
- 3. PostgreSQL speaks Python! CREATEFUNCTION greet(name TEXT) RETURNS TEXT AS $$ import random attr = random.choice([ 'great', 'fabulous', 'bright', 'brave']) return 'Hello {} {}!'.format(attr, name) $$ LANGUAGE plpythonu; SELECT greet('Python fan');
- 4. PostgreSQL speaks Python ●PL/Python – “just” another procedural language ● SQL script that contains a string of Python ● Allows using any Python module
- 5. Real World Examples... mydb=#SELECT * FROM get_server_connection_stats() LIMIT 5; server_addr | server_port | client_addr | client_port ---------------+-------------+--------------+------------- 10.160.26.128 | 5435 | 10.111.74.16 | 40524 10.160.26.128 | 5435 | 10.160.26.1 | 49681 10.160.26.128 | 5435 | 10.160.26.1 | 15456 10.160.26.128 | 5435 | 10.160.26.2 | 64136 10.160.26.128 | 5435 | 10.160.26.1 | 1938 (5 rows)
- 6. Expose Stats viaSQL with Python CREATE FUNCTION get_server_connection_stats() RETURNS SETOF server_connection AS $$ # … try: with open('/proc/net/tcp') as fp: lines = fp.read().splitlines() except: plpy.error('Unable to read /proc/net/tcp') # … return result $$ LANGUAGE plpythonu;
- 7. Custom JSON TypeValidation CREATE TRIGGER validation AFTER INSERT OR UPDATE ON config_value FOR EACH … CREATE FUNCTION validate(value JSON, type_id INT) RETURNS VOID AS $$ import json # … complex type checking here # … using all of Python's expressiveness :-) raise ValueError('{} not valid'.format(value)) $$ LANGUAGE plpythonu;
- 8.