New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

I have bug with array of composite types #231

Closed
psycoteam opened this Issue Aug 22, 2014 · 3 comments

Comments

Projects
None yet
2 participants
@psycoteam

psycoteam commented Aug 22, 2014

Originally submitted by: Psycopg website

Submitted by: Nikita Kuznetsov

import psycopg2
import psycopg2.extras


conn = psycopg2.connect("dbname=test user=y")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS test_comp_table;")
cur.execute("DROP TYPE IF EXISTS test_comp_type;")
cur.execute("CREATE TYPE test_comp_type AS (first text, second text);")
psycopg2.extras.register_composite("test_comp_type", conn, globally=True)
cur.execute("CREATE TABLE test_comp_table (data test_comp_type[]);")
try:
    cur.execute("INSERT INTO test_comp_table VALUES (%(data)s)",
                dict(data=[("first", "second")]))
except:
    print(cur.query)
    raise

"""
b"INSERT INTO test_comp_table VALUES (ARRAY[('first', 'second')])"
Traceback (most recent call last):
  File "../common/test.py", line 14, in <module>
    dict(data=[("first", "second")]))
psycopg2.ProgrammingError: column "data" is of type test_comp_type[] but expression is of type record[]
LINE 1: INSERT INTO test_comp_table VALUES (ARRAY[('first', 'second'...
                                            ^
HINT:  You will need to rewrite or cast the expression.
"""
@dvarrazzo

This comment has been minimized.

Show comment
Hide comment
@dvarrazzo

dvarrazzo Aug 22, 2014

Member

Try adding an explicit cast after your placeholder specifying that the type you are passing is an array of test_comp_type. This should work:

cur.execute("INSERT INTO test_comp_table VALUES (%(data)s::test_comp_type[])",
            dict(data=[("first", "second")]))
Member

dvarrazzo commented Aug 22, 2014

Try adding an explicit cast after your placeholder specifying that the type you are passing is an array of test_comp_type. This should work:

cur.execute("INSERT INTO test_comp_table VALUES (%(data)s::test_comp_type[])",
            dict(data=[("first", "second")]))
@psycoteam

This comment has been minimized.

Show comment
Hide comment
@psycoteam

psycoteam Aug 22, 2014

Originally submitted by: Nikita Kuznetsov

No, I want autocast, because I use sqlalchemy and I work througth sqlalchemy interface.

psycoteam commented Aug 22, 2014

Originally submitted by: Nikita Kuznetsov

No, I want autocast, because I use sqlalchemy and I work througth sqlalchemy interface.

@dvarrazzo

This comment has been minimized.

Show comment
Hide comment
@dvarrazzo

dvarrazzo Aug 22, 2014

Member

In this case you should create your data as a specific python type, it cannot be a generic tuple, and register a typecaster for it. For instance:

Comp = namedtuple('Comp', 'first second')

class CompAdapter:
    def __init__(self, x):
        self.adapted = psycopg2.extensions.SQL_IN(x)
    def prepare(self, conn):
        self.adapted.prepare(conn)
    def getquoted(self):
        return self.adapted.getquoted() + '::test_comp_type'

psycopg2.extensions.register_adapter(Comp, CompAdapter)

# this now works
cur.execute("INSERT INTO test_comp_table VALUES (%(data)s)",
    {'data': [ Comp('a', 'b'), Comp('c', 'd') ]})
Member

dvarrazzo commented Aug 22, 2014

In this case you should create your data as a specific python type, it cannot be a generic tuple, and register a typecaster for it. For instance:

Comp = namedtuple('Comp', 'first second')

class CompAdapter:
    def __init__(self, x):
        self.adapted = psycopg2.extensions.SQL_IN(x)
    def prepare(self, conn):
        self.adapted.prepare(conn)
    def getquoted(self):
        return self.adapted.getquoted() + '::test_comp_type'

psycopg2.extensions.register_adapter(Comp, CompAdapter)

# this now works
cur.execute("INSERT INTO test_comp_table VALUES (%(data)s)",
    {'data': [ Comp('a', 'b'), Comp('c', 'd') ]})

This issue was closed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment