从tushare网站获取股票基本信息并存入数据库中的python源代码

最新推荐文章于 2024-04-24 11:09:01 发布

dataat

最新推荐文章于 2024-04-24 11:09:01 发布

阅读量842

点赞数

分类专栏： python tushare 股票文章标签： python 量化交易量化投资 python python 金融量化

本文链接：https://blog.csdn.net/booklijian/article/details/107051421

版权

python 同时被 3 个专栏收录

46 篇文章 1 订阅

订阅专栏

tushare

3 篇文章 0 订阅

订阅专栏

股票

3 篇文章 1 订阅

订阅专栏

要对股票进行分析，需要关注股票的基本信息，例如上市时间、市值，所属行业等等，本文以从tushare网站获取相关信息为例，展示如何获取并存入到mysql数据库中，闲言少叙，直接上代码：

#coding:utf8
import tushare as ts
import pandas as pd
from sqlalchemy import create_engine
import pymysql
import configparser
import time

startTime=int(time.time())
# print('StartTime'+str(startTime))
# print(type(startTime))
conf = configparser.ConfigParser()
conf.read('config.ini')
userName = conf['db']['name']
password= conf['db']['password']
dbHost = conf['db']['host']
dbPort = conf['db']['port']
dbName = conf['db']['dbname']
tsToken = conf['tushare']['token']

dbConnect=create_engine('mysql+pymysql://'+userName+":"
    +password+"@"+dbHost+":"+dbPort+"/"+dbName+"?charset=utf8")
conn = pymysql.connect(host=dbHost,user=userName,
    password=password,database=dbName,charset='utf8')
cursor=conn.cursor()
ts.set_token(tsToken)

cursor.execute("drop table if exists basiclist")
cursor.execute("drop table if exists stock_company")
cursor.execute("drop table if exists tmp_basicInfo")
conn.commit()

pro=ts.pro_api()
#the basic information of stock
print("Getting the basic Information with stock_basic-------")
tmpSql='ts_code,symbol,name,area,industry,fullname,enname,market,' \
       'exchange,curr_type,list_status,list_date,delist_date,is_hs'
data1=pro.stock_basic(exchange='',list_status='L',fields=tmpSql)
data2=pro.stock_basic(exchange='',list_status='D',fields=tmpSql)
data=data1.append(data2)
data3=pro.stock_basic(exchange='',list_status='P',fields=tmpSql)
data=data.append(data3)
pd.io.sql.to_sql(data,'basiclist',dbConnect,schema='stocks', if_exists='replace',index=True)

print("Getting the company Information with stock_company-------")
#the basic information of stock company
tmpSql='ts_code,exchange,chairman,manager,secretary,reg_' \
       'capital,setup_date,province,city,introduction,website,' \
       'email,office,employees,main_business,business_scope'
df1 = pro.stock_company(exchange='SZSE',fields=tmpSql)
df2 = pro.stock_company(exchange='SSE',fields=tmpSql)
df=df2.append(df1)
pd.io.sql.to_sql(df,'stock_company',dbConnect,schema='stocks',
                 if_exists='replace',index=True)

print("drop table if exists tmp_basicInfo")
cursor.execute("drop table if exists tmp_basicInfo")
print("create tmp table and insert data")
tmpSql="create table tmp_basicInfo  select a.ts_code," \
       "a.symbol,a.name,a.area,a.industry,a.fullname,a.enname," \
       "a.market,a.curr_type,a.list_status,a.list_date," \
       "a.delist_date,a.is_hs,b.chairman,b.manager,b.secretary," \
       "b.reg_capital,b.setup_date,b.province,b.city,b.introduction," \
       "b.website,b.email,b.office,b.employees,b.main_business," \
       "b.business_scope from basiclist as a  left join stock_company" \
       " as b on a.ts_code =b.ts_code "
cursor.execute(tmpSql)
print("delete from basicInfo")
cursor.execute("delete from basicInfo")
print("insert the new data into basicInfo")
tmpSql="insert into basicInfo(ts_code,symbol,name,area,industry," \
       "fullname,enname,market,curr_type,list_status,list_date," \
       "delist_date,is_hs,chairman,manager,secretary,reg_capital," \
       "setup_date,province,city,introduction,website,email,office," \
       "employees,main_business,business_scope) select ts_code," \
       "symbol,name,area,industry,fullname,enname,market,curr_type," \
       "list_status,list_date,delist_date,is_hs,chairman,manager," \
       "secretary,reg_capital,setup_date,province,city,introduction," \
       "website,email,office,employees,main_business,business_scope " \
       " from tmp_basicInfo"
cursor.execute(tmpSql)
cursor.execute("update basicInfo set employees=0 where employees is NULL")
cursor.execute("update basicInfo set reg_capital=0 where reg_capital is NULL")
cursor.execute("drop table if exists basiclist")
cursor.execute("drop table if exists stock_company")
cursor.execute("drop table if exists tmp_basicInfo")
conn.commit()
endTime=int(time.time())
strSql="insert into crontablog(name,start,end,timelong) value('basicInfo',"+str(startTime)+","+str(endTime)+","+str(endTime-startTime)+")"
print(strSql)
cursor.execute(strSql)
conn.commit()
cursor.close()
conn.close()

-------------------- 正文到此结束------------------------

推荐一个公众号：健哥聊量化，会持续推出股票相关基础知识，以及python实现的一些基本的分析代码。欢迎大家关注，二维码如下：

Ã¥Â¥Ã¥Â¥Ã¨Ã©Ã¥