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'
