Read in and execute multiline SQL text file into Python program

Question

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)

Tim Biegeleisen · Accepted Answer · 2021-02-24 15:30:52Z

1

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

answered Feb 24, 2021 at 15:30

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Read in and execute multiline SQL text file into Python program

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related