Python reads and processes data files suffixed with & quot;. sqlite & quot,

Python reads and processes data files suffixed with ". sqlite,

Recently, when I got a project analysis, I saw a suffix ". since I have never touched on sqlite data files before, I thought about how to use python to open and perform data analysis and processing. So I did a little research.

SQLite is a very popular relational database, which is used by a large number of applications due to its lightweight nature.

Like csv files, SQLite can store data in a single data file to facilitate sharing with others. Many programming languages Support SQLite data processing, and python is no exception.

Sqlite3 is a standard library of python and can be used to process SQLite databases.

Use sqlite3 to create and operate database files

For database SQL statements, this article will use the most basic SQL statements, should not affect reading. For more information, see the following URL:
Http://www.w3school.com.cn/ SQL /index.asp

Next, we will apply the salite3 module to create SQLite data files and perform data read/write operations. The main steps are as follows:

The Demo code is as follows:

import sqlite3with sqlite3.connect('test_database.sqlite') as con:    c = con.cursor()    c.execute('''CREATE TABLE test_table    (date text, city text, value real)''')    for table in c.execute("SELECT name FROM sqlite_master WHERE type='table'"):        print("Table", table[0])    c.execute('''INSERT INTO test_table VALUES    ('2017-6-25', 'bj', 100)''')    c.execute('''INSERT INTO test_table VALUES    ('2017-6-25', 'pydataroad', 150)''')    c.execute("SELECT * FROM test_table")    print(c.fetchall())

Table test_table[('2017-6-25', 'bj', 100.0), ('2017-6-25', 'pydataroad', 150.0)]

You can use many tools to visually preview data in the SQLite database. I am using SQLite Studio, which is a free tool and does not need to be installed, you can download it and use it. If you are interested, refer to the following link.

Https://sqlitestudio.pl/index.rvt? Act = download

The data preview effect is as follows:

Use pandas to read sqlite data files

The running result of the code above shows that the data query result is a list composed of tuple. Python list data may be inconvenient for further data processing and analysis. It can be imagined that if there are 1 million rows or more data in the database table, the efficiency will be relatively low if the data is retrieved from the list cyclically.

At this time, we can consider using the functions provided by pandas to read the relevant data information from the SQLite database file and store it in DataFrame for further processing.

Pandas provides two functions to read the information of data files suffixed with ". sqlite.

• Read_ SQL ()
• Read_ SQL _query ()

Import pandas as pdwith sqlite3.connect ('test _ database. sqlite ') as con: # Both read_ SQL _query and read_ SQL can obtain data information from database files through SQL statements. df = pd. read_ SQL _query ("SELECT * FROM test_table", con = con) # df = pd. read_ SQL ("SELECT * FROM test_table", con = con) print (df. shape) print (df. dtypes) print (df. head ())

(2, 3)date      objectcity      objectvalue    float64dtype: object        date        city  value0  2017-6-25          bj  100.01  2017-6-25  pydataroad  150.0