SQLite数据库的DB-API.pdf

### SQLite数据库的DB-API2.0接口详解 #### 模块功能概述 SQLite数据库的DB-API2.0接口，即`sqlite3`模块，提供了与SQLite数据库交互的标准接口。此接口遵循Python DB-API 2.0规范，允许开发者通过Python语言进行数据库的操作。 #### 模块中的函数和常量 `sqlite3`模块包含了多种用于创建连接、执行查询等功能的函数，以及一些用于定义数据类型的常量。这些函数和常量为开发者提供了丰富的工具箱来管理和操作SQLite数据库。 #### Connection对象 - **创建连接**：`sqlite3.connect(database)`方法用于建立与数据库的连接。参数`database`可以是一个文件名（如果数据库不存在则创建），也可以是字符串`':memory:'`，在这种情况下，将在内存中创建一个临时数据库。 - 示例：`conn = sqlite3.connect('/tmp/example')` - 内存数据库示例：`conn = sqlite3.connect(':memory:')` - **属性**：`Connection`对象支持多种属性，如`row_factory`（设置获取结果行的方式）、`isolation_level`（设置隔离级别）等。 - **方法**：`Connection`对象还支持多种方法，如`commit()`（提交当前事务）、`rollback()`（回滚当前事务）、`close()`（关闭连接）等。 #### Cursor对象 `Cursor`对象是通过`Connection`对象创建的，主要用于执行SQL命令。通过`Cursor`对象的`execute()`方法，可以执行SQL语句。 - **创建游标**：`c = conn.cursor()` - **执行SQL语句**：`c.execute(sql)`，其中`sql`是SQL语句字符串。 - 创建表：`c.execute("CREATE TABLE stocks (date TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL)")` - 插入数据：`c.execute("INSERT INTO stocks VALUES (?, ?, ?, ?, ?)", ('2006-01-05', 'BUY', 'RHAT', 100, 35.14))` - **提交更改**：`conn.commit()`用于保存对数据库所做的更改。 - **关闭游标**：`c.close()`用于释放资源。 #### SQLite与Python的数据类型 - **基础类型**：SQLite支持NULL、INTEGER、REAL、TEXT和BLOB五种基本数据类型。 - **类型转换**：Python与SQLite之间存在自动类型转换机制，但可以通过注册适配器或转换器来实现更复杂的类型转换。 - **使用适配器存储额外的Python类型**：可以定义适配器函数，将其注册到`Connection`对象上，以处理特定Python类型到SQLite类型之间的转换。 - **注册适配器**：`conn.register_adapter(mytype, myadapter)` - **将SQLite值转换为自定义Python类型**：同样，可以通过注册转换器函数来处理SQLite值到Python类型之间的转换。 - **注册转换器**：`conn.register_converter(typename, myconverter)` #### 缺省的适配器和转换器 - `sqlite3`模块为一些常见的Python类型提供了缺省的适配器和转换器。例如，Python中的`str`类型可以直接映射到SQLite的`TEXT`类型。 #### 事务控制 - `sqlite3`模块支持事务处理。可以通过`Connection`对象的`commit()`方法提交更改，通过`rollback()`方法撤销更改。默认情况下，每个SQL语句都会触发一个事务的开始和结束。但可以通过设置`isolation_level`属性来开启显式事务。 #### 高效地使用pysqlite - **使用快捷方法**：为了提高效率，`Cursor`对象提供了一些快捷方法，如`c.executemany()`，该方法可以一次性执行多条SQL命令。 - 示例：`c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', [('2006-03-28', 'BUY', 'IBM', 1000, 45.00), ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00), ('2006-04-06', 'SELL', 'IBM', 500, 53.00)])` - **通过名字而不是索引访问列**：当从查询结果中获取数据时，可以通过列名而不是索引来访问，这可以通过设置`row_factory`属性来实现。 - 示例：`conn.row_factory = sqlite3.Row`之后，`c.fetchone()['symbol']`即可通过列名访问数据。 - **将Connection用作上下文管理器**：使用`with`语句可以更方便地管理连接和异常处理。 - 示例：`with sqlite3.connect('example.db') as conn:` #### 安全性注意事项 - 在构建SQL语句时，应避免使用字符串拼接的方式，因为这样容易受到SQL注入攻击。应该使用参数化查询，即将变量值作为参数传递给`execute()`方法。 - 不安全示例：`c.execute("WHERE symbol='%s'" % symbol)` - 安全示例：`c.execute('SELECT * FROM stocks WHERE symbol=?', (symbol,))` 总结来说，`sqlite3`模块为Python开发人员提供了一种简单且强大的方式来与SQLite数据库进行交互。通过了解和利用其提供的各种功能，开发者可以高效地进行数据库操作，并确保代码的安全性和性能。