Python 操作数据库

数据库基础

连接对象 Connection

要使用链接对象，首先要生成链接对象。
在Python数据库中，如果要生产链接对象，通常使用 Connect() 函数生成一个 Connection 对象。

connect() 函数的常用参数及说明如下：

参数	说明
dsn	数据源名称，给出该参数表示数据库依赖
user	用户名
password	用户密码
host	主机名
database	数据库名称

在Python中使用具体模块时，相关参数可能会有细节上的差异。

Connection 对象表示目前和数据库的会话，链接对象支持的方法如下：

方法名	说明
close()	关闭数据库连接
commit()	提交事务
rollback()	回滚事务
cursor()	获取游标对象，操作数据库。如执行 DML 操作，调用存储过程等

---

游标对象 Cursor

生成 Cursor 游标对象，需要使用 Connection 对象的 Cursor() 方法
Cursor 游标对象的常用方法如下：

方法名	说明
callproc(procname [,parameters])	调用存储过程，需要数据库支持
close()	关闭当前游标
execute(operation [,parameters])	执行数据库操作，SQL 语句或者数据库命令
executemany(operation, seq_of_params)	用于批量操作，如批量更新
fetchone()	获取查询结果集中的下一条记录
fetchmany(size)	获取指定数量的记录
fetchall()	获取结果集的所有记录
nextset()	跳至下一个可用的结果集
arraysize	指定使用 fetchmany() 获取的行为，默认为1
setinputsizes(sizes)	设置在调用 execute*() 方法时分配的内存区域大小
setoutputsizes(sizes)	设置列缓冲区大小，对大数据列（如 LONGS 和 BLOBS ） 尤其有用

使用游标对象的过程

示例如下：

import pymysql

# 创建游标对象 Connection
conn = pymysql.connect(host='localhost',  	# 主机名
                       user='user',			# 用户名
                       password='password',	# 用户密码
                       db='test',			# 数据库(database)名称
                       charset='unt8',		# 字符串编码
                       cursorcalss=pymysql.cursors.DictCursor)	# 游标类
cur = conn.cursor() 	# 获取 Cursor 对象
cur.excute("insert into users(id,name) values(1,'mr')") 	# 执行SQL操作，插入一条记录
cur.close()		# 关闭 Cursor 对象
conn.commit()   # 使用链接对象提交事物，相当于保存
conn.close()    # 关闭 Connection 对象

数据库基本知识

数据库结构与工作簿结构基本类似，如下图所示

---

使用 Python 操作 SQLite 数据库

SQLite 是一种嵌入式的数据库，其数据库就是一个文件。它将整个数据库（包括定义表、索引、数据）作为单独的可跨平台使用的文件存储在主机中。
由于SQLite 是由C写的，所以体积很小，经常被应用在各种应用程序中，Python中也内置了相应的模块（sqlite3）。
使用 Python 操作 SQLine 与正常的数据库操作实现步骤基本一致，仅是增加了导入 sqlite3 模块的步骤.

创建数据库

创建一个名为 testDB.db 的数据库文件，并创建一个名为 user 的表

import sqlite3		# 导入模块
conn = sqlite3.connect('testDB.db') # 创建连接对象，连接到 testDB.db 数据库
cursor = conn.cursor()              # 创建游标对象
cursor.execute('create table user (id int(10) primary key,name varchar(20),yearsold int(3))') # 执行SQL语句
cursor.close()      # 关闭游标
conn.close()        # 关闭连接    

---

新增记录

import sqlite3 as sq

conn = sq.connect('testDB.db')  # 创建连接对象，连接到 testDB.db 数据库,如不存在则创建
cursor = conn.cursor()          # 创建游标
cursor.execute("insert into user (id,name,yearsold) values ('1','紫薯',25)")  # 向 user 表插入数据
cursor.execute("insert into user (id,name,yearsold) values ('2','丑牛',26)")
cursor.execute("insert into user (id,name,yearsold) values ('3','银狐',18)")
cursor.execute("insert into user (id,name,yearsold) values ('4','卯兔',33)")
cursor.execute("insert into user (id,name,yearsold) values ('5','陈龙',28)")
cursor.execute("insert into user (id,name,yearsold) values ('6','巳蛇',25)")
cursor.close()  # 关闭游标
conn.commit()   # 提交事务
conn.close()    # 关闭连接

---

查询记录

查找表中的数据可以使用如下语句：

select Column_1,Column_2,Column_3 ... from TableName where Pattern

此外，查询数据时通常使用以下3种方式：

• fetchone()：获取下一条查询结果，返回一个元组，为空返回 None
• fetchmany()：获取（从下一条记录开始的）指定数量的记录，返回一个由元组组成的列表；为空返回空列表 []
• fetchall()：获取（从下一条记录开始的）全部查询结果，返回一个元组组成的列表；为空返回空列表 []

示例如下：

import sqlite3 as sq
conn = sq.connect('testDB.db')	# 创建连接
cursor = conn.cursor()			# 创建游标
cursor.execute('select * from user')    # 执行查询语句

result1 = cursor.fetchone() 
# 获取下一条查询结果，返回一个元组，为空返回 None
print(result1)

result2 = cursor.fetchmany(2)  
# 获取（从下一条记录开始的）指定数量的记录，返回一个由元组组成的列表；为空返回空列表[]
print(result2)

result3 = cursor.fetchall() 
# 获取（从下一条记录开始的）全部查询结果，返回一个元组组成的列表；为空返回空列表[]
print(result3)

cursor.close()  # 关闭游标
conn.close()    # 关闭连接

返回结果如下

(1, '紫薯', 25)
[(2, '丑牛', 26), (3, '银狐', 18)]
[(4, '卯兔', 33), (5, '陈龙', 28), (6, '巳蛇', 25)]

---

修改记录

语法：

update TableName set Column1=Value1,Column2=Value2... where Some_column=Some_value

示例如下:

import sqlite3 as sq

conn = sq.connect("testDB.db")  # 创建连接
cursor = conn.cursor()  # 创建游标

cursor.execute('update user set name = ? where id = ?',('寅虎',3))   # 执行修改语句
cursor.execute('update user set name = ? where yearsold > ?',('old_Person',25))   # 执行修改语句
cursor.execute('select * from user')    # 执行查询语句
result = cursor.fetchall()  # 查询所有记录
print(result)   # 打印记录

cursor.close()  # 关闭游标
conn.commit()   # 提交事务
conn.close()    # 关闭连接

输出结果：

[(1, '紫薯', 25), (2, 'old_Person', 26), (3, '寅虎', 18), (4, 'old_Person', 33), (5, 'old_Person', 28), (6, '巳蛇', 25)]

---

删除记录

基本语法：

delete from TableName where Pattern

示例如下：

import sqlite3 as sq

conn = sq.connect('testDB.db')   # 创建连接
cursor = conn.cursor()      # 创建游标

cursor.execute('delete from user where id = ?',(3,))    # 删除记录
cursor.execute('delete from user where yearsold = ?',(25,))
cursor.execute('delete from user where yearsold > ?',(30,))

cursor.execute('select * from user')    # 查询记录
result = cursor.fetchall()
print(result)

cursor.close()      # 关闭游标
conn.commit()       # 提交事务
conn.close()        # 关闭连接

输出结果：

[(2, '丑牛', 26), (5, '陈龙', 28)]