Python Forum
Reading SQL scripts from excel file and run it using python
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Reading SQL scripts from excel file and run it using python
#1
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)
Reply
#2
Tell us what error you get and how you merged both codes. Perhaps we can then help you.
Reply
#3
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)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  is it possible to copy image from email and place into excel file? cubangt 3 212 Nov-30-2022, 05:11 PM
Last Post: snippsat
  Replace columns indexes reading a XSLX file Larry1888 2 268 Nov-18-2022, 10:16 PM
Last Post: Pedroski55
  Appending a row of data in an MS Excel file azizrasul 3 294 Nov-06-2022, 05:17 PM
Last Post: azizrasul
  Creating csv files from Excel file azizrasul 40 1,076 Nov-03-2022, 08:33 PM
Last Post: azizrasul
  Convert Excel file into csv with Pipe symbol.. mg24 4 317 Oct-18-2022, 02:59 PM
Last Post: Larz60+
  Changing the initial worksheet name in an MS Excel file azizrasul 3 263 Oct-02-2022, 07:56 PM
Last Post: azizrasul
  Failing reading a file and cannot exit it... tester_V 8 539 Aug-19-2022, 10:27 PM
Last Post: tester_V
  Excel file in use from Python Mark17 7 808 Jul-04-2022, 02:06 PM
Last Post: Mark17
  How to keep columns header on excel without change after export data to excel file? ahmedbarbary 0 460 May-03-2022, 05:46 PM
Last Post: ahmedbarbary
  Reading .csv file doug2019 4 715 Apr-29-2022, 09:55 PM
Last Post: deanhystad

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020