Programming with Python and PostgreSQL

Programming with Python
and PostgreSQL
Peter Eisentraut
peter@eisentraut.org

F-Secure Corporation

PostgreSQL Conference East 2011

CC-BY

Partitioning

• Part I: Client programming (60 min)
• Part II: PL/Python (30 min)

Why Python?
Pros:
• widely used
• easy
• strong typing
• scripting, interactive use
• good PostgreSQL support
• client and server (PL) interfaces
• open source, community-based

Why Python?
Pros:
• widely used
• easy
• strong typing
• scripting, interactive use
• good PostgreSQL support
• client and server (PL) interfaces
• open source, community-based
Pros:
• no static syntax checks, must rely on test coverage
• Python community has varying interest in RDBMS

Example
import psycopg2

dbconn = psycopg2.connect('dbname=dellstore2')
cursor = dbconn.cursor()
cursor.execute("""
SELECT firstname, lastname
FROM customers
ORDER BY 1, 2
LIMIT 10
""")
for row in cursor.fetchall():
print "Name: %s %s" % (row[0], row[1])
cursor.close()
db.close()

Drivers

Name License Platforms Py Versions
Psycopg LGPL Unix, Win 2.4–3.2
PyGreSQL BSD Unix, Win 2.3–2.6
ocpgdb BSD Unix 2.3–2.6
py-postgresql BSD pure Python 3.0+
bpgsql (alpha) LGPL pure Python 2.3–2.6
pg8000 BSD pure Python 2.5–3.0+

Drivers

Name License Platforms Py Versions
Psycopg LGPL Unix, Win 2.4–3.2
PyGreSQL BSD Unix, Win 2.3–2.6
ocpgdb BSD Unix 2.3–2.6
py-postgresql BSD pure Python 3.0+
bpgsql (alpha) LGPL pure Python 2.3–2.6
pg8000 BSD pure Python 2.5–3.0+
More details
• http://wiki.postgresql.org/wiki/Python
• http://wiki.python.org/moin/PostgreSQL

DB-API 2.0

• the standard Python database API
• all mentioned drivers support it
• deﬁned in PEP 249
• discussions: db-sig@python.org
• very elementary (from a PostgreSQL perspective)
• outdated relative to Python language development
• lots of extensions and incompatibilities possible

Higher-Level Interfaces

• Zope
• SQLAlchemy
• Django

Psycopg Facts

• Main authors: Federico Di Gregorio, Daniele Varrazzo
• License: LGPLv3+
• Web site: http://initd.org/psycopg/
• Documentation: http://initd.org/psycopg/docs/
• Git, Gitweb
• Mailing list: psycopg@postgresql.org
• Twitter: @psycopg
• Latest version: 2.4 (February 27, 2011)

Using the Driver

import psycopg2

dbconn = psycopg2.connect(...)
...

Driver Independence?

import psycopg2

dbconn = psycopg2.connect(...) # hardcodes driver name


import psycopg2 as dbdriver

dbconn = dbdriver.connect(...)


dbtype = 'psycopg2' # e.g. from config file
dbdriver = __import__(dbtype,
globals(), locals(),
[], -1)

dbconn = dbdriver.connect(...)

Connecting
# libpq-like connection string
dbconn = psycopg2.connect('dbname=dellstore2
host=localhost port=5432')

# same
dbconn = psycopg2.connect(dsn='dbname=dellstore2
host=localhost port=5432')

# keyword arguments
# (not all possible libpq options supported)
dbconn = psycopg2.connect(database='dellstore2',
host='localhost',
port='5432')

DB-API 2.0 says: arguments database dependent

“Cursors”


• not a real database cursor, only an API abstraction
• think “statement handle”

Server-Side Cursors

cursor = dbconn.cursor(name='mycursor')

• a real database cursor
• use for large result sets

Executing
# queries
cursor.execute("""
FROM customers
ORDER BY 1, 2
LIMIT 10
""")

# updates
cursor.execute("UPDATE customers SET password = NULL")
print "%d rows updated" % cursor.rowcount

# or anything else
cursor.execute("ANALYZE customers")

Fetching Query Results

cursor.execute("SELECT firstname, lastname FROM ...")
cursor.fetchall()

[('AABBKO', 'DUTOFRPLOK'),
('AABTSI', 'ZFCKMPRVVJ'),
('AACOHS', 'EECCQPVTIW'),
('AACVVO', 'CLSXSGZYKS'),
('AADVMN', 'MEMQEWYFYE'),
('AADXQD', 'GLEKVVLZFV'),
('AAEBUG', 'YUOIINRJGE')]



Note: ﬁeld access only by number


row = cursor.fetchone()
if row is not None:


for row in cursor:

Fetching Query Results in Batches

cursor.arraysize = 500 # default: 1
while True:
batch = cursor.fetchmany()
break if not batch
for row in batch:

Fetching Query Results in Batches

cursor.itersize = 2000 # default
for row in cursor:

Getting Query Metadata

cursor.execute("SELECT DISTINCT state, zip FROM
customers")
print cursor.description[0].name
print cursor.description[0].type_code
print cursor.description[1].name
print cursor.description[1].type_code

state
1043 # == psycopg2.STRING
zip
23 # == psycopg2.NUMBER

Passing Parameters

cursor.execute("""
UPDATE customers
SET password = %s
WHERE customerid = %s
""", ["sekret", 37])

Passing Parameters

Not to be confused with (totally evil):
cursor.execute("""
UPDATE customers
SET password = '%s'
WHERE customerid = %d
""" % ["sekret", 37])

Passing Parameters

cursor.execute("INSERT INTO foo VALUES (%s)",
"bar") # WRONG

("bar")) # WRONG

("bar",)) # correct

["bar"]) # correct

(from Psycopg documentation)

Passing Parameters

cursor.execute("""
UPDATE customers
SET password = %(pw)s
WHERE customerid = %(id)s
""", {'id': 37, 'pw': "sekret"})

Passing Many Parameter Sets

cursor.executemany("""
UPDATE customers
SET password = %s
WHERE customerid = %s
""", [["ahTh4oip", 100],
["Rexahho7", 101],
["Ee1aetui", 102]])

Calling Procedures

cursor.callproc('pg_start_backup', 'label')

Data Types

from decimal import Decimal
from psycopg2 import Date

cursor.execute("""
INSERT INTO orders (orderdate, customerid,
netamount, tax, totalamount)
VALUES (%s, %s, %s, %s, %s)""",
[Date(2011, 03, 23), 12345,
Decimal("899.95"), 8.875, Decimal("979.82")])

Mogrify
from decimal import Decimal
from psycopg2 import Date

cursor.mogrify("""
INSERT INTO orders (orderdate, customerid,
netamount, tax, totalamount)
VALUES (%s, %s, %s, %s, %s)""",
[Date(2011, 03, 23), 12345,
Decimal("899.95"), 8.875, Decimal("979.82")])

Result:
"nINSERT INTO orders (orderdate, customerid,n
netamount, tax, totalamount)nVALUES
('2011-03-23'::date, 12345, 899.95, 8.875, 979.82)"

Data Types

cursor.execute("""
SELECT * FROM orders WHERE customerid = 12345
""")

Result:
(12002, datetime.date(2011, 3, 23), 12345,
Decimal('899.95'), Decimal('8.88'),
Decimal('979.82'))

Nulls

Input:
cursor.mogrify("SELECT %s", [None])

'SELECT NULL'

Output:
cursor.execute("SELECT NULL")
cursor.fetchone()

(None,)

Booleans

cursor.mogrify("SELECT %s, %s", [True, False])

'SELECT true, false'

Binary Data
Standard way:
from psycopg2 import Binary
cursor.mogrify("SELECT %s", [Binary("foo")])

"SELECT E'x666f6f'::bytea"

Binary Data
Standard way:
from psycopg2 import Binary
cursor.mogrify("SELECT %s", [Binary("foo")])


Other ways:
cursor.mogrify("SELECT %s", [buffer("foo")])


cursor.mogrify("SELECT %s",
[bytearray.fromhex(u"deadbeef")])

"SELECT E'xdeadbeef'::bytea"

There are more. Check the documentation. Check the versions.

Date/Time

Standard ways:
from psycopg2 import Date, Time, Timestamp

cursor.mogrify("SELECT %s, %s, %s",
[Date(2011, 3, 23),
Time(9, 0, 0),
Timestamp(2011, 3, 23, 9, 0, 0)])

"SELECT '2011-03-23'::date, '09:00:00'::time,
'2011-03-23T09:00:00'::timestamp"

Date/Time
Other ways:
import datetime

cursor.mogrify("SELECT %s, %s, %s, %s",
[datetime.date(2011, 3, 23),
datetime.time(9, 0, 0),
datetime.datetime(2011, 3, 23, 9, 0),
datetime.timedelta(minutes=90)])

"SELECT '2011-03-23'::date, '09:00:00'::time,
'2011-03-23T09:00:00'::timestamp, '0 days
5400.000000 seconds'::interval"

mx.DateTime also supported

Arrays

foo = [1, 2, 3]
bar = [datetime.time(9, 0), datetime.time(10, 30)]

cursor.mogrify("SELECT %s, %s",
[foo, bar])

"SELECT ARRAY[1, 2, 3], ARRAY['09:00:00'::time,
'10:30:00'::time]"

Tuples

foo = (1, 2, 3)

cursor.mogrify("SELECT * FROM customers WHERE
customerid IN %s",
[foo])

'SELECT * FROM customers WHERE customerid IN (1, 2, 3)'

Hstore

import psycopg2.extras

psycopg2.extras.register_hstore(cursor)

x = {'a': 'foo', 'b': 'bar'}

cursor.mogrify("SELECT %s",
[x])

"SELECT hstore(ARRAY[E'a', E'b'], ARRAY[E'foo',
E'bar'])"

Unicode Support

Cause all result strings to be returned as Unicode strings:
psycopg2.extensions.register_type(psycopg2.extensions.
UNICODE)
psycopg2.extensions.register_type(psycopg2.extensions.
UNICODEARRAY)

Transaction Control

Transaction blocks are used by default. Must use
dbconn.commit()

or
dbconn.rollback()

Transaction Control: Autocommit

import psycopg2.extensions

dbconn.set_isolation_level(psycopg2.extensions.
ISOLATION_LEVEL_AUTOCOMMIT)

cursor.execute("VACUUM")

Transaction Control: Isolation Mode


dbconn.set_isolation_level(psycopg2.extensions.
ISOLATION_LEVEL_SERIALIZABLE) # or other level

cursor.execute(...)
...
dbconn.commit()

Error Messages

try:
cursor.execute("boom")
except Exception, e:
print e.pgerror

Error Codes

import psycopg2.errorcodes

while True:
try:
cursor.execute("UPDATE something ...")
cursor.execute("UPDATE otherthing ...")
break
except Exception, e:
if e.pgcode ==
psycopg2.errorcodes.SERIALIZATION_FAILURE:
continue
else:
raise

Connection and Cursor Factories

Want: accessing result columns by name
Recall:
dbconn = psycopg2.connect(dsn='...')
cursor.execute("""
FROM customers
ORDER BY 1, 2
LIMIT 10
""")
print "Name: %s %s" % (row[0], row[1]) # stupid :(

Solution 1: Using DictConnection:

dbconn = psycopg2.connect(dsn='...',
connection_factory=psycopg2.extras.DictConnection)
cursor.execute("""
FROM customers
ORDER BY 1, 2
LIMIT 10
""")
print "Name: %s %s" % (row['firstname'], # or row[0]
row['lastname']) # or row[1]

Solution 2: Using RealDictConnection:

connection_factory=psycopg2.extras.RealDictConnection)
cursor.execute("""
FROM customers
ORDER BY 1, 2
LIMIT 10
""")
print "Name: %s %s" % (row['firstname'],
row['lastname'])

Solution 3: Using NamedTupleConnection:

connection_factory=psycopg2.extras.NamedTupleConnection)
cursor.execute("""
FROM customers
ORDER BY 1, 2
LIMIT 10
""")
print "Name: %s %s" % (row.firstname, # or row[0]
row.lastname) # or row[1]

Alternative: Using
DictCursor/RealDictCursor/NamedTupleCursor:


dbconn = psycopg2.connect(dsn='...')
cursor = dbconn.cursor(cursor_factory=psycopg2.extras.
DictCursor/RealDictCursor/NameTupleCursor)
cursor.execute("""
FROM customers
ORDER BY 1, 2
LIMIT 10
""")
print "Name: %s %s" % (row['firstname'],
row['lastname'])
# (resp. row.firstname, row.lastname)

Supporting New Data Types

Only a ﬁnite list of types is supported by default: Date, Binary,
etc.
• map new PostgreSQL data types into Python
• map new Python data types into PostgreSQL

Mapping New PostgreSQL Types Into
Python
import psycopg2

def cast_oidvector(value, _cursor):
"""Convert oidvector to Python array"""
if value is None:
return None
return map(int, value.split(' '))

OIDVECTOR = psycopg2.extensions.new_type((30,),
'OIDVECTOR', cast_oidvector)
psycopg2.extensions.register_type(OIDVECTOR)

Mapping New Python Types into
PostgreSQL
from psycopg2.extensions import adapt,
register_adapter, AsIs

class Point(object):
def __init__(self, x, y):
self.x = x
self.y = y

def adapt_point(point):
return AsIs("'(%s, %s)'" % (adapt(point.x),
adapt(point.y)))

register_adapter(Point, adapt_point)

cur.execute("INSERT INTO atable (apoint) VALUES (%s)",
(Point(1.23, 4.56),))

(from Psycopg documentation)

Connection Pooling With Psycopg
from psycopg2.pool import SimpleConnectionPool

pool = SimpleConnectionPool(1, 20, dsn='...')
dbconn = pool.getconn()
...
pool.putconn(dbconn)
pool.closeall()

Connection Pooling With Psycopg
for non-threaded applications:
from psycopg2.pool import SimpleConnectionPool

pool = SimpleConnectionPool(1, 20, dsn='...')
...
pool.closeall()

for non-threaded applications:
from psycopg2.pool import ThreadedConnectionPool

pool = ThreadedConnectionPool(1, 20, dsn='...')
...
pool.closeall()

Connection Pooling With DBUtils

import psycopg2
from DBUtils.PersistentDB import PersistentDB

dbconn = PersistentDB(psycopg2, dsn='...')
...

see http://pypi.python.org/pypi/DBUtils/

The Other Stuff

• thread safety: can share connections, but not cursors
• COPY support: cursor.copy_from(), cursor.copy_to()
• large object support: connection.lobject()
• 2PC: connection.xid(), connection.tpc_begin(), . . .
• query cancel: dbconn.cancel()
• notices: dbconn.notices
• notiﬁcations: dbconn.notifies
• asynchronous communication
• coroutine support
• logging cursor

Setup

• included with PostgreSQL
• configure --with-python
• apt-get/yum install postgresql-plpython
• CREATE LANGUAGE plpythonu;
• Python 3: CREATE LANGUAGE plpython3u;
• “untrusted”, superuser only

Basic Examples
CREATE FUNCTION add(a int, b int) RETURNS int
LANGUAGE plpythonu
AS $$
return a + b
$$;

CREATE FUNCTION longest(a text, b text) RETURNS text
LANGUAGE plpythonu
AS $$
if len(a) > len(b):
return a
elif len(b) > len(a):
return b
else:
return None
$$;

Using Modules

CREATE FUNCTION json_to_array(j text) RETURNS text[]
LANGUAGE plpythonu
AS $$
import json

return json.loads(j)
$$;

Database Calls

CREATE FUNCTION clear_passwords() RETURNS int
LANGUAGE plpythonu
AS $$
rv = plpy.execute("UPDATE customers SET password =
NULL")
return rv.nrows
$$;

Database Calls With Parameters

CREATE FUNCTION set_password(username text, password
text) RETURNS boolean
LANGUAGE plpythonu
AS $$
plan = plpy.prepare("UPDATE customers SET password = $1
WHERE username= $2", ['text', 'text'])
rv = plpy.execute(plan, [username, password])
return rv.nrows == 1
$$;

Avoiding Prepared Statements

CREATE FUNCTION set_password(username text, password
LANGUAGE plpythonu
AS $$
rv = plpy.execute("UPDATE customers SET password = %s
WHERE username= %s" %
(plpy.quote_nullable(username),
plpy.quote_literal(password)))
$$;

(available in 9.1-to-be)

Caching Plans

CREATE FUNCTION set_password2(username text, password
LANGUAGE plpythonu
AS $$
if 'myplan' in SD:
plan = SD['myplan']
else:
plan = plpy.prepare("UPDATE customers SET password
= $1 WHERE username= $2", ['text', 'text'])
SD['myplan'] = plan
rv = plpy.execute(plan, [username, password])
$$;

Processing Query Results

CREATE FUNCTION get_customer_name(username text)
RETURNS boolean
LANGUAGE plpythonu
AS $$
plan = plpy.prepare("SELECT firstname || ' ' ||
lastname AS ""name"" FROM customers WHERE username =
$1", ['text'])
rv = plpy.execute(plan, [username], 1)
return rv[0]['name']
$$;

Compare: PL/Python vs. DB-API

PL/Python:
plan = plpy.prepare("SELECT ...")
for row in plpy.execute(plan, ...):
plpy.info(row["fieldname"])

DB-API:
dbconn = psycopg2.connect(...)
cursor.execute("SELECT ...")
for row in cursor.fetchall() do:
print row[0]

Set-Returning and Table Functions

CREATE FUNCTION get_customers(id int) RETURNS SETOF
customers
LANGUAGE plpythonu
AS $$
plan = plpy.prepare("SELECT * FROM customers WHERE
customerid = $1", ['int'])
rv = plpy.execute(plan, [id])
return rv
$$;

Triggers

CREATE FUNCTION delete_notifier() RETURNS trigger
LANGUAGE plpythonu
AS $$
if TD['event'] == 'DELETE':
plpy.notice("one row deleted from table %s" %
TD['table_name'])
$$;

CREATE TRIGGER customers_delete_notifier AFTER DELETE
ON customers FOR EACH ROW EXECUTE PROCEDURE
delete_notifier();

Exceptions

CREATE FUNCTION test() RETURNS text
LANGUAGE plpythonu
AS $$
try:
rv = plpy.execute("SELECT ...")
except plpy.SPIError, e:
plpy.notice("something went wrong")

The transaction is still aborted in < 9.1.

New in PostgreSQL 9.1

• SPI calls wrapped in subtransactions
• custom SPI exceptions: subclass per SQLSTATE,
.sqlstate attribute
• plpy.subtransaction() context manager
• support for OUT parameters
• quoting functions
• validator
• lots of internal improvements

Programming with Python and PostgreSQL

More Related Content

What's hot

Viewers also liked

Similar to Programming with Python and PostgreSQL

More from Peter Eisentraut

Recently uploaded

Programming with Python and PostgreSQL