def test_table_creation(self):
dd = SQL(self.engine,
'testtable',
schema='{name: string, amount: int}',
primary_key='name')
assert self.engine.has_table('testtable')
assert dd.table.columns.get('name').primary_key
assert not dd.table.columns.get('amount').primary_key
assert dd.dshape == dshape('var * {name: string, amount: int}')
assert raises(ValueError, lambda: SQL(self.engine, 'testtable2'))
def test_chunks(self):
schema = '{name: string, amount: int32}'
dd = SQL(self.engine, 'testtable3',
schema=schema,
primary_key='name')
data_list = [('Alice', 100), ('Bob', 50), ('Charlie', 200)]
data_dict = [{'name': name, 'amount': amount} for name, amount in data_list]
chunk = nd.array(data_list, dtype=str(dd.dshape))
dd.extend_chunks([chunk])
assert list(iter(dd)) == data_list or list(iter(dd)) == data_dict
self.assertEquals(len(list(dd.chunks(blen=2))), 2)
def test_chunks(self):
schema = '{name: string, amount: int32}'
dd = SQL(self.engine, 'testtable3', schema=schema, primary_key='name')
data_list = [('Alice', 100), ('Bob', 50), ('Charlie', 200)]
data_dict = [{
'name': name,
'amount': amount
} for name, amount in data_list]
chunk = nd.array(data_list, dtype=str(dd.dshape))
dd.extend_chunks([chunk])
assert list(iter(dd)) == data_list or list(iter(dd)) == data_dict
self.assertEquals(len(list(dd.chunks(blen=2))), 2)
def test_extension(self):
dd = SQL(self.engine, 'testtable2',
schema='{name: string, amount: int32}',
primary_key='name')
data_list = [('Alice', 100), ('Bob', 50)]
data_dict = [{'name': name, 'amount': amount} for name, amount in data_list]
dd.extend(data_dict)
with self.engine.connect() as conn:
results = conn.execute('select * from testtable2')
self.assertEquals(list(results), data_list)
assert list(iter(dd)) == data_list or list(iter(dd)) == data_dict
assert dd.as_py() == data_list or dd.as_py() == data_dict
def test_csv_sql_json(self):
data = [('Alice', 100), ('Bob', 200)]
text = '\n'.join(','.join(map(str, row)) for row in data)
schema = '{name: string, amount: int}'
engine = create_engine('sqlite:///:memory:')
with filetext(text) as csv_fn:
with filetext('') as json_fn:
csv = CSV(csv_fn, mode='r', schema=schema)
sql = SQL(engine, 'testtable', schema=schema)
json = JSON_Streaming(json_fn, mode='r+', schema=schema)
copy(csv, sql)
self.assertEqual(list(sql), data)
copy(sql, json)
with open(json_fn) as f:
assert 'Alice' in f.read()
def test_csv_sql_json(self):
data = [('Alice', 100), ('Bob', 200)]
text = '\n'.join(','.join(map(str, row)) for row in data)
schema = '{name: string, amount: int}'
with filetext(text) as csv_fn:
with filetext('') as json_fn:
with tmpfile('db') as sqldb:
csv = CSV(csv_fn, mode='r', schema=schema)
sql = SQL('sqlite:///' + sqldb, 'testtable', schema=schema)
json = JSON_Streaming(json_fn, mode='r+', schema=schema)
into(sql, csv)
self.assertEqual(into(list, sql), data)
into(json, sql)
with open(json_fn) as f:
assert 'Alice' in f.read()
def test_extension(self):
dd = SQL(self.engine,
'testtable2',
schema='{name: string, amount: int32}',
primary_key='name')
data_list = [('Alice', 100), ('Bob', 50)]
data_dict = [{
'name': name,
'amount': amount
} for name, amount in data_list]
dd.extend(data_dict)
with self.engine.connect() as conn:
results = conn.execute('select * from testtable2')
self.assertEquals(list(results), data_list)
assert list(iter(dd)) == data_list or list(iter(dd)) == data_dict
assert dd.as_py() == data_list or dd.as_py() == data_dict
def test_setup_with_uri(self):
dd = SQL('sqlite:///:memory:',
'accounts',
schema='{name: string, amount: int}')