#!/usr/bin/env python
# coding: utf-8

import numpy as np
import pandas as pd
import xlsxwriter
from sqlalchemy import create_engine, event
from urllib.parse import quote_plus
from optparse import OptionParser

from sqlalchemy.engine import create_engine
con =  "DRIVER={SQL Server Native Client 11.0};SERVER=127.0.0.1;DATABASE=GSOD;UID=GSOD;PWD=GSOD!Pass"
Iteration = 3 # Used to increment a file number manually for testing
xlsxoutput = "c:/data/pandas_simple_" + str(Iteration) + ".xlsx"
quoted = quote_plus(con)
new_con = 'mssql+pyodbc:///?odbc_connect={}'.format(quoted)
#engine = create_engine(new_con)
Q = [["SELECT top 10 * from [DBX].[dbo].[MY_RAW_Order_Item_Stats_RAW]","Sheet_10_Items"],
     ["SELECT top 5 * from [DBX].[dbo].[MY_RAW_Order_Item_Stats_RAW]","Sheet_5_Items"],
     ["SELECT top 1 * from [DBX].[dbo].[MY_RAW_Order_Item_Stats_RAW]","Sheet_1_Items"]]

writer = pd.ExcelWriter(xlsxoutput, engine='xlsxwriter')
# Create a Pandas Excel writer using XlsxWriter as the engine.
for Query in Q:
   # worksheet1 = workbook.add_worksheet()
   # worksheet1.Name=Query[1]
    df = pd.read_sql(Query[0],new_con)
    # Convert the dataframe to an XlsxWriter Excel object.
    df.to_excel(writer, sheet_name=Query[1])

# Close the Pandas Excel writer and output the Excel file.
writer.save()