def join(cls, A, B, A_name, B_name, conditions):
"""This function join two table according to the condition
Inputs: A : Table
B : Table
A_name : str, name of table A
B_name : str, name of table B
conditions : str
Output: Table
"""
name_to_table = {A_name: A, B_name: B}
equality, operation = find_equality(conditions)
output = []
if equality:
print(equality)
for left, right in equality:
left_name, left_column = re.match(r'([^\.]+)\.([^\.]+)',
left).groups()
right_name, right_column = re.match(r'([^\.]+)\.([^\.]+)',
right).groups()
left_table = name_to_table[left_name]
right_table = name_to_table[right_name]
table = {left_name: 'left_row', right_name: 'right_row'}
row_condition, _ = construct_condition(conditions,
tablename=table)
if left_column in left_table.index:
for right_row in right_table.table:
for j in left_table.index[left_column].get(
right_row[right_column]):
left_row = left_table.table[j]
new_line = add_line(left_row, right_row, left_name,
right_name, row_condition)
if new_line:
output.append(new_line)
return cls(output)
elif right_column in right_table.index:
for left_row in left_table.table:
for j in right_table.index[right_column].get(
left_row[left_column]):
right_row = right_table.table[j]
new_line = add_line(left_row, right_row, left_name,
right_name, row_condition)
if new_line:
output.append(new_line)
return cls(output)
table = {A_name: 'left_row', B_name: 'right_row'}
row_condition, _ = construct_condition(conditions, tablename=table)
for left_row in A.table:
for right_row in B.table:
new_line = add_line(left_row, right_row, A_name, B_name,
row_condition)
if new_line:
output.append(new_line)
return cls(output)
def test_construct_condition(self):
'''Test the construct condtion'''
table = {'R1':'A_row', 'S':'B_row'}
test_case_join = '(R1.C2 >= S.C) and (S.B < 10)'
test_case_search = '(saleid >= 100) and (pricerange < 10)'
test_case_arithm = '(10*R1.C2 >= S.C) and (S.B/5 < 10)'
expect_arithm = '(10*A_row["C2"] >= B_row["C"]) and (B_row["B"]/5 < 10)'
output_join = construct_condition(test_case_join, tableName = table)
output_arithm = construct_condition(test_case_arithm, tableName = table)
output_search = construct_condition(test_case_search, table = 'R')
self.assertEqual('(A_row["C2"] >= B_row["C"]) and (B_row["B"] < 10)',output_join)
self.assertEqual('(R["saleid"] >= 100) and (R["pricerange"] < 10)', output_search)
self.assertEqual(expect_arithm, output_arithm)
def select(cls, table, conditions):
""" This function selects rows according to conditions in the table
Inputs: table : Table
condition : str
Output: Table
"""
data_under_condition = []
row_condition, columns = construct_condition(conditions, table='row')
equality, operation = find_equality(conditions)
index = set() # index is a set.
if equality: # use index only for equality
print(equality)
if not operation or operation == 'and': # if the logic operation is 'or' we don't use index.
for column, key in equality:
if column not in table.table[0]:
temp = key
key = column
column = temp
key = is_float(key)
print(column)
if column in table.index:
new_index = table.index[column].get(key)
if new_index:
index.update(new_index)
index = range(len(table.table)) if len(index) == 0 else index
for row_index in index:
row = table.table[row_index]
if eval(row_condition):
data_under_condition.append(row)
return cls(data_under_condition)
def test_construct_condition_with_single_equal_sign(self):
"""Test the construct condtion with single equal sign"""
table = {'R1': 'A_row', 'S': 'B_row'}
test_case = '(R1.C2 >= S.C) and (S.B < 10) and (R1.salesid = 5)'
expected = '(A_row["C2"] >= B_row["C"]) and (B_row["B"] < 10) and (A_row["salesid"] == 5)'
output, columns = construct_condition(test_case, tablename=table)
self.assertEqual(expected, output)
def test_construct_condition_with_more_than_two_condtion(self):
"""Test the construct condtion with more than two condition"""
table = {'R1': 'A_row', 'S': 'B_row'}
test_case = '(R1.C2 >= S.C) and (S.B < 10) and (R1.salesid <= 5)'
expected = '(A_row["C2"] >= B_row["C"]) and (B_row["B"] < 10) and (A_row["salesid"] <= 5)'
output, columns = construct_condition(test_case, tablename=table)
self.assertEqual(expected, output)
def join(cls, A, B, A_name, B_name, condition):
'''This function join two table according to the condition
Inputs: A : Table
B : Table
A_name : str, name of table A
B_name : str, name of table B
condtion : str
Output: Table
'''
table = {A_name: 'A_row', B_name: 'B_row'}
row_condition, columns = construct_condition(condition,
tableName=table)
output = []
for A_row in A.table:
for B_row in B.table:
if eval(row_condition):
new_row = {}
for key in A_row:
column = '{}_{}'.format(A_name, key)
new_row[column] = A_row[key]
for key in B_row:
column = '{}_{}'.format(B_name, key)
new_row[column] = B_row[key]
output.append(new_row)
return cls(output)
def select(cls, table, conditions):
''' This function selects rows according to conditions in the table
Inputs: table : Table
condtion : str
Output: Table
'''
data_under_condition = []
row_condition = construct_condition(conditions, table='row')
for row in table.table:
if eval(row_condition):
data_under_condition.append(row)
return cls(data_under_condition)
