python uses sqlite3 module to operate sqlite database

SQLite is a lightweight database contained in a C library. It does not require a separate maintenance process and allows the use of nonstandard variants of SQL queries to access the database.

Some applications use SQLite to save internal data. It can also be used when prototyping applications for later transfer to larger databases.

The main advantages of SQLite:

1. Consistent file format:

This is explained in the official documentation of SQLite. We should not compare SQLite with Oracle or PostgreSQL, but with our custom format data files. Compared with SQLite, it not only provides good portability, such as big-endian, little-endian, 32/64-bit and other platform-related issues, but also provides efficient data access, such as establishing indexes based on certain information, thereby improving access Or the performance of sorting this type of data, the transaction function provided by SQLite cannot be effectively guaranteed when operating ordinary files.

2. Application on embedded or mobile devices:

Since SQLite takes up less resources at runtime and does not require any management overhead, it is suitable for mobile devices such as PDAs, smart phones, etc.

The advantages are undoubted.

3. Internal database:

In some application scenarios, we need to perform data filtering or data cleaning for the data inserted into the database server to ensure the validity of the data finally inserted into the database server. Sometimes, whether the data is valid cannot be judged by a single record. Instead, special calculations need to be performed with the historical data of a short period of time before, and then the calculation results are used to judge whether the current data is legal.

In this application, we can use SQLite to buffer this part of historical data. There is another simple scenario that also applies to SQLite, which is the precomputation of statistics. For example, if we are running a service program that collects data in real time, we may need to summarize the data every 10 seconds to form hourly statistics. This statistics can greatly reduce the amount of data when users query, thereby greatly improving the front-end program. query efficiency. In this application, we can cache the collected data within 1 hour in SQLite, and when the hour is reached, calculate the cached data and clear the data.

4. Data analysis:

You can make full use of the SQL features provided by SQLite to complete simple data statistical analysis functions. This is unmatched by yaml and csv files.

In my words, it is very small and very suitable for temporary database. Migrating data is very simple, just transfer the file directly. In fact, I chose leveldb from the beginning, but its features are like nosql, so some slightly complex queries are a bit troublesome.

1. Create a new database: sqlite3 file name

This test.db stores all data.

sqlite3 rui.db

2. Open an existing database: sqlite3 Existing file name

The command to create a new database is exactly the same as to open an existing database. If the file is not in the current directory If it exists, create it; if it exists, open it.

3. Import data: .read data file

Open Notepad and copy the following SQL statement into Notepad, save it as test.sql to the Db directory mentioned above, and enter it in the command line environment

.read test.sql

is to import all data into the rui.db database.

4. List all data tables: .tables

After completing all the above work, we can list all data tables

[root@devops-ruifengyun /tmp ]$ sqlite3 rui.db 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
ceshi  tbl1 
sqlite> 
sqlite>

5. Display the database structure: .schema

In fact They are some SQL statements, they describe the structure of the database, as shown in the figure

sqlite> .schema
CREATE TABLE tbl1(one varchar(10), two smallint);
CREATE TABLE ceshi (user text, note text);

6. Display the structure of the table: .schema Table name

sqlite> .schema ceshi
CREATE TABLE ceshi (user text, note text)

7. Export the data of a certain table: .dump Table name

sqlite> .dump tbl1
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1(one varchar(10), two smallint);
INSERT INTO "tbl1" VALUES('goodbye',20);
INSERT INTO "tbl1" VALUES('hello!',10);
COMMIT;

Let’s explain the usage of python sqlite3. In fact, it is very similar to mysqldb. Its syntax is similar to mysql

import sqlite3
#原文： xiaorui.cc 
#链接数据库文，sqlite都是以文件的形式存在的。
#如果数据库文件不存在，回新建一个，如果存在则打开此文件
conn = sqlite3.connect('example')
c = conn.cursor()
#创建table
c.execute('''create table ceshi (user text, note text)''')
   
# 插入数据，执行SQL语句
c.execute('''insert into ceshi (user,note)  values('mPfiJRIH9T','mPfiJRIH9T')''')
c.execute('''insert into ceshi (user,note)  values('7IYcUrKWbw','7IYcUrKWbw')''')
c.execute('''insert into ceshi (user,note)  values('bXB9VcPdnq','bXB9VcPdnq')''')
c.execute('''insert into ceshi (user,note)  values('2JFk7EWcCz','2JFk7EWcCz')''')
c.execute('''insert into ceshi (user,note)  values('QeBFAlYdPr','QeBFAlYdPr')''')
c.execute('''insert into ceshi (user,note)  values('bDL4T69rsj','bDL4T69rsj')''')
c.execute('''insert into ceshi (user,note)  values('BOxPqmkEd9','BOxPqmkEd9')''')
c.execute('''insert into ceshi (user,note)  values('rvBegjXs16','rvBegjXs16')''')
c.execute('''insert into ceshi (user,note)  values('CWrhA2eSmQ','CWrhA2eSmQ')''')
c.execute('''insert into ceshi (user,note)  values('qQicfV2gvG','qQicfV2gvG')''')
c.execute('''insert into ceshi (user,note)  values('s3vg1EuBQb','s3vg1EuBQb')''')
c.execute('''insert into ceshi (user,note)  values('Lne4xj3Xpc','Lne4xj3Xpc')''')
c.execute('''insert into ceshi (user,note)  values('PH3R86CKDT','PH3R86CKDT')''')
c.execute('''insert into ceshi (user,note)  values('HEK7Ymg0Bw','HEK7Ymg0Bw')''')
c.execute('''insert into ceshi (user,note)  values('lim2OCxhQp','lim2OCxhQp')''')
c.execute('''insert into ceshi (user,note)  values('kVFfLljBJI','kVFfLljBJI')''')
c.execute('''insert into ceshi (user,note)  values('Hpbs3VOXNq','Hpbs3VOXNq')''')
c.execute('''insert into ceshi (user,note)  values('f5ubmznBIE','f5ubmznBIE')''')
c.execute('''insert into ceshi (user,note)  values('beJCQA2oXV','beJCQA2oXV')''')
c.execute('''insert into ceshi (user,note)  values('JyPx0iTBGV','JyPx0iTBGV')''')
c.execute('''insert into ceshi (user,note)  values('4S7RQTqw2A','4S7RQTqw2A')''')
c.execute('''insert into ceshi (user,note)  values('ypDgkKi27e','ypDgkKi27e')''')
c.execute('''insert into ceshi (user,note)  values('Anrwx8SbIk','Anrwx8SbIk')''')
c.execute('''insert into ceshi (user,note)  values('k5ZJFrd8am','k5ZJFrd8am')''')
c.execute('''insert into ceshi (user,note)  values('KYcTv54QVC','KYcTv54QVC')''')
c.execute('''insert into ceshi (user,note)  values('Jv6OyfMA0g','Jv6OyfMA0g')''')
c.execute('''insert into ceshi (user,note)  values('kpSLsQYzuV','kpSLsQYzuV')''')
c.execute('''insert into ceshi (user,note)  values('u2zkJQWdOY','u2zkJQWdOY')''')
c.execute('''insert into ceshi (user,note)  values('D0aspFbW8c','D0aspFbW8c')''')
c.execute('''insert into ceshi (user,note)  values('CwqhvDOrWZ','CwqhvDOrWZ')''')
c.execute('''insert into ceshi (user,note)  values('Tdy5LA9sWO','Tdy5LA9sWO')''')
c.execute('''insert into ceshi (user,note)  values('76HnRVbLX0','76HnRVbLX0')''')
c.execute('''insert into ceshi (user,note)  values('B3aoFibRPV','B3aoFibRPV')''')
c.execute('''insert into ceshi (user,note)  values('7Q6lNdL5JP','7Q6lNdL5JP')''')
c.execute('''insert into ceshi (user,note)  values('Hsob6Jyv4A','Hsob6Jyv4A')''')
#将变动保存到数据库文件，如果没有执行词语句，则前面的insert 语句操作不会被保存
conn.commit()
c.execute('''select * from ceshi ''').fetchall()
#得到所有的记录
rec = c.execute('''select * from ceshi''')
print c.fetchall()