BPwcNsfJ

1
2"""display_menu is the interface for the database that allows the user to be able to access parts of the database.
3get_menu allows for the user to gain access to a specific function by typing in one of the values. If the value isnt between what is allowed,
4the user will be stuck in a loop until the variable "accepted" becomes true.
5
6My plan is for the menu to have several functions: Creating a new table, adding new countries, editing countries, deleting countries and searching
7for specific countries. This allows the user to make several adjustments to the table in case anything happens to go wrong.
8
9the value {0} represents the name of the table itself which can be seen as when the code is played, instead of it saying {0}, it uses the table name
10as the table_name variable appears as a result instead.
11
12
13
14"""
15
16# SQLITE is a c library that provides a lightweight disk-based datanase that doesnt require a seperate server process,
17# and allows accessing the database using a non-standard variant of the SQL query language
18#import allows for functions that are defined elsewhere to be used in this code.
19
20import sqlite3
21""" This function below is what creats the table by """
22
23# What is this? What is a function?
24
25#Custom functions
26
27# def is used to...
28def create_table(table_name,sql): #what are table_name and sql?
29    with sqlite3.connect(DATABASE) as db: # this allows for the function to be stored in a variable so that it is easier to write for the user.
30        print(table_name) #this prints the name of the database
31        cursor = db.cursor() # this enables traversal over the records in a database.
32        cursor.execute("select name from sqlite_master where name=?",(table_name,)) # This method executes the database, which the name is given by sqlite_master
33        result = cursor.fetchall() #this method fetches all the rows of a query result and returns a list of tuples.
34        keep_table = True
35        if len(result) == 1: #If the length of the results ==1, it shows there is a database, which means that to create a new table the current one would have to be deleted
36            response = input("The table {0} already exists, do you wish to recreate it? (y/n): ".format(table_name))
37            if response == "y": #if the user enters y, then the original table is overwritten and a new one is made
38                keep_table = False
39                print("The {0} table will be recreated - all existing data will be lost".format(table_name)) # prints this to tell the user everything is being deleted.
40                cursor.execute("drop table if exists {0}".format(table_name)) #drops the current table and replaces it with the new one.
41                db.commit() #updaes all the records in the table.
42            else:
43                print("The existing table was kept") #prints this if the user enters anything except y.
44        else:
45            keep_table = False
46        if not keep_table: #Do not understand that this does.
47            cursor.execute(sql)
48            db.commit()
49
50def query(sql,data):
51    with sqlite3.connect(DATABASE) as db:
52        cursor = db.cursor()
53        cursor.execute(sql,data)
54        db.commit()
55
56def display_menu():
57    print("Country Database")
58    print()
59    print("1. (Re)Create Country Table")
60    print("2. Add new country")
61    print("3. Edit existing country")
62    print("4. Delete existing country")
63    print("5. Search for countries")
64    print("6. Display the entire database")
65    print("0. Exit")
66    print()
67
68
69
70"""This option allows for the user to select a menu choice ranging from 0 to 5. 1 goes to the function that allows for the user to recreate the table
712 Allows for the user to add a new record to the table. 3 Allows the user to edit an existing records fields, 4 allows for a record to be deleted
72from the table and 5 allows for a record to be searched."""
73def get_menu_choice():
74    accepted = False
75    while not accepted:
76     choice = input("Please select an option:")
77     if "0" == choice or "1" == choice or "2" == choice or "3" == choice or "4" == choice or "5" == choice or "6" == choice:
78       accepted = True
79     else:
80      print("Pleae enter a valid value")
81    return choice
82
83def insert_data(values):
84    sql = "insert into Country (country,Capital,TimeZone,Population,Nationallanguage,currency) values(?,?,?,?,?,?)"
85    query(sql,values)
86
87def query_with_results(sql,data):
88    with sqlite3.connect(DATABASE) as db:
89        cursor = db.cursor()
90        if data == None:
91            cursor.execute(sql)
92        else:
93            cursor.execute(sql,data)
94        results = cursor.fetchall()
95        return results
96
97def query_with_single_result(sql,data):
98    with sqlite3.connect(DATABASE) as db:
99        cursor = db.cursor()
100        cursor.execute(sql,data)
101        result = cursor.fetchone()
102        return result
103
104""" This function is used to display the results of the search from the person using the database. Current issue is that due to the amount of
105fields the function is not displaying due to a "tuple index out of range" error appearing
106
107The print function is not printing all the column headings due to there being a tuple index out of range error, however the
108results are printing.
109
110https://stackoverflow.com/questions/20296188/indexerror-tuple-index-out-of-range-python
111one of the indexes may be incorrect.
112
113https://pypi.python.org/pypi/PTable/0.9.0
114
115Deleted one of the sql variables as it was not needed and a shortened table causes less problems.
116"""
117
118#added CountryID to sql variable
119def update_country(data):
120    sql = "update Country set Country=?, Capital=?, TimeZone=?, Population=?, Nationallanguage=?, Currency=? where CountryID=?"
121    print(sql)
122    query(sql,data)
123
124def display_select_results(results):
125    if results[0] != None:
126        print()
127        print("{0:<15} {1:<15} {2:<15} {3:<15} {4:<15} {5:<15} {6:<15}".format("Country ID","Country Name","Capital", "TimeZone", "Population","National Language","Currency"))
128        for result in results:
129            print("{0:<15} {1:<15} {2:<15} {3:<15} {4:<15} {5:<15} {6:<15}".format(result[0],result[1],result[2],result[3],result[4],result[5],result[6]))
130        print()
131    else:
132        print("The query returned no results")
133
134"""This displays all the records that fall under the criteria of the requirements"""
135def select_all_countries():
136    sql = "select * from Country"
137    return query_with_results(sql,None)
138
139"""the main function which gets called at the bottom. This allows people to be linked to the database as the functions given that can change the database can allow things to be edited
140or changed to have new things."""
141def main():
142    finished = False
143    while not finished:
144        display_menu()
145        choice = get_menu_choice()
146        if choice == "1":
147            sql = """create table Country
148            (CountryID integer,
149            Country text,
150            Capital text,
151            TimeZone text,
152            Population text,
153            Nationallanguage text,
154            Currency text,
155            primary key(CountryID))"""
156            create_table("Country",sql)
157        elif choice == "2":
158            country = input("Please enter name of new country: ")
159            Capital = (input("Please enter the capital of the country"))
160            TimeZone = input("What is the timezone of the capital")
161            Population = input("What is the population of the country")
162            Nationallanguage = input("What is the national language of the country")
163            currency = input("What is the currency of the country")
164            insert_data((country,Capital,TimeZone,Population,Nationallanguage,currency))
165        elif choice == "3":
166            countries = select_all_countries()
167            display_select_results(countries)
168            CountryID = int(input("Please enter the id of the product to edit: "))
169            country = input("Please enter new name for the Country: ")
170            Capital = (input("Please enter the Capital of the country: "))
171            TimeZone = input("What is the timezone of the capital")
172            Population = input("What is the population of the country")
173            Nationallanguage = input("What is the national language of the country")
174            currency = input("What is the currency of the country")
175            update_country((country,Capital,TimeZone,Population,Nationallanguage,currency,CountryID))
176        elif choice == "4":
177            countries = select_all_countries()
178            display_select_results(countries)
179            CountryID = int(input("Please enter the id of the country to delete: "))
180            Country = select_country(CountryID)
181            delete_country((Country[1],))
182        elif choice == "5":
183            name = input("Please enter the name of the country to search for: ")
184            country = select_country_with_name(name)
185            display_select_results([country])
186        elif choice == "6":
187            countries = select_all_countries()
188            display_select_results(countries)
189
190        elif choice == "0":
191            finished = True
192
193def select_country_with_name(name):
194    sql = "select * from Country where Country=?"
195    return query_with_single_result(sql,(name,))
196
197def select_country(id):
198    sql = "select * from Country where CountryID=?"
199    return query_with_single_result(sql,(id,))
200
201def delete_country(data):
202    sql = "delete from Country where Country=?"
203    query(sql,data)
204
205if __name__ == "__main__":
206    DATABASE = "countries.db"
207    main()