Using pandas.to_sql() and getting TypeError: not all arguments converted during string formatting

Question

I am trying to load a pandas DataFrame to a Snowflake database. I am using sqlalchemy to create the engine. Based in what other StackOverflow answers have suggested I am using with engine.begin() to try to upload this df.(pd_writer is an import from 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",
    )

This gives the following error:

   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

I don't even know what it's trying to convert.

Tried changing the type of connection used, tried different connection profiles, tried passing just the engine instead of the connection, checked the dtypes of the DataFrame and they're all objects, ints, or floats (one datetime).

Answer 1

It seems the problem is related to SQLAlchemy trying to connect to sqlite instead of connecting to Snowflake. As seen in your error log:

DatabaseError: Execution failed on sql '
        SELECT
            name
        FROM
            sqlite_master
        WHERE
            type IN ('table', 'view')
            AND name=?;

Let's research similar issues like:

• Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;':
• "SELECT name FROM sqlite_master" error while uploading DataFrame using .to_sql()

You didn't share the code for creating the string given to the engine, try checking that part.

engine = create_engine(sqlachemy_db_conn_string)

• https://docs.snowflake.com/en/developer-guide/python-connector/sqlalchemy