How can I parse sql file from within Python?

Question

Is there any way to execute some SQL commands in .sql file from within Python, but not all SQL commands in the file? Suppose I have the following .sql file:

DROP TABLE IF EXISTS `tableA`;

CREATE TABLE `tableA`(
some_code
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `tableB`;

CREATE TABLE `tableB`(
some_code
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `tableC`;

CREATE TABLE `tableC`(
some_code
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

...to be continued...

In this file, I want to parse and run only tableB-related command (i.e. drop and create tableB), but don't like to execute any SQL commands on other tables from within Python. I have some knowledge on how to execute .sql file from within Python, but don't know how to execute only some specific commands in .sql file as stated in the above example. The first thing striking upon my head is to use regular expression. But after a little bit of wrangling, I couldn't come up with the correct regex syntax to get me what I expected due to my poor regex knowledge and experience.

So my question is,

1) Is it the correct way here to use regular expression to get only the desired commands, and if so, can you show me the correct syntax to parse it?

2) If regular expression is not the best way here, what is an alternative solution?

3) I found some online regex testing tools, but all of them is to specify both the expression and test strings, and highlight matched data in the string. I believe it's great if there are some tools that have me specify test strings first, then highlight the desired data within the string manually, and then returns some appropriate syntax/expression adversely. If you know such tools (no restriction to online tools! I'm also glad if it's Macintosh Application), please tell me...

Thanks.

Answer 1

You can try out the sqlparse library that will ease your work by parsing SQL statements and give you the capapbility to query and work with tokens within a SQL statement. It can be a goos base to filter out statements containing a specific token like tableB in your case

Answer 2

While regex may not be the right tool, you can still use it.

>>> statements = """
... DROP TABLE IF EXISTS `tableA`;
...
... CREATE TABLE `tableA`(
... some_code
... ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
...
... DROP TABLE IF EXISTS `tableB`;
...
... CREATE TABLE `tableB`(
... some_code
... ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
...
... DROP TABLE IF EXISTS `tableC`;
...
... CREATE TABLE `tableC`(
... some_code
... ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
... """
>>> regex = r"((?:CREATE|DROP) TABLE (?:IF (?:NOT )?EXISTS )?`tableB`(?:[^;]|(?:'.*?'))*;)"
>>> re.findall(regex, statements, re.I)
['DROP TABLE IF EXISTS `tableB`;', 'CREATE TABLE `tableB`(\nsome_code\n) ENGINE=MyISAM DEFAULT CHARSET=latin1;']
>>>

If you are wondering what

`(?:[^;]|(?:'.*?'))*`

is for, it is simply used to match any character except ;, any amount of times, including none

or

a string literal, meaning it will allow ; to match inside a string like 'this is a ;value; for a varchar field'.

Answer 3

Although I personally believe you should use some parsing library to parse an AST of the SQL, looking through the code makes this option viable too:

my_sql_code = '''DROP TABLE...''' #big long string, multiline
statements = my_sql_code.split(';')
statements = [s for s in statements if 'tableB' in s]
for s in statements:
    execute_sql(s)