def log_request(req: 'flask_request', res: str) -> None:
_host = 'irock.enroute.xyz'
_ssh_port = 22
_username = '******'
_password = '******'
_remote_bind_address = 'localhost'
_local_mysql_port = 9990
_local_bind_address = '127.0.0.1'
_remote_mysql_port = 3306
_db_user = '******'
_db_password = '******'
_db_name = 'Mario'
_SQL = """insert into log (phrase, letters, ip, browser_string, results) values (%s, %s, %s, %s, %s)"""
with sshtunnel.SSHTunnelForwarder(
(_host, _ssh_port),
ssh_username=_username,
ssh_password=_password,
remote_bind_address=(_remote_bind_address, _remote_mysql_port),
local_bind_address=(_local_bind_address,
_local_mysql_port)) as tunnel:
conn = mysql.connector.connect(user=_db_user,
password=_db_password,
host=_local_bind_address,
database=_db_name,
port=_local_mysql_port)
cursor = conn.cursor()
cursor.execute(_SQL, (
req.form['phrase'],
req.form['letters'],
req.remote_addr,
req.user_agent.browser,
res,
))
conn.commit()
cursor.close()
conn.close()
_SQL = "SELECT * FROM log;"
with sshtunnel.SSHTunnelForwarder(
(_host, _ssh_port),
ssh_username=_username,
ssh_password=_password,
remote_bind_address=(_remote_bind_address, _remote_mysql_port),
local_bind_address=(_local_bind_address,
_local_mysql_port)) as tunnel:
conn = mysql.connector.connect(user=_db_user,
password=_db_password,
host=_local_bind_address,
database=_db_name,
port=_local_mysql_port)
cursor = conn.cursor()
cursor.execute(_SQL)
res = cursor.fetchall()
for row in res:
print(row)
def insert_fail(x) -> None:
_host = 'irock.enroute.xyz'
_ssh_port = 22
_username = '******'
_password = '******'
_remote_bind_address = 'localhost'
_local_mysql_port = 9990
_local_bind_address = '127.0.0.1'
_remote_mysql_port = 3306
_db_user = '******'
_db_password = '******'
_db_name = 'Mario'
_SQL = """insert into nochido (Address, Status) values (%s, %s)"""
with sshtunnel.SSHTunnelForwarder(
(_host, _ssh_port),
ssh_username=_username,
ssh_password=_password,
remote_bind_address=(_remote_bind_address, _remote_mysql_port),
local_bind_address=(_local_bind_address,
_local_mysql_port)) as tunnel:
conn = mysql.connector.connect(user=_db_user,
password=_db_password,
host=_local_bind_address,
database=_db_name,
port=_local_mysql_port)
cursor = conn.cursor()
for i in x:
tmp = i.split('|')
cursor.execute(_SQL, (tmp[0], tmp[1]))
conn.commit()
cursor.close()
conn.close()
_SQL = "SELECT * FROM nochido;"
with sshtunnel.SSHTunnelForwarder(
(_host, _ssh_port),
ssh_username=_username,
ssh_password=_password,
remote_bind_address=(_remote_bind_address, _remote_mysql_port),
local_bind_address=(_local_bind_address,
_local_mysql_port)) as tunnel:
conn = mysql.connector.connect(user=_db_user,
password=_db_password,
host=_local_bind_address,
database=_db_name,
port=_local_mysql_port)
cursor = conn.cursor()
cursor.execute(_SQL)
res = cursor.fetchall()
for row in res:
print(row)
def connect(number, sql):
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username='******',
ssh_password='******',
remote_bind_address=(
'mrfrankiekklee.mysql.pythonanywhere-services.com',
3306)) as tunnel:
connection = mysql.connector.connect(
user='******',
password='******',
host='127.0.0.1',
port=tunnel.local_bind_port,
database='mrfrankiekklee$default',
)
if number == 0:
cursor = connection.cursor()
cursor.execute(sql)
query = cursor.fetchone()
print(query)
return query
elif number == 1:
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
elif number == 2:
cursor = connection.cursor(dictionary=True)
cursor.execute(sql)
query = cursor.fetchall()
print(query)
return query
def remote_connect(self, ssh_host, ssh_user, ssh_pass, db_host, db_port, db_user, db_pass, db_name):
sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
self.conn_inactivity = int(self.conn_inactivity)
self.server = sshtunnel.SSHTunnelForwarder(
(ssh_host),
ssh_username=ssh_user, ssh_password=ssh_pass,
remote_bind_address=(db_host, db_port)
)
# stops hanging connections in transport
self.server.daemon_forward_servers = True
self.server.daemon_transport = True
self.server.start()
self.conn = pymysql.connect(
user=db_user, password=db_pass,
host='127.0.0.1', port=self.server.local_bind_port,
database=db_name,
)
if self.conn is not None:
self.conn.autocommit = True
else:
self.server.stop()
raise ISPyBConnectionException
self.last_activity_ts = time.time()
def run_insert_query_pythonanywhere(self, query_list: list):
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com', 22),
ssh_username='******',
ssh_password='******',
remote_bind_address=(
'countrynomics.mysql.pythonanywhere-services.com',
3306)) as tunnel:
connection = pymysql.connect(host='127.0.0.1',
user='******',
passwd='countsql@',
db='countrynomics$new_indicator',
port=tunnel.local_bind_port)
try:
with connection.cursor() as cursor:
i = 0
for q in query_list:
print(i)
cursor.execute(query=q)
connection.commit()
i = i + 1
print(i)
print('Successful!')
except:
time.sleep(60)
self.run_insert_query_pythonanywhere(query_list)
finally:
connection.close()
return 'successfully uploaded'
def delete_watchlist(user_id: str, title: Optional[str] = None):
with sshtunnel.SSHTunnelForwarder(
("129.74.154.187", 22),
ssh_username=settings.sshuser,
ssh_password=settings.sshpass,
remote_bind_address=("127.0.0.1", 3306)) as tunnel:
config = {
'user': "******",
'password': settings.dbpass,
'host': "127.0.0.1",
'port': tunnel.local_bind_port,
'database': "smanfred"
}
connection = pymysql.connect(**config)
cursor = connection.cursor(pymysql.cursors.DictCursor)
if title:
# Delete just one show from watchlist when title is specified
cursor.execute(user_watchlist_remove_show % (title, user_id))
else:
# Clear entire watchlist when no title specified
cursor.execute(user_watchlist_remove_all % user_id)
connection.commit()
connection.close()
def submit_job_to_db(algo_argu_list):
# job_ID is the database file name
num_job = len(algo_argu_list)
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username=PYTHONANYWHERE_USERNAME,
ssh_password=PYTHONANYWHERE_PASSWORD,
remote_bind_address=(DATABASE_HOSTNAME, 3306)) as tunnel:
mydb = pymysql.connect(user=PYTHONANYWHERE_USERNAME,
password=DATABASE_PASSWORD,
host='127.0.0.1',
port=tunnel.local_bind_port,
database=DATABASE_NAME)
i = 0
for job in algo_argu_list:
print(i, num_job)
i = i + 1
algorithm = job[0]
arguments = job[1]
create_db_command = '''CREATE TABLE IF NOT EXISTS jobs(
job_ID VARCHAR(200) NOT NULL,
algorithm VARCHAR(200) NOT NULL,
arguments VARCHAR(400) NOT NULL,
finished TINYINT(1) NOT NULL,
n_evals INT NOT NULL,
PRIMARY KEY ( job_ID )
);'''
mycursor = mydb.cursor()
mycursor.execute(create_db_command)
sql = "INSERT IGNORE INTO jobs (job_ID, algorithm, arguments, finished, n_evals) VALUES (%s, %s, %s, %s, %s)"
db_path = get_db_path(arguments)
val = (db_path, algorithm, arguments, 0, -1)
mycursor.execute(sql, val)
mydb.commit()
mydb.close()
def makePlayerDF():
sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username='******',
ssh_password='******',
remote_bind_address=('skoyron.mysql.pythonanywhere-services.com',
3306)) as tunnel:
connection = mysql.connector.connect(
user='******',
password='******',
host='127.0.0.1',
port=tunnel.local_bind_port,
database='skoyron$default',
)
count = 0
df = pd.read_sql_query('select name from dailyDon group by name;',
connection)
# print (df['name'])
for name in df['name']:
# print(name, 'ciao')
count += 1
print(count)
dfname = pd.read_sql_query(
'select * from dailyDon where name="' + name + '";',
connection)
plot(dfname, name)
# print(stop the loop)
connection.close()
def sqlExecute(sql, params, type):
# 使用sshtunnel跳转访问外网
with sshtunnel.SSHTunnelForwarder(
('10.37.20.204', 22),
ssh_username='******',
ssh_password='******',
remote_bind_address=(
'rm-wz9ol46kro5h2gbjqzo.mysql.rds.aliyuncs.com', 3306),
local_bind_address=('127.0.0.1', 13306)) as tunnel:
# 打开数据库连接
db = pymysql.connect(host="127.0.0.1",
port=13306,
user="******",
password="******",
charset="utf8",
database="testdb")
cursor = db.cursor()
if type == 'fetchone':
cursor.execute(sql)
result = cursor.fetchone()
print(result)
elif type == 'fetchall':
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
print(row)
elif type == 'executemany':
result = cursor.executemany(sql, params)
print(result)
else:
result = cursor.execute(sql, params)
print(result)
db.commit()
db.close()
def local_get_df(query):
sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username=passwords.ssh_username, ssh_password=passwords.ssh_password,
remote_bind_address=(passwords.host, 3306)
) as tunnel:
#print('server run')
connection = pymysql.connect(
user=passwords.user, password=passwords.password,
host='127.0.0.1', port=tunnel.local_bind_port,
database=passwords.db,
)
#print('connected')
c = connection.cursor()
c.execute(query)
df = pd.read_sql_query(query, con=connection)
c.close()
connection.close()
return df
def __init__(self, ssh, ssh_host, ssh_user, ssh_password, host,
database_name, user, password, user_id):
self.ssh = ssh
self.ssh_host = ssh_host
self.ssh_user = ssh_user
self.ssh_password = ssh_password
self.host = host
self.database_name = database_name
self.user = user
self.password = password
self.user_id = user_id
#use ssh tunnel
self.tunnel = sshtunnel.SSHTunnelForwarder(
(ssh),
ssh_username=ssh_user,
ssh_password=ssh_password,
remote_bind_address=(ssh_host, 3306))
self.tunnel.start()
#connect with database
self.connection = mysql.connector.connect(
user=user,
password=password,
host=host,
port=self.tunnel.local_bind_port,
database=database_name,
autocommit=True)
def create_ssh_connection(self):
tunnel = sshtunnel.SSHTunnelForwarder(
'ssh.pythonanywhere.com',
ssh_username=self.user,
ssh_password=self.pw,
remote_bind_address=(self.db_host, self.port_db))
return tunnel
def update_services(sshuser, sshpass, dbpass, acctID, service):
with sshtunnel.SSHTunnelForwarder(
("129.74.154.187", 22),
ssh_username=sshuser,
ssh_password=sshpass,
remote_bind_address=("127.0.0.1", 3306)) as tunnel:
config = {
'user': "******",
'password': dbpass,
'host': "127.0.0.1",
'port': tunnel.local_bind_port,
'database': "smanfred"
}
connection = pymysql.connect(**config)
cursor = connection.cursor(pymysql.cursors.DictCursor)
userQuery = get_user_query.replace(":acctID", acctID)
cursor.execute(userQuery)
result = cursor.fetchone()
# Append the new desired service
currServices = result["services"]
currServices += f",{service}"
# Write the new desired service back to DB
updateQuery = update_user_services.replace(":services", currServices)
updateQuery = updateQuery.replace(":acctID", acctID)
print(updateQuery)
cursor.execute(updateQuery)
connection.commit()
connection.close()
def connect_to_info(self, connection: ConnectionInfo):
self.running_query.emit(True)
self.disconnect()
self.info.emit(str(f'Connecting to: {connection}...'))
if connection.ssh_host and connection.ssh_user:
self.tunnel_server = sshtunnel.SSHTunnelForwarder(
(connection.ssh_host, connection.ssh_port),
ssh_username=connection.ssh_user,
ssh_pkey=path.expanduser('~/.ssh/id_rsa'),
remote_bind_address=(connection.host, connection.port))
try:
self.tunnel_server.start()
except Exception as err:
self.error.emit(str(err))
return
try:
self.c = MySQLdb.connect(
host=connection.host
if self.tunnel_server is None else '127.0.0.1',
user=connection.user,
password=connection.get_password(),
port=connection.port if self.tunnel_server is None else
self.tunnel_server.local_bind_port,
cursorclass=MySQLdb.cursors.DictCursor)
except Exception as err:
self.error.emit(str(err))
return
self.connection = connection
self.info.emit(str(f'Connected to: {connection}.'))
self.running_query.emit(False)
self.job.emit('db_list', '', '', {})
self.run_query(
'SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED')
def __enter__(self):
if self.cluster:
ssh_info = self.credentials.machine_credentials()
forward = sshtunnel.SSHTunnelForwarder(
ssh_info['ssh_address'],
ssh_username=ssh_info['username'],
ssh_password=ssh_info['password'],
remote_bind_address=('127.0.0.1', 5432))
forward.start()
self.forward = forward
self.pgsql_port = forward.local_bind_port
pgsql_string = prepare_connection(
self.credentials.cluster_cmbp_postgresql_credentials(),
str(self.pgsql_port))
else:
self.forward = None
self.pgsql_port = ''
pgsql_string = prepare_connection(
self.credentials.cmbp_postgresql_credentials(),
str(self.pgsql_port))
self.pgsql_string = pgsql_string
self.conn = psycopg2.connect(**pgsql_string)
self.conn.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
self.cur = self.conn.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
return self
def tunnel(remote_host, remote_port, local_port=0): # `0` means random port
if tb.is_pyinstaller():
# https://pyinstaller.readthedocs.io/en/stable/runtime-information.html
script_dir = sys.executable
else:
script_dir = __file__
# config file in same directory as this file
config_file = pathlib.Path(script_dir).with_name(ini_file)
config.read(config_file)
try:
section = config[remote_host]
except KeyError:
# remote host is not in ini file, so we don't need a real tunnel
tunnel = MockTunnel(
local_bind_host = remote_host,
local_bind_port = remote_port
)
else:
tunnel = sshtunnel.SSHTunnelForwarder(
ssh_address_or_host = section['proxy_host'],
ssh_config_file = None,
ssh_port = int(section['proxy_port']),
ssh_username = section['proxy_user'],
remote_bind_address = (section['remote_host'], int(remote_port)),
local_bind_address = ('localhost', local_port)
)
return tunnel
def __enter__(self):
"""Enter method."""
try:
if main_config.db_ssh_forward:
forward = sshtunnel.SSHTunnelForwarder(
credentials.machine_credentials()['ssh_address'],
ssh_username=credentials.machine_credentials()['username'],
ssh_password=credentials.machine_credentials()['password'],
remote_bind_address=('127.0.0.1', 5432))
forward.start()
self.forward = forward
self.pgsql_port = forward.local_bind_port
else:
self.forward = None
self.pgsql_port = ''
pgsql_string = credentials.postgresql_connection(
str(self.pgsql_port))
self.pgsql_string = pgsql_string
self.conn = psycopg2.connect(**pgsql_string)
self.conn.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
self.cur = self.conn.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
except Exception as e:
self.close_db()
if main_config.db_ssh_forward:
self.forward.close()
print(e)
return self
def serve():
global tunnel
tunnel = sshtunnel.SSHTunnelForwarder(server, **tunnel_params)
tunnel.start()
while tunnel.is_active:
thread.wait_or_exit(timeout=10)
def query(q):
with sshtunnel.SSHTunnelForwarder(
('bluenose.cs.dal.ca', 22),
ssh_username=os.environ['SSH_USER'],
ssh_password=os.environ['SSH_PASS'],
remote_bind_address=('db.cs.dal.ca', 3306),
local_bind_address=('127.0.0.1', 3307)
) as tunnel:
conv = pymysql.converters.conversions.copy()
conv[246] = float # convert decimals to floats
connection = pymysql.connect(host='127.0.0.1',
port=3307,
user=os.environ['MYSQL_USER'],
password=os.environ['MYSQL_PASS'],
db='csci4140_group8',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor,
conv=conv)
try:
with connection.cursor() as cursor:
cursor.execute(q)
result = cursor.fetchall()
finally:
connection.close()
return result
def sql_calls(choice, field=None, i_sql=None, file_o=None):
#pass_wd = get_password()
sql = i_sql
_host = "produtil01.ipsoft.ny1"
_ssh_port = 22
_username = '******'
_password = "******" #pass_wd
_local_bind_address = '0.0.0.0'
_local_mysql_port = 12345
_remote_bind_address = "ipdb-s"
_remote_mysql_port = 3306
with sshtunnel.SSHTunnelForwarder(
(_host, _ssh_port),
ssh_username=_username,
ssh_password=_password,
remote_bind_address=(_remote_bind_address, _remote_mysql_port),
local_bind_address=(_local_bind_address,
_local_mysql_port)) as tunnel:
connection = pymysql.connect(host='localhost',
port=12345,
user='******',
password='******',
db='IPradar')
try:
with connection.cursor() as cursor:
sql, file_o, file_i, path2script, args = sql_queries(
choice, field, i_sql, file_o)
sql_runn(cursor, sql, file_o, file_i, path2script, args)
finally:
connection.close()
def __init__(self, env, db=None, filename=SSH_KEY_PATH):
"""env: environment. Can be one of the following:
- Environment 1: ENV1
- Environment 2: ENV2
- Environment 3: ENV3
"""
self.env = env
## Access user and psw for the selected environment
self.env_user = config[env]["user"]
self.env_psw = config[env]["psw"]
db = db or config[env].get('db')
if not db:
raise ValueError("No DB Specified.")
self.db = db
self.local_port = str(config[self.env]["local_port"])
self.connection = None
## Create rsa_key object
self.key = paramiko.RSAKey.from_private_key_file(filename=filename)
## Create sshtunnel object
fwd = config["MySQLfwd"]
self.ssh_connection = sshtunnel.SSHTunnelForwarder(
(fwd["ip"], fwd["remote_port"]),
ssh_pkey=self.key,
ssh_username=fwd["user"],
remote_bind_address=(config[env]["ip"], config[env]["remote_port"]),
local_bind_address=("127.0.0.1", config[env]["local_port"])
)
def create_tunnel():
tunnel = sshtunnel.SSHTunnelForwarder(
(SSH_HOST),
ssh_username=SSH_USER,
ssh_password=SSH_PASS,
remote_bind_address=(SQL_HOSTNAME, SQL_PORT))
return tunnel
def _connect(self):
assert self.state == _WorkerStates.INITIALIZING
ssh_address = self._instance.get_ssh_address()
remote_port = self._vast_params.instance_params.remote_port
self._tunnel = sshtunnel.SSHTunnelForwarder(
ssh_address,
remote_bind_address=("127.0.0.1", remote_port),
ssh_username="******",
compression=True,
#
# mute_exceptions=False,
mute_exceptions=True,
#
# This is in seconds.
set_keepalive=15.0,
)
self._tunnel.start()
if not self._is_tunnel_good_to_go():
raise _ConnectFailedException(
"Failed to create SSH tunnel to VastAI worker."
)
self._conn = connection.Client(("127.0.0.1", self._tunnel.local_bind_port))
def __init__(self, stand):
response = urllib2.urlopen(
'http://{}.dev.magonline.ru/sql.html'.format(stand))
sql_port = int(response.read())
password_file = open("ssh.password", 'r')
ssh_password = password_file.read()
password_file.close()
self.server = sshtunnel.SSHTunnelForwarder(
("dev.magdv.com", 42244),
ssh_host_key=None,
ssh_username="******",
ssh_password=None,
ssh_private_key="OpenSsh",
ssh_private_key_password=ssh_password,
remote_bind_address=("localhost", sql_port))
self.server.start()
self.cnx = mysql.connector.connect(user='******',
password='******',
host='127.0.0.1',
port=self.server.local_bind_port,
database='magonline')
self.cursor = self.cnx.cursor()
def __init__(self,
hostname,
username,
path_to_key=None,
port=22,
via_hostname=None,
via_port=22,
local_port=1234):
self.hostname = hostname
self.username = username
self.port = port
self.path_to_key = path_to_key
self.via_hostname = via_hostname
self.via_port = via_port
self.local_port = local_port
ssh_address = (self.via_hostname, self.via_port)
ssh_private_key = os.path.expanduser(self.path_to_key)
ssh_private_key = paramiko.RSAKey.from_private_key_file(
ssh_private_key)
local_bind_address = ("localhost", self.local_port)
remote_bind_address = (self.hostname, self.port)
self.client = sshtunnel.SSHTunnelForwarder(
ssh_address=ssh_address,
ssh_username=self.username,
ssh_private_key=ssh_private_key,
local_bind_address=local_bind_address,
remote_bind_address=remote_bind_address)
def connect_to_db_via_ssh(ssh_info, db_info):
"""
Connects to a remote PostgreSQL db, via SSH tunnel.
Args:
ssh_info (obj): All ssh connection info.
db_info (obj): All db related connection info.
Returns:
:class:`psycopg2.extensions.connection`: Live connection suitable for queries.
"""
tunnel = sshtunnel.SSHTunnelForwarder(
ssh_info.host,
ssh_private_key=ssh_info.ssh_private_key,
ssh_username=ssh_info.ssh_username,
remote_bind_address=ssh_info.remote_bind_address)
tunnel.start()
logger.info("SSH tunnel connected.")
conn = psycopg2.connect(database=db_info.db_name,
user=db_info.db_user,
password=db_info.db_password,
host=tunnel.local_bind_host,
port=tunnel.local_bind_port)
logger.info("Postgres database %s connected" % db_info.db_name)
return conn
def connect_to_db(query=""): #username='', password=''):
with sshtunnel.SSHTunnelForwarder(
('nova.cs.tau.ac.il', 22),
ssh_username=USERNAME,
ssh_password=PASSWORD,
remote_bind_address=("mysqlsrv1.cs.tau.ac.il", 3306),
local_bind_address=("127.0.0.1", 3307)) as tunnel:
con = mdb.connect(
host='127.0.0.1', # your host, usually localhost
user=DB_USERNAME, # your username
passwd=DB_PASSWORD, # your password
db=DB_NAME,
port=3307) # name of the data base
cur = con.cursor(mdb.cursors.DictCursor)
if query == "":
print "ERROR: no query in input"
cur.close()
return None
try:
print("************* QUERY **************")
print(query)
print("*********** END QUERY ************")
cur.execute(query)
ans = cur.fetchall()
except Exception as e:
print "ERROR: couldn't execute and fetch from db:", e
cur.close()
return None
cur.close()
return ans
def getSessionServer():
#"""
user = '******'
ssh_user = '******'
passwssh = "6CmINL2eRPo%baH"
passw = 'GiME4OI9'
dbName = 'sec2'
localhost = '127.0.0.1'
port = 3307
host = "200.16.7.185"
tunnel = sshtunnel.SSHTunnelForwarder(
(host, 22),
ssh_password=passwssh,
ssh_username=ssh_user,
remote_bind_address=(localhost, 3306))
tunnel.start()
app.config[
'SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(
user, passw, localhost, tunnel.local_bind_port, dbName)
#"""
#app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:holi123@localhost:3306/florestack'
app.config['SQLALCHEMY_POOL_SIZE'] = 5
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 30
app.config['SQLALCHEMY_POOL_RECYCLE'] = 31
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
return db
def insert_ok(x) -> None:
_host = 'irock.enroute.xyz'
_ssh_port = 22
_username = '******'
_password = '******'
_remote_bind_address = 'localhost'
_local_mysql_port = 9990
_local_bind_address = '127.0.0.1'
_remote_mysql_port = 3306
_db_user = '******'
_db_password = '******'
_db_name = 'Mario'
_SQL = """insert into ok (Formatted_Addres, Status, Latitude, Longitude) values (%s, %s, %s, %s)"""
with sshtunnel.SSHTunnelForwarder(
(_host, _ssh_port),
ssh_username=_username,
ssh_password=_password,
remote_bind_address=(_remote_bind_address, _remote_mysql_port),
local_bind_address=(_local_bind_address,
_local_mysql_port)) as tunnel:
conn = mysql.connector.connect(user=_db_user,
password=_db_password,
host=_local_bind_address,
database=_db_name,
port=_local_mysql_port)
cursor = conn.cursor()
cursor.execute(_SQL, (x[0], x[1], x[2], x[3]))
conn.commit()
cursor.close()
conn.close()
_SQL = "SELECT * FROM ok;"
def __init__(self, need_ssh, config):
if need_ssh:
sshtunnel.SSH_TIMEOUT = float(config['ssh']['ssh_timeout'])
sshtunnel.TUNNEL_TIMEOUT = float(config['ssh']['tunnel_timeout'])
self.ssh_server = sshtunnel.SSHTunnelForwarder((config['ssh']['host'], int(config['ssh']['port'])),
ssh_username=config['ssh']['username'],
ssh_password=config['ssh']['password'],
remote_bind_address=(config['ssh']['db_remote_bind_address'],
int(config['ssh'][
'db_remote_mysql_port'])),
local_bind_address=(config['ssh']['db_local_bind_address'],
int(config['ssh'][
'db_local_mysql_port'])))
self.ssh_server.start()
self.db = mysql.connector.connect(
user=config['db']['user'],
password=config['db']['pass'],
host=config['ssh']['db_local_bind_address'],
database=config['db']['name'],
port=config['ssh']['db_local_mysql_port'])
else:
self.db = MySQLdb.connect(host=config['local']['host'], user=config['db']['user'],
passwd=config['db']['pass'], db=config['db']['name'])
print("Connected to DB " + ("(local)" if need_ssh else "(server)"))
self.analytics_table = config['db']['analytics_table']
self.price_table = config['db']['price_table']
self.index_table = config['db']['index_table']
self._check_analytics_table()