Boulder Linux Users Group, '2020-04-09'::date

Daniele Varrazzo

slides source

• There is this language, Python 🐍
  • it's got all its types (strongly typed, dynamically typed)
• and this database, PostgreSQL 🐘
  • yeah, not a great name. Let's call it Postgres
  • it's got a lot of different types too
• You want to do something with the two 🐍↔️🐘
  • they are both very extendible
  • someone should map the two together

Psycopg!

• Yeah, maybe questionable name too. 🤔
• But now we sort of like it. 🤭

psycopg2

• Current mature adapter: in production for about 15 years 👴
• https://www.psycopg.org/

psycopg3

• Development recently started. Exciting! 👶
• https://www.varrazzo.com/blog/2020/03/06/thinking-psycopg3/
• Please support the project 💜

The roles of the main actors

import psycopg2                             # the driver
conn = psycopg2.connect("dbname=piro")      # the connection/session
cur = conn.cursor()                         # the cursor - holds a result

cur.execute("select 10 as a, 'foo' as b")   # sends command
cur.fetchone()                              # retrieve results
conn.commit()                               # controls the session

Different ways to consume data

cur.fetchone()      # returns one tuples
cur.fetchmany(n)    # returns a list of n tuples
cur.fetchall()      # returns a list with all the tuples
for t in cur:
    pass            # iterable of tuples

Default data types mapping

Typecasters have:

• one or more OID
• a name
• a conversion function

Customizing a typecaster

>>> cur.execute("select 123.45")
>>> cur.fetchone()
(Decimal('123.45'),)

>>> from psycopg2 import extensions as ext

>>> def num2float(s, cur):
...     if s is None:
...         return float(s)

>>> t = ext.new_type((1700,), "NUM2FLOAT", num2float)
>>> ext.register_type(t, cur)

>>> cur.execute("select 123.45")
>>> cur.fetchone()
(123.45,)

>>> cur.execute("select '%s' || '%s'" % ('a', 'b'))
>>> cur.fetchone()
('ab',)

>>> cur.execute("select '%s' || '%s'" % ("O'Reilly", ' Books'))
Traceback (most recent call last):
  File "<ipython-input-29-720a7746fc83>", line 1, in <module>
    cur.execute("select '%s' || '%s'" % ("O'Reilly", ' Books'))
ProgrammingError: syntax error at or near "' || '"
LINE 1: select 'O'Reilly' || ' Books'
                        ^

>>> cur.execute("select %s || %s", ("O'Reilly", ' Books'))
>>> cur.fetchone()
("O'Reilly Books",)

>>> cur.execute("insert into students (name) values ('%s')" % name)

Funny, but wrong conclusion:

>>> cur.execute("insert into students (name) values (%s)" , [name])

• Look ma: no sanitizing database input here!
• (You must not do it, we do it for you)

Using gevent, gevent-websocket, psycogreen

Note: the pushdemo.py script is not running.

Download the demo code

• Q: how can I install psycopg without needing a C compiler, install packages, etc?

  A: psycopg3 has only an optional C package and it's otherwise pure Python

• Q: adaptation is kinda slow on my 1M entries array

  A: psycopg3 uses a different mechanism than adaptation and is much more performing with less objects created

• Q: my database is SQL_ASCII and now it's a jumble of different encodings which don't make sense, but we are scraper guys so we have to

  A: psycopg3 allows you to use SQL_ASCII databases as a binary databases, encoding-agnostic

• Q: ... A: You should really sponsor psycopg3! 💜