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)