Import Excel data into a database using Python

• Because of the need for data processing, import Excel data into the database, record the process.
• Libraries used: XLRD and Pymysql (if you need to write to Excel you can use XLWT)
• Drop the code directly, use Python3, the comments are clear.

• Import xlrdimport pymysql# import importlib# importlib.reload (SYS) #出现呢reload错误使用def open_excel (): Try:book = x    Lrd.open_workbook ("xx.xlsx") #文件名, put the file and the py file in the same directory Except:print ("Open Excel file Failed!") Try:sheet = book.sheet_by_name ("sheet name") #execl里面的worksheet1 return sheet except:print ("Locat e worksheet in Excel failed! ") #连接数据库try: db = Pymysql.connect (host= "127.0.0.1", user= "root", passwd= "xxx", db= "xxx", charset= ' UTF 8 ') Except:print ("Could not connect to MySQL server") def search_count (): cursor = db.cursor () select = "SELECT Co UNT (ID) from XXXX "#获取表中xxxxx记录数 cursor.execute (select) #执行sql语句 Line_Count = Cursor.fetchone () print (line_count[ 0]) def insert_deta (): sheet = open_excel () cursor = Db.cursor () for I in range (1, sheet.nrows): #第一行是标题名, the name of the field in the corresponding table         So starting with the second line, the computer counts as 0, so the value is 1 name = Sheet.cell (i,0). Value #取第i行第0列 data = Sheet.cell (i,1). value# take the 1th column of line I, and so on PriNT (name) print (data) value = (name,data) print (value) sql = "INSERT into XXX (name,data) VALUES ( %s,%s) "Cursor.execute (sql,value) #执行sql语句 db.commit () cursor.close () #关闭连接insert_deta () db.close () #关闭数据pr int ("OK")

  XXX in the self-modification of their own name.

• Description: For irregular cells, such as merged cells, a null value is taken.
• Have the opportunity to write the database to Excel paste it up.
• In fact, there are a lot of database tools to solve this problem directly, note the character conversion format is good.
• Above.

Import Excel data into a database using Python