数据处理模块:shujuchuli.py
- 类名
shujuchuli
- 初始函数
__init__(self, user, password, dsn, database)
- 下载数据
get_url(self)
- 调用数据
get_sql(self,code)
# -*- coding: utf-8 -*- class shujuchuli: def __init__(self, user, password, dsn, database): self.user = user self.password = password self.dsn = dsn self.database = database import sqlalchemy self.engine = sqlalchemy.create_engine('mssql+pyodbc://'+user+':'+password+'@'+dsn) def get_url(self): import tushare import pandas codelist = pandas.DataFrame((tushare.get_today_all())['code']) codelist.to_sql('codelist', self.engine, if_exists='replace', index=False) for i in range(0, len(codelist)): stockdata = tushare.get_hist_data(codelist['code'][i]) index = list(stockdata['open'].index) stockdata['date'] = pandas.Series(index, index) pandas.DataFrame(stockdata, index) stockdata.to_sql(codelist['code'][i], self.engine, if_exists='replace', index=False) def get_sql(self, code): connection = self.engine.connect() result = connection.execute('select * from ['+self.database+'].[dbo].['+code+']') stockdata = result.fetchall() return stockdata
调用方法:
Python 使用 pyodbc 方式连接数据库比较简单,但需先设置系统 DSN ,方法为(控制面板 - ODBC - 系统 DSN)
# -*- coding: utf-8 -*- import sys sys.path.append('C:\WinPython-32bit-3.4.4.2\my_WorkSpace1\shujuchuli.py') import shujuchuli test = shujuchuli.shujuchuli('sa', '123456', 'XiTongDSN', 'ShuJuKu') test.get_url() test.get_sql('002237')
附图: