python—使用dataframe选择要从mysql导出到excel的列

bybem2ql  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(339)

使用 mysql.connector ,我用python做了一个mysql查询,由 pd.read_sql_query ,代码如下:

import pandas as pd
import mysql.connector
db_con= mysql.connector(user=..., pass=..., host:..., database='db')
query= ("""SELECT tagnumber,value,content,tagname 
            FROM db.table
            WHERE tagnumber>=%d AND tagname='XXX' AND content!="";""" % (2))

df=pd.read_sql_query(query,db_con,index_col=['value','content'])
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()

不过,虽然它为我正确地索引了“value”和“content”,但当我将查询导出到excel文件时,它还会在excel文件中显示我不需要的“tagnumber”和“tagname”列。我只想打印我想在excel工作表中显示的列(即“值”和“内容”)。在这种情况下,如何选择使用sql\U read将哪些列写入excel文件?

5us2dqdw

5us2dqdw1#

按以下方式编写sql查询:

query= ("""SELECT value,content
            FROM db.table
            WHERE tagnumber>=%d AND tagname='XXX' AND content!="";""" % (2))
lfapxunr

lfapxunr2#

如果不想更改查询,请执行其他更正

writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
        df.filter(items=['value','content']).to_excel(writer, sheet_name='Sheet1', index = False)
        writer.save()

相关问题