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?
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
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.
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.