Question 376736 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – I am trying to connect my Aiven database to python using the SQLalchemy module

from sqlalchemy import create_engine, text

db_connection_str = "mysql+pymysql://user:pass@some_mariadb/dbname?charset=utf8mb4"
#Don't worry while running the code , I had replaced all the values in the db_connection_str with my values

engine = create_engine(
    db_connection_str,
    connect_args={
        # "ssl": {
        #     "ssl_ca": "/etc/ssl/cert.pem",
        # }
        # "ssl": {
        #     "ca": "/home/gord/client-ssl/ca.pem",
        #     "cert": "/home/gord/client-ssl/client-cert.pem",
        #     "key": "/home/gord/client-ssl/client-key.pem"
        # }
        # I tried both of these methods and even without using connect_args but it still gives me an error
    })

with engine.connect() as conn:
    result = conn.execute(text("select * from jobs"))
    print(result.all())

This is my entire code, When I try running it while the ssl_ca is uncommented or when I have not entered the connect_args dictionary itself, this is the error I get:

Traceback (most recent call last):
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagespymysqlconnections.py", line 644, in connect
    sock = socket.create_connection(
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:Program FilesWindowsAppsPythonSoftwareFoundation.Python.3.11_3.11.2288.0_x64__qbz5n2kfra8p0Libsocket.py", line 851, in create_connection
    raise exceptions[0]
  File "C:Program FilesWindowsAppsPythonSoftwareFoundation.Python.3.11_3.11.2288.0_x64__qbz5n2kfra8p0Libsocket.py", line 836, in create_connection
    sock.connect(sa)
TimeoutError: timed out

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 3304, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 712, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolimpl.py", line 179, in _do_get
    with util.safe_reraise():
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyutillanghelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolimpl.py", line 177, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 390, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 674, in __init__
    self.__connect()
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 900, in __connect
    with util.safe_reraise():
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyutillanghelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginecreate.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginedefault.py", line 617, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagespymysqlconnections.py", line 358, in __init__
    self.connect()
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagespymysqlconnections.py", line 711, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'jovian-careers-db-jovian-careers.a.aivencloud.com' (timed out)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "E:GunkarCodingSelfFlaskJovian-Careers-Website-V2database.py", line 20, in <module>
    with engine.connect() as conn:
         ^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 3280, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 148, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 2444, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 3304, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 712, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolimpl.py", line 179, in _do_get
    with util.safe_reraise():
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyutillanghelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolimpl.py", line 177, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 390, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 674, in __init__
    self.__connect()
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 900, in __connect
    with util.safe_reraise():
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyutillanghelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginecreate.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginedefault.py", line 617, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagespymysqlconnections.py", line 358, in __init__
    self.connect()
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagespymysqlconnections.py", line 711, in connect
    raise exc
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'jovian-careers-db-jovian-careers.a.aivencloud.com' (timed out)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)

**And if I dont uncomment the ssl_ca thing and instead use the other method , then it just says this :

**

Traceback (most recent call last):
  File "E:GunkarCodingSelfFlaskJovian-Careers-Website-V2database.py", line 20, in <module>
    with engine.connect() as conn:
         ^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 3280, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginebase.py", line 3304, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 712, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolimpl.py", line 179, in _do_get
    with util.safe_reraise():
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyutillanghelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolimpl.py", line 177, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 390, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 674, in __init__
    self.__connect()
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 900, in __connect
    with util.safe_reraise():
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyutillanghelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemypoolbase.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginecreate.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagessqlalchemyenginedefault.py", line 617, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagespymysqlconnections.py", line 289, in __init__
    self.ctx = self._create_ssl_ctx(ssl)
               ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersravinAppDataLocalPackagesPythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0LocalCachelocal-packagesPython311site-packagespymysqlconnections.py", line 373, in _create_ssl_ctx
    ctx = ssl.create_default_context(cafile=ca, capath=capath)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:Program FilesWindowsAppsPythonSoftwareFoundation.Python.3.11_3.11.2288.0_x64__qbz5n2kfra8p0Libssl.py", line 770, in create_default_context
    context.load_verify_locations(cafile, capath, cadata)
FileNotFoundError: [Errno 2] No such file or directory

I have mentioned whatever I tried above and what I was expecting is to get the information from one of my tables "jobs" but python didnt even reach that line.

Also this is the tutorial I am following : "https://www.youtube.com/watch?v=yBDHkveJUf4&list=PLiAbjvVKX4Q-7ygi3CYBd8QOES3A3lyqd&index=2&t=8184s&ab_channel=freeCodeCamp.org"

start watching from 2:45:00

He found his solution to this same problem as he was using another service for his web database called "PlanetScale", I did not use that because I found out it is paid now and the free service is no longer available.

Also later I am going to integrate this in my flask website as per the tutorial

Tags:

2

Answers


  1. 0

    Download the CA certificate from your service provider in this case. After downloading the CA certificate using your python code, create a dictionary like this:

    ssl_args = {
    'ssl': {
        'sslmode': 'REQUIRED',
        'ca': '/path/to/ca-cert.pem'  # Replace '/path/to/ca-cert.pem' with the actual path to your CA cert file
    }
}

    now connect to your sqlalchemy engine:

    create_engine(db_connect, connection_args=ssl_args)
    Login or Signup to reply.
  2. 0

    I also had the same issue with aiven.io MySql. I tried a small tweak and it worked. I am sharing the code I have used, you can use this with your account information.

    • First, you must add ca.pem certificate from aiven.io in the connection_args. So download it and add it to your project folder.
    • Now add your user_name, password, host_name, port no, and database_name provided by Aiven. Make sure you use an accurate file path of ca.pem.
    # Create connection string    
db_connection_str = "mysql+pymysql://user_name:password@host_name:port/database_name?charset=utf8mb4"
        
# Create the engine
engine = create_engine(db_connection_str, connect_args={
            "ssl": {
                "ca": "<your_file_path>/ca.pem",  
            },
        })
    • If you follow the above code with your own aiven.io account info, I think it would work.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search