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 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)
)
