def get_columns_full(self, database, table): try: columns, description = query_and_fetch( self.db, "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'" % (database, table)) return [{ "comment": col[2] and col[2].strip(), "type": col[1], "name": col[0] and col[0].strip() } for col in columns] except Exception as e: if 'SQLServerException' in str(e) and 'COLUMN_COMMENT' in str(e): LOG.warn( 'Seems like SQLServer is use, COLUMN_COMMENT field does not exist in INFORMATION_SCHEMA.COLUMNS' ) columns, description = query_and_fetch( self.db, "SELECT COLUMN_NAME, DATA_TYPE, NULL as COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS " "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'" % (database, table)) return [{ "comment": col[2] and col[2].strip(), "type": col[1], "name": col[0] and col[0].strip() } for col in columns]
def get_tables_full(self, database, table_names=[]): try: tables, description = query_and_fetch( self.db, "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='%s'" % database) return [{ "comment": table[1] and table[1].strip(), "type": "Table", "name": table[0] and table[0].strip() } for table in tables] except Exception as e: if 'SQLServerException' in str(e) and 'TABLE_COMMENT' in str(e): LOG.warn( 'Seems like SQLServer is use, TABLE_COMMENT field does not exist in INFORMATION_SCHEMA.TABLES' ) tables, description = query_and_fetch( self.db, "SELECT TABLE_NAME, NULL as TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='%s'" % database) return [{ "comment": table[1] and table[1].strip(), "type": "Table", "name": table[0] and table[0].strip() } for table in tables]
def execute(self, notebook, snippet): if self.db is None: raise AuthenticationRequired() data, description = query_and_fetch(self.db, snippet['statement'], 1000) has_result_set = data is not None return { 'sync': True, 'has_result_set': has_result_set, 'result': { 'has_more': False, 'data': data if has_result_set else [], 'meta': [{ 'name': col[0], 'type': col[1], 'comment': '' } for col in description] if has_result_set else [], 'type': 'table' } }
def get_tables_full(self, database, table_names=[]): cache_key = str(self.db.db_url) + str(database) if ( cache_key not in self.cached_data or time.time() - self.cached_data[cache_key]["time"] > self.freeze_time ): tables, description = query_and_fetch( self.db, "SELECT table_name, '' FROM v_catalog.tables WHERE table_schema='%s' order by 1" % database, ) list_of_tables = [ { "comment": table[1] and table[1].strip(), "type": "Table", "name": table[0] and table[0].strip(), } for table in tables ] VerticaAssist.cached_data[cache_key] = { "time": time.time(), "result": list_of_tables, } VerticaAssist.cache_use_stat["query"] += 1 else: VerticaAssist.cache_use_stat["cache"] += 1 return self.cached_data[cache_key]["result"]
def get_columns_full(self, database, table): cache_key = str(self.db.db_url) + str(database) + str(table) if ( cache_key not in self.cached_data or time.time() - self.cached_data[cache_key]["time"] > self.freeze_time ): columns, description = query_and_fetch( self.db, "select column_name, data_type, '' from v_catalog.columns where table_schema='%s' and table_name='%s' order by 1" % (database, table), ) list_of_columns = [ { "comment": col[2] and col[2].strip(), "type": col[1], "name": col[0] and col[0].strip(), } for col in columns ] VerticaAssist.cached_data[cache_key] = { "time": time.time(), "result": list_of_columns, } VerticaAssist.cache_use_stat["query"] += 1 else: VerticaAssist.cache_use_stat["cache"] += 1 return VerticaAssist.cached_data[cache_key]["result"]
def get_databases(self): cache_key = str(self.db.db_url) if ( cache_key not in self.cached_data or time.time() - self.cached_data[cache_key]["time"] > self.freeze_time ): dbs, description = query_and_fetch( self.db, "select schema_name FROM v_catalog.schemata where is_system_schema=0 and schema_name not in ('v_func', 'v_txtindex') order by 1", ) list_of_db = [db[0] and db[0].strip() for db in dbs] VerticaAssist.cached_data[cache_key] = { "time": time.time(), "result": list_of_db, } VerticaAssist.cache_use_stat["query"] += 1 else: VerticaAssist.cache_use_stat["cache"] += 1 if self.cache_use_stat["cache"] % 5 == 0: LOG.info( "Autocomplete data, vertica: " + str(self.cache_use_stat["query"]) + " cache: " + str(self.cache_use_stat["cache"]) + ", cache is used in " + "%.2f" % ( old_div(100 * float(self.cache_use_stat["cache"]), (self.cache_use_stat["query"] + self.cache_use_stat["cache"])) ) + "% cases" ) return self.cached_data[cache_key]["result"]
def get_sample_data(self, database, table, column=None): column = column or '*' # data, description = query_and_fetch(self.db, 'SELECT %s FROM %s.%s limit 100' % (column, database, table)) # response['rows'] = data # response['columns'] = [] try: return query_and_fetch( self.db, 'SELECT %s FROM %s.%s limit 100' % (column, database, table)) except Exception as e: if 'SQLServerException' in str(e) and '100' in str(e): LOG.warn( 'Seems like SQLServer is use, LIMIT condition does not exist' ) return query_and_fetch( self.db, 'SELECT TOP 100 %s FROM %s.%s' % (column, database, table))
def get_sample_data(self, database, table, column=None): column = column or '*' #data, description = query_and_fetch(self.db, 'SELECT %s FROM %s.%s limit 100' % (column, database, table)) #response['rows'] = data #response['columns'] = [] return query_and_fetch( self.db, 'SELECT %s FROM %s.%s limit 100' % (column, database, table))
def get_tables_full(self, database, table_names=[]): tables, description = query_and_fetch( self.db, "SELECT name, '' FROM system.tables WHERE database='%s'" % database) return [{ "comment": table[1] and table[1].strip(), "type": "Table", "name": table[0] and table[0].strip() } for table in tables]
def get_columns(self, database, table): columns, description = query_and_fetch( self.db, "SELECT ColumnName, ColumnType, CommentString FROM DBC.Columns WHERE DatabaseName='%s' AND TableName='%s'" % (database, table)) return [[ col[0] and col[0].strip(), self._type_converter(col[1]), '', '', col[2], '' ] for col in columns]
def get_tables_full(self, database, table_names=[]): tables, description = query_and_fetch( self.db, "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='%s'" % database) return [{ "comment": table[1] and table[1].strip(), "type": "Table", "name": table[0] and table[0].strip() } for table in tables]
def get_columns_full(self, database, table): columns, description = query_and_fetch( self.db, "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'" % (database, table)) return [{ "comment": col[2] and col[2].strip(), "type": col[1], "name": col[0] and col[0].strip() } for col in columns]
def get_columns_full(self, database, table): columns, description = query_and_fetch( self.db, "SELECT ColumnName, ColumnType, CommentString FROM DBC.Columns WHERE DatabaseName='%s' AND TableName='%s' ORDER BY ColumnName" % (database, table)) return [{ "comment": col[1] and col[1].strip(), "type": self._type_converter(col[1]), "name": col[0] and col[0].strip() } for col in columns]
def get_tables_full(self, database, table_names=[]): tables, description = query_and_fetch( self.db, "SELECT TableName, CommentString FROM dbc.tables WHERE tablekind = 'T' and databasename='%s' ORDER BY TableName" % database) return [{ "comment": table[1] and table[1].strip(), "type": "Table", "name": table[0] and table[0].strip() } for table in tables]
def get_columns_full(self, database, table): columns, description = query_and_fetch( self.db, "SELECT name, type, '' FROM system.columns WHERE database='%s' AND table = '%s'" % (database, table)) return [{ "comment": col[2] and col[2].strip(), "type": col[1], "name": col[0] and col[0].strip() } for col in columns]
def get_tables(self, database, table_names=[]): tables, description = query_and_fetch( self.db, "SELECT * FROM dbc.tables WHERE tablekind = 'T' and databasename='%s'" % database) return [{ "comment": table[7] and table[7].strip(), "type": "Table", "name": table[1] and table[1].strip() } for table in tables]
def download(self, notebook, snippet, format, user_agent=None, max_rows=None, store_data_type_in_header=False): file_name = _get_snippet_name(notebook) data, description = query_and_fetch(self.db, snippet['statement']) db = FixedResult(data, description) return data_export.download(None, format, db, id=snippet['id'], file_name=file_name)
def execute(self, notebook, snippet): if self.db is None: raise AuthenticationRequired() data, description = query_and_fetch(self.db, snippet["statement"], 1000) has_result_set = data is not None return { "sync": True, "has_result_set": has_result_set, "result": { "has_more": False, "data": data if has_result_set else [], "meta": [{"name": col[0], "type": col[1], "comment": ""} for col in description] if has_result_set else [], "type": "table", }, }
def get_sample_data(self, database, table, column=None): column = column or '*' return query_and_fetch( self.db, 'SELECT %s FROM %s.%s' % (column, database, table))
def get_sample_data(self, database, table): return query_and_fetch(self.db, "SELECT * FROM %s.%s" % (database, table))
def get_databases(self): dbs, description = query_and_fetch(self.db, 'SHOW DATABASES') return [db[0] and db[0].strip() for db in dbs]
def get_sample_data(self, database, table, column=None): column = column or '*' return query_and_fetch(self.db, 'SELECT %s FROM %s.%s' % (column, database, table))
def get_databases(self): dbs, description = query_and_fetch(self.db, 'SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA') return [db[0] and db[0].strip() for db in dbs]
def get_databases(self): dbs, description = query_and_fetch( self.db, 'SELECT DatabaseName FROM DBC.Databases') return [db[0] and db[0].strip() for db in dbs]
def get_sample_data(self, database, table): return query_and_fetch(self.db, 'SELECT * FROM %s.%s' % (database, table))
def get_databases(self): databases, description = query_and_fetch(self.db, 'SHOW DATABASES') return databases
def get_tables(self, database, table_names=[]): tables, description = query_and_fetch(self.db, 'SHOW TABLES') return tables
def get_columns(self, database, table): columns, description = query_and_fetch(self.db, 'SHOW COLUMNS FROM %s.%s' % (database, table)) return columns
def get_sample_data(self, database, table, column=None): column = column or "*" return query_and_fetch( self.db, "SELECT %s FROM %s.%s limit 10" % (column, database, table) )