CODEWITHSUNDEEP
postgresqlpython-2.7dictionarypsycopg2
jwi
jwi

Reputation: 1178

Passing a list of column names and values to Psycopg

Im trying to generate some valid Postgresql statements, but the cursor.execute method interprets my parameters in a odd way.

Here's my code cutting:

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))

Output:

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');

I've also tried to assign heads and values as lists to cursor.execute. But that looks even more curious.

What I want is the heads without apostrophes and the values with apostrophes for each item (beside the integers) in my statement.

Upvotes: 2

Views: 1823

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

Given a list of dicts

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])
    ))

Output:

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')

a Python tuple is adapted to a Postgresql record as expected by the values clause.

Given a dictionary:

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
    ))

Output:

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')

Upvotes: 2

Related Questions