在PostgreSQL中将列表插入为单数

我使用sqlalchemy引擎在postgresql表中插入数据。我不会将list插入一行,就好像list是具有很多值的字符串一样。

query = text('INSERT INTO table (list_id, list_name) VALUES ({}, {}) RETURNING'.format(my_list,'list_name'))
result_id = self.engine.execute(query)

当我尝试执行代码时,收到错误消息:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "["
LINE 1: ...INTO table (list_id, list_name) VALUES (['str1...                                                             ^

[SQL: INSERT INTO table (list_id, list_name) VALUES (['str1', 'str1', 'str1'], 'list_name') RETURNING id]

我试图将列表表示为str(my list),但结果相同。我也尝试str(['str1','str1','str1'])。replace('[','{').replace(']','}')

我的表查询:

CREATE TABLE api_services (
                  id SERIAL PRIMARY KEY,
                  list_id VARCHAR,
                  list_name VARCHAR(255) NOT NULL
                  );
评论
  • 依、太难
    依、太难 回复

    Since array in postgres is of form {"a","b"}, try to convert it to this form before the insert statement:

    my_list = str(map(str, my_list)).replace('[', '{').replace(']', '}').replace('\'', '\"')
    query = text('INSERT INTO table (list_id, list_name) VALUES ({}, {}) RETURNING'.format(my_list,'list_name'))
    result_id = self.engine.execute(query)