python connection to DB throwing error

Question

I am new to using python to connect to a mysql DB and I am getting the following error:

OperationalError: (pymysql.err.OperationalError) (1045, u"Access denied for user 'xxxxxxadmin'@'xx.xx.xx.xx' (using password: YES)") (Background on this error at: http://sqlalche.me/e/e3q8)
xx.xxx.216.44 - - [02/Apr/2018 17:27:49] "GET /testconnect HTTP/1.1" 500 -

This is most of the connect script in my python file:

   #!/usr/bin/python3
    from flask import Flask, request
    from flask_restful import Resource, Api
    from sqlalchemy import create_engine
    from json import dumps
    from flask.ext.jsonpify import jsonify

    db_connect = create_engine("mysql+pymysql://xxxxxxxadmin:password@,mymaindb.xxxxxxxx.us-east-2.rds.amazonaws.com:3306/myDBname")

    app = Flask(__name__)
    api = Api(app)

    class TestConnect(Resource):
        def get(self):
            conn = db_connect.connect() # connect to database
            query = conn.execute("select * from Players") # This line performs query and returns json result
            return {'employees': [i[0] for i in query.cursor.fetchall()]} # Fetches first column that is Employee ID

api.add_resource(TestConnect, '/testconnect') # Route_1



if __name__ == '__main__':
        app.run(host='0.0.0.0', debug = False)

Other background:

But when I try to connect to the same mysql database using the exact same credentials via the command line on the server running the python script I am able to get in.

Not sure how to test more to get a better error result that will help me figure this issue out.

UPDATE So I was able to connect to my DB via mysql workbench with the connection strings and information I have in the python script. Does this mean my python script is doing something wrong?

Your MySQL Host is rejecting connection from this host or your credential are wrong. Step 1 check if username and password are correct Step 2 Check if user is allowed to connect from this IP. — Rehan Azher
– Rehan Azher, Commented Apr 2, 2018 at 17:46
Step 3: actually connect from a MySQL client (e.g. the mysql CLI command). Do not proceed with Python code until you've made a canonical, known-to-work MySQL tool connect with the correct credentials. — 9000
– 9000, Commented Apr 2, 2018 at 17:49
@9000 does connecting using: mysql -h mymaindb.xxxxxxx.us-east-2.rds.amazonaws.com -P 3306 -u myDBname -p count as connecting from the command line? Because that does work and I get the mysql bash prompt after entering my password. — Denoteone
– Denoteone, Commented Apr 2, 2018 at 17:53
So I was able to connect to my DB via mysql workbench with the connection strings and information I have in the python script. I will update my question with that info. — Denoteone
– Denoteone, Commented Apr 2, 2018 at 18:08

fixatd · Accepted Answer · 2018-04-02 17:49:07Z

1

Why not use:

mysql+pymysql://xxxxxxxadmin:[email protected]:3306/myDBname

instead of

mysql+pymysql://xxxxxxxadmin:password@**,**mymaindb.xxxxxxxx.us-east-2.rds.amazonaws.com:3306/myDBname

Not sure why you're connection string has a comma. Might just be a typo?

On that note, I usually build the connection URL before passing it to create_engine just to make it easier to manage in the future incase I have to pull the actual values from the environmental variables:

HOST = "mymaindb.xxxxxxxx.us-east-2.rds.amazonaws.com"
PORT = 3306
USERNAME = "xxxxxxxadmin"
PASSWORD = "password"
DBNAME = "myDBname"

CONNECTION_URL = 'mysql+pymysql://%s:%s@%s:%s/%s' % (
    USERNAME,
    PASSWORD,
    HOST,
    PORT,
    DBNAME
)

answered Apr 2, 2018 at 17:49

fixatd

1,4041 gold badge13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Denoteone Over a year ago

I will build out my connection string in the future def cleaner. That was a typo with the comma. I will edit my question so there is no confusion.

Denoteone Over a year ago

I think it had something to do with a special character or a line end but when I used your method and saved it worked first try. thanks!

Collectives™ on Stack Overflow

python connection to DB throwing error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related