import pandas as pd
import snowflake.connector
import csv

mfa=input('Enter MFA Passcode: ')
credentials=snowflake.connector.connect(
    user='put_your_username',
    password='put_your_password',
    passcode=mfa,
    account='orchard',
    warehouse='LOOKER_HIGH_PERFORMANCE'
)

cursor=credentials.cursor()

with open('row1.sql','r') as f:
    sql=f.read()

results = cursor.execute(sql).fetchall()

df= pd.DataFrame(results)
df.to_csv('mechanicals_row1.csv', index=False)

with open('row2.sql','r') as f:
    sql=f.read()

results = cursor.execute(sql).fetchall()

df= pd.DataFrame(results)
df.to_csv('mechanicals_row2.csv', index=False)

with open('header.sql','r') as f:
    sql=f.read()

results = cursor.execute(sql).fetchall()

df= pd.DataFrame(results)
df.to_csv('mechanicals_header.csv', index=False)

with open('mechanicals_header.csv','r') as h:
    with open('mechanicals_row1.csv','r') as r1:
        with open('mechanicals_row2.csv','r') as r2:
            with open('headers_columns.csv', 'r') as hc:
                with open('row1_columns.csv','r') as r1c:
                    with open('row2_columns.csv','r') as r2c:
                        
                        headers=list(csv.reader(h))
                        row1=list(csv.reader(r1))
                        row2=list(csv.reader(r2))
                        h_columns=list(csv.reader(hc))
                        r1_columns=list(csv.reader(r1c))
                        r2_columns=list(csv.reader(r2c))
            
                        with open('sap_upload.csv','w') as s:
                            sap = csv.writer(s, delimiter=',')
                        
                            sap.writerow(h_columns[0])
                            sap.writerow(r1_columns[0])
                            sap.writerow(r2_columns[0])
                            
                            index=1

                            for row in headers[1:]:
                                sap.writerow(headers[index])
                                sap.writerow(row1[index])
                                sap.writerow(row2[index])
                                index+=1