Sqlite3 usage in python

Source: Internet
Author: User

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.

The python database module has a unified interface standard, so database operations are in a unified mode, basically the following steps (assuming that the database module is named db ):

1. Use db. connect to create a database connection. Assume that the connection object is conn.
2. If the Data Warehouse does not return the result, you can directly use conn.exe cute to query the database. Depending on the database transaction isolation level, you may need to change the database to conn. commit.
3. If you need to return the query result, use conn. cursor to create the cursor object cur, use cur.exe cute to query the database, and use cur. fetchall/cur. fetchone/cur. fetchmany to return the query result. Depending on the Database Service Isolation level, you may need to change the database to conn. commit.
4. Disable cur, conn

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 calling the connect function, 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:

A cursor provides a flexible means to operate the data retrieved from a table. In essence, A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records. A cursor is always associated with an SQL selection statement. Because the cursor is composedResult set(It can be zero records, one record, or multiple records retrieved by the relevant selection statement) andCursor Position. When processing a result set, a cursor pointing to the result set must be declared. If you have written a program to process a file in C language, the cursor is like the file handle you get when you open the file. If the file is successfully opened, the file handle can represent the file. For a cursor, the principle is the same. The visible cursor can process the result set from the basic table in a similar way as the traditional program reads the flat file, so as to present the data in the table to the program in the form of a flat file.
We knowThe relational database management system is set-oriented.In Sqlite, there is no way to describe a single record in the table, unless you use the where clause to limit that only one record is selected. Therefore, we must use a cursor to process a single record. The cursor allows the application to perform the same or different operations on each row in the row result set returned by the select statement, rather than performing the same operation on the entire result set at a time; it also provides the ability to delete or update data in a table based on the cursor position. It is the combination of the cursor as a collection-oriented database management system and row-oriented programming, enable communication between the two data processing methods.

The following describes how to use cursor cursors. In fact, all SQL statements must be executed under the cursor object.

First, define a cursor:

Cu = cx. cursor () defines a cursor. The cursor object has the following operations:

Execute () -- execute SQL statement execute.pdf -- execute multiple SQL statements close () -- close the cursor fetchone () -- retrieve a record from the result and direct the cursor to the next record fetchiterator () -- fetch multiple records from the results fetchall () -- retrieve all records from the results scroll () -- use the cursor in the Python SQLITE database to perform some operations on the database we created above as below:

1. Create a table:
Cu.exe cute ('create table catalog (id integer primary key, pid integer, name varchar (10) UNIQUE) ') The preceding statement creates a table named catalog, it 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 commit and roll back rollback operations.
Cx. commit ()

3. Query:
Cu.exe cute ("select * from catalog") to extract the queried data, use the fetch *** function of the cursor, such:
Print cu. fetchall () returns the following results:
[(0, 0, u 'name1'), (1, 0, u 'hello')] If we use cu. fetchone (), the first item in the list is returned first, and the second item is returned again.

4. Modify:
Cu.exe cute ("update catalog set name = 'name2' where id = 0 ")

Cx. commit () Note: Submit after modifying data

5. Delete:
Cu.exe cute ("delete from catalog where id = 1") cx. the basic points of the Python SQLITE database operations reflected by the simple operations above commit () 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.

Example:

Exemplary exemplar 1

Import sqlite3

Con = sqlite3.connect ('d:/mydatabase. db3 ')
Cur = con. cursor ()
Cur.exe cute ('create TABLE foo (o_id integer primary key, fruit VARCHAR (20), veges VARCHAR (30 ))')
Con. commit ()
Cur.exe cute ('insert INTO foo (o_id, fruit, veges) VALUES (NULL, "apple", "broccoli ")')
Con. commit ()
Print cur. lastrowid

Cur.exe cute ('select * FROM foo ')
Print cur. fetchall ()

Exemplary exemplar 2

#-*-Encoding: gb2312 -*-
Import sqlite3

Conn = sqlite3.connect ("D:/aaa. db ")
Conn. isolation_level = None # This is the transaction isolation level. By default, You need to commit yourself to modify the database. If it is set to None, it is automatically submitted for each modification; otherwise, it is ""
# Create a table
Conn.exe cute ("create table if not exists t1 (id integer primary key autoincrement, name varchar (128), info varchar (128 ))")
# Insert data
Conn.exe cute ("insert into t1 (name, info) values ('zhaowei', 'only a test ')")
# If the isolation level is not automatically submitted, You need to manually execute commit
Conn. commit ()
# Obtain the cursor object
Cur = conn. cursor ()
# Query with a cursor to obtain the result
Cur.exe cute ("select * from t1 ")
# Getting all results
Res = cur. fetchall ()
Print 'row: ', cur. rowcount
# Cur. description is the description of the table structure.
Print 'desc', cur. description
# The result returned by fetchall is a two-dimensional list.
For line in res:
For f in line:
Print f,
Print
Print '-' * 60

Cur.exe cute ("select * from t1 ")
# After this query, only one result is obtained, which is a one-dimensional list.
Res = cur. fetchone ()
Print 'row: ', cur. rowcount
For f in res:
Print f,
Print
# Fetch another row
Res = cur. fetchone ()
Print 'row: ', cur. rowcount
For f in res:
Print f,
Print
Print '-' * 60

Cur. close ()
Conn. close ()

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.