def test_copy_with_file(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
conn2 = vertica_python.connect(**conn_info)
cur2 = conn.cursor()
f = tempfile.TemporaryFile()
f.write("1,foo\n2,bar")
# move rw pointer to top of file
f.seek(0)
cur.copy(""" COPY vertica_python_unit_test (a, b) from stdin DELIMITER ',' """, f)
f.close()
# verify this cursor can see copy data
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
res = cur.fetchall()
assert 1 == len(res)
assert 1 == res[0][0]
assert 'foo' == res[0][1]
# verify other cursor can see copy data
cur2.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2")
res = cur2.fetchall()
assert 1 == len(res)
assert 2 == res[0][0]
assert 'bar' == res[0][1]
def test_copy_with_string(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
conn2 = vertica_python.connect(**conn_info)
cur2 = conn.cursor()
cur.copy(""" COPY vertica_python_unit_test (a, b) from stdin DELIMITER ',' """, "1,foo\n2,bar")
# no commit necessary for copy
# verify this cursor can see copy data
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
res = cur.fetchall()
assert 1 == len(res)
assert 1 == res[0][0]
assert 'foo' == res[0][1]
# verify other cursor can see copy data
cur2.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2")
res = cur2.fetchall()
assert 1 == len(res)
assert 2 == res[0][0]
assert 'bar' == res[0][1]
def test_copy_with_string(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
conn2 = vertica_python.connect(**conn_info)
cur2 = conn.cursor()
cur.copy(
""" COPY vertica_python_unit_test (a, b) from stdin DELIMITER ',' """,
"1,foo\n2,bar")
# no commit necessary for copy
# verify this cursor can see copy data
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
res = cur.fetchall()
assert 1 == len(res)
assert 1 == res[0][0]
assert 'foo' == res[0][1]
# verify other cursor can see copy data
cur2.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2")
res = cur2.fetchall()
assert 1 == len(res)
assert 2 == res[0][0]
assert 'bar' == res[0][1]
def test_copy_with_file(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
conn2 = vertica_python.connect(**conn_info)
cur2 = conn.cursor()
f = tempfile.TemporaryFile()
f.write(b"1,foo\n2,bar")
# move rw pointer to top of file
f.seek(0)
cur.copy(
""" COPY vertica_python_unit_test (a, b) from stdin DELIMITER ',' """,
f)
f.close()
# verify this cursor can see copy data
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
res = cur.fetchall()
assert 1 == len(res)
assert 1 == res[0][0]
assert 'foo' == res[0][1]
# verify other cursor can see copy data
cur2.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2")
res = cur2.fetchall()
assert 1 == len(res)
assert 2 == res[0][0]
assert 'bar' == res[0][1]
def fetch_from_vertica_to_df(self, data_set, query, block_size=100000):
data_set_query = self.get_meta_data(data_set, 'query')
if data_set_query != query:
connection = connect(self.connection_details)
cursor_remote = connection.cursor()
print 'Executing ', data_set, 'Query...'
print query
columns = get_column_names_from_sql_query(query)
self.create_table(data_set, columns)
cursor_remote.execute(query)
while True:
rows = cursor_remote.fetchmany(block_size)
rows = [tuple([str(ele) for ele in row]) for row in rows]
self.insert_rows_to_table(data_set, columns, rows)
if len(rows) < block_size:
break
self.db.commit()
cursor_remote.close()
if self.get_number_of_rows_in_table(data_set) == 0:
raise (ValueError('SQL result in empty fetch!!'))
else:
self.split_table_into_features(data_set)
self.set_meta_data(data_set, 'query', query)
self.set_meta_data(data_set, 'columns', ','.join(columns))
self.set_meta_data(data_set, 'built_features', '')
def getGameName(game_id,conn_info):
with vpy.connect(**conn_info) as conn:
cur = conn.cursor()
query = """select game_name from lookups.l_game where game_state='live' and game_id={}""".format(game_id)
cur.execute(query)
game_name = cur.fetchall()
return '_'.join(game_name[0][0].lower().split())
def fetch_from_vertica_to_df(self, data_set, query, block_size=100000):
data_set_query = self.get_meta_data(data_set, 'query')
if data_set_query != query:
connection = connect(self.connection_details)
cursor_remote = connection.cursor()
print 'Executing ', data_set, 'Query...'
print query
columns = get_column_names_from_sql_query(query)
self.create_table(data_set, columns)
cursor_remote.execute(query)
while True:
rows = cursor_remote.fetchmany(block_size)
rows = [tuple([str(ele) for ele in row]) for row in rows]
self.insert_rows_to_table(data_set, columns, rows)
if len(rows) < block_size:
break
self.db.commit()
cursor_remote.close()
if self.get_number_of_rows_in_table(data_set) == 0:
raise (ValueError('SQL result in empty fetch!!'))
else:
self.split_table_into_features(data_set)
self.set_meta_data(data_set, 'query', query)
self.set_meta_data(data_set, 'columns', ','.join(columns))
self.set_meta_data(data_set, 'built_features', '')
def create_new_conn(self,datasourceName,is_autocommit = True):
try:
#Load Data source by name
dsource = next((ds for ds in self.datasources if ds.name == datasourceName),None)
#load params incase of the data source type
newConn_param = self.get_direct_con_settings(datasourceName)
#Decide which class to instantuate
if dsource.databaseType.lower() == 'postgresql' and dsource.connectionType.lower() == 'direct':
newConn = psycopg2.connect(host=newConn_param['server'],port=newConn_param['port'],database=newConn_param['database'],user=newConn_param['uid'],password=newConn_param['pwd'])
newConn.autocommit(is_autocommit)
if dsource.databaseType.lower() == 'redshift' and dsource.connectionType.lower() == 'direct':
newConn = psycopg2.connect(host=newConn_param['server'],port=newConn_param['port'],database=newConn_param['database'],user=newConn_param['uid'],password=newConn_param['pwd'])
newConn.autocommit(is_autocommit)
if dsource.databaseType.lower() == 'vertica' and dsource.connectionType.lower() == 'direct':
newConn = vertica_python.connect(host=newConn_param['server'],port=newConn_param['port'],database=newConn_param['database'],user=newConn_param['uid'],password=newConn_param['pwd'])
newConn.autocommit(is_autocommit)
if dsource.databaseType.lower() == 'mssql' and dsource.connectionType.lower() == 'direct':
newConn = pymssql.connect(host=newConn_param['server'],port=newConn_param['port'],database=newConn_param['database'],user=newConn_param['uid'],password=newConn_param['pwd'])
newConn.autocommit(is_autocommit)
else:
newConn = pyodbc.connect(dsource.connectionString,autocommit=is_autocommit)
except:
if newConn is None:
print("Connection could not be made please check your configuration settings or that the server is alive")
print(sys.exc_info())
finally:
return newConn
def connect(self):
import vertica_python
return vertica_python.connect(host=self.host, port=self.port, database=self.database,
user=self.user, password=self.password,
read_timeout=self.read_timeout,
unicode_error='replace')
def main():
# file login.ini contains host, username, password, and db name
with open('login.ini', 'r') as f:
host = f.readline().strip()
username = f.readline().strip()
password = f.readline().strip()
database = f.readline().strip()
#use 5433
conn_info = {'host': host,
'port': 5433,
'user': username,
'password': password,
'database': database,
'read_timeout': 600,
'connection_timeout': 5}
# clear the log files
open ('NF.sql', 'w').close()
open ('NF.txt', 'w').close()
# grab input from command line argument
# only 2 argument allowed
if len(sys.argv) != 2:
print('Invalid input - follow format "python p2v.py database=something.txt"')
return
descriptor, db_file_name = sys.argv[1].split('=')
lines = [line.rstrip('\r\n') for line in open(db_file_name)]
# connect to database
try:
connection = vertica_python.connect(**conn_info)
cur = connection.cursor()
except Exception as e:
print(e)
print('Failed to connect to database, check login.ini')
return
print('\n#Table\t\tFailed\t\tReason')
with open ('NF.txt', 'a') as f_txt:
f_txt.write('#Table\t\tFailed\t\tReason\n')
# from the schema, evaluate each line into the table class which forms a key and non key list
for line in lines:
# If there is empty line continue
if not line:
continue
temp_table = Table(line)
if (temp_table.check_name_validity()):
#table names are valid, now check normal form
normal_forms, reason, table_failure = check_nf(temp_table, cur)
print_row(temp_table.table_name, normal_forms, reason, table_failure)
else:
print('Invalid table was found. \t' + line)
# write to file NF.txt
with open ('NF.txt', 'a') as f_txt:
f_txt.write('Invalid table was found. \t' + line + '\n')
print('')
def sql_trip(trail):
try:
with connect(host=Con_vert.host,
port=Con_vert.port,
user=Con_vert.user,
password=Con_vert.password) as con:
with open('./check/Sql/sql_trip.sql', 'r') as sql:
cursor = con.cursor()
cursor.execute(sql.read(), (trail, ))
data = cursor.fetchall()
# переменная водитель
driver_id = data[0][0]
# переменная клиент
customer_id = data[0][1]
# переменная водитель короткий
drv_id = data[0][2]
return driver_id, customer_id, drv_id
except:
driver_id, customer_id, drv_id = [
i for i in ["Нет данных" for i in range(3)]
]
return driver_id, customer_id, drv_id
def sql_old_drv(drv_id, trail):
try:
with connect(host=Con_vert.host,
port=Con_vert.port,
user=Con_vert.user,
password=Con_vert.password) as con:
with con.cursor() as cursor:
with open('./check/Sql/sql_old_drv-sql.sql', 'r') as sql:
cursor.execute(sql.read(), (
drv_id,
drv_id,
))
data = cursor.fetchall()
head = cursor.description
a, b = peremen(data, head)
put = trail + '/Сводная_по_водителю.csv'
save_csv(b, a, put)
return
except:
put = trail + '/Сводная_по_водителю.csv'
print("Connection error")
return
def load_db_data():
global faq_dictionary, faq_raw_dictionary, conn_info
for section in [
"ari", "ccpa", "escrow", "gi", "heloc", "ii", "pdp", "pi", "pt",
"rlt", "sm", "tax_statement"
]:
with vertica_python.connect(**conn_info) as connection:
cur = connection.cursor()
cur.execute(
f"""SELECT * FROM {config.schema_name}.static_faq_data WHERE faq_group = '{section}' AND root_question = true;"""
)
response = cur.fetchall()
questions = []
raw_questions = []
answers = []
# DB response is a list of lists (list of rows, each sublist is a list of row values). row1 = question, row2 = answer
for row in response:
questions.append(clean_sentence(row[1]))
raw_questions.append(row[1])
answers.append(row[2])
faq_dictionary[section] = (questions, answers)
faq_raw_dictionary[section] = (raw_questions, answers)
def test_mid_iterator_execution(self):
with vertica_python.connect(**conn_info) as conn:
cur = conn.cursor()
init_table(cur)
cur.execute(
""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa') """
)
cur.execute(
""" INSERT INTO vertica_python_unit_test (a, b) VALUES (2, 'bb') """
)
cur.execute(
""" INSERT INTO vertica_python_unit_test (a, b) VALUES (3, 'cc') """
)
conn.commit()
cur.execute(
"SELECT a, b from vertica_python_unit_test ORDER BY a ASC")
# don't finish iterating
for row in cur.iterate():
break
# make new query and verify result
cur.execute(""" SELECT COUNT(*) FROM vertica_python_unit_test """)
res = cur.fetchall()
assert 1 == len(res)
assert 3 == res[0][0]
def FraudDetalizationTable(date_from, date_to, city_id, drv_ids):
_date_from = str(date_from)
_date_to = str(date_to)
_city_id = str(city_id)
_drv_ids = drv_ids
with closing(
connect(host=Con_vert.host,
port=Con_vert.port,
user=Con_vert.user,
password=Con_vert.password,
data_base=Con_vert.data_base,
read_timeout=Con_vert.read_timeout)) as con:
with open('./fraud_inspector/Sql/FraudDetalizationTable.sql',
'r') as sql:
df = pd.read_sql_query(
sql.read(),
con,
params=[_date_from, _date_to, _city_id, _drv_ids])
cols = df.columns.tolist()
cols = cols[-1:] + cols[1:2] + cols[0:1]
df = df[cols]
return df
def get_connection(self):
connection_options = {
'database': self._db,
'host': self._server,
'port': self._port,
'user': self._username,
'password': self._password,
'backup_server_node': self._backup_servers,
'connection_load_balance': self._connection_load_balance,
'connection_timeout': self._timeout,
}
if self._client_lib_log_level:
connection_options['log_level'] = self._client_lib_log_level
# log_path is required by vertica client for using logging
# when log_path is set to '', vertica won't log to a file
# but we still get logs via parent root logger
connection_options['log_path'] = ''
if self._use_tls:
tls_context = self.get_tls_context()
connection_options['ssl'] = tls_context
try:
connection = vertica.connect(**exclude_undefined_keys(connection_options))
except Exception as e:
self.log.error('Unable to connect to database `%s` as user `%s`: %s', self._db, self._username, e)
self.service_check(self.SERVICE_CHECK_CONNECT, self.CRITICAL, tags=self._tags)
else:
self.service_check(self.SERVICE_CHECK_CONNECT, self.OK, tags=self._tags)
return connection
def get_overlappings(k, file_path, query_column):
conn_info = {
'host': 'SERVER_IP_ADDRESS',
'port': 5433,
'user': '******',
'password': '******',
'database': 'DATABASE_NAME',
'session_label': 'some_label',
'read_timeout': 6000,
'unicode_error': 'strict',
}
connection = vertica_python.connect(**conn_info)
cur = connection.cursor()
data = get_dataset(file_path)[[query_column]]
data[query_column] = data[query_column].apply(get_cleaned_text)
distinct_clean_values = data[query_column].unique()
joint_distinct_values = '\',\''.join(distinct_clean_values).encode('utf-8')
query = 'SELECT SUBQ.ids FROM (SELECT table_col_id AS ids,' \
'CONCAT(table_col_id,CONCAT(\'_\',REGEXP_REPLACE(REGEXP_REPLACE(' \
'tokenized, \'\W+\', \' \'), \' +\', \' \'))) AS COL_ELEM from cbi_inverted_index_2 WHERE REGEXP_REPLACE(' \
'REGEXP_REPLACE(tokenized, \'\W+\', \' \'), \' +\', \' \') IN (\'{}\') ' \
'GROUP BY table_col_id,CONCAT(table_col_id,CONCAT(\'_\',' \
'REGEXP_REPLACE(REGEXP_REPLACE(tokenized, \'\W+\', \' \'), \' +\', \' \'))) ) AS SUBQ GROUP BY SUBQ.ids ' \
'HAVING COUNT(COL_ELEM) > {} ' \
'ORDER BY COUNT(COL_ELEM) DESC LIMIT {};'.format(joint_distinct_values, 3, k)
cur.execute(query)
result = [item for sublist in cur.fetchall() for item in sublist]
return result
def test_create_new_table(self):
self.vertica.create_table(
schema="my_schema",
table="my_table_1",
columns=[("name", "VARCHAR(255)"), ("loadTs", "TIMESTAMP")],
)
self.vertica.copy(
schema="my_schema",
table="my_table_1",
path_or_str="string,2019-01-01 00:00:00",
header=False,
)
with vertica_python.connect(host="localhost",
database="docker",
user="******",
password="",
port=5433) as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM my_schema.my_table_1;")
self.assertEqual(
cursor.fetchone(),
["string", datetime.datetime(2019, 1, 1, 0, 0, 0)])
def vertica_conn(
section: str,
dsn: str = "",
):
"""
---------------------------------------------------------------------------
Reads the input DSN and creates a Vertica Database connection.
Parameters
----------
section: str
Name of the section in the configuration file.
dsn: str, optional
Path to the file containing the credentials. If empty, the ODBCINI
environment variable will be used.
Returns
-------
conn
Database connection
See Also
--------
new_auto_connection : Saves a connection to automatically create database cursors.
read_auto_connect : Automatically creates a connection.
"""
check_types([(
"dsn",
dsn,
[str],
)])
conn = vertica_python.connect(**read_dsn(section, dsn))
return conn
def setUp(self):
self.setUpClass()
self.logger.info(
"\n\n" + "-" * 50 + "\n Begin " + self.__class__.__name__ + "\n" + "-" * 50
)
self._connection = vertica_python.connect(**self._conn_info)
self.cursor = self._connection.cursor()
def fetching():
conn = vertica_python.connect(**VERTICA_CONFIG)
cur = conn.cursor()
with conn:
cur.execute("""
INSERT INTO {} (a, b)
SELECT 1, 'a'
UNION ALL
SELECT 2, 'b'
UNION ALL
SELECT 3, 'c'
UNION ALL
SELECT 4, 'd'
UNION ALL
SELECT 5, 'e'
""".format(TEST_TABLE))
assert cur.rowcount == -1
cur.execute("SELECT * FROM {};".format(TEST_TABLE))
cur.fetchone()
cur.rowcount == 1
cur.fetchone()
cur.rowcount == 2
cur.fetchall()
cur.rowcount == 5
def get_vertica_results(credentials, query):
"""Run a single query in Vertica and return the results."""
credentials_target = ExternalURL(url=credentials).output()
cred = None
with credentials_target.open('r') as credentials_file:
cred = json.load(credentials_file)
# Externalize autocommit and read timeout
connection = vertica_python.connect(user=cred.get('username'),
password=cred.get('password'),
host=cred.get('host'),
port=cred.get('port'),
database='warehouse',
autocommit=False,
read_timeout=None)
if not vertica_client_available:
raise ImportError('Vertica client library not available')
try:
cursor = connection.cursor()
cursor.execute(query)
results = cursor.fetchall()
finally:
connection.close()
return results
def vertica_connection():
'''Establish a connection with the Vertica database a return a connection object.
Args:
None
Returns:
Connection Object: connection object to the database.
Raises:
'''
logger.name = 'vertica_connection'
conn_info = {
'host': os.environ.get("VERTICA_HOST"),
'port': int(os.environ.get("VERTICA_PORT")),
'user': os.environ.get("VERTICA_USER"),
'password': os.environ.get("VERTICA_PASS"),
'database': os.environ.get("VERTICA_DB"),
# 10 minutes timeout on queries
'read_timeout': 600,
# default throw error on invalid UTF-8 results
'unicode_error': 'replace', # 'unicode_error': 'strict',
# SSL is disabled by default
'ssl': False
# 'connection_timeout': 5
# connection timeout is not enabled by default}
}
try:
conn = vp.connect(**conn_info)
logger.info('connection stablished...')
return conn
except Exception as e:
logger.critical('Exception Error: ' + str(e))
def load_data_g(gorod,date_from,date_to):
conn_info = {
'host': Con_vert.host,
'port': Con_vert.port,
'user': Con_vert.user,
'password': Con_vert.password
}
with connect(**conn_info) as con:
with open('./fraud_inspector/Sql/load_data-loaddata_g.sql', 'r') as load_data_sql:
data = pd.read_sql_query(
load_data_sql.read(), con, params=[gorod,date_from,date_to])
test = (data.groupby(['order_id'])['pattern_name']
.apply(', '.join).reset_index(name='pattern_name'))
del data['pattern_name']
data = pd.merge(data, test, on='order_id', how='inner')
data = data.fillna(0)
columns = data.columns.tolist()
columns = columns[:6] + columns[-1:] + columns[6:-1]
data = data[columns]
data[['driver_id', 'compensation']] = \
data[['driver_id', 'compensation']].astype(int).astype(str)
data = data.drop_duplicates()
data = data.values.tolist()
return data
def conn(cls):
"""
return the vertica connection handle to the configured server
"""
config = cls.config
try:
conn_driver_dict = {}
conf_to_driver_map = {
'host': 'host',
'username': '******',
'password': '******',
'revision_db_name': 'database',
'port': 'port'
}
for conf_key, conf_value in config.iteritems():
try:
driver_key = conf_to_driver_map[conf_key]
driver_value = conf_value
# NOTE: Vertica Python driver requires non-unicode strings
if isinstance(driver_value, unicode):
driver_value = str(driver_value)
conn_driver_dict[driver_key] = driver_value
except KeyError:
pass
conn = vertica_python.connect(conn_driver_dict)
except Exception, e:
raise DbError(
"Cannot connect to Vertica Db: %s\n"
"Ensure that the server is running and you can connect normally"
% e.message)
def SQL_SELECT_from_vertica(SQLRequest, conn_info):
this_function_name = sys._getframe().f_code.co_name
print(f"{this_function_name} started")
conn_info = {
'host': conn_info['host'],
'port': conn_info['port'],
'user': conn_info['user'],
'password': conn_info['password'],
'database': conn_info['database'],
# autogenerated session label by default,
# 'session_label': 'some_label',
# default throw error on invalid UTF-8 results
'unicode_error': conn_info['unicode_error'],
# SSL is disabled by default
'ssl': conn_info['ssl'],
# using server-side prepared statements is disabled by default
'use_prepared_statements': conn_info['use_prepared_statements'],
# connection timeout is not enabled by default
'connection_timeout': conn_info['connection_timeout']
}
resultSQLList = []
with vertica_python.connect(**conn_info) as connection:
cur = connection.cursor()
cur.execute(SQLRequest)
for row in cur.iterate():
resultSQLList.append(row)
cur.close()
connection.close()
connection.close()
print(f"{this_function_name} fullfilled")
return resultSQLList
def test_iterator(self):
with vertica_python.connect(**conn_info) as conn:
cur = conn.cursor()
init_table(cur)
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa') """)
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (2, 'bb') """)
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (3, 'cc') """)
conn.commit()
cur.execute("SELECT a, b from vertica_python_unit_test ORDER BY a ASC")
i = 0;
for row in cur.iterate():
if i == 0:
assert 1 == row[0]
assert 'aa' == row[1]
if i == 1:
assert 2 == row[0]
assert 'bb' == row[1]
if i == 2:
assert 3 == row[0]
assert 'cc' == row[1]
i = i + 1
def fetch_from_vertica_to_df(self, data_set, query):
data_set_query = self.get_data_set_attribute(data_set, 'query')
if data_set_query != query:
connection = connect(self.connection_details)
cursor = connection.cursor()
print 'Executing ', data_set, 'Query...'
print query
columns = get_column_names_from_sql_query(query)
cursor.execute(query)
data = []
while True:
rows = cursor.fetchmany(10000)
data.extend([[str(ele) for ele in row] for row in rows])
if len(rows) <= 1:
break
df = MetadataDataFrame(data=data, columns=columns, meta_info={'query': query, 'built_features': [], 'aggregate_values': {},
'columns': columns})
cursor.close()
if len(df) == 0:
raise(ValueError('SQL result in empty fetch!!'))
else:
self.set_data_set_attribute(data_set, 'data', df)
self.set_data_set_attribute(data_set, 'query', query)
self.set_data_set_attribute(data_set, 'columns', columns)
self.set_data_set_attribute(data_set, 'built_features', [])
def fetch_from_vertica_to_df(self, data_set, query):
data_set_query = self.get_data_set_attribute(data_set, 'query')
if data_set_query != query:
connection = connect(self.connection_details)
cursor = connection.cursor()
print 'Executing ', data_set, 'Query...'
print query
columns = get_column_names_from_sql_query(query)
cursor.execute(query)
data = []
while True:
rows = cursor.fetchmany(10000)
data.extend([[str(ele) for ele in row] for row in rows])
if len(rows) <= 1:
break
df = MetadataDataFrame(data=data,
columns=columns,
meta_info={
'query': query,
'built_features': [],
'aggregate_values': {},
'columns': columns
})
cursor.close()
if len(df) == 0:
raise (ValueError('SQL result in empty fetch!!'))
else:
self.set_data_set_attribute(data_set, 'data', df)
self.set_data_set_attribute(data_set, 'query', query)
self.set_data_set_attribute(data_set, 'columns', columns)
self.set_data_set_attribute(data_set, 'built_features', [])
def test_update(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
cur.execute(
""" INSERT INTO vertica_python_unit_test (a, b) VALUES (5, 'cc') """
)
# validate insert count
res = cur.fetchone()
assert 1 == len(res)
assert 1 == res[0]
conn.commit()
cur.execute(
""" UPDATE vertica_python_unit_test SET b = 'ff' WHERE a = 5 """)
# validate update count
assert cur.rowcount == -1
res = cur.fetchone()
assert 1 == len(res)
assert 1 == res[0]
conn.commit()
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 5")
res = cur.fetchall()
assert 1 == len(res)
assert 5 == res[0][0]
assert 'ff' == res[0][1]
def test_update(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (5, 'cc') """)
# validate insert count
res = cur.fetchone()
assert 1 == len(res)
assert 1 == res[0]
conn.commit()
cur.execute(""" UPDATE vertica_python_unit_test SET b = 'ff' WHERE a = 5 """)
# validate update count
assert cur.rowcount == -1
res = cur.fetchone()
assert 1 == len(res)
assert 1 == res[0]
conn.commit()
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 5")
res = cur.fetchall()
assert 1 == len(res)
assert 5 == res[0][0]
assert 'ff' == res[0][1]
def load(self, credits):
import vertica_python
with vertica_python.connect(**credits) as con:
cursor = con.cursor('dict')
cursor.execute("""
select table_schema, table_name, column_name
from v_catalog.columns
union all
select max(table_schema) over(partition by projection_id),
projection_name, projection_column_name
from v_catalog.projection_columns
union all
select table_schema, table_name, column_name
from v_catalog.view_columns
""")
for row in cursor.fetchall():
table_name = (row['table_schema'] + '.' +
row['table_name']).lower()
self.table_columns.setdefault(table_name, list()).append(
row['column_name'].lower())
cursor.execute("""
select schema_name, function_name
from v_catalog.user_functions
where procedure_type = 'User Defined Transform'
""")
for row in cursor.fetchall():
func_name = (row['schema_name'] + '.' +
row['function_name']).lower()
self.transform_functions.add(func_name)
def conn(cls):
"""
return the vertica connection handle to the configured server
"""
config = cls.config
try:
conn_driver_dict = {}
conf_to_driver_map = {'host':'host',
'username':'******',
'password':'******',
'revision_db_name':'database',
'port':'port'}
for conf_key, conf_value in config.iteritems():
try:
driver_key = conf_to_driver_map[conf_key]
driver_value = conf_value
# NOTE: Vertica Python driver requires non-unicode strings
if isinstance(driver_value, unicode):
driver_value = str(driver_value)
conn_driver_dict[driver_key] = driver_value
except KeyError:
pass
conn = vertica_python.connect(conn_driver_dict)
except Exception, e:
raise DbError("Cannot connect to Vertica Db: %s\n"
"Ensure that the server is running and you can connect normally"
% e.message)
def _copy(self, path, copy_sql):
'''
@description: 使用copy的方式来插入（仅限于Vertica插入）
@param copy_sql :样例："copy {schema.table}(columnstr) FROM STDIN DELIMITER ',' ENCLOSED BY '\"' commit;"
@return:
'''
conn_info = {
'host': self._server_name,
'port': 5433,
'user': self._user_name,
'password': self._user_password,
'database': self._database_name,
'log_level': logging.DEBUG,
# 默认情况下会自动生成会话标签，
'session_label': 'some_label',
# 无效的UTF-8结果默认抛出错误
'unicode_error': 'strict',
# 默认情况下禁用SSL
'ssl': False,
# 默认情况下，禁用使用服务器端预处理语句
'use_prepared_statements': False,
# 默认情况下未启用连接超时
# 套接字操作5秒超时（建立TCP连接或读/写操作）
'connection_timeout': 5}
# 使用copy的方式来插入
# copy = "copy {parame.schema}.test_{parame.table}({columnstr}) FROM STDIN DELIMITER ',' ENCLOSED BY '\"' commit;"
connection = vertica_python.connect(**conn_info)
c = connection.cursor()
with open(path, "rb") as fs:
c.copy(copy_sql, fs, buffer_size=65536)
c.execute("commit;")
c.close()
connection.close()
def test_query_errors(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
failed = False;
# create table syntax error
try:
failed = False;
cur.execute("""CREATE TABLE vertica_python_unit_test_fail (
a int,
b varchar(32),,,
) ;
""")
except errors.VerticaSyntaxError:
failed = True;
assert True == failed
# select table not found error
try:
failed = False;
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa') """)
cur.execute(""" SELECT * from vertica_python_unit_test_fail """)
#conn.commit()
except errors.QueryError:
failed = True;
assert True == failed
# verify cursor still useable after errors
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
res = cur.fetchall()
assert 1 == len(res)
assert 1 == res[0][0]
assert 'aa' == res[0][1]
def test_iterator(self):
with vertica_python.connect(**conn_info) as conn:
cur = conn.cursor()
init_table(cur)
cur.execute(
""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa') """
)
cur.execute(
""" INSERT INTO vertica_python_unit_test (a, b) VALUES (2, 'bb') """
)
cur.execute(
""" INSERT INTO vertica_python_unit_test (a, b) VALUES (3, 'cc') """
)
conn.commit()
cur.execute(
"SELECT a, b from vertica_python_unit_test ORDER BY a ASC")
i = 0
for row in cur.iterate():
if i == 0:
assert 1 == row[0]
assert 'aa' == row[1]
if i == 1:
assert 2 == row[0]
assert 'bb' == row[1]
if i == 2:
assert 3 == row[0]
assert 'cc' == row[1]
i = i + 1
def test_configuration_routine(self):
"""Ensure that the integration routines can be configured."""
routine_config = dict(patch={
'vertica_python.vertica.connection.Connection':
dict(routines=dict(cursor=dict(
operation_name='get_cursor',
trace_enabled=True,
), ), ),
}, )
# Make a copy of the vertica config first before we merge our settings over
# DEV: First argument gets merged into the second
copy = _deepmerge(config.vertica, dict())
overrides = _deepmerge(routine_config, copy)
with self.override_config('vertica', overrides):
patch()
import vertica_python
test_tracer = get_dummy_tracer()
conn = vertica_python.connect(**VERTICA_CONFIG)
Pin.override(conn, service='mycustomservice', tracer=test_tracer)
conn.cursor() # should be traced now
conn.close()
spans = test_tracer.writer.pop()
assert len(spans) == 1
assert spans[0].name == 'get_cursor'
assert spans[0].service == 'mycustomservice'
def get_connection(self):
connection_options = {
'database': self._db,
'host': self._server,
'port': self._port,
'user': self._username,
'password': self._password,
'backup_server_node': self._backup_servers,
'connection_load_balance': self._connection_load_balance,
'connection_timeout': self._timeout,
}
if self._client_lib_log_level:
connection_options['log_level'] = self._client_lib_log_level
# log_path is required by vertica client for using logging
# when log_path is set to '', vertica won't log to a file
# but we still get logs via parent root logger
connection_options['log_path'] = ''
if self._tls_verify: # no cov
# https://docs.python.org/3/library/ssl.html#ssl.SSLContext
# https://docs.python.org/3/library/ssl.html#ssl.PROTOCOL_TLS
tls_context = ssl.SSLContext(protocol=PROTOCOL_TLS_CLIENT)
# https://docs.python.org/3/library/ssl.html#ssl.SSLContext.verify_mode
tls_context.verify_mode = ssl.CERT_REQUIRED
# https://docs.python.org/3/library/ssl.html#ssl.SSLContext.check_hostname
tls_context.check_hostname = self._validate_hostname
# https://docs.python.org/3/library/ssl.html#ssl.SSLContext.load_verify_locations
if self._cafile or self._capath:
tls_context.load_verify_locations(self._cafile, self._capath,
None)
# https://docs.python.org/3/library/ssl.html#ssl.SSLContext.load_default_certs
else:
tls_context.load_default_certs(ssl.Purpose.SERVER_AUTH)
# https://docs.python.org/3/library/ssl.html#ssl.SSLContext.load_cert_chain
if self._cert:
tls_context.load_cert_chain(self._cert,
keyfile=self._private_key)
connection_options['ssl'] = tls_context
try:
connection = vertica.connect(**connection_options)
except Exception as e:
self.log.error(
'Unable to connect to database `%s` as user `%s`: %s',
self._db, self._username, e)
self.service_check(self.SERVICE_CHECK_CONNECT,
self.CRITICAL,
tags=self._tags)
else:
self.service_check(self.SERVICE_CHECK_CONNECT,
self.OK,
tags=self._tags)
return connection
def _get_connection(self) -> vertica_python.Connection:
"""
Open a connection to Vertica, returning vertica_python connection obj
:return: Connection object
"""
conn_info = self._build_conn_info()
return vertica_python.connect(**conn_info)
def get_data(query):
cnx = vp.connect(**config)
cursor = cnx.cursor('dict')
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
cnx.close
return results
def test_empty_query(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
cur.execute("")
res = cur.fetchall()
assert 0 == len(res)
def test_with_conn(self):
with vertica_python.connect(**conn_info) as conn:
cur = conn.cursor()
init_table(cur)
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa'); commit; """)
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
res = cur.fetchall()
assert 1 == len(res)
def query_and_fetchall(self, query):
"""
Creates new connection to vertica, executes query, returns all fetched results. Closes connection.
:param query: query to execute
:return: all fetched results as returned by cursor.fetchall()
"""
with vertica_python.connect(**conn_info) as conn:
cur = conn.cursor()
cur.execute(query)
return cur.fetchall()
def test_conn_commit(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (5, 'cc') """)
conn.commit()
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 5")
res = cur.fetchall()
assert 1 == len(res)
def test_inline_commit(self):
conn = vertica_python.connect(conn_info)
cur = conn.cursor()
init_table(cur)
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa'); commit; """)
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
res = cur.fetchall()
assert 1 == len(res)
assert 1 == res[0][0]
assert 'aa' == res[0][1]
def db_conn(self):
connection = connect({
'host': '198.202.90.32',
'port': 5433,
'user': '******',
'password': '******',
'database': 'wellderly'
})
return connection
def run_query(self, query, user):
import vertica_python
if query == "":
json_data = None
error = "Query is empty"
return json_data, error
connection = None
try:
conn_info = {
'host': self.configuration.get('host', ''),
'port': self.configuration.get('port', 5433),
'user': self.configuration.get('user', ''),
'password': self.configuration.get('password', ''),
'database': self.configuration.get('database', ''),
'read_timeout': self.configuration.get('read_timeout', 600)
}
if self.configuration.get('connection_timeout'):
conn_info['connection_timeout'] = self.configuration.get('connection_timeout')
connection = vertica_python.connect(**conn_info)
cursor = connection.cursor()
logger.debug("Vetica running query: %s", query)
cursor.execute(query)
# TODO - very similar to pg.py
if cursor.description is not None:
columns_data = [(i[0], i[1]) for i in cursor.description]
rows = [dict(zip((c[0] for c in columns_data), row)) for row in cursor.fetchall()]
columns = [{'name': col[0],
'friendly_name': col[0],
'type': types_map.get(col[1], None)} for col in columns_data]
data = {'columns': columns, 'rows': rows}
json_data = json_dumps(data)
error = None
else:
json_data = None
error = "No data was returned."
cursor.close()
except KeyboardInterrupt:
error = "Query cancelled by user."
json_data = None
finally:
if connection:
connection.close()
return json_data, error
def __init__(self):
self.host = "127.0.0.1"
if len(sys.argv) == 2:
self.port = sys.argv[1]
else:
self.port = 5433
self.user = "******"
self.password = "******"
self.database = ""
self.conn = connect(host=self.host, port=int(self.port), user=self.user, password=self.password, database=self.database)
self.cur = self.conn.cursor()
def connect(self, autocommit=False):
"""
Creates a connection to a Vertica database using the supplied credentials.
:param autocommit: whether the connection should automatically commit.
:type autocmommit: bool
"""
# vertica-python 0.5.0 changes the code for connecting to databases to use kwargs instead of a dictionary.
# The 'database' parameter is included for DBAPI reasons and does not actually affect the session.
connection = vertica_python.connect(user=self.user, password=self.password, host=self.host, port=self.port,
database="", autocommit=autocommit, read_timeout=self.read_timeout)
return connection
def vertica_BuildConnect():
"""create a vertica conntion object"""
conn_info = {
"host": db_host,
"port": db_port,
"user": db_user,
"password": db_pass,
"database": db_database,
# 10 minutes timeout on queries
"read_timeout": read_timeout,
}
connection = vertica_python.connect(**conn_info)
# print(connection)
return connection
def create_cn(hostname, password, username, dbname, port):
"""
Open connection to database.
:param hostname:
:param password:
:param username:
:param dbname: string
:return: vertica_python.Connection
"""
cn = connect(host=hostname, user=username, database=dbname,
password=password, port=port)
print('Created connection: {0}.'.format(hostname))
return cn
def test_multi_inserts_and_transaction(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
conn2 = vertica_python.connect(**conn_info)
cur2 = conn2.cursor()
# insert data without a commit
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (2, 'bb') """)
# verify we can see it from this cursor
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2")
res = cur.fetchall()
assert 1 == len(res)
assert 2 == res[0][0]
assert 'bb' == res[0][1]
# verify we cant see it from other cursor
cur2.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2")
res = cur2.fetchall()
assert 0 == len(res)
# insert more data then commit
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (3, 'cc') """)
cur.execute(""" commit; """)
# verify we can see it from this cursor
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2 or a = 3")
res = cur.fetchall()
assert 2 == len(res)
# verify we can see it from other cursor
cur2.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2 or a = 3")
res = cur2.fetchall()
assert 2 == len(res)
def get_conn(self):
"""
Returns verticaql connection object
"""
conn = self.get_connection(self.vertica_conn_id)
conn_config = {"user": conn.login, "password": conn.password, "database": conn.schema}
conn_config["host"] = conn.host or "localhost"
if not conn.port:
conn_config["port"] = 5433
else:
conn_config["port"] = int(conn.port)
conn = connect(**conn_config)
return conn
def connect(self):
logger.info("DB Connection Initiated")
conn_info = {
'host': self.server,
'port': self.port,
'user': self.user,
'password': self.password,
'database': self.database_name,
'read_timeout': 600,
'unicode_error': 'strict',
'ssl': False
}
self.con = vertica_python.connect(**conn_info)
logger.info("DB Connection Established. User: %s, Database: %s, Server: %s (%s)" %
(self.user, self.database_name, self.server, self.port))
def connect(self, database=None, user=None, password=None, host=None,
port=None):
db = (database or self.dbname)
user = (user or self.user)
password = (password or self.password)
host = (host or self.host)
port = (port or self.port)
conn = vertica.connect(database=db, user=user, password=password,
host=host, port=int(port))
# Print notice message for PROFILE (#42)
def print_notice(message):
print('%(Severity)s: %(Message)s' % message.values)
hint = message.values.get('Hint')
if hint:
print('HINT: ' + hint)
conn.notice_handler = print_notice
# HACK: Modify vertica_python's connection socket to do keep alive
# TODO: Keep alive for Windows and other platforms
# http://stackoverflow.com/questions/12248132/how-to-change-tcp-keepalive-timer-using-python-script
sock = conn._socket()
if sys.platform == 'darwin': # Mac OS X
tcp_keepalive = 0x10
sock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
sock.setsockopt(socket.IPPROTO_TCP, tcp_keepalive, 60)
sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPINTVL, 10)
sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPCNT, 5)
elif sys.platform.startswith('linux'):
sock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPIDLE, 60)
sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPINTVL, 10)
sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPCNT, 5)
if hasattr(self, 'conn'):
self.conn.close()
self.conn = conn
# self.conn.autocommit = True
self.dbname = db
self.user = user
self.password = password
self.host = host
self.port = port
def main():
args = docopt(__doc__)
start_date = args.get('--start-date')
print('start_date: {}'.format(start_date))
conn_info = {'host': args.get('--host'),
'port': int(args.get('--port')),
'user': args.get('--username'),
'password': args.get('--password'),
'database': args.get('--database'),
'read_timeout': 3600} # 1 hour timeout on queries
print(conn_info)
with vertica_python.connect(**conn_info) as conn:
cur = conn.cursor()
cur.execute("SELECT SYSDATE")
for row in cur.iterate():
print(row)
def vertica_db_filler():
connection = vertica_python.connect(**conn_info)
cur = connection.cursor('dict')
cur.execute("SELECT TO_HEX(id) as id,name from MonMetrics.Definitions")
id_list = cur.fetchall()
for id_dict in id_list:
# find all definition dimensions and dimension sets
print id_dict['name']
query = SELECT_DIMENSIONS_QUERY.format(str(id_dict['id']))
cur = connection.cursor('dict')
cur.execute(query)
dim_list = cur.fetchall()
for dim in dim_list:
create_measurements(datetime.datetime.utcnow(), dim['id'], connection)
connection.close()
print('Finished loading DB')
def test_nextset(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
cur.execute("select 1; select 2;")
res = cur.fetchall()
assert 1 == len(res)
assert 1 == res[0][0]
assert cur.fetchone() is None
assert cur.nextset() == True
res = cur.fetchall()
assert 1 == len(res)
assert 2 == res[0][0]
assert cur.fetchone() is None
assert cur.nextset() is None
def test_delete(self):
conn = vertica_python.connect(**conn_info)
cur = conn.cursor()
init_table(cur)
cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (5, 'cc') """)
conn.commit()
cur.execute(""" DELETE from vertica_python_unit_test WHERE a = 5 """)
# validate delete count
assert cur.rowcount == -1
res = cur.fetchone()
assert 1 == len(res)
assert 1 == res[0]
conn.commit()
cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 5")
res = cur.fetchall()
assert 0 == len(res)
