python - 如何让循环更有效率

Question

我开发的代码如下。我有一个名为 asx_codes 的 SQL 表，其中包含股票和股票分配到的 GICS 行业的代码。每个 GICS 行业在 MySQL 数据库中都有自己的表。我目前正在调整下面的代码来执行增量更新。我想要拥有它，这样我就不必重复 ELIF 语句 25 次。我尝试过使用另一个表并再次执行循环，但这效果不太好。我还希望它将来能够从更新的 MySQL 表中获取新的代码等。

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.

Answer 1

如果 if 语句中唯一更改的部分是表名，则可以使用函数:

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)

根据您需要更新的功能可能会发生变化