I am running into a strange error: I am able to successfully access my wordpress SQL database using SSH tunnel inside my Flask app like this:
import json
from turtle import position
from flask import Flask, request, jsonify
import pandas as pd
import numpy as np
import xgboost as xgb
from xgboost.sklearn import XGBRegressor
from itertools import combinations
import pickle
from flaskext.mysql import MySQL
from sshtunnel import SSHTunnelForwarder
from phpserialize import *
from os.path import expanduser
import paramiko
app = Flask(__name__)
mypkey = paramiko.RSAKey.from_private_key_file(r"/home/joeyengelberg/mysite/id_rsa")
sql_hostname = 'XXX.X.X.X'
sql_username = 'XXXXXXXX'
sql_password = 'XXXXXXXXXXXXXXX'
sql_main_database = 'XXXXXXX'
sql_port = 3306
ssh_host = 'XXXX.XXX.XXXXXX.XXX'
ssh_user = 'XXXXXXX'
ssh_port = 22
sql_ip = '1.1.1.1.1'
tunnel = SSHTunnelForwarder(
(ssh_host, ssh_port),
ssh_username=ssh_user,
ssh_pkey=mypkey,
remote_bind_address=(sql_hostname, sql_port))
#Start Tunnel
tunnel.start()
mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = sql_username
app.config['MYSQL_DATABASE_PASSWORD'] = sql_password
app.config['MYSQL_DATABASE_DB'] = 'XXXXXXX'
app.config['MYSQL_DATABASE_HOST'] = '127.0.0.1'
app.config['MYSQL_DATABASE_PORT'] = tunnel.local_bind_port
mysql.init_app(app)
conn = mysql.connect()
cursor = conn.cursor()
post_key = "1912"
cursor.execute("SELECT meta_value FROM wp_postmeta WHERE post_id = %s AND meta_key = 'sp_statistics', post_key)
data = cursor.fetchone()
output = unserialize(bytes(data[0], 'utf-8'))
conn.close()
def Merge(dict1, dict2):
res = {**dict1, **dict2}
return res
@app.route('/')
def hello():
resp = jsonify({"message":"honey"})
resp.status_code = 200
return resp
@app.route('/predict', methods = ['POST','GET'])
def predict():
if request.method == 'POST':
resp = jsonify({"message":"post"})
resp.status_code = 200
return resp
if request.method == 'GET':
try:
post_key = request.args.get("id")
resp = jsonify(post_key)
resp.status_code = 200
return resp
except Exception as e:
print(e)
else:
resp = jsonify({"message":"Something went wrong"})
resp.status_code = 405
return resp
I can confirm that the above code works because when I print(output) I get exactly what I want from the SQL database. HOWEVER, when I move the cursor.execute commands inside the if request.method == 'GET': block (which is what I want to do) it no longer works. Specifically, when I go to my web app site and set the id value to 1912 like this:
http://joeyengelberg.pythonanywhere.com/predict?id=1912
the web app just hangs and eventually gives a 504-backend error. Specifically, this code will not work:
import json
from turtle import position
from flask import Flask, request, jsonify
import pandas as pd
import numpy as np
import xgboost as xgb
from xgboost.sklearn import XGBRegressor
from itertools import combinations
import pickle
from flaskext.mysql import MySQL
from sshtunnel import SSHTunnelForwarder
from phpserialize import *
from os.path import expanduser
import paramiko
app = Flask(__name__)
mypkey = paramiko.RSAKey.from_private_key_file(r"/home/joeyengelberg/mysite/id_rsa")
sql_hostname = 'XXX.X.X.X'
sql_username = 'XXXXXXXX'
sql_password = 'XXXXXXXXXXXXXXX'
sql_main_database = 'XXXXXXX'
sql_port = 3306
ssh_host = 'XXXX.XXX.XXXXXX.XXX'
ssh_user = 'XXXXXXX'
ssh_port = 22
sql_ip = '1.1.1.1.1'
tunnel = SSHTunnelForwarder(
(ssh_host, ssh_port),
ssh_username=ssh_user,
ssh_pkey=mypkey,
remote_bind_address=(sql_hostname, sql_port))
#Start Tunnel
tunnel.start()
mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = sql_username
app.config['MYSQL_DATABASE_PASSWORD'] = sql_password
app.config['MYSQL_DATABASE_DB'] = 'XXXXXXX'
app.config['MYSQL_DATABASE_HOST'] = '127.0.0.1'
app.config['MYSQL_DATABASE_PORT'] = tunnel.local_bind_port
mysql.init_app(app)
conn = mysql.connect()
cursor = conn.cursor()
def Merge(dict1, dict2):
res = {**dict1, **dict2}
return res
@app.route('/')
def hello():
resp = jsonify({"message":"honey"})
resp.status_code = 200
return resp
@app.route('/predict', methods = ['POST','GET'])
def predict():
if request.method == 'POST':
resp = jsonify({"message":"post"})
resp.status_code = 200
return resp
if request.method == 'GET':
try:
post_key = request.args.get("id")
cursor.execute("SELECT meta_value FROM wp_postmeta WHERE post_id = %s AND meta_key='sp_statistics', post_key)
data = cursor.fetchone()
output = unserialize(bytes(data[0], 'utf-8'))
conn.close()
resp = jsonify(output)
resp.status_code = 200
return resp
except Exception as e:
print(e)
else:
resp = jsonify({"message":"Something went wrong"})
resp.status_code = 405
return resp
Any idea why accessing the SQL database outside of the if block works just fine but inside the if block fails?
joeyengelberg
|
3
posts
|
pafk
|
2973
posts
|
PythonAnywhere staff
|
