CODEWITHSUNDEEP
pythonsqlexcelcsvsnowflake-cloud-data-platform
sh1704
sh1704

Reputation: 69

Python code to execute a SQL script and create a CSV file with a customised name

I have stored a list of snowflake SQL queries in an excel. Attached a sample format but the original excel has more than 50 rows. enter image description here

I have written a python code to read and execute these queries but the output is not as i was expecting. The intention here is to read the excel and execute the query from the 'query' column and save the query result in an CSV with the file name mentioned in the 'Scenario' column from the excel.

The python code i have written executes and creates different files in the output path but all the CSV file is having the result of last query, i.e the customer_count.csv file also has the same data of customer_attribute.csv

If my excel has 50 different scenarios, it creates 50 different CSV files but the data inside all these files are the result of the 50th Query

Below is my code and i'm not sure where its going wrong. Could someone help me out here please

  from EXCEL_CONNECTION import *
  from SNOWFLAKE_CONNECTION import *
  import pandas
  import openpyxl

  wb = openpyxl.load_workbook("QUERY.xlsx")
  ws = wb.get_sheet_by_name('Sheet1')

  cur = ctx.cursor()

  for col in ws.iter_rows(min_row=2, min_col=2, max_col=2):
      for cell2 in col:
          df1 = (cell2.value)
      for row in ws.iter_rows(min_row=2, min_col=3, max_col=3):
          for cell in row:
              cur.execute(cell.value)
              df = cur.fetch_pandas_all()
      df.to_csv(r"target path" + df1 + r".csv")

Upvotes: 1

Views: 786

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

I cannot spot the problem by eye. So I would do the next best thing... add some debugging and see what is actually happening:

  for col in ws.iter_rows(min_row=2, min_col=2, max_col=2):
      print('col', col)
      for cell2 in col:
          print('cell2', cell2.value)
          df1 = (cell2.value)
      for row in ws.iter_rows(min_row=2, min_col=3, max_col=3):
          print('row', row)
          for cell in row:
              print('cell', cell.value)
              cur.execute(cell.value)
              df = cur.fetch_pandas_all()
      print('to_csv', r"target path" + df1 + r".csv")
      df.to_csv(r"target path" + df1 + r".csv")

I am puzzled why you are put the name of the file into a tuple (cell2.value) instead of using it directly. The SQL cell is used directly?

But you will ether see you N execute's with different files names, and then it will be a "are the results actually different" OR you we see that while you are loop X you are not step over Y, or you are doing every X for each Y (which sounds like the output you are getting).

And when you know exactly what is happening, you can ask a more targeted question. simplified repo like:

from EXCEL_CONNECTION import *
from SNOWFLAKE_CONNECTION import *

cur = ctx.cursor()
cur.execute('select count(*) from customer')
df = cur.fetch_pandas_all()
df.to_csv(r"target path\customer_count.csv")

cur.execute('select * from customer')
df = cur.fetch_pandas_all()
df.to_csv(r"target path\customer_attribute.csv")

Upvotes: 1

Related Questions