how to execute .sql file using pyodbc connection

Question

I have a folder which contains SQL files. I want to execute all these SQL files using a pyodbc connection.

I tried different ways but I am still facing some issues. My code looks like below.

import os
import pyodbc

conn =  pyodbc.connect('Driver=ODBC Driver 17 for SQL Server;'
                      'Server=SIS10647\MSSQLSERVER14;'
                      'Database=LeelaVenkatesh;'
                      'Trusted_Connection=yes;')


inputdir = 'C:\SCM\pyodbc'

for script in os.listdir(inputdir):
    with open(inputdir+'\\' + script,'r') as inserts:
        sqlScript = inserts.readlines()
        for statement in sqlScript.split(';'):
            with conn.cursor() as cur:
                cur.execute(statement)
    print(script)

conn.close()

The error I am getting as follows

AttributeError: 'list' object has no attribute 'split'

Tim Roberts · Accepted Answer · 2020-12-09 06:32:29Z

4

Don't use readlines, use read. read brings in the whole file as one string, so you can split it on the semicolons. readlines splits the file into lines, and returns a list of lines.

answered Dec 9, 2020 at 6:32

Tim Roberts

55.3k4 gold badges29 silver badges41 bronze badges

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

1 Comment

Chanukya Over a year ago

Thank you for the answer it works for me :)

Collectives™ on Stack Overflow

how to execute .sql file using pyodbc connection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related