尝试使用云SQL Python连接器连接到云SQL时出现“WinError 10061”

6vl6ewon  于 2023-02-11  发布在  Python
关注(0)|答案(2)|浏览(150)

我已经创建了一个云SQL示例和一个新数据库。但是,我似乎无法使用Cloud SQL Python Connector连接到数据库。我已经按照文档中的示例代码和步骤操作,但仍然失败。
错误:

Traceback (most recent call last):
  File "c:\Users\my_name\my_path\gl_cloudsql_mysql_fx.py", line 149, in <module>
    print(bnm_data_db.isTableExist('MY_TABLE'))
  File "c:\Users\my_name\my_path\gl_cloudsql_mysql_fx.py", line 81, in isTableExist
    with self.__pool.connect() as db_conn:
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\engine\base.py", line 3245, in connect
    return self._connection_cls(self)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\engine\base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\engine\base.py", line 3269, in raw_connection
    return self.pool.connect()
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\base.py", line 455, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\base.py", line 1270, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\base.py", line 719, in checkout
    rec = pool._do_get()
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\impl.py", line 168, in _do_get
    with util.safe_reraise():
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\util\langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\impl.py", line 166, in _do_get
    return self._create_connection()
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\base.py", line 396, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\base.py", line 681, in __init__
    self.__connect()
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\base.py", line 905, in __connect
    with util.safe_reraise():
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\util\langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\base.py", line 901, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\sqlalchemy\pool\base.py", line 368, in <lambda>
    return lambda rec: creator_fn()
  File "c:\Users\my_name\my_path\gl_cloudsql_mysql_fx.py", line 27, in getconn
    conn: pymysql.connections.Connection = self.__connector.connect(
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\google\cloud\sql\connector\connector.py", line 154, in connect
    return connect_task.result()
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\concurrent\futures\_base.py", line 458, in result
    return self.__get_result()
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\concurrent\futures\_base.py", line 403, in __get_result
    raise self._exception
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\google\cloud\sql\connector\connector.py", line 261, in connect_async
    return await asyncio.wait_for(get_connection(), timeout)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\asyncio\tasks.py", line 445, in wait_for
    return fut.result()
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\google\cloud\sql\connector\connector.py", line 257, in get_connection
    return await self._loop.run_in_executor(None, connect_partial)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\concurrent\futures\thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\site-packages\google\cloud\sql\connector\pymysql.py", line 54, in connect
    socket.create_connection((ip_address, SERVER_PROXY_PORT)),
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\socket.py", line 845, in create_connection
    raise err
  File "C:\Users\my_name\Anaconda3\envs\composer-bis-gl-env\lib\socket.py", line 833, in create_connection
    sock.connect(sa)
ConnectionRefusedError: [WinError 10061] No connection could be made because the target machine actively refused it

我在本地机器上运行这个python代码(在Conda环境中)。
下面是我的代码:

from google.cloud.sql.connector import Connector
import sqlalchemy
import pymysql

connector = Connector()

# function to return the database connection
def getconn() -> pymysql.connections.Connection:
    conn: pymysql.connections.Connection = connector.connect(
      "my_project:my_location:my_instance",
      "pymysql",
      user="user",
      password="password",
      db="my_database",
    )
    return conn

# create connection pool
pool = sqlalchemy.create_engine(
  "mysql+pymysql://",
  creator=getconn,
)

# insert statement
check_table_statement = sqlalchemy.text(
  "SELECT count(*) from information_schema.tables",
)

with pool.connect() as db_conn:
  result = db_conn.execute(check_table_statement)
  print(result)

connector.close()

我尝试在云SQL示例中包含我的IP地址。但是,我不确定哪一个是正确的地址。我用多种方法检查了我的IP地址,但是所有方法都不同:

  1. Google
  2. whatsmyip.org
  3. cmd命令nslookup myip.opendns.com resolver1.opendns.com
    所有人都不走运。
    • 注:**
  • 我没有勾选Allow only SSL connections框。所以,我想,没有必要包括CA证书。
  • CloudSQL示例正在使用公共IP。
    • 更新:**我添加了完整错误。
3df52oht

3df52oht1#

要解决此问题,请检查以下内容-
1.确保在云SQL示例上启用了公共IP
1.然后,创建一个允许主机为%(所有IP)的用户。
1.接下来,单击云SQL Jmeter 板左侧导航栏中的Databases按钮,确保创建了一个名为my_database的数据库。
1.确保在脚本中包含正确的Cloud SQL Client角色服务凭据。
您应该能够连接。请让我知道,如果您面临任何问题,将相应地更新答案:)

h43kikqp

h43kikqp2#

为了解决ConnectionRefusedError问题,您可以尝试几件事。
1.确保在Google Cloud Project中启用了the Cloud SQL Admin API
1.确保您在环境中用于授权的IAM主体已被授予Cloud SQL Client Role
1.通过在Cloud SQL instance overview page上验证示例连接名称(传递给.connect方法的第一个参数),仔细检查它是否正确。
1.如果您要连接到云SQL示例私有IP,请确保您是从VPC网络内的计算机进行连接,并且您的代码已针对私有IP连接进行更新:

from google.cloud.sql.connector import Connector, IPTypes
import sqlalchemy
import pymysql

connector = Connector()

# function to return the database connection
def getconn() -> pymysql.connections.Connection:
    conn: pymysql.connections.Connection = connector.connect(
      "my_project:my_location:my_instance",
      "pymysql",
      user="user",
      password="password",
      db="my_database",
      ip_type=IPTypes.PRIVATE,
    )
    return conn

让我知道这些是否有助于解决你的连接,否则我可以为你更深入地研究:)

相关问题