Python操作MySQL数据库详细案例
创始人
2024-06-01 23:34:43
0

Python操作MySQL数据库详细案例

  • 一、前言
  • 二、数据准备
  • 三、建立数据库
  • 四、处理和上传数据
  • 五、下载数据
  • 六、完整项目数据和代码

一、前言

  本文通过案例讲解如何使用Python操作MySQL数据库。具体任务为:假设你已经了解MySQL和知识图谱标注工具Brat,将Brat标注的结果上传到MySQL。

  在知识图谱的文本标注任务中,需要将数据按照事先决定的标注规则进行人工标注。Brat是一种比较著名的标注工具,但是目前不支持Windows系统,你可以安装虚拟机使用该工具。本文已经完成了一项基于Windows、Python3.7的标注软件制作工作,你可以点击 实体关系文本标注工具 进行查看。

二、数据准备

  本文从知网下载了一些关于医学文章的摘要,提前设置了标注规则,例如实体类别、关系等。通过Brat工具标注该文本数据并得到输出标注好的结果文件,该文件共有2042行,以T开头表示实体,以R开头表示关系,该文件是对网络图结构的文本表述。

在这里插入图片描述

三、建立数据库

  在开始Python代码任务之前,你需要对数据库有基础的了解,比如你已经具备使用SQL语言操作MySQL数据库的能力。

1、将MySQL中的操作定义为python函数(基于MySQL80),那么就可以直接调用该函数,来操作数据库。

import pandas as pd
import pymysqlprint("1")def mycursor(db_name = 'mysql80'):connection = pymysql.connect(host='localhost',user='root',port = 3308,password='',#123456database= db_name,charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)cursor = connection.cursor()return cursor, connectiondef use(db_name):'''切换数据库,返回游标'''return mycursor(db_name)def create_database(db_name):'''新建数据库'''sql = f'create database if not exists {db_name};'cursor.execute(sql)def create_table(tbl_name):'''新建数据表'''sql = f'create table if not exists {tbl_name};'cursor.execute(sql) def drop_database(db_name):'''删除数据库'''sql = f'drop database if exists {db_name};'cursor.execute(sql)def drop_table(tbl_name):'''删除数据表'''sql = f'drop table if exists {tbl_name};'cursor.execute(sql)def query(sql):'''以数据框形式返回查询据结果'''cursor.execute(sql)data = cursor.fetchall()  # 以元组形式返回查询数据header = [t[0] for t in cursor.description]df = pd.DataFrame(list(data), columns=header)  # pd.DataFrem 对列表具有更好的兼容性# return dfprint(df)def show_databases():'''查看服务器上的所有数据库'''sql = 'show databases;'return query(sql)def select_database():'''查看当前数据库'''sql = 'select database();'return query(sql)def show_tables():'''查看当前数据库中所有的表'''sql = 'show tables;'return query(sql)

2、创建数据库

#选择需要使用的数据库,此前需要创建数据库cursor, db = use('mysql') #默认设置,不要更改!dbname = 'kgg' #此处更改为需要创建的数据库名
create_database(dbname)
cursor, db = use(dbname)

3、建立数据库中各表

# 建实体类表
sqltb1 = ''' create table entity(id int auto_increment primary key comment '实体类编号',name varchar(20) comment '实体类名'
) comment '实体类表';
'''
cursor.execute(sqltb1)# 插入数据
sqlin1  = '''insert into entity values (1, '病症'),(2, '病名'),(3, '诊断方案'),(4, '治疗方案'),(5, '药名'),(6, '其它');
'''
cursor.execute(sqlin1)
db.commit() #事务
# 检查是否传递成功
sqlset = "select * from entity ;"
query(sqlset)#-------------------------------------------------------------------------# 创建关系表格
sqltb2 = ''' create table relation(id int auto_increment primary key comment '关系编号',name varchar(20) comment '关系名'
) comment '关系表';
'''
cursor.execute(sqltb2)#插入数据
sqlin2 = '''insert into relation values (1, '包含'),(2, '治疗'),(3, '危险因素'),(4, '辅助诊断'),(5, '特征'),(6, '并发'),(7, '别名'),(8, '作用'),(9, '条件');'''cursor.execute(sqlin2)
db.commit() #事务
#检查是否传递成功
sqlset = "select * from relation ;"
query(sqlset)#-------------------------------------------------------------------------# 创建实体表
sqltb3 = ''' create table entitymin(id int auto_increment primary key comment '编号',name varchar(50) comment '实体名'
) comment '实体表';
'''
cursor.execute(sqltb3)
db.commit() #事务#-------------------------------------------------------------------------

4、建立一些交叉表

#建立 实体-关系库,并设置主键外键关联# 创建实体表
sqltb5 = ''' create table entityrela(id int auto_increment primary key comment '编号',headclass int comment '头实体类',headentity int comment '头实体',relation int comment '关系',tailentity int comment '尾实体',tailclass int comment '尾实体类'
) comment '实体和关系表';
'''
cursor.execute(sqltb5)
db.commit() #事务#-------------------------------------------------------------------------
#创建链接该表(子表、外键)与另外三张表(父表、主键)链接sqlkey = ''' 
alter table entityrela add constraint encl_he_en_id foreign key (headclass) references entity (id);
'''
cursor.execute(sqlkey)sqlkey = ''' 
alter table entityrela add constraint enla_he_enmin_id foreign key (headentity) references entitymin (id);
'''
cursor.execute(sqlkey)sqlkey = ''' 
alter table entityrela add constraint enla_re_re_id foreign key (relation) references relation (id);
'''
cursor.execute(sqlkey)sqlkey = ''' 
alter table entityrela add constraint enla_ta_enmin_id foreign key (tailentity) references entitymin (id);
'''
cursor.execute(sqlkey)sqlkey = ''' 
alter table entityrela add constraint enla_ta_en_id foreign key (tailclass) references entity (id);
'''
cursor.execute(sqlkey)
db.commit() #事务#-------------------------------------------------------------------------

5、查看创建情况

# 查看该库中所有表格、查看表是否创建成功
show_tables()#-------------------------------------------------------------------------
# end

6、此外,若你在任何操作过程中,需要清空数据库,你可以:

# # #格式化数据库,取消下面三行注释,运行后便清空数据库。
# drop_database(dbname)
# create_database(dbname)
# print('数据库格式化')

四、处理和上传数据

  创建好数据库后,便可以对数据进行处理,并将数据上传到数据库中。

1、同上,连接数据库,并定义工具函数。

# 将数据添加到数据库(数据库已经提前建好)import pandas as pd
import pymysql
import redef mycursor(db_name = 'mysql80'):connection = pymysql.connect(host='localhost',user='root',password='',#123456port = 3308,database= db_name,charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)cursor = connection.cursor()return cursor, connectiondef use(db_name):'''切换数据库,返回游标'''return mycursor(db_name)def query(sql):'''以数据框形式返回查询据结果'''cursor.execute(sql)data = cursor.fetchall()  # 以元组形式返回查询数据header = [t[0] for t in cursor.description]df = pd.DataFrame(list(data), columns=header)  # pd.DataFrem 对列表具有更好的兼容性return df# print(df)def show_databases():'''查看服务器上的所有数据库'''sql = 'show databases;'return query(sql)def select_database():'''查看当前数据库'''sql = 'select database();'return query(sql)def show_tables():'''查看当前数据库中所有的表'''sql = 'show tables;'return query(sql)cursor, db = use('mysql') #默认设置,不要更改!
dbname = 'kgg'
cursor, db = use(dbname)#-------------------------------------

2、数据处理-实体

# 将ann文件数据传入(暂以txt格式传入)
txtfile = open(r'C:\Users\DELL\Desktop\mysql数据库\0001-0500.ann', 'r', encoding='utf-8').readlines()
# print(txtfile[:3])#将实体与关系数据分开
entity = []
relation = []
for i in txtfile:if i[0] == 'T':entity.append(i)else:relation.append(i)# print(relation)#将实体名称、类别与编号提取出来
entity = [i.strip('\n').split('\t') for i in entity]
entity = [[i[0],i[1].split(' ')[0], i[-1]] for i in entity]
# print(entity[:5])

3、上传实体表中数据

#-------------------------------------------------------------------------
#在实体表中插入数据
# #提取实体名,去重;需要提前查看数据库已有的实体名称 需要表名称
entitymin = [i[-1] for i in entity]
entitymin = list(set(entitymin))
# print(entitymin[:5])for q in entitymin:sqlq = "select name from entitymin where  name = (%s);"TF = cursor.execute(sqlq, q)if TF == 0: #存在则为1,不存在为0;不存在则添加    sqlin2 = "insert into entitymin values (null, %s) ;"cursor.execute(sqlin2, q)
db.commit() #事务
# 查看数据效果
sqlq = "select * from entitymin;"
query(sqlq)

4、数据处理-5元组关系

)#-------------------------------------------------------------------------
#将三元组提取出来
relation = [re.split("[\tA:' ']",i) for i in relation]
relation = [[i[1],i[4],i[7]] for i in relation]
# print(relation[:5])dicen = dict([('病症',1),('病名',2),('诊断方案',3),('治疗方案',4),('药名',5),('其它',6)])
dicre =  dict([('包含',1),('治疗',2),('危险因素',3),('辅助诊断',4),('特征',5),('并发',6),('别名',7),('作用',8),('条件',9)])
# print(dicre['包含'])# #将三元组中的实体编号替换成实体名称
for r in relation:r[0] = dicre[r[0]]for e in entity:if r[1] == e[0]:r[1] = e[-1]r.insert(0,e[1])if r[-1] == e[0]:r[-1] = e[-1]r.append(e[1])
# print(relation[:5])#编码化---['头实体类', '头实体','关系类','尾实体', '尾实体类']
enre = []
for j in relation:j[0] = dicen[j[0]]j[-1] = dicen[j[-1]]sqlchaen = "select id from entitymin where name = (%s);"cursor.execute(sqlchaen, j[2])j[2] = cursor.fetchone()['id']sqlchaen = "select id from entitymin where name = (%s);"cursor.execute(sqlchaen, j[3])j[3] = cursor.fetchone()['id']enre.append([j[0],j[2],j[1],j[3],j[4]])
print(enre[-5:]) #传入实体-关系库

5、上传5元组数据

#插入实体-关系库数据,并创建链接该表(子表、外键)与另外三张表(父表、主键)链接# #插入数据
for en in enre:sqlin2 = "insert into entityrela values (null, %s, %s, %s, %s, %s) ;"cursor.execute(sqlin2, (en[0],en[1],en[2],en[3],en[4]))
db.commit() #事务
print('ok')#-------------------------------------------------------------------------
#清除重复数据并id排序
sql = '''
delete p1
from entityrela p1,entityrela p2
where (p1.headclass = p2.headclassand p1.headentity = p2.headentityand p1.relation = p2.relationand p1.tailentity = p2.tailentityand p1.tailclass = p2.tailclassand p1.id > p2.id);
'''
cursor.execute(sql)sql = '''
ALTER TABLE entityrela DROP id;
'''
cursor.execute(sql)
sql ='''
ALTER TABLE entityrela ADD id MEDIUMINT( 8 ) NOT NULL FIRST;
'''
cursor.execute(sql)
sql ='''
ALTER TABLE entityrela MODIFY COLUMN id MEDIUMINT( 8 ) NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY(id);
'''
cursor.execute(sql)
db.commit() #事务
cursor.close()
#-------------------------------------------------------------------------
#end

  其中,db.commit() 表示该事务要么全部成功,要么全部不执行。cursor.close()表示关闭数据库。

6、在MySQL中查看执行结果

(1)在MySQL中查看所有数据库
在这里插入图片描述
(2)使用本文创建的kgg数据库,并查看库内所有表
在这里插入图片描述
(3)检索任意表,查看数据
在这里插入图片描述
  以上3步表示数据库创建成功并成功写入数据。

五、下载数据

  假设我们已经创建好数据库以及上传了数据。很久以后,我们需要从数据库中下载数据,进行分析或者分享给他人,那么本文在这里考虑了这一情况。

1、连接数据库,定义工具函数


# 从数据库中调取数据,以中文显示;import pandas as pd
import pymysqldef mycursor(db_name = 'mysql80'):connection = pymysql.connect(host='localhost',user='root',password='', #123456port = 3308,database= db_name,charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)cursor = connection.cursor()return cursor, connectiondef use(db_name):'''切换数据库,返回游标'''return mycursor(db_name)def query(sql):'''以数据框形式返回查询据结果'''cursor.execute(sql)data = cursor.fetchall()  # 以元组形式返回查询数据header = [t[0] for t in cursor.description]df = pd.DataFrame(list(data), columns=header)  # pd.DataFrem 对列表具有更好的兼容性return df# print(df)def show_databases():'''查看服务器上的所有数据库'''sql = 'show databases;'return query(sql)def select_database():'''查看当前数据库'''sql = 'select database();'return query(sql)def show_tables():'''查看当前数据库中所有的表'''sql = 'show tables;'return query(sql)
dbname = 'mysql'
cursor, db = use(dbname)
dbname = 'kgg'
cursor, db = use(dbname)# print(show_tables())

2、选择数据,并设计输出结构

    #a.headentity ,
sql = '''select  m.name from entityrela a left outer join entitymin m on a.headentity = m.id'''
i_s = list(query(sql)['name'])sql = '''select  m.name from entityrela a left outer join entitymin m on a.tailentity = m.id'''
j_s = list(query(sql)['name'])sql = '''select  m.name from entityrela a left outer join relation m on a.relation = m.id'''
k_s = list(query(sql)['name'])eql = '''select  m.name from entityrela a left outer join entity m on a.headclass = m.id'''
ek_1 = list(query(eql)['name'])eq2 = '''select  m.name from entityrela a left outer join entity m on a.tailclass = m.id'''
ek_2 = list(query(eq2)['name'])result = [[i,j,k,w,p] for i,j,k,w,p in zip(i_s,ek_1,k_s,ek_2,j_s)]
print(result[:5])
cursor.close()

3、设置保存文件地址并下载

f = open(r"C:\Users\DELL\Desktop\neo4j_python\data\4.csv",'w', encoding='utf-8')
for i in result:f.write('\''+i[0]+'\',\''+i[1]+'\',\''+i[2]+'\',\''+i[3]+'\',\''+i[4]+'\'\n')
f.close()
print('ok')

六、完整项目数据和代码

以下任意方式皆可:
1、评论、留言邮箱账号,博主定期回复。
2、点击:GitHub-python_mysql (或者访问: https://github.com/chenyiadam/python_mysql.git ) 进行下载

相关内容

热门资讯

喜欢穿一身黑的男生性格(喜欢穿... 今天百科达人给各位分享喜欢穿一身黑的男生性格的知识,其中也会对喜欢穿一身黑衣服的男人人好相处吗进行解...
发春是什么意思(思春和发春是什... 本篇文章极速百科给大家谈谈发春是什么意思,以及思春和发春是什么意思对应的知识点,希望对各位有所帮助,...
网络用语zl是什么意思(zl是... 今天给各位分享网络用语zl是什么意思的知识,其中也会对zl是啥意思是什么网络用语进行解释,如果能碰巧...
为什么酷狗音乐自己唱的歌不能下... 本篇文章极速百科小编给大家谈谈为什么酷狗音乐自己唱的歌不能下载到本地?,以及为什么酷狗下载的歌曲不是...
华为下载未安装的文件去哪找(华... 今天百科达人给各位分享华为下载未安装的文件去哪找的知识,其中也会对华为下载未安装的文件去哪找到进行解...
怎么往应用助手里添加应用(应用... 今天百科达人给各位分享怎么往应用助手里添加应用的知识,其中也会对应用助手怎么添加微信进行解释,如果能...
家里可以做假山养金鱼吗(假山能... 今天百科达人给各位分享家里可以做假山养金鱼吗的知识,其中也会对假山能放鱼缸里吗进行解释,如果能碰巧解...
一帆风顺二龙腾飞三阳开泰祝福语... 本篇文章极速百科给大家谈谈一帆风顺二龙腾飞三阳开泰祝福语,以及一帆风顺二龙腾飞三阳开泰祝福语结婚对应...
四分五裂是什么生肖什么动物(四... 本篇文章极速百科小编给大家谈谈四分五裂是什么生肖什么动物,以及四分五裂打一生肖是什么对应的知识点,希...
美团联名卡审核成功待激活(美团... 今天百科达人给各位分享美团联名卡审核成功待激活的知识,其中也会对美团联名卡审核未通过进行解释,如果能...