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