Parsing two SQL statements using split , the result is incorrect

Question

>>> import sqlparse
>>> t_sql = """create table dmc_o2_test(
...     stuid number(38) primary key,
...     stuname varchar2(30) not null
... )
...
... select * from dmc_o2_test;"""
>>>
>>>
>>> sqlparse.split(t_sql)
['create table dmc_o2_test(\n\tstuid number(38) primary key,\n  \tstuname varchar2(30) not null\n)\n\nselect * from dmc_o2_test;']
>>>
>>> len(sqlparse.split(t_sql ))
1

t_sql is two SQL statements， and there is no semicolon at the end of the first SQL statement。 But the result is considered one statement。

Well, How can I solve this problem that SQL statement without semicolon? Any good suggestions？ THANKS !

Answer 1

you can use sqlglot

import sqlglot

for statement in sqlglot.parse(sql):
    statement.sql()

Answer 2

Try RegEx:

import re

sqlList= re.split('((;\s*\n\s*|;\s*\n|;\n\s*|;\n+)(select|delete|update|insert|create))|(;\s*$)', t_sql , flags=re.I)

sqlList[0] is the "Create" block.

sqlList[1] is the "Select" statement.

(You cab play with the delimiters, but this is common to split SQL statements)