JuRxeE17

1import pymysql
2import pandas as pd
3import string 
4
5db = pymysql.connect('localhost', 'root', '12345678', 'dbdatabase')
6cursor = db.cursor()
7
8#cursor.execute("DROP TABLE IF EXISTS FIRMS")
9
10#queryCreateFirmsTable = """CREATE TABLE FIRMS(
11#                       ID int  AUTO_INCREMENT,
12#                       REGISTRATION_NUMBER varchar(50) NOT NULL,
13#                       NAME varchar(255) NOT NULL,
14#                       DATE_ESTABLISHED varchar(255) NOT NULL,
15#                       ADDRESS varchar(255) NOT NULL,
16#                       PHONE_NO varchar(255) NOT NULL,
17#                       FAX_NO varchar(255) NOT NULL,
18#                       EMAIL varchar(255) NOT NULL,
19#                       STATUS varchar(255) NOT NULL,
20#                       POSTAL_CODE int,
21#                       DISTRICT varchar(255) NOT NULL,
22#                       STATE varchar(255) NOT NULL,
23#                       PRIMARY KEY(ID)
24#                        )"""
25#cursor.execute(queryCreateFirmsTable)
26
27
28#f = open("sarawak.csv", "r")
29#fString = f.read()
30#print(fString)
31
32
33#df = pd.read_table("sarawak.csv", sep=",")
34#newDF = df.dropna()
35#print(newDF)
36
37
38
39
40
41
42
43
44
45dataCSV = pd.read_csv("sarawak.csv")
46newDF = dataCSV.dropna()
47#newDF.drop(newDF.columns[[0]], axis=1, inplace=True)
48
49
50for data in newDF:
51    print(data)
52
53
54#vSQLI = "INSERT INTO firms "
55#vSQLI = vSQLI + "( REGISTRATION_NUMBER, NAME, DATE_ESTABLISHED, ADDRESS, PHONE_NO, FAX_NO, EMAIL, STATUS, POSTAL_CODE, DISTRICT, STATE ) "
56#vSQLI = vSQLI + " VALUES ( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )"
57
58
59vSQLI = "INSERT INTO firms "
60vSQLI = vSQLI + "( REGISTRATION_NUMBER, NAME, DATE_ESTABLISHED, ADDRESS ) "
61vSQLI = vSQLI + " VALUES "
62
63
64#Bil,No Daftar,Nama Koperasi,Tkh Daftar,Alamat,No Telefon,No Faks,Email,Status,Poskod,Bandar,Negeri
65vSQL  = "('"
66a = ""
67b = ""
68c = ""
69d = ""
70
71for ind in newDF.index:
72    a = str(newDF["No Daftar"][ind]) + "', '"
73    b = str(newDF["Nama Koperasi"][ind]) + "', '"
74    c = str(newDF["Tkh Daftar"][ind]) + "', '"
75    d = str(newDF["Alamat"][ind]) + "' ) "
76    vSQL = vSQL + a + b + c + d
77    cursor.execute(vSQLI + vSQL)
78    db.commit()
79    a = ""
80    b = ""
81    c = ""
82    d = ""
83    vSQL = "('"
84    
85    #vSQL = vSQL + "\'" + str(newDF["No Daftar"][ind]) + "\')"
86    #vSQL = vSQL + "\"" + str(newDF["Nama Koperasi"][ind]) + "\","
87    #vSQL = vSQL + "\"" + str(newDF["Tkh Daftar"][ind]) + "\","
88    #vSQL = vSQL + "\"" + str(newDF["Alamat"].str.replace(",","")[ind]) + "\","
89    #vSQL = vSQL + "\"" + str(newDF["No Telefon"][ind]) + "\","
90    #vSQL = vSQL + "\"" + str(newDF["No Faks"][ind]) + "\","
91    #vSQL = vSQL + "\"" + str(newDF["Email"][ind]) + "\","
92    #vSQL = vSQL + "\"" + str(newDF["Status"][ind]) + "\","
93    #vSQL = vSQL + "\"" + str(newDF["Poskod"][ind]) + "\","
94    #vSQL = vSQL + "\"" + str(newDF["Bandar"][ind]) + "\","
95    #vSQL = vSQL + "\"" + str(newDF["Negeri"][ind]) + "\")"
96    
97
98    
99    #print(vSQLI + vSQL)
100   print("Done!")
101    
102    
103    
104    
105#vSQL = vSQL[0:len(vSQL)-2]
106#print(vSQL)
107#cursor.execute(vSQL)