Python convert string of sql data to list

Question

I have sql data in string form which I'm trying to convert to list.

sql = "(10001,'AEX','CCC','X12344','John, Doe','Not indicated','None','No','No','No','\r\n'),(10002,'AEX','CCC','X12344','John, Doe','Not indicated','None','No','No','No','\r\n')"
sql = sql.replace("(", "[")
sql = sql.replace(")", "]")

However when I try to convert it to list using list(), it breaks everything.

• PacketLoss: Do you want to keep the ' and \n\t in your output?
• Inyoung Kim 김인영: Have a look at this answer. It lets you split with multiple delimiters
• Paul Becotte: lots of good answers. However, almost certainly the wrong question. Whatever database driver you are using almost certainly can format that for you automatically.

Answer 1

Try using ast.literal_eval and a nested list comprehension:

>>> from ast import literal_eval as leval
>>> sql = "(10001,'AEX','CCC','X12344','John, Doe','Not indicated','None','No','No','No','\r\n'),(10002,'AEX','CCC','X12344','John, Doe','Not indicated','None','No','No','No','\r\n')"
>>> [[leval(x) for x in i.strip('()').replace(",'", "split'").split('split')[:-1]] for i in sql.replace('\r\n', '').replace('),(', ')split(').split('split')]
[[10001, 'AEX', 'CCC', 'X12344', 'John, Doe', 'Not indicated', 'None', 'No', 'No', 'No'], [10002, 'AEX', 'CCC', 'X12344', 'John, Doe', 'Not indicated', 'None', 'No', 'No', 'No']]
>>> 

• Jarvis: Seems like your solution has one extra empty string ('') in each of the lists formed, which should not be in the expected output.
• U13-Forward: @Jarvis Edited mine

Answer 2

Simple solution using regex (re module in python) :

import re
sql = "(10001,'AEX','CCC','X12344','John, Doe','Not indicated','None','No','No','No','\r\n'),(10002,'AEX','CCC','X12344','John, Doe','Not indicated','None','No','No','No','\r\n')"
groups = re.findall('\([^()]*\)', sql)
lists = [list(eval(lst[:-6]+')')) for lst in groups]

'\([^()]*\)' regex to capture all the characters between all the parentheses (()).

lst[:-6]+')' to remove the trailing ,'\r\n') and append ) (to make a complete 'tuple string') so that eval runs smoothly and returns a tuple, then convert it to a list using list().