0

I have multiple .sql files that have DROP IF EXISTS and CREATE TABLE statements that I want to execute automatically without having to click on them through python. I'm getting errors using this script:

import os
import fnmatch

for root, dirnames, filenames in os.walk("C:/Users/user/Desktop/Generated"):
  for filename in fnmatch.filter(filenames, '*.sql'):
    exec(filename)

The .sql files each look something like this

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tab1]') AND type in (N'U'))

BEGIN

    PRINT 'DROPPING TABLE [dbo].[tab1]....'

    DROP TABLE [dbo].[tab1]; 

END; 



CREATE TABLE [tab1] (  [a] nvarchar(5)  NOT NULL,
  [b] nvarchar(8)  NOT NULL,
  [c] nvarchar(2)  NULL,
  [d] nvarchar(400)  NULL,
  [e] int  NULL,
  [f] int  NULL,
  [g] real  NULL,
  [h] bit  NULL,
  [i] bit  NULL,
  [j] int  NULL,
  [k] nvarchar(2)  NULL,
  [l] bit  NULL,
)ALTER TABLE tab1 ADD CONSTRAINT k PRIMARY KEY NONCLUSTERED  ([f], [g])

Any suggestions or tips would be greatly appreciated!

moni
  • 3
  • 4

2 Answers2

0

Check out this answer. You'll have to use a library to actually execute the file.

  • Read the .sql file
  • Execute the text read from the file.
sql = filename.read() % params # Don't do that with untrusted inputs
            cursor.execute(sql)
            cursor.commit()
            cursor.close()
MiConnell
  • 46
  • 2
0

This should help.

from your_databse_driver import Client as cl

client = cl(connection_details like por, password etc)


for root, dirnames, filenames in os.walk("C:/Users/user/Desktop/Generated"):
        for filename in fnmatch.filter(filenames, '*.sql'):
                sql = open(root+ "/"+ filename,'r')
                sql = sql.read()
                try:
                        result = client.execute(sql)
                        print("successfully executed!")
                except:
                       print("problem executing!")
anadi pandharkar
  • 21
  • 3