[最新更新] 感谢杰克的巨大帮助!!我设法连接到Cloud SQL postgres DB并将我的数据帧读/写到数据库中。但是，我仍然遇到以前遇到的相同错误，即...
struct.error："h"格式需要 -32768 <= 数字 <= 32767
当数据帧较小、紧凑且列中没有太多 NaN 值时，不会发生此错误。但是，当列中有许多 NaN 值时，程序会引发以下错误。
另外，我尝试使用df = df.fillna(0)用 0 填充 NaN 值。但它并没有很好地工作，同样的错误浮出水面。请帮忙！
Traceback (most recent call last):
File "...falcon_vbackupSTEP5_SavetoDB_and_SendEmail.py", line 81, in <module>
main_SavetoDB_and_SendEmail(
File "...falcon_vbackupSTEP5_SavetoDB_and_SendEmail.py", line 37, in main_SavetoDB_and_SendEmail
Write_Dataframe_to_SQLTable(
File "...falcon_vbackupAPPENDIX_Database_ReadWrite_v2.py", line 143, in Write_Dataframe_to_SQLTable
df_Output.to_sql(sql_tablename, con=conn, schema='public', index=False, if_exists=if_exists, method='multi', chunksize=1000)
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagespandascoregeneric.py", line 2963, in to_sql
return sql.to_sql(
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagespandasiosql.py", line 697, in to_sql
return pandas_sql.to_sql(
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagespandasiosql.py", line 1739, in to_sql
total_inserted = sql_engine.insert_records(
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagespandasiosql.py", line 1322, in insert_records
return table.insert(chunksize=chunksize, method=method)
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagespandasiosql.py", line 950, in insert
num_inserted = exec_insert(conn, keys, chunk_iter)
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagespandasiosql.py", line 873, in _execute_insert_multi
result = conn.execute(stmt)
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagessqlalchemyenginebase.py", line 1289, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagessqlalchemysqlelements.py", line 325, in _execute_on_connection
return connection._execute_clauseelement(
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagessqlalchemyenginebase.py", line 1481, in _execute_clauseelement
ret = self._execute_context(
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagessqlalchemyenginebase.py", line 1845, in _execute_context
self._handle_dbapi_exception(
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagessqlalchemyenginebase.py", line 2030, in _handle_dbapi_exception
util.raise_(exc_info[1], with_traceback=exc_info[2])
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagessqlalchemyutilcompat.py", line 207, in raise_
raise exception
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagessqlalchemyenginebase.py", line 1802, in _execute_context
self.dialect.do_execute(
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagessqlalchemyenginedefault.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagespg8000dbapi.py", line 455, in execute
self._context = self._c.execute_unnamed(
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagespg8000core.py", line 627, in execute_unnamed
self.send_PARSE(NULL_BYTE, statement, oids)
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagespg8000core.py", line 601, in send_PARSE
val.extend(h_pack(len(oids)))
struct.error: 'h' format requires -32768 <= number <= 32767
Exception ignored in: <function Connector.__del__ at 0x00000213190D8700>
Traceback (most recent call last):
File "c:Usersng_yj.condaenvsvenv_falconlibsite-packagesgooglecloudsqlconnectorconnector.py", line 167, in __del__
File "c:Usersng_yj.condaenvsvenv_falconlibconcurrentfutures_base.py", line 447, in result
concurrent.futures._base.TimeoutError:
我在GCP的Cloud SQL中设置了一个postgresql。我正在尝试使用 google.cloud.sql.connector. 我已从 GCP 控制台创建了一个服务帐户，并下载了 json 密钥。 我想使用服务帐户，凭据/密钥(以读取与我的 main.py 代码位于同一目录中的.json文件的格式)来验证对cloud_sql的访问。 我正在尝试进行身份验证，但不断收到一条错误消息，指出找不到服务帐户 json 文件。
任何人都可以帮助弄清楚如何解决此错误吗？谢谢！
import pandas as pd
from google.cloud.sql.connector import connector
import os
import pandas as pd
import pandas as pd
import sqlalchemy
import os
# configure Cloud SQL Python Connector properties
def getconn():
conn = connector.connect(
os.environ['LL_DB_INSTANCE_CONNECTION_NAME'],
"pg8000",
user=os.environ['LL_DB_USER'],
password=os.environ['LL_DB_PASSWORD'],
db=os.environ['LL_DB_NAME'])
return conn
# Show existing SQLTables within database
def Show_SQLTables_in_Database(conn):
if conn!=None:
# Show what tables remain in database
results = conn.execute("""SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'""").fetchall()
for table in results:
print(table)
if __name__=="__main__":
# Set the Google Application Credentials as environment variable
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = os.path.join(os.getcwd(),"Google-Credentials-LL-tech2.json")
# create connection pool to re-use connections
pool = sqlalchemy.create_engine("postgresql+pg8000://", creator=getconn)
with pool.connect() as db_conn:
# Show what tables remain in database
results = db_conn.execute("""SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'""").fetchall()
for table in results:
print(table)
您看到的错误意味着找不到.json文件。这很可能是由os.getcwd()引起的，它从中获取当前工作目录的路径，从中调用main.py。如果从父目录以外的任何位置调用文件，则会导致错误。
工作案例：python main.py
错误情况：python folder/main.py
将设置凭据的行更改为以下内容：
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = os.path.join(os.path.dirname(os.path.abspath(__file__)),"Google-Credentials-LL-tech2.json")
这将允许为调用main.py的所有情况正确设置凭据路径。
响应错误的最新更新。
首先，请确保服务帐户应用了云 SQL 客户端角色。
其次，尝试在自定义配置之前执行以下基本脚本，这将有助于将错误隔离到 Python 连接器或服务帐户/实现。
以下内容应该只是连接到您的数据库并打印时间。
from google.cloud.sql.connector import connector
import sqlalchemy
import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = os.path.join(os.path.dirname(os.path.abspath(__file__)),"GSheet-Credentials-LL-tech2.json")
# build connection for db using Python Connector
def getconn():
conn = connector.connect(
os.environ['LL_DB_INSTANCE_CONNECTION_NAME'],
"pg8000",
user=os.environ['LL_DB_USER'],
password=os.environ['LL_DB_PASSWORD'],
db=os.environ['LL_DB_NAME'],
)
return conn
# create connection pool
pool = sqlalchemy.create_engine("postgresql+pg8000://", creator=getconn)
def db_connect():
with pool.connect() as conn:
current_time = conn.execute(
"SELECT NOW()").fetchone()
print(f"Time: {str(current_time[0])}")
db_connect()
如果仍然出现错误，请提供错误的完整堆栈跟踪，以便我可以尝试使用更多信息进一步调试它。