Parsing the correct implementation method for operating Sqlite in Python

Source: Internet
Author: User

The Python programming language has many advantages. Its programming features are mainly reflected in the scalability. In the next article, we will introduce the application skills related to Python-based Sqlite operations in detail, hoping to help you.

  • Summary of the basic concepts of the Python base64 Module
  • Python Socket programming network programming
  • Tips for displaying Python strings
  • Python PAMIE module for IE Automation
  • Brief introduction to the basic application method of Python HTTP operations

I. Installation

Go to the PySqlite homepage to download the installation package, which has the windows version. Now supports Python 2.3 and 2.5.

2. Create a database/open a database

Python uses a file as a database to operate Sqlite. You can specify the location of the database file.

 
 
  1. >>> import sqlite3 >>> cx = sqlite.connect("d:/test.db", 
    encoding='cp936') 

You can use sqlite connect to create a database file. The path is specified above. When the database file does not exist, it is automatically created. If the file already exists, open the file. Encoding indicates the encoding used to save the data. cp936 is a self-contained encoding in Python, which is actually GBK encoding. Cx is the database connection object.

III. Basic database objects

3.1 database connection object

Like the preceding cx 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

3.2 cursor object

All SQL statements must be executed under the cursor object.

 
 
  1. Cu = cx. cursor () defines a cursor. The cursor object has the following operations:
  2. Execute () -- execute an SQL statement
  3. Executemany -- execute multiple SQL statements
  4. Close () -- close the cursor
  5. Fetchone () -- get a record from the result
  6. Fetchrecords () -- retrieve multiple records from the results
  7. Fetchall () -- retrieve multiple records from the result
  8. Scroll () -- cursor scroll

For object methods, go to the Python homepage to view detailed database API documentation. But PySqlite? I don't know what programs support DB APIs. All the operations I listed are supported, but I have not used them all.

Iv. Examples

4.1 database creation

We already have it. We will not repeat it. If you are interested in these examples, you can try it directly in the Python interaction environment)

4.2 create a table

 
 
  1. >>> cu=cx.cursor() >>> 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.

For the data types supported by Python to operate Sqlite, see Version 2 DataTypes?

4.3 insert)

 
 
  1. >>> cu.execute("insert into catalog values(0, 0, 'name1')") 
    >>> cu.execute("insert into catalog values(1, 0, 'hello')") >>> cx.commit() 

If you want to, you can always use the cu cursor object. Note that the transaction statement commit () or rollback () must be used to modify the data, and the object is the database connection object. cx is used here.

4.4 select)

 
 
  1. >>> cu.execute("select * from catalog") >>> cu.fetchall() 
    [(0, 0, 'name2'), (1, 0, 'hello')]fetchall()  

Returns all data in the result set. The result is a tuple list. Each tuple element is arranged in the order of fields in the table creation. Note that a cursor is stateful and records the first few records of the result. Therefore, you can traverse the result set only once. In the preceding case, if fetchone () is executed, null is returned. Note This during testing.

 
 
  1. >>> cu.execute("select * from catalog where id = 1") 
    >>> cu.fetchone() (1, 0, 'hello') 

If no statement is modified for the database, you do not need to execute the transaction statement after execution.

4.5 update (modify)

 
 
  1. >>> Cu.exe cute ("update catalog set name = 'name2' where id = 0 ")
    >>> Cx. commit () >>> cu.exe cute ("select * from catalog ")
    >>> Cu. fetchone () (0, 0, 'name2') 4.6 delete (delete)
  2. >>> Cu.exe cute ("delete from catalog where id = 1") >>> cx. commit ()
    >>> Cu.exe cute ("select * from catalog") >>> cu. fetchall () [(0, 0, 'name2')]

The above is a simple example of how to use Python to operate Sqlite.

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.