Apply type cast to items of array in parameter with psycopg2-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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.