Python uses SQLite and Excel for data analysis.

Source: Internet
Author: User

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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.