关于人工智能:Python操作MySQL数据库详细案例

@TOC

一、前言

  本文通过案例解说如何应用 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 pymysql

print("1")

def mycursor(db_name = 'mysql80'):
    connection = pymysql.connect(host='localhost',
                             user='root',
                             port = 3308,
                             password='',#123456
                             database= db_name,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
    cursor = connection.cursor()
    return cursor, connection

def 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 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)

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 re

def mycursor(db_name = 'mysql80'):
    connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',#123456
                             port = 3308,
                             database= db_name,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
    cursor = connection.cursor()
    return cursor, connection

def 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.headclass
  and p1.headentity = p2.headentity
  and p1.relation = p2.relation
  and p1.tailentity = p2.tailentity
  and p1.tailclass = p2.tailclass
  and 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 pymysql

def mycursor(db_name = 'mysql80'):
    connection = pymysql.connect(host='localhost',
                             user='root',
                             password='', #123456
                             port = 3308,
                             database= db_name,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
    cursor = connection.cursor()
    return cursor, connection

def 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) 进行下载

本文由 mdnice 多平台公布