+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Reading SQL scripts from excel file and run it using python (/thread-29203.html)
Reading SQL scripts from excel file and run it using python - saravanatn - Aug-22-2020
Hi All,
I placed my SQL scripts in excel file and my python code needs to read the SQL code from excel and then run the SQL code from SQL Server using Python . If the fetch row is greater than zero then it is 'Failed' else 'Passed'. This failed or Passed results needs to be writes into Excel file as output.
What I tried do far . I am able to run the SQL scripts directly
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
'Server=ServerName;'
'Database=DatabaseName;'
'Trusted_Connection=yes;')
cursor = conn.cursor()
cursor.execute('SELECT * FROM [DUMMY]')
title = [i[0] for i in cursor.description]
print (title)
for row in cursor:
print(row)I am able to read the excel files separately . When I merge both the code I am getting error from SQL code part import xlrd file_location= "pathname" workbook =xlrd.open_workbook(file_location) sheet =workbook.sheet_by_index(0) sheet.cell_value(0,0)
RE: Reading SQL scripts from excel file and run it using python - ibreeden - Aug-23-2020
Tell us what error you get and how you merged both codes. Perhaps we can then help you.
RE: Reading SQL scripts from excel file and run it using python - saravanatn - Aug-23-2020
Thanks Ibreedan
Now python code is working fine. But 2 problem's
1) I am not getting column header name when I run SQL scripts through Python.
2) How to auto increment next row in excel file. E.g If my Cell value is (0,0) and if I want to read the SQL script available in Cell value (1,0) . How to do it ?
import xlrd
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
'Server=Servername'
'Database=PythonSQL;'
'Trusted_Connection=yes;')
file_location= "E:/Python Automation/file.xls"
workbook =xlrd.open_workbook(file_location)
sheet =workbook.sheet_by_index(0)
query=sheet.cell_value(0,0)
cursor = conn.cursor()
cursor.execute(query)
for row in cursor:
print(row)