API/databaseOperation.py at master · Conansoloman/API · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
__author__ = 'Conan'
#!/usr/bin/python

import pandas as pd
import urllib2
import MySQLdb as mdb
import datetime as dt
import matplotlib.pyplot as plt

symbol = ['AMZN','AAPL','GOOG']
beginDate = dt.date(2015,6,1)
endDate = dt.date(2015,6,30)

def loadDataIntoDB(symbol = None, beginDate=None, endDate = None):
    hostName = 'localhost'
    userName = 'sec_user'
    passWord = 'password'
    dataBase = 'securities_master'
    db = mdb.connect(hostName,userName,passWord,dataBase)
    cursor = db.cursor()
    yahoo_url = "http://ichart.finance.yahoo.com/table.csv?s=%s&a=%s&b=%s&c=%s&d=%s&e=%s&f=%s"%(symbol,beginDate.month-1,beginDate.day,beginDate.year,endDate.month-1,endDate.day,endDate.year )
    yf_data = urllib2.urlopen(yahoo_url).readlines()[1:]
    for data in yf_data:
        cdata = data.split(',')
        insertCommond = "INSERT INTO dailyStockMarket(Symbol, priceDate, openPrice, highPrice, lowPrice, closePrice, adjClosePrice, Volume) VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s')"%(symbol,cdata[0],cdata[1],float(cdata[2]),float(cdata[3]),float(cdata[4]),float(cdata[6]),int(cdata[5]))
        cursor.execute(insertCommond)
        db.commit()
    db.close()

def fetchDataFromDB(symbol,beginDate,endDate):
    hostName = 'localhost'
    userName = 'sec_user'
    passWord = 'password'
    dataBase = 'securities_master'
    db = mdb.connect(hostName,userName,passWord,dataBase)
    cursor = db.cursor()
    retrieveCommond = "select * from dailyStockMarket where Symbol = '%s'and priceDate >= '%s'and priceDate <= '%s'"%(symbol,beginDate,endDate)
    cursor.execute(retrieveCommond)
    results = cursor.fetchall()
    return results
    db.close()

<<<<<<< Updated upstream
#for sym in symbol:
#    loadDataIntoDB(sym,beginDate,endDate)


=======

#for sym in symbol:
#    loadDataIntoDB(sym,beginDate,endDate)

>>>>>>> Stashed changes
for sym in symbol:
    loadDataIntoDB(sym,beginDate,endDate)
'''
>>>>>>> origin/master
res = fetchDataFromDB("GOOG",beginDate,endDate)
resl =[]
for item in res:
    resl.append(item)
dfres = pd.DataFrame(resl)
dfres.columns = ['ID','Symbol','priceDate', 'openPrice', 'highPrice', 'lowPrice', 'closePrice', 'adjClosePrice','Volume']
dfres.index = dfres.priceDate
del dfres['ID']
del dfres['Symbol']
del dfres['priceDate']
print dfres
'''
'''
pType = 'adjClosePrice'
price = dfres[pType]
date = dt.date(2015,6,2)
print price[date]
print "..."
price = dfres.adjClosePrice
if 560 > 530.0:
    print price.index.values
    print "..."
'''