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. It lets you split with multiple delimiters — Inyoung Kim 김인영
– Inyoung Kim 김인영, Commented Jan 29, 2020 at 3: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
– Paul Becotte, Commented Jan 29, 2020 at 3:18

U13-Forward · Accepted Answer · 2020-01-29 03:41:30Z

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, 2020 at 3:41

answered Jan 29, 2020 at 3:15

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jarvis Over a year ago

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 Over a year ago

@Jarvis Edited mine

Jarvis · Accepted Answer · 2020-01-29 03:39:35Z

1

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

edited Jan 29, 2020 at 3:39

answered Jan 29, 2020 at 3:32

Jarvis

8,5923 gold badges33 silver badges61 bronze badges

Collectives™ on Stack Overflow

Python convert string of sql data to list

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related