Python利用pymysql创建数据库，表单以及数据填充

和C#相比，更喜欢用Python进行数据库操作以及文件相关操作，不要问为什么，懂的人都懂，不懂的还是不懂，虽然我也不太懂，装下B还是可以的！！！！！
最近项目需要，因为C#操作Mysql插件版本的问题，我选择了Python来实现。具体代码如下：

# # -*- coding: UTF-8 -*-
import pymysql
import xlrd
import docx
'''
数据库及表单属性
'''
datainfo = {
    'DATABASE': 'query_system',
    'TABLE': ['datas_table'],
    'element': ['id INT,', 'name varchar(255) not null,',
                'sex varchar(255) not null,',
                'birthdate varchar(255) not null,',
                'ancestral_home varchar(255) not null,',
                'education varchar(255) not null,',
                'major_in varchar(255) not null,',
                'join_party_time text(21845) not null,',
                'medal text(21845) not null'
                ]
}

'''
创建数据库以及表单
'''


def creatDatable(host, account, pd, code, li):
    # 链接数据库
    conn = pymysql.connect(host=host, user=account, password=pd, charset=code)
    # 获取游标
    cursor = conn.cursor()
    # 获取所有数据库名称
    cursor.execute('show databases;')
    data_tup = cursor.fetchall()
    # 转换为固定格式
    s_database = f"('{datainfo['DATABASE']}',)"
    # 判断当前所建数据库是否存在
    if s_database.lower() in str(data_tup):
        print('数据库已存在')
    else:
        cursor.execute(f'CREATE DATABASE {datainfo["DATABASE"]} character set utf8;')
    # 创建好数据库后访问数据库中的表单
    cursor.execute(f'use {datainfo["DATABASE"]};')
    cursor.execute('SHOW tables;')
    table_info = cursor.fetchall()
    # 创建表单
    for tableitem in datainfo['TABLE']:
        s_table = f"('{tableitem}',)"
        if s_table.lower() in str(table_info):
            print("表单已存在")
            # 先查询数据再写入数据
            searchdatainsql(conn, tableitem, li)

        else:
            print("表单不存在")
            s_element = ""
            for i in datainfo['element']:
                s_element += i
            print(s_element)
            for tableitem in datainfo["TABLE"]:
                cursor.execute((f'CREATE TABLE IF NOT EXISTS %s({s_element})') % tableitem)
                # 写入数据
                writeFilesInMysql(conn, tableitem, li)

    # 向数据库提交执行的语句
    conn.commit()
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()


'''
查询数据库中的数据
'''


def searchdatainsql(conn, table, datali):
    cursor = conn.cursor()
    # 获取一条数据
    '''
    namne='id'
    id = 5
    sql = f"select * from {table} where {namne} = {id} "
    cursor.execute(sql)
    res = cursor.fetchone()
    print(res)
    '''
    ''' 
    # 获取从第二条开始的多条数据 
    sql = f"select * from {table}"
    cursor.execute(sql) 
    cursor.fetchmany()
    res = cursor.fetchmany(10)
    print(res)
    '''
    # 获取所有数据
    sql = f"select * from {table}"
    cursor.execute(sql)
    res = cursor.fetchall()  # 注意一般会通过sql里的limit进行限制数据获取限制，以此获得所有数据
    li = list(res)
    for i in li:
        if list(i)[1] != datali[1]:
            datali[0]=len(li)+1
            writeFilesInMysql(conn, table, datali)


'''
写入数据到数据库
'''

def writeFilesInMysql(conn, table, li):
    cursor = conn.cursor()
    tableinfo = table
    tableinfo = 'select * from %s' % tableinfo
    print(tableinfo)
    cursor.execute(tableinfo)
    sqlvalue = "INSERT INTO %s" % table
    tableTitle = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '%s'"
    cursor.execute(tableTitle % table)
    try:
        # 获取表单下的字段
        nametitle = []
        results = cursor.fetchall()
        for row in results:
            nametitle.append(row[0])
        # 冒泡排序
        for i in range(len(datainfo['element'])):
            for j in range(len(nametitle)):
                if nametitle[j] in datainfo['element'][i]:
                    temp = nametitle[j]
                    nametitle[j] = nametitle[i]
                    nametitle[i] = temp

        print(nametitle)
        print(datainfo['element'])
        name = ','.join(nametitle)
        # 创建占位符
        placeholder = []
        for i in range(len(nametitle)):
            placeholder.append("'%s'")
        placestr = ','.join(placeholder)
        value = f"({name}) VALUES({placestr})"
        sss = '{0}{1}'.format(sqlvalue, value)

        sshs = ''
        for i in li[8]:
            sshs += i
        # 执行SQL语句，插入数据到 test 表，栏位名称为 name,value
        cursor.execute(sss % (
            str(li[0]), str(li[1]), str(li[2]), str(li[3]), str(li[4]), str(li[5]), str(li[6]), str(li[7]),
            str(sshs)))

    except:
        print("存入数据库失败")


def read_excel(xls_name, sheet_name):  # 读取excel文档某个工作表中第一列的数据，放入cell中
    worksheet = xlrd.open_workbook(xls_name)  # xls_name：excel文件名
    sheet = worksheet.sheet_by_name(sheet_name)  # sheet_name：某个工作表的名称
    rows = sheet.nrows  # 获取行数
    row_content = []  # 定义列表，用来存放读取的数据
    for i in range(1, sheet.nrows):
        item = sheet.row_values(i)
        item[0] = int(item[0])
        row_content.append(item)
    return row_content


def getlistdata(path):
    file = docx.Document(path)
    li = []
    for i in range(len(file.paragraphs)):
        if file.paragraphs[i].text != "":
            li.append(file.paragraphs[i].text)
    abdc = str(li[1]).split('，')
    abdc.insert(0, '1')
    liabc = [abdc[0], abdc[1], abdc[2], abdc[3], abdc[4], abdc[5], abdc[6], abdc[7], li[2:-1]]

    return liabc


if __name__ == '__main__':
    path = r"D:\Program Files (x86)\DeskTop\Demo\XXXXXX简介.docx"
    vaaa = getlistdata(path)
    print(vaaa)
    creat = creatDatable('localhost', 'root', 'root', 'utf8', vaaa)

Mysql结果如下：