· 6 years ago · Oct 10, 2019, 10:46 AM
1# import standard library modules
2import sqlite3
3
4# import pip modules
5
6# import in-project modules
7
8
9# create function to initiate the database either in memory or file
10def initiate_database(database=None):
11 try:
12 # initialize the database in memory instead of a file
13 if database is None:
14 print("Initializing database in memory")
15 connection = sqlite3.connect(':memory:')
16
17 # initialize database as a file or from a file
18 else:
19 print(f"Initializing {database}")
20 connection = sqlite3.connect(database)
21
22 # initialize a cursor
23 c = connection.cursor()
24
25 # return the connection and the cursor
26 return connection, c
27
28 except TypeError:
29 print("Failed to initialize database... Type error in database argument detected, make sure entry is a string")
30
31
32# initiate a database connection and cursor for the script to execute sql commands
33conn, cursor = initiate_database('employee.db')
34
35
36# create a function to create new tables in an optionally specified database
37def create_table(table=None, fields=None, database=None):
38 # sub_function to name the table
39 def m_table():
40 # create a while loop for the event that the input is out of range, or somehow breaks our rules.
41 while True:
42 # prompt for input
43 print("What would you like to name your table? (no spaces)", end=" > ")
44 table_name = input()
45
46 if " " in table_name:
47 print("Your table name cannot contain spaces, please try again")
48 print("")
49
50 # TODO: Should probably create a conditional to prevent non-alpha characters using additional elif?
51
52 # if we are space free
53 else:
54 print("")
55 return table_name
56
57 # sub_function to get number of desired fields
58 def n_fields():
59 # create a while loop for the event that the input is out of range, or somehow breaks our rules.
60 while True:
61 # create a conditional variable and set it to off, this will help protect our input from crashing
62 # the program
63 failed = False
64
65 # first we'll try our input prompt
66 try:
67 # prompt for input
68 print("How many fields would you like to make? (int)", end=" > ")
69 fields_num = int(input())
70
71 # if it fails we'll trigger the failed variable to skip the return and loop back around
72 except ValueError:
73 fields_num = "FAILURE"
74 failed = True
75
76 # if our input didn't fail, we return the number of fields
77 if not failed:
78 print("")
79 return fields_num
80
81 # other wise we loop back around
82 else:
83 print("Input is not an integer, please try again.")
84 print("")
85
86 # sub-function to name a new field
87 def name_field(index):
88 # create a while loop for the event that the input is out of range, or somehow breaks our rules.
89 while True:
90 # prompt for input
91 print(f"[{index}] What would you like to name the field?", end=" > ")
92 _name = input()
93
94 # make sure our field name doesn't have any spaces.
95 if " " in _name:
96 print("Your field name cannot contain spaces, please try again")
97 print("")
98
99 # TODO: Should probably create a conditional to prevent non-alpha characters using additional elif?
100
101 # if we are space free
102 else:
103 print("")
104 return _name
105
106 # sub-function to select the type for a new field
107 def select_field_type(index):
108 # define our possible choices for our type
109 nulls = ("null", "NULL")
110 ints = ("int", "integer")
111 floats = ("float", "real")
112 strings = ("str", "string", "text")
113 blobs = ("blob", "BLOB")
114 options = "null Null int integer float real str string text blob BLOB"
115
116 # while loop for input in case our user inputs the wrong thing.
117 while True:
118 # tell them their basic options
119 print(f"| null, integer, real(float), text(string), or blob |")
120 # prompt for input
121 print(f"[{index}]What 'Type' do you want our field to be?", end=" > ")
122 _type = input()
123
124 # make sure the choice is in range of the possible choices, if not loop back around to the begbinning
125 # of the while loop
126 if _type not in options:
127 print(options)
128 print("Input out of range, please try again")
129
130 # if the choice is in range, make sure _type is set to the corresponding value needed
131 else:
132 if _type in nulls:
133 _type = "null"
134
135 if _type in ints:
136 _type = "integer"
137
138 if _type in floats:
139 _type = "real"
140
141 if _type in strings:
142 _type = "text"
143
144 if _type in blobs:
145 _type = "blob"
146
147 # finish sub-function mission statement and return our type
148 print("")
149 return _type
150
151 # if the database argument is empty we will use an in memory
152 if database is None:
153 print("Database argument field is empty, defaulting to the script's active database")
154 print("")
155 db = conn
156
157 # allow for the database entry to either take a string pointing to the database
158 # or an active in script database such as conn
159 else:
160 # if our database entry is a string pointing to a .db file
161 if ".db" in database:
162 db = sqlite3.connect(database)
163 # or if our database entry is an existing conn
164 else:
165 db = database
166
167 # establish a local cursor, in the event we are accessing a database not established in the script
168 _cursor = db.cursor()
169
170 if table is None:
171 # call the m_table() sub-function to get our table name
172 table = m_table()
173
174 if fields is None:
175 fields = int(n_fields())
176
177 # create a blank entries string to add our field entries to in the following for loop
178 entries = """"""
179
180 # since we're not working with a list and instead an integer, we're forced to use the x in range(#) method
181 # so we can't index, something in things:, instead we'll use a variable with a starting value of 0 and add
182 # to it by 1 as we go along through each loops.
183 i_index = 0
184 for i in range(fields):
185 # increase the index by 1
186 i_index += 1
187
188 # call the name_field() and select_field_type() sub-functions
189 # and pass in our i_index number to let the sub-functions know
190 # which loop we are on
191 field_name = name_field(i_index)
192 field_type = select_field_type(i_index)
193
194 # if we've reached the last loop, do not add a new line
195 if i_index == fields:
196 field_entry = f"{field_name} {field_type}"
197 # otherwise add a new line to the end of the string
198 else:
199 field_entry = f"{field_name} {field_type}\n"
200
201 # create a line for visual line spacing
202 print("")
203
204 # add our new field_entry string to the entries string
205 entries += field_entry
206
207 # construct our CREATE TABLE call for the cursor.execute() call and load it into a string
208 execute_string = f"""CREATE TABLE {table} (
209 {entries}
210 )"""
211
212 # create a try except loop to make sure that the program doesn't crash if the table already exists
213 try:
214 print("creating table...")
215 # execute our CREATE TABLE call passing in the execute_string
216 _cursor.execute(execute_string)
217
218 print(f"committing to the database...")
219 # confirm the data and commit it to the database
220 db.commit()
221
222 print("closing database...")
223 # it is always a good idea to close your database after accessing it!
224 db.close()
225 print("finished...")
226
227 except sqlite3.OperationalError:
228 print("ERROR: Table already exists!")
229
230 # end create_table() function <3
231
232
233if __name__ == '__main__':
234 # create_table() can support a None database entry and will default to the conn
235 # it can support a sqlite connection object (such as conn)
236 # or it can support a string (such as 'example.db'), and if it doesn't exist it'll be created
237
238 # call our create_table function with our conn variable in the database argument
239 create_table('example.db')