Python reads an excel file to generate an SQL file instance,

Python reads an excel file to generate an SQL file instance,

SQL file generation using python to read excel files

After learning python for so long, it was finally used at work. This is to read data from an excel file and write it into the database. This logic is too heavy to be written in java, so this time it is implemented through the python script.

Before that, you need to add an xlrd module for python. This module is used to operate excel files.

In mac, you can use the easy_install xlrd command to automatically install the module.

Import xdrlib, sysimport xlrddef open_excel (file = a.xlsx '): try: data = xlrd. open_workbook (file) # Open the excel file return data failed t Exception, e: print str (e) def excel_table_bycol(file='a.xlsx ', colindex = [0], table_name = 'sheet1 '): data = open_excel (file) table = data. sheet_by_name (table_name) # obtain a page in excel, nrows = table. nrows # obtain the number of rows colnames = table. row_values (0) # obtain the value of the first row and use it as the key. You can adjust the Value list = [] for different excel files. # (1, nrows) indicates the row after the first row, because the first row is usually the header for rownum in range (1, nrows): row = table. row_values (rownum) if row: app ={} for I in colindex: app [str (colnames [I]). encode ("UTF-8")] = str (row [I]). encode ("UTF-8") # enter the data into a dictionary and perform UTF-8 transcoding for the data, because some data is a list encoded in unicode. append (app) # Add the dictionary to the list and return listdef main (): # colindex is an array used to select which column to read, because a small part of excel is usually the tables = excel_table_bycol (colindex = [], table_name = u'areaco De ') file = open ('Channel _ area_code. SQL', 'w') # create an SQL file and enable the write mode for row in tables: if row ['area _ Code']! = '': File. write ("update table_name set para1 = '% s' where para2 =' % s'; \ n" % (row ['para1'], row ['para2 ']) # Write the SQL statement if _ name __= = "_ main _" to the file: main ()

This is not a general python script. You still need to make some adjustments based on the excel file format, but the code is not complicated and the development speed is also fast, which is much easier than using java before.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!