Python USES SQLite and Excel operations for data analysis

Yesterday, female ticket took an Excel document, which contained tens of thousands of data to be analyzed. At the beginning, Excel used the first field analysis well and could be done, but later, the analysis of two fields, as well as the data analysis of the interval such as the age group, was too painful to do, so she wanted to process it for her.

, of course, I am a straight C + + 1 and Qt, then want to Qt + sqlite3 directly written to the database, and then all kinds of data query, can be found to do it, she didn't Qt machine environment, no C + + compiler, too, if the configuration environment also get a few hours, but don't have much time to do at that time, fortunately, had also seen something Python, 1 and Python environment good match, so he want to write in Python realize a database, query functions. So, do it.

Environment configuration

1, download Python: http: / / www python. org downloads /;
2, read library: download Excel http: / / pypi python. org/pypi/xlrd;
3, Sqlite3 database: This is Python come with, but do not need to download.

Write the code

The code is simple, 1 class, initializes the database object at constructor time, and releases the database object at destructor time. 1 insert data function, 1 read Excel function, without further ado, above code:

import sqlite3
import xlrd

class FileDispose(object):
  """docstring for FileDispose"""
  def __init__(self, file):
    super(FileDispose, self).__init__()
    ''' Initializes the database instance '''
    self.conn = sqlite3.connect(file)
    self.cursor = self.conn.cursor()

  def __del__(self):
    ''' Release the database instance '''
    self.cursor.close()
    self.conn.close()

  ''' Database insert operation '''
  def insert(self,id,name,sex,age,score,addr):
    sql = 'insert into student(id,name,sex,age,score,addr) values (%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\")' % (int(id),name,sex,age,score,addr)
    print(sql)
    self.cursor.execute(sql)
    self.conn.commit()

  ''' read Excel file '''
  def readFile(self, file):
    data = xlrd.open_workbook(file)
    table = data.sheets()[2]
    for rowId in range(1, 100):
      row = table.row_values(rowId)
      if row:
        self.insert(rowId,row[0],row[1],row[2],row[3],row[4])


fd = FileDispose("F:/test.db")
fd.readFile('F:/excel.xlsx')

Is directly take SQLiteSpy I create database tables, fields have id, name, sex, age, score, addr this a few.

conclusion

The above is all about the data analysis of Python using SQLite and Excel operations, I hope it will be helpful to you. Interested friends can continue to refer to other related topics in this site, if there is any deficiency, welcome to comment out. Thank you for your support!