(数据科学学习手札51)用pymysql来操控MySQL数据库

一、简介

  pymysql是Python中专门用来操控MySQL数据库的模块,通过pymysql,可以编写简短的脚本来方便快捷地操控MySQL数据库,本文就将针对pymysql的基本功能进行介绍;

二、操控数据库

2.1 连接数据库

  利用pymysql.connect(host,user,password,port,db)来实现对已知MySQL数据库的连接,其中各参数分别对应着目标数据库的各项属性,db用于指定要连接的database的名称,下面是一个示例:

  要连接的数据库:

import pymysql

'''连接数据库'''
try:
    print('-'*200)
    print('尝试连接数据库')
    print('-'*200)
    db = pymysql.connect(host='localhost',user='root',password='我的密码',port=3306,db='test')
    print('连接成功!','\n',
          'host:{}'.format(db.host),'\n',
          'port:{}'.format(db.port),'\n',
          'user:{}'.format(db.user),'\n',
          'database:{}'.format(db.db))
    
except Exception as e:
    print('因{},连接失败'.format(e))
finally:
    print('-'*200,'\n','连接过程结束')
    print('-'*200)

  若连接成功,显示如下信息:

2.2 插入数据

  在成功连接数据库之后,我们使用db.cursor()来获取数据库的操作游标:

'''获取操作游标'''
cur = db.cursor()

  接下来我们使用.execute()来执行需要完成的SQL语句,其传入参数为字符串类型的SQL语句,譬如,下面的例子中我们创建一个新的表,并将sklearn中内置的鸢尾花数据传入进去:

from sklearn.datasets import load_iris

'''获取鸢尾花数据,其中X,y为二维数组'''
X,y = load_iris(return_X_y=True)
'''建立指定鸢尾花类别名称的列表'''
Species = ['setosa','versicolor','virginica']

'''通过操作游标执行SQL语句,以创建iris表'''
cur.execute("CREATE TABLE IF NOT EXISTS iris"
            "(Sepal_Length float,"
            "Sepal_Width float,"
            "Petal_Length float,"
            "Petal_Width float,Species char(20)"
            ")")

'''构造将X,y数据一次性插入iris的SQL语句'''
BaseSQL = "INSERT INTO iris VALUES"

for i in range(X.shape[0]):
    BaseSQL += "({},{},{},{},'{}'),".format(X[i,0],X[i,1],X[i,2],X[i,3],Species[y[i]])

'''去除末尾多余的逗号'''
BaseSQL = BaseSQL[:-1]

'''执行插入鸢尾花数据的SQL语句'''
cur.execute(BaseSQL)

  运行完上述语句,在已经连接数据库的navicat中查看iris表中的全部数据:

USE test;
SELECT * FROM iris;

  这是会发现,查询结果只有一张空表,这是因为在执行完插入数据的语句后,并没有提交结果,使用.commit()向数据库提交结果:

'''提交结果'''
db.commit()

在navicat中再次查询得到想要的结果:

2.3 查询数据

  查询功能是数据库中核心功能之一,查询取数也是数据分析人员在数据库上最常用的操作,在pymysql中想要完成查询取数的过程,要在执行SQL语句之后,对我们的游标对象使用.fetchall()方法来取得对应的查询结果:

'''查询取数'''
cur.execute("SELECT Species,COUNT(*) FROM iris GROUP BY Species")

'''获取查询结果'''
results = cur.fetchall()

print(results)

  可以看到,取回的结果为一个规整的tuple对象,可以按照其格式打印出查询结果:

print('Species','|','Counts')
for result in results:
    print('-'*20)
    print(result[0],'|',result[1])
print('-'*20)

  或者转换为其他格式保存为其他规整的格式以便进一步分析:

import pandas as pd

df = pd.DataFrame(list(results),columns=['Species','Counts'])
print(df)

  而关于其他对数据库的操作,与上面类似,只是涉及到更改数据库中数据时,不要忘记commit();

2.4 关闭数据库

  在完成需要的操作后,不要忘记断开与数据库间的连接:

db.close()

  以上就是本文的全部内容,如有笔误,望指出!

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏james大数据架构

微软官方提供的用于监控MS SQL Server运行状况的工具及SQL语句

Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返...

34070
来自专栏Java架构师历程

sql必会基础3

对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查...

14420
来自专栏解Bug之路

MySql之自动同步表结构

在开发过程中,由于频繁的修改数据库的字段,导致rd和qa环境的数据库表经常不一致。 而由于这些修改数据库的操作可能由多个rd操作,很难一次性收集全。人手工去和...

14510
来自专栏Java成神之路

Mysql总结_03_mysql常用命令

一、MySQL服务的启动和停止  net stop mysql  net start mysql 二、登陆mysql mysql -u用户名 -p用户密码

8220
来自专栏数据和云

深入内核:Oracle数据库里SELECT操作Hang解析

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

408100
来自专栏菩提树下的杨过

mysql: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for opera

昨天把mysql里所有table的varchar字段的字符集,批量换成了utf8mb4/utf8mb4_unicode_ci ,以便能保存一些emoji火星文 ...

284100
来自专栏数据之美

Hive 常见问题与技巧【Updating】

1Q: 是否有像类似于phpmyadmin一样的hive查询客户端,能以界面的方式查询hive语句和导出数据 A: 有的,客户端的话可以使用squirre...

27970
来自专栏跟着阿笨一起玩NET

sql语句延时执行或者是指定时间执行

以下示例在晚上 10:20 (22:20) 执行存储过程 sp_update_job。

63910
来自专栏JMCui

SQL优化二(SQL性能调优)

一·、前言:这篇博文内容非原创,是我们公司的架构师给我们做技术培训的时候讲的内容,我稍微整理了下,借花献佛。这篇博文只是做一个大概的科普介绍,毕竟SQL优化的知...

43960
来自专栏Spark学习技巧

phoenix二级索引

二级索引 二级索引是从主键访问数据的正交方式。Hbase中有一个按照字典排序的主键Rowkey作为单一的索引。不按照Rowkey去读取记录都要遍历整张表,然后按...

88490

扫码关注云+社区

领取腾讯云代金券