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:

1. use python to pull daily close data from quandl for n securities
   
2. store the data in a database
   
3. retrieve, clean, and normalize the data
   
4. run regressions on different pairs
   
5. 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))

Answer 1

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.