使用pandas.to_sql()时出现TypeError: 不是所有参数都在字符串格式化中转换

Question

我正在尝试把一个 pandas 数据框（DataFrame）加载到 Snowflake 数据库里。我使用 sqlalchemy 来创建连接引擎。根据其他 StackOverflow 上的回答，我用 with engine.begin() 来尝试上传这个数据框。（pd_writer 是从 snowflake.connector.pandas_tools 导入的）

engine = create_engine(sqlachemy_db_conn_string)

with engine.begin() as current_connection:
    df_copy.to_sql(
         name= sf_tbl_name,
         con= current_connection.connection,
         index= False,
         method= pd_writer,
         schema= sf_schema_name,
         if_exists= "replace",
    )

这时出现了以下错误：

   1159 with conn.begin() as current_connection:
-> 1160     df_copy.to_sql(
   1161         name= sf_tbl_name,
   1162         con= current_connection.connection,
   1163         index= False,
   1164         method= pd_writer,
   1165         schema= sf_schema_name,
   1166         if_exists = "replace",
   1167         )

File ~/.conda/envs/kernel/lib/python3.11/site-packages/pandas/util/_decorators.py:333, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
    327 if len(args) > num_allow_args:
    328     warnings.warn(
    329         msg.format(arguments=_format_argument_list(allow_args)),
    330         FutureWarning,
    331         stacklevel=find_stack_level(),
    332     )
--> 333 return func(*args, **kwargs)

File ~/.conda/envs/kernel/lib/python3.11/site-packages/pandas/core/generic.py:3084, in NDFrame.to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2886 """
   2887 Write records stored in a DataFrame to a SQL database.
   2888 
   (...)
   3080 [(1,), (None,), (2,)]
   3081 """  # noqa: E501
   3082 from pandas.io import sql
-> 3084 return sql.to_sql(
   3085     self,
   3086     name,
   3087     con,
   3088     schema=schema,
   3089     if_exists=if_exists,
   3090     index=index,
   3091     index_label=index_label,
   3092     chunksize=chunksize,
   3093     dtype=dtype,
   3094     method=method,
   3095 )

File ~/.conda/envs/kernel/lib/python3.11/site-packages/pandas/io/sql.py:842, in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)
    837     raise NotImplementedError(
    838         "'frame' argument should be either a Series or a DataFrame"
    839     )
    841 with pandasSQL_builder(con, schema=schema, need_transaction=True) as pandas_sql:
--> 842     return pandas_sql.to_sql(
    843         frame,
    844         name,
    845         if_exists=if_exists,
    846         index=index,
    847         index_label=index_label,
    848         schema=schema,
    849         chunksize=chunksize,
    850         dtype=dtype,
    851         method=method,
    852         engine=engine,
    853         **engine_kwargs,
    854     )

File ~/.conda/envs/kernel/lib/python3.11/site-packages/pandas/io/sql.py:2848, in SQLiteDatabase.to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)
   2837             raise ValueError(f"{col} ({my_type}) not a string")
   2839 table = SQLiteTable(
   2840     name,
   2841     self,
   (...)
   2846     dtype=dtype,
   2847 )
-> 2848 table.create()
   2849 return table.insert(chunksize, method)

File ~/.conda/envs/kernel/lib/python3.11/site-packages/pandas/io/sql.py:984, in SQLTable.create(self)
    983 def create(self) -> None:
--> 984     if self.exists():
    985         if self.if_exists == "fail":
    986             raise ValueError(f"Table '{self.name}' already exists.")

File ~/.conda/envs/kernel/lib/python3.11/site-packages/pandas/io/sql.py:970, in SQLTable.exists(self)
    969 def exists(self):
--> 970     return self.pd_sql.has_table(self.name, self.schema)

File ~/.conda/envs/kernel/lib/python3.11/site-packages/pandas/io/sql.py:2863, in SQLiteDatabase.has_table(self, name, schema)
   2852 wld = "?"
   2853 query = f"""
   2854 SELECT
   2855     name
   (...)
   2860     AND name={wld};
   2861 """
-> 2863 return len(self.execute(query, [name]).fetchall()) > 0

File ~/.conda/envs/kernel/lib/python3.11/site-packages/pandas/io/sql.py:2684, in SQLiteDatabase.execute(self, sql, params)
   2681     raise ex from inner_exc
   2683 ex = DatabaseError(f"Execution failed on sql '{sql}': {exc}")
-> 2684 raise ex from exc

DatabaseError: Execution failed on sql '
        SELECT
            name
        FROM
            sqlite_master
        WHERE
            type IN ('table', 'view')
            AND name=?;
        ': not all arguments converted during string formatting

我甚至不知道它在尝试转换什么。

我尝试过更改连接类型，试过不同的连接配置，尝试只传递引擎而不是连接，检查了数据框的类型，它们都是对象、整数或浮点数（还有一个是日期时间）。

sqlalchemy pandas snowflake database_connection data_frame type_error data_upload pandas_tools

使用pandas.to_sql()时出现TypeError: 不是所有参数都在字符串格式化中转换

1 个回答

撰写回答