This database works just fine

Posted by cockney on Wed, 09 Mar 2022 16:23:45 +0100

My official account is written in Bear Mac App. It wrote on the official website that all note data is stored through SQLite, as shown in the figure below.

SQLite is a file based relational database. It has only one file, but it can store up to 140TB of data [1].

The official website of SQLite provides a standard for judging whether it is suitable to use SQLite:

If the program and data are separated and they are connected through the Internet, SQLite is not suitable
SQLite is not suitable for high concurrency write
If the amount of data is very large, it is not suitable for SQLite
In addition, select SQLite

Linux/macOS systems have their own SQLite database, and it is easy to read and write SQLite using Python's own module. This article demonstrates the addition, deletion, modification, query and associated table query of SQLite.

Create data table

In SQLite, a file is a library. So we just need to create a data table. The method is very simple:

import sqlite3

conn = sqlite3.connect('kingname.db')
with conn:
    conn.execute('''
    CREATE TABLE user (
    name TEXT,
    age INTEGER,
    address TEXT,
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);
    ''')

The operation is shown in the figure below:

In this way, we have created a file called kingname under the current folder DB and created a table called user. The database link object of sqlite3 library supports context manager, so you can execute SQL statements only with conn: without creating cursors. When you exit the indent, the SQL statement is automatically submitted and takes effect.

Write data

Now let's write a few pieces of data:

sql = 'insert into user (id, name, age, address) values(?, ?, ?, ?)'
datas = [
    (1, 'kingname', 20, 'Hangzhou City, Zhejiang Province'),
    (2, 'product manager', 18, 'Shanghai'),
    (3, 'Fat pig', 8, 'Fart Island')
]
with conn:
    conn.executemany(sql, datas)


The operation effect is shown in the figure below:

There is a software called DB Browser for SQLite on macOS, which can be used to check whether the data table we just created is successful:

Query data

You can also view data using SQL statements:

with conn:
    datas = conn.execute("select * from user where name = 'kingname'")
    for data in datas:
        print(data)

The operation effect is shown in the figure below:

Of course, by modifying row_factory can also read data by column name:

conn.row_factory = sqlite3.Row
with conn:
    datas = conn.execute("select * from user where name = 'kingname'")
    for data in datas:
        print(data['address'])

The operation effect is shown in the figure below:

Delete data

Like other operations, to delete data, you only need to write the corresponding SQL statement:

with conn:
    conn.execute('delete from user where id = 3')

The operation effect is shown in the figure below:

Query table

In essence, it is a change of SQL statement. Let's create another table first:

with conn:
    conn.execute('''
    CREATE TABLE info (
    user_id INTEGER,
    salary integer,
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);
    ''')
    sql = 'insert into info (user_id, salary) values(?, ?)'
    datas = [
    (1, 99999999),
    (2, 0),
    ]
    conn.executemany(sql, datas)

Next, perform a join table query:

with conn:
    sql = 'select u.name as name, i.salary as salary from user u inner join info i on u.id=i.user_id'
    datas = conn.execute(sql)
    for data in datas:
        print(f'{data["name"]}Your salary is:{data["salary"]}')

The operation effect is shown in the figure below:

summary

For low concurrency, programs and data can be stored together with SQLite, which is more controllable and friendly than writing a text file to store data alone. Moreover, to operate SQLite with Python, you only need to use the built-in module sqlite3. When you want to give the data to others, you only need to give the generated data Just give the db file to someone else. They can open it using any tool that supports SQLite.

reference material

[1]

Up to 140TB of data can be stored: https://www.sqlite.org/whentouse.html

Official account: WeChat public No. code

Programmer Think