首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >干货 | Python+MySQL数据库操作

干货 | Python+MySQL数据库操作

作者头像
用户1621951
发布2019-06-06 14:19:21
1.1K0
发布2019-06-06 14:19:21
举报
文章被收录于专栏:数据魔术师数据魔术师

Python+MySQL数据库操作

本文介绍如何利用python来对MySQL数据库进行操作,本文将主要从以下几个方面展开介绍:

1.数据库介绍

2.MySQL数据库安装和设置

3.Python操作MySQL

  • 在Python3.X上安装MySQL驱动
  • 创建数据库连接
  • 创建数据表
  • 增、改、删、查
  • 分组、聚合
  • 按批量读取和处理数据

4.小结

01

数据库介绍

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,能直接通过条件快速查询到指定的数据。随着信息技术和市场的发展,数据管理不再仅仅是存储和管理数据,而转变成用户所需要的各种数据管理的方式。

目前,数据库主要有两种形式,一种是非关系型数据库,另一种是关系型数据库。目前,我们用得非常广泛的一种数据库类型是关系型数据库,它可以分为以下几种:

  • Oracle: 付费产品,主要是银行在用(万一出错了有Oracle背锅)
  • DB2: 付费产品,IBM产品
  • SQL Sever

付费产品,微软产品,windows专用

  • PostgreSQL: 免费产品,主要是高校学术上使用
  • MySQL: 大众,免费,开源

作为手无寸金的大学生,我们应该用哪种数据库呢?当然是MySQL。一方面是因为MySQL免费,另一方面是因为普及率最高,出了错,可以很容易找到解决方法。而且,围绕MySQL有一大堆监控和运维的工具,安装和使用很方便。所以,本文接下来也会介绍如何用Python来操作MySQL。

02

MySQL安装和设置

2.1 下载安装:

  • STEP1:MySQL官方网站上下载最新的MySQL Installer 8.0.14版本,下载链接为: https://dev.mysql.com/downloads/installer/
  • STEP2:按照指示操作默认安装,在安装时,MySQL会要求我们设置一个本地登陆账号,账号名一般命为root,端口为3306,自定义一个password即可。

2.2 MySQL workbench创建用户与授权:

  • STEP1:创建新用户。以root用户登录MySQL workbench,先选择users and privileges,然后选择下方的add account去添加用户。
  • STEP2:我们以创建好的blank为例,创建好后在左边的表中出现了blank这个用户。返回MySQL Workbench主页,新建MySQL Connection,用户名和密码为新创建的用户名和密码,这个时候,我们就能看到除了root用户外,还能看到新创建的blank这个user账户了。
  • STEP3:创建数据库和数据表。在root账户中新建一个名字为test_s的Schema,然后我们会在左下角的schemas中看到我们新建的schema。对于Schema,在MySQL中,它等同于database,它是数据库对象的集合,这个集合包括了各种对象,如Tables(表)、Views(视图)、Sorted Procedures(存储过程)、Functions等,我们可以选中Table,点击鼠标右键,选择creat table,即可在该数据库下创建数据表。创建过程和创建数据库类似。
  • STEP4:设置用户权限:而当我们用blank连接数据库时,是没有test_s这个schema的,这个时候我们需要通过root开放权限给blank这个用户。返回root用户操作选项卡,选择users and privileges,选中blank用户,再选择schema privileges,点击add entry;在弹出来的窗口中选择权限范围,这里我们选择指定的test_s给blank这个user,点击ok;双击tets_s,privileges全部授予,点击select all。
  • STEP5:进入主界面,重新进入blank用户操作选项卡,我们会在schemas中看到test_s这个schema。blank这个用户可以对root授权的test_s这个schema中的表进行操作。

至此,我们完成了MySQL中用户的新建和授权。

03

Python操作MySQL

目前,关于Python操作数据库主要有以下几种方法:

  • MySQLdb的使用

MySQLdb是用于Python连接MySQL数据库的接口,它实现了Python数据库API规范V2.0,基于MySQL C API上建立的,目前只支持Python2.x。

  • PyMySQL的使用

PyMySQL是Python中用于连接MySQL服务器的一个库,它支持Python3.x,是一个纯Python写的MySQL客户端,它的目标是替代MySQLdb。PyMySQL在MIT许可下发布。

  • mysql.connector 的使用

由于 MySQL 服务器以独立的进程运行,并通过网络对外服务,所以,需要支持 Python 的 MySQL 驱动来连接到 MySQL 服务器。

目前,有两个 MySQL 驱动:

  1. mysql-connector-python:是 MySQL 官方的纯 Python 驱动
  2. MySQL-python :是封装了 MySQL C驱动的 Python 驱动

  • SQLAlchemy的使用

是一种ORM(Object-Relational Mapping)框架,将关系数据库的表结构映射到对象上,隐藏了数据库操作背后的细节,简化了数据操作。

3.1 在Python3.X上安装MySQL驱动

STEP1:由于MySQL官方提供了mysql-connector-python驱动。安装时,在Anaconda Prompt中输入:

conda install mysql-connector-python

STEP2:使用以下代码来测试mysql-connector是否安装成功:

import mysql.connector

如果没有产生错误,则表明安装成功。

3.2 创建数据库连接

这里连接的是我之前创建的blank这个user。如果数据库已经存在的话,我们可以直接连接;如果数据库不存在,直接连接则会报错,这个时候我们就需要创建一个数据库,创建数据库可以在MySQL Workbench中创建,也可以在python中使用"CREATE DATABASE"语句,在本实验中,我们使用已经在MySQL workbench中已经建好的test_s这个数据库。

import mysql.connector
#连接数据库
config = {
    'user' : 'blank'        #用户名
    'password' : 'password' #自己设定的密码
    'host' : '127.0.0.1'    #ip地址,本地填127.0.0.1,也可以填localhost
    'port' : '3306'         #端口,本地的一般为3306
    'database' : 'test_s'   #数据库名字,这里选用test_s
}
con = mysq;.connector.connect(**config)

3.3 创建数据表

  • STEP1:当Python 和数据之间的连接建立起来之后,要操作数据库,就需要让 Python对数据库执行SQL语句。创建数据表我们使用"CREATE TABLE"语句,在test_s这个数据库中创建一个叫做customers的表格,其中包含id、name、address、sex、age、sl这六个columns。Python是通过游标执行SQL语句的,所以,连接建立之后,就要利用连接对象得到游标对象。

cursor():表示游标

execute():是执行语句

  • STEP2:一般在创建新表的时候,我们还会设置一个主键(PRIMARY KEY)来方便进行查询工作。创建主键,我们可以用"INT AUTO_INCREMENT PRIMARY KEY"
# 创建一个表
# buffered = True 不设的话,查询结果没有读完会报错
# raise errors.InternalError("Unread result found")
mycursor = con.cursor(buffered = True)
mycursor.execute("CREATE TABLE customers(id INT AUTO_INCREMENT PRIMARY KEY, \
                               name VARCHAR(255) , address VARCHAR(255), \
                               7sex VARCHAR(225) , age INT(10) , sl INT(10))")

#VARCHAR()表示的是数据类型,定义的是变长字符串;INT()表示整型

  • STEP3:执行语句。执行完后,我们可以回到MySQL workbench,可以看到在test_s下面的customers这个表格,其中Columns为我们创建的id,name,address,sex,age和sl。
  • STEP4:但是,当我们再次执行语句的时候,由于已经创建了"customers"这个表,所以再次执行会报错,这个时候就需要加一个判断,判断这个表是否已经存在于test_s这个数据库中
ProgrammingError: Table 'customers' alreadyy exists
  • STEP5:我们可以用"SHOW TABLES"语句来查看数据表是否已经存在,如果存在就print"table already exists",如果不存在,就print"table does not exist"。
def tableExists(mycursor, name):
    stmt = "SHOW TABLES LIKE '" +name+ "'"
    mycursor.execute(stmt)
    return mycursor.fetchone()
mycursor = con.cursor()
if tableExists(mycursor , 'customers'):
    print("table already exists")
else:
    print("table not exists")
  • STEP6:上面的语句只是为了帮助我们判断是否有同名表,当我们要新建一个表时,我们可以在这个判断的基础上,在创建新表前删掉数据库内的同名表,再建新表。删除我们用的是"DROP TABLE",新建表是"CERATE TABLE"
import mysql.connector  
#连接数据库  
config = {  
    'user' : 'blank',        
    'password' :'fuying123888',  
    'host' : '127.0.0.1',   
    'port':'3306',           
    'database' : 'test_s'  
}  
con = mysql.connector.connect(**config)  
# 检查一个表是否存在  
def tableExists(mycursor, name):  
    stmt = "SHOW TABLES LIKE '"+name+"'"  
    mycursor.execute(stmt)  
    return mycursor.fetchone()     
# 删除一个表(无论它是否已经存在)  
def dropTable(mycursor, name):  
    stmt = "DROP TABLE IF EXISTS "+name  
    mycursor.execute(stmt)  
# buffered=True 不设的话,查询结果没有读完会报错  
# raise errors.InternalError("Unread result found")  
mycursor = con.cursor(buffered=True)  
# 删除临时表  
tableName = 'customers'  
dropTable(mycursor, tableName)  
# 创建一个表       
mycursor.execute("CREATE TABLE customers(id INT AUTO_INCREMENT PRIMARY KEY,\
                               name VARCHAR(255), address VARCHAR(255), \  
                               sex VARCHAR(225), age INT(10), sl INT(10))")

3.4 增、改、删、查

3.4.1增

在cutomers表中插入数据用的是"INSERT INTO"语句。

除了用一条条用execute( )插入之外,我们还可以用executemany()的方式批量插入,也就是val中包含的是一个元组列表,包含我们想要插入的数据。

需要注意的事是:如果数据表格有更新,那么必须用到commit()语句,否则在workbench是看不到插入的数据的。

# 往表里插入一些记录  
sql="INSERT INTO customers(name,address,sex,age,sl) VALUES(%s, %s,%s,%s,%s)"
val = ("John", "Highway 21","M",23,5000)  
mycursor.execute(sql, val)  
val = ("Jenny", "Highway 29","F",30,12500)  
mycursor.execute(sql, val)  
val=[("Tom","ABC 35","M",35,14000),  
     ("Tom1","Highway 29","M",28,6700),  
     ("Lily","Road 11","F",30,8000),  
     ("Martin","Road 24","M",35,14000),  
     ("Sally","Fast 56","M",32,15000)]  
mycursor.executemany(sql, val)  
con.commit()  

执行以上代码后,回到workbench,,我们可以看到最终的结果为:

3.4.2 改

在cutomers表中更改数据用的是"UPDATE"语句。例如,我们将最后一条 “Sally”的名字改成“Tiny”:

# 将Sally改为Tiny  
sql="UPDATE customers SET name='Tiny' WHERE name ='Sally'"  
mycursor.execute(sql)  
con.commit()  

执行代码,回到workbench我们可以看到结果为:

3.4.3 删

关于删,我们在上文提到了删除表格,用的是“DROP TABLE ”语句,“IF EXISTS”关键字是用于判断表是否存在,只有在存在的情况才删除当我们要删除一条数据记录时候,用到的语句是“DELETE FROM”语句。例如:我们想在customers这个表格当中,删除name为Tiny的这一条记录:

#删除名字为Tiny的记录  
sql="DELETE FROM  customers WHERE name='Tiny'"  
mycursor.execute(sql)  
con.commit()  

执行代码,回到workbench我们可以看到结果为:

3.4.4 查
  • 普通查询

普通查询数据用的是SELECT语句。例如:我们想查询customers的所有信息,并且进行打印输出:

#查询这里面所有的人:  
sql="SELECT * FROM customers"  
mycursor.execute(sql)  
myresult = mycursor.fetchall()     # fetchall() 获取所有记录  
for x in myresult:  
  print(x)  

得到最终结果为:

值得注意的是:fetchall()表示的是获得所有记录;fetchone()表示只获取一条数据;fetchmany(size=3)表示获取三条记录;

  • 限定条件查找

为了获取指定条件下的查找结果,我们可以使用where语句。例如:我们想在查询customers的所有信息基础上,输出年龄大于30岁的消费者的信息:

sql="SELECT * FROM customers WHERE age > 30"  
mycursor.execute(sql)  
myresult = mycursor.fetchall()     # fetchall() 获取所有记录  
for x in myresult:  
  print(x)  

最终得到的结果为:

  • 通配符查找

有时候为了进行模糊查询,可以匹配通配符,通过“LIKE”来进行查找:

百分号 (%):代表零个、一个或多个数字或字符;

下划线 (_):代表一个单一的数字或字符。

例如:查出所有名字中含有t的记录:

#%代表零个、一个或者多个数字或字符  
#_代表一个单一的数字或者字符  
sql = "SELECT * FROM customers WHERE name LIKE '%t%'"  
mycursor.execute(sql)  
myresult=mycursor.fetchall()  
for x in myresult:  
    print(x)  

执行代码,我们得到的结果如下:

值得注意的是:但是使用Like查询时,即使我们在代码输入的是“t”,执行过程中也会将含有“T”的记录同样输出,即用LIKE匹配通配符对大小写不敏感。为了区分大小写,可以用“GLOB”进行查询。

  • 排序

查询结果排序可以使用 ORDER BY 语句,默认的排序方式为升序,如果要设置降序排序,可以设置关键字 DESC。例如:我们要按照年龄对customers进行升序排列:

#排序  
#按照年龄排序  
sql = "SELECT * FROM customers ORDER BY age"  
mycursor.execute(sql)  
myresult=mycursor.fetchall()  
for x in myresult:  
    print(x)  

执行代码,得到的结果为:

n zxx m nb

  • LIMIT

当数据库数量非常大的时候,为了限制查询的数据量,可以采用"LIMIT"语句来指定,比如我们希望在customers表中找出工资最高的三个人:

#找出其中工资最高的3个人  
sql = "SELECT * FROM customers ORDER BY sl DESC LIMIT 3"  
mycursor.execute(sql)  
myresult=mycursor.fetchall()  
for x in myresult:  
    print(x)  

执行代码,得到结果为:

  • 二次筛选

有时候我们在进行一次筛选后,还需要设定一个筛选条件进行二次筛选,我们就可以采用“HAVING”语句。例如:我们希望统计在年龄处于20-30(不包括20岁,但是包括30岁)的人当中,选择薪资大于5000的消费者:

#二次过滤  
#统计在年龄处于20-30之间的人中,选择薪资大于5000的人  
sql = "SELECT * FROM customers WHERE age>20 and age<=30 HAVING sl>5000 "  
mycursor.execute(sql)  
myresult=mycursor.fetchall()  
for x in myresult:  
    print(x)  
con.commit()  
con.close()  

执行代码后,得到的结果如下:

3.5 分组聚合

在数据库中,分组常用的语句为“GROUP BY”语句,聚合函数,通常是配合分组进行使用,在数据库中常用的聚合函数为:

  1. COUNT(*):表示计算总行数,括号可以写*和字段名字
  2. MAX(column):表示求此列的最大值
  3. MIN(column):表示求此列的最小值
  4. SUM(column):表示求此列的和
  5. AVG(column):表示求此列的平均值
  • 从customers表中统计出男女薪资总和

以sex为类别进行GROUP BY 分组,加上WHERE来做条件判断。

#统计出男女的薪水总数  
sql = "SELECT sex,sum(sl) FROM customers GROUP BY sex"  
mycursor.execute(sql)  
myresult=mycursor.fetchall()  
for x in myresult:  
    print(x)  

最终结果为:

  • 从customers表中,按性别进行分组,统计出年龄在20-30的消费者的薪资,并且按照薪资高低进行排序
# 按照性别进行分组,统计出年龄在20-30岁的消费者的薪资  
sql = "SELECT sex,sum(sl) FROM customers WHERE age>20 and age<=30 GROUP BY sex ORDER BY sl"  
mycursor.execute(sql)  
myresult=mycursor.fetchall()  
for x in myresult:  
    print(x)  

值得注意的是:本例是以sex为类别进行GROUP BY 分组,加上WHERE来做条件判断,加上ORDER BY 排序,但是GROUP BY 的位置必须要在WHERE 之后,在ORDER BY 之前。

3.6 分批量读取和处理数据

程序运行的时候,数据都是在内存中的,但是有时候如果数据量太大,内存会装不下,这个时候我们就需要分批从数据库去读取数据,然后再处理,等到处理完了之后,再去读取。比如:我们要从customers当中分批读取和处理薪资大于8000的消费者,并将其存入另一张表中。我们的做法是先新建一个表,然后从数据库当中读取3个,并且将读取的这3个进行处理,处理完读取的这三个后,再去数据库重新读取三个,直到数据库的数据读完为止。

# 分批读取并且处理将薪资大于8000的消费者的记录存到另一张表中  
# 创建一个临时表   
tmpName = 'cust_tmp'  
dropTable(mycursor, tmpName)    
mycursor.execute("CREATE TABLE cust_tmp(id INT AUTO_INCREMENT PRIMARY KEY,\
                               name VARCHAR(255), address VARCHAR(255), \  
                               sex VARCHAR(225), age INT(10), sl INT(10))")
ins = con.cursor(buffered=True)  
if tableExists(mycursor, tableName):  
    print("process table: %s", tableName)  
    # 查询表里的记录  
    sql = "SELECT * FROM customers WHERE address is not null"  
    mycursor.execute(sql)  
    # 每次处理 batchsize 条记录,直到所有查询结果处理完  
    batchsize = 3  
    readsize = batchsize  
    while readsize == batchsize:  
        print("before batch")  
        myresult = mycursor.fetchmany(size=batchsize)  
        for x in myresult:  
            if x[5]>8000:  
                ins.execute("INSERT INTO"+tmpName+"(id,name,address,sex,age,sl) VALUES (%s, %s,%s, %s,%s,%s)", x)  
                print(x)  
        readsize = len(myresult)  
else:  
    print("table: does not exists", tableName)  
con.commit()  
con.close()  

我们回到workbench找到这个新建的表格cust_tmp,我们可以发现薪资大于8000的消费者都被记录上了:

执行代码,我们可以看到处理的过程如下:

在第一批读取的三条记录中,只有两条是满足薪资大于8000的要求,第二批读取的三条记录中,只有一条满足薪资大于8000的要求,而在第三批读取的三条记录中,没有任何记录是满足薪资大于8000的要求,当没有记录可以读的时候,程序即停止。

值得注意的是:就分批读取的batchsize而言,当batchsize太大时,会导致内存装不下,batchsize太小,会导致每次通过网络连接数据库会很慢。因此,我们选取batchsize大小的原则是在内存够用的前提下尽可能的大,在真实的业务场景下,建议每次读取100以上,当内存够用的话,也可以增加至几千上万条。

04

小结

本文介绍了Python+MySQL的基本操作,包括如何安装Mysql,如何装驱动,如何创建连接以及对数据库进行增删改查、分组聚合以及批量读取和处理等操作。但是,本文涉及到的只是对单表进行操作,只是数据库操作的冰山一角;在实际的开发和工作环境中,需要根据实际内容对多表进行操作,这部分请持续关注数据魔术师关于数据库的后期推文。

---The End---

文案 && 代码:付莹

审稿 && 排版:任伟涛

指导老师: 朱文斌教授(华南理工大学工商管理学院)

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2019-02-25,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 数据魔术师 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 2.1 下载安装:
  • 3.1 在Python3.X上安装MySQL驱动
  • 3.2 创建数据库连接
  • 3.3 创建数据表
  • 3.4 增、改、删、查
    • 3.4.2 改
      • 3.4.3 删
        • 3.4.4 查
        • 3.5 分组聚合
        • 3.6 分批量读取和处理数据
        相关产品与服务
        云数据库 SQL Server
        腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档