Python将excel导入到mysql中

encoding: utf-8 # !/usr/bin/python import sys import types import datetime import MySQLdb import xlrd from PyQt4 import QtGui class MainWindow(QtGui.QDialog): def __init__(self, parent=None): QtGui.QDialog.__init__(self) self.resize(450, 250) self.setWindowTitle(u‘导入员工信息‘) self.excel_file_le = QtGui.QLineEdit(self) self.excel_file_le.setDisabled(True) self.db_url_le = QtGui.QLineEdit(self) self.db_url_le.setPlaceholderText(u"数据库链接") self.db_url_le.setText("localhost") self.db_name_le = QtGui.QLineEdit(self) self.db_name_le.setPlaceholderText(u"数据库名") self.db_name_le.setText("sys") self.db_user_le = QtGui.QLineEdit(self) self.db_user_le.setPlaceholderText(u"用户名") self.db_user_le.setText("root") self.db_pwd_le = QtGui.QLineEdit(self) self.db_pwd_le.setPlaceholderText(u"密码") self.db_pwd_le.setText("root") layout = QtGui.QVBoxLayout() layout.addWidget(self.excel_file_le) layout.addWidget(self.db_url_le) layout.addWidget(self.db_name_le) layout.addWidget(self.db_user_le) layout.addWidget(self.db_pwd_le) spacer_item = QtGui.QSpacerItem(20, 48, QtGui.QSizePolicy.Minimum, QtGui.QSizePolicy.Expanding) layout.addItem(spacer_item) self.btnSelectExcelFile = QtGui.QPushButton(u‘选择文件‘, self) self.btnSelectExcelFile.clicked.connect(self.openFile) self.btnTestConnection = QtGui.QPushButton(u‘测试连接‘, self) self.btnTestConnection.clicked.connect(self.test_connection) self.btnConfirmImport = QtGui.QPushButton(u‘确认导入‘, self) self.btnConfirmImport.clicked.connect(self.import_excel_file) button_layout = QtGui.QHBoxLayout() spacer_item2 = QtGui.QSpacerItem(40, 20, QtGui.QSizePolicy.Expanding, QtGui.QSizePolicy.Minimum) button_layout.addItem(spacer_item2) button_layout.addWidget(self.btnSelectExcelFile) button_layout.addWidget(self.btnTestConnection) button_layout.addWidget(self.btnConfirmImport) layout.addLayout(button_layout) self.setLayout(layout) def test_connection(self): try: url_list = ["hosts=" + self.db_url_le.displayText(), "user=" + self.db_user_le.displayText(), "passwd=" + self.db_pwd_le.displayText(), "db=" + self.db_name_le.displayText(), "charset=‘utf8‘"] db = MySQLdb.connect(‘,‘.join(url_list).decode("utf-8")) # QtGui.QMessageBox. except Exception, e: QtGui.QMessageBox.critical(self, u‘错误‘, u‘连接失败!‘) print e def import_excel_file(self): if not self.excel_file_le.displayText(): QtGui.QMessageBox.critical(self, u‘错误‘, u‘请选择文件‘) else: self.read_excel_data(self, unicode(self.excel_file_le.displayText())) def openFile(self): file_list = QtGui.QFileDialog.getOpenFileNameAndFilter(self, u"选择导入文件", "", "Excel(*.xls)") if (file_list[0]): self.excel_file_le.setText(unicode(file_list[0])) @staticmethod def read_excel_data(self, path): excel_workbook = xlrd.open_workbook(path) sheet_data = excel_workbook.sheets()[0] ncols = sheet_data.ncols db = MySQLdb.connect("localhost", "root", "111111", "sys", charset=‘utf8‘) cursor = db.cursor() table_name = "pq_dw_info" cursor.execute("desc " + table_name) table_desc_list = cursor.fetchall() table_desc_json = {} for row in table_desc_list: table_desc_json[row[0].encode("utf-8").lower()] = row first_row_data = sheet_data.row_values(0) excute_header_sql = "insert into " + table_name + "(" header_col_list = [] header_col_type_list = [] for i in range(0, ncols): header_col_type_list.append(table_desc_json[(first_row_data[i].lower().encode("utf-8"))]) if i == 0: header_col_list.append("dw_code") continue header_col_list.append(first_row_data[i]) excute_header_sql += ‘,‘.join(header_col_list) + ",CREATED_BY_USER,CREATED_OFFICE,CREATED_DTM_LOC,RECORD_VERSION) values(" data_rows = [] __s_date = datetime.date(1899, 12, 31).toordinal() - 1 for i in range(1, sheet_data.nrows): row_data_list = sheet_data.row_values(i) # find data exsits count_sql = "select count(1) from " + table_name + " where dw_code=‘" + str(int(row_data_list[0])) + "‘" cursor.execute(count_sql) count_result = cursor.fetchall() if count_result[0][0] != 0: continue sql = excute_header_sql for j in range(0, ncols): cell_data = row_data_list[j] column_type = header_col_type_list[j][1] column_default_value = header_col_type_list[j][4] if j == 0: sql += "‘" + str(int(cell_data)) + "‘,"‘‘ continue if (column_type.startswith("int") or column_type.startswith("decimal") or column_type.startswith( "bigint")): if cell_data: sql += unicode(cell_data) + "," elif column_default_value: sql += unicode(column_default_value) + "," else: sql += "null," elif (column_type.startswith("varchar") or column_type.startswith("char") or column_type.startswith("longtext")): if type(cell_data) is types.FloatType: sql += "‘" + str(int(cell_data)) + "‘,"‘‘ else: sql += "‘" + unicode(cell_data) + "‘,"‘‘ elif (column_type.startswith("date")): if cell_data: sql += "str_to_date(‘" + datetime.date.fromordinal( __s_date + int(cell_data)).strftime("%Y-%m-%d") + "‘,‘%Y-%m-%d‘),"‘‘ else: sql += "null," elif (column_type.startswith("datetime")): if cell_data: sql += "str_to_date(‘" + datetime.date.fromordinal( __s_date + int(cell_data)).strftime("%Y-%m-%d") + "‘,‘%Y-%m-%d‘),"‘‘ else: sql += "null," else: sql += "‘" + unicode(cell_data) + "‘,"‘‘ sql += "‘admin‘,‘admin‘,sysdate(),0)" # print sql cursor.execute(sql) try: db.commit() db.close() except Exception, e: db.rollback() QtGui.QMessageBox.critical(self, u‘错误‘, u‘导入失败‘) print e print "import success!" app = QtGui.QApplication(sys.argv) main = MainWindow() main.show() sys.exit(app.exec_())

小学生水平，偶尔拿来玩玩，现在公司项目都是安装在Windows上的，好想拿python当运维工具啊，看到很多小伙伴，使用python登录n台服务器，各种自动化脚本，羡慕。。。

Python将excel导入到mysql中

标签：