Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to use sqlite3 database in python. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Python SQLITE database is a very small embedded open source database software, that is to say, there is no independent maintenance process, 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 that it is easy to use, and there is indeed some gap in function compared with other large databases. But in terms of performance, SQLITE is not inferior. Although the sparrow is small and well-equipped, sqlite implements most of the sql-92 standards, such as transaction, trigger and complex queries.

Python's database module has a unified interface standard, so all database operations have a unified mode, which is basically the following steps (assuming that the database module is named db):

1. Create a database connection with db.connect, assuming that the connection object is conn

two。 If the database operation does not need to return results, use conn.execute query directly. Depending on the isolation level of database transactions, conn.commit may be required to modify the database.

3. If you need to return the query results, create the cursor object cur with conn.cursor, query the database through cur.execute, and return the query results with cur.fetchall/cur.fetchone/cur.fetchmany. Depending on the isolation level of the database, conn.commit may be required to modify the database

4. Close cur, conn

Let's step into SQLite in Python step by step.

First, Python SQLITE database import module:

Import sqlite3

Second, create / open the database:

Cx = sqlite3.connect ("E:/test.db") actually we don't need to explicitly create a sqlite database, specify the library name when calling the connect function, open the database directly if the specified database exists, and create a new one if it doesn't exist. This is easy to understand.

Third, database connection objects:

The object cx returned when you open the database is a database connection object, which can do the following:

Commit ()-transaction commit rollback ()-transaction rollback close ()-close a database connection cursor ()-create a cursor

Fourth, the use of Python SQLITE database cursors:

Cursors provide a flexible means of manipulating data retrieved from a table. In essence, cursors are actually a mechanism to extract one record at a time from a result set that includes multiple data records. A cursor is always associated with a SQL select statement. Because cursors consist of a result set (which can be zero, one, or multiple records retrieved by an associated selection statement) and a cursor location that points to a specific record in the result set. When you decide to process the result set, you must declare a cursor that points to the result set. If you have ever used

C language has written procedures for processing files, so cursors are like the file handles you get when you open a file. As long as the file is opened successfully, the file handle can represent the file. The truth is the same for cursors. The visible cursor can process the result set from the underlying table in a similar way to the traditional program reading the flat file, thus presenting the data in the table to the program in the form of a flat file.

We know that relational database management systems are collection-oriented in nature, and there is no expression in Sqlite to describe a single record in a table, unless you use the where clause to restrict that only one record is selected. Therefore, we must use cursors to process data for a single record. Thus, cursors allow applications to perform the same or different operations on each row of the row result set returned by the query statement select, rather than on the entire result set at once; it also provides the ability to delete or update data in the table based on the cursor location It is the cursor that connects the collection-oriented database management system and line-oriented programming, so that the two data processing methods can communicate.

The following focuses on the use of cursor cursors. In fact, all sql statements are executed under the cursor object.

First, define a cursor:

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

Execute ()-- execute sql statement executemany-- execute multiple sql statements close ()-- close cursor fetchone ()-- take a record from the result and point the cursor to the next record fetchmany ()-- take multiple records from the result fetchall ()-- fetch all records from the result scroll ()-- cursor scrolling below let's use the cursor in the Python SQLITE database to do some operations on the database we built above:

1. Create a table:

The above statement cu.execute ('create table catalog (id integer primary key,pid integer,name varchar (10) UNIQUE)') creates a table called catalog, which has a primary key id, a pid, and a name,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')") simply inserts two rows of data, but it should be reminded that it will not take effect until it has been submitted. We use the database connection object cx for commit commit and rollback rollback operations.

Cx.commit ()

3, query:

Cu.execute ("select * from catalog") to extract the queried data, use the fetch*** function of the cursor, such as:

Print cu.fetchall () returns the following result:

[(0,0, upright name 1'), (1,0, upright hello')] if we use cu.fetchone (), we first return the first item in the list, and if we use it again, we return the second item.

4. Modify:

Cu.execute ("update catalog set name='name2' where id = 0")

Cx.commit () Note: modify the data and submit it later.

5, delete:

Cu.execute ("delete from catalog where id = 1") cx.commit () the above simple operations reflect the basic points of Python SQLITE database operations, so far. Then, the power of SQLite is not limited to this. Its support for the advanced features of SQL and its compact and flexible features make SQLite favored by developers in many fields.

For example:

Exemplary exemplar 1

Import sqlite3

Con = sqlite3.connect ('d _ lug _ love _ mydatabase.db3')

Cur = con.cursor ()

Cur.execute ('CREATE TABLE foo (o_id INTEGER PRIMARY KEY, fruit VARCHAR (20), veges VARCHAR (30)')

Con.commit ()

Cur.execute ('INSERT INTO foo (o_id, fruit, veges) VALUES (NULL, "apple", "broccoli")')

Con.commit ()

Print cur.lastrowid

Cur.execute ('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 your own commit to modify the database. If you set it to None, each modification will be committed automatically, otherwise it will be "".

# here's how to create a table

Conn.execute ("create table if not exists T1 (id integer primary key autoincrement, name varchar (128c), info varchar (128C)")

# insert data

Conn.execute ("insert into T1 (name,info) values ('zhaowei',' only a test')")

# if the isolation level is not automatically submitted, you need to perform commit manually

Conn.commit ()

# get the cursor object

Cur = conn.cursor ()

# you can get the results by querying with cursors

Cur.execute ("select * from T1")

# get all the results

Res = cur.fetchall ()

Print 'row:', cur.rowcount

# cur.description is a description of the table structure

Print 'desc', cur.description

# the result returned with fetchall is a two-dimensional list

For line in res:

For f in line:

Print f

Print

Print'-'* 60

Cur.execute ("select * from T1")

# only one result is taken after this query, that is, an one-dimensional list

Res = cur.fetchone ()

Print 'row:', cur.rowcount

For f in res:

Print f

Print

# take another row

Res = cur.fetchone ()

Print 'row:', cur.rowcount

For f in res:

Print f

Print

Print'-'* 60

Cur.close ()

Conn.close ()

After reading the above, have you mastered how to use the sqlite3 database in python? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.