零基础学Python|Python高阶-使用Python操作数据库

作者主页：编程指南针

作者简介：Java、前端、Python开发多年，做过高程，项目经理，架构师

主要内容：Java项目开发、毕业设计开发、面试技术整理、最新技术分享

收藏点赞不迷路 关注作者有好处

文末获得源码

一，使用Python操作数据库

数据库作为存储系统数据的主要工具，担负着数据持久化存储的重任，本次主要讲解如何使用Python连接操作SQLite和MYSQL数据库。本部分需要具有一定的数据库基本知识，比如数据库的DML\DDL语句操作等，不再讲述数据库相关知识。

1.1 连接对象

数据库连接对象主要提供获取数据库游标对象和提交/回滚事务的方法，以及如何关闭数据库连接。

python中使用connect()函数来获得数据库连接对象。

语法：

connect(server=None,user=None,password=None,database=None,charset=None)

连接示例：

 import pymysql
 con = pymysql.connect(host='localhost',
                       user='user',
                       password='passwd',
                       db='test',
                       charset='utf8',
                       cursorclass=pymysql.cursors.DictCursor)

connect()函数返回连接对象，它提供了一些方法用于操作事务和游标：

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

1.2 游标对象

游标对象（Cursor Object)代表数据库中的游标，用于指示抓取数据操作的上下文，主要提供执行SQL语句、调用存储过程、获取查询结果等方法。

通过cursor()方法来获得游标对象。游标对象有以下两个常用的属性：

* description:数据库列类型和值的描述信息
* rowcount: 返回结果的行数统计信息，如SELECT,UPDATE,CALLPROC等

常用方法如下图所示：

1.3 SQLite 数据库

SQLite数据库是一种嵌入式数据库，它的数据库就一个文件。它本身是使用C语言编写的，体积小，所以经常被集成到各种应用程序中，Python就内置了SQLite3,所以在Python中使用SQLite,不需要安装任何模块，直接使用。

1.3.1 创建数据库文件

数据库操作的基本流程图如下图所示。

示例：

#操作SQLite数据库
import sqlite3
conn = sqlite3.connect("mysoft.db")  #如果不存会自动创建mysoft.db
cursor = conn.cursor()
cursor.execute("create table user(id int(10) primary key,name varchar(20))")
#关闭游标
cursor.close()
#关闭connection
conn.close()

IDEA中集成的有SQLite插件，可以直接双击打开创建的SQLite数据库。具体如下图所示：

1.3.2 数据操作

1.新增用户数据：

#操作SQLite数据库
import sqlite3
conn = sqlite3.connect("mysoft.db")  #如果不存会自动创建mysoft.db
cursor = conn.cursor()
cursor.execute("insert into user(id,name) values('1','znz')")
cursor.execute("insert into user(id,name) values('2','znz001')")
cursor.execute("insert into user(id,name) values('3','znz002')")
conn.commit()  #提交事务后数据才会进入数据库中
#关闭游标
cursor.close()
#关闭connection
conn.close()

可在IDEA中的右侧显示SQLite处user表右键选择Run SQL Script来执行SQL查询：

select * from user

2.查询用户数据

提供的三种常方式来进行数据查询：

* fetchone(): 获得查询结果集中的下一条记录
* fetchmany(size): 获取指定数量的记录
* fetchall(): 获取结构集的所有记录

示例：

#操作SQLite 数据查询
import sqlite3
conn = sqlite3.connect("mysoft.db")  #如果不存会自动创建mysoft.db
cursor = conn.cursor()
cursor.execute("select * from user")
result = cursor.fetchone()  #取第一条数据后，指针停留在第二条
print(result)
result1 = cursor.fetchmany(2)  #取出剩下的两条
print(result1)
result2 = cursor.fetchall()   #再取就没有了
print(result2)

输出结果：

(1, 'znz')
[(2, 'znz001'), (3, 'znz002')]
[]

示例：

#操作SQLite 数据查询
import sqlite3
conn = sqlite3.connect("mysoft.db")  #如果不存会自动创建mysoft.db
cursor = conn.cursor()
cursor.execute("select * from user")
result2 = cursor.fetchall()
print(result2)

conn.commit()
#关闭游标
cursor.close()
#关闭connection
conn.close()

输出结果：

[(1, 'znz'), (2, 'znz001'), (3, 'znz002')]

占位符传参：可以通过？来表示一个参数的占位，实现运行时动态传参

#操作SQLite 数据查询
import sqlite3
conn = sqlite3.connect("mysoft.db")  #如果不存会自动创建mysoft.db
cursor = conn.cursor()
cursor.execute("select * from user where id>?",(1,))
result2 = cursor.fetchall()
print(result2)

conn.commit()
#关闭游标
cursor.close()
#关闭connection
conn.close()

输出结果：

[(2, 'znz001'), (3, 'znz002')]

3.修改用户的数据

示例：

#操作SQLite 数据修改
import sqlite3

conn = sqlite3.connect("mysoft.db")  #如果不存会自动创建mysoft.db
cursor = conn.cursor()
cursor.execute("update user set name=? where id=?",('fxp',1,))

conn.commit()
cursor.execute("select * from user")
result = cursor.fetchall()
print(result)
#关闭游标
cursor.close()
#关闭connection
conn.close()

4.删除用户数据

示例：

#操作SQLite 数据修改
import sqlite3

conn = sqlite3.connect("mysoft.db")  #如果不存会自动创建mysoft.db
cursor = conn.cursor()
cursor.execute("delete from user where id=?",(1,))

conn.commit()
cursor.execute("select * from user")
result = cursor.fetchall()
print(result)
#关闭游标
cursor.close()
#关闭connection
conn.close()

1.4 MYSQL数据库

本次使用MYSQL5.7版本数据库，安装参考演示视频：https://live.csdn.net/v/282244

python操作MYSQL数据库，需要支持Python的MySQL驱动来连接到MySQL服务器，我们常用PyMySQL模块来操作MYSQL数据库。需在线安装PyMySQL模块：

pip install PyMySQL

我们通过MYSQL的客户端工具Navicat来连接MYSQL服务器后，可以创建一个名为studentPython的数据库。

然后 就可以编写代码连接MYSQL进行相应操作了。

示例：

#操作MYSQL数据库
import pymysql
#此处传参最好是使用关键字参数，不同版本的connect传的不太一样
conn = pymysql.connect(host="localhost",user="root",password="root",db="studentPython")
cursor = conn.cursor()
cursor.execute("select version()")

result = cursor.fetchone()
print("database version:%s" % result)
#关闭游标
cursor.close()
#关闭connection
conn.close()

1.创建数据表

在数据库操作中，创建表，删除表这都属于DDL语句，也就是数据结构定义语句。

示例：

#操作MYSQL数据库  创建表
import pymysql

conn = pymysql.connect(host="localhost",user="root",password="root",db="studentPython")
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS books")
#使用预处理语句创建表
sql="""
create table books(
   id int(8) NOT NULL AUTO_INCREMENT,
   name varchar(50) NOT NULL,
   category varchar(50) NOT NULL,
   price decimal (10,2) DEFAULT NULL,
   publish_time date DEFAULT NULL,
   primary key(id)
)
"""
#执行SQL语句
cursor.execute(sql)
#关闭游标
cursor.close()
#关闭connection
conn.close()

2.操作MYSQL表

对表的增删改查操作和SQLite一样，下面举例演示使用 executemany（）方法批量插入数据。

示例：

#操作MYSQL数据库  操作表
import pymysql

conn = pymysql.connect(host="localhost",user="root",password="root",db="studentPython")
cursor = conn.cursor()
#数据列表
data =[
    ("零基础学Python","python",'79.80','2018-5-28'),
    ("Python从入门到精通","python",'69.80','2018-5-28'),
    ("零基础学PHP","php",'79.80','2018-5-28'),
    ("零基础学Java","java",'59.80','2018-5-28'),
]
try:
    cursor.executemany("insert into books(name,category,price,publish_time) values(%s,%s,%s,%s)",data)
    #提交数据
    conn.commit()
except:
    #发生错误时回滚
    conn.rollback()

#关闭游标
cursor.close()
#关闭connection
conn.close()