def test_99_cartesian_product_mutable(self):
t1 = Table()
t1.set_dict({'a': ['b', 'c'], 'x': ['y', 'z']})
t1copy = {'a': ['b', 'c'], 'x': ['y', 'z']}
t2 = Table()
t2.set_dict({'d': ['e', 'f'], 'u': ['v', 'w']})
t2copy = {'d': ['e', 'f'], 'u': ['v', 'w']}
a3.cartesian_product(t2, t1)
self.assertEqual(t1.get_dict(), t1copy, "t1 should not be modified")
self.assertEqual(t2.get_dict(), t2copy, "t1 should not be modified")
def test_09_cartesian_product_zero_one_rows(self):
t1 = Table()
t1.set_dict({'a': []})
t2 = Table()
t2.set_dict({'e': ['f']})
expected = {'a': [], 'e': []}
res = a3.cartesian_product(t1, t2)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"no rows and one row")
def test_03_cartesian_product_two_col2_one_row(self):
t1 = Table()
t1.set_dict({'a': ['b'], 'x': ['y']})
t2 = Table()
t2.set_dict({'c': ['d']})
expected = {'a': ['b'], 'c': ['d'], 'x': ['y']}
res = a3.cartesian_product(t2, t1)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"two columns in second table, one row")
def test_01_cartesian_product_one_col_one_row(self):
t1 = Table()
t1.set_dict({'a': ['b']})
t2 = Table()
t2.set_dict({'c': ['d']})
expected = {'a': ['b'], 'c': ['d']}
res = a3.cartesian_product(t1, t2)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"one column one row")
def test_one_empty(self):
d1={}
d2={'a.a': ['b', 'c'], 'b.b': ['d', 'e']}
t1 = Table()
t2 = Table()
t1.set_dict(d1)
t2.set_dict(d2)
result_table = squeal.cartesian_product(t1, t2)
result_dict = result_table.get_dict()
expected_dict = {}
self.assertEqual(result_dict, expected_dict)
def test_01_blank_tables(self):
# start with two tables that will be empty
t1 = Table()
t2 = Table()
# the result of the cartesian product of two empty tables
# should iteslf be an empty table
result = squeal.cartesian_product(t1, t2)
# we'll know it's an empty table if its dictionary is empty
result_dict = result.get_dict()
expected = {}
self.assertEqual(result_dict, expected,
"product of two empty tables should be empty")
def test_06_cartesian_product_two_col_three_three_row(self):
t1 = Table()
t1.set_dict({'a': ['b', 'c', 'q'], 'x': ['y', 'z', 'r']})
t2 = Table()
t2.set_dict({'d': ['e', 'f', 'l'], 'u': ['v', 'w', 'm']})
expected = {
'a': ['b', 'c', 'q', 'b', 'c', 'q', 'b', 'c', 'q'],
'x': ['y', 'z', 'r', 'y', 'z', 'r', 'y', 'z', 'r'],
'd': ['e', 'e', 'e', 'f', 'f', 'f', 'l', 'l', 'l'],
'u': ['v', 'v', 'v', 'w', 'w', 'w', 'm', 'm', 'm']
}
res = a3.cartesian_product(t1, t2)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"two columns three rows")
def test_04_cartesian_product_two_col_two_row(self):
t1 = Table()
t1.set_dict({'a': ['b', 'c'], 'x': ['y', 'z']})
t2 = Table()
t2.set_dict({'d': ['e', 'f'], 'u': ['v', 'w']})
expected = {
'a': ['b', 'c', 'b', 'c'],
'x': ['y', 'z', 'y', 'z'],
'd': ['e', 'e', 'f', 'f'],
'u': ['v', 'v', 'w', 'w']
}
res = a3.cartesian_product(t2, t1)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"two columns two rows")
def test_03_blank_table_second(self):
# initialize two tables
table_1 = Table()
table_2 = Table()
# set table 2
table_2.set_dict({'t2.title': ['CSCA', '08']})
# the result should return 0 row, and titles of t2
result = squeal.cartesian_product(table_1, table_2)
# we'll know it's an empty table if its dictionary is empty
# but t2 has title, so we should return its title
result_dict = result.get_dict()
expected = {'t2.title': []}
self.assertEqual(result_dict, expected,
'''if one table is empty, result should be empty,
and return another tables' title''')
def test_06_two_blank_rows(self):
# initialize two tables
table_1 = Table()
table_2 = Table()
# two tables both have no row
table_1.set_dict({'t1.title': []})
table_2.set_dict({'t2.title': []})
# the result of the cartesian product of a table without any row
# it should return no row
result = squeal.cartesian_product(table_1, table_2)
# if there is no row for one table, then result should be no row
result_dict = result.get_dict()
expected = {'t2.title': [], 't1.title': []}
self.assertEqual(result_dict, expected,
'''if two tables has no row, then there is no row
to return but title names''')
def test_04_blank_row_second(self):
# initialize two tables
table_1 = Table()
table_2 = Table()
# table 1 has no row only titles, but table 2 has
table_1.set_dict({'t1.title': []})
table_2.set_dict({'t2.title': ['CSCA', '08']})
# the result of the cartesian product of a table without any row
# it should return no row
result = squeal.cartesian_product(table_1, table_2)
# if there is no row for one table, then result should be no row
result_dict = result.get_dict()
expected = {'t2.title': [], 't1.title': []}
self.assertEqual(result_dict, expected,
'''if one table has no row, product should be empty,
but it has all the title names''')
def test_07_normal_cases(self):
# initialize two tables
table_1 = Table()
table_2 = Table()
# set two tables
table_1.set_dict({'CSC': ['A08', 'A67'], 'MAT': ['A31', 'A23']})
table_2.set_dict({'c.A08': ['term work', 'term tests', 'final']})
result = squeal.cartesian_product(table_1, table_2)
# get the result dict of cartesian product
result_dict = result.get_dict()
expected = {'MAT': ['A31', 'A31', 'A31', 'A23', 'A23', 'A23'],
'c.A08': ['term work', 'term tests', 'final', 'term work',
'term tests', 'final'],
'CSC': ['A08', 'A08', 'A08', 'A67', 'A67', 'A67']}
self.assertEqual(result_dict, expected,
'''your cartesian product must get something
wrong''')
def test_07_cartesian_product_one_col_three_six_row(self):
t1 = Table()
t1.set_dict({'a': ['b', 'c', 'd']})
t2 = Table()
t2.set_dict({'e': ['f', 'g', 'h', 'i', 'j', 'k']})
expected = {
'a': [
'b', 'c', 'd', 'b', 'c', 'd', 'b', 'c', 'd', 'b', 'c', 'd',
'b', 'c', 'd', 'b', 'c', 'd'
],
'e': [
'f', 'f', 'f', 'g', 'g', 'g', 'h', 'h', 'h', 'i', 'i', 'i',
'j', 'j', 'j', 'k', 'k', 'k'
]
}
res = a3.cartesian_product(t1, t2)
self.assertTrue(equiv.equiv_tables(res.get_dict(), expected),
"two columns three and six rows")
def test_08_normal_test(self):
# initialize two tables
table_1 = Table()
table_2 = Table()
# set two tables
table_1.set_dict({'m.A31': ['homework', 'midterm', 'final']})
table_2.set_dict({'c.A08': ['term work', 'term tests', 'final']})
result = squeal.cartesian_product(table_1, table_2)
# get the result dict of cartesian product
result_dict = result.get_dict()
expected = {'m.A31': ['homework', 'homework', 'homework', 'midterm',
'midterm', 'midterm', 'final', 'final', 'final'],
'c.A08': ['term work', 'term tests', 'final', 'term work',
'term tests', 'final', 'term work',
'term tests', 'final']}
self.assertEqual(result_dict, expected,
'''your cartesian product must get something
wrong''')
def test_11_normal_test(self):
# initialize two tables
table_1 = Table()
table_2 = Table()
# set two tables
table_1.set_dict({'a': ['1', '2', '3', '4']})
table_2.set_dict({'b': ['10', '11', '12'], 'c': ['99', '98', '97']})
result = squeal.cartesian_product(table_2, table_1)
# get the result dict of cartesian product
result_dict = result.get_dict()
expected = {'a': ['1', '2', '3', '4', '1', '2', '3', '4', '1', '2',
'3', '4'],
'b': ['10', '10', '10', '10', '11', '11', '11', '11',
'12', '12', '12', '12'],
'c': ['99', '99', '99', '99', '98', '98', '98', '98',
'97', '97', '97', '97']}
self.assertEqual(result_dict, expected,
'''your cartesian product must get something
wrong''')
'Creating an empty table is working incorrectly.'
d = Database()
assert is_database(d), \
'Creating an empty database is working incorrectly.'
# typecheck the reading.py functions
result = reading.read_table('csv_files/books.csv')
assert is_table(result), \
'read_table should return a table; please check the handout \
for the definition of a table.'
# typecheck reading.read_database
result = reading.read_database()
assert is_database(result), \
'read_database should return a database; please check the handout \
for the definition of a database.'
# typecheck the required squeal.py function
import squeal
d1 = {'a': ['b', 'c']}
d2 = {'d': ['e', 'f']}
t1 = Table()
t2 = Table()
t1.set_dict(d1)
t2.set_dict(d2)
result = squeal.cartesian_product(t1, t2)
assert is_table(result), \
'cartesian_product should return a table; please check the handout \
for the definition of a table.'
# typecheck the db_io.py functions
# typecheck db_io.read_table
result = db_io.read_table(open('movies.csv'))
assert is_table(result), \
'read_table should return a table; please check the handout \
for the definition of a table.'
# typecheck db_io.read_database
result = db_io.read_database()
assert is_database(result), \
'read_database should return a database; please check the handout \
for the definition of a database.'
# typecheck the required squeal.py function
import squeal
t1 = {'a':['b', 'c']}
t2 = {'d':['e', 'f']}
result = squeal.cartesian_product(t1, t2)
assert is_table(result), \
'cartesian_product should return a table; please check the handout \
for the definition of a table.'
def main():
""" () -> NoneType
Ask for queries from the keyboard; stop when empty line is received. For
each query, process it and use db_io.print_csv to print the results.
"""
# Write your main function body here.
# An infinite while loop continues until a blank line is entered
while True:
#ask user for input
choose_query = input('')
#if an empty line is entered, break the loop
if choose_query == '':
break
index_of_from = choose_query.index('from')
#Saperate the titles given by the user from the user input
list_of_titles = choose_query[7:index_of_from-1].split(',')
list_of_where_names = []
#If the optional query where is used, saperates the title which is used in the where query
if 'where' in choose_query :
index_of_where = choose_query.index('where')
#Saperate the table names given by the user from the user input
list_of_tables_names = choose_query[index_of_from+5:index_of_where-1\
].split(',')
#Saperate the title names from where query
#given by the user from the user input
if '=' in choose_query:
list_of_where_names = choose_query[index_of_where+6:].split('=')
elif '>' in choose_query:
list_of_where_names = choose_query[index_of_where+6:].split('>')
else:
#Saperate the table names given by the user from the user input
list_of_tables_names = choose_query[index_of_from+5:].split(',')
#Creating a list for storing the tables which the user is required to use
list_of_tables = []
#Put the correct tables with the names user given into the list_of_tables list.
for items in list_of_tables_names:
list_of_tables.append(db_io.read_table(items+'.csv'))
#if the title names given is a '*', then use all the titles in the tables
if list_of_titles[0] == '*' :
list_of_titles = []
for items in list_of_tables :
for key in items.keys():
list_of_titles.append(key)
#product_table represents the table or cartesian product
#of two tables from the user's input
product_table = {}
if len (list_of_tables) == 1:
product_table = list_of_tables[0]
else:
product_table = squeal.cartesian_product(list_of_tables[0],\
list_of_tables[1])
#Check if the second value in the where query is a value or a column name
value_or_column_name = 'value'
for key in product_table.keys():
if 'where' in choose_query and key == list_of_where_names[1]:
value_or_column_name = 'column'
#procesed_table represents the processed table after operation
#from the user's where query, which is optional
processed_table = {}
#If where query is entered, added the missing title, (which is the
#title after the = operation in the user input) into the product
if 'where' in choose_query and value_or_column_name == 'column':
processed_table[list_of_where_names[1]] = \
product_table[list_of_where_names[1]]
#Eliminate all the titles which the user did not request in the query
for key in product_table.keys() :
if key in list_of_titles:
processed_table[key] = product_table[key]
#Get keys from the processed_table for further use
processed_table_keys = []
#A variable use to save the length of a row
row_length = 0
#If where query is used by the user, then operate the where query
if 'where' in choose_query :
#Loop each row in the table and check for duplicate in the given titles
for row_count in range(len(processed_table[list_of_where_names[0]])) :
row_length = len(processed_table[list_of_where_names[0]])
#If the information in the given titles from the where query
#are the same, then mark the row as "DELETEDROW"
if '=' in choose_query and value_or_column_name == 'column':
if processed_table[list_of_where_names[0]][row_count] != \
processed_table[list_of_where_names[1]][row_count] :
for key in list(processed_table.keys()) :
processed_table[key][row_count] = "DELETEDROW"
#are greater than the other, then mark the row as "DELETEDROW"
elif '>' in choose_query and value_or_column_name == 'column':
if processed_table[list_of_where_names[0]][row_count] <= \
processed_table[list_of_where_names[1]][row_count] :
for key in list(processed_table.keys()) :
processed_table[key][row_count] = "DELETEDROW"
if '=' in choose_query and value_or_column_name == 'value':
if processed_table[list_of_where_names[0]][row_count] != \
list_of_where_names[1] :
for key in list(processed_table.keys()) :
processed_table[key][row_count] = "DELETEDROW"
elif '>' in choose_query and value_or_column_name == 'value':
if processed_table[list_of_where_names[0]][row_count] <= \
list_of_where_names[1]:
for key in list(processed_table.keys()) :
processed_table[key][row_count] = "DELETEDROW"
#Delete the title after = operator given in the where query
if value_or_column_name == 'column' and list_of_where_names[0] \
!= list_of_where_names[1] :
del processed_table[list_of_where_names[1]]
position_changes = 0
#Check all the keys in the processed_table
for key in list(processed_table.keys()) :
position_changes = 0
for row_count in range(row_length) :
#Delete all the rows with the letter 'DELETEDROW'
if processed_table[key][row_count-position_changes] \
== "DELETEDROW" :
del processed_table[key][row_count-position_changes]
position_changes = position_changes + 1
#Print the table
db_io.print_csv(processed_table)
