Python Forum

Hello,

Is there a way to reliably split a string containing multiple sqlite statements?

str.split(sep=None, maxsplit=-1) does not always work -

Python 3.5.3 (default, Sep 27 2018, 17:25:39) 
[GCC 6.3.0 20170516] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> sql = "select 'abc'; select 'def'; select 'annoying semicolon ;'"
>>> sql.split(';')
["select 'abc'", " select 'def'", " select 'annoying semicolon ", "'"]
>>> 

I was hoping to have something like -

["select 'abc'", " select 'def'", " select 'annoying semicolon ;'"]

split of ';' should work just fine.
or perhaps "';"

>>> sql = "select 'abc'; select 'def'; select 'annoying semicolon ;'"
>>> sql.split("';")
["select 'abc", " select 'def", " select 'annoying semicolon ;'"]
>>> for n, item in enumerate(newsql):
...     if 'select' in item:
...         newitem = f"{item.strip()}';"
...     newsql[n] = newitem
... 
>>> newsql
["select 'abc';", "select 'def';", "select 'annoying semicolon ;'';"]
>>>

almost right

@Larz60+ Your solution works perfectly for the example I gave - but not in general. Consider the following 3 statements-

>>> sql = "select 123; select 'def'; select 'annoying semicolon ;'"
>>> sql.split("';")
["select 123; select 'def", " select 'annoying semicolon ;'"]
>>> 

After the split there are only 2 items in the list