· 6 years ago · Jul 23, 2019, 08:54 AM
1import pymysql
2import pandas as pd
3import string
4
5db = pymysql.connect('localhost', 'root', '12345678', 'dbdatabase')
6cursor = db.cursor()
7
8cursor.execute("DROP TABLE IF EXISTS FIRMS")
9
10queryCreateFirmsTable = """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 )"""
25cursor.execute(queryCreateFirmsTable)
26
27
28dataCSV = pd.read_csv("sarawak.csv")
29newDF = dataCSV.dropna()
30
31
32
33vSQLI = "INSERT INTO firms "
34vSQLI = vSQLI + "( REGISTRATION_NUMBER, NAME, DATE_ESTABLISHED, ADDRESS, PHONE_NO, FAX_NO, EMAIL, STATUS, POSTAL_CODE, DISTRICT, STATE ) "
35vSQLI = vSQLI + " VALUES "
36
37
38#Bil,No Daftar,Nama Koperasi,Tkh Daftar,Alamat,No Telefon,No Faks,Email,Status,Poskod,Bandar,Negeri
39vSQL = "('"
40vRegNo = ""
41vName = ""
42vDate = ""
43vAddress = ""
44vPhoneno = ""
45vFaxNo = ""
46vEmail = ""
47vStatus = ""
48vPostalcode = ""
49vDistrict = ""
50vState = ""
51vNo = 1
52
53for ind in newDF.index:
54 vRegNo = str(newDF["No Daftar"][ind]) + "', '"
55 vName = str(newDF["Nama Koperasi"][ind]) + "', '"
56 vDate = str(newDF["Tkh Daftar"][ind]) + "', '"
57 vAddress = str(newDF["Alamat"][ind]) + "', '"
58 vPhoneno = str(newDF["No Telefon"][ind]) + "', '"
59 vFaxNo = str(newDF["No Faks"][ind]) + "', '"
60 vEmail = str(newDF["Email"][ind]) + "', '"
61 vStatus = str(newDF["Status"][ind]) + "', '"
62 vPostalcode = str(newDF["Poskod"][ind]) + "', '"
63 vDistrict = str(newDF["Bandar"][ind]) + "', '"
64 vState = str(newDF["Negeri"][ind]) + "' )"
65 vSQL= vSQL + vRegNo + vName + vDate + vAddress + vPhoneno + vFaxNo + vEmail + vStatus + vPostalcode + vDistrict + vState
66 print(vNo)
67 vNo = vNo + 1
68 cursor.execute(vSQLI + vSQL)
69 db.commit()
70 vSQL = "('"
71 vRegNo = ""
72 vName = ""
73 vDate = ""
74 vAddress = ""
75 vPhoneno = ""
76 vFaxNo = ""
77 vEmail = ""
78 vStatus = ""
79 vPostalcode = ""
80 vDistrict = ""
81 vState = ""
82
83
84print("Import Succesful!!")