[FIXED] How to adapt new Python types to SQL syntax in python3

Issue

I'm trying to figure out how to insert compound data types into postgresql from python3 using psycopg2. In this I'm following the example from the psycopg2 documentation:

>>> 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):
...     x = adapt(point.x).getquoted()
...     y = adapt(point.y).getquoted()
...     return AsIs("'(%s, %s)'" % (x, y))

>>> register_adapter(Point, adapt_point)

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

But the resulting sql command is incorrect:

psycopg2.ProgrammingError: syntax error at or near "1.23"
LINE 1: INSERT INTO atable (apoint) VALUES ('(b'1.23', b'4.56')')

How should I modify the example to make psycopg2 produce the correct sql command?

INSERT INTO atable (apoint) VALUES ('(1.23, 4.56)');

Solution

You need to decode the x/y values to strings, they are Byte values (visually marked by the prefixed b). That's a fundamental change between Python2 and Python3.

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

def adapt_point(point):
    x = adapt(point.x).getquoted().decode('utf-8')
    y = adapt(point.y).getquoted().decode('utf-8')
    return AsIs("'(%s, %s)'" % (x, y))

register_adapter(Point, adapt_point)
p = Point(1.23, 4.56)
print (cur.mogrify("INSERT INTO atable (apoint) VALUES (%s)", (p,)))

returns:

b"INSERT INTO atable (apoint) VALUES ('(1.23, 4.56)')"

Answered By - Maurice Meyer