Temporary database Python uses the Sqlite3 module to manipulate SQLite

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 don't compare SQLite with Oracle or PostgreSQL, compared to our custom format data files, SQLite not only provides a good

portability, such as platform-related issues such as big-endian, 32/64-bit, and also provides the efficiency of data access, such as To improve the performance of accessing or sorting data,SQLite provides transactional functionality , cannot be effectively guaranteed when working as a normal file.

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 data inserted into the database server to protect data validation 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 the Yaml,csv file without analogy.

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:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
ceshi  tbl1 
sqlite> 
sqlite>

5. Display database structure:. Schema

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

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

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

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

7. Export data for a 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;

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

Import sqlite3
#原文: xiaorui.cc
#链接database, sqlite is in the form of a file.
#If the database file does not exist, go back to create a new one, if it exists, open this file
Conn = sqlite3.connect(‘example‘)
 
c = conn.cursor()
 
#Create table
C.execute(‘‘‘create table ceshi (user text, note text)‘‘‘)
 
# Insert data, execute SQL statement
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’)‘‘‘)


#Save changes to the database file, if the word statement is not executed, the previous insert statement operation will not be saved
Conn.commit()

C.execute(‘‘‘select * from ceshi ‘‘‘).fetchall()
#Get all the records
Rec = c.execute(‘‘‘select * from ceshi‘‘‘)
Print c.fetchall() 

Original: http://rfyiamcool.blog.51cto.com/1030776/1433196

This thing is very handy for small applications.

This article comes from "Feng Yun, on her." "blog, declined reprint!"