-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQLiteInsertIntoTables.py
184 lines (144 loc) · 6.17 KB
/
SQLiteInsertIntoTables.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
# 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
from random import randint
def CreateConnection(db_file):
""" create a database connection to the SQLite database specificed by db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
except Error as e:
print(e)
return conn
def CreateSchedule(conn, schedule):
""" Insert a new schedule into the schedule table
:param conn: database connection
:param schedule: tuple with the values for the record
:return: schedule id
"""
sql = ''' INSERT INTO schedule(gameId, playerId, positionId, inningNumber) VALUES(?, ?, ?, ?) '''
cur = conn.cursor()
cur.execute(sql, schedule)
conn.commit()
# return cur.lastrowid
def CreateGame(conn, game):
""" Insert a new game into the games table
:param conn: connection to the database
:param game: tuple with the values for the record
:return: game id
"""
sql = ''' INSERT INTO games(id, date, vs, startTime, homeFlag) VALUES(?, ?, ?, ?, ?) '''
cur = conn.cursor()
cur.execute(sql, game)
conn.commit()
# return cur.lastrowid
def CreatePlayer(conn, player):
"""
Insert a new player into the players table
:param conn: connection to the database
:param player:
:return: player id
"""
sql = ''' INSERT INTO players(firstName, lastName) VALUES(?, ?) '''
cur = conn.cursor()
cur.execute(sql, player)
conn.commit()
# return cur.lastrowid
def CreatePosition(conn, position):
"""
Insert a new position into the positions table
:param conn: connection to the database
:param position:
:return: position id
"""
sql = ''' INSERT INTO positions(name, infieldFlag) VALUES(?, ?) '''
cur = conn.cursor()
cur.execute(sql, position)
conn.commit()
# return cur.lastrowid
def CreatePositionCounter(conn, counter):
"""
INSERT a new record into the positionCounters table
:param conn: connection to the database
:param counter:
:return: counter id
"""
sql = ''' INSERT INTO positionCounters(playerId, firstBaseCounter, firstBaseLastGame, firstBaseLastInning, secondBaseCounter,
secondBaseLastGame, secondBaseLastInning, thirdBaseCounter, thirdBaseLastGame, thirdBaseLastInning, shortStopCounter,
shortStopLastGame, shortStopLastInning, pitcherCounter, pitcherLastGame, pitcherLastInning, rightFieldCounter,
rightFieldLastGame, rightFieldLastInning, leftFieldCounter, leftFieldLastGame, leftFieldLastInning, centerFieldCounter,
centerFieldLastGame, centerFieldLastInning, homeRunCounter, homeRunLastGame, homeRunLastInning)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?) '''
cur = conn.cursor()
conn.execute(sql, counter)
conn.commit()
# return cur.lastrowid
def CreateTempCounter(conn, counter):
"""
INSERT a new record into the positionCounters table
:param conn: connection to the database
:param counter:
:return: counter id
"""
sql = ''' INSERT INTO tempCounters(playerId, firstBaseCounter, firstBaseLastGame, firstBaseLastInning, secondBaseCounter,
secondBaseLastGame, secondBaseLastInning, thirdBaseCounter, thirdBaseLastGame, thirdBaseLastInning, shortStopCounter,
shortStopLastGame, shortStopLastInning, pitcherCounter, pitcherLastGame, pitcherLastInning, rightFieldCounter,
rightFieldLastGame, rightFieldLastInning, leftFieldCounter, leftFieldLastGame, leftFieldLastInning, centerFieldCounter,
centerFieldLastGame, centerFieldLastInning, homeRunCounter, homeRunLastGame, homeRunLastInning)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?) '''
cur = conn.cursor()
conn.execute(sql, counter)
conn.commit()
# return cur.lastrowid
#===============================================================================
# def create_task(conn, task):
# """
# Create a new task
# :param conn:
# :param task:
# :return:
# """
#
# sql = ''' INSERT INTO tasks(name,priority, status_id, project_id, begin_date, end_date) VALUES(?, ?, ?, ?, ?, ?) '''
# cur = conn.cursor()
# cur.execute(sql, task)
# conn.commit()
#
# return cur.lastrowid
#===============================================================================
def main():
database = r"C:\sqlite\db\t_ball_db.db"
# create a database connection
conn = CreateConnection(database)
with conn:
# Most of this code is commented out so as not to accidentally create new record if this script is executed
# create a new project
# schedule = (randint(1,6), randint(1,8), randint(1,8), 1);
# CreateSchedule(conn, schedule)
# create a new game
# game = (6, "8/21/2021", "Deep Orange", "11:00", 1)
# CreateGame(conn, game)
# create a player
# player = ("Cannon", "McDonald")
# CreatePlayer(conn, player)
# create a position
# position = ("Center Field", 0)
# CreatePosition(conn, position)
# create positionCounter
# counter = (8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
# CreatePositionCounter(conn, counter)
# create tempCounter
counter = (8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
CreateTempCounter(conn, counter)
if __name__ == '__main__':
main()