Bulk import of Excel data into a database using Python

• This is an optimized version of the previous article, compared to a single execution SQL statement, in this article, all the data in Excel is first written to the list of lists
• In the PASS function

  Cursor.executemany (SQL, list)

  Write once to the database

• Import pymysqlimport xlrd "Connection database Args:db_name (database name) returns:db" Def Mysql_link (de_name): try:d b = Pymysql.connect (host= "127.0.0.1", user= "root", passwd= "XXX", D B=de_name, charset= ' UTF8 ') return db Except:print ("Could not connect to MYSQ        L Server ") reads Excel function Args:excel_file (Excel file, directory in py file with directory) Returns:book" Def Open_excel (excel_file): try: Book = Xlrd.open_workbook (excel_file) #文件名, put the file and the py file in the same directory return book Except:print ("Open Excel fi    Le failed! ")" Perform the insert Operation Args:db_name (database name) table_name (table name) Excel_file (Excel filename, place the file in the same directory as the py file) ' Def store_to (db_name,table_name,excel_file): db = Mysql_link (db_name) # Open database connection cursor = Db.cursor () # Create a Cursor object using the cursor () method Cursor book = Open_excel (excel_file) # open Excel File sheets = book.sheet_names () # Get all sheet table names for sheet in sheet   S     SH = book.sheet_by_name (sheet) # Open each table Row_num = sh.nrows print (row_num) list = [] # The definition lists are used to Store data for I in range (1, row_num): # The first line is the title name, and the field name in the corresponding table should start at the second line and the computer will start at 0, so the value is 1 row_data = Sh.row_values (                     i) # Gets the value of Excel by line = (Row_data[0], row_data[1],row_data[2],row_data[3],row_data[4],row_data[5], row_data[6],row_data[7],row_data[8],row_data[9],row_data[10],row_data[11],row_data[12],row_data[13],row_data[ ) List.append (value) # The data is persisted in the list # print (i) sql = "INSERT into" + table_name + "(BJ_SHIJ Ian,bjr_xingbie,anfa_didian, ZB_X,ZB_Y,BJ_CHONGFU,JIEJING_LB_NAME,BAOJING_LB_NAME,BAOJING_LX_NAME,BAOJING_LX_XL _name, Guanxia_qy_name,guanxian_dw_name,anfa_qulu,anfa_xiaoqu,chujing_dw_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s ,%s,%s,%s,%s,%s,%s) "Cursor.executemany (SQL, list) # Execute SQL Statement db.commit () # Submit List.clear () # Empty Lis T print ("Worksheets: "+ Sheet +" has been inserted "+ str (row_num) +" datas! ") Cursor.close () # Close Connection db.close () if __name__ = = ' __main__ ': Store_to (' demo ', ' Demo_yangben ', ' qh.xlsx ')

  You can select the incoming database, and the database table name, Excel file name (Excel files and py files with the same directory, otherwise plus path)

• You can take a look at an article to make a transition.

Bulk import of Excel data into a database using Python