前言:
最近一直在学习有关python的各种操作,但是还是写不正确。以下是朋友写的,自己分享一下。大家共勉
要求:
给一个excle,将内容插入到数据库中
![在这里插入图片描述](https://img8.php1.cn/3cdc5/187ec/ae9/c1ee8da1967707ea.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY2NTYzNw==,size_16,color_FFFFFF,t_70)
![在这里插入图片描述](https://img8.php1.cn/3cdc5/187ec/ae9/d420c3846253f58a.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY2NTYzNw==,size_16,color_FFFFFF,t_70)
=====代码如下:
import pymysql
import xlrd
import xlwt
def get_conn():
conn = pymysql.connect(
host=‘localhost’,
port=3306,
user=‘root’,
passwd=‘123456’,
db=‘tt’,
charset=‘utf8’)
return conn
def insert(cur, sql, args):
cur.execute(sql, args)
def read_xlsx_to_mysql(filename):
excel = xlrd.open_workbook(filename) # 打开xlsx文件,返回一个对象
sheet = excel.sheet_by_index(0) # 获取第一个sheet表格
conn = get_conn()
cur = conn.cursor()
sql = ‘insert into student values(%s,%s,%s,%s,%s)’
print(sheet.nrows)
for row in range(sheet.nrows):
print(row)
args = sheet.row_values(row)
print(args)
print(type(args))
if row == 0:
continue
if args[1] is None or args[1] == ‘’:
continue
insert(cur, sql, args=args)
conn.commit()
cur.close()
conn.close()
if name == ‘main’:
read_xlsx_to_mysql(‘C:\Users\LDH\Desktop\student.xls’)
如果大家要引用请修改
1、数据库的链接
![在这里插入图片描述](https://img8.php1.cn/3cdc5/187ec/ae9/062b1410b055288b.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY2NTYzNw==,size_16,color_FFFFFF,t_70)
2、插入的数据库表的名称和要插入的列数
![在这里插入图片描述](https://img8.php1.cn/3cdc5/187ec/ae9/fe1aca688c0906f8.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTY2NTYzNw==,size_16,color_FFFFFF,t_70)
3、引用的txt的地址
![在这里插入图片描述](https://img8.php1.cn/3cdc5/187ec/ae9/55d5c3857efddd8c.png)