CODEWITHSUNDEEP
pythonstringpostgresqldictionarystring-formatting
Zeliax
Zeliax

Reputation: 5386

Python - creating string from dict items (for writing to postgresql db)

I'm writing some code using psycopg2 to connect to a PostGreSQL database.

I have a lot of different data types that I want to write to different tables in my PostGreSQL database. I am trying to write a function that can write to each of the tables based on a single variable passed in the function and I want to write more than 1 row at a time to optimize my query. Luckily PostGreSQL allows me to do that: PostGreSQL Insert:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

I have run into a problem that I was hoping someone could help me with.

I need to create a string:

string1 = (value11, value21, value31), (value12, value22, value32)

The string1 variable will be created by using a dictionary with values. So far I have been able to create a tuple that is close to the structure I want. I have a list of dictionaries. The list is called rows:

string1 = tuple([tuple([value for value in row.values()]) for row in rows])

To test it I have created the following small rows variable:

rows = [{'id': 1, 'test1': 'something', 'test2': 123},
        {'id': 2, 'test1': 'somethingelse', 'test2': 321}]

When rows is passed through the above piece of code string1 becomes as follows:

((1, 'something', 123), (2, 'somethingelse', 321))

As seen with string1 I just need to remove the outmost parenthesis and make it a string for it to be as I need it. So far I don't know how this is done. So my question to you is: "How do I format string1 to have my required format?"

Upvotes: 0

Views: 2120

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125414

execute_values makes it much easier. Pass the dict sequence in instead of a values sequence:

import psycopg2, psycopg2.extras

rows = [
    {'id': 1, 'test1': 'something', 'test2': 123},
    {'id': 2, 'test1': 'somethingelse', 'test2': 321}
]

conn = psycopg2.connect(database='cpn')
cursor = conn.cursor()

insert_query = 'insert into t (id, test1, test2) values %s'
psycopg2.extras.execute_values (
    cursor, insert_query, rows,
    template='(%(id)s, %(test1)s, %(test2)s)',
    page_size=100
)

And the values are inserted:

table t;
 id |     test1     | test2 
----+---------------+-------
  1 | something     |   123
  2 | somethingelse |   321

To have the number of affected rows use a CTE:

insert_query = '''
    with i as (
        insert into t (id, test1, test2) values %s
        returning *
    )
    select count(*) from i
'''
psycopg2.extras.execute_values (
    cursor, insert_query, rows,
    template='(%(id)s, %(test1)s, %(test2)s)',
    page_size=100
)
row_count = cursor.fetchone()[0]

Upvotes: 2

Vsevolod Kulaga
Vsevolod Kulaga

Reputation: 676

The solution that you described is not so well because potentially it may harm your database – that solution does not care about escaping string, etc. So SQL injection is possible. Fortunately, psycopg (and psycopg2) has cursor's methods execute and mogrify that will properly do all this work for you:

import contextlib

with contextlib.closing(db_connection.cursor()) as cursor:
    values = [cursor.mogrify('(%(id)s, %(test1)s, %(test2)s)', row) for row in rows]
    query = 'INSERT INTO films (id, test1, test2) VALUES {0};'.format(', '.join(values))

For python 3:

import contextlib

with contextlib.closing(db_connection.cursor()) as cursor:
    values = [cursor.mogrify('(%(id)s, %(test1)s, %(test2)s)', row) for row in rows]
    query_bytes = b'INSERT INTO films (id, test1, test2) VALUES ' + b', '.join(values) + b';'

Upvotes: 1

DexJ
DexJ

Reputation: 1264

With little modification you can achieve this. change your piece of cod as follows 

','.join([tuple([value for value in row.values()]).__repr__() for row in rows])

current output is

tuple of tuple

(('something', 123, 1), ('somethingelse', 321, 2))

After changes output will be

in string format as you want

"('something', 123, 1),('somethingelse', 321, 2)"

Upvotes: 2

Related Questions