def Connect ( self, hostname, port, username, password ):
self.hostname = hostname
#Use SINGLE DB by default
self.port = 30015 + int(port)*100
self.username = username
self.password = password
if self.isConnected:
self.conn.close()
self.isConnected = False
try:
self.conn = dbapi.connect(self.hostname, self.port, self.username, self.password)
except:
#Try MDC mode
self.port = 30013 + int(port)*100
#print self.port
try:
self.conn = dbapi.connect(self.hostname, self.port, self.username, self.password)
except:
self.isConnected = False
#print 'Connection failure'
if self.conn:
#print 'Connected to HANA'
self.isConnected = True
self.curs = self.conn.cursor()
def unauth_ss_delete():
output = 'Python UnAuthorized SecureStore Delete. \n'
schema = hana.credentials['schema']
host = hana.credentials['host']
port = hana.credentials['port']
user = hana.credentials['user']
password = hana.credentials['password']
# The certificate will available for HANA service instances
# that require an encrypted connection
# Note: This was tested to work with python hdbcli-2.3.112 tar.gz package
# not hdbcli-2.3.14 provided in XS_PYTHON00_0-70003433.ZIP
if 'certificate' in hana.credentials:
haascert = hana.credentials['certificate']
output += 'schema: ' + schema + '\n'
output += 'host: ' + host + '\n'
output += 'port: ' + port + '\n'
output += 'user: '******'\n'
output += 'pass: '******'\n'
# Connect to the python HANA DB driver using the connection info
# User for HANA as a Service instances
if 'certificate' in hana.credentials:
connection = dbapi.connect(address=host,
port=int(port),
user=user,
password=password,
currentSchema=schema,
encrypt="true",
sslValidateCertificate="true",
sslCryptoProvider="openssl",
sslTrustStore=haascert)
else:
connection = dbapi.connect(address=host,
port=int(port),
user=user,
password=password,
currentSchema=schema)
# Prep a cursor for SQL execution
cursor = connection.cursor()
# Form an SQL statement
cursor.callproc("SYS.USER_SECURESTORE_DELETE",
("TestStoreName", False, "TestKey"))
# Close the DB connection
connection.close()
output += 'key TestKey was deleted from store TestStoreName.\n'
# Return the results
return Response(
output,
mimetype='text/plain',
status=200,
)
def _cursor(self):
settings_dict = self.settings_dict
if self.connection is None:
if not settings_dict['NAME']:
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured(
"settings.DATABASES is improperly configured. "
"Please supply the NAME value.")
conn_params = {
'database': settings_dict['NAME'],
}
conn_params.update(settings_dict['OPTIONS'])
if 'autocommit' in conn_params:
del conn_params['autocommit']
if settings_dict['USER']:
conn_params['user'] = settings_dict['USER']
if settings_dict['PASSWORD']:
conn_params['password'] = settings_dict['PASSWORD']
if settings_dict['HOST']:
conn_params['host'] = settings_dict['HOST']
if settings_dict['PORT']:
conn_params['port'] = settings_dict['PORT']
self.connection = Database.connect(address=conn_params['host'],port=int(conn_params['port']),user=conn_params['user'],password=conn_params['password'])
self.connection.setautocommit(auto=True)
self.default_schema=settings_dict['NAME']
# make it upper case
self.default_schema=self.default_schema.upper()
cursor = self.connection.cursor()
self.create_or_set_default_schema(cursor)
return cursor
def get_workshops(user_id,db) :
conn = dbapi.connect(address=db['host'],port=db['port'],user=db['user'],password=db['pwd'],encrypt=True, sslValidateCertificate=False )
sql = "SELECT ID,TITLE,WORKSHOP_START,REGISTRATION_START,REGISTRATION_END,MAX_USER, INFO, URL, MODERATOR "\
"FROM DIREGISTER.WORKSHOPS"
if not (user_id == 'wradmin' or user_id == 'anonymous') :
sql = sql + " WHERE MODERATOR=\'{}\'".format(user_id)
sql = sql + ';'
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
conn.close()
# return result as
# * list of dictionaries or
# * list of 1 dictionary with empty values
if len(rows) > 0 :
srows = sorted(rows, key=lambda k: k[0])
wss = {r[0]:{'ID':r[0],'Title':r[1],'Moderator':r[8],'Workshop Start':r[2],'Regist. Start':r[3],'Regist. End':r[4],'Max. User':r[5],\
'Info':r[6],'url':r[7]} for r in srows}
ws_titles = [(e['ID'], '{} - {} - {}'.format(e['ID'], e['Title'], e['Workshop Start'])) for k,e in wss.items()]
else :
wss = {' ':{'ID':"",'Title':"",'Moderator':"",'Workshop Start':"",'Regist. Start':"",'Regist. End':"",'Max. User':"",\
'Info':"",'url':""}}
ws_titles = []
return wss, ws_titles
def get_external_data(self):
ext_fields = [
"ItemCode", "ItemName", "FrgnName", "CodeBars", "OnHand",
"AvgPrice", "StockValue", "LastPurDat", "SHeight1", "SWidth1",
"SLength1", "SVolume", "SWeight1"
]
settings = self.env['lighting.portal.connector.settings'].sudo(
).search([]).sorted(lambda x: x.sequence)
from hdbcli import dbapi
conn = dbapi.connect(settings['host'], settings['port'],
settings['username'], settings['password'])
cursor = conn.cursor()
stmnt = """SELECT %s
FROM %s.OITM p
WHERE p."ItemCode" = ?""" % (', '.join(
['p."%s"' % x for x in ext_fields]), settings['schema'])
cursor.execute(stmnt, self.reference)
header = [x[0] for x in cursor.description]
result = cursor.fetchone()
if result is not None:
result_d = dict(zip(header, result))
#TODO deal with more than one occurrence
for field in ext_fields:
setattr(self, 'ext_%s' % field.lower(), result_d[field])
cursor.close()
conn.close()
def connect(self, host, port=30015, **kwargs):
"""
Connect to the SAP HANA database
# TODO: Add option to connect using the key
# TODO: Add encryption options
Args:
host (str): Host where the database is running
port (int): Database port (3{inst_number}15 by default)
user (str): Existing username in the database
password (str): User password
"""
self._logger.info('connecting to SAP HANA database at %s:%s' %
(host, port))
try:
self._connection = dbapi.connect(
address=host,
port=port,
user=kwargs.get('user'),
password=kwargs.get('password'),
)
except dbapi.Error as err:
raise base_connector.ConnectionError(
'connection failed: {}'.format(err))
self._logger.info('connected succesfully')
def domath():
logger = logging.getLogger('route.logger')
logger.info('Someone accessed us')
average = 0
jokecount = 0
if 'authorization' not in request.headers:
abort(403)
access_token = request.headers.get('authorization')[7:]
security_context = xssec.create_security_context(access_token, uaa_service)
isAuthorized = security_context.check_scope('$XSAPPNAME.access')
if not isAuthorized:
abort(403)
logger.info('Authorization successful')
conn = dbapi.connect(address=hana.credentials['host'], port=int(hana.credentials['port']), user=hana.credentials['user'], password=hana.credentials['password'],CURRENTSCHEMA=hana.credentials['schema'])
cursor = conn.cursor()
try:
cursor.execute("SELECT REVIEW FROM DB_JOKES")
result = [i[0] for i in cursor.fetchall()]
average = round(s.mean(result),1)
jokecount = len(result)
cursor.close()
conn.close()
return jsonify(reviews=average,resultcount=jokecount)
except:
logger.error('DB read error')
return jsonify(reviews=0,resultcount=0)
def main(_):
connection = dbapi.connect(address=args.hxehost, port=args.hxeport, user=args.hxeusr, password=args.hxepwd)
cursor = connection.cursor()
# Create a TF_DATA schema
try:
cursor.execute('CREATE SCHEMA TF_DATA;')
except (RuntimeError, TypeError, NameError, dbapi.Error):
pass
# Set the current schema to TF_DATA schema
cursor.execute('SET SCHEMA TF_DATA;')
# Drop the table before creating them
try:
cursor.execute('DROP TABLE TF_DATA.IRIS_DATA;')
except (RuntimeError, TypeError, NameError, dbapi.Error):
pass
# Create the tables
cursor.execute('CREATE TABLE TF_DATA.IRIS_DATA (ID INTEGER, SEPALLENGTH FLOAT, SEPALWIDTH FLOAT, PETALLENGTH FLOAT, PETALWIDTH FLOAT, SPECIES INT);')
query_iris = 'INSERT INTO TF_DATA.IRIS_DATA (ID, SEPALLENGTH, SEPALWIDTH, PETALLENGTH, PETALWIDTH, SPECIES) VALUES (?,?,?,?,?,?)'
# Load the IRIS data in the table
imported_row = 0
(train_x, train_y), (test_x, test_y) = iris_data.load_data()
for index, row in test_x.iterrows():
cursor.execute(query_iris, (index, row["SepalLength"], row["SepalWidth"], row["PetalLength"], row["PetalWidth"], test_y[index]))
imported_row +=1
print("Import row(s) " + str(imported_row))
cursor.close()
connection.close()
def setUp(self):
try:
self.connection = dbapi.connect('dubl60244901a.dhcp.dub.sap.corp',
30115, 'SYSTEM', 'Manager01')
self.connection.setautocommit(auto=True)
except dbapi.Error, error:
print error
def connect(self, host, port=30015, **kwargs):
"""
Connect to the SAP HANA database
# TODO: Add option to connect using the key
# TODO: Add encryption options
Args:
host (str): Host where the database is running
port (int): Database port (3{inst_number}15 by default)
user (str): Existing username in the database
password (str): User password
properties : Additional properties can be used with named parameters. More info at:
https://help.sap.com/viewer/0eec0d68141541d1b07893a39944924e/2.0.02/en-US/ee592e89dcce4480a99571a4ae7a702f.html
Example:
To avoid automatic reconnection set RECONNECT='FALSE' as parameter
"""
self._logger.info('connecting to SAP HANA database at %s:%s', host,
port)
self.__properties = kwargs
try:
self._connection = dbapi.connect(
address=host,
port=port,
#user=kwargs.get('user'),
#password=kwargs.get('password'),
**self.__properties)
except dbapi.Error as err:
raise base_connector.ConnectionError(
'connection failed: {}'.format(err))
self._logger.info('connected successfully')
def main(_):
channel = implementations.insecure_channel(args.tmshost, args.tmsport)
request = predict_pb2.PredictRequest()
request.model_spec.name = 'flowers'
request.model_spec.signature_name = 'serving_default'
stub = prediction_service_pb2.beta_create_PredictionService_stub(channel)
connection = dbapi.connect(address=args.hxehost, port=args.hxeport, user=args.hxeusr, password=args.hxepwd)
cursor_flowers = connection.cursor()
cursor_flowers_image = connection.cursor()
query_flowers = 'SELECT IMAGE_ID, IMAGE_LABEL, IMAGE_FILE_PATH, IMAGE_FILE_NAME, IMAGE_RAW_DATA FROM TF_DATA.FLOWERS_DATA'
query_flowers_image = 'SELECT IMAGE_RAW_DATA FROM TF_DATA.FLOWERS_DATA WHERE IMAGE_ID = :id'
cursor_flowers.execute(query_flowers)
results = cursor_flowers.fetchall()
for row in results:
image_id = row['IMAGE_ID']
image_label = row['IMAGE_LABEL']
image_path = row['IMAGE_FILE_PATH']
image_file = row['IMAGE_FILE_NAME']
image_data = bytes(row['IMAGE_RAW_DATA'])
request.inputs['image_blob'].CopyFrom(tf.contrib.util.make_tensor_proto(image_data, shape=[1]))
response = stub.Predict(request, 100)
predicted_class_name = response.outputs['class'].string_val[0]
predicted_class_probability = response.outputs['probability'].float_val[0]
print("Expected class: " + image_label.ljust(10) + " -> Predicted class: " + predicted_class_name.ljust(10) + " with probability: " + str(round(predicted_class_probability * 100, 2) ) + "% (image file: " + image_file + ")")
cursor_flowers.close()
connection.close()
def upload2db(sql, data,db) :
conn = dbapi.connect(address=db['host'], port=db['port'], user=db['user'], password=db['pwd'], encrypt=True,
sslValidateCertificate=False)
cursor = conn.cursor()
if db['batchsize'] == 0 :
print('Uploading: {}'.format(len(data)))
cursor.executemany(sql, data)
elif db['batchsize'] == 1 :
print('Uploading each record separately: {}'.format(len(data)))
for i,rec in enumerate(data) :
if db['info_batchsize']> 0 and i%db['info_batchsize'] == 0 :
print('Uploaded: {}/{}'.format(i,len(data)))
try :
cursor.execute(sql, rec)
except dbapi.DataError as de :
logging.warning('Data Error: line {} ({})'.format(i,de))
logging.warning('Record: {}'.format(rec))
else:
print('Uploading in batches: {} of batch size: {} (#{})'.format(len(data),db['batchsize'],int(len(data)/db['batchsize'])+1))
for i in range(0,len(data),db['batchsize']) :
print('Uploaded: {}/{} - Uploading: {}'.format(i,len(data),len(data[i:i+db['batchsize']])))
cursor.executemany(sql, data[i:i + db['batchsize']])
cursor.close()
conn.close()
def connect(self):
if not self.settings_dict['NAME']:
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured(
"settings.DATABASES is improperly configured. "
"Please supply the NAME value.")
conn_params = {}
if self.settings_dict['USER']:
conn_params['user'] = self.settings_dict['USER']
if self.settings_dict['PASSWORD']:
conn_params['password'] = self.settings_dict['PASSWORD']
if self.settings_dict['HOST']:
conn_params['host'] = self.settings_dict['HOST']
if self.settings_dict['PORT']:
conn_params['port'] = self.settings_dict['PORT']
self.connection = Database.connect(address=conn_params['host'],
port=int(conn_params['port']),
user=conn_params['user'],
password=conn_params['password'])
# set autocommit on by default
self.connection.setautocommit(auto=True)
self.default_schema = self.settings_dict['NAME']
# make it upper case
self.default_schema = self.default_schema.upper()
self.create_or_set_default_schema()
def connectDB(serviceName):
service = env.get_service(name=serviceName)
conn = dbapi.connect(address=service.credentials['host'],
port= int(service.credentials['port']),
user = service.credentials['user'],
password = service.credentials['password'],
CURRENTSCHEMA=service.credentials['schema'])
return conn
def connectToHana():
print("Try to connect to hana: ")
conn = dbapi.connect(address="localhost",
port=30015,
user="******",
password="******")
print('Connected', conn.isconnected())
return conn
def remove_event(ws_id,db) :
conn = dbapi.connect(address=db['host'],port=db['port'],user=db['user'],password=db['pwd'], encrypt=True,
sslValidateCertificate=False)
sql_command = "DELETE FROM DIREGISTER.WORKSHOPS WHERE ID = \'{}\';".format(ws_id)
cursor = conn.cursor()
cursor.execute(sql_command)
cursor.close()
conn.close()
def register_moderator(username,pwdhash,db) :
conn = dbapi.connect(address=db['host'],port=db['port'],user=db['user'],password=db['pwd'],encrypt=True, sslValidateCertificate=False )
sql_command = "INSERT INTO DIREGISTER.MODERATORS (USERNAME, PWDHASH) VALUES (\'{}\',\'{}\');".format(username,pwdhash)
cursor = conn.cursor()
cursor.execute(sql_command)
cursor.close()
conn.close()
def getDbConnection(self):
conn = dbapi.connect(
address='hanadb',
port=30015,
user='******',
password='******',
)
return conn
def __get_connection():
conn = dbapi.connect(
address="cdab230a-29d1-4a66-ab93-eb0ec219ed8b.hana.trial-us10.hanacloud.ondemand.com",
port="443",
encrypt="true",
user="******",
password='******'
)
return conn
def main(_):
max_species_len = len(max(iris_data.SPECIES, key=len))
channel = implementations.insecure_channel(args.tmshost, args.tmsport)
request = predict_pb2.PredictRequest()
request.model_spec.name = 'iris'
request.model_spec.signature_name = 'serving_default'
stub = prediction_service_pb2_grpc.PredictionServiceStub(channel._channel)
connection = dbapi.connect(address=args.hxehost,
port=args.hxeport,
user=args.hxeusr,
password=args.hxepwd)
cursor = connection.cursor()
query_iris = 'SELECT ID, SEPALLENGTH, SEPALWIDTH, PETALLENGTH, PETALWIDTH, SPECIES FROM TF_DATA.IRIS_DATA'
cursor.execute(query_iris)
results = cursor.fetchall()
for row in results:
id = row["ID"]
PetalLength = row["PETALLENGTH"]
PetalWidth = row["PETALWIDTH"]
SepalLength = row["SEPALLENGTH"]
SepalWidth = row["SEPALWIDTH"]
label = row["SPECIES"]
request.inputs['PetalLength'].CopyFrom(
tf.contrib.util.make_tensor_proto(PetalLength, shape=[1]))
request.inputs['PetalWidth'].CopyFrom(
tf.contrib.util.make_tensor_proto(PetalWidth, shape=[1]))
request.inputs['SepalLength'].CopyFrom(
tf.contrib.util.make_tensor_proto(SepalLength, shape=[1]))
request.inputs['SepalWidth'].CopyFrom(
tf.contrib.util.make_tensor_proto(SepalWidth, shape=[1]))
response = stub.Predict(request, 100)
# in case you are using SPS02 or prior, the int64 output is not supported
if (len(response.outputs['predicted_class_id'].int64_val) > 0):
predicted_class_id = response.outputs[
'predicted_class_id'].int64_val[0]
else:
predicted_class_id = int(
response.outputs['predicted_class_id'].float_val[0])
predicted_class_name = iris_data.SPECIES[predicted_class_id]
predicted_class_probability = response.outputs[
'probabilities'].float_val[int(predicted_class_id)]
print("Expected class: " +
iris_data.SPECIES[label].ljust(max_species_len) +
" -> Predicted class: " +
predicted_class_name.ljust(max_species_len) +
" with probability: " + str(predicted_class_probability))
cursor.close()
connection.close()
def connect(self):
connection_config = default_info_hana
self._connection = hana_driver.connect(
address=connection_config['host'],
port=connection_config['port'],
user=connection_config['user'],
password=connection_config['password'],
)
self._cursor = self._connection.cursor()
def get_rows(schemaname, tablename):
# Initialize your connection
tablename = tablename.replace('|', '/')
query = "SELECT * FROM " + schemaname + "." + tablename
logger.info(query)
try:
conn = dbapi.connect(address=HANA_IP,
port=HANA_PORT,
user=HANA_USER,
password=HANA_PASS)
logger.info("Connected to Hana")
except:
logger.info("Couldn't connect to Hana")
return Response(status=403)
def emit_rows(connection, querystring):
logger.info("Running query {}".format(querystring))
yield "["
### rest of your code here ###
cursor = connection.cursor()
cursor.execute(querystring)
column_names = []
# get property names. cursor.description is tuples with the names and column parameters, we just need column name
for i in range(len(cursor.description)):
desc = cursor.description[i]
column_names.append("{}".format(desc[0]))
is_first = True
for result in cursor:
if (is_first):
is_first = False
else:
yield ","
entity = {}
for i in range(len(result)):
entity[column_names[i]] = "{}".format(result[i])
yield json.dumps(entity)
cursor.close()
yield "]"
try:
return Response(emit_rows(conn, query),
status=200,
mimetype='application/json')
except dbapi.Error as exc:
logger.error("Error from Hana: %s", exc)
return Response(status=500)
def get_userlist(event,db) :
conn = dbapi.connect(address=db['host'],port=db['port'],user=db['user'],password=db['pwd'],encrypt=True, sslValidateCertificate=False )
sql_command = "select USER, PWD, USERNAME, REGISTRATION_DATE, BUFFER_USER from DIREGISTER.USERS WHERE WORKSHOP_ID = \'{}\';".format(event)
cursor = conn.cursor()
cursor.execute(sql_command)
rows = cursor.fetchall()
cursor.close()
conn.close()
userlist = [{'User': r[0], 'Password': r[1], 'Name': r[2], 'Regist.Time': r[3], 'Buffer User':r[4]} for r in rows]
return userlist
def main(_):
connection = dbapi.connect(address=args.hxehost,
port=args.hxeport,
user=args.hxeusr,
password=args.hxepwd)
connection.setautocommit(False)
cursor = connection.cursor()
# Create a TF_DATA schema
try:
cursor.execute('CREATE SCHEMA TF_DATA;')
except (RuntimeError, TypeError, NameError, dbapi.Error):
pass
# Set the current schema to TF_DATA schema
cursor.execute('SET SCHEMA TF_DATA;')
# Drop the table before creating them
try:
cursor.execute('DROP TABLE TF_DATA.FLOWERS_DATA;')
except (RuntimeError, TypeError, NameError, dbapi.Error):
pass
# Create the tables
cursor.execute(
'CREATE TABLE TF_DATA.FLOWERS_DATA (IMAGE_ID INTEGER, IMAGE_LABEL VARCHAR(50), IMAGE_FILE_PATH VARCHAR(255), IMAGE_FILE_NAME VARCHAR(255), IMAGE_RAW_DATA BLOB);'
)
query_flowers = 'INSERT INTO TF_DATA.FLOWERS_DATA (IMAGE_ID, IMAGE_LABEL, IMAGE_FILE_PATH, IMAGE_FILE_NAME, IMAGE_RAW_DATA) VALUES (:id, :label,:path,:file,:raw_data)'
image_lists = create_image_lists(args.image_dir)
imported_row = 0
for image_label in image_lists.keys():
image_file_list = image_lists[image_label]
image_file_dir = image_file_list["dir"]
for image_file in image_file_list["files"]:
with open(image_file_dir + "/" + image_file, 'rb') as f:
imported_row += 1
jpeg_data = f.read()
img_blob = dbapi.LOB()
cursor.execute(
query_flowers, {
"id": imported_row,
"label": image_label,
"path": image_file_dir,
"file": image_file,
"raw_data": img_blob
})
img_blob.write(data=jpeg_data)
img_blob.close()
connection.commit()
print("Import row(s) " + str(imported_row))
cursor.close()
connection.close()
def execute_sql1():
con = dbapi.connect('10.78,113.19', 30015, 'I057588', 'Jan2015#')
cur = con.cursor()
try:
ret = cur.execute(
'SELECT TOP 1000 * FROM "I4AA"."MEASUREMENT_READING"')
ret = cur.fetchall()
for row in ret:
print row
except dbapi.Error, err:
print err
def hana_conn():
#address='fced79e4-d9ec-4819-a6bc-723570ac80b7.hana.trial-us10.hanacloud.ondemand.com'
address='2cde67eb-e7c5-4de4-975b-6170c3b75764.hana.trial-us10.hanacloud.ondemand.com'
port=int('443')
user='******'
#password='******'
password='******'
conn = dbapi.connect(address, port, user, password)
return conn
def createConnection(uri):
ds_uri = QgsDataSourceUri(uri)
conn = dbapi.connect(address=ds_uri.host(),
port=ds_uri.port(),
user=ds_uri.username(),
password=ds_uri.password(),
ENCRYPT=True,
sslValidateCertificate=False,
CHAR_AS_UTF8=1)
conn.setautocommit(False)
return conn
def execute_sql(sql=None):
con = dbapi.connect('dubl60244901a.dhcp.dub.sap.corp', 30115, 'SYSTEM',
'Manager01')
cur = con.cursor()
try:
ret = cur.execute("SELECT CURRENT_TIMESTAMP FROM DUMMY")
ret = cur.fetchall()
for row in ret:
print row
except dbapi.Error, err:
print err
def get_moderator(username,db) :
conn = dbapi.connect(address=db['host'],port=db['port'],user=db['user'],password=db['pwd'],encrypt=True, sslValidateCertificate=False )
sql_command = "select USERNAME, PWDHASH from DIREGISTER.MODERATORS WHERE USERNAME = \'{}\';".format(username)
cursor = conn.cursor()
cursor.execute(sql_command)
row = cursor.fetchone()
cursor.close()
conn.close()
if not row:
return [None,None]
else :
return row[0], row[1]
def get_conn(hana):
schema = hana.credentials['schema']
host = hana.credentials['host']
port = hana.credentials['port']
user = hana.credentials['user']
password = hana.credentials['password']
# The certificate will available for HANA service instances that require an encrypted connection
# Note: This was tested to work with python hdbcli-2.3.112 tar.gz package not hdbcli-2.3.14 provided in XS_PYTHON00_0-70003433.ZIP
if 'certificate' in hana.credentials:
haascert = hana.credentials['certificate']
#output += 'schema: ' + schema + '<br >\n'
#output += 'host: ' + host + '<br >\n'
#output += 'port: ' + port + '<br >\n'
#output += 'user: '******'<br >\n'
#output += 'pass: '******'<br >\n'
# # Connect to the python HANA DB driver using the connection info
# User for HANA as a Service instances
if 'certificate' in hana.credentials:
connection = dbapi.connect(address=host,
port=int(port),
user=user,
password=password,
currentSchema=schema,
encrypt="true",
sslValidateCertificate="true",
sslCryptoProvider="openssl",
sslTrustStore=haascert)
else:
connection = dbapi.connect(address=host,
port=int(port),
user=user,
password=password,
currentSchema=schema)
return connection
class SecureStoreService:
def __init__(self, storeName = 'TestStoreName'):
env = AppEnv()
port = int(os.getenv("PORT", 9099))
hana = env.get_service(label='hana')
self.__connection = None
self.__store = storeName
self.__hana['schema'] = hana.credentials['schema']
self.__hana['host'] = hana.credentials['host']
self.__hana['port'] = int(hana.credentials['port'])
self.__hana['user'] = hana.credentials['user']
self.__hana['password'] = hana.credentials['password']
# Certificate for SAP HANA service instances that require an encrypted connection
if 'certificate' in hana.credentials:
self.__hana['haascert'] = hana.credentials['certificate']
self.__connection = dbapi.connect(
address=self.__hana['host'],
port=self.__hana['port'],
user=self.__hana['user'],
password=self.__hana['password'],
currentSchema=self.__hana['schema'],
encrypt="true",
sslValidateCertificate="true",
sslCryptoProvider="openssl",
sslTrustStore=self.__hana['haascert']
)
else:
self.__connection = dbapi.connect(
address=self.__hana['host'],
port=self.__hana['port'],
user=self.__hana['user'],
password=self.__hana['password'],
currentSchema=self.__hana['schema']
)
assert(self__connection is not None)
def save_event(record,user_id,db) :
conn = dbapi.connect(address=db['host'],port=db['port'],user=db['user'],password=db['pwd'], encrypt=True,
sslValidateCertificate=False)
sql_command = "UPSERT DIREGISTER.WORKSHOPS (\"ID\",\"TITLE\",\"MAX_USER\",\"URL\",\"WORKSHOP_START\",\"REGISTRATION_START\","\
"\"REGISTRATION_END\",\"INFO\",\"MODERATOR\") VALUES" \
"(\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\') WITH PRIMARY KEY;"\
.format(record['id'],record['title'],record['max_user'],record['url'],record['workshop_start'],record['registration_start'],\
record['registration_end'],record['info'],user_id)
cursor = conn.cursor()
cursor.execute(sql_command)
cursor.close()
conn.close()
return
def getconn(self):
"""Convenient method to get connection"""
#hdbport = int('3%s15' % Settings.hdbinstancenum)
con = dbapi.connect(address = self.host, \
port = self.port, \
user = self.username, \
password = self.password, \
autocommit = True)
if self.schema:
cur = con.cursor()
try:
cur.execute('ALTER SESSION SET CURRENT_SCHEMA = %s' % self.schema)
return con
except dbapi.Error, err:
cur.close()
con.close()
cur = None
raise err
finally:
def connect(self):
if not self.settings_dict['NAME']:
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured(
"settings.DATABASES is improperly configured. "
"Please supply the NAME value.")
conn_params = {}
if self.settings_dict['USER']:
conn_params['user'] = self.settings_dict['USER']
if self.settings_dict['PASSWORD']:
conn_params['password'] = self.settings_dict['PASSWORD']
if self.settings_dict['HOST']:
conn_params['host'] = self.settings_dict['HOST']
if self.settings_dict['PORT']:
conn_params['port'] = self.settings_dict['PORT']
self.connection = Database.connect(address=conn_params['host'],port=int(conn_params['port']),user=conn_params['user'],password=conn_params['password'])
# set autocommit on by default
self.connection.setautocommit(auto=True)
self.default_schema=self.settings_dict['NAME']
# make it upper case
self.default_schema=self.default_schema.upper()
'''
Created on Jun 27, 2015
@author: i056936
'''
from hdbcli import dbapi
if __name__ == '__main__':
print "SmartStock Hello World!"
host = "lu187256"
port = 30215
conn = dbapi.connect(host, port, "system", "manager")
if conn.isconnected():
cursor = conn.cursor()
else:
print "connection failed!"
pass
def getDbConnection(self): conn = dbapi.connect(address='hanadb', port=30015, user='******', password='******', ) return conn
def __init__(self, p_db):
db = p_db
s = ""
self.db = None
self.cursor = None
(self.dbtype, driver, hostname, port, dbname, user, pwd) = getDbStringDetails(db)
if (self.dbtype in ["teradata", "maxdb"]) or (driver == "-odbc"):
dsn = self.odbc(hostname, port, dbname)
print dsn
self.db = pyodbc.connect(dsn=dsn, user=user, password=pwd, ansi=True, autocommit=True)
self.cursor=self.db.cursor()
elif self.dbtype == "ase":
# hostname defined in interface file
self.db = Sybase.connect(dsn=hostname, user=user, passwd=pwd, database=dbname, auto_commit=True)
self.cursor = self.db.cursor()
self.cursor.execute("set quoted_identifier on")
elif self.dbtype in ["asa", "iq"]:
import sqlanydb # Module for Sybase ASA or IQ
s = "%s" % (hostname)
print s
self.db = sqlanydb.connect(eng=s, userid=user, password=pwd, dbn=dbname)
self.cursor = self.db.cursor()
elif self.dbtype == "mssql":
s = "%s:%s" % (hostname, port)
self.db = pymssql.connect(host=s, user=user, password=pwd, database=dbname, as_dict=False)
self.cursor = self.db.cursor()
elif self.dbtype == "mysql":
self.db = MySQLdb.connect (host=hostname, port=int(port), user = user, passwd = pwd, db=dbname)
self.cursor = self.db.cursor()
elif self.dbtype == "db2":
self.db = DB2.connect (dsn=dbname, uid=user, pwd=pwd)
self.cursor = self.db.cursor()
elif self.dbtype in ["postgres", "greenplum"]:
s = "host='%s' port='%s' user='******' password='******' dbname='%s'" % (hostname, port, user, pwd, dbname)
self.db = psycopg2.connect (s)
self.db.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
self.cursor = self.db.cursor()
elif self.dbtype == "oracle":
s = "%s/%s@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%s)(PORT=%s))(CONNECT_DATA=(SERVICE_NAME=%s)))"
s = s % (user, pwd, hostname, port, dbname)
self.db = cx_Oracle.connect(s)
self.cursor = self.db.cursor()
elif self.dbtype == "netezza":
# conn="DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; PORT=3306; DATABASE=mysql; UID=joe;
# PASSWORD=bloggs; OPTION=3;SOCKET=/var/run/mysqld/mysqld.sock;"
self.cursor = Connect (hostname, user, pwd)
elif self.dbtype in ["hana"]:
from hdbcli import dbapi
self.db = dbapi.connect(address=hostname, port=30015+int(port), user=user, password=pwd, autocommit=True)
self.cursor = self.db.cursor()
elif self.dbtype in ["progress"]:
dsn = self.odbc(hostname, port, dbname)
self.db = pyodbc.connect(dsn=dsn, user=user, password=pwd, autocommit=True)
self.cursor=self.db.cursor()
elif self.dbtype in ["ingres", "vectorwise"]:
# vnode = @host,protocol,port[;attribute=value{;attribute=value}][[user,password]]
s = "@%s,tcp_ip,%s;connection_type=direct" % (hostname, port)
self.db=ingresdbi.connect(database=dbname, vnode=s, uid=user, pwd=pwd, dbms_pwd=pwd, autocommit = "Y") # trace = (7, "dbi.log")
self.cursor=self.db.cursor()
else:
perror("Unknown_db_type", self.dbtype)
