qAp
Reputation: 1189
How to connect to an AWS RDS MySQL Aurora database from Jupyter notebook using sql line magic
I want to be able to connect to an AWS RDS MySQL Aurora database using the "sql" magic, with the syntax:
%sql mysql://username:password@hostname/dbname
and just write SQL into cells directly afterwards:
%%sql
SELECT *
FROM dbname.table_name
LIMIT 10
But I am getting an error with the connection. How can I make this work?
I have first checked that I can indeed establish a connection with the database from my computer some other way, to make sure there're no security measures that are blocking it in the first place. In a terminal, if I do:
mysql -h <RDS_ENDPOINT> --user <USERNAME> --password
I am prompted the password. After entering my password, <PASSWORD>, a mysql prompt opens, confirming that I can establish a connection to the database.
Now, in a Jupyter notebook running on the same computer, I first form the connection string that comes after the sql magic:
from sqlalchemy.engine import URL
connection_dict = {
'drivername': 'mysql',
'username': <USERNAME>,
'password': <PASSWORD>,
'host': <RDS_ENDPOINT>,
'port': 3306,
'database': 'reference'
}
connection_url = URL.create(**connection_dict)
The connection_url created is: mysql://<USERNAME>:***@<RDS_ENDPOINT>:3306/reference.
Then, I try to connect with:
%sql {connection_url}
But it doesn't work, getting the following error:
Traceback (most recent call last):
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3298, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
with util.safe_reraise():
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
self.__connect()
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
with util.safe_reraise():
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 646, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 622, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/MySQLdb/__init__.py", line 121, in Connect
return Connection(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/MySQLdb/connections.py", line 200, in __init__
super().__init__(*args, **kwargs2)
MySQLdb.OperationalError: (1045, "Access denied for user 'admin'@'<IP_ADDRESS>' (using password: YES)")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sql/magic.py", line 196, in execute
conn = sql.connection.Connection.set(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sql/connection.py", line 70, in set
cls.current = existing or Connection(descriptor, connect_args, creator)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sql/connection.py", line 55, in __init__
self.internal_connection = engine.connect()
^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3274, in connect
return self._connection_cls(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 148, in __init__
Connection._handle_dbapi_exception_noconnection(
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2439, in _handle_dbapi_exception_noconnection
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3298, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
with util.safe_reraise():
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
self.__connect()
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
with util.safe_reraise():
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 646, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 622, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/MySQLdb/__init__.py", line 121, in Connect
return Connection(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ubuntu/uroam_de/.venv/lib/python3.11/site-packages/MySQLdb/connections.py", line 200, in __init__
super().__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1045, "Access denied for user 'admin'@'<IP_ADDRESS>' (using password: YES)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Connection info needed in SQLAlchemy format, example:
postgresql://username:password@hostname/dbname
or an existing connection: dict_keys([])
I have used <IP_ADDRESS> above to mask the actual IP address.
Any ideas what the issue might be here? The driver name used I got it from this example: https://gist.github.com/ttadesusi/69224203c01ff107f735d66496bf26a2, but I think that is for MySQL (no Aurora).
Upvotes: 0
Views: 12
Answers (0)
Related Questions
- How to import an SQL file using the command line in MySQL?
- How can I get a list of user accounts using the command line in MySQL?
- sqlalchemy and airflow connection
- How do I connect to a MySQL Database in Python?
- SQLAlchemy db.create_all() Error, not creating db
- How can i connect this hello-flask-app with my database and create model in the database
- Getting sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: user.image_file
- How to access a non-Google MySQL server database (no Cloud SQL!) from Google Cloud Function in Python runtime using SQLAlchemy
- Zapier Mysql connection error