python string parsing issues when saving sql commands to file

I have dicts data I am looping through to form INSERT commands for postgresql. where the parent keys of the dicts are the column names and the parent values are the column values:

data = {
        'id': 45,
        'col1': "foo's",
        'col2': {'dict_key': 5}
       }

columns = ', '.join(data.keys())
# replace single quote with double to form a json type for psql column
data['col2'] = data['col2'].replace("'", '"')

with open("file.sql", "w") as f:
    command = "INSERT INTO table1({}) VALUES {};"    
    f.write(command.format(columns, tuple(data.values()))

The problem is that the output of this is not formatted correctly for sql to execute. This is the output of the above:

INSERT INTO table1(id, col1, col2) VALUES (45, "foo's", '{"dict_key":5}');

The json field is formatted correctly with the single quotes around the value. But col2 keeps the double quotes if the string in col2 contains a single quote. This is a problem because postgresql requires single quotes to identify TEXT input.

Is there a better way to parse data into psql insert commands?

Did you try using json.dump() and repr()?

columns = ', '.join(data.keys())

data['col1'] = repr(data['col1'])
data['col2'] = json.dumps(data['col2'])
...

This appears to be a limitation (or rather an implementation detail) in Python, with how the __repr__() for strings or str is defined.

Try this sample code out:

value = 'wont fix'; assert f'{value!r}' == "'wont fix'"
value = 'won\'t fix'; assert f'{value!r}' == '"won\'t fix"'

As can be seen, single quotes are preferred in the repr for strings, unless the string itself contains a single quote - in that case, double quotes are used to wrap the repr for the string.

A "quick and dirty" solution is to implement a custom string subclass, SQStr, which effectively overrides the default repr to always wrap a string with single quotes:

class SQStr(str):
    def __repr__(self):
        value = self.replace("'", r"\'")
        return f"'{value}'"

If you want to also support double-escaped single quotes like r"\\\'", then something like this:

class SQStr(str):
    def __repr__(self, _escaped_sq=r"\'", _tmp_symbol="|+*+|",
                 _default_repr=str.__repr__):
        if "'" in self:
            if _escaped_sq in self:
                value = (self
                         .replace(_escaped_sq, _tmp_symbol)
                         .replace("'", _escaped_sq)
                         .replace(_tmp_symbol, r"\\\'"))
            else:
                value = self.replace("'", _escaped_sq)
            return f"'{value}'"
        # else, string doesn't contain single quotes, so we
        # can use the default str.__repr__()
        return _default_repr(self)

Now it appears to work as expected:

value = 'wont fix'; assert f'{SQStr(value)!r}' == "'wont fix'"
value = 'won\'t fix'; assert f'{SQStr(value)!r}' == r"'won\'t fix'"
# optional
value = r"won't fix, won\'t!"; assert f'{SQStr(value)!r}' == r"'won\'t fix, won\\\'t!'"