Python Data Persistence 简明教程

Python Data Persistence - Sqlite3 Module

CSV、JSON、XML 等文件的一个主要缺点是，它们对随机访问和事务处理并不十分起作用，因为它们在很大程度上都是非结构化的。因此，对内容进行修改变得非常困难。

One major disadvantage of CSV, JSON, XML, etc., files is that they are not very useful for random access and transaction processing because they are largely unstructured in nature. Hence, it becomes very difficult to modify the contents.

这些平面文件不适合于客户-服务器环境，因为它们缺乏异步处理能力。使用非结构化数据文件会导致数据冗余和不一致。

These flat files are not suitable for client-server environment as they lack asynchronous processing capability. Using unstructured data files leads to data redundancy and inconsistency.

这些问题可以通过使用关系数据库来克服。数据库是一个有组织的数据集合，用于消除冗余和不一致，并维护数据完整性。关系数据库模型非常流行。

These problems can be overcome by using a relational database. A database is an organized collection of data to remove redundancy and inconsistency, and maintain data integrity. The relational database model is vastly popular.

它的基本概念是按实体表（称作关系）排列数据。实体表结构提供了一个属性，其值为每行唯一。这样的属性称为 'primary key' 。

Its basic concept is to arrange data in entity table (called relation). The entity table structure provides one attribute whose value is unique for each row. Such an attribute is called 'primary key'.

当一个表的主键显示在其他表的结构中时，这称为 'Foreign key' ，并且这形成了两个表之间关系的基础。基于此模型，目前有许多流行的关系型数据库管理系统产品 −

When primary key of one table appears in the structure of other tables, it is called 'Foreign key' and this forms the basis of the relationship between the two. Based on this model, there are many popular RDBMS products currently available −

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase
SQLite

SQLite 是一款轻量级关系数据库，用于各种各样的应用程序。它是一款自包含的、无服务器的、零配置的、事务性的 SQL 数据库引擎。整个数据库是一个独立文件，可以放置在文件系统的任何地方。它是一款开源软件，占用空间非常小，并且无需任何配置。它通常用于嵌入式设备、物联网和移动应用程序。

SQLite is a lightweight relational database used in a wide variety of applications. It is a self-contained, serverless, zero-configuration, transactional SQL database engine. The entire database is a single file, that can be placed anywhere in the file system. It’s an open-source software, with very small footprint, and zero configuration. It is popularly used in embedded devices, IOT and mobile apps.

所有关系数据库都使用 SQL 在表格中处理数据。然而，较早以前，每个此类数据库都使用特定于数据库类型的 Python 模块与 Python 应用程序相连接。

All relational databases use SQL for handling data in tables. However, earlier, each of these databases used to be connected with Python application with the help of Python module specific to the type of database.

因此，它们之间缺乏兼容性。如果用户希望转向不同的数据库产品，这将被证明是困难的。通过提出“Python 增强提案 (PEP 248)”以推荐与称为 DB-API 的关系数据库相一致的接口，解决了这种不兼容性问题。最新的推荐称为 DB-API 2.0 版本。（PEP 249）

Hence, there was a lack of compatibility among them. If a user wanted to change to different database product, it would prove to be difficult. This incompatibility issue was addresses by raising 'Python Enhancement Proposal (PEP 248)' to recommend consistent interface to relational databases known as DB-API. Latest recommendations are called DB-API Version 2.0. (PEP 249)

Python 的标准库包含 sqlite3 模块，这是一个 DB-API 兼容模块，可通过 Python 程序处理 SQLite 数据库。本章解释了 Python 与 SQLite 数据库的连接。

Python’s standard library consists of the sqlite3 module which is a DB-API compliant module for handling the SQLite database through Python program. This chapter explains Python’s connectivity with SQLite database.

如前所述，Python 在 sqlite3 模块的形式中包含了对 SQLite 数据库的内置支持。对于其他数据库，必须使用 pip 实用工具安装相应的 DB-API 兼容 Python 模块。例如，要使用 MySQL 数据库，我们需要安装 PyMySQL 模块。

As mentioned earlier, Python has inbuilt support for SQLite database in the form of sqlite3 module. For other databases, respective DB-API compliant Python module will have to be installed with the help of pip utility. For example, to use MySQL database we need to install PyMySQL module.

pip install pymysql

在 DB-API 中推荐以下步骤 −

Following steps are recommended in DB-API −

Establish connection with the database using connect() function and obtain connection object.
Call cursor() method of connection object to get cursor object.
Form a query string made up of a SQL statement to be executed.
Execute the desired query by invoking execute() method.
Close the connection.

import sqlite3
db=sqlite3.connect('test.db')

此处，db 是表示 test.db 的连接对象。请注意，如果数据库尚未存在，它将被创建。连接对象 db 具有以下方法 −

Here, db is the connection object representing test.db. Note, that database will be created if it doesn’t exist already. The connection object db has following methods −

Sr.No.	Methods & Description
1	cursor(): Returns a Cursor object which uses this Connection.
2	commit(): Explicitly commits any pending transactions to the database.
3	rollback(): This optional method causes a transaction to be rolled back to the starting point.
4	close(): Closes the connection to the database permanently.

游标作为给定 SQL 查询的句柄，允许检索结果中一行或多行。从连接获取游标对象以使用以下语句执行 SQL 查询 −

A cursor acts as a handle for a given SQL query allowing the retrieval of one or more rows of the result. Cursor object is obtained from the connection to execute SQL queries using the following statement −

cur=db.cursor()

游标对象具有以下定义的方法 −

The cursor object has following methods defined −

Sr.No	Methods & Description
1	execute() Executes the SQL query in a string parameter.
2	executemany() Executes the SQL query using a set of parameters in the list of tuples.
3	fetchone() Fetches the next row from the query result set.
4	fetchall() Fetches all remaining rows from the query result set.
5	callproc() Calls a stored procedure.
6	close() Closes the cursor object.

以下代码在 test.db 中创建一个表：-

Following code creates a table in test.db:-

import sqlite3
db=sqlite3.connect('test.db')
cur =db.cursor()
cur.execute('''CREATE TABLE student (
StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT (20) NOT NULL,
age INTEGER,
marks REAL);''')
print ('table created successfully')
db.close()

数据库中所需的数据完整性通过连接对象的 commit() 和 rollback() 方法实现。SQL 查询字符串可能包含可能引发异常的错误 SQL 查询，该异常应得到妥善处理。为此，execute() 语句放置在 try 块中。如果成功，则使用 commit() 方法永久保存结果。如果查询失败，则使用 rollback() 方法撤消交易。

Data integrity desired in a database is achieved by commit() and rollback() methods of the connection object. The SQL query string may be having an incorrect SQL query that can raise an exception, which should be properly handled. For that, the execute() statement is placed within the try block If it is successful, the result is persistently saved using the commit() method. If the query fails, the transaction is undone using the rollback() method.

以下代码在 test.db 中的 student 表上执行 INSERT 查询。

Following code executes INSERT query on the student table in test.db.

import sqlite3
db=sqlite3.connect('test.db')
qry="insert into student (name, age, marks) values('Abbas', 20, 80);"
try:
   cur=db.cursor()
   cur.execute(qry)
   db.commit()
print ("record added successfully")
except:
   print ("error in query")
   db.rollback()
db.close()

如果你希望 INSERT 查询中的值子句由用户输入动态提供，请使用 Python DB-API 中推荐的参数替换。？字符用作查询字符串中的占位符，并以元组形式提供 execute() 方法中的值。以下示例使用参数替换方法插入记录。将姓名、年龄和分数作为输入。

If you want data in values clause of INSERT query to by dynamically provided by user input, use parameter substitution as recommended in Python DB-API. The ? character is used as a placeholder in the query string and provides the values in the form of a tuple in the execute() method. The following example inserts a record using the parameter substitution method. Name, age and marks are taken as input.

import sqlite3
db=sqlite3.connect('test.db')
nm=input('enter name')
a=int(input('enter age'))
m=int(input('enter marks'))
qry="insert into student (name, age, marks) values(?,?,?);"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,a,m))
   db.commit()
   print ("one record added successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

sqlite3 模块定义了 executemany() 方法，该方法能够一次添加多条记录。要添加的数据应以元组列表提供，每个元组包含一条记录。列表对象是 executemany() 方法的参数，以及查询字符串。但是，某些其他模块不支持 executemany() 方法。

The sqlite3 module defines The executemany() method which is able to add multiple records at once. Data to be added should be given in a list of tuples, with each tuple containing one record. The list object is the parameter of the executemany() method, along with the query string. However, executemany() method is not supported by some of the other modules.

UPDATE 查询通常包含 WHERE 子句指定的逻辑表达式。execute() 方法中的查询字符串应包含 UPDATE 查询语法。若要将名称为“Anil”的“age”值更新为 23，请按如下方式定义字符串：

The UPDATE query usually contains a logical expression specified by WHERE clause The query string in the execute() method should contain an UPDATE query syntax. To update the value of 'age' to 23 for name='Anil', define the string as below:

qry="update student set age=23 where name='Anil';"

为了使更新过程更具动态性，我们使用如上所述的参数替换方法。

To make the update process more dynamic, we use the parameter substitution method as described above.

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
a=int(input(‘enter age’))
qry="update student set age=? where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (a, nm))
   db.commit()
   print("record updated successfully")
except:
   print("error in query")
   db.rollback()
db.close()

类似地，DELETE 操作是通过使用包含 SQL 的 DELETE 查询语法的字符串调用 execute() 方法执行的。顺便提一下， DELETE 查询通常也包含 WHERE 子句。

Similarly, DELETE operation is performed by calling execute() method with a string having SQL’s DELETE query syntax. Incidentally, DELETE query also usually contains a WHERE clause.

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
qry="DELETE from student where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,))
   db.commit()
   print("record deleted successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

对数据库表进行的重要操作之一是从中检索记录。SQL 为此提供 SELECT 查询。当将包含 SELECT 查询语法的字符串提供给 execute() 方法时，将返回结果集对象。使用游标对象有两个重要的方法，可以使用它们从结果集中检索一条或多条记录。

One of the important operations on a database table is retrieval of records from it. SQL provides SELECT query for the purpose. When a string containing SELECT query syntax is given to execute() method, a result set object is returned. There are two important methods with a cursor object using which one or many records from the result set can be retrieved.

fetchone()

从结果集中提取下一个可用的记录。这是一个元组，由检索到的记录的每一列的值组成。

Fetches the next available record from the result set. It is a tuple consisting of values of each column of the fetched record.

fetchall()

以元组列表的形式检索所有剩余的记录。每个元组对应一个记录，并包含表的每列的值。

Fetches all remaining records in the form of a list of tuples. Each tuple corresponds to one record and contains values of each column in the table.

以下示例列出了学生表中的所有记录

Following example lists all records in student table

import sqlite3
db=sqlite3.connect('test.db')
37
sql="SELECT * from student;"
cur=db.cursor()
cur.execute(sql)
while True:
   record=cur.fetchone()
   if record==None:
      break
   print (record)
db.close()

如果您计划使用 MySQL 数据库而不是 SQLite 数据库，则需要按照上述说明安装 PyMySQL 模块。由于 MySQL 数据库安装在服务器上，因此数据库连接过程中的所有步骤都是相同的，connect() 函数需要 URL 和登录凭据。

If you plan to use a MySQL database instead of SQLite database, you need to install PyMySQL module as described above. All the steps in database connectivity process being same, since MySQL database is installed on a server, the connect() function needs the URL and login credentials.

import pymysql
con=pymysql.connect('localhost', 'root', '***')

可能与 SQLite 不同的唯一一件事是 MySQL 特定的数据类型。同样，通过安装 pyodbc 模块，任何 ODBC 兼容数据库都可以与 Python 一起使用。

Only thing that may differ with SQLite is MySQL specific data types. Similarly, any ODBC compatible database can be used with Python by installing pyodbc module.