trjhtr

I have a table in my DB that contains Riders as rows and their points throughout the season as columns. Here are the columns:

['name', 'wwq_id', 'wwq_code', 'year', 'round1_qualy', 'round1_final', 
'round1_timed', 'round1_total_qualy', 'round1_total_qualy_position', 
'round1_total_final', 'round1_total_final_position', 'round1_total_timed', 
'round1_total_timed_position', 'round1_total', 'round1_total_position'...etc

The 'round1_qualy', 'round1_final', 'round1_timed' all come from different tables and are added using another function. Once they are in the DB all of total columns are calculated using generated columns in MySQL.

I need to calculate the position of the rider at different points in the season. I.E. After round one qualy, after round two final, etc.

Here is the code I have right now:

def set_positions():
 """Add positions to the DB for each column in our overall table"""
 engine = sql.create_engine(MYSQL_CON_STRING)
 connection = engine.connect()
 trans = connection.begin()

 # Men, Women, Junior
 for category in CATEGORIES:
 table = category + '_overall'
 df = pd.read_sql('SELECT * FROM 0'.format(table), engine)
 points_columns_list = [col for col in df.columns
 if 'total' in col and 'position' not in col]

 # Calculate the rank based on current total points column and
 # insert into current column index + 1
 for points_column in points_columns_list:
 position_index = df.columns.get_loc(points_column) + 1
 position_column = df.columns[position_index]
 current_points = df.groupby('year')
 position = 
 current_points[points_column].rank(ascending=0,
 method='min').astype(int)
 df.loc[:, position_column] = position.values

 # No points awarded in this and/or previous rounds. Everyone is 
 # at 0 so everyone is rank 1
 df.loc[(df[points_column] == 0)
 & (df[position_column] == 1), position_column] = 'NULL'

 # Add rows to the DB
 for row in df.itertuples():
 connection.execute('UPDATE 0 '
 'SET 1 = 2 '
 'WHERE year = 3 and name = "4"'
 ''.format(table, position_column,
 getattr(row, position_column),
 getattr(row, 'year'),
 getattr(row, 'name')))
 trans.commit()
 connection.close()

The code works perfectly, it just takes a long time. This function doesn't get executed very often, but considering there are about 8000 rows per table, it takes a bit of time.

Is there any way to get the complexity down?

You should strive to reduce the number of queries to your DB. Instead of updating the whole table, row by row, each time you compute a new value, you could:

1. load the data (using pd.read_sql_table);


2. perform the computation of the positions (this may require you to use None or pd.np.NaN instead of 'NULL');


3. write the whole new table at once at the end (using df.to_sql).

The rewrite could look like:

def set_positions():
 """Add positions to the DB for each column in our overall table"""
 engine = sql.create_engine(MYSQL_CON_STRING)
 connection = engine.connect()
 trans = connection.begin()

 # Men, Women, Junior
 for category in CATEGORIES:
 table = category + '_overall'
 df = pd.read_sql_table(table, engine)
 compute_positions_for_table(df)
 df.to_sql(table, engine, if_exists='replace')
 trans.commit()
 connection.close()


def compute_positions_for_table(df):
 points_columns_list = [col for col in df.columns
 if 'total' in col and 'position' not in col]

 # Calculate the rank based on current total points column and
 # insert into current column index + 1
 for points_column in points_columns_list:
 position_index = df.columns.get_loc(points_column) + 1
 position_column = df.columns[position_index]
 current_points = df.groupby('year')
 position = current_points[points_column].rank(ascending=0, method='min').astype(int)
 df.loc[:, position_column] = position.values

 # No points awarded in this and/or previous rounds. Everyone is 
 # at 0 so everyone is rank 1
 df.loc[(df[points_column] == 0)
 & (df[position_column] == 1), position_column] = None

And even if you can't "replace" the table, you should still update the table only once at the end of the computation rather than at each new column computation.