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'

Answer 1

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.