0

I have a dataframe column with some of them containing single quotation mark in the string like so (which are two single quotation marks next to each other so it can escape the single quotation in SQL): 

 'Group1>>TV>>Blue>>Q''19>>four'

and the rest have strings that don't contain a quotation mark. When converting my dataframe column to get a list of all strings that I can copy and paste into an SQL query, it ends up putting double quotation marks around the string when I need a single quotation mark for SQL.

 ids = ["Group1>>TV>>Blue>>Q''19>>four", 'Group3>>Desktop>>Blue>>>two', 'Group1>>Desktop>>Green>>>two']

I want to change it so that I get this: 

ids = ['Group1>>TV>>Blue>>Q''19>>four', 'Group3>>Desktop>>Blue>>>two', 'Group1>>Desktop>>Green>>>two']

I have tried a few different things but nothing seems to work. 

 [str(x) for x in ids]
 [x.strip('"') for x in ids]
 [x.replace('"', "'") for x in ids]
mjoy
  • 606
  • 2
  • 9
  • 19
  • The string `"Group1>>TV>>Blue>>Q''19>>four"` does not contain double quotes. The quotes are just delimiters, to specify where the string begins and ends. – John Gordon Jun 18 '19 at 15:13
  • 2
    Are you building SQL with string formatting? That's a really bad, dangerous thing to do. Use parameterized queries. – user2357112 Jun 18 '19 at 15:13
  • This `'Group1>>TV>>Blue>>Q''19>>four'` became this `'Group1>>TV>>Blue>>Q19>>four'` after string constant concatenation https://stackoverflow.com/questions/34174539/python-string-literal-concatenation – geckos Jun 18 '19 at 15:21
  • I just wanted to get a list of the strings to copy and paste into a sql query where clause. However, SQL doesn't read double quotes around a string as a string so I just needed to change it to double quotes. I was hoping to find a quick way with Python do it instead of manually changing the double quotes to single quotes. – mjoy Jun 19 '19 at 15:40
  • change the outside double quotes to a single quote** – mjoy Jun 19 '19 at 15:47

1 Answers1

3

One approach adapting this answer would be to extend python's built-in str class modifying the canonical string representation of the object, i.e. the __repr__() dunder method so that the double quotes are replaced by single quotes:

class str2(str):
    def __repr__(self):
        return ''.join(('\'', super().__repr__()[1:-1], '\''))

Then simply instanciate with the strings in you list:

ids = ["Group1>>TV>>Blue>>Q''19>>four", 'Group3>>Desktop>>Blue>>>two', \
       'Group1>>Desktop>>Green>>>two']

list(map(str2, ids))

['Group1>>TV>>Blue>>Q''19>>four',
 'Group3>>Desktop>>Blue>>>two',
 'Group1>>Desktop>>Green>>>two']
yatu
  • 86,083
  • 12
  • 84
  • 139
  • This is a great solution for changing outer double quote representation to outer single quote representation. Google kept trying to give solutions for going from single to double and not this direction. Hopefully, this comment helps this solution move up the list – mathisfun Mar 14 '23 at 14:59