metavar='scenario_id', help='Scenario ID for the simulation') parser.add_argument('-i', type=str, default='inputs', metavar='inputsdir', help='Directory where the inputs will be built') args = parser.parse_args() passw = getpass.getpass('Enter database password for user %s:' % args.user) try: # Connection settings are determined by parsed command line inputs # Start an ssh tunnel because the database only permits local connections server = SSHTunnelForwarder(args.host, ssh_pkey=os.path.expanduser('~') + "/.ssh/id_rsa", remote_bind_address=('127.0.0.1', args.port)) server.start() # with SSHTunnelForwarder( # args.host, # ssh_pkey= os.path.expanduser('~') + "/.ssh/id_rsa", # remote_bind_address=('127.0.0.1', args.port) # ) as server: con = psycopg2.connect( database=args.database, user=args.user, host='127.0.0.1', # port=server.local_bind_port, password='******') port=server.local_bind_port, password=passw) print "Connection to database established..."
def dump(self): # This function takes a dataframe and creates a MySQL table # (if not existing) containing the needed columns with the proper format # and includes some index. # Then the function dumps the content of the input dataframe into the table. # The connection to the database is done through a SSH tunnel. # Establishing the SSH tunnel # Local bind port is allocated randomly and # local bind host is allocated automatically with SSHTunnelForwarder(ssh_address_or_host=(config.SSH_HOST, config.SSH_PORT), ssh_username=config.SSH_USER, ssh_private_key=config.PRIVATE_KEY_PATH, remote_bind_address=(config.REMOTE_HOST, config.REMOTE_PORT)) as tunnel: # Connecting to the database # (mysql.connector does not work through SSH tunnel) # (pymysql needs an explicit parameter to enable multiple queries) if config.LOCAL_TESTING is False: conn = pymysql.connect(user=config.REMOTE_USER, password=config.REMOTE_PASSWORD, host=tunnel.local_bind_host, port=tunnel.local_bind_port, database=config.DB, client_flag=CLIENT.MULTI_STATEMENTS) else: # This is used for local testing conn = pymysql.connect(user=config.LOCAL_USER, password=config.LOCAL_PASSWORD, host=config.LOCAL_HOST, database=config.LOCAL_DB, client_flag=CLIENT.MULTI_STATEMENTS) cur = conn.cursor() # Executing a query to create the needed table (if not existing) # (indexes are added for the columns date, temperature and at_risk, # one multiple column index is added to allow smoothly extraction # of data for calculatimg aggregate values) sql1 = "CREATE TABLE IF NOT EXISTS Daily_Dump "\ "(id BIGINT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT, "\ "date DATETIME, "\ "temperature ENUM('LOW', 'MEDIUM', 'HIGH'), "\ "at_risk BOOL, "\ "skipped_beat SMALLINT, "\ "price DOUBLE, "\ "INDEX day (date), "\ "INDEX temp_group (temperature), "\ "INDEX risk (at_risk), "\ "INDEX create_aggr_data (temperature, price)) "\ "DEFAULT CHARACTER SET=utf8;" cur.execute(sql1) # Looping through all the indexes of the dataframe for ind in self.index.tolist(): # Preparing the values for the database values = (self.iloc[ind, 0], # date self.iloc[ind, 1], # temperature self.iloc[ind, 2], # at_risk self.iloc[ind, 3], # skipped_beat self.iloc[ind, 4]) # price # Inserting the entry into the database table sql2 = "INSERT INTO Daily_Dump "\ "(date, "\ "temperature, "\ "at_risk, "\ "skipped_beat, "\ "price) "\ "VALUES (%s, %s, %s, %s, %s)" cur.execute(sql2, values) # Committing the queries conn.commit() print("File loaded into the database") # Closing the connection to the database conn.close()
import os import sys import json import pymongo from sshtunnel import SSHTunnelForwarder import pprint hostname = "10.127.250.99" username = "******" password = "******" #MONGO_DB = "temp_UI" server = SSHTunnelForwarder(hostname, ssh_username=username, ssh_password=password, remote_bind_address=('127.0.0.1', 27017)) server.start() client = pymongo.MongoClient('127.0.0.1', server.local_bind_port) db = client["temp_ASR9K"] pprint.pprint(db.collection_names())
def MakeTunnel(bastion_ip, bastion_user, bastion_pwd, endpoint): tunnel = SSHTunnelForwarder((bastion_ip, 22), ssh_username=bastion_user, ssh_password=bastion_pwd, remote_bind_address=(endpoint, 6379)) return tunnel
import csv import mysql.connector import os import shutil from sshtunnel import SSHTunnelForwarder with SSHTunnelForwarder( ('rcg-linux-ts1.rcg.sfu.ca', 22), ssh_username="******", ssh_password="******", remote_bind_address=('cs-oschulte-01.cs.sfu.ca', 3306)) as server: #connect to database server mydb = mysql.connector.connect(host='127.0.0.1', port=server.local_bind_port, user='******', passwd='joinbayes', db='chao_draft') cursor = mydb.cursor() # comment the following statements if table has been created cursor.execute( "Drop table if exists chao_draft.lmt_10years_CSS_null_norm_prob;") mydb.commit() cursor.execute("""Create table chao_draft.lmt_10years_CSS_null_norm_prob (id INT, PlayerName VARCHAR(50), DraftAge_norm double, country_group VARCHAR(10), Height_norm double, Weight_norm double, Position VARCHAR(5), DraftYear INT, Overall INT, CSS_rank_norm double, rs_GP_norm double, rs_G_norm double, rs_A_norm double, rs_P_norm double, rs_PIM_norm double,
def plus(self): pfid = self.lineEdit_4.text() monkey = self.lineEdit_5.text() if pfid == '': QtWidgets.QMessageBox.information(self, "Tips", "账号不能为空") elif str(pfid).isdigit() == False: QtWidgets.QMessageBox.information(self, "Tips", "账号只能为数字,请重新输入数字") self.lineEdit_4.clear() if monkey == '': QtWidgets.QMessageBox.information(self, "Tips", "浪花不能为空") elif str(monkey).isdigit() == False: QtWidgets.QMessageBox.information(self, "Tips", "浪花只能为数字,请重新输入数字") self.lineEdit_5.clear() else: with SSHTunnelForwarder( ('13.112.45.12', 22), # B机器的配置 ssh_pkey=file, # ssh_password=flie, ssh_username="******", remote_bind_address=(address, 3306)) as server: # A机器的配置 conn = pymysql.connect( host='127.0.0.1', # 此处必须是是127.0.0.1 port=server.local_bind_port, user=userid, passwd=password, # db='db_admin', charset='utf8') cursor = conn.cursor() sql = "select db_member_01.tb_user.pfid from db_member_01.tb_user where db_member_01.tb_user.pfid=" + self.lineEdit_4.text( ) print(sql) cursor.execute(sql) result6 = cursor.fetchall() print(result6) conn.close() li = [] list1 = list(result6) li.append(list1) li2 = str(li).replace("[", "").replace("]", "") print(li2) if li2 == '': QtWidgets.QMessageBox.information(self, "Tips", "账号不存在,请先注册") self.lineEdit_4.clear() else: url1 = "https://api.s.lang.live/dd/balance/add" data1 = { "pfid": int(str(pfid)), "key": "flzx3qc", "gold": int(str(monkey)) } print(data1) headers1 = { 'Content-Type': 'application/x-www-form-urlencoded' } r1 = requests.post(url=url1, data=data1, headers=headers1) print(r1.json()) print(r1.json()['balance']) QtWidgets.QMessageBox.information( self, "Tips", self.tr('当前账号: ' + pfid + " \n增加的浪花为 " + monkey + " \n现有的浪花为 " + r1.json()['balance']))
def main(): db_port_actual = db_port if socket.getfqdn() != 'soi-volt.ischool.utexas.edu': tunnel = SSHTunnelForwarder((ssh_host, ssh_port), ssh_username=ssh_user, ssh_password=ssh_pass, remote_bind_address=(db_host, db_port)) tunnel.start() db_port_actual = tunnel.local_bind_port print("Not on volt. SSH tunnel started.") print("Connecting to database at port: %d" % db_port_actual) conn = pymysql.connect(host=db_host, port=tunnel.local_bind_port, user=db_user, passwd=db_pass, db=db_name, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) # ----------- actual code starts from here ----------------- sql_select_userid = (" SELECT DISTINCT a.userid " " FROM sandbox.pcde_metrics_user_doc a ") sql_select_hv = (" select a.scan_hv_ratio_2, a.DOCID_USERID " " from sandbox.pcde_metrics_user_doc a " " where a.userid = %s " " order by a.scan_hv_ratio_2 ") sql_upd_metrics = ( " UPDATE sandbox.pcde_metrics_user_doc a " #" SET a.scan_hv_ratio_2_personalized = %s " " SET a.scan_hv_ratio_2_pers2 = %s " " WHERE a.DOCID_USERID = %s ") #cursor cur_select_userid = conn.cursor() cur_select_hv = conn.cursor() cur_upd_metrics = conn.cursor() print('Start processing...') try: cur_select_userid.execute(sql_select_userid) bulk_insert = [] i_user = 0 # ---- user id loop---- for row_id in cur_select_userid: i_user += 1 # get hv_ratio and user_docid for each user cur_select_hv.execute(sql_select_hv, row_id['userid']) hv = [] user_docid = [] personalized_hv = [] # user loop to save hv_ratio and user_docid for row_hv in cur_select_hv: hv.append(row_hv['scan_hv_ratio_2']) user_docid.append(row_hv['DOCID_USERID']) #-----whisker---- lower_whisker, upper_whisker = calc_whisker(hv) #-----calculate for each docid_userid ------ # normalized with respect to the individual whisker-intervals for i in range(0, len(hv)): if hv[i] < lower_whisker: normalize = 0 elif hv[i] > upper_whisker: normalize = 1 else: normalize = (hv[i] - lower_whisker) / (upper_whisker - lower_whisker) #append list personalized_hv.append( [float(round(normalize, 10)), user_docid[i]]) # update for each user cur_upd_metrics.executemany(sql_upd_metrics, personalized_hv) print(i_user, "updated;") # -- end ids loop --- conn.commit() print('Done. Finished') # ----------- actual code ENDS here ----------------- finally: cur_select_hv.close() conn.close() if socket.getfqdn() != 'soi-volt.ischool.utexas.edu': tunnel.stop() tunnel.close()
from sshtunnel import SSHTunnelForwarder from vk_api import VkApi access_token = {"access_token": "xxxxx", "expires_in": 0, "user_id": 500000} access_login = '******' access_password = '******' server = SSHTunnelForwarder(('hostname.com', 22), ssh_username="******", ssh_pkey="id_rsa", ssh_private_key_password="", remote_bind_address=("127.0.0.1", 433), ssh_proxy_enabled=True) server.start() proxies = dict(http=f'socks5://[email protected]:{server.local_bind_port}', https=f'socks5://[email protected]:{server.local_bind_port}') vk_session = VkApi(login=access_login, password=access_password, token=access_token, proxies=proxies) server.stop()
from sshtunnel import SSHTunnelForwarder from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker from sqlalchemy import inspect load_dotenv() db_user = os.getenv("db_user" ) db_password = os.getenv("db_password") tunnel_user = os.getenv("tunnel_user") tunnel_password = os.getenv("tunnel_password") server = SSHTunnelForwarder( (tunnel_address, 21098), ssh_username=tunnel_user, ssh_password= tunnel_password, remote_bind_address=('127.0.0.1', 3306), local_bind_address=('0.0.0.0', 3306) ) server.start() print( server.local_bind_port) engine = create_engine(f'mysql://{db_user}:{db_password}@127.0.0.1:3306/nogslbqf_despacho') Base = declarative_base(engine) class Despacho(Base): __tablename__ = 'despacho' __table_args__ = { 'autoload' : True} metadata = Base.metadata
def replicate_pb_users(self): app.logger.info('Starting replication on PB users') account_name = config['pb_account_name'] last_id = select([ func.max(Users.c.original_id) ]).where(Users.c.account == account_name).execute().first() last_id = last_id[0] if last_id[0] else 0 log_data = [] with SSHTunnelForwarder( (config['pb_ssh_host'], int(config['pb_ssh_port'])), ssh_username=config['pb_ssh_username'], ssh_password=config['pb_ssh_password'], remote_bind_address=('127.0.0.1', 3306), local_bind_address=('127.0.0.1', 3307)) as server: connection_string = config['pb_connection_string'] + '?charset=utf8' source_engine = create_engine(connection_string) source_conn = source_engine.connect() sql = text('''SELECT * FROM users WHERE users.id > :last_id ORDER BY users.id ASC LIMIT :count''') result = source_conn.execute(sql, last_id=int(last_id), count=100).fetchall() users = [] for user in result: sql = text( '''SELECT country FROM webid WHERE web_id = :webid''') webid_info = source_conn.execute(sql, webid=user['webid']).first() if not webid_info: webid_info = dict(country=None) user_temp = dict(user) user_temp['country'] = webid_info['country'].title( ) if webid_info['country'] else None users.append(user_temp) source_conn.close() values = [] for user in users: app.logger.info('Inserting users id = %i, account = %s' % (user['id'], account_name)) created_at = user['created_date'] - timedelta(hours=1) values.append( dict(account=account_name, customer_id=user['customer_id'], email=user['email'], fname=user['fname'], lname=user['lname'], webid=user['webid'], country=user['country'], pubid=user['pubid'], subid=user['subid'], utm_medium=user['utm_medium'], utm_term=user['utm_term'], utm_content=user['utm_content'], utm_campaign=user['utm_campaign'], referrer_url=user['referrer_url'], ip_address=user['ip_addr'], click_id=user['click_id'], user_agent=user['user_agent'], original_id=user['id'], created_at=created_at)) if values: db_conn.execute(Users.insert(), values) app.logger.info('Finished replication on PB users')
#!/usr/bin/python from sshtunnel import SSHTunnelForwarder from pymongo import MongoClient from pprint import pprint from getpass import getpass MONGO_HOST = "cs1.ncf.edu" MONGO_DB = "hfmil" MONGO_USER = input("Enter your username: "******"Enter your password: ") server = SSHTunnelForwarder(MONGO_HOST, ssh_username=MONGO_USER, ssh_password=MONGO_PASS, remote_bind_address=('127.0.0.1', 27017)) server.start() client = MongoClient('127.0.0.1', server.local_bind_port) db = client[MONGO_DB] # Prints the collection names pprint(db.collection_names()) server.stop() server.is_active
import MySQLdb from sshtunnel import SSHTunnelForwarder import time with SSHTunnelForwarder( ("10.29.24.47", 222), # ssh IP和port ssh_password="******", # ssh 密码 ssh_username="******", # ssh账号 remote_bind_address=("10.29.129.94", 3306)) as server: # 数据库所在的IP和端口 server.start() # 打印本地端口,已检查是否配置正确 print(server.local_bind_port) conn = MySQLdb.connect( host="127.0.0.1", # 固定写法 port=server.local_bind_port, user="******", # 数据库账号 passwd="zts000", # 数据库密码 db='brokerage', charset='utf8') # 可以限定,只访问特定的数据库,否则需要在mysql的查询或者操作语句中,指定好表名 print('连接成功') time.sleep(100) cur = conn.cursor() # sql = """CREATE TABLE shenzhen_month ( # month CHAR(150), # total_amount CHAR(150), # market_share CHAR(150), # stock_trading_amount CHAR(150), # fund_trading_amount CHAR(150), # bond_trading_amount CHAR(150),
def main(): curDirName = os.path.dirname(__file__) fullPathToSettings = os.path.join(curDirName, '../../settings.json') # Read system settings. with open(fullPathToSettings) as settings: settings = json.load(settings) # Step 1: # - Connect with the arduino. # We will scan all the serial ports and connect to the first Arduino found. # For the SparkFun Blackboard, something like "USB-SERIAL CH340 (COM4)" will # show up in the port description. print("\nConnecting to Arduino ...") serPortToArduino = waitForDeviceOnSerial( settings['controllers']['arduino_serial_port']['keyword']) with serial.Serial(serPortToArduino, settings['controllers']['arduino_serial_port']['serial_baud_rate'], timeout=settings['controllers']['arduino_serial_port']['time_out_in_s'] ) as serPortToArduino: print("Succeeded!") # Fetching and displaying all incoming messages from the Arduino. waitForDataOnSerial(serPortToArduino) # The servos were successfully initialized. lastUnixTimeInSForServoAdjustment = time.time() minTimeInSToWaitForServoAdjustment = 1/MAX_SERVO_ADJUSTMENT_FREQUENCY_IN_HZ printAllSerData(serPortToArduino, ' Arduino (Raw Message during Initialization): ') print("Signalling Arduino to start " + "the auto antenna alignment procedure ...") serPortToArduino.write(b"r") # Read the acknowledgement. waitForDataOnSerial(serPortToArduino) ackFromArduino = serPortToArduino.readline() # Determine which side (TX/RX) the controller is at. if ackFromArduino == b'r\r\n': controllerSide = 'rx' elif ackFromArduino == b't\r\n': controllerSide = 'tx' else: raise ValueError( "Unknown controller role ({ack}) requested by Arduino".format( ack=ackFromArduino)) print("Succeeded!") # Step 2: # - Connect to the remote mySQL database. # For easy and secure remote access of the database, we will ssh forward # the MySQL port from the remote server to localhost (the controller of # the rotator). Please make sure the public ssh key of the controller # machine is registered at the server. print("\nForwarding port for accessing remote database ...") ssh_public_key = paramiko.RSAKey.from_private_key_file( bytes(settings['controllers'][controllerSide] ['ssh']['private_key_dir'], encoding='utf-8'), password=settings['controllers'][controllerSide] ['ssh']['private_key_password']) with SSHTunnelForwarder( (settings['server']['url'], settings['controllers'][controllerSide]['ssh']['port']), ssh_username=settings['controllers'][controllerSide] ['ssh']['user_account'], ssh_pkey=ssh_public_key, remote_bind_address=(settings['server']['mysql_instance']['host'], settings['server']['mysql_instance']['port']) ) as tunnelledMySql: print("Succeeded!\n\nConnecting to remote database ...") databaseConnection = mysql.connector.connect( host='localhost', port=tunnelledMySql.local_bind_port, database=settings['server']['mysql_instance']['database'], user=settings['controllers'][controllerSide] ['database_credential']['username'], password=settings['controllers'][controllerSide] ['database_credential']['password'], auth_plugin=settings['server']['mysql_instance']['auth_plugin']) databaseCur = databaseConnection.cursor() print("Succeeded!\nAuto antenna alignment initiated ...") # Step 3: # - Sensor data collection and # - automatic antenna alignment. try: # Initialize the cache variables for checking when new data is # available for realign the antenna. # (latInDegXe7, lonInDegXe7, altInMmMeanSeaLevel) currentGps = None currentImuQuat = None # (quatReal, quatI, quatJ, quatK) # (latInDegXe7, lonInDegXe7, altInMmMeanSeaLevel) currentGpsCounterpart = None # We will need to adjust the servos if new GPS or IMU data # become available. flagNeedToAdjustServos = True if controllerSide == 'rx': # Only the RX can create new record in the table # record_series. (currentRecordSeriesId, _, currentGpsSerialData, txLoc) = createNewRecordSeries( settings, serPortToArduino, databaseConnection, databaseCur) # Extract (latInDegXe7, lonInDegXe7, altInMmMeanSeaLevel). currentGps = currentGpsSerialData[3:6] currentGpsCounterpart = txLoc elif controllerSide == 'tx': # TODO: The RX will wait for new RX data entries and fetch # and update the latest record_series record accordingly. currentRecordSeriesId = fetchCurrentRecordSeries(databaseCur) updateCurrentRecordSeriesWithTxTimestamps(databaseCur) # Fetch (latInDegXe7, lonInDegXe7, altInMmMeanSeaLevel) for # the TX. currentGps = fetchTxLoc(settings) # TODO: Fetch the latest RX loc. currentGpsCounterpart = fetchRxGps() # Initialize motor PID controllers. pidX = PID(PID_P_X, PID_I_X, PID_D_X, setpoint = 0) pidX.sample_time = minTimeInSToWaitForServoAdjustment pidX.output_limits = PID_OUTPUT_LIMITS pidZ = PID(PID_P_Z, PID_I_Z, PID_D_Z, setpoint = 0) pidZ.sample_time = minTimeInSToWaitForServoAdjustment pidZ.output_limits = PID_OUTPUT_LIMITS while(True): # Fetch and process all incoming messages from the Arduino. sensorData = receiveDataFromSerial(serPortToArduino, ' Arduino (Sensor Data): ') # GPS package generates 22 values. if (sensorData is not None) and len(sensorData)==22: newGpsSerialData = sensorData currentGps = newGpsSerialData[3:6] # Adjust servos first to avoid delay caused by data # uploading. lastUnixTimeInSForServoAdjustment = adjustServos( serPortToArduino, currentGps, currentImuQuat, currentGpsCounterpart, pidX, pidZ) sendGpsDataToDatabase( currentRecordSeriesId, newGpsSerialData, controllerSide, databaseConnection, databaseCur) # IMU package generates 11 values. elif (sensorData is not None) and len(sensorData)==11: newImuSerialData = sensorData currentImuQuat = newImuSerialData[2:6] # Adjust servos first to avoid delay caused by data # uploading. lastUnixTimeInSForServoAdjustment = adjustServos( serPortToArduino, currentGps, currentImuQuat, currentGpsCounterpart, pidX, pidZ) sendImuDataToDatabase( currentRecordSeriesId, newImuSerialData, controllerSide, databaseConnection, databaseCur) # Counter part GPS location can change if this is the TX # side. if controllerSide == 'tx': lastGpsCounterpart = currentGpsCounterpart currentGpsCounterpart = fetchRxGps() if ((lastGpsCounterpart is not None) and (currentGpsCounterpart is not None) and (currentGpsCounterpart != lastGpsCounterpart)): lastUnixTimeInSForServoAdjustment = adjustServos( serPortToArduino, currentGps, currentImuQuat, currentGpsCounterpart, pidX, pidZ) finally: stopServos(serPortToArduino) databaseConnection.close() databaseCur.close()
''' __title__ = 'test_requests.py' __author__ = 'FenG' __mtime__ = '2017/11/20 14:21' ''' from __future__ import unicode_literals import json import os from elasticsearch import Elasticsearch from sshtunnel import SSHTunnelForwarder SSH_IP = os.environ.get('SSH_IP', 'localhost') SSH_PWD = os.environ.get('SSH_PWD', '123456') if __name__ == '__main__': with SSHTunnelForwarder( (SSH_IP, 22), # B机器的配置 ssh_password=SSH_PWD, ssh_username="******", remote_bind_address=("10.27.157.14", 9200)) as server: es = Elasticsearch(hosts='127.0.0.1', port=server.local_bind_port) data = es.search(index='index', doc_type='test', body={'query': { 'match_all': {} }}) print json.dumps(data, indent=4)
def info(self): pfid3 = self.lineEdit_11.text() if self.lineEdit_11.text() == '': QtWidgets.QMessageBox.information(self, "Tips", self.tr("pfid不能为空")) elif str(self.lineEdit_11.text()).isdigit() == False: QtWidgets.QMessageBox.information(self, "Tips", self.tr("pfid只能为数字,请重新输入数字")) self.lineEdit_11.clear() else: with SSHTunnelForwarder( ('13.112.45.12', 22), # B机器的配置 ssh_pkey=file, # ssh_password=flie, ssh_username="******", remote_bind_address=(address, 3306)) as server: # A机器的配置 conn = pymysql.connect( host='127.0.0.1', # 此处必须是是127.0.0.1 port=server.local_bind_port, user=userid, passwd=password, # db='db_admin', charset='utf8') cursor = conn.cursor() print(pfid3) sql = "select db_member_01.tb_user.uid from db_member_01.tb_user where db_member_01.tb_user.pfid=" + pfid3 sql1 = "select db_member_01.tb_user.nickname from db_member_01.tb_user where db_member_01.tb_user.pfid=" + pfid3 sql2 = "select db_member_01.tb_user.sex from db_member_01.tb_user where db_member_01.tb_user.pfid=" + pfid3 sql3 = "select db_member_01.tb_user.sex from db_member_01.tb_user where db_member_01.tb_user.pfid=" + pfid3 print(sql) print(sql1) print(sql2) cursor.execute(sql) result0 = cursor.fetchall() cursor.execute(sql1) result1 = cursor.fetchall() cursor.execute(sql2) result2 = cursor.fetchall() print(result0) print(result1) print(result2) li = [] list1 = list(result0) li.append(list1) # print "+++++",type(li) li2 = str(li) li3 = li2.replace("(", "").replace(")", "").replace( "[", "").replace("]", "").replace(",", "").replace("'", "") print('+++', li3) self.lineEdit_12.setText(str(li3)) if li3 == '': QtWidgets.QMessageBox.information(self, "Tips", self.tr("pfid不存在,请先注册")) self.lineEdit_11.clear() else: lii = [] list2 = list(result1[0]) lii.append(list2) # print "+++++",type(li) li4 = str(lii) li5 = li4.replace("(", "").replace(")", "").replace( "[", "").replace("]", "").replace(",", "").replace("'", "") print(li5) liii = [] list3 = list(result2[0]) liii.append(list3) # print "+++++",type(li) li6 = str(liii) li7 = li6.replace("(", "").replace(")", "").replace( "[", "").replace("]", "").replace(",", "") print(li7) if li7 == '1': li7 = '男' if li7 == '2': li7 = '女' if li7 == '0': li7 = '秘密' self.lineEdit_13.setText(str(li5)) self.lineEdit_14.setText(str(li7))
def main(): db_port_actual = db_port if socket.getfqdn() != 'soi-volt.ischool.utexas.edu': tunnel = SSHTunnelForwarder((ssh_host, ssh_port), ssh_username=ssh_user, ssh_password=ssh_pass, remote_bind_address=(db_host, db_port)) tunnel.start() db_port_actual = tunnel.local_bind_port print("Not on volt. SSH tunnel started.") print("Connecting to database at port: %d" % db_port_actual) conn = pymysql.connect(host=db_host, port=db_port_actual, user=db_user, passwd=db_pass, db=db_name, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) # ----------- actual code starts from here ----------------- sql_select_scanpath_id = (" SELECT DISTINCT a.scanpath_id " " FROM sandbox.rdtm_metrics_user_doc_viewnum a ") sql_select_fixns = (" select a.fixn_dur " " from sandbox.rdtm_fixations a " " where a.scanpath_id = %s ") sql_upd_dur = (" UPDATE sandbox.rdtm_metrics_user_doc_viewnum a " " SET a.fixn_dur_avg = %s," " a.fixn_dur_sd = %s " " where a.scanpath_id = %s ") #cursor cur_select_scanpath_id = conn.cursor() cur_select_fixns = conn.cursor() cur_upd_dur = conn.cursor() print('Start processing...') try: bulk_insert = [] cur_select_scanpath_id.execute(sql_select_scanpath_id) i_scanpath = 0 for scanpath_id in cur_select_scanpath_id: id = scanpath_id['scanpath_id'] i_scanpath += 1 cur_select_fixns.execute(sql_select_fixns, id) list_fixn_dur = [] for fixn in cur_select_fixns: list_fixn_dur.append(fixn['fixn_dur']) fixn_dur_avg = np.nan_to_num(np.mean(list_fixn_dur)) fixn_dur_sd = np.nan_to_num(np.std(list_fixn_dur)) # print ("fixn_dur_avg",fixn_dur_avg) bulk_insert.append([ float(round(fixn_dur_avg, 10)), float(round(fixn_dur_sd, 10)), id ]) if i_scanpath % BULK_THRESH == 0: cur_upd_dur.executemany(sql_upd_dur, bulk_insert) print("No. of docid_userids inserted = %d" % (i_scanpath)) bulk_insert = [] conn.commit() print('Done. Finished') # ----------- actual code ENDS here ----------------- finally: cur_select_fixns.close() conn.close() print("end") if socket.getfqdn() != 'soi-volt.ischool.utexas.edu': tunnel.stop() tunnel.close()
def __init__(self, parent=None): super().__init__(parent) self.setWindowTitle(u'背包道具列表') self.setWindowIcon(QtGui.QIcon(':/img/123.png')) self.table = QTableWidget(500, 9) self.resize(919, 799) self.setCentralWidget(self.table) with SSHTunnelForwarder( ('13.112.45.12', 22), # B机器的配置 ssh_pkey=file, # ssh_password=flie, ssh_username="******", remote_bind_address=(address, 3306)) as server: # A机器的配置 conn = pymysql.connect( host='127.0.0.1', # 此处必须是是127.0.0.1 port=server.local_bind_port, user=userid, passwd=password, # db='db_admin', charset='utf8') cursor = conn.cursor() cursor.execute("SELECT VERSION()") data = cursor.fetchone() # print "Database version : %s " % data sql = "select db_billing.tb_bag_item.id from db_billing.tb_bag_item GROUP BY db_billing.tb_bag_item.id Asc" sql1 = "select db_billing.tb_bag_item.item_type from db_billing.tb_bag_item GROUP BY db_billing.tb_bag_item.id Asc" sql2 = "select db_billing.tb_bag_item.alert_desc from db_billing.tb_bag_item GROUP BY db_billing.tb_bag_item.id Asc" cursor.execute(sql) result = cursor.fetchall() cursor.execute(sql1) result1 = cursor.fetchall() cursor.execute(sql2) result2 = cursor.fetchall() conn.close() a = self.class_li(result) b = self.class_lii(result1) c = self.class_lii(result2) self.table.verticalHeader().setVisible(False) self.table.setEditTriggers(QTableWidget.NoEditTriggers) self.table.setColumnWidth(0, 30) self.table.setColumnWidth(3, 30) self.table.setColumnWidth(6, 30) self.table.setColumnWidth(1, 80) self.table.setColumnWidth(4, 80) self.table.setColumnWidth(7, 80) self.table.setColumnWidth(2, 190) self.table.setColumnWidth(5, 190) self.table.setColumnWidth(8, 190) self.table.setAlternatingRowColors(True) self.table.setHorizontalHeaderLabels([ u'编号', u'类型', u'道具名', u'编号', u'类型', u'道具名', u'编号', u'类型', u'道具名' ]) # self.table.horizontalHeader().setStretchLastSection(True) x = 0 y = 0 for i in a: newItem = QTableWidgetItem(i) self.table.setItem(x, y, newItem) y = y + 3 if y >= 9: y = 0 x = x + 1 x = 0 z = 1 for i in b: if i == '3': i = u'称号道具' if i == '2': i = u'人气道具' if i == '1': i = u'普通道具' newItem1 = QTableWidgetItem(i) self.table.setItem(x, z, newItem1) z = z + 3 if z >= 9: z = 1 x = x + 1 x = 0 v = 2 for i in c: newItem1 = QTableWidgetItem(i) self.table.setItem(x, v, newItem1) v = v + 3 if v >= 9: v = 2 x = x + 1
from sshtunnel import SSHTunnelForwarder # This python file's only purpose of this code is to SSH tunnel into the deployment server from a development environment. # This code will not be used by the deployment server. db_port = 3306 remote_user = "******" remote_key = "./credentials/648team13.pem" remote_host = "ec2-3-17-190-122.us-east-2.compute.amazonaws.com" remote_port = 22 localhost = "127.0.0.1" server = SSHTunnelForwarder( ssh_address_or_host=(remote_host, remote_port), ssh_username=remote_user, ssh_pkey=remote_key, remote_bind_address=(localhost, db_port), )
def get_score(): global tunnel if database_option == 'Production': tunnel = SSHTunnelForwarder( (p_tunnel, 22), ssh_username=p_ssh_user, ssh_private_key='vladan_open', remote_bind_address=(p_remote_bind_address, 5432), local_bind_address=('localhost', 5432)) else: tunnel = SSHTunnelForwarder( (s_tunnel, 22), ssh_username=s_ssh_user, ssh_private_key='vladan_open', remote_bind_address=(s_remote_bind_address, 5432), local_bind_address=('localhost', 5432)) # Start the tunnel tunnel.start() try: devices = getVehicles() vin_data = request.get_json() flag = 0 if not vin_data["vin"]: return jsonify({'result': 'Unvalid'}) for i in range(0, len(devices)): if (vin_data["vin"] == devices.iloc[i]["vin"]): flag = 1 if not flag: return jsonify({'result': "Unknown Vin"}) results = [] results.append([ 'vin', 'overall', 'imei_vin_score', 'fuel_level_score', 'battery_voltage_score', 'odometer_score', 'gps_score' ]) imei_vin_score = 10000 fuel_score = 0 battery_score = 0 odometer_score = 0 gps_null_score = 10000 vin = vin_data["vin"] print(vin) # print("IMEI_VIN Score") try: imei_vin_data = get20DayData(vin) if len(imei_vin_data.index) > 0: print("20 Days IMEI_VIN Data") print(imei_vin_data) imei_vin_score = calcIMEIVINScorePhase1(imei_vin_data) imei_vin_data = get50LastData(vin) if len(imei_vin_data.index) > 0: imei_vin_score = calcIMEIVINScorePhase2( imei_vin_data, imei_vin_score) print("Last 50 Days IMEI_VIN Data") print(imei_vin_data) else: print("No 50 Last Day IMEI_VIN data") else: print("No 20 Day IMEI_VIN data") except Exception as error: print(error) try: fuel_data = getFuelLevelData(vin) if len(fuel_data.index) > 0: fuel_score = calcFuelScore(fuel_data) print("Fuel Level Data") print(fuel_data) else: print("No fuel level data") except Exception as error: print(error) try: day15_battery_data = get15DayData(vin) hour12_battery_data = get12HourData(vin) if len(day15_battery_data.index) > 0 and len( hour12_battery_data.index) > 0: battery_score = calcBatteryScore(day15_battery_data, hour12_battery_data) print("15 Days Battery Data") print(day15_battery_data) print("12 Hours Battery Data") print(hour12_battery_data) else: print("No battery voltage data") except Exception as error: print(error) try: odometer_data = getOdometerData(vin) if len(odometer_data.index) > 0: odometer_score = calcOdometerScore(odometer_data) print("Odometer Data") print(odometer_data) else: print("No odometer data") except Exception as error: print(error) try: gps_null_data = getGPSData(vin) if len(gps_null_data.index) > 0: gps_null_score = calcGPSNullScore(gps_null_data) print("GPS Data") print(gps_null_data) else: print("No GPS data") except Exception as error: print(error) overall = 10000 try: overall = imei_vin_score * IMEI_VIN_SCORE_WEIGHT + fuel_score * FUEL_SCORE_WEIGHT + battery_score * BATTERY_VOLTAGE_SCORE_WEIGHT + odometer_score * ODOMETER_SCORE_WEIGHT + gps_null_score * GPS_SCORE_WEIGHT results.append([ vin, overall, imei_vin_score, fuel_score, battery_score, odometer_score, gps_null_score ]) tunnel.stop() return jsonify({'result': results}) except Exception as error: tunnel.stop() return jsonify({'result': "error"}) print("Global warning count: " + str(global_warning_count)) except Exception as error: tunnel.stop() return jsonify({'result': "error"})
def replicate_pb_chargebacks(self): app.logger.info('Starting replication on PB') account_name = config['pb_account_name'] last_id = select([ func.max(Chargebacks.c.original_id) ]).where(Chargebacks.c.account == account_name).execute().first() last_id = last_id[0] if last_id[0] else 0 chargebacks = [] with SSHTunnelForwarder( (config['pb_ssh_host'], int(config['pb_ssh_port'])), ssh_username=config['pb_ssh_username'], ssh_password=config['pb_ssh_password'], remote_bind_address=('127.0.0.1', 3306), local_bind_address=('127.0.0.1', 3307)) as server: connection_string = config['pb_connection_string'] + '?charset=utf8' source_engine = create_engine(connection_string) source_conn = source_engine.connect() sql = text( '''SELECT maxpay_chargeback_new.*, maxpay_charge_new.merchant_user_id FROM maxpay_chargeback_new JOIN maxpay_charge_new ON maxpay_chargeback_new.base_reference = maxpay_charge_new.reference WHERE maxpay_chargeback_new.id > :last_id ORDER BY maxpay_chargeback_new.id ASC LIMIT :count''') result = source_conn.execute(sql, last_id=int(last_id), count=50).fetchall() for chargeback in result: sql = text( '''SELECT temp_users.webid as webid, webid.country AS country FROM temp_users JOIN webid ON webid.web_id = temp_users.webid WHERE temp_users.cust_id = :customer_id''') user_info = source_conn.execute( sql, customer_id=chargeback['merchant_user_id']).first() if not user_info: sql = text( '''SELECT users.webid AS webid, webid.country AS country FROM users JOIN webid ON webid.web_id = users.webid WHERE users.customer_id = :customer_id''') user_info = source_conn.execute( sql, customer_id=chargeback['merchant_user_id']).first() if not user_info: user_info = dict(webid=None, country=None) chargeback_temp = dict(chargeback) chargeback_temp['webid'] = user_info['webid'] chargeback_temp['webid_country'] = user_info['country'].title( ) if user_info['country'] else None chargebacks.append(chargeback_temp) source_conn.close() values = [] for chargeback in chargebacks: app.logger.info('Inserting chargeback id = %i, account = %s' % (chargeback['id'], account_name)) created_at = chargeback['date_created'] - timedelta(hours=1) response = json.loads(chargeback['charge_response']) mid_name = response['transaction']['custom_fields'][ 'custom_mid_name'] card_brand = response['transaction']['card']['brand'] values.append( dict(account=account_name, merchant_user_id=chargeback['merchant_user_id'], webid=chargeback['webid'], country=chargeback['webid_country'], original_id=chargeback['id'], status=chargeback['status'], type=chargeback['type'], mode=chargeback['mode'], amount=chargeback['amount'], bank_time=chargeback['bank_time'], currency=chargeback['currency'], bank_id=chargeback['bank_id'], bank_authcode=chargeback['bank_authcode'], bank_update_time=chargeback['bank_update_time'], reference=chargeback['reference'], base_reference=chargeback['base_reference'], transaction_unique_id=chargeback['transaction_unique_id'], created_at=created_at, mid_name=mid_name, response=chargeback['charge_response'], time=chargeback['time'], brand=card_brand)) if values: db_conn.execute(Chargebacks.insert(), values) app.logger.info('Finished replication on PB')
def send_to_mysql( instance, data, replace=True, batch_size=1000, types=None, primary_key=(), create_boolean=False): """ data = { "table_name" : 'name_of_the_mysql_schema' + '.' + 'name_of_the_mysql_table' #Must already exist, "columns_name" : [first_column_name,second_column_name,...,last_column_name], "rows" : [[first_raw_value,second_raw_value,...,last_raw_value],...] } """ connection_kwargs = mysql_credentials.credential(instance) print("Initiate send_to_mysql...") print("Test to know if the table exists...") if (not create.existing_test(instance, data["table_name"])) or (types is not None) or (primary_key != ()): create_boolean = True print("Test to know if the table exists...OK") if create_boolean: create.create_table(instance, data, primary_key, types) # Create an SSH tunnel ssh_host = os.environ.get("SSH_%s_HOST" % instance) ssh_user = os.environ.get("SSH_%s_USER" % instance) ssh_path_private_key = os.environ.get("SSH_%s_PATH_PRIVATE_KEY" % instance) if ssh_host: tunnel = SSHTunnelForwarder( (ssh_host, 22), ssh_username=ssh_user, ssh_private_key=ssh_path_private_key, remote_bind_address=(os.environ.get("MYSQLDM_%s_HOST" % instance), 3306), local_bind_address=('localhost', 6543), # could be any available port ) # Start the tunnel try: tunnel.start() print("Tunnel opened!") except sshtunnel.HandlerSSHTunnelForwarderError: pass connection_kwargs["host"] = "localhost" connection_kwargs["port"] = 6543 con = pymysql.connect(**connection_kwargs) cursor = con.cursor() if replace: cleaning_request = '''DELETE FROM ''' + data["table_name"] + ''';''' print("Cleaning") cursor.execute(cleaning_request) print("Cleaning Done") boolean = True index = 0 total_nb_batchs = len(data["rows"]) // batch_size + 1 while boolean: temp_row = [] for i in range(batch_size): if not data["rows"]: boolean = False continue temp_row.append(data["rows"].pop()) final_data = [] for x in temp_row: for y in x: final_data.append(y) temp_string = ','.join(map(lambda a: '(' + ','.join(map(lambda b: '%s', a)) + ')', tuple(temp_row))) inserting_request = '''INSERT INTO ''' + data["table_name"] + ''' (''' + ", ".join( data["columns_name"]) + ''') VALUES ''' + temp_string + ''';''' if final_data: cursor.execute(inserting_request, final_data) index = index + 1 percent = round(index * 100 / total_nb_batchs, 2) if percent < 100: print("\r %s / %s (%s %%)" % (str(index), total_nb_batchs, str(percent)), end='\r') else: print("\r %s / %s (%s %%)" % (str(index), total_nb_batchs, str(percent))) con.commit() cursor.close() con.close() if ssh_host: tunnel.close() print("Tunnel closed!") print("data sent to mysql") return 0
import collections import itertools import sys import pymongo from bson import objectid from sshtunnel import SSHTunnelForwarder from scipy import stats as scipy_stats MONGO_IP = "ds_core_mongo" MONGO_KEYFILE = "dsworker_rsa" # SSH / Mongo Configuration # MONGO_SERVER = SSHTunnelForwarder((MONGO_IP, 22), ssh_username="******", ssh_pkey="~/.ssh/{0}".format(MONGO_KEYFILE), remote_bind_address=('localhost', 27017)) # stuff that eventually needs to come from config CONFIG = None MY_MODEL = None CONTEXT = None RDB = None SDB = None WINDOWING_POLICY = None def _check_compatibility_temporal(candidate_sets): # records are temporally-ordered # if there are enough to satisfy the current window, repackage the constituent DSIRs into a new DSAR window_begin, window_end = _quantize_temporal_window(
# !/usr/bin/env python3 # -*- coding: utf-8 -*- """ @Time : 2019/12/3 14:10 @Auth : 明明 @IDE : PyCharm """ import re import pymongo from openpyxl import Workbook from sshtunnel import SSHTunnelForwarder # ssh连接库 server = SSHTunnelForwarder( ssh_address_or_host=('115.159.127.155', 22), # ssh地址 ssh_username='******', # ssh连接的用户名 ssh_password='******', # ssh连接的用户名 remote_bind_address=('10.1.6.173', 28018), local_bind_address=('127.0.0.1', 10022)) with server: client = pymongo.MongoClient('127.0.0.1', 10022) db = client["KF"] collection = db["tmkf"] res = collection.find({ "chat_time": re.compile('2019-11-04') }).sort("customer") wb = Workbook() ws = wb.active for info in res: # .sort({"brand", "keyword", "type", "from_url"}) info.pop("_id")
from email.mime.base import MIMEBase from email import encoders import os import config today = date.today().strftime('%Y-%m-%d') ######################################################################################## ## Conexiones a la base de datos. ######################################################################################## #Conexión al Tunnel SSH server = SSHTunnelForwarder( (config.TUNNEL_SSH['host'], 22), ssh_username=config.TUNNEL_SSH['user'], ssh_password=config.TUNNEL_SSH['password'], remote_bind_address=(config.TUNNEL_SSH['rebind_address'], config.TUNNEL_SSH['rebind_address_port'])) print('SSH Tunnel created') server.start() # Conexión a la base de datos Postgresql local_port = str(server.local_bind_port) engine = create_engine('postgresql://{}:{}@{}:{}/{}'.format( config.DATABASE_CONFIG['user'], config.DATABASE_CONFIG['password'], config.DATABASE_CONFIG['host'], local_port, config.DATABASE_CONFIG['dbname'])) print('Engine create') # Ejecución del SP que crea la lista de clientes para el día de hoy. SP_execution = (pd.read_sql(
# -*- coding:utf-8 -*- from sshtunnel import SSHTunnelForwarder import pymysql.cursors with SSHTunnelForwarder( ('52.80.128.243', 22), # B机器的配置 ssh_username="******", ssh_pkey='D:/macys/Operation/macys-uat.pem', remote_bind_address=( 'macys-uat-mysql-new.ck31axgwtpkd.rds.cn-north-1.amazonaws.com.cn', 13306)) as server: # A机器的配置 connection = pymysql.connect(host='127.0.0.1', port=server.local_bind_port, user='******', passwd='4g8*V#my', db='wmb2c') with connection.cursor() as cursor: cursor.execute( "SELECT * FROM `tbl_om_order` where order_id='W1803261407283942'") print(cursor.fetchone()) connection.commit() with connection.cursor() as cursor: order = "'W1803261407283942'" sql = "SELECT * FROM tbl_om_order where order_id=%s" % order print(sql) cursor.execute(sql) print(cursor.fetchone()) connection.commit()
def plusitem(self): pfid1 = self.lineEdit_6.text() itemid = self.lineEdit_7.text() numid = self.lineEdit_8.text() if pfid1 == '': QtWidgets.QMessageBox.information(self, "Tips", self.tr("账号不能为空")) elif str(pfid1).isdigit() == False: QtWidgets.QMessageBox.information(self, "Tips", self.tr("账号只能为数字,请重新输入数字")) self.lineEdit_6.clear() if itemid == '': QtWidgets.QMessageBox.information(self, "Tips", self.tr("背包道具编号不能为空")) elif str(itemid).isdigit() == False: QtWidgets.QMessageBox.information(self, "Tips", self.tr("背包道具编号只能为数字,请重新输入数字")) self.lineEdit_7.clear() if numid == '': QtWidgets.QMessageBox.information(self, "Tips", self.tr("背包道具数量不能为空")) elif str(numid).isdigit() == False: QtWidgets.QMessageBox.information(self, "Tips", self.tr("背包道具数量只能为数字,请重新输入数字")) self.lineEdit_8.clear() else: with SSHTunnelForwarder( ('13.112.45.12', 22), # B机器的配置 ssh_pkey=file, # ssh_password=flie, ssh_username="******", remote_bind_address=(address, 3306)) as server: # A机器的配置 conn = pymysql.connect( host='127.0.0.1', # 此处必须是是127.0.0.1 port=server.local_bind_port, user=userid, passwd=password, # db='db_admin', charset='utf8') cursor = conn.cursor() sql = "select db_member_01.tb_user.pfid from db_member_01.tb_user where db_member_01.tb_user.pfid=" + pfid1 print(sql) cursor.execute(sql) result7 = cursor.fetchall() print(result7) conn.close() li = [] list3 = list(result7) li.append(list3) # print "+++++",type(li) li2 = str(li).replace("[", "").replace("]", "") print(li2) if li2 == '': QtWidgets.QMessageBox.information(self, "Tips", self.tr("账号不存在,请先注册")) self.lineEdit_6.clear() else: url2 = "https://api.s.lang.live/v2/bag/debug_add" data2 = { "pfid": int(str(pfid1)), "item_id": int(str(itemid)), "item_num": int(str(numid)) } print(data2) headers2 = { 'Content-Type': 'application/x-www-form-urlencoded' } r2 = requests.post(url=url2, data=data2, headers=headers2) print(r2.json()) with SSHTunnelForwarder( ('13.112.45.12', 22), # B机器的配置 ssh_pkey=file, # ssh_password=flie, ssh_username="******", remote_bind_address=(address, 3306)) as server: # A机器的配置 conn = pymysql.connect( host='127.0.0.1', # 此处必须是是127.0.0.1 port=server.local_bind_port, user=userid, passwd=password, # db='db_admin', charset='utf8') cursor = conn.cursor() # print type(li) print(itemid) sql = "select db_billing.tb_bag_item.item_type from db_billing.tb_bag_item where db_billing.tb_bag_item.id=" + itemid sql1 = "select db_billing.tb_bag_item.alert_desc from db_billing.tb_bag_item where db_billing.tb_bag_item.id=" + itemid print(sql) cursor.execute(sql) print("+++", sql) result0 = cursor.fetchall() print(result0) print(result0[0]) cursor.execute(sql1) result1 = cursor.fetchall() # conn.close() li = [] list1 = list(result0[0]) li.append(list1) # print "+++++",type(li) li2 = str(li) li3 = li2.replace("[", "").replace("]", "") if li3 == '3': li3 = '称号道具' if li3 == '2': li3 = '人气道具' if li3 == '1': li3 = '普通道具' print(li3) li1 = [] list2 = list(result1[0]) li1.append(list2) # print "+++++", type(li1) li4 = li1 print(li4) li5 = str(li4).replace("u", "").replace( "\'", "").replace(u"使用", "").replace(u"是否", "").replace( "[", "").replace("]", "").replace("?", "") print(li5) li6 = li5 conn.close() # conn.close() # print sql QtWidgets.QMessageBox.information( self, "Tips", self.tr('当前账号: ' + pfid1 + " \n道具名称为 " + li6 + " \n道具类型为 " + li3 + "\n增加数量为 " + numid + " 个"))
# coding=utf-8 import pymysql from sshtunnel import SSHTunnelForwarder with SSHTunnelForwarder(('139.196.173.61', 15555), ssh_username="******", ssh_password="******", remote_bind_address=('localhost', 3306)) as server: server.start() p = server.local_bind_port print(p) conn = pymysql.connect(host='127.0.0.1', port=p, user='******', password='******', db='hntx_gongqiu', charset='utf8', cursorclass=pymysql.cursors.DictCursor) cur = conn.cursor() cur.execute( """SELECT member_tel as '手机号',producing_place as '用苗地',product_name as '产品名称',spec_list as '产品规格',want_count as '需求数量' FROM gq_dys_want_to_buy left join hn_member on want_user_id=member_id WHERE want_date>1519488000""" ) re = cur.fetchall() print(re[9]) server.stop()
def liveid(self): pfid2 = self.lineEdit_9.text() if pfid2 == '': QtWidgets.QMessageBox.information(self, "Tips", self.tr("账号不能为空")) elif str(pfid2).isdigit() == False: QtWidgets.QMessageBox.information(self, "Tips", self.tr("账号只能为数字,请重新输入数字")) self.lineEdit_9.clear() else: with SSHTunnelForwarder( ('13.112.45.12', 22), # B机器的配置 ssh_pkey=file, # ssh_password=flie, ssh_username="******", remote_bind_address=(address, 3306)) as server: # A机器的配置 conn = pymysql.connect( host='127.0.0.1', # 此处必须是是127.0.0.1 port=server.local_bind_port, user=userid, passwd=password, # db='db_admin', charset='utf8') cursor = conn.cursor() print(pfid2) sql = "select db_global.tb_live_log.pfid from db_global.tb_live_log where db_global.tb_live_log.pfid=" + pfid2 + ' ORDER BY auto_id desc limit 1' sql1 = "select db_global.tb_live_log.live_id from db_global.tb_live_log where db_global.tb_live_log.pfid=" + pfid2 + ' ORDER BY auto_id desc limit 1' sql2 = "select db_global.tb_live_log.play_id from db_global.tb_live_log where db_global.tb_live_log.pfid=" + pfid2 + ' ORDER BY auto_id desc limit 1' print(sql) print(sql1) print(sql2) cursor.execute(sql) result0 = cursor.fetchall() cursor.execute(sql1) result1 = cursor.fetchall() cursor.execute(sql2) result2 = cursor.fetchall() print(result0) print(result1) print(result2) li = [] list1 = list(result0) li.append(list1) # print "+++++",type(li) li2 = str(li) li3 = li2.replace("[", "").replace("]", "") print('+++', li3) if li3 == '': QtWidgets.QMessageBox.information(self, "Tips", self.tr("账号不存在,请先注册")) self.lineEdit_9.clear() else: lii = [] list2 = list(result1[0]) lii.append(list2) # print "+++++",type(li) li4 = str(lii) li5 = li4.replace("[", "").replace("]", "").replace("\'", "") print(li5) liii = [] list3 = list(result2[0]) liii.append(list3) # print "+++++",type(li) li6 = str(liii) li7 = li6.replace("[", "").replace("]", "").replace("\'", "") print(li7) if li3 == 'None': self.lineEdit_10.setText(str(li7)) else: QtWidgets.QMessageBox.information( self, "Tips", self.tr("当前账号未在开播,请先开播\n上一次开播的live_id为" + li5)) self.lineEdit_10.clear()
fp.close() else: fp = open(path, 'rb') msg = MIMEBase(main_type, sub_type) msg.set_payload(fp.read()) fp.close() msg.add_header('Content-Disposition', 'attachment', filename=filename) message.attach(msg) return {'raw': base64.urlsafe_b64encode(message.as_string())} with SSHTunnelForwarder(("MONGO_SERVER_IP", 22), ssh_username="******", ssh_pkey="/home/USER/.ssh/KEYFILE", remote_bind_address=("localhost", 27017), local_bind_address=('0.0.0.0', 10022)) as tunnel: sleep(1) client = pymongo.MongoClient('0.0.0.0', port=10022) # code starts here gradebook = client['grades'] lab_num = '2' lab = 'lab' + lab_num lab = gradebook[lab] cursor = lab.find({ "graded": False, "comment": { "$exists": True },
import psycopg2 from sshtunnel import SSHTunnelForwarder with SSHTunnelForwarder( ('138.197.138.231', 22), #B机器的配置 ssh_password="******", ssh_username="******", remote_bind_address=('138.197.138.231', 5432)) as server: #A机器的配置 conn = psycopg2.connect(host='127.0.0.1', #此处必须是是127.0.0.1 port=server.local_bind_port, user='******', passwd='123456', db='test3') conn. # is_ssh = True # ssh_host = "138.197.138.231" # ssh_port = 22 # ssh_username = "******" # ssh_password = "******" # database = "saninco_realtor_db" # user = "******" # password = "******" # host = "localhost" # port = 5432 # dbname = 'test3'