def test_prepare_to_export_wrong_obj_type(self):
"""`prepare_to_export` raises exception if obj isn't `*Table`"""
expected_message = "Table type not recognized"
with self.assertRaises(ValueError) as exception_context:
next(plugins_utils.prepare_to_export(1))
self.assertEqual(exception_context.exception.args[0], expected_message)
with self.assertRaises(ValueError) as exception_context:
next(plugins_utils.prepare_to_export(42.0))
self.assertEqual(exception_context.exception.args[0], expected_message)
with self.assertRaises(ValueError) as exception_context:
next(plugins_utils.prepare_to_export([list("abc"), [1, 2, 3]]))
self.assertEqual(exception_context.exception.args[0], expected_message)
def export_to_xls(table, filename_or_fobj=None, sheet_name='Sheet1', *args,
**kwargs):
work_book = xlwt.Workbook()
sheet = work_book.add_sheet(sheet_name)
prepared_table = prepare_to_export(table, *args, **kwargs)
field_names = prepared_table.next()
for column_index, field_name in enumerate(field_names):
sheet.write(0, column_index, field_name)
for row_index, row in enumerate(prepared_table, start=1):
for column_index, (field_name, value) in \
enumerate(zip(field_names, row)):
field_type = table.fields[field_name]
data = {}
if field_type in FORMATTING_STYLES:
data['style'] = FORMATTING_STYLES[field_type]
sheet.write(row_index, column_index, value, **data)
if filename_or_fobj is not None:
_, fobj = get_filename_and_fobj(filename_or_fobj, mode='wb')
work_book.save(fobj)
fobj.flush()
return fobj
else:
fobj = BytesIO()
work_book.save(fobj)
fobj.seek(0)
result = fobj.read()
fobj.close()
return result
def export_to_json(table,
filename_or_fobj=None,
encoding='utf-8',
indent=None,
*args,
**kwargs):
'''Export a `rows.Table` to a JSON file or file-like object
If a file-like object is provided it MUST be open in binary mode (like in
`open('myfile.json', mode='wb')`).
'''
# TODO: will work only if table.fields is OrderedDict
fields = table.fields
prepared_table = prepare_to_export(table, *args, **kwargs)
field_names = next(prepared_table)
data = [{
field_name: _convert(value, fields[field_name], *args, **kwargs)
for field_name, value in zip(field_names, row)
} for row in prepared_table]
result = json.dumps(data, indent=indent)
if type(result) is six.text_type: # Python 3
result = result.encode(encoding)
if indent is not None:
# clean up empty spaces at the end of lines
result = b'\n'.join(line.rstrip() for line in result.splitlines())
return export_data(filename_or_fobj, result, mode='wb')
def export_to_xls(table, filename_or_fobj=None, sheet_name='Sheet1', *args,
**kwargs):
work_book = xlwt.Workbook()
sheet = work_book.add_sheet(sheet_name)
prepared_table = prepare_to_export(table, *args, **kwargs)
field_names = next(prepared_table)
for column_index, field_name in enumerate(field_names):
sheet.write(0, column_index, field_name)
_convert_row = _python_to_xls([table.fields.get(field)
for field in field_names])
for row_index, row in enumerate(prepared_table, start=1):
for column_index, (value, data) in enumerate(_convert_row(row)):
sheet.write(row_index, column_index, value, **data)
if filename_or_fobj is not None:
_, fobj = get_filename_and_fobj(filename_or_fobj, mode='wb')
work_book.save(fobj)
fobj.flush()
return fobj
else:
fobj = BytesIO()
work_book.save(fobj)
fobj.seek(0)
result = fobj.read()
fobj.close()
return result
def export_to_json(table, filename_or_fobj=None, encoding='utf-8', indent=None,
*args, **kwargs):
'''Export a `rows.Table` to a JSON file or file-like object
If a file-like object is provided it MUST be open in binary mode (like in
`open('myfile.json', mode='wb')`).
'''
# TODO: will work only if table.fields is OrderedDict
fields = table.fields
prepared_table = prepare_to_export(table, *args, **kwargs)
field_names = next(prepared_table)
data = [{field_name: _convert(value, fields[field_name], *args, **kwargs)
for field_name, value in zip(field_names, row)}
for row in prepared_table]
result = json.dumps(data, indent=indent)
if type(result) is six.text_type: # Python 3
result = result.encode(encoding)
if indent is not None:
# clean up empty spaces at the end of lines
result = b'\n'.join(line.rstrip() for line in result.splitlines())
return export_data(filename_or_fobj, result, mode='wb')
def export_to_yaml(table,
filename_or_fobj=None,
encoding='utf-8',
indent=None,
*args,
**kwargs):
'''Export a `rows.Table` to a YAML file or file-like object
'''
all_fields = table.fields
prepared_table = prepare_to_export(table, *args, **kwargs)
field_names = next(prepared_table)
data = [{
field_name: _convert(value, all_fields[field_name], *args, **kwargs)
for field_name, value in zip(field_names, row)
} for row in prepared_table]
result = yaml.dump(data, indent=indent)
if type(result) is six.text_type:
result = result.encode(encoding)
if indent is not None:
# clean up empty spaces at the end of lines
result = b'\n'.join(line.rstrip() for line in result.splitlines())
return export_data(filename_or_fobj, result, mode='wb')
def export_to_xlsx(table, filename_or_fobj=None, sheet_name="Sheet1", *args, **kwargs):
"""Export the rows.Table to XLSX file and return the saved file."""
workbook = Workbook()
sheet = workbook.active
sheet.title = sheet_name
prepared_table = prepare_to_export(table, *args, **kwargs)
# Write header
field_names = next(prepared_table)
for col_index, field_name in enumerate(field_names):
cell = sheet.cell(row=1, column=col_index + 1)
cell.value = field_name
# Write sheet rows
_convert_row = _python_to_cell(list(map(table.fields.get, field_names)))
for row_index, row in enumerate(prepared_table, start=1):
for col_index, (value, number_format) in enumerate(_convert_row(row)):
cell = sheet.cell(row=row_index + 1, column=col_index + 1)
cell.value = value
if number_format is not None:
cell.number_format = number_format
if filename_or_fobj is not None:
_, fobj = get_filename_and_fobj(filename_or_fobj, mode="wb")
workbook.save(fobj)
fobj.flush()
return fobj
else:
fobj = BytesIO()
workbook.save(fobj)
fobj.seek(0)
result = fobj.read()
fobj.close()
return result
def export_to_xlsx(table, filename_or_fobj=None, sheet_name='Sheet1', *args,
**kwargs):
workbook = Workbook()
sheet = workbook.active
sheet.title = sheet_name
field_names = list(enumerate(table.fields))
prepared_table = prepare_to_export(table, *args, **kwargs)
# Write header
header = prepared_table.next()
for col_index, field_name in enumerate(header):
_write_cell(sheet, 0, col_index, field_name, fields.TextField)
# Write sheet rows
table_fields = table.fields
for row_index, row in enumerate(prepared_table, start=1):
for col_index, field_name in field_names:
_write_cell(sheet, row_index, col_index,
value=row[col_index],
field_type=table_fields[field_name])
if filename_or_fobj is not None:
_, fobj = get_filename_and_fobj(filename_or_fobj, mode='wb')
workbook.save(fobj)
fobj.flush()
return fobj
else:
fobj = BytesIO()
workbook.save(fobj)
fobj.seek(0)
result = fobj.read()
fobj.close()
return result
def export_to_xls(table,
filename_or_fobj=None,
sheet_name='Sheet1',
*args,
**kwargs):
"""Export the rows.Table to XLS file and return the saved file."""
work_book = xlwt.Workbook()
sheet = work_book.add_sheet(sheet_name)
prepared_table = prepare_to_export(table, *args, **kwargs)
field_names = next(prepared_table)
for column_index, field_name in enumerate(field_names):
sheet.write(0, column_index, field_name)
_convert_row = _python_to_xls(
[table.fields.get(field) for field in field_names])
for row_index, row in enumerate(prepared_table, start=1):
for column_index, (value, data) in enumerate(_convert_row(row)):
sheet.write(row_index, column_index, value, **data)
if filename_or_fobj is not None:
_, fobj = get_filename_and_fobj(filename_or_fobj, mode='wb')
work_book.save(fobj)
fobj.flush()
return fobj
else:
fobj = BytesIO()
work_book.save(fobj)
fobj.seek(0)
result = fobj.read()
fobj.close()
return result
def export_to_sqlite(
table,
filename_or_connection,
table_name=None,
table_name_format="table{index}",
batch_size=100,
callback=None,
*args,
**kwargs
):
# TODO: should add transaction support?
prepared_table = prepare_to_export(table, *args, **kwargs)
connection = _get_connection(filename_or_connection)
cursor = connection.cursor()
if table_name is None:
table_names = [item[0] for item in cursor.execute(SQL_TABLE_NAMES)]
table_name = make_unique_name(
table_name_format.format(index=1),
existing_names=table_names,
name_format=table_name_format,
start=1,
)
elif not _valid_table_name(table_name):
raise ValueError("Invalid table name: {}".format(table_name))
field_names = next(prepared_table)
field_types = list(map(table.fields.get, field_names))
columns = [
"{} {}".format(field_name, SQLITE_TYPES.get(field_type, DEFAULT_TYPE))
for field_name, field_type in zip(field_names, field_types)
]
cursor.execute(
SQL_CREATE_TABLE.format(table_name=table_name, field_types=", ".join(columns))
)
insert_sql = SQL_INSERT.format(
table_name=table_name,
field_names=", ".join(field_names),
placeholders=", ".join("?" for _ in field_names),
)
_convert_row = _python_to_sqlite(field_types)
if callback is None:
for batch in ipartition(prepared_table, batch_size):
cursor.executemany(insert_sql, map(_convert_row, batch))
else:
total_written = 0
for batch in ipartition(prepared_table, batch_size):
cursor.executemany(insert_sql, map(_convert_row, batch))
written = len(batch)
total_written += written
callback(written, total_written)
connection.commit()
return connection
def export_to_sqlite(table,
filename_or_connection,
table_name=None,
table_name_format="table{index}",
batch_size=100,
callback=None,
*args,
**kwargs):
# TODO: should add transaction support?
prepared_table = prepare_to_export(table, *args, **kwargs)
connection = _get_connection(filename_or_connection)
cursor = connection.cursor()
if table_name is None:
table_names = [item[0] for item in cursor.execute(SQL_TABLE_NAMES)]
table_name = make_unique_name(
table_name_format.format(index=1),
existing_names=table_names,
name_format=table_name_format,
start=1,
)
elif not _valid_table_name(table_name):
raise ValueError("Invalid table name: {}".format(table_name))
field_names = next(prepared_table)
field_types = list(map(table.fields.get, field_names))
columns = [
"{} {}".format(field_name, SQLITE_TYPES.get(field_type, DEFAULT_TYPE))
for field_name, field_type in zip(field_names, field_types)
]
cursor.execute(
SQL_CREATE_TABLE.format(table_name=table_name,
field_types=", ".join(columns)))
insert_sql = SQL_INSERT.format(
table_name=table_name,
field_names=", ".join(field_names),
placeholders=", ".join("?" for _ in field_names),
)
_convert_row = _python_to_sqlite(field_types)
if callback is None:
for batch in ipartition(prepared_table, batch_size):
cursor.executemany(insert_sql, map(_convert_row, batch))
else:
total_written = 0
for batch in ipartition(prepared_table, batch_size):
cursor.executemany(insert_sql, map(_convert_row, batch))
written = len(batch)
total_written += written
callback(written, total_written)
connection.commit()
return connection
def test_prepare_to_export_all_fields(self):
result = plugins_utils.prepare_to_export(utils.table, export_fields=None)
self.assertEqual(list(utils.table.fields.keys()), next(result))
for row in utils.table._rows:
self.assertEqual(row, next(result))
with self.assertRaises(StopIteration):
next(result)
def test_prepare_to_export_some_fields_dont_exist(self):
field_names = list(utils.table.fields.keys())
error_fields = ['does_not_exist', 'java']
export_fields = field_names + error_fields
result = plugins_utils.prepare_to_export(utils.table,
export_fields=export_fields)
with self.assertRaises(ValueError) as exception_context:
next(result)
self.assertIn(exception_context.exception.args[0],
possible_field_names_errors(error_fields))
def test_prepare_to_export_some_fields_dont_exist(self):
field_names = utils.table.fields.keys()
error_fields = ['does_not_exist', 'java']
export_fields = field_names + error_fields
result = plugins_utils.prepare_to_export(utils.table,
export_fields=export_fields)
with self.assertRaises(ValueError) as exception_context:
result.next()
self.assertIn(exception_context.exception.message,
possible_field_names_errors(error_fields))
def test_prepare_to_export_with_FlexibleTable(self):
flexible = rows.FlexibleTable()
for row in utils.table:
flexible.append(row._asdict())
field_names = list(flexible.fields.keys())
prepared = plugins_utils.prepare_to_export(flexible)
self.assertEqual(next(prepared), field_names)
for row, expected_row in zip(prepared, flexible._rows):
values = [expected_row[field_name] for field_name in field_names]
self.assertEqual(values, row)
def export_to_postgresql(
table,
connection_or_uri,
table_name=None,
table_name_format="table{index}",
batch_size=100,
close_connection=False,
*args,
**kwargs
):
# TODO: should add transaction support?
if table_name is not None and not _valid_table_name(table_name):
raise ValueError("Invalid table name: {}".format(table_name))
prepared_table = prepare_to_export(table, *args, **kwargs)
connection = _get_connection(connection_or_uri)
cursor = connection.cursor()
if table_name is None:
cursor.execute(SQL_TABLE_NAMES)
table_names = [item[0] for item in cursor.fetchall()]
table_name = make_unique_name(
table_name_format.format(index=1),
existing_names=table_names,
name_format=table_name_format,
start=1,
)
field_names = next(prepared_table)
field_types = list(map(table.fields.get, field_names))
columns = [
"{} {}".format(field_name, SQL_TYPES.get(field_type, DEFAULT_TYPE))
for field_name, field_type in zip(field_names, field_types)
]
cursor.execute(
SQL_CREATE_TABLE.format(table_name=table_name, field_types=", ".join(columns))
)
insert_sql = SQL_INSERT.format(
table_name=table_name,
field_names=", ".join(field_names),
placeholders=", ".join("%s" for _ in field_names),
)
_convert_row = _python_to_postgresql(field_types)
for batch in ipartition(prepared_table, batch_size):
cursor.executemany(insert_sql, map(_convert_row, batch))
connection.commit()
cursor.close()
if close_connection:
connection.close()
return connection, table_name
def test_prepare_to_export_some_fields_dont_exist(self):
field_names = list(utils.table.fields.keys())
error_fields = ["does_not_exist", "java"]
export_fields = field_names + error_fields
result = plugins_utils.prepare_to_export(
utils.table, export_fields=export_fields
)
with self.assertRaises(ValueError) as exception_context:
next(result)
self.assertIn(
exception_context.exception.args[0],
possible_field_names_errors(error_fields),
)
def export_to_postgresql(table,
connection_or_uri,
table_name=None,
table_name_format="table{index}",
batch_size=100,
close_connection=False,
*args,
**kwargs):
# TODO: should add transaction support?
if table_name is not None and not _valid_table_name(table_name):
raise ValueError("Invalid table name: {}".format(table_name))
prepared_table = prepare_to_export(table, *args, **kwargs)
connection = _get_connection(connection_or_uri)
cursor = connection.cursor()
if table_name is None:
cursor.execute(SQL_TABLE_NAMES)
table_names = [item[0] for item in cursor.fetchall()]
table_name = make_unique_name(
table_name_format.format(index=1),
existing_names=table_names,
name_format=table_name_format,
start=1,
)
field_names = next(prepared_table)
field_types = list(map(table.fields.get, field_names))
columns = [
"{} {}".format(field_name, SQL_TYPES.get(field_type, DEFAULT_TYPE))
for field_name, field_type in zip(field_names, field_types)
]
cursor.execute(
SQL_CREATE_TABLE.format(table_name=table_name,
field_types=", ".join(columns)))
insert_sql = SQL_INSERT.format(
table_name=table_name,
field_names=", ".join(field_names),
placeholders=", ".join("%s" for _ in field_names),
)
_convert_row = _python_to_postgresql(field_types)
for batch in ipartition(prepared_table, batch_size):
cursor.executemany(insert_sql, map(_convert_row, batch))
connection.commit()
cursor.close()
if close_connection:
connection.close()
return connection, table_name
def test_prepare_to_export_some_fields(self):
field_names = list(utils.table.fields.keys())
number_of_fields = random.randint(2, len(field_names) - 1)
some_fields = [field_names[index] for index in range(number_of_fields)]
random.shuffle(some_fields)
result = plugins_utils.prepare_to_export(utils.table, export_fields=some_fields)
self.assertEqual(some_fields, next(result))
for row in utils.table:
expected_row = [getattr(row, field_name) for field_name in some_fields]
self.assertEqual(expected_row, next(result))
with self.assertRaises(StopIteration):
next(result)
def test_prepare_to_export_with_FlexibleTable_and_export_fields(self):
flexible = rows.FlexibleTable()
for row in utils.table:
flexible.append(row._asdict())
field_names = flexible.fields.keys()
field_types = flexible.fields.values()
export_fields = field_names[:len(field_names) // 2]
prepared = plugins_utils.prepare_to_export(flexible,
export_fields=export_fields)
self.assertEqual(prepared.next(), export_fields)
for row, expected_row in zip(prepared, flexible._rows):
values = [expected_row[field_name] for field_name in export_fields]
self.assertEqual(values, row)
def export_to_json(table, filename_or_fobj=None, encoding='utf-8', indent=None,
*args, **kwargs):
# TODO: will work only if table.fields is OrderedDict
fields = table.fields
prepared_table = prepare_to_export(table, *args, **kwargs)
field_names = prepared_table.next()
data = [{field_name: _convert(value, fields[field_name], *args, **kwargs)
for field_name, value in zip(field_names, row)}
for row in prepared_table]
result = json.dumps(data, indent=indent)
if indent is not None:
result = '\n'.join(line.rstrip() for line in result.splitlines())
return export_data(filename_or_fobj, result)
def test_prepare_to_export_with_FlexibleTable_and_export_fields(self):
flexible = rows.FlexibleTable()
for row in utils.table:
# convertion to text_type is needed on Python 2 since namedtuples'
# keys are bytes, not unicode
flexible.append({six.text_type(key): value
for key, value in row._asdict().items()})
field_names = list(flexible.fields.keys())
export_fields = field_names[:len(field_names) // 2]
print([(x, type(x)) for x in export_fields])
prepared = plugins_utils.prepare_to_export(flexible,
export_fields=export_fields)
self.assertEqual(next(prepared), export_fields)
for row, expected_row in zip(prepared, flexible._rows):
values = [expected_row[field_name] for field_name in export_fields]
self.assertEqual(values, row)
def test_prepare_to_export_with_FlexibleTable_and_export_fields(self):
flexible = rows.FlexibleTable()
for row in utils.table:
# convertion to text_type is needed on Python 2 since namedtuples'
# keys are bytes, not unicode
flexible.append(
{six.text_type(key): value for key, value in row._asdict().items()}
)
field_names = list(flexible.fields.keys())
export_fields = field_names[: len(field_names) // 2]
print([(x, type(x)) for x in export_fields])
prepared = plugins_utils.prepare_to_export(
flexible, export_fields=export_fields
)
self.assertEqual(next(prepared), export_fields)
for row, expected_row in zip(prepared, flexible._rows):
values = [expected_row[field_name] for field_name in export_fields]
self.assertEqual(values, row)
def export_to_json(table,
filename_or_fobj=None,
encoding='utf-8',
indent=None,
*args,
**kwargs):
# TODO: will work only if table.fields is OrderedDict
fields = table.fields
prepared_table = prepare_to_export(table, *args, **kwargs)
field_names = prepared_table.next()
data = [{
field_name: _convert(value, fields[field_name], *args, **kwargs)
for field_name, value in zip(field_names, row)
} for row in prepared_table]
result = json.dumps(data, indent=indent)
if indent is not None:
result = '\n'.join(line.rstrip() for line in result.splitlines())
return export_data(filename_or_fobj, result)
def test_prepare_to_export_wrong_obj_type(self):
'''`prepare_to_export` raises exception if obj isn't `*Table`'''
expected_message = 'Table type not recognized'
with self.assertRaises(ValueError) as exception_context:
plugins_utils.prepare_to_export(1).next()
self.assertEqual(exception_context.exception.message, expected_message)
with self.assertRaises(ValueError) as exception_context:
plugins_utils.prepare_to_export(42.0).next()
self.assertEqual(exception_context.exception.message, expected_message)
with self.assertRaises(ValueError) as exception_context:
plugins_utils.prepare_to_export([list('abc'), [1, 2, 3]]).next()
self.assertEqual(exception_context.exception.message, expected_message)
def export_to_xlsx(table,
filename_or_fobj=None,
sheet_name='Sheet1',
*args,
**kwargs):
"""Export the rows.Table to XLSX file and return the saved file."""
workbook = Workbook()
sheet = workbook.active
sheet.title = sheet_name
prepared_table = prepare_to_export(table, *args, **kwargs)
# Write header
field_names = next(prepared_table)
for col_index, field_name in enumerate(field_names):
cell = sheet.cell(row=1, column=col_index + 1)
cell.value = field_name
# Write sheet rows
_convert_row = _python_to_cell(list(map(table.fields.get, field_names)))
for row_index, row in enumerate(prepared_table, start=1):
for col_index, (value, number_format) in enumerate(_convert_row(row)):
cell = sheet.cell(row=row_index + 1, column=col_index + 1)
cell.value = value
if number_format is not None:
cell.number_format = number_format
if filename_or_fobj is not None:
_, fobj = get_filename_and_fobj(filename_or_fobj, mode='wb')
workbook.save(fobj)
fobj.flush()
return fobj
else:
fobj = BytesIO()
workbook.save(fobj)
fobj.seek(0)
result = fobj.read()
fobj.close()
return result
def export_to_xlsx(table,
filename_or_fobj=None,
sheet_name='Sheet1',
*args,
**kwargs):
workbook = Workbook()
sheet = workbook.active
sheet.title = sheet_name
field_names = list(enumerate(table.fields))
prepared_table = prepare_to_export(table, *args, **kwargs)
# Write header
header = prepared_table.next()
for col_index, field_name in enumerate(header):
_write_cell(sheet, 0, col_index, field_name, fields.TextField)
# Write sheet rows
table_fields = table.fields
for row_index, row in enumerate(prepared_table, start=1):
for col_index, field_name in field_names:
_write_cell(sheet,
row_index,
col_index,
value=row[col_index],
field_type=table_fields[field_name])
if filename_or_fobj is not None:
_, fobj = get_filename_and_fobj(filename_or_fobj, mode='wb')
workbook.save(fobj)
fobj.flush()
return fobj
else:
fobj = BytesIO()
workbook.save(fobj)
fobj.seek(0)
result = fobj.read()
fobj.close()
return result
def export_to_xls(table,
filename_or_fobj=None,
sheet_name='Sheet1',
*args,
**kwargs):
work_book = xlwt.Workbook()
sheet = work_book.add_sheet(sheet_name)
prepared_table = prepare_to_export(table, *args, **kwargs)
field_names = prepared_table.next()
for column_index, field_name in enumerate(field_names):
sheet.write(0, column_index, field_name)
for row_index, row in enumerate(prepared_table, start=1):
for column_index, (field_name, value) in \
enumerate(zip(field_names, row)):
field_type = table.fields[field_name]
data = {}
if field_type in FORMATTING_STYLES:
data['style'] = FORMATTING_STYLES[field_type]
sheet.write(row_index, column_index, value, **data)
if filename_or_fobj is not None:
_, fobj = get_filename_and_fobj(filename_or_fobj, mode='wb')
work_book.save(fobj)
fobj.flush()
return fobj
else:
fobj = BytesIO()
work_book.save(fobj)
fobj.seek(0)
result = fobj.read()
fobj.close()
return result
