Python SQLITE database is easy to use

Source: Internet
Author: User
Tags python sqlite

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:

 
 
  1. import sqlite3 

2. Create a database/open a database:

 
 
  1. 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:

 
 
  1. Commit () -- transaction commit
  2. Rollback () -- transaction rollback
  3. Close () -- close a database connection
  4. 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:

 
 
  1. cu = cx.cursor() 

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

 
 
  1. Execute () -- execute an SQL statement
  2. Executemany -- execute multiple SQL statements
  3. Close () -- close the cursor
  4. Fetchone () -- retrieves a record from the result and points the cursor to the next record
  5. Fetchrecords () -- retrieve multiple records from the results
  6. Fetchall () -- retrieve all records from the result
  7. 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:

 
 
  1. 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:
 

 
 
  1. cu.execute("insert into catalog values(0, 0, 'name1')")  
  2. 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.
 

 
 
  1. cx.commit() 

3. Query:
 

 
 
  1. cu.execute("select * from catalog") 

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

 
 
  1. cu.fetchall() 

The returned results are as follows:
 

 
 
  1. [(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:
 

 
 
  1. cu.execute("update catalog set name='name2' where id = 0")  
  2. cx.commit() 

Note: Submit after data modification

5. Delete:
 

 
 
  1. cu.execute("delete from catalog where id = 1")  
  2. 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.