小知识:python 与 sql 交互
来都来了,不关注下吗?~~
hello大家好,今天给大家科普一个比较常用的获取数据技能。
不知道你在工作或者学习中有没有这样的情况,经常需要手动跟踪且更新一些数据,举个粒子。
比如每周都要更新某些基金的业绩。如果是一只还好,手动导出就好,但是如果是100个呢?尤其是当100个还要合在一个表中....
又或者是让你先在基金中识别出哪些是“量化基金”,然后获取这些基金的数据。怎么破? 当然了这个稍微有点难,那我们可能需要先爬虫去识别哪些基金是量化基金(基金简介里面出现“量化投资团队”等关键字眼初步认为是量化基金),再去导出。今天在这里我们只先说第一种情况。
这些数据当然是从网上可以爬下来的,但是如果不用爬虫可以解决问题,我们就不爬虫,因为爬虫比较复杂,可能需要对前端的知识也有要求,那么我们首选API。今天介绍一下我们怎么去从数据库导出数据,未来会加一些怎么简单粗暴的直接从网页抓取想要的数据~~
API的话就需要拿到这个数据库接口的账号和密码,然后从数据库用mysql直接导出到python进行处理,最后保存到excel文件,如果有特殊要求时还可以按要求合并到一个excel中。
那么就拿导出基金的例子来说,比如我想导出五个产品历年来的业绩净值、基金名称和日期,并且按指数的date合到一个excel表中。
首先把这些产品名称写进list里面,一定要注意和database里一字不差,必须准确,核对核对!然后从db里面导出我们想要的字段,比如fund_name, price_date, cumulative_nav_withdrawal,这个也是db里面的字段名称,然后看它们分布在哪些table中,必要的时候要用到类似excel里面的vlookup这样的功能,就是把表和表合并,组成一个完整的table。
(ps: 如果基础不好建议补一下sql,很简单的!)
lst = [ "白鹭桃花岛量化对冲一号","衍盛量化精选一期", "念空安皓多策略", "因诺启航1号", "微丰凯旋9号" ]sql = '''SELECT fund_name, price_date, cumulative_nav_withdrawalFROM table1 INNER JOIN table2 ON table.fund_id = table.fund_id WHERE %sORDER BY price_date ''' % " or ".join(["fund_name='"+i+"'" for i in lst ])index_code = "ZYCTAB"
导入要用的包:
import pymysqlimport pandas as pd
host, user, passwd, db, port 都是要根据API去填。
def to_excel(lst, index_code,sql,file_name): # 连接基金的API conn = pymysql.connect(host='XXX', user='XXX', passwd='XXX', db='XXX',port=XX,charset='utf8') data_sql = pd.read_sql(sql, conn) data_sql["price_date"] = pd.to_datetime(data_sql["price_date"]) conn.close() # 连接指数的API conn = pymysql.connect(host='XXX', user='XXX', passwd='XXX', db="XXX", port=XX,charset='utf8') data_index = pd.read_sql("SELECT index_name,statistic_date from table WHERE index_code ='" + index_code + "'", conn) data_index["price_date"] = pd.to_datetime(data_index["price_date"]) conn.close() # 创建一个空的excel文件 pd.DataFrame().to_excel(file_name) # 打开excel writer = pd.ExcelWriter(file_name) # 写入指数 merge_data = data_index.copy() # sheets是要写入的excel工作簿名称列表 for sheet in lst: data_sheet = data_sql[data_sql["fund_short_name"]==sheet] data_sheet = data_sheet.rename(columns={"cumulative_nav_withdrawal": sheet}) merge_data = merge_data.merge(data_sheet[[sheet, "price_date"]], on="price_date", how="outer") data_sheet.to_excel(writer, sheet_name=sheet, index=False) merge_data.to_excel(writer, sheet_name="汇总表", index=False) # 保存writer中的数据至excel writer.save() if __name__ == "__main__": to_excel(lst, index_code, sql,"output.xlsx")
这样就完成了,导出结果是一个excel,多个sheet,请看:
单个:
汇总表:
是不是瞬间工作效率倍增!
当然了,代码还要根据具体场景具体改,但是基本的都在这里啦~~希望可以帮到你哦! (交互那部分代码可以直接copy哦)
今天看到一句话,也送给大家:
再长的路,一步步也能走完,再短的路,不迈开双脚也无法到达。
记得关注喵~~