Python数据库编程

目录

1、SQLite

2、MySQL

---

---

一、SQLite

1.1.简介

        定义：SQLite是嵌入式关系数据库管理系统。 它是独立的，无服务器的，零配置和事务性SQL数据库引擎
        作用：可以自由地用于商业或私有的任何目的
        SQLite是一种开源，零配置，独立的，旨在嵌入到应用程序中的事务关系数据库引擎
与其它数据库的区别：
    SQLite没有单独的服务器进程
    直接读取和写入普通磁盘文件
    具有 多个表，索引，触发器和视图的完整SQL数据库包含在单个磁盘文件中

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

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

1.2.为什么要使用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）中运行。

1.3.案例

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

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

# 导入sqlite3的内置模块
import sqlite3

# 创建数据库连接对象。如果sqlite.db文件不存在，则创建；
connect = sqlite3.connect("sqlite.db")

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

数据库文件的格式是 filename.db ，如果该数据库文件不存在，那么它会被自动创建。

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

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

1.4.配置SQLite数据库

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

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

1.5.SQLite数据类型

        定义：SQLite 数据类型是一个用来指定任何对象的数据类型的属性。

SQLite 中的每一列，每个变量和表达式都有相关的数据类型。可以在创建表的同时使用这些数据类型。SQLite 使用一个更普遍的动态类型（与Python一致）系统。

在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。

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

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

1.6.综合案例

        建立与数据库的连接后，需要创建一个游标 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()