Escaping characters in Python Psycopg2 query

Question

im trying to escape characters to create a transaction block in python and commit it all at once with no success...

for example:

def transaction_block(sql):
    global sql_transaction
    sql_transaction.append(sql)
    if len(sql_transaction) > 1000:
        db.execute('BEGIN TRANSACTION')
        for s in sql_transaction:
            try:
                db.execute(s)
            except Exception as e:
                print(e)
        db.commit()
        sql_transaction = []

def sql_insert_comment(parentid,comment):
    try:
        sql = "INSERT INTO p_reply (parent_id, comment) VALUES ('{}','{}');".format(parentid, comment)
        transaction_block(sql)
    except Exception as e:
        print('error s02',str(e))

how should i adapt this to use the %s so i can escape it all? or there is a better way to accomplish this?

###UPDATE 1###

found a possible answer using execute_values() credits to: https://billyfung.com/writing/2017/06/improving-multiple-inserts-with-psycopg2/

700% increase performance

IF you know a better way yet, please let me know ;)

vmonteco · Accepted Answer · 2017-12-12 01:48:36Z

What about something like this:

entries = [
    ("parent_id1", "comment1"),
    ("parent_id2", "comment2"),
    ("parent_id3", "comment3")
]
query = "INSERT INTO p_reply (parent_id, comment) VALUES %s;" % ", ".join(str(entry) for entry in entries)

?

That may fit your needs:

>>> "INSERT INTO p_reply (parent_id, comment) VALUES %s;" % ", ".join(str(entry) for entry in [("parent_id1", "comment1"), ("parent_id2", "comment2")])
"INSERT INTO p_reply (parent_id, comment) VALUES ('parent_id1', 'comment1'), ('parent_id2', 'comment2');"

If you have integers (parent_id values) in your dataset, you may also have to convert it into str or modify the formatted string to add the '.

Thanks, I think it's something close to the execute_values() function, it works.

Denis · Accepted Answer · 2017-12-12 00:43:01Z

0

You should escape like this:

try:
    sql = "INSERT INTO p_reply (parent_id, comment) VALUES (%s,%s)"
    db.execute(sql,[parent_id,comment])
except Exception as e:
    print('error s02',str(e))

answered Dec 12, 2017 at 0:43

Denis

1,6682 gold badges20 silver badges33 bronze badges

1 Comment

tubadc Over a year ago

Tks, i know that i can escape with %s, but how can i add this for example: (sql,[parent_id,comment]) to a single variable and add the variable to a list so i can iterate through it and commit every 1000 queries...? that is my problem... i can do it as tuple item to a list, and loop n^2 but then... i think its just less time consume to execute 1 by 1.... what do you think? Tks again!

Collectives™ on Stack Overflow

Escaping characters in Python Psycopg2 query

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related