import pymysql
import pandas as pd
import string

db = pymysql.connect('localhost', 'root', '12345678', 'dbdatabase')
cursor = db.cursor()

#cursor.execute("DROP TABLE IF EXISTS FIRMS")

#queryCreateFirmsTable = """CREATE TABLE FIRMS(
#                       ID int  AUTO_INCREMENT,
#                       REGISTRATION_NUMBER varchar(50) NOT NULL,
#                       NAME varchar(255) NOT NULL,
#                       DATE_ESTABLISHED varchar(255) NOT NULL,
#                       ADDRESS varchar(255) NOT NULL,
#                       PHONE_NO varchar(255) NOT NULL,
#                       FAX_NO varchar(255) NOT NULL,
#                       EMAIL varchar(255) NOT NULL,
#                       STATUS varchar(255) NOT NULL,
#                       POSTAL_CODE int,
#                       DISTRICT varchar(255) NOT NULL,
#                       STATE varchar(255) NOT NULL,
#                       PRIMARY KEY(ID)
#                        )"""
#cursor.execute(queryCreateFirmsTable)


#f = open("sarawak.csv", "r")
#fString = f.read()
#print(fString)


#df = pd.read_table("sarawak.csv", sep=",")
#newDF = df.dropna()
#print(newDF)









dataCSV = pd.read_csv("sarawak.csv")
newDF = dataCSV.dropna()
#newDF.drop(newDF.columns[[0]], axis=1, inplace=True)


for data in newDF:
    print(data)


#vSQLI = "INSERT INTO firms "
#vSQLI = vSQLI + "( REGISTRATION_NUMBER, NAME, DATE_ESTABLISHED, ADDRESS, PHONE_NO, FAX_NO, EMAIL, STATUS, POSTAL_CODE, DISTRICT, STATE ) "
#vSQLI = vSQLI + " VALUES ( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )"


vSQLI = "INSERT INTO firms "
vSQLI = vSQLI + "( REGISTRATION_NUMBER, NAME, DATE_ESTABLISHED, ADDRESS ) "
vSQLI = vSQLI + " VALUES "


#Bil,No Daftar,Nama Koperasi,Tkh Daftar,Alamat,No Telefon,No Faks,Email,Status,Poskod,Bandar,Negeri
vSQL  = "('"
a = ""
b = ""
c = ""
d = ""

for ind in newDF.index:
    a = str(newDF["No Daftar"][ind]) + "', '"
    b = str(newDF["Nama Koperasi"][ind]) + "', '"
    c = str(newDF["Tkh Daftar"][ind]) + "', '"
    d = str(newDF["Alamat"][ind]) + "' ) "
    vSQL = vSQL + a + b + c + d
    cursor.execute(vSQLI + vSQL)
    db.commit()
    a = ""
    b = ""
    c = ""
    d = ""
    vSQL = "('"

    #vSQL = vSQL + "\'" + str(newDF["No Daftar"][ind]) + "\')"
    #vSQL = vSQL + "\"" + str(newDF["Nama Koperasi"][ind]) + "\","
    #vSQL = vSQL + "\"" + str(newDF["Tkh Daftar"][ind]) + "\","
    #vSQL = vSQL + "\"" + str(newDF["Alamat"].str.replace(",","")[ind]) + "\","
    #vSQL = vSQL + "\"" + str(newDF["No Telefon"][ind]) + "\","
    #vSQL = vSQL + "\"" + str(newDF["No Faks"][ind]) + "\","
    #vSQL = vSQL + "\"" + str(newDF["Email"][ind]) + "\","
    #vSQL = vSQL + "\"" + str(newDF["Status"][ind]) + "\","
    #vSQL = vSQL + "\"" + str(newDF["Poskod"][ind]) + "\","
    #vSQL = vSQL + "\"" + str(newDF["Bandar"][ind]) + "\","
    #vSQL = vSQL + "\"" + str(newDF["Negeri"][ind]) + "\")"



    #print(vSQLI + vSQL)
   print("Done!")




#vSQL = vSQL[0:len(vSQL)-2]
#print(vSQL)
#cursor.execute(vSQL)