Apply type cast to items of array in parameter with psycopg2

user_3272715 0
Viewed 361

Issue

I am struggling with inserting data into table with column of array of custom data type in Python.

The scheme looks like:

CREATE TYPE data_source AS ENUM ('smtp', 'ftp', 'http');
CREATE TABLE IF NOT EXISTS data(
    id BIGSERIAL PRIMARY KEY,
    foo TEXT NOT NULL,
    sources data_source[]
);

then, I want to insert some data into such table from Python using psycopg2:

foo = "my_text"
sources = ["ftp", "http"]

cursor.execute(
    """
        INSERT INTO data(foo, sources)
        VALUES (%s, %s)
    """,
    (foo, sources),
)

this code ends up with runtime exception:

LINE 3: ...('my text', ARRAY['ftp...
                       ^
HINT:  You will need to rewrite or cast the expression.

I understand that I need to call ::data_source type casting to each of element of the ARRAY. How can I achieve this?

A variant with class and adapt()

I tried to take advantage of adapt function from the psycopg2.extensions package

class Source:
    def __init__(self, source):
        self.string = source


def adapt_source(source):
     quoted = psycopg2.extensions.adapt(source.string).getquoted()
     return psycopg2.extensions.AsIs(f"{quoted}::data_source'")


psycopg2.extensions.register_adapter(Source, adapt_source)

foo = "my_text"
sources = [Source("ftp"), Source("http")]

cursor.execute(
    """
        INSERT INTO data(foo, sources)
        VALUES (%s, %s)
    """,
    (foo, sources),
)

but this code ends up with:

psycopg2.errors.SyntaxError: syntax error at or near ""'ftp'""
LINE 3: ...my text', (b"'ftp'"::...
                       ^

I guess the problem is in AsIs function which combines bytes from getquoted function and formatted string.

Can anybody helps me or point me to any solution?

Thanks

psycopg2 postgresql python
2 Answers

Extending the Answer of Adrian Klaver, you also need to cast to the database type data_source you defined in schema.

cur.execute(
    """
        INSERT INTO data(foo, sources)
        VALUES (%s, %s::data_source[])
    """,
    (foo, sources),
)
con.commit()

This works for me.

user_35828290

A complete example of what I proposed in my comment:

CREATE TYPE data_source AS ENUM ('smtp', 'ftp', 'http');
CREATE TABLE IF NOT EXISTS data(
    id BIGSERIAL PRIMARY KEY,
    foo TEXT NOT NULL,
    sources data_source[]
);


import psycopg2 
con = psycopg2.connect(database="test", host='localhost', user='postgres')  
cur = con.cursor()

foo = "my_text"
source_list = ["ftp", "http"]
sources = '{' + ','.join(source_list) + '}'
sources
'{ftp,http}'

cur.execute(
    """
        INSERT INTO data(foo, sources)
        VALUES (%s, %s)
    """,
    (foo, sources),
)
con.commit()

select * from data;
 id |   foo   |  sources   
----+---------+------------
  1 | my_text | {ftp,http}

Turn a list of sources into a string representation of an array and use that as the sources value.

user_70706130
Related