Url = 'http://quote.eastmoney.com/stocklist.html'#東方財(cái)富網(wǎng)股票數(shù)據(jù)連接地址
filepath = 'D:\\data\\'#定義數(shù)據(jù)文件保存路徑
#實(shí)施抓取
code = getStackCode(getHtml(Url)) 
#獲取所有股票代碼（以6開(kāi)頭的，應(yīng)該是滬市數(shù)據(jù)）集合
CodeList = []
for item in code:
    if item[0]=='6':
        CodeList.append(item)
#抓取數(shù)據(jù)并保存到本地csv文件
for code in CodeList:
    print('正在獲取股票%s數(shù)據(jù)'%code)
    url = 'http://quotes.money.163.com/service/chddata.html?code=0' code         '&end=20161231&fields=TCLOSE;HIGH;LOW;TOPEN;LCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP'
    urllib.request.urlretrieve(url, filepath code '.csv')

#創(chuàng)建數(shù)據(jù)庫(kù)stockDataBase，如果存在則跳過(guò)
sqlSentence1 = 'create database if not exists stockDataBase'
cursor.execute(sqlSentence1)#選擇使用當(dāng)前數(shù)據(jù)庫(kù)
sqlSentence2 = 'use stockDataBase;'
cursor.execute(sqlSentence2)

#關(guān)閉游標(biāo),，提交,，關(guān)閉數(shù)據(jù)庫(kù)連接
cursor.close()
db.commit()
db.close()

#導(dǎo)入需要使用到的模塊
import urllib
import re
import pandas as pd
import pymysql
import os

#爬蟲(chóng)抓取網(wǎng)頁(yè)函數(shù)
def getHtml(url):
    html = urllib.request.urlopen(url).read()
    html = html.decode('gbk')
    return html

#抓取網(wǎng)頁(yè)股票代碼函數(shù)
def getStackCode(html):
    s = r'<li><a target='_blank' 
    pat = re.compile(s)
    code = pat.findall(html)
    return code
    
#########################開(kāi)始干活############################
Url = 'http://quote.eastmoney.com/stocklist.html'#東方財(cái)富網(wǎng)股票數(shù)據(jù)連接地址
filepath = 'C:\\Users\\Lenovo\\Desktop\\data\\'#定義數(shù)據(jù)文件保存路徑
#實(shí)施抓取
code = getStackCode(getHtml(Url)) 
#獲取所有股票代碼（以6開(kāi)頭的,，應(yīng)該是滬市數(shù)據(jù)）集合
CodeList = []
for item in code:
    if item[0]=='6':
        CodeList.append(item)
#抓取數(shù)據(jù)并保存到本地csv文件
for code in CodeList:
    print('正在獲取股票%s數(shù)據(jù)'%code)
    url = 'http://quotes.money.163.com/service/chddata.html?code=0' code         '&end=20161231&fields=TCLOSE;HIGH;LOW;TOPEN;LCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP'
    urllib.request.urlretrieve(url, filepath code '.csv')


##########################將股票數(shù)據(jù)存入數(shù)據(jù)庫(kù)###########################

#數(shù)據(jù)庫(kù)名稱(chēng)和密碼
name = 'xxxx'
password = 'xxxx'  #替換為自己的賬戶(hù)名和密碼
#建立本地?cái)?shù)據(jù)庫(kù)連接(需要先開(kāi)啟數(shù)據(jù)庫(kù)服務(wù))
db = pymysql.connect('localhost', name, password, charset='utf8')
cursor = db.cursor()
#創(chuàng)建數(shù)據(jù)庫(kù)stockDataBase
sqlSentence1 = 'create database stockDataBase'
cursor.execute(sqlSentence1)#選擇使用當(dāng)前數(shù)據(jù)庫(kù)
sqlSentence2 = 'use stockDataBase;'
cursor.execute(sqlSentence2)

#獲取本地文件列表
fileList = os.listdir(filepath)
#依次對(duì)每個(gè)數(shù)據(jù)文件進(jìn)行存儲(chǔ)
for fileName in fileList:
    data = pd.read_csv(filepath fileName, encoding='gbk')
   #創(chuàng)建數(shù)據(jù)表，如果數(shù)據(jù)表已經(jīng)存在,，會(huì)跳過(guò)繼續(xù)執(zhí)行下面的步驟print('創(chuàng)建數(shù)據(jù)表stock_%s'% fileName[0:6])
    sqlSentence3 = 'create table stock_%s' % fileName[0:6]   '(日期 date, 股票代碼 VARCHAR(10),     名稱(chēng) VARCHAR(10),                       收盤(pán)價(jià) float,    最高價(jià)    float, 最低價(jià) float, 開(kāi)盤(pán)價(jià) float, 前收盤(pán) float, 漲跌額    float,                        漲跌幅 float, 換手率 float, 成交量 bigint, 成交金額 bigint, 總市值 bigint, 流通市值 bigint)'
    cursor.execute(sqlSentence3)
    except:
        print('數(shù)據(jù)表已存在,！')

    #迭代讀取表中每行數(shù)據(jù)，依次存儲(chǔ)（整表存儲(chǔ)還沒(méi)嘗試過(guò)）
    print('正在存儲(chǔ)stock_%s'% fileName[0:6])
    length = len(data)
    for i in range(0, length):
        record = tuple(data.loc[i])
        #插入數(shù)據(jù)語(yǔ)句
        try:
            sqlSentence4 = 'insert into stock_%s' % fileName[0:6]   '(日期, 股票代碼, 名稱(chēng), 收盤(pán)價(jià), 最高價(jià), 最低價(jià), 開(kāi)盤(pán)價(jià), 前收盤(pán), 漲跌額, 漲跌幅, 換手率,             成交量, 成交金額, 總市值, 流通市值) values ('%s',%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' % record
            #獲取的表中數(shù)據(jù)很亂,，包含缺失值,、Nnone、none等,，插入數(shù)據(jù)庫(kù)需要處理成空值
            sqlSentence4 = sqlSentence4.replace('nan','null').replace('None','null').replace('none','null') 
            cursor.execute(sqlSentence4)
        except:
            #如果以上插入過(guò)程出錯(cuò),，跳過(guò)這條數(shù)據(jù)記錄，繼續(xù)往下進(jìn)行
            break

#關(guān)閉游標(biāo),，提交,，關(guān)閉數(shù)據(jù)庫(kù)連接
cursor.close()
db.commit()
db.close()


###########################查詢(xún)剛才操作的成果##################################

#重新建立數(shù)據(jù)庫(kù)連接
db = pymysql.connect('localhost', name, password, 'stockDataBase')
cursor = db.cursor()
#查詢(xún)數(shù)據(jù)庫(kù)并打印內(nèi)容
cursor.execute('select * from stock_600000')
results = cursor.fetchall()
for row in results:
    print(row)
#關(guān)閉
cursor.close()
db.commit()
db.close()