我已经创建了一个云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地址,但是所有方法都不同:
- whatsmyip.org
- cmd命令
nslookup myip.opendns.com resolver1.opendns.com
所有人都不走运。
- 注:**
- 我没有勾选
Allow only SSL connections
框。所以,我想,没有必要包括CA证书。 - CloudSQL示例正在使用公共IP。
- 更新:**我添加了完整错误。
2条答案
按热度按时间3df52oht1#
要解决此问题,请检查以下内容-
1.确保在云SQL示例上启用了公共IP
1.然后,创建一个允许主机为
%
(所有IP)的用户。1.接下来,单击云SQL Jmeter 板左侧导航栏中的
Databases
按钮,确保创建了一个名为my_database
的数据库。1.确保在脚本中包含正确的
Cloud SQL Client
角色服务凭据。您应该能够连接。请让我知道,如果您面临任何问题,将相应地更新答案:)
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连接进行更新:
让我知道这些是否有助于解决你的连接,否则我可以为你更深入地研究:)