def insertIntoTable(df2): print('entered here') udaExec = teradata.UdaExec(appName="test", version="1.0", logConsole=False) with udaExec.connect(method="odbc", system=host, username=username, password=password, driver="Teradata") as connect: print('entered') df3 = pd.read_sql("select count(*) from dev_wrk.customers_tmp", connect) print(df3) connect.execute("delete from dev_wrk.customers_tmp") df3 = pd.read_sql("select count(*) from dev_wrk.customers_tmp", connect) print(df3) try: for i in range(len(df2)): connect.execute("INSERT INTO DEV_WRK.CUSTOMERS_TMP VALUES(" + "'" + str(df2["Customer_Name"].loc[i]) + "','" + str(df2["ID"].loc[i]) + "','" + str(df2["Open_Date"].loc[i]) + "','" + str(df2["Last_Consulted_Date"].loc[i]) + "','" + str(df2["Vaccination_Id"].loc[i]) + "','" + str(df2["Dr_Name"].loc[i]) + "','" + str(df2["State"].loc[i]) + "','" + str(df2["Country"].loc[i]) + "',0,'" + str(df2["DOB"].loc[i]) + "','" + str(df2["Is_Active"].loc[i]) + "')") return 'pass' except Exception as e: print(e)
def __init__(self, znr_info, adressbook_info): """ Contain fields: self.tab_number self.first_name self.surname self.patronymic self.user_alpha_email self.user_sigma_email self.user_omega_email self.login self.roles_to_add self.tab_number_from_adresbook self.znr_number self.system_type self.tb_code """ {setattr(self, key, value) for key, value in znr_info.items()} {setattr(self, key, value) for key, value in adressbook_info.items()} self.login = self.translit(self.surname + '-' + self.first_name[0] + self.patronymic[0]) # Если из телефонного справочника пришел код тербанка, то добавляем его к логину if self.tb_code: self.login = self.tb_code + '-' + self.login # Если забирая информацию из телефонного справочника удалось забрать табельный номер, # то этот табельный номер считаем действительным self.tab_number = self.tab_number_from_adresbook if self.tab_number_from_adresbook else self.tab_number # Если из система определена как ПРОМ, то используем конфиг для подключения к ПРОМ, иначе к ПСИ td_config_file = 'udaexec.ini' if self.system_type == 'prom' else 'uratd_exec.ini' udaexec = teradata.UdaExec(appConfigFile=td_config_file) self.session = udaexec.connect("${dataSourceName}") self.is_created = False self.added_roles = []
def transform(self, data: Data, params: Params) -> Data: if params["file_path"]: with open(params["file_path"], "r") as f: sql = f.read() else: sql = params["sql"] sort_alphabetically = params["sort_alphabetically"] start = time.time() udaExec = teradata.UdaExec( appName="td", version="1.0", configureLogging=False, logConsole=False, logLevel="TRACE", dataTypeConverter=customDataTypeConverter(), ) conn = udaExec.connect(method="odbc", DSN="Teradata") df = pd.read_sql_query(sql, conn) logger.info( "teradata returned %s rows in % seconds", str(len(df)), str(round(time.time() - start)), ) conn.close() if sort_alphabetically: df = df.sort_index(axis=1) return {"df": df}
def connect_teradata(env, connector): env_n, env_dsn, env_short, usr, passw = load_db_info(env) if connector == "pyodbc": conn = odbc.connect( 'DRIVER={Teradata};VERSION=14.10;' + f"DBCNAME={env_n};DSN={env_dsn};UID={usr};PWD={passw};QUIETMODE=YES", autocommit=True) return (conn) elif connector == "teradata": udaExec = teradata.UdaExec( appName="Anomaly Detection", version='1.0', odbcLibPath="/opt/teradata/client/15.10/odbc_64/lib/libodbc.so", logConsole=False) session = udaExec.connect(method='odbc', system=env_n, username=usr, password=passw) return (session) else: raise ValueError( "Wrong value error: Need to specify connector as either teradata or pyodbc" )
def get_columns_str(table_name): ''' Get cols and datatypes for table name. Tablename includes schema / db name :param table_name: :return: String of cols, comma separated ''' dsn = 'TDDB' udaExec = teradata.UdaExec(appName="tdPyInterface", version="1.0", logConsole=False, appConfigFile="tdPyInterface.ini") session = udaExec.connect(dsn) cols = [] # to store cols col_qry = "select * from " + table_name + ' where 1=2' cursor = session.execute(col_qry) for row in cursor.description: cols.append(row[0]) tbl_cols = [] # to store cols in expected format for x in cols: tbl_cols.append(x.strip()) # convert to str preserving string quotes and delim col_str = ",".join(tbl_cols) return col_str
def fetch_table_data_json(table_name): dsn = 'TDDB' # 'TDDEV', 'TDDB' udaExec = teradata.UdaExec(appName="tdPyInterface", version="1.0", logConsole=False, appConfigFile="tdPyInterface.ini") session = udaExec.connect(dsn) rows = [] # to store rows list cols = [] qry = "select top 10000 cast(JSON_Compose(" + get_columns_str( table_name) + ") AS CLOB(500K)) PAYLOAD from " + table_name # print(qry) cursor = session.execute(qry) try: while True: row = cursor.fetchone().values rows.append(json.loads( row[0].strip())) # to convert str row to dict if row is None: break except Exception as e: # to handle None # print('Caught exception: ' + str(e)) print("Payload ready") # print(rows) return rows
def connectTD(): print "Attempt to connect to TDPRO Database" print "************************************" print varSettings.td_configfile_name with open(varSettings.td_configfile_name) as f: file_config = f.read() #read credential file prior to the connecting config = ConfigParser.RawConfigParser(allow_no_value=True) config.readfp(io.BytesIO(file_config)) udaExec = teradata.UdaExec( appName="ConnectTDDEV", version="2", odbcLibPath="/opt/unixodbc-2.3.4-0/share/man/man1/odbcinst.1") fh = open(varSettings.pos_txt_file_path, "w") with udaExec.connect(method=config.get('DEFAULT', 'method'), system=config.get('TDDEV', 'system'), username=config.get('TDDEV', 'username'), password=config.get('TDDEV', 'password')) as session: for row in session.execute( "select OREPLACE(SITE_NAME,'.',' ') as SITE_NAME from D_ADS_BEX_COMMON.DM_OMNITURE_SITE" ): #Write the point of sale to file fh.write(str(row[0] + "\n")) print "File Writing completes. The pox.txt is updated with the latest data from database" fh.close()
def get_columns(table_name): # TODO: Add datatype map ''' Get cols and datatypes for table name. Tablename includes schema / db name :param table_name: :return: list of dicts. Each dict represents a col. e.g. {"Name": "Col1"},{"Name": "Col2"} ''' dsn = 'TDDB' udaExec = teradata.UdaExec(appName="tdPyInterface", version="1.0", logConsole=False, appConfigFile="tdPyInterface.ini") session = udaExec.connect(dsn) cols = [] # to store cols col_qry = "select * from " + table_name + ' where 1=2' cursor = session.execute(col_qry) for row in cursor.description: cols.append(row[0]) tbl_cols = [] # to store cols in expected format for x in cols: tbl_cols.append({"Name": x.strip()}) # print(tbl_cols) return tbl_cols
def Load_Final_Data(): print("Hi") udaExec = teradata.UdaExec(appName="test", version="1.0", logConsole=False) with udaExec.connect(method="odbc", system=host, username=username, password=password, driver="Teradata") as connect: df = pd.read_sql("select distinct country from dev_wrk.customers_tmp", connect) df.columns = ['country'] for i in range(len(df)): COUNTRY_CODE = str(df["country"].loc[i]) cursor = connect.cursor() select_query = "select distinct country from dev_wrk.countries where country_code = '" + COUNTRY_CODE + "'" cursor.execute(select_query) # get all records records = cursor.fetchall() for row in records: str1 = row[0] #Check Main table presence and fetch the tablename table = checkTablePresence(str1) #inserting the data into final tables based on country insert_query = "INSERT INTO DEV_WRK." + table + "SEL * FROM DEV_WRK.CUSTOMERS_TMP WHERE COUNTRY = '" + COUNTRY_CODE + "'" cursor.execute(insert_query) #print("Total number of rows in table: ", cursor.rowcount) return "pass"
def loginToTD(strHost, strUsr, strPwd): try: #tdSession = td.connect(host=strHost, user=strUsr, password=strPwd, logmech='LDAP') tdInfo = td.UdaExec(appName = 'UCBD', version = '0.1', logConsole = True) tdConn = tdInfo.connect(method = 'odbc', driver = 'Teradata', system = strHost, username = strUsr, password = strPwd, authentication = 'LDAP') return True except: return False
def run(args): if dependencies_missing: module.log('Python Teradata module missing, cannot continue', level=error) return # Define UdaExec ODBC connection "application" globally, must be before LogHandler udaExec = teradata.UdaExec(appName="Auth", version="1.0", logConsole=False, configureLogging=False) module.LogHandler.setup(msg_prefix='{}:{} - '.format(args['rhost'], 1025)) scanner = login_scanner.make_scanner(lambda host, port, username, password: valid_login(udaExec, host, username, password)) scanner(args)
def create_session(username, password, host): # Init databse connection udaExec = teradata.UdaExec( appName="SQL shell", version="1.0", logConsole=False, odbcLibPath="/opt/teradata/client/ODBC_64/lib/libodbc.so") connection = udaExec.connect(method="odbc", system=host, username=username, password=password) return connection
def __init__(self, cluster, host): self.cluster_details = conf_info('teradata') if cluster in self.cluster_details: self.cluster_details = self.cluster_details[cluster] host = self.cluster_details['host'] uda_exec = teradata.UdaExec(appName="Jupyter Notebooks", version="1.0", logConsole=False) password = TeradataConnection._get_password_() connection = uda_exec.connect(method="odbc", system=host, username=getpass.getuser(), password=password.replace('$', '$$')) super(TeradataConnection, self).__init__(connection)
def testResumeFromCheckPoint(self): checkpoint = "testResumeFromCheckPoint" self.udaExec.checkpoint(checkpoint) udaExec = teradata.UdaExec(configFiles=configFiles, configureLogging=False) self.assertEqual(udaExec.resumeFromCheckpoint, checkpoint) with udaExec.connect("ODBC") as session: self.assertIsNone( session.execute("SELECT 1").fetchone(), "Query was executed but should have been skipped.") udaExec.checkpoint("notTheExpectedCheckpoint") self.assertIsNone( session.execute("SELECT 1").fetchone(), "Query was executed but should have been skipped.") udaExec.checkpoint(checkpoint) self.assertEqual(session.execute("SELECT 1").fetchone()[0], 1) # Clear the checkpoint. self.udaExec.checkpoint() udaExec = teradata.UdaExec(configFiles=configFiles, configureLogging=False) self.assertIsNone(udaExec.resumeFromCheckpoint) udaExec.setResumeCheckpoint(checkpoint) self.assertEqual(udaExec.resumeFromCheckpoint, checkpoint)
def _get_uda_exec(params=None, dataTypeConverter=type_conversion.DataTypeConverter()): uda_exec = None try: uda_exec = teradata.UdaExec(dataTypeConverter=dataTypeConverter, **params) except OSError: raise teradata_errors.TeradataError( 'Impossible to connect to Teradata. Could not initialize UdaExec. Driver issue.' ) except (teradata.DatabaseError, teradata.InterfaceError): raise teradata_errors.TeradataError( 'Impossible to connect to Teradata. Could not initialize UdaExec.') return uda_exec
def DB_CONNECTION_MNG_1(): '''CONNECTION TO Teradata DWH''' print('\n INIZIO CONNESSIONE \n') host, username, password = '******', 'xxxxx', 'xxxx' udaExec = teradata.UdaExec(appName="HelloWorld", version="1.0", logConsole=False) session = udaExec.connect(method="odbc", system=host, username="******", password="******") print(' \n CONNESSIONE EFFETTUATA \n') return (udaExec.connect(method="odbc", system=host, username="******", password="******"))
def run_teradata_query(self, sql_query): # Get Creds host, username, password = self.credentials[ 'server'], self.credentials['username'], self.credentials[ 'password'], udaExec = teradata.UdaExec(appName=host, version="1.0", logConsole=False) with udaExec.connect(method="odbc", system=host, username=username, password=password.replace('$', '$$')) as connect: df = pd.read_sql(sql_query, connect) print(df.shape) return df
def main(): """Main""" udaexec = teradata.UdaExec(appName="td_envmgr_test_init" ,version=1) # Where am i logging.info("PWD={}".format(os.path.curdir)) logging.info("PWD={}".format(os.path.abspath(os.path.curdir))) # Get environment variable for config session = udaexec.connect(method='ODBC', system="192.168.31.142", username='******', password='******',driver='Teradata Database ODBC Driver 16.00') udaexec_set_defaults(udaexec) # Delete from databases session.execute("DELETE DATABASE ${DBADMIN_ENVCONFIG}" ,ignoreErrors = [3802]) session.execute("DELETE DATABASE ${DBADMIN_SP}" ,ignoreErrors = [3802]) session.execute("DELETE DATABASE ${DBADMIN}" ,ignoreErrors = [3802]) # Drop databases session.execute("DROP DATABASE ${DBADMIN_ENVCONFIG}" ,ignoreErrors = [3802]) session.execute("DROP DATABASE ${DBADMIN_SP}" ,ignoreErrors = [3802]) session.execute("DROP DATABASE ${DBADMIN}" ,ignoreErrors = [3802])
def run(args): # Define UdaExec ODBC connection "application", must be before LogHandler udaExec = teradata.UdaExec(appName="Auth", version="1.0", logConsole=False, configureLogging=False) # Metasploit LogHandler module.LogHandler.setup(msg_prefix='{} - '.format(args['rhost'])) # Return error for missing dependency if dependencies_missing: logging.error('Python Teradata module missing, cannot continue') return # Set variables to current RHOST, and USERNAME and PASSWORD options host = args['rhost'] user = args['username'] password = args['password'] # Perform login attempt module.log(host + ' - ' + user + ':' + password + ' - Starting') try: session = udaExec.connect(method="odbc", system=host, username=user, password=password) except teradata.api.Error as e: logging.error(user + ':' + password + ' - ' + format(e)) return else: module.log(host + ' - ' + user + ':' + password + ' - Login Successful', level='good') try: query = args['sql'] module.log(host + ' - Starting - ' + query) for row in session.execute(query): outputRow = str(row) module.log(host + ' - ' + outputRow, level='good') except teradata.api.Error as e: logging.error(format(e)) return
def get_session(db, usr, pwd): """Функция устанавливает соединение с ТД и возвращает сессию""" if platform.system() == 'Windows': driver = 'Teradata' else: driver = 'Teradata Database ODBC Driver 16.20' udaExec = teradata.UdaExec(appName='DataLoad', version='0.1', logConsole=False) session = udaExec.connect( method='odbc', system=db, # Сервер ТД из файла username=usr, # Логин TD password=pwd, # Пароль TD driver=driver, charset='UTF8', autoCommit='True', USEREGIONALSETTINGS='N', transactionMode='TERADATA') return session
def checkTablePresence(str1): table_name = 'TABLE_' + str1 udaExec = teradata.UdaExec(appName="test", version="1.0", logConsole=False) with udaExec.connect(method="odbc", system=host, username=username, password=password, driver="Teradata") as connect: cursor = connect.cursor() sel_query = "select count(*) from dbc.tables where tablename = '" + table_name + "' and databasename = 'DEV_WRK'" create_query = "CREATE SET TABLE DEV_WRK." + table_name + ",FALLBACK ,NO BEFORE JOURNAL,NO AFTER " + "JOURNAL,CHECKSUM = DEFAULT,DEFAULT MERGEBLOCKRATIO(CUSTOMER_NAME VARCHAR(255) CHARACTER SET LATIN NOT" + " CASESPECIFIC NOT NULL,CUSTOMER_ID VARCHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT " + "NULL,CUSTOMER_OPEN_DATE DATE FORMAT 'YYYY/MM/DD' NOT NULL,LAST_CONSULTED_DATE DATE FORMAT " + "'YYYY/MM/DD',VACCINATION_TYPE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,Doctor_Consulted CHAR(255)" + " CHARACTER SET LATIN NOT CASESPECIFIC,State CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,Country" + " CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,Post_Code INTEGER,DATE_OF_BIRTH DATE FORMAT 'MM/DD/YYYY'," + "ACTIVE_CUSTOMER CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( CUSTOMER_ID,CUSTOMER_NAME );" cursor.execute(sel_query) # get all records records = cursor.fetchall() for row in records: if (row[0] == 0): print("table not present") #creating the required table cursor.execute(create_query) return table_name
def __init__(self, appConfigFile=None, system=None, username=None, password=None, external_dns=None): """Initialise appConfigFile = local udaexec.ini system = system to logon to username = user name password = password external_dbs = external DNS """ if "ODBCINI" not in os.environ: os.environ["ODBCINI"] = """/opt/teradata/client/ODBC_64/odbc.ini""" self.udaexec = teradata.UdaExec( appName="td_proto", version=1, appConfigFile= """C:/Users/CW171001.TD/PycharmProjects/robot_prototype/udaexec.ini""" ) self.session = None
def __init__(self): # logging.basicConfig(filename='tdloader.log', # filemode='a', # format='%(asctime)s,%(msecs)d %(name)s %(levelname)s %(message)s', # datefmt='%H:%M:%S', # level=logging.DEBUG) # os.system('config.py') # from config import username, password, tdip # username = str(username) # password = str(password) # tdip = str(tdip) # print("TDIP: {0}, Username: {1}, Passwrod: {2}".format(tdip, username, password)) #udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0", logConsole=False) #session = udaExec.connect(method="odbc", system=tdip,username=username, password=password) udaExec = teradata.UdaExec (appConfigFile="tdloader.ini") logger = logging.getLogger(__name__) session = udaExec.connect("${dataSourceName}") self.session = session self.log = logger
def get_conn(self): """ Returns a teradata connection object Optional parameters for using a custom DSN connection (instead of using a server alias from tnsnames.ora) The dsn (data source name) is the TNS entry (from the Teradata names server or tnsnames.ora file) or is a string like the one returned from makedsn(). :param dsn: the host address for the Teradata server :param service_name: the db_unique_name of the database that you are connecting to (CONNECT_DATA part of TNS) You can set these parameters in the extra fields of your connection as in ``{ "dsn":"some.host.address" , "service_name":"some.service.name" }`` """ conn = self.get_connection(self.teradata_conn_id) externalDsn = conn.extra_dejson.get('externalDsn', None) appn = conn.extra_dejson.get('appName', 'airflow') ver = conn.extra_dejson.get('version', '1.0') log = conn.extra_dejson.get('logging', False) teradata_log = logging.getLogger("teradata") teradata_log.addHandler(logging.NullHandler()) teradata_log.propagate = False conn = teradata.UdaExec(appName=appn, version=ver, logConsole=log, configureLogging=log, logFile=None, logLevel="ERROR", checkpointFile=False ).\ connect(method="odbc", externalDSN=externalDsn, system=conn.host, username=conn.login, password=conn.password, charset='UTF8', transactionMode='Teradata', ) return conn
def fetch_table_data(table_name): dsn = 'TDDB' # 'TDDEV', 'TDDB' udaExec = teradata.UdaExec(appName="tdPyInterface", version="1.0", logConsole=False, appConfigFile="tdPyInterface.ini") session = udaExec.connect(dsn) cols = [] # to store cols vals = [] # to store data table_data_dict = {} table_data_list = [] qry = "select * from " + table_name cursor = session.execute(qry) for row in cursor.description: cols.append(row[0]) for row in cursor: vals.append(str(row)) for item in vals: data = item[item.find('[') + 1:item.find(']')] # print("data = %s\n"% data) parts = data.split(',') # print("parts = %s\n"% parts) for x, y in zip(cols, parts): table_data_dict.update({x.strip(): y.strip()}) # print("table_data_dict = %s\n"% table_data_dict) table_data_list.append(table_data_dict.copy()) # need to append a copy, otherwise you are just adding references to the same dictionary over and over again # print("table_data_list = %s"% table_data_list) # print(table_data_list) return table_data_list
import teradata udaExec = teradata.UdaExec(appName="HelloWorld", version="1.0", logConsole=False) #session = udaExec.connect(method="odbc", system="tdprod", # username="******", password="******"); #for row in session.execute("SELECT GetQueryBand()"): # print(row)
import csv import json import teradata import boto3 as b3 import pandas as pd import sys import RekognitionInterface import os import boto3 import pprint udaExec = teradata.UdaExec(appName="hackathon", version=1, logConsole=True) con = udaExec.connect(method="odbc", system="tdmpp01cop1.teradatalabs.net", username="******", password="******") cursor = con.cursor() sql = """insert into hack10.image_results (driver_license, image_results) values (?, ?)""" img_list = "/qd0047/tl151006/hack/img/driver_imgs_list.csv" image_directory = "/qd0047/tl151006/hack/img" drivers_df = pd.read_csv(img_list) def getImageInfo(drivers_df, image_directory, images_list, index): drivers_list = [] filename = image_directory + "/train/" + drivers_df['classname'][ index] + "/" + drivers_df['img'][index] print(filename) if (not os.path.isfile(str(filename))):
# The unit tests in the UdaExecExecuteTest are execute once for each named # data source below. util.createTestCasePerDSN( UdaExecExecuteTest, unittest.TestCase, ("HTTP", "HTTPS", "ODBC")) if __name__ == '__main__': formatter = logging.Formatter( "%(asctime)s - %(name)s - %(levelname)s - %(message)s") sh = logging.StreamHandler(sys.stdout) sh.setFormatter(formatter) sh.setLevel(logging.INFO) root = logging.getLogger() root.setLevel(logging.INFO) root.addHandler(sh) configFiles = [os.path.join(os.path.dirname(__file__), 'udaexec.ini')] udaExec = teradata.UdaExec(configFiles=configFiles, configureLogging=False) udaExec.checkpoint() def runTest(testName): suite = unittest.TestSuite() suite.addTest(UdaExecExecuteTest_ODBC(testName)) # @UndefinedVariable # noqa suite.addTest(UdaExecExecuteTest_HTTPS(testName)) # @UndefinedVariable # noqa unittest.TextTestRunner().run(suite) if __name__ == '__main__': # runTest('testProcedure') unittest.main()
def testRunNumber(self): # Check that runNumber is incremented by 1. udaExec = teradata.UdaExec(configFiles=configFiles, configureLogging=False) self.assertEqual(int(udaExec.runNumber.split("-")[1]), int(self.udaExec.runNumber.split("-")[1]) + 1)
def testMissingAppName(self): with self.assertRaises(teradata.InterfaceError) as cm: teradata.UdaExec(configFiles=[], configureLogging=False) self.assertEqual(cm.exception.code, teradata.CONFIG_ERROR)