Psycopg2 Python3 Inserting multiple variables as a List

Question

We have a function

    def update_cfg(self, alert_name, description, owner, cfg_json, permission= '{"org_ids":[""], "dept_ids":[""]}'):
    try:
        sql = "UPDATE scheduled_tasks SET json = %s, last_updated_date = %s, description = %s, permission = %s WHERE owner = %s AND scheduled_task_name = %s;"

        return self.execute_write_sql(sql, [cfg_json, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                            description, permission, owner, alert_name])
    except Exception as e:
        raise e

The 'Update_cfg' calls the 'execute_write_sql' function

    def execute_write_sql(self, sql, values, datasource='localdb'):
    try:
        self.db_con = self.connect_to_db(datasource)

        print('values are'+str(values))
        # print('value length is' + len(values))

        cursor = self.db_con.cursor()
        if len(values) > 0:
            cursor.execute(sql, values)
            print('1st query is' + str(cursor.query))
            cursor.execute()
            print('2nd query is' + str(cursor.query))

        else:
            cursor.execute(sql)
            print(sql)
        self.db_con.commit()
    except Exception as exception:
        logging.error("Failed to execute SQL in the function execute_write_sql: " + sql)
        print(sql)
        self.exception = exception
        if self.db_con != None:
            self.db_con.rollback()
        return False
    finally:
        if (self.db_con != None):
            self.db_con.close()

    return True

The 'value' input of the 'execute_write_sql' function is a list:

['{giant json}', '2019-03-25 14:49:36', 'test', '{"org_ids":["xxx"], "dept_ids":[]}', 'abc', 'v2']

This makes the 'cursor.query' returns the sql query with improper '\' signs like this

UPDATE scheduled_tasks SET json = \'{giant json}\', last_updated_date = \'2019-03-25 14:49:36\', description = \'test\', permission = \'{"org_ids":["xxx"], "dept_ids":[]}\' WHERE owner = \'abc\' AND scheduled_task_name = \'v2\';

Ideally we want the sql queries to be without '\'

UPDATE scheduled_tasks SET json = '{giant json}', last_updated_date = '2019-03-25 14:49:36', description = 'test', permission = '{"org_ids":["xxx"], "dept_ids":[]}' WHERE owner = 'abc' AND scheduled_task_name = 'v2';

Is there any easy way to fix this?

You are misunderstanding the backslashes you see. **They are not part of the value**. You are looking at the string representation, which produces a valid Python string literal, one you can copy and paste into your code again or into an interpreter session. In order for the string literal, surrounded by single quotes (`'this is a string representation'`) to use both double quotes and single quotes *in the value*, a choice must be made to either surround with double quotes and escape the double quotes in the value, or to escape the single quotes. — Martijn Pieters, Jul 22 '19 at 13:04

score -2 · Answer 1 · answered Mar 26 '19 at 00:45

Enlightening by this link:

https://www.compose.com/articles/formatted-sql-in-python-with-psycopgs-mogrify/

            # very tricky here. We should use 'mogrify' to show us what the real executed query by psycopgs first

            new_sql = cursor.mogrify(sql, values)

            # The mogrify result suggests that psycopgs returns weird bytes with escapes \\, b\ that messes up the sql syntax

           new_sql2 = new_sql.decode("unicode_escape")

            # keep this line to make debugging easier locally

            new_sql3 = str(new_sql2)

            # after decoding, there are still escape symbols here and there. Need to clean them up

            new_sql4 = new_sql3.replace("\\", "")

            cursor.execute(new_sql4)

            # print the real executed sql query in the log file for future debuggin purposes

            print('The query is in the execute_write_sql function is ' + str(cursor.mogrify(new_sql4)))

The backslashes are **not part of the value** and there is nothing to replace or decode here. — Martijn Pieters, Jul 22 '19 at 13:05

Psycopg2 Python3 Inserting multiple variables as a List

1 Answers1