def test_storage():
# Get resources
articles_descriptor = json.load(io.open('data/articles.json', encoding='utf-8'))
comments_descriptor = json.load(io.open('data/comments.json', encoding='utf-8'))
articles_rows = Stream('data/articles.csv', headers=1).open().read()
comments_rows = Stream('data/comments.csv', headers=1).open().read()
# Engine
engine = create_engine(os.environ['DATABASE_URL'])
# Storage
storage = Storage(engine=engine, prefix='test_storage_')
# Delete buckets
storage.delete()
# Create buckets
storage.create(
['articles', 'comments'],
[articles_descriptor, comments_descriptor],
indexes_fields=[[['rating'], ['name'], ['created_datetime']], []])
# Recreate bucket
storage.create('comments', comments_descriptor, force=True)
# Write data to buckets
storage.write('articles', articles_rows)
gen = storage.write('comments', comments_rows, as_generator=True)
lst = list(gen)
assert len(lst) == 1
# Create new storage to use reflection only
storage = Storage(engine=engine, prefix='test_storage_')
# Create existent bucket
with pytest.raises(RuntimeError):
storage.create('articles', articles_descriptor)
# Assert representation
assert repr(storage).startswith('Storage')
# Assert buckets
assert storage.buckets == ['articles', 'comments']
# Assert descriptors
assert storage.describe('articles') == sync_descriptor(articles_descriptor)
assert storage.describe('comments') == sync_descriptor(comments_descriptor)
# Assert rows
assert list(storage.read('articles')) == sync_rows(articles_descriptor, articles_rows)
assert list(storage.read('comments')) == sync_rows(comments_descriptor, comments_rows)
# Delete non existent bucket
with pytest.raises(RuntimeError):
storage.delete('non_existent')
# Delete buckets
storage.delete()
def test_only_parameter():
# Check the 'only' parameter
# Get resources
simple_descriptor = json.load(io.open('data/simple.json', encoding='utf-8'))
# Engine
engine = create_engine(os.environ['DATABASE_URL'], echo=True)
# Storage
storage = Storage(engine=engine, prefix='test_only_')
# Delete buckets
storage.delete()
# Create buckets
storage.create(
'names',
simple_descriptor,
indexes_fields=[['person_id']])
def only(table):
ret = 'name' not in table
return ret
engine = create_engine(os.environ['DATABASE_URL'], echo=True)
storage = Storage(engine=engine, prefix='test_only_', reflect_only=only)
# Delete non existent bucket
with pytest.raises(RuntimeError):
storage.delete('names')
def handle_resource(self, resource, spec, parameters, datapackage):
resource_name = spec['name']
if resource_name not in self.converted_resources:
return resource
else:
converted_resource = self.converted_resources[resource_name]
mode = converted_resource.get('mode', 'rewrite')
table_name = converted_resource['table-name']
storage = Storage(self.engine, prefix=table_name)
if mode == 'rewrite' and '' in storage.buckets:
storage.delete('')
if '' not in storage.buckets:
logging.info('Creating DB table %s', table_name)
storage.create('', spec['schema'])
update_keys = None
if mode == 'update':
update_keys = converted_resource.get('update_keys')
if update_keys is None:
update_keys = spec['schema'].get('primaryKey', [])
logging.info('Writing to DB %s -> %s (mode=%s, keys=%s)',
resource_name, table_name, mode, update_keys)
return storage.write('',
resource,
keyed=True,
as_generator=True,
update_keys=update_keys)
def create_storage_adaptor(connection_string,
db_schema,
geometry_support,
from_srid=None,
to_srid=None):
engine = create_engine(connection_string)
storage = Storage(engine,
dbschema=db_schema,
geometry_support=geometry_support,
from_srid=from_srid,
to_srid=to_srid,
views=True)
return engine, storage
def test_storage_bigdata():
# Generate schema/data
descriptor = {'fields': [{'name': 'id', 'type': 'integer'}]}
rows = [{'id': value} for value in range(0, 2500)]
# Push rows
engine = create_engine(os.environ['DATABASE_URL'])
storage = Storage(engine=engine, prefix='test_storage_bigdata_')
storage.create('bucket', descriptor, force=True)
storage.write('bucket', rows, keyed=True)
# Pull rows
assert list(storage.read('bucket')) == list(map(lambda x: [x['id']], rows))
def test_bad_type():
# Engine
engine = create_engine(os.environ['DATABASE_URL'])
# Storage
storage = Storage(engine=engine, prefix='test_bad_type_')
with pytest.raises(TypeError):
storage.create('bad_type', {
'fields': [
{
'name': 'bad_field',
'type': 'any'
}
]
})
def test_storage_bigdata_rollback():
# Generate schema/data
descriptor = {'fields': [{'name': 'id', 'type': 'integer'}]}
rows = [(value,) for value in range(0, 2500)] + [('bad-value',)]
# Push rows
engine = create_engine(os.environ['DATABASE_URL'])
storage = Storage(engine=engine, prefix='test_storage_bigdata_rollback_')
storage.create('bucket', descriptor, force=True)
try:
storage.write('bucket', rows)
except Exception:
pass
# Pull rows
assert list(storage.read('bucket')) == []
def test_update():
# Get resources
descriptor = json.load(io.open('data/original.json', encoding='utf-8'))
original_rows = Stream('data/original.csv', headers=1).open().read()
update_rows = Stream('data/update.csv', headers=1).open().read()
update_keys = ['person_id', 'name']
# Engine
engine = create_engine(os.environ['DATABASE_URL'])
# Storage
storage = Storage(engine=engine, prefix='test_update_', autoincrement='__id')
# Delete buckets
storage.delete()
# Create buckets
storage.create('colors', descriptor)
# Write data to buckets
storage.write('colors', original_rows, update_keys=update_keys)
gen = storage.write('colors', update_rows, update_keys=update_keys, as_generator=True)
gen = list(gen)
assert len(gen) == 5
assert len(list(filter(lambda i: i.updated, gen))) == 3
assert list(map(lambda i: i.updated_id, gen)) == [5, 3, 6, 4, 5]
storage = Storage(engine=engine, prefix='test_update_', autoincrement='__id')
gen = storage.write('colors', update_rows, update_keys=update_keys, as_generator=True)
gen = list(gen)
assert len(gen) == 5
assert len(list(filter(lambda i: i.updated, gen))) == 5
assert list(map(lambda i: i.updated_id, gen)) == [5, 3, 6, 4, 5]
# Create new storage to use reflection only
storage = Storage(engine=engine, prefix='test_update_')
rows = list(storage.iter('colors'))
assert len(rows) == 6
color_by_person = dict(
(row[1], row[3])
for row in rows
)
assert color_by_person == {
1: 'blue',
2: 'green',
3: 'magenta',
4: 'sunshine',
5: 'peach',
6: 'grey'
}
# Storage without autoincrement
storage = Storage(engine=engine, prefix='test_update_')
storage.delete()
storage.create('colors', descriptor)
storage.write('colors', original_rows, update_keys=update_keys)
gen = storage.write('colors', update_rows, update_keys=update_keys, as_generator=True)
gen = list(gen)
assert len(gen) == 5
assert len(list(filter(lambda i: i.updated, gen))) == 3
assert list(map(lambda i: i.updated_id, gen)) == [None, None, None, None, None]
from sqlalchemy import create_engine
#from dotenv import load_dotenv; load_dotenv('.env')
from jsontableschema_sql import Storage
# Get resources
articles_schema = json.load(io.open('data/articles.json', encoding='utf-8'))
comments_schema = json.load(io.open('data/comments.json', encoding='utf-8'))
articles_data = topen('data/articles.csv', with_headers=True).read()
comments_data = topen('data/comments.csv', with_headers=True).read()
# Engine
engine = create_engine(os.environ['DATABASE_URL'])
# Storage
storage = Storage(engine=engine, prefix='prefix_')
# Delete tables
for table in reversed(storage.buckets):
storage.delete(table)
# Create tables
storage.create(['articles', 'comments'], [articles_schema, comments_schema])
print(articles_data)
# Write data to tables
storage.write('articles', articles_data)
storage.write('comments', comments_data)
# List tables
