Detailed explanation of the steps for operating SQLite database in Python

This article mainly introduces the method of operating SQLite database in Python. It provides a more detailed analysis of Python installation of sqlite database module and common operating techniques for sqlite database. Friends in need can refer to the following

Examples of this article Learn how Python operates SQLite databases. Share it with everyone for your reference, the details are as follows:

A brief introduction to SQLite

##SQLite database is a very compact embedded open source database software , that is to say, there is no independent maintenance process, and all maintenance comes from the program itself. It is a relational database management system that complies with ACID. Its design target is embedded, and it has been used in many embedded products. It occupies very low resources. In embedded devices, it may only require a few One hundred K of memory is enough. It can support mainstream operating systems such as Windows/Linux/Unix, and can be combined with many programming languages, such as Tcl, C#, PHP, Java, etc., as well as ODBC interfaces. It is also compared to the two open source worlds of Mysql and PostgreSQL. In terms of famous database management systems, its processing speed is faster than them all. The first Alpha version of SQLite was born in May 2000. It has been 10 years now, and SQLite has also ushered in a version. SQLite 3 has been released.

Installation and use

1. Import the Python SQLITE database module

After Python2.5 , built-in SQLite3, becomes a built-in module, which saves us the effort of installation, just import it~

import sqlite3

2. Create/open the database

When calling the connect function, specify the library name. If the specified database exists, open the database directly. If it does not exist, create a new one and open it.

cx = sqlite3.connect("E:/test.db")

You can also

Create a database in memory.

con = sqlite3.connect(":memory:")

3. Database connection object

The object cx returned when opening the database is a database connection object, which can have the following Operation:

① commit()--Transaction submission

② rollback()--Transaction rollback
③ close()--Close a database connection
④ cursor()-- Create a cursor

Regarding commit(), if the isolation_level isolation level is default, then this command needs to be used for every operation on the database. You can also set isolation_level=None, which will change to automatic submission mode.

4.Use cursorQuery the database

We need to use the cursor object SQL statement to query the database and obtain the query object. Define a cursor in the following ways.

cu=cx.cursor()

The cursor object has the following operations:

① execute()--Execute sql statement

② executemany--execute multiple sql statements
③ close()--close the cursor
④ fetchone()--take a record from the result and point the cursor to the next record
⑤ fetchmany() --Fetch multiple records from the result
⑥ fetchall()--Fetch all records from the result
⑦ scroll()--Cursor scroll

1. Create table

Copy code The code is as follows:

cu.execute("create table catalog (id

integer primary key,pid integer,name varchar( 10) UNIQUE, nickname text NULL)")

The above statement creates a table called catalog, which has a primary key id, a pid, and a name. The name cannot be repeated, and a nickname defaults is NULL.

2. Insert data

Please be careful to avoid the following writing:

# Never do this -- insecure 会导致注入攻击
pid=200
c.execute("... where pid = '%s'" % pid)

The correct approach is as follows. If t is just a single value, it should also be in the form of t=(n,), because the tuple is immutable.

for t in[(0,10,'abc','Yu'),(1,20,'cba','Xu')]:
  cx.execute("insert into catalog values (?,?,?,?)", t)

Simply insert two rows of data, but you need to be reminded that it will only take effect after it is submitted. We use the database connection object cx to commit and rollback rollback operation.

cx.commit()

3. Query

cu.execute("select * from catalog")

To extract the queried data , use the fetch function of the cursor, such as:

In [10]: cu.fetchall()
Out[10]: [(0, 10, u'abc', u'Yu'), (1, 20, u'cba', u'Xu')]

If we use cu.fetchone(), the first item in the list will be returned first, and if used again, the first item will be returned. Two items, proceed in order.

4. Modify

In [12]: cu.execute("update catalog set name='Boy' where id = 0")
In [13]: cx.commit()

Note, submit after modifying the data

5. Delete

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

6. Use Chinese

Please confirm your IDE or system default encoding first It is utf-8, and add u

before Chinese

x=u'鱼'
cu.execute("update catalog set name=? where id = 0",x)
cu.execute("select * from catalog")
cu.fetchall()
[(0, 10, u'\u9c7c', u'Yu'), (1, 20, u'cba', u'Xu')]

如果要显示出中文字体，那需要依次打印出每个字符串

In [26]: for item in cu.fetchall():
  ....:   for element in item:
  ....:     print element,
  ....:   print
  ....: 
0 10 鱼 Yu
1 20 cba Xu

7.Row类型

Row提供了基于索引和基于名字大小写敏感的方式来访问列而几乎没有内存开销。 原文如下：

sqlite3.Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. It will probably be better than your own custom dictionary-based approach or even a db_row based solution.

Row对象的详细介绍

class sqlite3.Row
A Row instance serves as a highly optimized row_factory for Connection objects. It tries to mimic a tuple in most of its features.
It supports mapping access by column name and index, iteration, representation, equality testing and len().
If two Row objects have exactly the same columns and their members are equal, they compare equal.
Changed in version 2.6: Added iteration and equality (hashability).
keys()
This method returns a tuple of column names. Immediately after a query, it is the first member of each tuple in Cursor.description.
New in version 2.6.

下面举例说明

In [30]: cx.row_factory = sqlite3.Row
In [31]: c = cx.cursor()
In [32]: c.execute('select * from catalog')
Out[32]: <sqlite3.Cursor object at 0x05666680>
In [33]: r = c.fetchone()
In [34]: type(r)
Out[34]: <type &#39;sqlite3.Row&#39;>
In [35]: r
Out[35]: <sqlite3.Row object at 0x05348980>
In [36]: print r
(0, 10, u&#39;\u9c7c&#39;, u&#39;Yu&#39;)
In [37]: len(r)
Out[37]: 4
In [39]: r[2]      #使用索引查询
Out[39]: u&#39;\u9c7c&#39;
In [41]: r.keys()
Out[41]: [&#39;id&#39;, &#39;pid&#39;, &#39;name&#39;, &#39;nickname&#39;]
In [42]: for e in r:
  ....:   print e,
  ....: 
0 10 鱼 Yu

使用列的关键词查询

In [43]: r['id']
Out[43]: 0
In [44]: r['name']
Out[44]: u'\u9c7c'

The above is the detailed content of Detailed explanation of the steps for operating SQLite database in Python. For more information, please follow other related articles on the PHP Chinese website!