Problems using sqlAlchemy to execute SQL queries on my database









up vote
-1
down vote

favorite












I am working on a project to work on a website that connects to a mysql server using flask and sqlAlchemy (Hosted on AWS RDS) and I am following this tutorial but when I try to do (/api/bar) I get this error. When I just do my localhost:8080 it shows "Hello World" perfectly fine.




sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([WinError 10061] No connection could be made because the target machine actively refused it)") (Background on this error at: http://sqlalche.me/e/e3q8)




config.py
database_url = "mysql+pymysql://username:password@localhost:3306/barbeerdrinker"



Here is my __init__.py



from flask import Flask
from flask import jsonify
from flask import make_response
from flask import request
import json

from barbeerdrinker import database

#Flask application
app = Flask(__name__)

@app.route("/")
def hello_world():
return "Hello World"

@app.route("/api/bar", methods=["GET"])
def get_bars():
return jsonify(database.get_bars())

@app.route("/api/bar/<name>", methods=["GETS"])
def find_bar(name):
try:
if name is None:
raise ValueError("Bar is not specified")
bar = database.find_bar(name)
if bar is None:
return make_response("No bar found within given name", 404)
return jsonify(bar)
except ValueError as e:
return make_response(str(e), 400)
except Exception as e:
return make_response(str(e), 500)

@app.route("/api/beers_cheaper_than", methods=["POST"])
def find_beers_cheaper_than():
body = json.loads(request.data)
max_price = body['maxPrice']
return jsonify(database.filter_beers(max_price))


database.py



from sqlalchemy import create_engine
from sqlalchemy import sql

from barbeerdrinker import config

engine = create_engine(config.database_url)

def get_bars():
with engine.connect() as con:
rs = con.execute("SELECT name, address, city, opening, closing, phoneNum FROM bars")
return [dict(row) for row in rs]

def find_bar(name):
with engine.connect() as con:
query = sql.text("SELECT * FROM bars WHERE name = :name;")
rs = con.execute(query, name=name)
result = rs.first()
if result is None:
return None
return dict(result)

def filter_beers(max_price):
with engine.connect() as con:
query = sql.text("SELECT * FROM sells WHERE price < : max_price;")
rs = con.execute(query, max_price=max_price)
results = [dict(row) for row in rs]
for r in results:
r['price'] = float(r['price'])
return results


**Edit: So it seems like the problem is not an issue with my code but a Windows error. One solution I tried to do was to open up the required ports through my firewall to no avail.










share|improve this question























  • None of this code is relevant. Sounds like Windows is configured to reject requests on that port.
    – roganjosh
    Nov 10 at 21:53










  • I tried to open up those ports in my firewall to no avail
    – onbu
    Nov 10 at 21:55










  • Is this all local or is the DB hosted?
    – roganjosh
    Nov 10 at 21:57










  • DB is hosted on an AWS RDS server
    – onbu
    Nov 10 at 21:59










  • Ahaha, I just spotted barbeerdrinker. I don't know enough in this area to help you, sorry, but I'm 99.99% sure it's nothing to do with python, it's a Windows issue
    – roganjosh
    Nov 10 at 22:03














up vote
-1
down vote

favorite












I am working on a project to work on a website that connects to a mysql server using flask and sqlAlchemy (Hosted on AWS RDS) and I am following this tutorial but when I try to do (/api/bar) I get this error. When I just do my localhost:8080 it shows "Hello World" perfectly fine.




sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([WinError 10061] No connection could be made because the target machine actively refused it)") (Background on this error at: http://sqlalche.me/e/e3q8)




config.py
database_url = "mysql+pymysql://username:password@localhost:3306/barbeerdrinker"



Here is my __init__.py



from flask import Flask
from flask import jsonify
from flask import make_response
from flask import request
import json

from barbeerdrinker import database

#Flask application
app = Flask(__name__)

@app.route("/")
def hello_world():
return "Hello World"

@app.route("/api/bar", methods=["GET"])
def get_bars():
return jsonify(database.get_bars())

@app.route("/api/bar/<name>", methods=["GETS"])
def find_bar(name):
try:
if name is None:
raise ValueError("Bar is not specified")
bar = database.find_bar(name)
if bar is None:
return make_response("No bar found within given name", 404)
return jsonify(bar)
except ValueError as e:
return make_response(str(e), 400)
except Exception as e:
return make_response(str(e), 500)

@app.route("/api/beers_cheaper_than", methods=["POST"])
def find_beers_cheaper_than():
body = json.loads(request.data)
max_price = body['maxPrice']
return jsonify(database.filter_beers(max_price))


database.py



from sqlalchemy import create_engine
from sqlalchemy import sql

from barbeerdrinker import config

engine = create_engine(config.database_url)

def get_bars():
with engine.connect() as con:
rs = con.execute("SELECT name, address, city, opening, closing, phoneNum FROM bars")
return [dict(row) for row in rs]

def find_bar(name):
with engine.connect() as con:
query = sql.text("SELECT * FROM bars WHERE name = :name;")
rs = con.execute(query, name=name)
result = rs.first()
if result is None:
return None
return dict(result)

def filter_beers(max_price):
with engine.connect() as con:
query = sql.text("SELECT * FROM sells WHERE price < : max_price;")
rs = con.execute(query, max_price=max_price)
results = [dict(row) for row in rs]
for r in results:
r['price'] = float(r['price'])
return results


**Edit: So it seems like the problem is not an issue with my code but a Windows error. One solution I tried to do was to open up the required ports through my firewall to no avail.










share|improve this question























  • None of this code is relevant. Sounds like Windows is configured to reject requests on that port.
    – roganjosh
    Nov 10 at 21:53










  • I tried to open up those ports in my firewall to no avail
    – onbu
    Nov 10 at 21:55










  • Is this all local or is the DB hosted?
    – roganjosh
    Nov 10 at 21:57










  • DB is hosted on an AWS RDS server
    – onbu
    Nov 10 at 21:59










  • Ahaha, I just spotted barbeerdrinker. I don't know enough in this area to help you, sorry, but I'm 99.99% sure it's nothing to do with python, it's a Windows issue
    – roganjosh
    Nov 10 at 22:03












up vote
-1
down vote

favorite









up vote
-1
down vote

favorite











I am working on a project to work on a website that connects to a mysql server using flask and sqlAlchemy (Hosted on AWS RDS) and I am following this tutorial but when I try to do (/api/bar) I get this error. When I just do my localhost:8080 it shows "Hello World" perfectly fine.




sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([WinError 10061] No connection could be made because the target machine actively refused it)") (Background on this error at: http://sqlalche.me/e/e3q8)




config.py
database_url = "mysql+pymysql://username:password@localhost:3306/barbeerdrinker"



Here is my __init__.py



from flask import Flask
from flask import jsonify
from flask import make_response
from flask import request
import json

from barbeerdrinker import database

#Flask application
app = Flask(__name__)

@app.route("/")
def hello_world():
return "Hello World"

@app.route("/api/bar", methods=["GET"])
def get_bars():
return jsonify(database.get_bars())

@app.route("/api/bar/<name>", methods=["GETS"])
def find_bar(name):
try:
if name is None:
raise ValueError("Bar is not specified")
bar = database.find_bar(name)
if bar is None:
return make_response("No bar found within given name", 404)
return jsonify(bar)
except ValueError as e:
return make_response(str(e), 400)
except Exception as e:
return make_response(str(e), 500)

@app.route("/api/beers_cheaper_than", methods=["POST"])
def find_beers_cheaper_than():
body = json.loads(request.data)
max_price = body['maxPrice']
return jsonify(database.filter_beers(max_price))


database.py



from sqlalchemy import create_engine
from sqlalchemy import sql

from barbeerdrinker import config

engine = create_engine(config.database_url)

def get_bars():
with engine.connect() as con:
rs = con.execute("SELECT name, address, city, opening, closing, phoneNum FROM bars")
return [dict(row) for row in rs]

def find_bar(name):
with engine.connect() as con:
query = sql.text("SELECT * FROM bars WHERE name = :name;")
rs = con.execute(query, name=name)
result = rs.first()
if result is None:
return None
return dict(result)

def filter_beers(max_price):
with engine.connect() as con:
query = sql.text("SELECT * FROM sells WHERE price < : max_price;")
rs = con.execute(query, max_price=max_price)
results = [dict(row) for row in rs]
for r in results:
r['price'] = float(r['price'])
return results


**Edit: So it seems like the problem is not an issue with my code but a Windows error. One solution I tried to do was to open up the required ports through my firewall to no avail.










share|improve this question















I am working on a project to work on a website that connects to a mysql server using flask and sqlAlchemy (Hosted on AWS RDS) and I am following this tutorial but when I try to do (/api/bar) I get this error. When I just do my localhost:8080 it shows "Hello World" perfectly fine.




sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([WinError 10061] No connection could be made because the target machine actively refused it)") (Background on this error at: http://sqlalche.me/e/e3q8)




config.py
database_url = "mysql+pymysql://username:password@localhost:3306/barbeerdrinker"



Here is my __init__.py



from flask import Flask
from flask import jsonify
from flask import make_response
from flask import request
import json

from barbeerdrinker import database

#Flask application
app = Flask(__name__)

@app.route("/")
def hello_world():
return "Hello World"

@app.route("/api/bar", methods=["GET"])
def get_bars():
return jsonify(database.get_bars())

@app.route("/api/bar/<name>", methods=["GETS"])
def find_bar(name):
try:
if name is None:
raise ValueError("Bar is not specified")
bar = database.find_bar(name)
if bar is None:
return make_response("No bar found within given name", 404)
return jsonify(bar)
except ValueError as e:
return make_response(str(e), 400)
except Exception as e:
return make_response(str(e), 500)

@app.route("/api/beers_cheaper_than", methods=["POST"])
def find_beers_cheaper_than():
body = json.loads(request.data)
max_price = body['maxPrice']
return jsonify(database.filter_beers(max_price))


database.py



from sqlalchemy import create_engine
from sqlalchemy import sql

from barbeerdrinker import config

engine = create_engine(config.database_url)

def get_bars():
with engine.connect() as con:
rs = con.execute("SELECT name, address, city, opening, closing, phoneNum FROM bars")
return [dict(row) for row in rs]

def find_bar(name):
with engine.connect() as con:
query = sql.text("SELECT * FROM bars WHERE name = :name;")
rs = con.execute(query, name=name)
result = rs.first()
if result is None:
return None
return dict(result)

def filter_beers(max_price):
with engine.connect() as con:
query = sql.text("SELECT * FROM sells WHERE price < : max_price;")
rs = con.execute(query, max_price=max_price)
results = [dict(row) for row in rs]
for r in results:
r['price'] = float(r['price'])
return results


**Edit: So it seems like the problem is not an issue with my code but a Windows error. One solution I tried to do was to open up the required ports through my firewall to no avail.







python mysql flask sqlalchemy amazon-rds






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 10 at 22:47

























asked Nov 10 at 21:51









onbu

357




357











  • None of this code is relevant. Sounds like Windows is configured to reject requests on that port.
    – roganjosh
    Nov 10 at 21:53










  • I tried to open up those ports in my firewall to no avail
    – onbu
    Nov 10 at 21:55










  • Is this all local or is the DB hosted?
    – roganjosh
    Nov 10 at 21:57










  • DB is hosted on an AWS RDS server
    – onbu
    Nov 10 at 21:59










  • Ahaha, I just spotted barbeerdrinker. I don't know enough in this area to help you, sorry, but I'm 99.99% sure it's nothing to do with python, it's a Windows issue
    – roganjosh
    Nov 10 at 22:03
















  • None of this code is relevant. Sounds like Windows is configured to reject requests on that port.
    – roganjosh
    Nov 10 at 21:53










  • I tried to open up those ports in my firewall to no avail
    – onbu
    Nov 10 at 21:55










  • Is this all local or is the DB hosted?
    – roganjosh
    Nov 10 at 21:57










  • DB is hosted on an AWS RDS server
    – onbu
    Nov 10 at 21:59










  • Ahaha, I just spotted barbeerdrinker. I don't know enough in this area to help you, sorry, but I'm 99.99% sure it's nothing to do with python, it's a Windows issue
    – roganjosh
    Nov 10 at 22:03















None of this code is relevant. Sounds like Windows is configured to reject requests on that port.
– roganjosh
Nov 10 at 21:53




None of this code is relevant. Sounds like Windows is configured to reject requests on that port.
– roganjosh
Nov 10 at 21:53












I tried to open up those ports in my firewall to no avail
– onbu
Nov 10 at 21:55




I tried to open up those ports in my firewall to no avail
– onbu
Nov 10 at 21:55












Is this all local or is the DB hosted?
– roganjosh
Nov 10 at 21:57




Is this all local or is the DB hosted?
– roganjosh
Nov 10 at 21:57












DB is hosted on an AWS RDS server
– onbu
Nov 10 at 21:59




DB is hosted on an AWS RDS server
– onbu
Nov 10 at 21:59












Ahaha, I just spotted barbeerdrinker. I don't know enough in this area to help you, sorry, but I'm 99.99% sure it's nothing to do with python, it's a Windows issue
– roganjosh
Nov 10 at 22:03




Ahaha, I just spotted barbeerdrinker. I don't know enough in this area to help you, sorry, but I'm 99.99% sure it's nothing to do with python, it's a Windows issue
– roganjosh
Nov 10 at 22:03












1 Answer
1






active

oldest

votes

















up vote
0
down vote



accepted










I just figured it out, turns out the issue is not a windows issue. The problem is within my config.py:



Instead of:
database_url = "mysql+pymysql://username:password@localhost:3306/barbeerdrinker"



It should be:
database_url = "mysql+pymysql://username:password@**AWSENDPOINT/HOSTNAME**:3306/barbeerdrinker"






share|improve this answer






















    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













     

    draft saved


    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53243762%2fproblems-using-sqlalchemy-to-execute-sql-queries-on-my-database%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    0
    down vote



    accepted










    I just figured it out, turns out the issue is not a windows issue. The problem is within my config.py:



    Instead of:
    database_url = "mysql+pymysql://username:password@localhost:3306/barbeerdrinker"



    It should be:
    database_url = "mysql+pymysql://username:password@**AWSENDPOINT/HOSTNAME**:3306/barbeerdrinker"






    share|improve this answer


























      up vote
      0
      down vote



      accepted










      I just figured it out, turns out the issue is not a windows issue. The problem is within my config.py:



      Instead of:
      database_url = "mysql+pymysql://username:password@localhost:3306/barbeerdrinker"



      It should be:
      database_url = "mysql+pymysql://username:password@**AWSENDPOINT/HOSTNAME**:3306/barbeerdrinker"






      share|improve this answer
























        up vote
        0
        down vote



        accepted







        up vote
        0
        down vote



        accepted






        I just figured it out, turns out the issue is not a windows issue. The problem is within my config.py:



        Instead of:
        database_url = "mysql+pymysql://username:password@localhost:3306/barbeerdrinker"



        It should be:
        database_url = "mysql+pymysql://username:password@**AWSENDPOINT/HOSTNAME**:3306/barbeerdrinker"






        share|improve this answer














        I just figured it out, turns out the issue is not a windows issue. The problem is within my config.py:



        Instead of:
        database_url = "mysql+pymysql://username:password@localhost:3306/barbeerdrinker"



        It should be:
        database_url = "mysql+pymysql://username:password@**AWSENDPOINT/HOSTNAME**:3306/barbeerdrinker"







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 11 at 9:48









        SuperShoot

        1,426619




        1,426619










        answered Nov 11 at 1:06









        onbu

        357




        357



























             

            draft saved


            draft discarded















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53243762%2fproblems-using-sqlalchemy-to-execute-sql-queries-on-my-database%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            How to read a connectionString WITH PROVIDER in .NET Core?

            Node.js Script on GitHub Pages or Amazon S3

            Museum of Modern and Contemporary Art of Trento and Rovereto