postgresql - 云 sql 连接中的 Google 身份验证错误

Question

[最新更新] 感谢 Jack 的大力帮助!!!我设法连接到 Cloud SQL postgres 数据库并将我的数据帧读/写到数据库。但是，我仍然遇到与之前相同的错误，即...

struct.error: 'h' format requires -32768 <= number <= 32767

当数据帧较小、紧凑且列中没有太多 NaN 值时，不会发生此错误。但是，当列中有很多 NaN 值时，程序会抛出以下错误。

另外，我尝试使用 df = df.fillna(0) 将 NaN 值填充为 0。但效果不佳，并且出现了相同的错误。请帮忙!

Traceback (most recent call last):
  File "...\falcon_vbackup\STEP5_SavetoDB_and_SendEmail.py", line 81, in <module>
    main_SavetoDB_and_SendEmail(
  File "...\falcon_vbackup\STEP5_SavetoDB_and_SendEmail.py", line 37, in main_SavetoDB_and_SendEmail
    Write_Dataframe_to_SQLTable(
  File "...\falcon_vbackup\APPENDIX_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:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\pandas\core\generic.py", line 2963, in to_sql
    return sql.to_sql(
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\pandas\io\sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\pandas\io\sql.py", line 1739, in to_sql
    total_inserted = sql_engine.insert_records(
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\pandas\io\sql.py", line 1322, in insert_records
    return table.insert(chunksize=chunksize, method=method)
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\pandas\io\sql.py", line 950, in insert
    num_inserted = exec_insert(conn, keys, chunk_iter)
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\pandas\io\sql.py", line 873, in _execute_insert_multi
    result = conn.execute(stmt)
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\sqlalchemy\engine\base.py", line 1289, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\sqlalchemy\sql\elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _execute_clauseelement
    ret = self._execute_context(
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\sqlalchemy\engine\base.py", line 1845, in _execute_context
    self._handle_dbapi_exception(
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\sqlalchemy\engine\base.py", line 2030, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\sqlalchemy\engine\base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\sqlalchemy\engine\default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\pg8000\dbapi.py", line 455, in execute
    self._context = self._c.execute_unnamed(
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\pg8000\core.py", line 627, in execute_unnamed
    self.send_PARSE(NULL_BYTE, statement, oids)
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\pg8000\core.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:\Users\ng_yj\.conda\envs\venv_falcon\lib\site-packages\google\cloud\sql\connector\connector.py", line 167, in __del__
  File "c:\Users\ng_yj\.conda\envs\venv_falcon\lib\concurrent\futures\_base.py", line 447, in result
concurrent.futures._base.TimeoutError:

---

我在 GCP 的 Cloud SQL 中设置了一个 postgresql。我正在尝试使用连接到它谷歌.cloud.sql.connector。我已经从 GCP 控制台创建了一个服务帐户，并下载了 json key 。我想使用服务帐户、凭据/ key (以读取与我的 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)

Answer 1

您看到的错误意味着未找到 .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 的所有情况正确设置凭据路径。