postgresql - 将列名和值的列表传递给 Psycopg

Question

我试图生成一些有效的 Postgresql 语句，但是 cursor.execute 方法以一种奇怪的方式解释我的参数。

这是我的代码切割:

for key in data_dict.keys():
   if key in table_columns:
      heads = ', '.join([i for i in data_dict.iterkeys()])
      values = [ ", ".join(i) for i in zip(*(data_dict[i] for i in data_dict.keys())) ]

for value in values:
   insert_statement = "INSERT INTO stamm_data (%s) VALUES (%s);"
   print cur.mogrify(insert_statement, (heads, value))

输出:

INSERT INTO stamm_data ('extra, produktcode, typ, hotelname, kategorie, size') VALUES ('ex1, ACE001, A, foo01, 3, big');
INSERT INTO stamm_data ('extra, produktcode, typ, hotelname, kategorie, size') VALUES ('ex2, ACE002, B, foo02, 4, small');
INSERT INTO stamm_data ('extra, produktcode, typ, hotelname, kategorie, size') VALUES ('ex3, ACE003, C, foo03, 5, big');

我还尝试将 heads 和 values 作为列表分配给 cursor.execute。但这看起来更奇怪。

我想要的是没有撇号的 heads 和声明中每个项目(整数旁边)的 values。

Answer 1

给定一个 list 的 dict

from psycopg2.extensions import AsIs

data_list = [
    {
        'extra': 'ex1',
        'produktcode': 'ACE001',
        'typ': 'A',
        'hotelname': 'foo01',
        'kategorie': 3,
        'size': 'big'
    },
    {
        'extra': 'ex2',
        'produktcode': 'ACE002',
        'typ': 'B',
        'hotelname': 'foo02',
        'kategorie': 4,
        'size': 'small'
    }
]

heads = data_list[0].keys()

insert_statement = 'insert into stamm_data (%s) values %s'

for d in data_list:

    print cursor.mogrify(insert_statement, (
        AsIs(','.join(heads)),
        tuple([d[head] for head in heads])
    ))

输出:

insert into stamm_data (extra,produktcode,typ,hotelname,kategorie,size) values ('ex1', 'ACE001', 'A', 'foo01', 3, 'big')
insert into stamm_data (extra,produktcode,typ,hotelname,kategorie,size) values ('ex2', 'ACE002', 'B', 'foo02', 4, 'small')

Python tuple 按照 values 子句的预期适应 Postgresql record。

给定一个字典:

data_dict = {
    'extra': ['ex1', 'ex2', 'ex3', 'ex4'],
    'produktcode': ['ACE001', 'ACE002', 'ACE003', 'ACE004'],
    'typ': ['A', 'B', 'C', 'D'],
    'hotelname': ['foo01','foo02','foo03','foo04'],
    'kategorie': [3, 4, 5, 6],
    'size': ['big', 'small', 'medium', 'big']
}

heads = data_dict.keys()

insert_statement = 'insert into stamm_data (%s) values %s'

for t in zip(*(data_dict[head] for head in heads)):
    print cursor.mogrify(insert_statement, (
        AsIs(','.join(heads)), t
    ))

输出:

insert into stamm_data (extra,produktcode,typ,hotelname,kategorie,size) values ('ex1', 'ACE001', 'A', 'foo01', 3, 'big')
insert into stamm_data (extra,produktcode,typ,hotelname,kategorie,size) values ('ex2', 'ACE002', 'B', 'foo02', 4, 'small')
insert into stamm_data (extra,produktcode,typ,hotelname,kategorie,size) values ('ex3', 'ACE003', 'C', 'foo03', 5, 'medium')
insert into stamm_data (extra,produktcode,typ,hotelname,kategorie,size) values ('ex4', 'ACE004', 'D', 'foo04', 6, 'big')