获取Tushare每日行情数据

获取tushare每日行情数据

import tushare as ts
from sqlalchemy import create_engine
import psycopg2
import datetime
import pandas as pd

def query_sql(sql):
    conn = psycopg2.connect(database='quant', host='10.211.55.20', port='5432', user='kiwi',password='xx')
    conn.set_client_encoding('utf-8')
    cursor = conn.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    return rows

def check_table_exits(table_name):
    sql = "select * from pg_tables where tablename ='{table_name}'".format(table_name=table_name)
    rows = query_sql(sql)
    if len(rows) == 0:
        print("表不存在，需要初始化表")
        return False
    else:
        return True

def last_trade_date(current_date):
    sql = '''
    select to_char(last_trade_date,'yyyymmdd') from ctl_tx_date where trade_date=to_date('{current_date}','yyyymmdd')
    '''.format(current_date=current_date)
    row = query_sql(sql)
    return row[0][0]


current_date = datetime.datetime.now().strftime('%Y%m%d')

engine = create_engine('postgresql+psycopg2://kiwi:xx@10.211.55.20:5432/quant')
# tushare初始化
ts.set_token('xx')
pro = ts.pro_api()

#判断每日行情数据表是否存在，不存在初始化行情数据
lastTradedate = last_trade_date(current_date)
if not check_table_exits('daily'):
    df = pro.daily(start_date='20200101', end_date=lastTradedate)
    df.set_index('trade_date',inplace=True)
    df.to_sql('daily',engine,if_exists='replace')

daily_sql = "select * from daily"
daily_rows = query_sql(daily_sql)
daily_df = pd.DataFrame(daily_rows)
print(daily_df)

输出