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.

Have a look at [this answer](https://stackoverflow.com/a/1059596/8471995). It lets you split with multiple delimiters — Inyoung Kim 김인영, Jan 29 '20 at 03:03
lots of good answers. However, almost certainly the wrong question. Whatever database driver you are using almost certainly can format that for you automatically. — Paul Becotte, Jan 29 '20 at 03:18

U13-Forward · Accepted Answer · 2020-01-29T03:41:30.280

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']]
>>>

edited Jan 29 '20 at 03:41

answered Jan 29 '20 at 03:15

U13-Forward

69,221
14
89
114

Seems like your solution has one extra empty string (`''`) in each of the lists formed, which should not be in the expected output. – Jarvis Jan 29 '20 at 03:34
1

@Jarvis Edited mine – U13-Forward Jan 29 '20 at 03:41

Jarvis · Answer 2 · 2020-01-29T03:39:35.590

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().

Python convert string of sql data to list

2 Answers2