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

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

"""add new calculated data to another sqlite table"""
try:
c_2 = database_connection.cursor()
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
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.

• One quick note, in your true_odds_calculations you're passing in three separate odds, performing the same operations on each of them, and then packing them together in a list for your return. Repetitive code like that is a sign you should consider receiving a list or tuple of odds and performing calculations via loops or list comprehension e.g. true_odds = [math_stuff(odds) for odds in raw_odds] Sep 19 '20 at 17:52
• And a second note, if I were you I would scrap all of this code and learn to use Pandas. It's the package people use for data analysis and manipulation like this and will read directly from sqlite and let you perform calculations over whole columns at once with it's dataframe structure. You could get all of that code down to 10-15 lines with it Sep 19 '20 at 17:57
• Thank you very much for your answer, I will study it in detail tonight.
– user230470
Sep 20 '20 at 9:44

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

conn = sql.connect('Test.db')

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']))

• Everything works perfectly, thanks a lot for the effort.
– user230470
Sep 20 '20 at 17:32
• I hope it's clear enough you can start to see how you can use pandas for other things. There's lots of tutorials on it but basically you need to read in a dataframe and then apply the formulas you want (in this case f is the function you repeated a bunch rewritten as a one line lambda function), and then apply it to each column. All the string joins are just to create column names automatically that will be unique for each function. I forgot to include saving it but pandas let's you write a dataframe directly to a table pretty easily if you google it. Good luck! Sep 20 '20 at 19:06
• This is a lot easier than what I did and the pandas look very interesting. I was able to save the data frame to the database table without any problems. All the best.
– user230470
Sep 20 '20 at 22:15