Python uses the sqlite3 module to operate the sqlite Database

Description: SQLite is a lightweight database included in the C library. It does not require independent maintenance processes and allows SQL query statements of nonstandardvariant to access the database. Some applications use SQLite to save internal data. It can also be used when building an application prototype, so that it can be transferred to a larger number in the future.

Description: SQLite is a lightweight database included in the C library. It does not require independent maintenance processes and allows SQL query statements of nonstandard variant to access the database. Some applications use SQLite to save internal data. It can also be used when building an application prototype, so that it can be transferred to a larger number in the future.

Introduction:

SQLite is a lightweight database included in the C library. It does not require independent maintenance processes and allows SQL query statements of nonstandard variant to access the database.

Some applications use SQLite to save internal data. It can also be used when building an application prototype for later migration to a larger database.

Hello, specify the original article address, blog. xiaorui. cc

Major advantages of SQLite:

1. Consistent file format:

This is explained in the official SQLite documentation. We should not compare SQLite with Oracle or PostgreSQL. Compared with data files in custom formats, SQLite not only provides good

Portability, such as big-end, small-end, 32/64-bit, and other platform-related issues. It also provides high data access efficiency, such as creating indexes based on certain information, so as to improve the performance of accessing or sorting such data, the transaction function provided by SQLite cannot be effectively guaranteed when operating common files.

2. Applications on embedded or mobile devices:

Because SQLite consumes less resources and does not require any management overhead

For mobile devices, the advantages of SQLite are beyond doubt.

3. internal database:

In some application scenarios, We need to filter or clear 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 determined by a single record. Instead, it needs to perform special calculations with the historical data of a short period of time, then, the calculation result is used to determine whether the current data is valid.

In this application, we can use SQLite to buffer this part of historical data. Another simple scenario is also applicable to SQLite, that is, pre-Calculation of statistical data. For example, if we are running a real-time data collection service program, we may need to summarize the data every 10 seconds to form hourly statistics, this statistical data can greatly reduce the amount of data queried by users, thus greatly improving the query efficiency of the front-end program. In this application, we can cache all the collected data within one hour in SQLite. When the entire point is reached, the cached data is calculated and cleared.

4. Data Analysis:

You can make full use of SQLite's SQL features to complete simple data statistical analysis. This is incomparable to yaml and csv files.

In my words, he is very small and suitable for temporary databases. It is very easy to migrate data, just transfer files directly. In fact, I used leveldb at the beginning, but its features are like nosql, and some slightly complicated queries are a little troublesome. Speaking of the single-file db mode, sqlite is the most popular for mysql.

1. Create a new database: sqlite3 file name

This test. db stores all the data.

Sqlite3 rui. db

2. Open an existing database: sqlite3 file name

Creating a new database is the same as opening an existing database command. If the file does not exist in the current directory, it is created. If yes, it is opened.

3. import data:. read data file

Open notepad, copy the following SQL statements to notepad, save as test. SQL to the Db directory mentioned above, and enter

. Read test. SQL

Import all data to the rui. db database.

4. list all data tables:. tables

After completing all the above work, we can list all the 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 ";"

Sqlite>. tables

Ceshi tbl1

Sqlite>

5. display the database structure:. schema

It is actually some SQL statements that describe the structure of the database,

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

6. display the table structure:. schema table name

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

7. Export data of a table:. dump table name

sqlite> .dump tbl1PRAGMA 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;

The usage of python sqlite3 is similar to that of mysqldb. Its syntax is similar to that of mysql.

Import sqlite3 # Original article: xiaorui. cc # link database, sqlite all exist in the form of files. # If the database file does not exist, create a new one. If yes, open the file conn = sqlite3.connect ('example ') c = conn.cursor(create tablec.exe cute ('''create table ceshi (user text, note text) ''') # insert data, execute SQL sentence c.exe cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) using cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note, note) values('qebfalydpr', 'qebfalydpr'{''{c.exe cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) using cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note, note) values('qqicfv2gvg', 'qqicfv2gvg'{'{c.exe cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) using cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note, note) values('lim2ocxhqp', 'lim2ocxhqp'{'{c.exe cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) using cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note, note) values('jypx0itbgv', 'jypx0itbgv'{'{c.exe cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) using cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note, note) values('kyctv54qvc', 'kyctv54qvc'{''{c.exe cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) using cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note, note) values('cwqhvdorwz', 'cwqhvdorwz'your 'your c.exe cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) using cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note) into cute (''' insert into ceshi (user, note, note) values ('hsob6jyv4a ', 'hsob6jyv4a') ''') # Save the changes to the database file. If no word sentence is executed, the previous insert sentence operation will not be saved as conn.commit()c.exe cute ('''select * from ceshi '''). fetchall () # obtain all records rec = c.exe cute ('''select * from ceshi ''') print c. fetchall ()

SQlite is suitable for some simulated development environments, or applications that do not require servers such as mysql. But who can resist the speed of Sqlite? I certainly won't mainly choose Sqlite. Some people say that sqlite is very similar to mysql and can easily transplant Code. It is not as simple as a file to maintain it, I remember a thread security problem once, but I am confused. Spit out ~