Python SQLITE database is easy to use

The Python programming language has been around for nearly two decades since its appearance. It plays an important role in the development field based on its powerful functions and easy-to-use methods. Today, we will analyze the operation skills of the Python SQLITE database to deepen our understanding of this language.

• How to Use Python to decrypt the basic VBS application code
• How to configure the Python CGI Environment in different operating systems
• Python Algorithm Implementation
• Analysis on the correct use of Python print
• Compile a Python program to calculate the number of rows

Python SQLITE database is a very small embedded open source database software. That is to say, there is no independent maintenance process, and all maintenance comes from the program itself. 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 terms of performance. Although the sparrow is small and dirty, sqlite implements the standard of most sql-92, such as transaction, trigger and complex query.

Since Python2.5, SQLite3 has become the Py standard module, which is the only database interface module in Python. This greatly facilitates the development of small database application systems using Python SQLITE database.

Next, let's step into SQLite in Python.

I. Python SQLITE Database Import module:

 
 

  
  
import sqlite3 
 
 

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 a 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
 
 

4. Use of Python SQLITE database 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 in the Python SQLITE database to perform some operations on the database we created above:

1. Create a table:

 
 

  
  
cu.execute("""create table catalog (id integer primary key,
pid integer,name varchar(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.execute("insert into catalog values(0, 0, 'name1')")  
  
  
cu.execute("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.execute("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.execute("update catalog set name='name2' where id = 0")  
  
  
cx.commit() 
 
 

Note: Submit after data modification

5. Delete:
 

 
 

  
  
cu.execute("delete from catalog where id = 1")  
  
  
cx.commit() 
 
 

The basic points of the Python SQLITE database operations described in the preceding simple operations are as follows. 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.