When I was doing a project analysis recently, I saw a data file with the suffix ".sqlite". Since I had not come into contact with it much before, I was thinking about how to use python to open it and perform data analysis and processing. , so I did a little research.

SQLite is a very popular relational database that is used by a large number of applications because it is very lightweight.

Like csv files, SQLite can store data in a single data file for easy sharing with other people. Many programming languages ​​support the processing of SQLite data, and the python language is no exception.

sqlite3 is a standard library of python that can be used to process SQLite databases.

Use sqlite3 to create and operate database files

For the SQL statements of the database, this article will use the most basic SQL statements, which should not affect reading. If you want to know more, you can refer to the following URL:

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

1. Establish a connection with the database and create a database file (.sqlite file)

2. Create a cursor

3. Create a data table (table)

4. Insert data into the data table

5. Query data

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)]

Regarding the visual preview of data in the SQLite database, there are many tools that can be used. What I use here is SQLite Studio, which is a free tool. , no installation is required, just download it and use it. Interested students can refer to the link below.

https://sqlitestudio.pl/index.rvt?act=download

The effect of data preview is as follows:

Use pandas to read sqlite data files

As can be seen from the running results of the above code, the result of the data query is a list composed of tuples. Python's list data may be inconvenient for further data processing and analysis. You can imagine that if there are 1 million or more rows of data in the database table, the efficiency of looping through the list to obtain the data will be relatively low.

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

Pandas provides two functions, both of which can read information from data files with the suffix ".sqlite".

• read_sql()

• read_sql_query()

import pandas as pdwith sqlite3.connect('test_database.sqlite') as con:# read_sql_query和read_sql都能通过SQL语句从数据库文件中获取数据信息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())

<code style="font-size: 14px; font-family: Roboto, 'Courier New', Consolas, Inconsolata, Courier, monospace; margin: auto 5px; white-space: pre; border-radius: 3px; display: block !important; overflow: auto; padding: 1px;">(2, 3)
date      object
city      object
value    float64
dtype: object
        date        city  value
0  2017-6-25          bj  100.0
1  2017-6-25  pydataroad  150.0<br></code>