https://tushare.pro/ 从该网站利用python下载数据保存到本地Oracle数据库

 

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

class Oracle(object):
def __init__(self, user_name, password, host, instance):
self._conn = cx_Oracle.connect('%s/%s@%s/%s' % (user_name, password, host, instance))
self.cursor = self._conn.cursor()
print('[提示]: 连接成功')

def query_all(self, sql):
self.cursor.execute(sql)
return self.cursor.fetchall()

def query_one(self, sql):
self.cursor.execute(sql)
return self.cursor.fetchone()

def query_by(self, sql, params={}):
self.cursor.execute(sql, params)
return self.cursor.fetchall()

def insert(self, sql, params={}):
self.cursor.execute(sql, params)
self._conn.commit()

def update(self, sql, params={}):
self.cursor.execute(sql, params)
self._conn.commit()

def delete(self, sql):
self.cursor.execute(sql)
self._conn.commit()
def procedure(self, sql, params={}):
self.cursor.execute(sql, params)
self._conn.commit()

'''

下面这一行变量token的值，每个人都不一样, 需要每个人去修改，需要根据您在https://tushare.pro/  网站注册后，

然后点击个人资料查看自己的token值是多少，需要完善填写个人资料积累积分满120分才能调用以下数据接口pro.stock_basic

'''

token = 'ksdjfljlfjdslfjdslfjadljldsjlkdjllkjfskljfldsjljkkdjl'
ts.set_token(token)
pro = ts.pro_api()

def pro1_stock_basic():
print('pro1_stock_basic begin')
p_fields = 'ts_code,symbol,name,area,industry,fullname,enname,market,exchange,curr_type,list_status,list_date,delist_date,is_hs'
df = pro.stock_basic(exchange='', list_status='L', fields=p_fields)
print(2)
#股票数据保存到表 hsgt_top10
engine = create_engine('oracle+cx_oracle://scott:abc123@192.168.1.88:1521/orcl')
cnx = engine.connect()
##先删除当日数据
##strSql1 = 'truncate table pro1_stock_basic'

print(3)
##cnx.execute(strSql1)
##print(4)
df.to_sql('pro1_stock_basic', cnx, if_exists='append', chunksize=500)
print('pro1_stock_basic end')

cnx.close()
print(5)
return df

with open('C:\\python_run_log\\pro1_stock_basic.txt', 'a', encoding='utf-8') as f:
time1 = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
time1_str = 'pro1_stock_basic 运行开始时间：' + time1
f.write('\n')
f.write(time1_str)

if __name__ == '__main__':
test = Oracle('scott', 'abc123', '127.0.0.1:1521', 'orcl')
sql = "delete scott.pro1_stock_basic"
test.delete(sql)
print('已经成功删除表的所有内容。')

df = pro1_stock_basic()
print('已经成功把数据保存到表pro1_stock_basic。')

with open('C:\\python_run_log\\pro1_stock_basic.txt', 'a', encoding='utf-8') as f:
time1 = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
time1_str = 'pro1_stock_basic 运行结束时间：' + time1
f.write('\n')
f.write(time1_str)
f.write('\n')

 

#Oracle数据库表建立的代码如下

'''

-- Create table
create table PRO1_STOCK_BASIC
(
"index" NUMBER,
ts_code VARCHAR2(15),
symbol VARCHAR2(10),
name VARCHAR2(50),
area VARCHAR2(15),
industry VARCHAR2(30),
fullname VARCHAR2(1000),
enname VARCHAR2(1000),
cnspell VARCHAR2(1000),
market VARCHAR2(10),
exchange VARCHAR2(10),
curr_type VARCHAR2(100),
list_status VARCHAR2(5),
list_date VARCHAR2(10),
delist_date VARCHAR2(10),
is_hs VARCHAR2(5),
act_name VARCHAR2(1000),
act_ent_type VARCHAR2(1000)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

'''

 

 

以下为运行结果显示：

 

以下为运行python时的pro1_stock_basic.txt日志文件内容，以便监控代码是否成功运行，