Python将sql数据字符串转换为列表

Question

我有字符串形式的sql数据，我正试图将其转换为列表

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(")", "]")

但是，当我尝试使用list（）将其转换为list时，它会破坏一切

Answer 1

尝试使用ast.literal_eval和嵌套列表：

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

Answer 2

使用正则表达式（python中的re模块）的简单解决方案：

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捕获所有括号之间的所有字符（()）

lst[:-6]+')'删除尾随的,'\r\n')并追加)（生成完整的“元组字符串”），以便eval平稳运行并返回元组，然后使用list()将其转换为列表