def valida_user(UserName, Password):
#consultar parametros para conectarse a la BD
(Motor, IP_Server, userBD, passBD, BD) = Functions.get_json_param('./encriptado1.json') #funcion q retorna los datos de conexion
puerto = IP_Server[(IP_Server.find(':')+1):]
ip = IP_Server[0:IP_Server.find(':')]
if Motor == "HANA":
#rutina de reintento de conexión
for x in range(5):
time.sleep(1)
try:
connection = pyhdb.connect(ip, puerto, userBD, passBD)
except AttributeError:
pass
if connection.isconnected():
cursor = connection.cursor()
cursor.execute("SELECT " + "\"Password\"" + " from \""+ BD + "\".OCRD where " + "\"CardCode\"" + " = '" + UserName + "';")
result = cursor.fetchone()
for l in result:
print('conexion OK')
password = l
break
connection.close()
if connection.isconnected()== False:
print('sin conexión')
if (Functions.desencriptar(llave, password)) == Password:
return True
else:
return False
def test_con(self):
if self.HDB_pwd + self.HDB_account + self.HDB_PORT +self.HDB_IP == '':
self.HDB_IP = self.ipline.text()
self.HDB_PORT = self.portline.text()
self.HDB_account = self.actline.text()
self.HDB_pwd = self.pwdline.text()
if self.char_legal('IP',self.HDB_IP) & self.char_legal('PORT',self.HDB_PORT):
try:
dbcon = pyhdb.connect(
host = self.HDB_IP,
port = self.HDB_PORT,
user = self.HDB_account,
password = self.HDB_pwd
)
except:
error_msg = "连接数据库发生意外"
QMessageBox.information(self,"errer_message",error_msg)
return False
else:
dbcon.close()
self.save_conf()
error_msg = "成功连接数据库"
self.SaveConButton.show()
self.ConfirmButton.show()
QMessageBox.information(self,"errer_message",error_msg)
return True
else:
QMessageBox.information(self,"errer_message","连接字符串有误")
return False
def connect_hana(self,da_path):
#Building the file path to access config file
DataAnalyticsShare_path = da_path.split("\\")
DataAnalyticsShare_path.append('PythonScripts')
DataAnalyticsShare_path.append('G2PLANET_CONFIG.cfg')
DataAnalyticsShare_path = os.path.join('\\\\',*DataAnalyticsShare_path)
#Reading the config file
cf = cp.ConfigParser()
cf.read(DataAnalyticsShare_path)
hana_server=cf.get('HANA_ENV','server')#'YmloYW5hZGV2LmNvcnAuc2VydmljZS1ub3cuY29t'#
hana_server = str(base64.b64decode(hana_server),'utf-8')
print(hana_server)
port=cf.get('HANA_ENV','port')#'MzAxMTU='#
port = int(base64.b64decode(port))
user_name=cf.get('HANA_ENV','user_name')#'U1ZDX0VCSV9EU0Q='#
user_name = str(base64.b64decode(user_name),'utf-8')
print(user_name)
pwd=cf.get('HANA_ENV','pwd')#'U2VydmljZW5vdzEyMyM='#
pwd = str(base64.b64decode(pwd),'utf-8')
try: #Connecting to HANA
con = pyhdb.connect(hana_server, port, user_name,pwd)
cur = con.cursor()
#print('Connection to HANA DB successful')
return con,cur
except pyhdb.DatabaseError as e:
#print ("Database connection error",e)
raise
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 = pyhdb.connect(
host=host,
port=port,
user=kwargs.get('user'),
password=kwargs.get('password'),
)
except socket.error as err:
raise base_connector.ConnectionError(
'connection failed: {}'.format(err))
self._logger.info('connected successfully')
def __init__(self, host, port, user, password):
self._connection = pyhdb.connect(
host=host,
port=port,
user=user,
password=password
)
def connect(self, database_settings: common.DatabaseSettings):
# +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# @title Creates a connection with a database
# @author Sanne Korzec (Alliander)
# @param username username of your database account
# @param password password of your database account
# @param database database that you want to use currently only possible to HANA
# @param server Server of the database that you want to use options are HANA: WHA/DHA/KHA/PHA
# @param class_path Optional path to the class_path for the database HANA: ngdbc.jar file
# @return A database connection
# +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# First check if the database is right
if database_settings.database not in ["HANA"]:
raise Exception(
"Do not recognize the database string options are: HANA. You selected:",
database_settings.database)
# Check if the server is implemented and construct the connection string
database_settings.connection_type = "direct"
self._set_connection_string(database_settings)
logging.getLogger(__name__).debug(self.host)
logging.getLogger(__name__).debug(self.port)
# Connect and create the connection
self.connection = pyhdb.connect(host=self.host,
port=self.port,
user=database_settings.username,
password=database_settings.password)
self.cursor = self.connection.cursor()
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(
host=conn_params['host'],
port=int(conn_params['port']),
user=conn_params['user'],
password=conn_params['password']
)
# set autocommit on by default
self.set_autocommit(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 get_result_set(self):
self.get_conf()
str_sql_prefix = "SELECT DISTINCT TABLE_NAME, COLUMN_NAME, POSITION, DATA_TYPE_NAME, LENGTH, SCALE, COMMENTS \
FROM SYS.TABLE_COLUMNS WHERE TABLE_NAME = "
str_sql = str_sql_prefix + "'" + str(
self.tabline.text()) + "'" + "ORDER BY POSITION"
try:
dbcon = pyhdb.connect(host=self.HDB_IP,
port=self.HDB_PORT,
user=self.HDB_account,
password=self.HDB_pwd)
cursor = dbcon.cursor()
print(cursor)
cursor.execute(str_sql)
except:
if cursor.connection == '':
error_msg = "光标出错"
QMessageBox.information(self, "error_msg", error_msg)
self.HDB_result_output.setText(str_sql)
else:
error_msg = "SQL执行出错"
QMessageBox.information(self, "error_msg", error_msg)
self.HDB_result_output.setText(str_sql)
else:
res = cursor.fetchall()
for i in range(len(res)):
for j in range(len(res[i])):
self.model.setItem(i, j, QStandardItem(str(res[i][j])))
self.HDB_result_table.setModel(self.model)
def query(self, matchId, teamId, frame):
try:
connection = pyhdb.connect(host="sports-ml.mo.sap.corp",
port=30015,
user="******",
password="******")
cursor = connection.cursor()
selectStatement = "select x,y from \"SAP_SPORTS_DFL\".\"sap.sports.dfl.module.matchdata.private.table::TRACKING\" where match_id=%s and team_id=%s and n=%s"
result = cursor.execute(selectStatement, [matchId, teamId, frame])
res = cursor.fetchall()
for i in res:
print('[', i[0], ',', i[1], ']')
players = []
for i in res:
players.append([float(i[0].real), float(i[1].real)])
players = np.array(players)
players = self.deleteGoalkeeper(players)
return players
connection.commit()
connection.close()
except Exception as e:
print(e)
connection.rollback()
connection.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(host=conn_params['host'],
port=int(conn_params['port']),
user=conn_params['user'],
password=conn_params['password'])
# set autocommit on by default
self.set_autocommit(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 __setConnection(self, sys):
self.__login['user'] = "******"
self.__login['password'] = "******" # Will drop it from login
if self.sys == 'SB3':
self.__login['host'] = r"10.230.82.120" #sapr3sb3
self.__login['instanceNo'] = "01"
elif self.sys == "CD1":
pass
elif self.sys == "CQ1":
pass
elif self.sys == "CD2":
pass
elif self.sys == "CQ2":
pass
elif self.sys == "SB1":
pass
elif self.sys == "SB2":
pass
else:
raise ValueError("System id {0} is not supported!".format(sys))
self.__login['port'] = 30015 + 100 * int(self.__login['instanceNo'])
#print(self.__login)
self.__schema = "SAPSB3"
self.__connection = pyhdb.connect(host=self.__login['host'],
port=self.__login['port'],
user=self.__login['user'],
password=self.__login['password'])
self.__cursor = self.__connection.cursor()
def hana_connection(wea_group):
conn = pyhdb.connect(host = 'ip',
port = 'prort',
user = "",
password = "")
cursor = conn.cursor()
# sql1="drop table config.CITY_WEATHER"
# sql2="create column table config.CITY_WEATHER(CITY NVARCHAR(25),DAY_ID NVARCHAR(10),DAY_DES nvarchar(25),ZWEA nvarchar(225),ZTEM nvarchar(25),ZWIN nvarchar(25))"
sql3="insert into config.CITY_WEATHER(PROVINCE,CITY, DAY_ID,DAY_DES,ZWEA,ZTEM,ZWIN) values(%s,%s,%s,%s,%s,%s,%s)"
try:
# cursor.execute(sql1)
# cursor.execute(sql2)
i=len(wea_group)
for num in range(i):
co1=wea_group[num].split(',')[0]
co2=wea_group[num].split(',')[1]
co3=wea_group[num].split(',')[2]
co4=wea_group[num].split(',')[3]
co5=wea_group[num].split(',')[4]
co6=wea_group[num].split(',')[5]
co7=wea_group[num].split(',')[6]
par=co1,co2,co3,co4,co5,co6,co7
print(par)
cursor.execute(sql3,par)
conn.commit()
# print( '----sql执行成功----')
except Exception as e:
print("----sql异常-->"+str(e))
conn.rollback()
finally:
conn.close()
def connection(request, hana_system):
connection = pyhdb.connect(*hana_system)
def _close():
connection.close()
request.addfinalizer(_close)
return connection
def viewHierarchy(view):
connection = pyhdb.connect(host=host, port=port, user=user, password=pwsd)
cursor = connection.cursor()
f = open('resultCalcViewHierarchy.json', 'w')
f.write(
str(getDependent(view, 'null', 'VIEW', cursor, {})).replace("'", '"'))
f.close()
connection.close()
def connection(request, hana_system):
connection = pyhdb.connect(*hana_system)
def _close():
connection.close()
request.addfinalizer(_close)
return connection
def get_connection():
conn_obj = pyhdb.connect(
host="192.168.1.13",
port=30415, #port的编号是3+instance number+15
user="******",
password="******")
return conn_obj
def _retrieve_data(self, data_source):
connection = pyhdb.connect(self.host, self.port, self.user, self.password)
df = pd.read_sql(data_source.query, con=connection)
connection.close()
return df
def get_connect(): # 连接数据库信息
conn_obj = pyhdb.connect(
host='10.150****',
port = 30015,
user = '******',
password = '******'
)
return conn_obj
def get_connection():
conn_obj = pyhdb.connect(
host="192.168.2.192", # 192.168.2.191 生产机 192.168.2.192 开发机
port=30241, # 30041生产机 30241开发机
user="******",
password="******"
)
return conn_obj
def get_connection(system, user, password):
conn = pyhdb.connect(host=system.host,
port=system.port,
user=user,
password=password)
if system.default_schema:
conn.cursor().execute(f'set schema {system.default_schema}')
return conn
def connector(self):
if self._connector == None:
connector = pyhdb.connect(host=db_HOST,
port=db_PORT,
user=db_USER,
password=db_PASSWORD)
self._connector = connector
return self._connector
def __init__(self):
self.hana_address = "10.58.114.74"
self.schema = "SBODEMOUS"
self.port = 30015
self.connection = None
self.connection = pyhdb.connect(host=self.hana_address,
port=self.port,
user="******",
password="******")
def create_hana_connection(system):
connection = pyhdb.connect(
host="10.200.81.10",
port=port_s[system],
user="******",
password=pass_s[system],
)
return connection
def __init__ (self, password):
self.connection = pyhdb.connect(
host = "52.87.77.114",
port = 30015,
user = '******',
password = password)
self.cursor = self.connection.cursor()
self.lastStatement = ""
def connector(self): if self._connector == None: connector = pyhdb.connect( host = db_HOST, port = db_PORT, user = db_USER, password = db_PASSWORD) self._connector = connector return self._connector
def conn_db():
connection = pyhdb.connect(
host="172.16.0.62",
port=34015,
user="******",
password="******"
)
return connection
def connect_hdb():
"""Connect to Hana Database and return the connection object."""
connection = pyhdb.connect(
host=os.getenv("DB_HOST"),
port=os.getenv("DB_PORT"),
user=os.getenv("DB_USER"),
password=os.getenv("DB_PASS")
)
return connection
def cursor_instance(self):
connection = pyhdb.connect(
host=self.host,
port=self.port,
user=self.user,
password=self.password
)
cursor = connection.cursor()
return cursor
def open_connection():
global hana_connection
global hana_cursor
hana_connection = pyhdb.connect(host=hana_config.db_host,
port=hana_config.db_port,
user=hana_config.db_user,
password=hana_config.db_pwd)
hana_cursor = hana_connection.cursor()
def test_auto_commit(self, request, hana_system, test_table):
connection_1 = pyhdb.connect(*hana_system, autocommit=True)
connection_2 = pyhdb.connect(*hana_system, autocommit=True)
def _close():
connection_1.close()
connection_2.close()
request.addfinalizer(_close)
cursor1 = connection_1.cursor()
cursor1.execute('INSERT INTO PYHDB_TEST_1 VALUES(%s)',
('connection_1', ))
cursor1.execute("SELECT * FROM PYHDB_TEST_1")
assert cursor1.fetchall() == [('connection_1', )]
cursor2 = connection_2.cursor()
cursor2.execute("SELECT * FROM PYHDB_TEST_1")
assert cursor2.fetchall() == [('connection_1', )]
def test_auto_commit(self, request, hana_system, test_table):
connection_1 = pyhdb.connect(*hana_system, autocommit=True)
connection_2 = pyhdb.connect(*hana_system, autocommit=True)
def _close():
connection_1.close()
connection_2.close()
request.addfinalizer(_close)
cursor1 = connection_1.cursor()
cursor1.execute(
'INSERT INTO PYHDB_TEST_1 VALUES(%s)', ('connection_1',)
)
cursor1.execute("SELECT * FROM PYHDB_TEST_1")
assert cursor1.fetchall() == [('connection_1',)]
cursor2 = connection_2.cursor()
cursor2.execute("SELECT * FROM PYHDB_TEST_1")
assert cursor2.fetchall() == [('connection_1',)]
def __init__(self, request):
self.request = request
self.connection = pyhdb.connect(host="10.58.114.58",
port=30015,
user="******",
password="******")
self.object_type = 'POR'
self.from_date = '20110101'
self.to_date = '20170331'
self.fields = 'Price,Quantity'
def try_reconnecting():
try:
global connection
db = get_settings('database')
connection = pyhdb.connect(host=db['host'],
port=db['port'],
user=db['username'],
password=db['password'])
except Exception, e:
print e
def connect(self):
"""
Connect to a HDB instance
"""
self.connection = pyhdb.connect(
host = self.host,
port = self.port,
user = self.user,
password = self.password,
)
self.cursor = self.connection.cursor()
def __init__(self):
try:
self.connection = pyhdb.connect(host=HOST,
port=30015,
user=USER,
password=PW,
autocommit=True)
self.cursor = self.connection.cursor()
except socket.gaierror as e:
print('Database instance is not available! \n\n')
raise e
def try_reconnecting():
try:
global connection
db = get_settings('database')
connection = pyhdb.connect(
host=db['host'],
port=db['port'],
user=db['username'],
password=db['password']
)
except Exception, e:
print e
def execute(query, arguments):
connection = pyhdb.connect(
config.host, config.port,
config.user, config.password
)
cur = connection.cursor()
cur.execute(query, arguments)
result = cur.fetchall()
connection.close()
return result
def do_login():
username = request.forms.get('username')
password = request.forms.get('password')
hostname = request.forms.get('hostname')
connection = pyhdb.connect(
host=hostname,
port=30015,
user=username,
password=password
)
global cursor
cursor = connection.cursor()
redirect("/home")
def test_rollback(request, hana_system, test_table_1):
connection_1 = pyhdb.connect(*hana_system)
connection_2 = pyhdb.connect(*hana_system)
def _close():
connection_1.close()
connection_2.close()
request.addfinalizer(_close)
cursor1 = connection_1.cursor()
cursor1.execute(
'INSERT INTO PYHDB_TEST_1 VALUES(%s)', ('connection_1',)
)
cursor1.execute("SELECT * FROM PYHDB_TEST_1")
assert cursor1.fetchall() == [('connection_1',)]
cursor2 = connection_2.cursor()
cursor2.execute("SELECT * FROM PYHDB_TEST_1")
assert cursor2.fetchall() == []
connection_1.rollback()
cursor1.execute("SELECT * FROM PYHDB_TEST_1")
assert cursor1.fetchall() == []
from pyhdb.protocol.lobs import NClob
if len(sys.argv) != 2:
print 'Usage: python import_business.py <path>'
print '\t path: path to texts file'
exit()
filepath = sys.argv[1]
with open("../secrets.json") as f:
secrets = json.load(f)
connection = pyhdb.connect(
host=secrets['host'],
port=secrets['port'],
user=secrets['username'],
password=secrets['password']
)
cursor = connection.cursor()
with open(filepath) as f:
for i, line in enumerate(f):
print i
try:
sql_to_prepare = 'CALL LTN_DEVELOP.add_document (?, ?, ?)'
params = {
'DOCUMENT_ID': 'MAZ_{n:06d}'.format(n=i),
# called inline by 01_buildStream.sh
# Author: Mark Teehan, March 2018.
import sys
from random import randrange
import random
from datetime import timedelta, datetime
import pyhdb
import os, shutil, fnmatch
from time import gmtime, strftime
from string import Template
vTableName=sys.argv[1]
vStringNumber=sys.argv[2]
conn = pyhdb.connect(host='xxx.xxx.xxx.corp',port=3xx15,user='******', password='******')
vDebug='N'
def filter_non_printable(str):
return ''.join([c for c in str if ord(c) > 31 or ord(c) == 9 or ord(c)>26])
def timeStamped(fmt='%Y%m%d_%H%M%S'):
return datetime.now().strftime(fmt)
def tsString(fmt='%Y%m%d%H%M%S'):
return datetime.now().strftime(fmt)
def Debug(pMsg):
if vDebug == 'Y':
print("[" + "Debug " +pMsg + "]")
for line in lines:
# remove express stations (subset of normal stations)
if len(line) > 1:
lines.remove(line)
return '-'.join(lines)
""" Script to import Subway-Data as KML into a given HANA-Database """
import credentials
# establish connection with provided credentials
connection = pyhdb.connect(
host = credentials.host,
port = credentials.port,
user = credentials.user,
password = credentials.password
)
cursor = connection.cursor()
xmlns = '{http://www.opengis.net/kml/2.2}'
directory = os.path.dirname(os.path.abspath(__file__))
path = os.path.join(directory, "data/Subway Stations.kml")
root = etree.parse(path)
root = etree.fromstring(etree.tostring(root))
set_count = 1
_clearTable()
_createTable()
def test_method_raises_error_after_close(hana_system, method):
connection = pyhdb.connect(*hana_system)
connection.close()
with pytest.raises(pyhdb.Error):
getattr(connection, method)()
import json
import pyhdb
with open("../secrets.json") as f:
secrets = json.load(f)
details = secrets.get('database')
connection = pyhdb.connect(
host=details.get('host'),
port=details.get('port'),
user=details.get('username'),
password=details.get('password')
)
types = list()
with open("UMLS Semantic Types with Codes and Groups.txt", "r") as f:
for line in f:
t = line.strip('\n, ').split("|")
types.append((t[2], t[0], t[1], t[3]))
cursor = connection.cursor()
cursor.executemany("INSERT INTO LTN_DEVELOP.TYPES(CODE, GROUP_ID, \"GROUP\", NAME) VALUES (?, ?, ?, ?)", types)
connection.commit()
groups = list()
cursor.execute('SELECT DISTINCT "GROUP_ID", "GROUP" FROM LTN_DEVELOP.TYPES')
for row in cursor.fetchall():
groups.append((row[0], row[0], row[1], row[1]))
cursor.executemany("INSERT INTO LTN_DEVELOP.TYPES(CODE, GROUP_ID, \"GROUP\", NAME) VALUES (?, ?, ?, ?)", groups)
connection.commit()
from werkzeug.contrib.cache import SimpleCache
from config import Config
from helpers import get_rides_from_area, get_neighborhood_data, \
hana_polygon_from_list, get_bounding_box_condition, \
get_bounding_box, get_neighborhoods_details, get_neighborhoods_rides
static_assets_path = path.join(path.dirname(__file__), "dist")
app = Flask(__name__, static_folder=static_assets_path)
cache = SimpleCache()
CACHE_TIMEOUT = 60*30
hana = pyhdb.connect(host=Config.hana_host, port=Config.hana_port, user=Config.hana_user, password=Config.hana_pass)
geo_data = {}
# ----- Cache Decorator -
def cached(timeout=30 * 60, key='view/%s'):
def decorator(f):
@wraps(f)
def decorated_function(*args, **kwargs):
cache_key = request.path + str(request.args.items())
rv = cache.get(cache_key)
if rv is not None:
return rv
rv = f(*args, **kwargs)
cache.set(cache_key, rv, timeout=timeout)
return rv
def test_connection_refused(mock_socket):
mock_socket.side_effect = socket.error("TEST ERROR")
with pytest.raises(Error) as e:
pyhdb.connect("localhost", 30015, "Fuu", "Bar")
assert e.value.message[0] == "TEST ERROR"
##################################
##################################
##################################
print " * What is the password for your HANA user? (User: %s)" % (user)
password = getpass.getpass()
##################################
##################################
##################################
print " * Opening connection to HANA..."
try:
connection = pyhdb.connect(
host=server,
port=port,
user=user,
password=password
)
cursor = connection.cursor()
except pyhdb.exceptions.DatabaseError, e:
parser.error("invalid username or password")
##################################
##################################
##################################
print " * Creating the schema and tables..."
createUMLSSchemaAndTables(connection)
if files['types']:
def test_connect_constructors(hana_system):
connection = pyhdb.connect(*hana_system)
assert isinstance(connection, pyhdb.connection.Connection)
connection.close()
