Python parses the excel file into the sqlite database

1. Build a database

According to the requirements, the database is established, two tables are established, and the data can be stored in the existing database. The code is as follows:

import sqlite3

def createDataBase():
cn = sqlite3.connect('check.db')

cn.execute('''CREATE TABLE IF NOT EXISTS TB_CHECK
(ID integer PRIMARY KEY AUTOINCREMENT,
NUMBER INTEGER,
ITEM TEXT,
REFERENCE TEXT,
SUMMARY TEXT,
OBJECT TEXT,
METHOD TEXT,
CONDITION TEXT,
VALUE TEXT,
RESULT TEXT,
SCORE TEXT,
REMARKS TEXT,
PROVINCE TEXT,
TIME TEXT);''')

cn.execute('''CREATE TABLE IF NOT EXISTS TB_SCORE
(ID integer PRIMARY KEY AUTOINCREMENT,
PROVINCE TEXT,
TIME TEXT,
FILETYPE TEXT,
SCORE INTEGER);''')

if __name__ == '__main__':
createDataBase()

2. Parse excel using Python

The xlrd module in Python is used to parse excel.

Relevant functions are introduced as follows:

1. The import

import xlrd

2. Read data

data = xlrd.open_workbook('file.xls')

Function of 3.

(1) through the index

table = data.sheet()[0]
table = data.sheet_by_index(0)

(2) get by name

table = data.sheet_by_name(u'sheet1')

(3) get the value of the whole row and the whole column (array)

table.row_values(i)
table.col_values(i)

(4) get the number of rows and columns

nrows = table.nrows
ncols = table.ncols

(5) circular row and row data

for i in range(nrows):
print table.row_values(i)

(6) cell

cell_A1 = table.cell(0,0).value

(7) use row and column indexes

cell_A1 = table.cell(0,0).value

Practice code:

import xlrd

0

3. Python reads the file name and parses it

In order to distinguish the data of each file, the signature field in the file name needs to be stored. The code to parse the file is as follows:

import xlrd

1

Coding problems will be encountered in the following use. Therefore, transcoding is required when using these fields. Transcoding functions are written as follows:

import xlrd

2

4. Parse the excel file and save it to sqlite

Python connected to the database selected Python's own sqlite database is relatively simple here without much introduction if you have doubts about Python operation sqlite personal recommended beginners tutorial ~

Here is the excel file parsed and stored in the database, which contains the contents of the judging cell:

def readExcel(filename,cn,check_province,check_time,FileType):
 # read 
 workbook = xlrd.open_workbook(filename)
 #  To obtain sheet
 sheet_name = workbook.sheet_names()[0]
 　　sheet = workbook.sheet_by_name(sheet_name)

 check_Item = 'a'

 itemCount = 0
 score = 0

 second = sheet.cell(7,1).value.encode('utf-8')

 for i in range(7,sheet.nrows):
  if sheet.cell(i,1).value.encode('utf-8') == second:
   check_Item = sheet.cell(i,0).value.encode('utf-8')
   continue

  temp = []
  for j in range(0,sheet.ncols):
   temp.append(sheet.cell(i,j).value.encode('utf-8'))

  answer = sheet.cell(i,7).value.encode('utf-8')

  if answer == "yes" or answer == "no":
   score = score + 1

  if answer == "other":
   print "!!!Failed to import'%s'" % (filename)
   print "!!!Please Choose an Right Answer for '%s'--------"%(filename)
   break
  else:
   cn.execute("insert into TB_CHECK (ITEM,FIELD,TYPE,CONTENT,"
      "ATTRIBUTE,CHECKPOINT,REMARKS,ANSWER,DESCRIPTION,"
      "SUGGESTION,PROVINCE,TIME,STYLE) "
      "values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"
      ""%(temp[0],temp[1],temp[2],temp[3],temp[4],temp[5],temp[6],temp[7],temp[8],temp[9],check_province,check_time,check_Item))

   itemCount = itemCount + 1
 if itemCount != 0:
  score = round(score * (100 / itemCount), 2)
  cn.execute("insert into TB_SCORE (PROVINCE,TIME,FILETYPE,SCORE) "
    "values('%s','%s','%s','%.2f')"%(check_province,check_time,FileType,score))
  print "Successful for'%s'--------" % (filename)
 cn.commit()

Integrate the above functions:

import xlrd

4

conclusion

The above is the whole content of this article, I hope the content of this article can help you to learn or use python, if you have any questions, you can leave a message to communicate.