Load data from SQLite table, modify them and save in to new SQLite table

Question

My goal was to create a script that would:

1. Pull certain data from an existing SQLite table
2. Apply a simple mathematical formula
3. Save the new values to another SQLite table

I’m just starting to learn Python (programming in general) and to be honest a lot of that is unclear to me. But using the internet and a lot of existing examples, I created something, and that something does its job, but I want to know what I did well and what I did not so well so that I can correct it and learn for the future.

import sqlite3 as sql
from sqlite3 import Error

def create_connection(db_file):
    """create a database connection to the SQLite database""" 
    database_connection = None
       
    try:
        database_connection = sql.connect(db_file)
        return database_connection 
    except Error as e:
        print(e) 
    return database_connection

def get_data(database_connection, get_data_sql):
    """get data from sqlite table"""
    try:
        c = database_connection.cursor()
        c.execute(get_data_sql)
        return c.fetchall()
    except Error as e:
        print(e)

def true_odds_calculation_3way(home_odds, draw_odds, away_odds):
    """use margin weights proportional to the odds method 
    to calculate true odds in games  with 3 possible outcomes"""
    margin = (1/home_odds)+(1/draw_odds)+(1/away_odds)-1
    home_true = ((3*home_odds)/(3-(home_odds*margin)))
    draw_true = ((3*draw_odds)/(3-(draw_odds*margin)))
    away_true = ((3*away_odds)/(3-(away_odds*margin)))
    
    # limiting floats to 3 decimal points
    margin_3way = float("{:.3f}".format(margin))
    home_true_3way = float("{:.3f}".format(home_true))
    draw_true_3way = float("{:.3f}".format(draw_true))
    away_true_true_3way = float("{:.3f}".format(away_true))
    
    true_odds_3way = [home_true_3way, draw_true_3way, away_true_true_3way]
    
    return true_odds_3way, margin_3way

def true_odds_calculation_2way(over_odds, under_odds):
    """use margin weights proportional to the odds method 
    to calculate true odds in games  with 2 possible outcomes"""
    margin_ou = ((1/over_odds)+(1/under_odds)-1)
    over_true = ((2*over_odds)/(2-(over_odds*margin_ou)))
    under_true = ((2*under_odds)/(2-(under_odds*margin_ou)))
    
    # limiting floats to 3 decimal points
    margin_2way = float("{:.3f}".format(margin_ou))
    over_true_2way = float("{:.3f}".format(over_true))
    under_true_2way = float("{:.3f}".format(under_true))

    true_odds_2way = [over_true_2way, under_true_2way]
    
    return true_odds_2way, margin_2way

def add_data(database_connection, add_data_sql, data):
    """add new calculated data to another sqlite table"""
    try:
        c_2 = database_connection.cursor()
        c_2.execute(add_data_sql, data)
        database_connection.commit()
    except Error as e:
        print(e)
   
def main():
    
    database = 'Test.db'
    # get data from existig table
    sql_get_pinnacle_odds = """SELECT Pinnacle_Home_Closing, Pinnacle_Draw_Closing, Pinnacle_Away_Closing,
                            Pinnacle_Home_Opening, Pinnacle_Draw_Opening, Pinnacle_Away_Opening,
                            Pinnacle_Over_Closing, Pinnacle_Under_Closing, Pinnacle_Over_Opening, Pinnacle_Under_Opening FROM Model;"""
    # add data in another table
    sql_add_data = """ INSERT INTO True_Odds VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"""
    
    # create a database connection
    database_connection = create_connection(database) 
    
    # get data
    if database_connection is not None:     
        # get data from table
        pinnacle_closing_lines = get_data(database_connection, sql_get_pinnacle_odds)
        for i in pinnacle_closing_lines:
            
            true_pinnacle_closing_odds ="True Closing Odds" # Just for better orientation
            true_closing_odds, margin1 = true_odds_calculation_3way(i[0], i[1], i[2])
            true_1x2_closing = [true_pinnacle_closing_odds] + true_closing_odds + [margin1]
            
            true_pinnacle_opening_odds ="True Opening Odds" # Just for better orientation
            true_opening_odds, margin2 = true_odds_calculation_3way(i[3], i[4], i[5])
            true_1x2_opening = [true_pinnacle_opening_odds] + true_opening_odds + [margin2]
            
            true_pinnacle_ou_closing_odds ="True Closing OU Odds" # Just for better orientation
            true_closing_ou_odds, margin3 = true_odds_calculation_2way(i[6], i[7])
            true_ou_closing = [true_pinnacle_ou_closing_odds] + true_closing_ou_odds + [margin3]
            
            true_pinnacle_ou_opening_odds ="True Opening OU Odds" # Just for better orientation
            true_opening_ou_odds, margin4 = true_odds_calculation_2way(i[8], i[9])
            true_ou_opening = [true_pinnacle_ou_opening_odds] + true_opening_ou_odds + [margin4]
             
            true_pinnacle = true_1x2_closing + true_1x2_opening + true_ou_closing + true_ou_opening
            
            # save data from table
            add_data(database_connection, sql_add_data, true_pinnacle)
    else:
        print("Error!")
  
if __name__ == '__main__':
    main()

First I wanted to create a script that would automatically add new columns to the existing SQLite table and populate them with the calculated values. But I failed.

Since I am still collecting data in a SQLite table from which this script will pull them and calculate new values, after I finish, I will assign a unique value to that table and insert it via the script into a new table where I will record the results and eventually merge these two tables into one. So I will be sure that everything will be in order.

Answer 1

Here's your first 65 lines (everything before main) cut down to 16 with Pandas.

Since you're new to programming watch out for when you start repeating the same lines with different variables or the same functions with different number of arguments. Those are signs what you're doing can likely be simplified.

And if you're trying to do anything with data stored in tables (csv, sql, etc) do it with Pandas. It's built off of dataframes that let you read, manipulate, and save data while preserving the full table structure and is basically the standard package for any data analysis work

import pandas as pd 
import sqlite3 as sql

def read_table():
    conn = sql.connect('Test.db')
    return pd.read_sql_table('MODEL', conn)

def true_odds_calculation_Nway(df, cols):
    n = len(cols)
    margin = 'true_' + "_".join(cols) + '_margin_' + str(n) + 'way'
    df[margin] = (1/df[cols]).sum(axis=1)-1

    f = lambda row: (n*row[col])/(n-(row[col]*row[margin]))
    for col in cols:
        df['true_' + col + "_" + str(n) + "way"] = df.apply(f, axis=1)
        
    return df.round(3)

And just a quick test to show how you use it:

test = pd.DataFrame([[1,2,3],[4,5,6],[7,8,9]], columns=['A', 'B', 'C'])
print(true_odds_calculation_Nway(test, ['A', 'B']))