Python uses the Sqlite3 module to manipulate the SQLite database

SQLite is a lightweight database that is contained in the C library. It does not require a separate maintenance process and allows access to the database using non-standard variant (nonstandard variant) SQL query statements.

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

The main advantages of SQLite:

1. Consistent file format:

As explained in SQLite's official documentation, we do not compare SQLite with Oracle or PostgreSQL, and SQLite does not only provide a very good comparison of data files in our custom format.

Portability, such as platform-related issues such as big-endian, 32/64-bit, and also provides the efficiency of data access, such as indexing based on certain information to improve the performance of accessing or sorting such data, SQLite provides transactional functionality that is not effectively guaranteed when operating ordinary files.

2. Applications on embedded or mobile devices:

Because SQLite consumes less resources at run time and does not require any administrative overhead, for PDAs, smartphones, etc.

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

3. Internal Database:

In some scenarios, we need data filtering or data cleansing for the data inserted into the database server to ensure data validity that is eventually inserted into the database server. Sometimes, the data is valid, can not be judged by a single record, but the need for a short period of time with the historical data for a special calculation, and then through the results of the calculation to determine whether the current data is legitimate.

In this application, we can use SQLite to buffer this part of the historical data. There is also a simple scenario that applies to SQLite, the pre-calculation of statistical data. For example, we are running a real-time data Acquisition Service program, we may need to summarize every 10 seconds of data to form hourly statistics, the statistics can greatly reduce the amount of data when users query, thus greatly improving the query efficiency of the front-end program. In this application, we can cache the collected data within 1 hours in SQLite, and when the whole point is reached, the data is emptied after the cached data is calculated.

4. Data analysis:

Can make full use of SQLite to provide SQL features, complete the Simple data statistical analysis function. This is unmatched by the Yaml,csv file.

In my words, he is very small, very suitable for the temporary database, migration data is very simple, directly pass the file on it. In fact, I open is to choose Leveldb, but his characteristics like NoSQL, some slightly complex query, it is a bit of trouble.

1. Create a new database: Sqlite3 file name

This test.db holds all the data.

Sqlite3 rui.db

2. Open an existing database: Sqlite3 file name already exists

Creating a new database is exactly the same as opening a database command that already exists, and if the file does not exist in the current directory, it is new, or opens if it exists.

3. Import data:. read Data file

Open Notepad and copy the following SQL statements into Notepad, save as Test.sql to the Db directory mentioned above, enter in the command-line environment

. Read Test.sql

All data will be imported into the RUI.DB database.

4. List all data sheets:. Tables

With all the work done above, we can list all the data sheets.

[Root@devops-ruifengyun/tmp]$ sqlite3 rui.db SQLite version 3.7.17 2013-05-20 00:56:22enter '. Help ' for Instructionsente R SQL statements terminated with a ";" sqlite>. Tablesceshi  tbl1 sqlite> sqlite>

5. Display database structure:. Schema

is actually some SQL statements, they describe the structure of the database,

sqlite>. Schemacreate TABLE tbl1 (one varchar (ten), and one smallint); CREATE TABLE Ceshi (user text, note text);

6, the structure of the display table:. Schema table Name

sqlite>. Schema ceshicreate TABLE ceshi (user text, note text)

7. Export data for a table:. Dump table Name

sqlite>. Dump Tbl1pragma Foreign_keys=off; BEGIN TRANSACTION; CREATE TABLE TBL1 (one varchar (ten), and one smallint), insert into "TBL1" values (' goodbye ', ' + '), insert INTO ' tbl1 ' values (' Hello ! ', 10); COMMIT;

Again to explain the use of Python sqlite3, in fact, and mysqldb very much like it, his grammar and MySQL almost

Import sqlite3# Original: xiaorui.cc #链接数据库文, SQLite exists in the form of files. #如果数据库文件不存在, go back to the new one, open this file if it exists conn = Sqlite3.connect (' example ') c = conn.cursor () #创建tablec. Execute (' CREATE table Ceshi (user text, note text) ') # Insert data, execute SQL statement c.execute (' INSERT into Ceshi (user,note) VALUES (' mpfijrih9t ', ' Mpfijrih 9T ') 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 ') ') #将变动保存到数据库文件, if no word sentence is executed, the preceding INSERT statement operation will not be saved Conn.commit () C.execute ("SELECT * from Ceshi"). Fetchall () #得到所有的记录rec = C.execute ("SELECT * from Ceshi") Print C.fetchall ()

