Preliminary use of SQLite database in Python

SQLite is a very small embedded open source database software. That is to say, there is no independent maintenance process, and all maintenance comes fromProgramItself. It uses a file to store the entire database, which is very convenient to operate. Its biggest advantage is its ease of use, which is indeed somewhat different from other large databases. However, SQLite is not inferior in performance. Although the sparrow is small and dirty, SQLite implements the standard of most sql-92, such as transaction, trigger and complex query.

From python2.5, sqlite3 has become the py standard module, which is the only database interface module in Python, which greatly facilitates the development of small database application systems using SQLite.

Next, let's step into SQLite in Python.

I. Import module:

 
ImportSqlite3
 

2. Create a database/open a database:

 
Cx = sqlite3.connect ("E:/test. DB")
 

 
In fact, we do not need to explicitly create an SQLite database. When the connect function is called, specify the database name. If the specified database exists, open the database directly, if it does not exist, create a new one and open it again. This application is easy to understand.
 

 
Iii. Database Connection objects:
 

 
The object CX returned when the database is opened is a database connection object. It can perform the following operations:
 

• Commit () -- transaction commit
• Rollback () -- transaction rollback
• Close () -- close a database connection
• Cursor () -- create a cursor

Iv. Usage of cursors:

Anyone who has a basic understanding of the database believes that you don't need to talk about the first three items. The following describes the use of the five cursor. In fact, all SQL statements must be executed under the cursor object.

First, define a cursor:

 
Cu = Cx. cursor ()
 

This defines a cursor. The cursor object has the following operations:

• Execute () -- execute an SQL statement
• Executemany -- execute multiple SQL statements
• Close () -- close the cursor
• Fetchone () -- retrieves a record from the result and points the cursor to the next record
• Fetchrecords () -- retrieve multiple records from the results
• Fetchall () -- retrieve all records from the result
• Scroll () -- cursor scroll

Next we will use the cursor to perform some operations on the database we created above:

1. Create a table:

 
Cu.exe cute (""" Create table catalog (ID integer primary key, PID integer,NameVarchar (10) Unique )""")
 

 
The preceding statement creates a table named catalog, which has a primary key ID, a PID, and a name and name that cannot be repeated.
 

 
2. Insert data:
 

 
Cu.exe cute ("Insert into catalog values (0, 0, 'name1 ')")
 

Cu.exe cute ("Insert into catalog values (1, 0, 'Hello ')")
 

 
Insert two rows of data, but note that the data takes effect only after the data is submitted. We use the database connection object CX to submit the commit and roll back the data.
 

 
Cx. Commit ()
 

 
3. Query:
 

 
Cu.exe cute ("Select * From catalog")
 

 
Use the fetch *** function of the cursor to extract the queried data, for example:
 

 
CU. fetchall ()
 

 
The returned results are as follows:
 

 
[(0, 0, u 'name1'), (1, 0, u 'hello')]
 

 
If we use cu. fetchone (), first the first item in the list is returned, and then the second item is returned.
 

 
4. Modify:
 

 
Cu.exe cute ("Update catalog set name = 'name2' where id = 0")
 

 
Cx. Commit ()
 

 
Note: Submit after data modification
 

 
5. Delete:
 

 
Cu.exe cute ("Delete from catalog where id = 1")
 

 
Cx. Commit ()
 

 
The above simple operation reflects the basic points of the SQLite database operation, which ends here. then, the power of SQLite is not limited to this. Its support for advanced SQL features and its small and flexible features make SQLite favored by developers in many fields.
 

 
<End>