赞
踩
需求:
从数据库中查询出的数据,直接保存到excel表格中的两种办法:
这里从sqlite3中查询出来的数据是这样的:
values 值为:[('31010251001390002578', 'xxx路门前南全东1'), ('31010251001390004176', 'XXX路门南全西'),('31010251001390002577', 'xxx路门前南全东2'),('31010251001390002512', 'xxx路门前南全东567'),('31010251001390002100', 'xxx路门前南全东8'),('31010251001390002999', 'xxx路门前南全东119'),('3101025100139009111', 'xxx路门前南全东11'),...]
values 值为:
[('31010251001390002578', 'xxx路门前南全东1'),
('31010251001390004176', 'XXX路门南全西'),
('31010251001390002577', 'xxx路门前南全东2'),
('31010251001390002512', 'xxx路门前南全东567'),
('31010251001390002100', 'xxx路门前南全东8'),
('31010251001390002999', 'xxx路门前南全东119'),
('3101025100139009111', 'xxx路门前南全东11'),...]
其中每一项是一个元组:
('31010251001390002578', 'xxx路门前南全东1')
把上述数据保存到excel表格的去:
excel保存数据方法:
方法一:excel库 xlsxwriter
-
- ######################excel-1-第一种保存的方法#################################3
- # ###from xlsxwriter.workbook import Workbook
- # ##### excel 读取库
- # # 1.workbook = Workbook('output.xlsx')
- # workbook = Workbook(excelname) #output.xlsx
- # worksheet=workbook.add_worksheet()
- #
- #
- #
- # #############连接sqlite3数据库,运行查询命令,获取查询结构;
- # conn = sqlite3.connect("./datacheck.db")
- # cur = conn.execute(strsql)
- # values = cur.fetchall() # 查询结果集
- # #################values [('31010251001390002578', 'xxx路门前南全东'), ('31010251001390004176', 'XXX路门南全西'),...]
- # ################保存到excel表格中
- #
- # for i,row in enumerate(values):
- # for j,value in enumerate(row):
- # worksheet.write(i,j,value)
- # # print(f"i={i},j={j},value={value}") #i,j,value分别对应的值 i=7842,j=1,value=xxx路口西门全景1 ;i=7842,j=0,value=31011353001910138692
- #
- # workbook.close()
-
- #####################excel-1-第一种保存的方法##################################
- ############################################################################
- cur.close() #游标关闭
- conn.close() #连接关闭
方法2:openpyxl库
- #################excel第二种保存方法:###################################
- wb=openpyxl.Workbook()
- wbsheet=wb.create_sheet("结果")
-
-
-
-
- # 连接sqlite3数据库,运行查询命令,获取查询结构;
- conn = sqlite3.connect("./datacheck.db")
- cur = conn.execute(strsql)
- values = cur.fetchall() #查询结果集
-
- for row in values:
- wbsheet.append(row)
- #print(row)
-
-
-
- wb.save(excelname) #保存文件名称
-
-
- cur.close() #游标关闭
- conn.close() #连接关闭
下面是函数代码:
函数参数:
sqlanylistShare(sqlstement,excelname):
第一个是执行的sql命令,第二个参数是excel表格的名称,例如“xx市服装对比数据.xlsx”
- def sqlanylistShare(sqlstement,excelname):
- #sql指令,用于寻找两个表中国标id相同的信息
- # strsql="select netwl.gbid,netwl.devicename from netwl inner join checkdata on netwl.gbid=checkdata.gbid"
- strsql=sqlstement
-
-
- # 2.excel写库 openpyxl
-
- #################excel第二种保存方法:###################################
- wb=openpyxl.Workbook()
- wbsheet=wb.create_sheet("结果")
-
-
-
-
- # 连接sqlite3数据库,运行查询命令,获取查询结构;
- conn = sqlite3.connect("./datacheck.db")
- cur = conn.execute(strsql)
- values = cur.fetchall() #查询结果集
-
- for row in values:
- wbsheet.append(row)
- print(row)
-
-
-
- wb.save(excelname) #保存文件名称
-
-
- #################excel第二种保存方法:###################################
-
- ######################excel-1-第一种保存的方法#################################3
- # ###from xlsxwriter.workbook import Workbook
- # ##### excel 读取库
- # # 1.workbook = Workbook('output.xlsx')
- # workbook = Workbook(excelname) #output.xlsx
- # worksheet=workbook.add_worksheet()
- #
- #
- #
- # #############连接sqlite3数据库,运行查询命令,获取查询结构;
- # conn = sqlite3.connect("./datacheck.db")
- # cur = conn.execute(strsql)
- # values = cur.fetchall() # 查询结果集
- # #################values [('31010251001390002578', 'xxx路门前南全东'), ('31010251001390004176', 'XXX路门南全西'),...]
- # ################保存到excel表格中
- #
- # for i,row in enumerate(values):
- # for j,value in enumerate(row):
- # worksheet.write(i,j,value)
- # # print(f"i={i},j={j},value={value}") #i,j,value分别对应的值 i=7842,j=1,value=xxx路口西门全景1 ;i=7842,j=0,value=31011353001910138692
- #
- # workbook.close()
-
- #####################excel-1-第一种保存的方法##################################
- ############################################################################
- cur.close() #游标关闭
- conn.close() #连接关闭
csv文件的保存方法:
jieguovalues 值为:
[('31010251001390002578', 'xxx路门前南全东1'),
('31010251001390004176', 'XXX路门南全西'),
('31010251001390002577', 'xxx路门前南全东2'),
('31010251001390002512', 'xxx路门前南全东567'),
('31010251001390002100', 'xxx路门前南全东8'),
('31010251001390002999', 'xxx路门前南全东119'),
('3101025100139009111', 'xxx路门前南全东11'),...]
- jieguovalues = cursor.fetchall()
-
- # sipidfilename = sipid + ".xlsx"
- sipidfilename = sipid + ".csv"
-
- # wb = openpyxl.Workbook()
- # wbsheet = wb.create_sheet("结果")
-
- #########csv文件保存方式###############
- fscv=open(sipidfilename,'w')
- writer=csv.writer(fscv)
-
- for value in jieguovalues:
- writer.writerow(value)
-
- fscv.close()
csv文件保存字典格式数据:
- # 直接字典字段写入到CSV文件中
- # coding:utf-8
- import csv
- data = {'id':'123','name':'anjing','age':'26'}
- with open('123.csv','w')as f:
- fieldnames = {'id','name','age'} # 表头
- writer = csv.DictWriter(f,fieldnames=fieldnames)
- writer.writeheader()
- writer.writerow(data)
如果出现空行的问题,优化
- # coding:utf-8
- import csv
- data = {'id':'123','name':'anjing','age':'26'}
- # 加入参数“enwline=''”
- with open('123.csv','w',newline='')as f:
- fieldnames = {'id','name','age'}
- writer = csv.DictWriter(f,fieldnames=fieldnames)
- writer.writeheader()
- writer.writerow(data)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。