python数据库编程

SQLite

简介

SQLite是嵌入式关系数据库管理系统。 它是独立的，无服务器的，零配置和事务性SQL数据库引擎。

SQLite可以自由地用于商业或私有的任何目的。 换句话说，“SQLite是一种开源，零配置，独立的，独 立的，旨在嵌入到应用程序中的事务关系数据库引擎”。

SQLite与其他SQL数据库不同，SQLite没有单独的服务器进程。 它直接读取和写入普通磁盘文件。 具有 多个表，索引，触发器和视图的完整SQL数据库包含在单个磁盘文件中。

SQLite的本质就是一个数据库文件。

SQLite内置于所有手机和大多数计算机中，并捆绑在人们每天使用的无数其他应用程序中。

为什么要使用SQLite

• 不需要一个单独的服务器进程或操作的系统（无服务器的）。

• SQLite 不需要配置，这意味着不需要安装或管理。

• 一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。

• SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。

• SQLite 是自给自足的，这意味着不需要任何外部的依赖。

• SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。

• SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。

• SQLite 使用 ANSI-C 编写的，并提供了简单和易于使用的 API。

• SQLite 可在 UNIX（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE, WinRT）中运行。

快速入门

入门案例

SQLite是python自带的数据库，不需要任何配置，使用sqlite3模块就可以驱动它。

sqlite3是内置模块，所以不需要安装的，直接import导入即可：

使用 sqlite3.connect() 函数连接数据库，返回一个 Connection 对象，我们就是通过这个对象与数据库进行交互。

数据库文件的格式是 filename.db ，如果该数据库文件不存在，那么它会被自动创建。该数据库文件是放在电脑硬盘里的，你可以自定义路径，后续操作产生的所有数据都会保存在该文件中

还可以在内存中创建数据库，只要输入特殊参数值 :memory: 即可，该数据库只存在于内存中，不会生成本地数据库文件。

connect = sqlite3.connect(':memory:')

对于connect对象来说，具有以下操作：

方法	说明
commit()	事务提交
rollback()	事务回滚
close()	关闭一个数据库链接
cursor()	创建一个游标

配置SQLite数据库

双击项目目录下的sqlite.db，可直接在Pycharm中打开内置的数据库管理工具。

注意：第一次打开Pycharm内置数据库管理工具时，请先选中sqlite数据库右键，点击属性，手动完成SQLite驱动下载。

错误示范如下：

create table t_student(
    sid int primary key autoincrement,
    sname varchar(50) not null,
    sex varchar(1) default '男',
    birthday datetime not null default current_date
);

具体SQLite数据类型，请参考2.3.3.SQLite数据类型

SQLite数据类型

SQLite 数据类型是一个用来指定任何对象的数据类型的属性。SQLite 中的每一列，每个变量和表达式都有相关的数据类型。可以在创建表的同时使用这些数据类型。SQLite 使用一个更普遍的动态类型（与Python一致）系统。在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。

在SQLite 3.0版中，值被定义为什么类型只和值自身有关，和列没有关系，和变量也没有关系。(这有时被称作弱类型)

为了最大限度的增加SQLite数据库和其他数据库的兼容性,SQLite支持列的"类型亲和性"。列的亲和性是为该列所存储的数据建议一个类型，要注意是建议而不是强迫。

在理论上来讲，任何列依然是可以存储任何类型的数据的。只是针对某些列，如果给建议类型的话,数据库将按所建议的类型存储。这个被优先使用的数据类型则被称为"亲和类型"。

存储类型	说明
NULL	值是一个 NULL 值。
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	值是一个 blob 数据，完全根据它的输入存储。

综合案例

建立与数据库的连接后，需要创建一个游标 cursor 对象，该对象的 .execute() 方法可以执行 sql 命令，让我们能够进行数据操作。

# 创建游标cursor对象
cursor = connect.cursor()

对于cursor对象来说，具有以下操作：

方法	说明
execute()	执行一条sql语句
executemany()	执行多条sql语句
close()	游标关闭
fetchone()	从结果中取出一条记录
fetchmany()	从结果中取出多条记录
fetchall()	从结果中取出所有记录

• 新增

# 方式一：
# 注意：虽然数据表中的数据列类型已经定义了，但是还是可以插入其他类型的数据值，与值有关
cursor.execute("insert into t_student values(2,3,4,5)")
# 方式二：
# 注意：请使用元组方式传递参数值
cursor.execute("insert into t_student(sname,sex,birthday) values(?,?,?)", ('张三', '女', datetime.datetime.now()))
# 方式三：批量新增
sql_text = """insert into t_student(sname,sex,birthday) values(?,?,?)"""
cursor.executemany(sql_text, [
    ('小明1', '男', datetime.datetime.now()),
    ('小明2', '男', datetime.datetime.now()),
    ('小明3', '男', datetime.datetime.now())
])

• 修改

cursor.execute("update t_student set sname=?,sex=?,birthday=? where sid=?",('王五','男',datetime.datetime.now(),2))

• 删除

cursor.execute("delete from t_student where sid=?",(1,))

注意：在删除时的传递单个参数需要以元组方式，也就是(1,)

• 查询

# 从查询结果中取出单条记录
cursor.execute("select * from t_student where sid=?",(3,))
print(cursor.fetchone())
# 从查询结果中取出多条记录
cursor.execute("select * from t_student")
print(cursor.fetchmany(3))
# 从查询结果中取出所有记录
cursor.execute("select * from t_student")
print(cursor.fetchall())
# 分页查询
# 方式一：
cursor.execute("select * from t_student limit ?,?",(0,2))
print(cursor.fetchall())
# 方式二：基于切片方式，内存分页（不推荐）
cursor.execute("select * from t_student")
print(cursor.fetchall()[0:2])

最后，请关闭游标和连接对象：

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

MySQL

使用 pymysql 模块,使用前需要先进行安装。PyMySQL 是在Python3.x版本中用于连接 MySQL 服务器的一个库，Python2中则使用 mysqldb 。

基于PyCharm配置安装PyMySQL模块，首先点击文件 -> 设置，找到项目:python08（注意：python08只是示例项目名，请以自己创建的项目名为准） -> Python 解析器，最后点击+按钮，输入PyMySQL的安装包，选择指定版本，点击安装包(I)按钮。

配置数据库连接：

from pymysql import connect
# 创建connection连接对象
connection = connect(user="root", password="1234",
                     host="localhost", port=3306,
                     database="vue", charset="utf8",
                     autocommit=True)

基本使用：

# 创建游标对象
cursor = connection.cursor()
cursor.execute("select * from t_region_vue")
print(cursor.fetchone())
print(cursor.fetchall())
print(cursor.fetchmany(5))
# 关闭游标
cursor.close()
# 关闭连接
connection.close()

模糊查询：

# 创建游标对象
cursor = connection.cursor()
cursor.execute("select * from t_region_vue where region_name like %s",('%城%',))
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
connection.close()

删除操作：

sql_text = """DELETE FROM lay_employee WHERE dept_no =?"""
try:
    cursor.execute(sql_text)
    connection.commit()
except ProgrammingError as e:
    print('sql执行失败了')
    connection.rollback()
finally:
    cursor.close()
    connection.close()