1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
| import xlrd
import MySQLdb
import re
# Open the workbook and define the worksheet
book= xlrd.open_workbook("testdata1.xls")
sheet = book.sheet_by_name("testdata1")
# Establish a MySQL connection
database = MySQLdb.connect (host="localhost", user = "username", passwd = "password", db = "database_name")
# Get the cursor, which is used to traverse the database, line by line
cursor = database.cursor()
# Create the INSERT INTO sql query
query = """INSERT INTO testdata VALUES (%s,%s,%s)"""
# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers
for r in range(1, sheet.nrows):
#first remove the numbers
state=re.sub("\d+","",sheet.cell(r,0).value)
#then remove the last two remaining brackets
state=state[:-2]
village=re.sub("\d+","",sheet.cell(r,4).value)
village=village[:-2]
habitation=re.sub("\d+","",sheet.cell(r,5).value)
habitation=habitation[:-2]
# Assign values from each row
values =(state,district,block,panchayat,village,habitation)
# Execute sql Query
try:
cursor.execute(query,values)
except:
pass
# Close the cursor
cursor.close()
# Commit the transaction
database.commit()
# Close the database connection
database.close()
|