-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQLiteCreateTables.py
186 lines (161 loc) · 10.2 KB
/
SQLiteCreateTables.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
# Author: Seth Hobbes
# Created: 7/17/2021
# Copyright: Springboro Technologies, LLC DBA Monarch Technologies all rights reserved
# Last Modified: 7/17/2021
import sqlite3
from sqlite3 import Error
def CreateConnection(db_file):
""" create a database connection to the SQLite database
specified by db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
return conn
except Error as e:
print(e)
return conn
def CreateTable(conn, create_table_sql):
""" create a table from the create_table_sql statement
:param conn: Connection object
:param create_table_sql: a CREATE TABLE statement
:return:
"""
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
def main():
""" Main program code holding the SQL scripts that generate the tables using the Create Table function
"""
# Path to the database
database = r"C:\sqlite\db\t_ball_db.db"
# SQL to create a table called Games
sql_create_games_table = """CREATE TABLE IF NOT EXISTS games (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
date DATE NOT NULL,
vs TEXT,
startTime TIME,
homeFlag BOOLEAN
);"""
# SQL to create a table called Players
sql_create_players_table = """CREATE TABLE IF NOT EXISTS players (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
firstName TEXT,
lastName TEXT,
lastPositionId INTEGER REFERENCES positions (id)
);"""
# SQL to create a table called Positions
sql_create_positions_table = """CREATE TABLE IF NOT EXISTS positions (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
name TEXT NOT NULL UNIQUE,
infieldFlag BOOLEAN
);"""
# SQL to create a table called Schedule
sql_create_schedule_table = """CREATE TABLE IF NOT EXISTS schedule (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
gameId INTEGER REFERENCES games (id),
playerId INTEGER REFERENCES players (id),
positionId INTEGER REFERENCES positions (id),
inningNumber INTEGER
);"""
# SQL to create a table called Innings
sql_create_innings_table = """CREATE TABLE IF NOT EXISTS innings (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
gameId INTEGER NOT NULL REFERENCES games (id),
playerId INTEGER NOT NULL REFERENCES players(id),
positionId INTEGER NOT NULL REFERENCES position (id),
inningNumber INTEGER NOT NULL
);"""
# SQL to create a table called Position Counters
sql_create_positionCounters_table = """CREATE TABLE IF NOT EXISTS positionCounters (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
playerId INTEGER NOT NULL REFERENCES players (id),
firstBaseCounter INTEGER NOT NULL DEFAULT 0,
firstBaseLastGame INTEGER NOT NULL DEFAULT 0,
firstBaseLastInning INTEGER NOT NULL DEFAULT 0,
secondBaseCounter INTEGER NOT NULL DEFAULT 0,
secondBaseLastGame INTEGER NOT NULL DEFAULT 0,
secondBaseLastInning INTEGER NOT NULL DEFAULT 0,
thirdBaseCounter INTEGER NOT NULL DEFAULT 0,
thirdBaseLastGame INTEGER NOT NULL DEFAULT 0,
thirdBaseLastInning INTEGER NOT NULL DEFAULT 0,
shortStopCounter INTEGER NOT NULL DEFAULT 0,
shortStopLastGame INTEGER NOT NULL DEFAULT 0,
shortStopLastInning INTEGER NOT NULL DEFAULT 0,
pitcherCounter INTEGER NOT NULL DEFAULT 0,
pitcherLastGame INTEGER NOT NULL DEFAULT 0,
pitcherLastInning INTEGER NOT NULL DEFAULT 0,
rightFieldCounter INTEGER NOT NULL DEFAULT 0,
rightFieldLastGame INTEGER NOT NULL DEFAULT 0,
rightFieldLastInning INTEGER NOT NULL DEFAULT 0,
leftFieldCounter INTEGER NOT NULL DEFAULT 0,
leftFieldLastGame INTEGER NOT NULL DEFAULT 0,
leftFieldLastInning INTEGER NOT NULL DEFAULT 0,
centerFieldCounter INTEGER NOT NULL DEFAULT 0,
centerFieldLastGame INTEGER NOT NULL DEFAULT 0,
centerFieldLastInning INTEGER NOT NULL DEFAULT 0,
homeRunCounter INTEGER NOT NULL DEFAULT 0,
homeRunLastGame INTEGER NOT NULL DEFAULT 0,
homeRunLastInning INTEGER NOT NULL DEFAULT 0,
lastInningOutfieldFlag BOOLEAN NOT NULL DEFAULT 0,
lastPositionId INTEGER REFERENCES positions (id)
);"""
# SQL to create a table called Temp Counters
sql_create_tempCounters_table = """CREATE TABLE IF NOT EXISTS tempCounters (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
playerId INTEGER NOT NULL REFERENCES players (id),
firstBaseCounter INTEGER NOT NULL DEFAULT 0,
firstBaseLastGame INTEGER NOT NULL DEFAULT 0,
firstBaseLastInning INTEGER NOT NULL DEFAULT 0,
secondBaseCounter INTEGER NOT NULL DEFAULT 0,
secondBaseLastGame INTEGER NOT NULL DEFAULT 0,
secondBaseLastInning INTEGER NOT NULL DEFAULT 0,
thirdBaseCounter INTEGER NOT NULL DEFAULT 0,
thirdBaseLastGame INTEGER NOT NULL DEFAULT 0,
thirdBaseLastInning INTEGER NOT NULL DEFAULT 0,
shortStopCounter INTEGER NOT NULL DEFAULT 0,
shortStopLastGame INTEGER NOT NULL DEFAULT 0,
shortStopLastInning INTEGER NOT NULL DEFAULT 0,
pitcherCounter INTEGER NOT NULL DEFAULT 0,
pitcherLastGame INTEGER NOT NULL DEFAULT 0,
pitcherLastInning INTEGER NOT NULL DEFAULT 0,
rightFieldCounter INTEGER NOT NULL DEFAULT 0,
rightFieldLastGame INTEGER NOT NULL DEFAULT 0,
rightFieldLastInning INTEGER NOT NULL DEFAULT 0,
leftFieldCounter INTEGER NOT NULL DEFAULT 0,
leftFieldLastGame INTEGER NOT NULL DEFAULT 0,
leftFieldLastInning INTEGER NOT NULL DEFAULT 0,
centerFieldCounter INTEGER NOT NULL DEFAULT 0,
centerFieldLastGame INTEGER NOT NULL DEFAULT 0,
centerFieldLastInning INTEGER NOT NULL DEFAULT 0,
homeRunCounter INTEGER NOT NULL DEFAULT 0,
homeRunLastGame INTEGER NOT NULL DEFAULT 0,
homeRunLastInning INTEGER NOT NULL DEFAULT 0
);"""
# create a database connection
conn = CreateConnection(database)
# If a connection exists, create the below tables
if conn is not None:
# create games table
CreateTable(conn, sql_create_games_table)
# create players table
CreateTable(conn, sql_create_players_table)
# create positions table
CreateTable(conn, sql_create_positions_table)
# create schedule table
CreateTable(conn, sql_create_schedule_table)
# create innings table
CreateTable(conn, sql_create_innings_table)
# create positionCounters table
CreateTable(conn, sql_create_positionCounters_table)
# create temp counters table
CreateTable(conn, sql_create_tempCounters_table)
# If no connection to database, print error message to the console
else:
print("Error! cannot create the database connection.")
if __name__ == '__main__':
main()