C:\mysql-5.7.20-winx64\bin>mysqld --initialize --user=mysql --console
C:\mysql-5.7.20-winx64\bin>mysqld  -install
C:\mysql-5.7.20-winx64\bin>net start mysql
C:\mysql-5.7.20-winx64\bin>mysql -u root -p
mysql> set character_set_server=utf8;
mysql> alter user root@localhost identified by "12345";
mysql> show variables like 'character%';
mysql> CREATE DATABASE test DEFAULT CHARACTER SET utf8;
mysql> quit


http://fund.eastmoney.com/f10/F10DataApi.aspx?type=lsjz&code=000002&page=1&per=730


# -*- coding: utf-8 -*-
"""
Created on Mon Jan  1 13:50:54 2018

@author: Eric
"""

import xlsxwriter
import requests
import time

def openurl(url):
    try:
        headers = {'User-Agent':'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.81 Safari/537.36'}
        r = requests.get(url,headers = headers)
        r.raise_for_status()
        r.encoding = r.apparent_encoding
        return r.text
    except Exception as e:
        print('Error:',e)

def writexls(fundlist):
    #name=values[1]
    #timenow=time.strftime("%Y-%m-%d", time.localtime())
    filename='fund.xlsx'
    workbook = xlsxwriter.Workbook(filename)
    #第一页sheet
    worksheet1 = workbook.add_worksheet("基金信息")
    worksheet1.set_column('B:B', 16)
    worksheet1.set_column('C:C', 12)
    worksheet1.set_column('F:F', 14)
    worksheet1.set_column('G:G', 16)
    titleFormat = workbook.add_format()
    titleFormat.set_bold()
    titleFormat.set_bg_color("#4F81BD")
    titleFormat.set_font_size(12)
    titleFormat.set_align("center")
    #titleFormat.set_align("vcenter")
    titleFormat.set_border(1)
    titleFormat.set_font_color('white')
    contentFormat = workbook.add_format()
    contentFormat.set_align("center")
    #contentFormat.set_align("vcenter")
    titleFormat.set_border(1)
    titleFormat.set_font_color('white')

    headList = ["基金代码",
                "基金简码",
                "基金名称",
                "基金类型",
                "基金字母"]
    for i in range(len(headList)):
        worksheet1.write(0, i, headList[i], titleFormat)
    for n in range(len(fundlist)):
        for i in range(len(fundlist[n])):
            try:
                worksheet1.write(n+1, i, fundlist[n][i], contentFormat)
            except:
                pass
    workbook.close()

url='http://fund.eastmoney.com/js/fundcode_search.js'
html=openurl(url)
fundlist=eval(html[8:-1])
print(fundlist[0])
for i in fundlist[0]:
    print(i)
    print(len(fundlist[0]))
writexls(fundlist)


# -*- coding: utf-8 -*-
"""
Created on Mon Jan  1 14:07:35 2018

@author: Eric
"""

import pandas as pd
from sqlalchemy import create_engine
import time

import warnings

def fxn():
    warnings.warn("deprecated", DeprecationWarning)

with warnings.catch_warnings():
    warnings.simplefilter("ignore")
    fxn()
    

def save_sql(code):
    df=pd.read_html('http://fund.eastmoney.com/f10/F10DataApi.aspx?type=lsjz&code='+code+'&page=1&per=730')
    df=df[0]
    df.columns =['date','nav','sum_nav','ret','a','b','c']
    del df['a']
    del df['b']
    del df['c']
    df['code']=code
    print("track 3")
    engine = create_engine('mysql+pymysql://root:12345@127.0.0.1:3306/test?charset=utf8')
    print("track 4")
    df.to_sql('fund', engine,schema='test',if_exists='append')
    print("track 5")

def main():
    df=pd.read_excel('fundlist.xlsx',converters = {u'基金代码':str})
    codes=df['基金代码']
    i=0
    for code in codes:
        print(i,code)
        try:
            save_sql(code)
        except:
            #6000多基金两年的数据，爬的过程中经常会被封。。。。。
            print('error',code)
            with open('error.txt','a') as f:
                f.write(str(i)+','+code+'\n')
                f.close()
        time.sleep(1)

if __name__ == "__main__":
    main()