def make_table_definition(df):
table_definition = TableDefinition()
for column in df.columns:
tableau_column = column.title().replace('_', ' ')
tableau_dtype = mapper[df[column].dtype]['tableau_datatype']
table_definition.addColumn(tableau_column, tableau_dtype)
return table_definition
def to_tde(df, extractname, data_type, index = False, new_extract = False, append = True):
'''
change pandas dataframe to tableau data extract
'''
table_definition_map = {
'bool' : Type.BOOLEAN,
'integer': Type.INTEGER,
'double': Type.DOUBLE,
'date': Type.DATE,
'datetime': Type.DATETIME,
'unicode': Type.UNICODE_STRING,
'char': Type.CHAR_STRING
}
value_set_map = {
Type.BOOLEAN: lambda row, col_num, value: row.setBoolean( col_num, bool(value) ),
Type.INTEGER: lambda row, col_num, value: row.setInteger( col_num, int(value) ),
Type.DOUBLE: lambda row, col_num, value: row.setDouble( col_num, float(value) ),
Type.UNICODE_STRING: lambda row, col_num, value: row.setString( col_num, unicode(value) ),
Type.CHAR_STRING: lambda row, col_num, value: row.setCharString( col_num, value ),
Type.DATE: lambda row, col_num, value: row.setDate(col_num, value.year, value.month, value.day),
Type.DATETIME: lambda row, col_num, value: row.setDateTime( col_num, value.year, value.month, value.day,
value.hour, value.minute, value.second,
value.microsecond/100 )
}
if index:
df_new = df.reset_index()
else:
df_new = df
if extractname[-4:] != '.tde':
extractname += '.tde'
if new_extract & (extractname in os.listdir('.')):
os.remove(extractname)
extract_to_save = Extract(extractname)
if extract_to_save.hasTable("Extract") & append:
table_to_save = extract_to_save.openTable("Extract")
table_definition = table_to_save.getTableDefinition()
else:
table_definition = TableDefinition()
for colname in df_new.columns:
type_code = table_definition_map[data_type.get(colname, 'unicode').lower()]
table_definition.addColumn(colname, type_code)
table_to_save = extract_to_save.addTable("Extract", table_definition) # currnetly table cannot be Extract!!!
for df_row_tuple in df_new.iterrows():
new_row = Row(table_definition)
# new_rwo is a tuple of len 2, index and the row as series
df_row = df_row_tuple[1]
for col_num, (col_val, null_col) in enumerate(zip(df_row, df_row.isnull())):
if null_col:
new_row.setNull(col_num)
else:
value_set_map[table_definition.getColumnType(col_num)](new_row, col_num, col_val)
table_to_save.insert(new_row)
extract_to_save.close()
ExtractAPI.cleanup()
def setUp(self):
self.table_def = TableDefinition()
self.table_def.addColumn("int_1", Type.INTEGER)
self.table_def.addColumn("int_2", Type.INTEGER)
self.table_def.addColumn("date", Type.DATETIME)
self.col_metadata = {
1: Type.INTEGER,
2: Type.INTEGER,
3: Type.DATETIME,
}
def _build_skeleton(self):
"""
Gets the columns from the table or view and then auto builds the Tableau
table definition - the skeleton of the extract we want to create
:return: table_def: Tableau table definition
"""
table_def = TableDefinition()
db_table_columns = self._db_consumer.get_table_definition()
for field_name, position, field_type in db_table_columns:
table_def.addColumn(field_name, field_type)
self.columns[position] = field_type
return table_def
def get_table_definition(tde_columns):
td = TableDefinition()
for c in tde_columns:
td.addColumn(c.column_name, c.tde_type)
return td
class TestExtractFiller(TestCase):
def setUp(self):
self.table_def = TableDefinition()
self.table_def.addColumn("int_1", Type.INTEGER)
self.table_def.addColumn("int_2", Type.INTEGER)
self.table_def.addColumn("date", Type.DATETIME)
self.col_metadata = {
1: Type.INTEGER,
2: Type.INTEGER,
3: Type.DATETIME,
}
def test_replace_null_functionality(self):
rn = ExtractFiller._replace_null
# boolean
self.assertEqual(rn(Type.BOOLEAN, None), False)
self.assertEqual(rn(Type.BOOLEAN, True), True)
# integer
self.assertEqual(rn(Type.INTEGER, None), 0)
self.assertEqual(rn(Type.INTEGER, 12), 12)
# float
self.assertEqual(rn(Type.DOUBLE, None), 0.0)
self.assertEqual(rn(Type.DOUBLE, 12.12), 12.12)
# string
self.assertEqual(rn(Type.CHAR_STRING, None), '')
self.assertEqual(rn(Type.CHAR_STRING, 'somestring'), 'somestring')
# unicode string
self.assertEqual(rn(Type.UNICODE_STRING, None), u'')
self.assertEqual(rn(Type.UNICODE_STRING, u'somestring'), u'somestring')
# date
self.assertEqual(rn(Type.DATE, None), get_fake_date())
self.assertEqual(rn(Type.DATE, date(1990, 1, 1)), date(1990, 1, 1))
# datetime
self.assertEqual(rn(Type.DATETIME, None), get_fake_datetime())
self.assertEqual(rn(Type.DATETIME, datetime(1990, 1, 1, 0, 0, 0, 0)),
datetime(1990, 1, 1, 0, 0, 0, 0))
def test_replace_null_with_test_data(self):
# row of data without NULLs
test_data = [1, 2, datetime.today()]
rn = ExtractFiller._replace_null
for pos, db_datatype in self.col_metadata.iteritems():
data = test_data[pos - 1]
self.assertEqual(rn(db_datatype, data), data)
# row of data with NULL
test_data = [None, None, None]
ret_val = [0, 0, get_fake_datetime()]
for pos, db_datatype in self.col_metadata.iteritems():
data = test_data[pos - 1]
ret = ret_val[pos - 1]
# should replace all NULLs
self.assertNotEqual(rn(db_datatype, data), None)
self.assertEqual(rn(db_datatype, data), ret)
@mock.patch('trext.db.fill.Row')
@mock.patch('tableausdk.Extract.Extract')
def test_insert_data(self, mock_extract, mock_row):
test_data = [1, 2, datetime.today()]
# get a mock extract and add table
mock_extract = mock_extract.return_value
mock_extract.addTable('test_extract', self.table_def)
# create instance of class to test
self.ef = ExtractFiller(mock_extract, self.table_def,
self.col_metadata)
self.ef.insert_data_to_extract(test_data)
# test call argument
mock_row.assert_called_with(self.table_def)
row_to_insert = mock_row.return_value
# test insert methods called for test data
self.assertEqual(row_to_insert.setInteger.call_count, 2)
self.assertEqual(row_to_insert.setDateTime.call_count, 1)
