无法通过Pandas向Snowflake创建表：SSH密钥连接报错求助

尝试通过SSH密钥对连接将Pandas数据集推送至Snowflake，执行代码时出现错误。已确认该用户在Snowflake工作台具备创建表的权限，仅通过Python连接时触发此问题。

执行代码 #

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
from snowflake.connector.pandas_tools import pd_writer

from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives.asymmetric import rsa
from cryptography.hazmat.primitives.asymmetric import dsa
from cryptography.hazmat.primitives import serialization

import pandas as pd
df_app = pd.DataFrame([[1,2],[3,4]],columns=['A','B'])


passcode = 'xxxxxxx'

with open(r"C:\Users\rsa_key.p8", "rb") as key:
    p_key= serialization.load_pem_private_key(
        key.read(),
        password=passcode.encode(),
        backend=default_backend()
    )

pkb = p_key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption())

engine = create_engine(URL(
    account='snowflake_account',
    warehouse='snowflake_warehouse',
    database='snowflake_db',
    schema='snowlfake_workspace',
    user='snowflake_user'
    ),
    connect_args={
        'private_key': pkb,
    }
    )


df_app.to_sql(name='test_connect1'.lower(), con=engine, if_exists='replace', method=pd_writer,index=False)

报错信息 #

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1704                 if not evt_handled:
-> 1705                     self.dialect.do_execute(
   1706                         cursor, statement, parameters, context

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py in do_execute(self, cursor, statement, parameters, context)
    691     def do_execute(self, cursor, statement, parameters, context=None):
-> 692         cursor.execute(statement, parameters)
    693 

~\AppData\Roaming\Python\Python38\site-packages\snowflake\connector\cursor.py in execute(self, command, params, _bind_stage, timeout, _exec_async, _no_retry, _do_reset, _put_callback, _put_azure_callback, _put_callback_output_stream, _get_callback, _get_azure_callback, _get_callback_output_stream, _show_progress_bar, _statement_params, _is_internal, _describe_only, _no_results, _is_put_get, _raise_put_get_error, _force_put_overwrite, file_stream)
    803             error_class = IntegrityError if is_integrity_error else ProgrammingError
-> 804             Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
    805         return self

~\AppData\Roaming\Python\Python38\site-packages\snowflake\connector\errors.py in errorhandler_wrapper(connection, cursor, error_class, error_value)
    275 
-> 276         handed_over = Error.hand_to_other_handler(
    277             connection,

~\AppData\Roaming\Python\Python38\site-packages\snowflake\connector\errors.py in hand_to_other_handler(connection, cursor, error_class, error_value)
    330             cursor.messages.append((error_class, error_value))
-> 331             cursor.errorhandler(connection, cursor, error_class, error_value)
    332             return True

~\AppData\Roaming\Python\Python38\site-packages\snowflake\connector\errors.py in default_errorhandler(connection, cursor, error_class, error_value)
    209         """
-> 210         raise error_class(
    211             msg=error_value.get("msg"),

ProgrammingError: 090105 (22000): Cannot perform CREATE TABLE. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.


~\AppData\Roaming\Python\Python38\site-packages\snowflake\connector\errors.py in hand_to_other_handler(connection, cursor, error_class, error_value)
    329         if cursor is not None:
    330             cursor.messages.append((error_class, error_value))
-> 331             cursor.errorhandler(connection, cursor, error_class, error_value)
    332             return True
    333         elif connection is not None:

~\AppData\Roaming\Python\Python38\site-packages\snowflake\connector\errors.py in default_errorhandler(connection, cursor, error_class, error_value)
    208             A Snowflake error.
    209         """
-> 210         raise error_class(
    211             msg=error_value.get("msg"),
    212             errno=error_value.get("errno"),

ProgrammingError: (snowflake.connector.errors.ProgrammingError) 090105 (22000): Cannot perform CREATE TABLE. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.
[SQL: 
CREATE TABLE test_connect1 (
    "A" BIGINT, 
    "B" BIGINT
)

]

解决建议 #

• 修正连接参数的拼写错误：代码中schema参数值写成了snowlfake_workspace，存在笔误（少了字母k），应改为snowflake_workspace。错误的Schema名称会导致连接会话无法关联到指定数据库，进而触发创建表时的上下文错误。
• 使用全限定表名：在to_sql方法中指定表名时，使用数据库名.模式名.表名的全限定格式，例如：

  df_app.to_sql(name='snowflake_db.snowflake_workspace.test_connect1'.lower(), con=engine, if_exists='replace', method=pd_writer,index=False)
  

• 显式设置会话上下文：创建连接后，手动执行USE语句指定当前数据库和Schema，确保会话上下文正确：

  with engine.connect() as conn:
      conn.execute("USE DATABASE snowflake_db")
      conn.execute("USE SCHEMA snowflake_workspace")
      df_app.to_sql(name='test_connect1'.lower(), con=conn, if_exists='replace', method=pd_writer,index=False)
  

内容的提问来源于stack exchange，提问作者kalaiyarasi Uma