Python_数据库编程_快速入门

数据库编程

数据库不仅支持各类数据的长期保存，更重要的是支持各种跨平台，跨地域的数据查询、共享和修改，极大方便了认类生活与工作。金融、聊天系统、各类网站、办公自动化系统、各种管理信息系统等，都需要数据库的支持。

在本篇博客中主要介绍SQLite和MySQL数据库的Python接口，并通过几个实例来演示如何通过Python代码实现对数据库的CURD(增删改查)

1. SQLite应用

SQLite是内嵌在Python中的轻量级、基于磁盘文件的数据库管理系统，不需要服务器支持，支持使用SQL语句来访问数据库。该数据库使用C语言开发，支持大多数SQL91标准，支持原子性、一致性、独立性和持久的事务，不支持外键限制；通过数据库级的独占性和共享锁来实现独立事务，当多个线程同时访问同一个数据库并写入数据时，每一时刻只有一个线程可以写入数据。

SQLite支持2TB大小的单个数据库，每个数据库完全存储在单个磁盘文件类，以B+数数据结构的形式存储.一个数据库就是一个文件，通过简单复制即可实现数据库的备份。

访问和操作SQLite数据时，需要首先导入sqlite3模块，然后就可以使用其中的接口来操作数据库了，该模块提供了与DB-API 2.0规范的SQL接口。
Python sqlite3 模块 API

序号	API & 描述
1	sqlite3.connect(database [,timeout ,other optional arguments]) 该 API 打开一个到 SQLite 数据库文件 database 的链接。您可以使用 “:memory:” 来在 RAM 中打开一个到 database 的数据库连接，而不是在磁盘上打开。如果数据库成功打开，则返回一个连接对象。当一个数据库被多个连接访问，且其中一个修改了数据库，此时 SQLite 数据库被锁定，直到事务提交。timeout 参数表示连接等待锁定的持续时间，直到发生异常断开连接。timeout 参数默认是 5.0（5 秒）。如果给定的数据库名称 filename 不存在，则该调用将创建一个数据库。如果您不想在当前目录中创建数据库，那么您可以指定带有路径的文件名，这样您就能在任意地方创建数据库。
2	connection.cursor([cursorClass]) 该例程创建一个 cursor，将在 Python 数据库编程中用到。该方法接受一个单一的可选的参数 cursorClass。如果提供了该参数，则它必须是一个扩展自 sqlite3.Cursor 的自定义的 cursor 类。
3	cursor.execute(sql [, optional parameters]) 该例程执行一个 SQL 语句。该 SQL 语句可以被参数化（即使用占位符代替 SQL 文本）。sqlite3 模块支持两种类型的占位符：问号和命名占位符（命名样式）。例如：cursor.execute(“insert into people values (?, ?)”, (who, age))
4	connection.execute(sql [, optional parameters]) 该例程是上面执行的由光标（cursor）对象提供的方法的快捷方式，它通过调用光标（cursor）方法创建了一个中间的光标对象，然后通过给定的参数调用光标的 execute 方法。
5	cursor.executemany(sql, seq_of_parameters) 该例程对 seq_of_parameters 中的所有参数或映射执行一个 SQL 命令。
6	connection.executemany(sql[, parameters]) 该例程是一个由调用光标（cursor）方法创建的中间的光标对象的快捷方式，然后通过给定的参数调用光标的 executemany 方法。
7	cursor.executescript(sql_script) 该例程一旦接收到脚本，会执行多个 SQL 语句。它首先执行 COMMIT 语句，然后执行作为参数传入的 SQL 脚本。所有的 SQL 语句应该用分号 ; 分隔。
8	connection.executescript(sql_script) 该例程是一个由调用光标（cursor）方法创建的中间的光标对象的快捷方式，然后通过给定的参数调用光标的 executescript 方法。
9	connection.total_changes() 该例程返回自数据库连接打开以来被修改、插入或删除的数据库总行数。
10	connection.commit() 该方法提交当前的事务。如果您未调用该方法，那么自您上一次调用 commit() 以来所做的任何动作对其他数据库连接来说是不可见的。
11	connection.rollback() 该方法回滚自上一次调用 commit() 以来对数据库所做的更改。
12	connection.close() 该方法关闭数据库连接。请注意，这不会自动调用 commit()。如果您之前未调用 commit() 方法，就直接关闭数据库连接，您所做的所有更改将全部丢失！
13	cursor.fetchone() 该方法获取查询结果集中的下一行，返回一个单一的序列，当没有更多可用的数据时，则返回 None。
14	cursor.fetchmany([size=cursor.arraysize]) 该方法获取查询结果集中的下一行组，返回一个列表。当没有更多的可用的行时，则返回一个空的列表。该方法尝试获取由 size 参数指定的尽可能多的行。
15	cursor.fetchall() 该例程获取查询结果集中所有（剩余）的行，返回一个列表。当没有可用的行时，则返回一个空的列表。

使用该模块，首先需要创建一个与数据库关联的Connection对象

import sqlite3
con = sqlite3.connect('example.db')

成功创建Connection对象之后，再创建一个Cursor对象，并且调用Cursor对象的execute()方法来执行SQL语句创建数据表以及查询、插入、修改或删除数据库中的数据。

c = con.cursor()
# 创建表
c.execute('''CREATE TABLE stocks(date text,trans text,symbol text,qty real,price real)''')

<sqlite3.Cursor at 0x18d7a860180>

# 插入一条记录
c.execute("insert into stocks values('2020-4-12','BUY','RHAT',100,35.14)")
# 提交当前事务，保存数据
con.commit()
# 关闭数据库连接
con.close()

如果需要查询表中内容，如下代码：

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
for row in c.execute('select * from stocks order by price'):
    print(row)

('2020-4-12', 'BUY', 'RHAT', 100.0, 35.14)

1.1 Connection对象

在各种数据库提供的接口中，Connection对象都是操作数据库中最基本也是最重要的一个类。其主要方法如下表：

方法	说明
Connection.execute(sql[,parameters])	执行一条SQL语句
Connection.executemany(sql[,parameters])	执行多条SQL语句
Connection.cursor()	返回连接的游标
Connection.commit()	提交当前事务，如果不提交，那么自上次调用commit()方法之后的所有修改都不会真正保存到数据库中
Connection.rollback()	事务的回滚，即撤销当前事务，将数据库恢复至上次调用commit()方法后的状态
Connection.close()	关闭数据库连接
Connection.create_function(name,num_params,func)	创建可在SQL语句中调用的函数，其中name为函数名，num_params表示该函数可以接收的参数个数,func表示Python可调用对象

下面的代码演示了如何在sqlite3连接中创建并调用自定义函数：

import sqlite3
import hashlib

def md5sum(t):
    return hashlib.md5(t).hexdigest()

conn = sqlite3.connect(":memory:")
conn.create_function("md5",1,md5sum) # 参数1--函数名，参数2--形参个数，参数3--函数本身即可调用对象
cur = conn.cursor()
cur.execute('select md5(?)',(b"foo",)) # 在SQL语句中调用自定义函数,注意，在SQLite数据库没有实际的虚表dual
print(cur.fetchone()[0])

acbd18db4cc2f85cedef654fccc4a4d8

1.2 Cursor对象

Cursor对象其实相当于JDBC中的Statement或PreStatement类

Cursor也是sqlite3模块中比较重要的一个对象，该对象具有如下常用方法。

(1) execute(sql[,parameters])

该方法用于执行一条SQL语句，下面的代码演示了该方法的用法；以及为SQL语句传递参数的两种方法，分别使用问号和命名变量作为占位符。

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute('create table people (name,age)')
name