How to store and retrieve data in python

Question

I have been looking for answers to my questions, but haven't found a definitive answer. I am new to python, mysql, and data science, so any advice is appreciated

What I want to be able to do is:

use python to pull daily close data from quandl for n securities
store the data in a database
retrieve, clean, and normalize the data
run regressions on different pairs
write the results to a csv file

The pseudocode below shows in a nutshell what I want to be able to do.

The questions I have are:
How do I store the quandl data in MySQL?
How do I retrieve that data from MySQL? Do I store it into lists and use statsmodels?

tickers = [AAPL, FB, GOOG, YHOO, XRAY, CSCO]
qCodes = [x + 'WIKI/' for x in tickers]
for i in range(0, len(qCodes)):
    ADD TO MYSQLDB->Quandl.get(qCodes[i], collapse='daily', start_date=start, end_date=end)

for x in range(0, len(qCodes)-1):
    for y in range(x+1, len(qCodes)):
        //GET FROM MYSQLDB-> x, y 
        //clean(x,y)
        //normalize(x,y)
        //write to csv file->(regression(x,y))

score 3 · Answer 1 · answered Sep 05 '15 at 05:40

There is a nice library called MySQLdb in Python, which helps you interact with the MySQL db's. So, for the following to execute successfully, you have to have your python shell and the MySQL shells fired up.

How do I store the quandl data in MySQL?

import MySQLdb

#Setting up connection
db = MySQLdb.connect("localhost", user_name, password, db_name)

cursor = db.cursor()

#Inserting records into the employee table
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES('Steven', "Karpinski", "50", "M", "43290")"""

try:
   cursor.execute(sql)
   db.commit()
except:
   db.rollback()
db.close()

I did it for custom values. So, for quandl data, create the schema in a similar way and store them by executing a loop.

How do I retrieve that data from MySQL? Do I store it into lists and use statsmodels?

For data retrieval, you execute the following command, similar to the above command.

sql2 = """SELECT * FROM EMPLOYEE;
        """
try:
   cursor.execute(sql2)
   db.commit()
except:
   db.rollback()

result = cursor.fetchall()

The result variable now contains the result of the query inside sql2 variable, and it is in form of tuples.

So, now you can convert those tuples into a data structure of your choice.

score 2 · Answer 2 · answered Sep 07 '15 at 05:10

Quandl has a python package that makes interacting with the site trivial.

From Quandl's python page:

import Quandl
mydata = Quandl.get("WIKI/AAPL")

By default, Quandl's package returns a pandas dataframe. You can use Pandas to manipulate/clean/normalize your data as you see fit and use Pandas to upload the data directly to a sql database :

import sqlalchemy as sql
engine = sql.create_engine('mysql://name:blah@location/testdb')
mydata.to_sql('db_table_name', engine, if_exists='append')

To get the data back from your database, you can also use Pandas:

import pandas as pd
import sqlalchemy as sql
engine = sql.create_engine('mysql://name:blah@location/testdb')
query = sql.text('''select * from quandltable''')
mydata = pd.read_sql_query(engine, query)

After using statsmodels to run your analyses, you can use either pandas' df.to_csv() method or numpy's savetxt() function. (Sorry, i cannot post the links for those functions; I don't have enough reputation yet!)

How to store and retrieve data in python

2 Answers2