问题
我很难在Python中用自定义数据类型的数组的列将数据插入表中。
该方案看起来像:
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[]
);
然后,我想使用psycopg2:从Python将一些数据插入到这样的表中
foo = "my_text"
sources = ["ftp", "http"]
cursor.execute(
"""
INSERT INTO data(foo, sources)
VALUES (%s, %s)
""",
(foo, sources),
)
该代码最终出现运行时异常:
LINE 3: ...('my text', ARRAY['ftp...
^
HINT: You will need to rewrite or cast the expression.
我知道我需要对ARRAY
的每个元素调用::data_source
类型转换。我怎样才能做到这一点?
带有类和adapt()的变体
我试图利用psycopg2.extensions
包的adapt
功能
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),
)
但这个代码的结尾是:
psycopg2.errors.SyntaxError: syntax error at or near ""'ftp'""
LINE 3: ...my text', (b"'ftp'"::...
^
我想问题出在AsIs
函数中,它将getquoted
函数中的字节和格式化的字符串组合在一起。
有人能帮助我或为我提供任何解决方案吗?
感谢
扩展Adrian Klaver的Answer,还需要将强制转换为您在schema中定义的数据库类型data_source
。
cur.execute(
"""
INSERT INTO data(foo, sources)
VALUES (%s, %s::data_source[])
""",
(foo, sources),
)
con.commit()
这对我有用。
我在评论中提出的一个完整的例子:
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}
将源列表转换为数组的字符串表示形式,并将其用作sources
值。