Reputation: 3801
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
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