CODEWITHSUNDEEP
pythonpython-3.xpyodbcpysqlite
Chanukya
Chanukya

Reputation: 5893

how to execute .sql file using pyodbc connection

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'

Upvotes: 2

Views: 4525

Answers (1)

Tim Roberts
Tim Roberts

Reputation: 54757

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.

Upvotes: 4

Related Questions