CODEWITHSUNDEEP
pythonlisttextsplit
JD2775
JD2775

Reputation: 3801

Read in and execute multiline SQL text file into Python program

I have a create_table.txt file that looks like this:

DROP TABLE TABLE_A;
CREATE TABLE TABLE_A (
ID VARCHAR(100),
NAME VARCHAR(100),
ADDRESS VARCHAR(100)
);
ALTER TABLE TABLE_A ADD CONSTRAINT PK_TABLE_A(ID);
...

I want to read in that file into a list, separated by semi-colon, to then be iterated over and executed. If these all existed on one line I could just do a line.split() but the new lines are throwing me off.

If it would be easier to change it to a .sql file I could do that too.

I have tried:

list1 = []
with open('create_table.txt', 'r') as f:
    for line in f:
        line.split(';')
        list1.append(line)

Upvotes: 0

Views: 770

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

One approach uses re.findall with dot all mode enabled:

inp = '''DROP TABLE TABLE_A;
CREATE TABLE TABLE_A (
ID VARCHAR(100),
NAME VARCHAR(100),
ADDRESS VARCHAR(100)
);
ALTER TABLE TABLE_A ADD CONSTRAINT PK_TABLE_A(ID);'''

sql = re.findall(r'(.*?;)\s*', inp, flags=re.DOTALL)
print(sql)

This prints:

['DROP TABLE TABLE_A;',
 'CREATE TABLE TABLE_A (\nID VARCHAR(100),\nNAME VARCHAR(100),\nADDRESS VARCHAR(100)\n);',
 'ALTER TABLE TABLE_A ADD CONSTRAINT PK_TABLE_A(ID);']

Upvotes: 1

Related Questions