Knowledge:

• Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table
• To insert multiple rows into a table, use the executemany() method.
• The second parameter of the executemany() method is a list of tuples or lists, containing the data you want to insert.

import xlrd import pymysql import sys import datetime def mysql_link ( database_name ) : try : db = pymysql . connect ( host = 'localhost' , user = 'root' , password = '888888' , database = database_name ) return db except Exception as e : print ( e ) print ( '数据库连接失败' ) def read_excel ( wb_name ) : try : wb = xlrd . open_workbook ( wb_name ) return wb except Exception as e : print ( e ) print ( '打开excel文件失败' ) def upload_data ( db_name , table_name , wb_name ) : db = mysql_link ( db_name ) # 打开数据库连接 cursor = db . cursor ( ) all_data = [ ] wb = read_excel ( wb_name ) # 读取文件 shts = wb . sheet_names ( ) # 返回该文件的所有Sheet名 print ( shts ) for sht in shts : sh = wb . sheet_by_name ( sht ) # 依次遍历每个sheet表格 row_num = sh . nrows # 获取行数 num = 0 for i in range ( 1 , row_num ) : # 第2行开始，遍历（row_num - 1）条记录 row_data = sh . row_values ( i ) value = [ row_data [ 0 ] , row_data [ 1 ] , row_data [ 2 ] , row_data [ 3 ] , row_data [ 4 ] , row_data [ 5 ] ] all_data . append ( value ) # sql = "insert into " + table_name + \ # "(salesdate,product_id,salesRegion,salesPerson,SalesQty,salesAmount)"\ # "values(%s,%s,%s,%s,%s,%s)" sql = f"""insert into { table_name } (salesdate,product_id,salesRegion,salesPerson,SalesQty,salesAmount) values(%s,%s,%s,%s,%s,%s)""" print ( all_data ) cursor . executemany ( sql , all_data ) db . commit ( ) print ( cursor . rowcount , "was inserted in total." ) cursor . close ( ) db . close ( ) if __name__ == "__main__" : upload_data ( 'ods_testdb' , "t_sales" , '销售数据.xls' )

#OperationalError: (1292, “Incorrect date value: ‘44400’ for column ‘salesdate’ at row 1”)
#录入数据需要日期数据需要文本？— NO
#excel数据格式要遵循常规格式 ！！！！

去MySql中创建库表： CREATE TABLE t_customers( `name` VARCHAR(100) not null, address VARCHAR(100) import mysql . connector db = mysql . connector . connect ( host = "localhost" , user = "root" , password = "888888" , database = "ods_testdb" cursor = mydb . cursor ( ) sql = "insert into t_customers(name, address)values(%s, %s)" val = [ ( 'Peter' , 'Lowstreet 4' ) , ( 'Amy' , 'Apple st 652' ) , ( 'Hannah' , 'Mountain 21' ) , ( 'Michael' , 'Valley 345' ) , ( 'Sandy' , 'Ocean blvd 2' ) , ( 'Betty' , 'Green Grass 1' ) , ( 'Richard' , 'Sky st 331' ) , ( 'Susan' , 'One way 98' ) , ( 'Vicky' , 'Yellow Garden 2' ) , ( 'Ben' , 'Park Lane 38' ) , ( 'William' , 'Central st 954' ) , ( 'Chuck' , 'Main Road 989' ) , ( 'Viola' , 'Sideway 1633' ) ] # 列表 cursor . executemany ( sql , val ) db . commit ( ) db . close ( ) print ( cursor . rowcount , "was inserted." ) def sfzh_sql_insert(): conn = py mysql .connect(host='172.31.100.11', user='root', password="root",