def test_table_creation(): table_name = 'test_table_creation' fields = { 'primary_key_field': int, 'field_1': str, 'field_2': float, 'field_3': datetime, 'field_4': date, 'field_5': bool, 'field_6': Point, 'field_7': MultiPolygon, } with sqlite_connection() as connection: cursor = connection.cursor() if database_has_table(cursor, table_name): cursor.execute(f'DROP TABLE {table_name};') table = SQLiteTable( table_name=table_name, fields=fields, primary_key='primary_key_field', **CREDENTIALS['sqlite'], ) test_remote_fields = table.remote_fields with sqlite_connection() as connection: cursor = connection.cursor() test_raw_remote_fields = database_table_fields(cursor, table_name) if table.exists: table.delete_table() table_exists = database_has_table(cursor, table_name) if table_exists: cursor.execute(f'DROP TABLE {table_name};') assert test_remote_fields == fields assert list(test_raw_remote_fields) == list(fields) assert not table_exists
def test_nonexistent_field_in_inserted_record(): table_name = 'test_nonexistent_field_in_inserted_record' fields = { 'primary_key_field': int, 'field_1': str, 'field_2': float, 'field_3': str, } record_with_extra_field = { 'primary_key_field': 2, 'field_1': datetime(2020, 1, 2), 'nonexistent_field': 'test', } with sqlite_connection() as connection: cursor = connection.cursor() if database_has_table(cursor, table_name): cursor.execute(f'DROP TABLE {table_name};') table = SQLiteTable( table_name=table_name, fields=fields, primary_key='primary_key_field', **CREDENTIALS['sqlite'], ) table[ record_with_extra_field['primary_key_field']] = record_with_extra_field test_records = table.records with sqlite_connection() as connection: cursor = connection.cursor() cursor.execute(f'DROP TABLE {table_name};') del record_with_extra_field['nonexistent_field'] record_with_extra_field[ 'field_1'] = f'{record_with_extra_field["field_1"]}' record_with_extra_field['field_2'] = None record_with_extra_field['field_3'] = None assert test_records == [record_with_extra_field]
def test_compound_primary_key(): table_name = 'test_compound_primary_key' fields = { 'primary_key_field_1': int, 'primary_key_field_2': str, 'primary_key_field_3': datetime, 'field_1': float, 'field_2': str, } records = [ { 'primary_key_field_1': 1, 'primary_key_field_2': 'test 1', 'primary_key_field_3': datetime(2020, 1, 1), 'field_1': None, 'field_2': 'test 1', }, { 'primary_key_field_1': 2, 'primary_key_field_2': 'test 1', 'primary_key_field_3': datetime(2020, 1, 2), 'field_1': 5.67, 'field_2': None, }, ] extra_record = { 'primary_key_field_1': 3, 'primary_key_field_2': 'test 3', 'primary_key_field_3': datetime(2020, 1, 3), 'field_1': 2.5, 'field_2': None, } extra_record_to_insert = { 'primary_key_field_2': 'overwritten value', 'primary_key_field_3': datetime(2020, 1, 3), 'field_1': 2.5, 'field_2': None, } primary_key = ('primary_key_field_1', 'primary_key_field_2', 'primary_key_field_3') with sqlite_connection() as connection: cursor = connection.cursor() if database_has_table(cursor, table_name): cursor.execute(f'DROP TABLE {table_name};') table = SQLiteTable(table_name=table_name, fields=fields, primary_key=primary_key, **CREDENTIALS['sqlite']) test_primary_key = primary_key table.insert(records) with pytest.raises(ValueError): table[1] with pytest.raises(IndexError): table[1] = extra_record_to_insert table[3, 'test 3', datetime(2020, 1, 3)] = extra_record_to_insert test_record = table[1, 'test 1', datetime(2020, 1, 1)] test_records = table.records with sqlite_connection() as connection: cursor = connection.cursor() test_raw_remote_fields = database_table_fields(cursor, table_name) if database_has_table(cursor, table_name): cursor.execute(f'DROP TABLE {table_name};') assert test_primary_key == primary_key assert test_records == records + [extra_record] assert test_record == records[0] assert list(test_raw_remote_fields) == list(fields)
def test_records_intersecting_polygon(): table_name = 'test_records_intersecting_polygon' fields = { 'primary_key_field': int, 'field_1': str, 'field_2': MultiPolygon, 'field_3': MultiPolygon, } crs = CRS.from_epsg(4326) inside_polygon = box(-77.7, 39.725, -77.4, 39.8) touching_polygon = box(-77.1, 39.575, -76.8, 39.65) outside_polygon = box(-77.7, 39.425, -77.4, 39.5) containing_polygon = box(-77.7, 39.65, -77.1, 39.8) # projected_containing_polygon = box(268397.8, 4392279.8, 320292.0, 4407509.6) multipolygon = MultiPolygon([inside_polygon, touching_polygon]) records = [ { 'primary_key_field': 1, 'field_1': 'inside box', 'field_2': MultiPolygon([inside_polygon]), 'field_3': None, }, { 'primary_key_field': 2, 'field_1': 'containing box', 'field_2': MultiPolygon([containing_polygon]), 'field_3': None, }, { 'primary_key_field': 3, 'field_1': 'outside box with multipolygon', 'field_2': MultiPolygon([outside_polygon]), 'field_3': multipolygon, }, ] with sqlite_connection() as connection: cursor = connection.cursor() if database_has_table(cursor, table_name): cursor.execute(f'DROP TABLE {table_name};') table = SQLiteTable( table_name=table_name, fields=fields, primary_key='primary_key_field', crs=crs, **CREDENTIALS['sqlite'], ) table.insert(records) test_query_1 = table.records_intersecting(inside_polygon) test_query_2 = table.records_intersecting(containing_polygon) test_query_3 = table.records_intersecting(inside_polygon, geometry_fields=['field_2']) test_query_4 = table.records_intersecting(containing_polygon, geometry_fields=['field_2']) # TODO fix SRID transformation from 32618 # test_query_5 = table.records_intersecting( # projected_containing_polygon, crs=CRS.from_epsg(32618), geometry_fields=['field_2'] # ) with sqlite_connection() as connection: cursor = connection.cursor() cursor.execute(f'DROP TABLE {table_name};') assert test_query_1 == records assert test_query_2 == records assert test_query_3 == records[:2] assert test_query_4 == records[:2]
def test_field_reorder(): table_name = 'test_field_reorder' fields = { 'primary_key_field': int, 'field_1': datetime, 'field_2': float, 'field_3': str, 'field_4': date, } reordered_fields = { 'field_4': date, 'field_2': float, 'primary_key_field': int, 'field_1': datetime, 'field_3': str, } records = [{ 'primary_key_field': 1, 'field_1': datetime(2020, 1, 1), 'field_3': 'test 1', 'field_4': date(2020, 1, 2), }] with sqlite_connection() as connection: cursor = connection.cursor() if database_has_table(cursor, table_name): cursor.execute(f'DROP TABLE {table_name};') table = SQLiteTable( table_name=table_name, fields=fields, primary_key='primary_key_field', **CREDENTIALS['sqlite'], ) table.insert(records) test_records = table.records with sqlite_connection() as connection: cursor = connection.cursor() test_fields = database_table_fields(cursor, table_name) reordered_table = SQLiteTable( table_name=table_name, fields=reordered_fields, primary_key='primary_key_field', **CREDENTIALS['sqlite'], ) test_reordered_records = reordered_table.records with sqlite_connection() as connection: cursor = connection.cursor() test_reordered_fields = database_table_fields(cursor, table_name) cursor.execute(f'DROP TABLE {table_name};') assert list(test_fields) == list(fields) assert list(test_reordered_fields) == list(reordered_fields) for test_records in (test_records, test_reordered_records): for record_index, record in enumerate(records): test_record = test_records[record_index] for field, value in record.items(): assert test_record[field] == value
def test_records_where(): table_name = 'test_records_where' fields = {'primary_key_field': int, 'field_1': datetime, 'field_2': str} records = [ { 'primary_key_field': 1, 'field_1': datetime(2020, 1, 1), 'field_2': 'test 1' }, { 'primary_key_field': 2, 'field_1': datetime(2020, 1, 2), 'field_2': 'test 2' }, { 'primary_key_field': 3, 'field_1': datetime(2020, 1, 3), 'field_2': 'test 3' }, { 'primary_key_field': 4, 'field_1': datetime(2020, 1, 4), 'field_2': None }, ] with sqlite_connection() as connection: cursor = connection.cursor() if database_has_table(cursor, table_name): cursor.execute(f'DROP TABLE {table_name};') table = SQLiteTable( table_name=table_name, fields=fields, primary_key='primary_key_field', **CREDENTIALS['sqlite'], ) table.insert(records) test_record_query_1 = table.records_where( {'field_1': datetime(2020, 1, 1)}) test_record_query_2 = table.records_where( {'field_2': ['test 1', 'test 3']}) test_record_query_3 = table.records_where({'primary_key_field': range(3)}) test_record_query_4 = table.records_where({'field_2': 'test%'}) test_record_query_5 = table.records_where( "field_1 = '2020-01-02 00:00:00'") test_record_query_6 = table.records_where( ["field_1 = '2020-01-02 00:00:00'", "field_2 IN ('test 1', 'test 2')"]) test_record_query_7 = table.records_where({'field_2': None}) with pytest.raises(sqlite3.OperationalError): table.records_where('nonexistent_field = 4') with pytest.raises(sqlite3.OperationalError): table.records_where('bad_ syn = tax') with pytest.raises(NotImplementedError): table.records_where(1) table.delete_where({'field_1': datetime(2020, 1, 1)}) test_records_after_deletion = table.records with sqlite_connection() as connection: cursor = connection.cursor() cursor.execute(f'DROP TABLE {table_name};') assert test_record_query_1 == [records[0]] assert test_record_query_2 == [records[0], records[2]] assert test_record_query_3 == records[:2] assert test_record_query_4 == records[:3] assert test_record_query_5 == [records[1]] assert test_record_query_6 == [records[1]] assert test_record_query_7 == [records[3]] assert test_records_after_deletion == records[1:]
def test_table_flexibility(): table_name = 'test_table_flexibility' fields = { 'primary_key_field': int, 'field_1': datetime, 'field_2': float, 'field_3': str, } incomplete_fields = {'primary_key_field': int, 'field_3': str} records = [{ 'primary_key_field': 1, 'field_1': datetime(2020, 1, 1), 'field_3': 'test 1' }] with sqlite_connection() as connection: cursor = connection.cursor() if database_has_table(cursor, table_name): cursor.execute(f'DROP TABLE {table_name};') # create table with incomplete fields incomplete_table = SQLiteTable( table_name=table_name, fields=incomplete_fields.copy(), primary_key='primary_key_field', **CREDENTIALS['sqlite'], ) incomplete_table.insert(records) incomplete_records = incomplete_table.records with sqlite_connection() as connection: cursor = connection.cursor() test_incomplete_remote_fields = database_table_fields( cursor, table_name) # create table with complete fields, pointing to existing remote table with incomplete fields complete_table = SQLiteTable( table_name=table_name, fields=fields, primary_key='primary_key_field', **CREDENTIALS['sqlite'], ) complete_records = complete_table.records with sqlite_connection() as connection: cursor = connection.cursor() test_complete_remote_fields = database_table_fields(cursor, table_name) # create table with incomplete fields, pointing to existing remote table with complete fields completed_table = SQLiteTable( table_name=table_name, fields=incomplete_fields.copy(), primary_key='primary_key_field', **CREDENTIALS['sqlite'], ) completed_records = completed_table.records with sqlite_connection() as connection: cursor = connection.cursor() test_completed_remote_fields = database_table_fields( cursor, table_name) cursor.execute(f'DROP TABLE {table_name};') assert list(test_incomplete_remote_fields) == list(incomplete_fields) assert list(test_complete_remote_fields) == list(fields) assert list(test_completed_remote_fields) == list(fields) for test_records in (incomplete_records, complete_records, completed_records): for record_index, record in enumerate(test_records): record = records[record_index] for field, value in record.items(): assert value == record[field]
def test_record_insertion(): table_name = 'test_record_insertion' fields = { 'primary_key_field': int, 'field_1': datetime, 'field_2': float, 'field_3': str, 'field_4': bool, } records = [ { 'primary_key_field': 1, 'field_1': datetime(2020, 1, 1), 'field_3': 'test 1', 'field_4': None, }, { 'primary_key_field': 2, 'field_1': datetime(2020, 1, 2), 'field_2': 5.67, 'field_4': True, }, ] extra_record = { 'primary_key_field': 3, 'field_1': datetime(2020, 1, 3), 'field_2': 3, 'field_3': 'test 3', 'field_4': False, } with sqlite_connection() as connection: cursor = connection.cursor() if database_has_table(cursor, table_name): cursor.execute(f'DROP TABLE {table_name};') table = SQLiteTable( table_name=table_name, fields=fields, primary_key='primary_key_field', **CREDENTIALS['sqlite'], ) table.insert(records) test_records_before_addition = table.records table[extra_record['primary_key_field']] = extra_record test_records_after_addition = table.records del table[extra_record['primary_key_field']] test_records_after_deletion = table.records records[0]['field_2'] = None records[1]['field_3'] = None assert records[0] in table assert records[0]['primary_key_field'] in table assert (records[0][field] for field in ['primary_key_field']) in table assert 'nonexistant' not in table assert len(table) == 2 with pytest.raises(ValueError): key_without_primary_key = { field: records[0][field] for field in records[0] if field not in ['primary_key_field'] } key_without_primary_key in table table.insert(records[0]) with sqlite_connection() as connection: cursor = connection.cursor() cursor.execute(f'DROP TABLE {table_name};') assert test_records_before_addition == records assert test_records_after_addition == records + [extra_record] assert test_records_after_deletion == records