CODEWITHSUNDEEP
pythonpython-3.xpsycopg2
Noyer282
Noyer282

Reputation: 984

How to adapt new Python types to SQL syntax in python3

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)');

Upvotes: 0

Views: 1335

Answers (1)

Maurice Meyer
Maurice Meyer

Reputation: 18136

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)')"

Upvotes: 1

Related Questions