import tushare as ts

df=ts.get_stock_basics()

def get_stock_basics():

    """

        获取沪深上市公司基本情况

    Return

    --------

    DataFrame

               code,代码

               name,名称

               industry,细分行业

               area,地区

               pe,市盈率

               outstanding,流通股本

               totals,总股本(万)

               totalAssets,总资产(万)

               liquidAssets,流动资产

               fixedAssets,固定资产

               reserved,公积金

               reservedPerShare,每股公积金

               eps,每股收益

               bvps,每股净资

               pb,市净率

               timeToMarket,上市日期

    """

    request = Request(ct.ALL_STOCK_BASICS_FILE)

    text = urlopen(request, timeout=10).read()

    text = text.decode('GBK')

    text = text.replace('--', '')

    df = pd.read_csv(StringIO(text), dtype={'code':'object'})

    df = df.set_index('code')

    return df

P_TYPE = {'http': 'http://', 'ftp': 'ftp://'}



DOMAINS = {'sina': 'sina.com.cn', 'sinahq': 'sinajs.cn',

           'ifeng': 'ifeng.com', 'sf': 'finance.sina.com.cn',

           'vsf': 'vip.stock.finance.sina.com.cn', 

           'idx': 'www.csindex.com.cn', '163': 'money.163.com',

           'em': 'eastmoney.com', 'sseq': 'query.sse.com.cn',

           'sse': 'www.sse.com.cn', 'szse': 'www.szse.cn',

           'oss': '218.244.146.57', 'idxip':'115.29.204.48',

           'shibor': 'www.shibor.org', 'mbox':'www.cbooo.cn'}

request = Request(ct.REPORT_URL%(ct.P_TYPE['http'], ct.DOMAINS['vsf'], ct.PAGES['fd'],year, quarter, pageNo, ct.PAGE_NUM[1]))

text = urlopen(request, timeout=10).read()

        df=ts.get_report_data(2016,4)

        #第四季度就是年报

# -*-coding=utf-8-*-

#数据库的操作

'''

http://30daydo.com

weigesysu@qq.com

'''

import sqlite3, time, datetime



__author__ = 'rocky'





class SqliteDb():





    def __init__(self,dbtable):

        '''

        self.today = time.strftime("%Y-%m-%d")

        self.DBname = self.today + '.db'

        self.conn = sqlite3.connect(self.DBname)

        '''

        today = time.strftime("%Y-%m-%d")

        DBname = today + '.db'

        self.conn = sqlite3.connect(DBname)

        self.dbtable=dbtable

        create_tb = "CREATE TABLE %s (date varchar(10),id varchar(6), name varchar(30), p_change REAL,turnover REAL);" %self.dbtable

        self.conn.execute(create_tb)

        self.conn.commit()



    def store_break_high(self,price_high_data):



        #data 是创新高的个股信息  dataframe

        #print today

        #create_tb = 'CREATE TABLE STOCK (date TEXT,id text PRIMARY KEY, p_change REAL,turnover REAL);'



        #conn.commit()

        #print "(%s,%s,%f,%f)" %(price_high_data[0], price_high_data[1], price_high_data[2], price_high_data[3])

        insert_data_cmd = "INSERT INTO %s(date,id,name,p_change,turnover) VALUES(\"%s\",\"%s\",\"%s\",%f,%f);" %(self.dbtable,price_high_data[0], price_high_data[1], price_high_data[2], price_high_data[3],price_high_data[4])

        self.conn.execute(insert_data_cmd)

        #self.conn.execute('INSERT INTO STOCK(date,id,name,p_change,turnover) VALUES(?,?,?,?,?)',(price_high_data[0], price_high_data[1], price_high_data[2], price_high_data[3],price_high_data[4]))

        self.conn.commit()





    def close(self):

        self.conn.close()

#-*-coding=utf-8-*-

__author__ = 'rocky'

'''

http://30daydo.com

weigesysu@qq.com

'''

#获取破指定天数内的新高 比如破60日新高

import tushare as ts

import datetime

info=ts.get_stock_basics()



def loop_all_stocks():

    for EachStockID in info.index:

         if is_break_high(EachStockID,60):

             print "High price on",

             print EachStockID,

             print info.ix[EachStockID]['name'].decode('utf-8')







def is_break_high(stockID,days):

    end_day=datetime.date(datetime.date.today().year,datetime.date.today().month,datetime.date.today().day)

    days=days*7/5

    #考虑到周六日非交易

    start_day=end_day-datetime.timedelta(days)



    start_day=start_day.strftime("%Y-%m-%d")

    end_day=end_day.strftime("%Y-%m-%d")

    df=ts.get_h_data(stockID,start=start_day,end=end_day)



    period_high=df['high'].max()

    #print period_high

    today_high=df.iloc[0]['high']

    #这里不能直接用 .values

    #如果用的df【：1】 就需要用.values

    #print today_high

    if today_high>=period_high:

        return True

    else:

        return False



loop_all_stocks()

pandas.DataFrame.to_sql

DataFrame.to_sql(name, con, flavor=None, schema=None, if_exists='fail', index=True, index_label=None,chunksize=None, dtype=None)[source]

Write records stored in a DataFrame to a SQL database.

Parameters:

name : string

Name of SQL table

con : SQLAlchemy engine or DBAPI2 connection (legacy mode)

Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.

flavor : ‘sqlite’, default None

DEPRECATED: this parameter will be removed in a future version, as ‘sqlite’ is the only supported option if SQLAlchemy is not installed.

schema : string, default None

Specify the schema (if database flavor supports this). If None, use default schema.

if_exists : {‘fail’, ‘replace’, ‘append’}, default ‘fail’

fail: If table exists, do nothing.
replace: If table exists, drop it, recreate it, and insert data.
append: If table exists, insert data. Create if does not exist.

index : boolean, default True

Write DataFrame index as a column.

index_label : string or sequence, default None

Column label for index column(s). If None is given (default) and index is True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex.

chunksize : int, default None

If not None, then rows will be written in batches of this size at a time. If None, all rows will be written at once.

dtype : dict of column name to SQL type, default None

Optional specifying the datatype for columns. The SQL type should be a SQLAlchemy type, or a string for sqlite3 fallback connection.

import sqlite3

db=sqlite3.connect("testdb.db")

df = ts.get_k_data('300333',start='2016-01-01',end='2016-12-28')

df.to_sql("newtable",db,flavor='sqlite')

excel中后缀为csv和xls，二者区别如下：
1、xls 文件就是Microsoft excel电子表格的文件格式。
2、csv是最通用的一种文件格式，它可以非常容易地被导入各种PC表格及数据库中。 此文件，一行即为数据表的一行。生成数据表字段用半角逗号隔开。
csv是文本文件,用记事本就能打开，XLS是二进制的文件只有用EXCEL才能打
csv文件是以逗号为分隔符号，将各字段列分离出的一种ASCII文件。
csv(*.csv) 文件格式只能保存活动工作表中的单元格所显示的文本和数值。工作表中所有的数据行和字符都将保存。
数据列以逗号分隔，每一行数据都以回车符结束。如果单元格中包含逗号，则该单元格中的内容以双引号引起。

stock_info=ts.get_stock_basics()

def get_all_stock_id():

    #获取所有股票代码

    for i in stock_info.index:

        print i

#-*-coding=utf-8-*-

__author__ = 'rocky'

import pandas as pd

import numpy as np

def excel_op():

    df=pd.read_excel("mystock.xls")

    print df.head()



excel_op()

df = pd.read_csv('test.csv', chunksize=10000)

for chunk in df:

    # TODO: process the chunk as a normal DataFrame

    for index,row in df.iterrows():

        print row[u'代码'],row[u'资金发生数']