从excel表格中将数据导入到数据库中

从Excel表格中将数据导入到数据库MySQL中

以下代码不涉及id的自动生成与表格的外键查询

# coding=utf-8
import xlrd
import pymysql
# 打开数据所在的工作簿，以及选择存有数据的工作表
book = xlrd.open_workbook("陕西省VR.xlsx")
sheet = book.sheet_by_name("Sheet1")
# 建立一个MySQL连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    passwd='root',
    db='ceshi',
    port=3306,
    charset='utf8'
)
# 获得游标
cur = conn.cursor()
# 创建插入SQL语句
query = 'insert into test1 (school_name,vr_url) values (%s, %s)'
# 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题行
for r in range(2, sheet.nrows):
    # 获取excel表中第r行第1列的数据
    school_name = sheet.cell(r, 0).value
    print(school_name)
    # 获取excel表中第r行第2列的数据
    vr_url = sheet.cell(r, 1).value
    print(vr_url)
    values = (school_name, vr_url)
    # 执行sql语句
    cur.execute(query, values)
cur.close()
# 提交
conn.commit()
conn.close()
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print("导入 " + columns + " 列 " + rows + " 行数据到MySQL数据库!")

以下代码涉及id的自动生成以及外键的查询

# coding=utf-8
import uuid
import xlrd
import pymysql
# 打开数据所在的工作簿，以及选择存有数据的工作表
book = xlrd.open_workbook("./excel_data/甘肃省vr.xlsx")
sheet = book.sheet_by_name("Sheet1")
# 建立一个MySQL连接
conn = pymysql.connect(
    host='rm-bp174a378ics211689o.mysql.rds.aliyuncs.com',
    user='fsnlxp',
    passwd='ZYL_fsnlxp',
    db='gsgkzyl',
    port=3306,
    charset='utf8'
)
# 获得游标
cur = conn.cursor()
# 创建插入SQL语句
query = 'insert into school_vr (id, school_name, vr_url, school_id)' \
        ' values (%s, %s, %s, %s)'   # 无论字段类型是整数还是字符串都选择%s占位符
# 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题行
for r in range(1, sheet.nrows):
    # id的自动生成
    id = str(uuid.uuid1()).replace('-', '')  # 对生成的id进行替换，将‘-’去掉
    school_name1 = sheet.cell(r, 0).value
    print(school_name1)
    # 判断excel表格中的数据如果为空的情况
    vr_url = sheet.cell(r, 1).value if sheet.cell(r, 1).value is not None else ''
    # 查询当前学校的school_id(外键的查询)
    cur.execute("SELECT id FROM tbl_school WHERE school_name = '%s'" % school_name1)
    school_id = cur.fetchall()[0][0]
    print(school_id)
    # 组织数据
    values = (id, school_name1,vr_url, school_id)
    # 执行sql语句
    cur.execute(query, values)
cur.close()
# 提交
conn.commit()
conn.close()
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print("导入 " + columns + " 列 " + rows + " 行数据到MySQL数据库!")

字段生成事项

自动生成id

import uuid
id = str(uuid.uuid1()).replace('-', '')

有关联的查询

# 查询另一个表中的数据
cur.execute("SELECT id FROM tbl_user")
user_id_tupe = cur.fetchall()
# user_id_tupe是一个字典
user_id = user_id_list[0][0]

使用字段默认值

# 专业id置空
profession_id = None

时间字段的生成

# 发表评论时间为当前时间
forum_time  = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

根据已查询出来的字段去查询另一个字段

# 当查询所需条件要用到前面查询到的字段时 %s 必须要用引号括起来
cur.execute("SELECT id FROM tbl_sports_recruit_forum where school_id = '%s'" % school_id)
forum_id = cur.fetchall()[0][0]