2020-12-03

最新推荐文章于 2024-09-27 17:27:59 发布

Lirengqiang

最新推荐文章于 2024-09-27 17:27:59 发布

阅读量57

点赞数

文章标签： mysql python sql 数据库 json

本文链接：https://blog.csdn.net/Lirengqiang/article/details/110532693

版权

如何用python连接MySQL,并处理数据,导入Excel表格中


import pymysql #导入mysql数据库
import pandas as pd
import numpy as np
class customerID ():
    __files__ = '文件名.xlsx' #定义Excel文件名

    def init_data(self):      #数据库取数
        sql1 = """ 
             select
		            customerId
		           ,appId
             from table1
            """
        con = pymysql.connect("数据库链接", "账号", "密码") #数据库账号密码
        df1 = pd.read_sql(sql1, con) #连接数据库,获取想要的数据

        df1_customerID = df1['customerId'].tolist() #处理数据
        df1_customerID = ','.join(["'%s'" % i for i in df1_customerID])
        # return df1
        sql11 = """
            select 
		           customerId
		           from table2
		           where customerId in ({df1_customer})
                """.format(df1_customer=df1_customerID)
        df2 = pd.read_sql(sql11, con)
        return df2
    def write_excel_pd(self):   #将数据导入Excel表格中
        df2 = self.init_data()
        writer = pd.ExcelWriter(self.__files__)
        df2.to_excel(excel_writer=writer, sheet_name="数据总", index=False)
        writer.save()
        writer.close()
        print("文件生成成功")


if __name__ == "__main__":
    Data = customerID()
    Data.write_excel_pd()