未来的门户网站,辽宁大连网站建设,移动端布局,匀贵网站建设Python操作MySQL数据库详细案例一、前言二、数据准备三、建立数据库四、处理和上传数据五、下载数据六、完整项目数据和代码一、前言 本文通过案例讲解如何使用Python操作MySQL数据库。具体任务为#xff1a;假设你已经了解MySQL和知识图谱标注工具Brat#xff0c;将Brat标注…
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(hostlocalhost,userroot,port 3308,password,#123456database db_name,charsetutf8mb4,cursorclasspymysql.cursors.DictCursor)cursor connection.cursor()return cursor, connectiondef use(db_name):切换数据库返回游标return mycursor(db_name)def create_database(db_name):新建数据库sql fcreate database if not exists {db_name};cursor.execute(sql)def create_table(tbl_name):新建数据表sql fcreate table if not exists {tbl_name};cursor.execute(sql) def drop_database(db_name):删除数据库sql fdrop database if exists {db_name};cursor.execute(sql)def drop_table(tbl_name):删除数据表sql fdrop 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), columnsheader) # 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()#-------------------------------------------------------------------------
# end6、此外若你在任何操作过程中需要清空数据库你可以
# # #格式化数据库,取消下面三行注释运行后便清空数据库。
# drop_database(dbname)
# create_database(dbname)
# print(数据库格式化)四、处理和上传数据 创建好数据库后便可以对数据进行处理并将数据上传到数据库中。
1、同上连接数据库并定义工具函数。
# 将数据添加到数据库数据库已经提前建好import pandas as pd
import pymysql
import redef mycursor(db_name mysql80):connection pymysql.connect(hostlocalhost,userroot,password,#123456port 3308,database db_name,charsetutf8mb4,cursorclasspymysql.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), columnsheader) # 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(rC:\Users\DELL\Desktop\mysql数据库\0001-0500.ann, r, encodingutf-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(hostlocalhost,userroot,password, #123456port 3308,database db_name,charsetutf8mb4,cursorclasspymysql.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), columnsheader) # 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(rC:\Users\DELL\Desktop\neo4j_python\data\4.csv,w, encodingutf-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 ) 进行下载
