python 学习（三）数据库、EXCEL处理

xlrd,xlwt import pymysql class Mysql: def __init__(self): self.content = pymysql.Connect( host=‘192.168.207.63‘, # mysql的主机ip port=32774, # 端口 user=‘root‘, # 用户名 passwd=‘123456‘, # 数据库密码 db=‘test‘, # 数据库名 charset=‘utf8‘, # 字符集 ) self.cursor = self.content.cursor() def query(self): sql = "select grade,teacher,location from grade;" self.cursor.execute(sql) for row in self.cursor.fetchall(): print("grade:%s\t teacher:%s\t location:%s" % row) rows.append(row) print(f"一共查找到：{self.cursor.rowcount}") def readtable(self): sql = """select * from stu;""" self.cursor.execute(sql) rows= [] for row in self.cursor.fetchall(): print(row) rows.append(row) print(f"一共查找到：{self.cursor.rowcount}") return rows def insert(self,grade,teacher,location): sql = """INSERT INTO grade(grade,teacher,location) VALUES(%s,%s,%s)""" values=(int(grade),str(teacher),str(location)) try: self.cursor.execute(sql,values) self.content.commit() # print("插入成功") except: self.content.rollback # print("插入失败") def end(self): self.cursor.close() self.content.close() class Exceltable: def __init__(self): self.book = xlrd.open_workbook("grade.xls") #文件名，把文件与py文件放在同一目录下 self.sheet = self.book.sheet_by_name("Sheet1") #execl里面的worksheet1 def readrow(self,row): #ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error row_values = [] for i in range(self.sheet.ncols): cell=self.sheet.cell_value(row,i) ctype = self.sheet.cell(row,i).ctype if ctype == 2 and cell%1== 0: cell =int(cell) elif ctype == 3: # 转成datetime对象 date = datetime(*xldate_as_tuple(cell, 0)) cell = date.strftime(‘%Y/%d/%m %H:%M:%S‘) elif ctype == 4: cell = True if cell == 1 else False row_values.append(cell) return row_values def readall(self): #ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error all_content = [] for i in range(1,self.sheet.nrows): row_values = [] for j in range(self.sheet.ncols): cell=self.sheet.cell_value(i,j) ctype = self.sheet.cell(i,j).ctype if ctype == 2 and cell%1== 0: cell =int(cell) elif ctype == 3: # 转成datetime对象 date = datetime(*xldate_as_tuple(cell, 0)) cell = date.strftime(‘%Y/%d/%m %H:%M:%S‘) elif ctype == 4: cell = True if cell == 1 else False row_values.append(cell) all_content.append(row_values) return all_content def readmutirow(self,start,end): muti_content = [] for i in range(start,end+1): row_values = [] for j in range(self.sheet.ncols): cell=self.sheet.cell_value(i,j) ctype = self.sheet.cell(i,j).ctype if ctype == 2 and cell%1== 0: cell =int(cell) elif ctype == 3: # 转成datetime对象 date = datetime(*xldate_as_tuple(cell, 0)) cell = date.strftime(‘%Y/%d/%m %H:%M:%S‘) elif ctype == 4: cell = True if cell == 1 else False row_values.append(cell) muti_content.append(row_values) return muti_content def writrow(self,rows): # 创建Excel工作薄 myWorkbook = xlwt.Workbook() # 3. 添加Excel工作表 mySheet = myWorkbook.add_sheet(‘test‘) # 4. 写入数据 #myStyle = xlwt.easyxf(‘font: name Times New Roman, color-index red, bold on‘, num_format_str=‘#,##0.00‘) #数据格式 for i in range(len(rows)): for j in range(len(rows[i])): mySheet.write(i, j, rows[i][j]) #写入A3，数值等于1 #5. 保存 myWorkbook.save(‘Wtest.xls‘) if __name__ == ‘__main__‘: mysql = Mysql() myexcel=Exceltable() #读单行 #cells=myexcel.readrow(3) #print(cells) #读整个表 #all_content = myexcel.readall() #for cells in all_content: # print(cells) # mysql.insert(cells[0],cells[1],cells[2]) #读指定表 #muti_content=myexcel.readmutirow(1,3) #for cells in muti_content: # print(cells) # mysql.insert(cells[0],cells[1],cells[2]) #查询数据库 #mysql.query() #将数据库内容写入EXCEL rows=mysql.readtable() myexcel.writrow(rows) mysql.end()

上面程序存在缺陷：1.处理数据量小，不适合大量数据处理，2.使用xlwt库写excel只支持.xls且仅支持新建，后续学习PANDAS后补充此章内容

python 学习（三）数据库、EXCEL处理

标签：_for   创建   utf8   mat   select   excel   大量数据   insert   cut