Python操作MySQL数据库详细案例

梦里梦外; 2023-10-04 23:23 61阅读 0赞

#### Python操作MySQL数据库详细案例 ####

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

## 一、前言 ##

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

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

## 二、数据准备 ##

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

![在这里插入图片描述][9c508bfc85224f249ae5456186eeb12a.png_pic_center]

## 三、建立数据库 ##

在开始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中查看所有数据库  
![在这里插入图片描述][296a6d6058364ade8324151663e6a110.png_pic_center]  
（2）使用本文创建的kgg数据库，并查看库内所有表  
![在这里插入图片描述][ad49a646b921441d86c0f76b976527a3.png_pic_center]  
（3）检索任意表，查看数据  
![在这里插入图片描述][6852e37063ec4d8faa8e59988832c062.png_pic_center]  
  以上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][GitHub-python_mysql] (或者访问： https://github.com/chenyiadam/python\_mysql.git ) 进行下载

[Link 1]: https://blog.csdn.net/AdamCY888/article/details/127613010
[9c508bfc85224f249ae5456186eeb12a.png_pic_center]: https://img-blog.csdnimg.cn/9c508bfc85224f249ae5456186eeb12a.png#pic_center
[296a6d6058364ade8324151663e6a110.png_pic_center]: https://img-blog.csdnimg.cn/296a6d6058364ade8324151663e6a110.png#pic_center
[ad49a646b921441d86c0f76b976527a3.png_pic_center]: https://img-blog.csdnimg.cn/ad49a646b921441d86c0f76b976527a3.png#pic_center
[6852e37063ec4d8faa8e59988832c062.png_pic_center]: https://img-blog.csdnimg.cn/6852e37063ec4d8faa8e59988832c062.png#pic_center
[GitHub-python_mysql]: https://github.com/chenyiadam/python_mysql.git