My goal was to create a script that would:
- Pull certain data from an existing sqlite table
- Apply a simple mathematical formula
- 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.
This is my code:
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.
