Python uses SQLite and Excel for data analysis.
Yesterday, female received an Excel document containing tens of thousands of pieces of data for analysis. At the beginning of a field analysis, Excel was well used and can be done, after the analysis of the two fields and the analysis of data with intervals such as age groups, it was really bad, so I wanted to process it for her program.
Of course, I have always been C ++ and Qt. At that time, I wanted to write Qt + sqlite3 into the database directly, and then I could query all kinds of data. However, I found that, there is no Qt environment on her machine, and no C ++ compiler. if the environment is configured for a few hours, there is not much time to do it. Fortunately, I have read some Python stuff before, and the Python environment is well-configured. So I want to use Python to implement a database writing and query function. So, action.
Environment Configuration
1. Download Python: http://www.python.org/downloads /;
2. Download the Excel database: http://pypi.python.org/pypi/xlrd;
3. Sqlite3 Database: Python-provided database does not need to be downloaded.
Write code
Code writing is very simple. A class initializes database objects during the constructor and releases database objects during the analysis. Insert a data function and read an Excel function:
Import sqlite3import xlrdclass FileDispose (object): "docstring for FileDispose" "def _ init _ (self, file): super (FileDispose, self ). _ init _ () ''' initialize the database instance ''' self. conn = sqlite3.connect (file) self. cursor = self. conn. cursor () def _ del _ (self): ''' release 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 \") '% (int (id), name, sex, age, score, addr) print (SQL) self.cursor.exe cute (SQL) self. conn. commit () ''' read the 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 ')
The database table is created by using SQLiteSpy. The fields include id, name, sex, age, score, and addr.
Summary
The above is all about Data Analysis Using SQLite and Excel in Python. I hope it will be helpful to you. If you are interested, you can continue to refer to other related topics on this site. If you have any shortcomings, please leave a message. Thank you for your support!