前置准备: 四个文件分别如下: 1. testdata.xls文件 2. 数据库配置信息 3. mysql数据库操作 4. excel读取 - 数据库配置信息
- PageCount=10
- dbhost="10.10.1.248"
- dbport=3306
- dbuser="root"
- dbpasswd="123456"
- dbname="testdb"
複製代碼
mysqldb.py文件内容- # 数据库操作
- import pymysql
- from configs import dbhost,dbpasswd,dbport,dbuser,dbname
- def get_cur():
- conn = pymysql.connect(host=dbhost, port=dbport, user=dbuser, passwd=dbpasswd, database=dbname)
- cur = conn.cursor()
- return cur,conn
- def get_count(sql):
- cur,conn = get_cur()
- cur.execute(sql)
- count = cur.fetchall()
- cur.close()
- conn.close()
- return count[0][0]
- def get_table_colum():
- cur,conn = get_cur()
- cur.execute("select * from xxljob_info")
- col_name_list = [tuple[0] for tuple in cur.description]
- cur.close()
- conn.close()
- return col_name_list
- def get_data(sql1):
- cur,conn = get_cur()
- cur.execute(sql1)
- results = cur.fetchall()
- cloumn = get_table_colum()
- res = {}
- reslist = []
- for r in range(len(list(results))):
- for m in range(len(list(cloumn))):
- res[str(list(cloumn)[m])] = str(list(results)[r][m])
- reslist.append(res)
- res = {}
- cur.close()
- conn.close()
- return reslist
- def execute_sql(sql):
- cur, conn = get_cur()
- cur.execute(sql)
- cur.close()
- conn.commit()
- conn.close()
- if __name__ == '__main__':
- get_table_colum()
- sql = "insert into xxljob_info (executer,describes,router,block,cron,is_repeater,leader,status,mark,create_time) VALUES ('testabc','testabc','第一个','单机串行','testabc','否','testabc','testabc','testabc','2022-06-16 13:14:19')"
- for i in range(55):
- execute_sql(sql)
- # get_count("select count(*) from xxljob_info")
複製代碼 readExcel.py文件内容- # 读取excel表数据,并执行sql
- import xlrd
- from mysqldb import execute_sql
- file = xlrd.open_workbook("testdata.xls")
- table1 = file.sheet_by_name("数据表")
- sql = "insert into testdata values "
- for row in range(table1.nrows - 1):
- list1 = table1.row_values(row + 1)
- list1 = [row + 1] + list1
- execute_sql(sql + str(tuple(list1)))
複製代碼 上 4 个文件即可通过 Python 实现 Excel 内容自动读取并入库 Mysql。
|