CODEWITHSUNDEEP
pythonmysql
noobMan
noobMan

Reputation: 123

How to make loop more efficent

The code I have developed is below. I have a table in sql called asx_codes that has the code for the stock and the GICS industry that stock is allocated to. Each GICS industry has its own table in a MySQL database. I am currently adapting the code below to perform incremental updates. I want to have it so I dont have to repeat the ELIF statement 25 times. I have tried using another table and performing a loop again but this didnt work that well. I also want it to be able to pick up new codes etc in the future from an updated MySQL table. 

from sqlalchemy import create_engine
import pymysql
import datetime
import pandas_datareader.data as web
import pandas as pd
import warnings; warnings.simplefilter('ignore')
sqlEngine = create_engine('mysql+pymysql://root:root@localhost/stocks', pool_recycle=3600)
dbConnection = sqlEngine.connect()

query = "SELECT * FROM asx_codes"
base_df = pd.read_sql(query, dbConnection)

for index, row in base_df.iterrows():
    stock = row[1]+".AX"
    category = row[2]
    if row[2] == 'banks':
        try:
            tableName = 'banks'
            df = web.DataReader(stock, 'yahoo')
            df['stock'] =row[1]
            df.reset_index(level=0, inplace=True)
            del df['Adj Close']
            frame = df.to_sql(tableName, dbConnection, if_exists='append', index = False)
            print(row[1] +" Downloaded")
        except:
            print("No Code for" + row[1])
    elif ... DO THE SAME AS ABOVE BUT FOR A DIFFERENT VALUE OF row[2]. row[2] if the industry code.

Upvotes: 0

Views: 86

Answers (1)

Nikaido
Nikaido

Reputation: 4629

If the only part that changes in the if statement is the table name, you can use a function:

from sqlalchemy import create_engine
import pymysql
import datetime
import pandas_datareader.data as web
import pandas as pd
import warnings; warnings.simplefilter('ignore')

def update(row, base_df, dbConnection):
    try:
        stock = row[1]+".AX"
        category = row[2]
        tableName = category
        df = web.DataReader(stock, 'yahoo')
        df['stock'] =row[1]
        df.reset_index(level=0, inplace=True)
        del df['Adj Close']
        frame = df.to_sql(tableName, dbConnection, if_exists='append', index = False)
        print(row[1] +" Downloaded")
    except:
        print("No Code for" + row[1])

sqlEngine = create_engine('mysql+pymysql://root:root@localhost/stocks', pool_recycle=3600)
dbConnection = sqlEngine.connect()

query = "SELECT * FROM asx_codes"
base_df = pd.read_sql(query, dbConnection)

for index, row in base_df.iterrows():
    update(row, base_df, dbConnection)

Depending on what you need to update the function may change

Upvotes: 1

Related Questions