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)
