Why does sqlalchemy work with insert from jupyter notebooks to snowflake and Python connector does not?

Question

Now that I have the connection working in the ipython3 notebook, I am testing inserting and selecting data with the Python connector. However when I was testing this, I was running into an issue with the correct method for inserting a dataframe from the Python connector, eventually I found that the sqlalchemy engine worked.

But now I am curious if I can use the Python connector as well. I have included my code below from the notebook. I do not understand the error message, when I looked it up I tried several of the attributesf from .to_sql. To be honest I got a bit lost in the errors and just went with the sqlalchemy method. If I needed to get the Python connection to work to insert or append data, have you seen this error before? I am sure it is just a coding error.

This method worked, but I kept getting errors with just the Python connection when trying df_to_sql:

from sqlalchemy import create_engine

#df_data
data = pd.read_csv("data/data.csv")
data.head()
df_data = pd.DataFrame(data)
df_data=df_data.rename(columns = {'Updated ?':'updated'})
df_data=df_data.rename(columns = {'Article Id':'article_id'})

engine = create_engine(URL(
    account = ACCOUNT,
    user = USER,
    password = PASSWORD,
    database = 'testdb',
    schema = 'public',
    warehouse = 'MYWH',
    role='ACCOUNTADMIN',
))
 
connection = engine.connect()
 
df_data.to_sql('testtb2', con=engine, index=False) #make sure index is False, Snowflake doesnt accept indexes
 
connection.close()
engine.dispose()

Second method with Python connector and:

import snowflake.connector

conn = snowflake.connector.connect(
  user=USER,
  password=PASSWORD,
  account=ACCOUNT)

conn.cursor().execute("create or replace table testtbl(article_id string, link string, status string)")
conn.cursor().execute("begin")
df_data.to_sql('testtbl', con=conn, schema ='testdb.testschema', dtype='varchar', method=None, if_exists='append',index=False) #make sure index is False, Snowflake doesnt accept indexes


conn.close()

---

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1594             else:
-> 1595                 cur.execute(*args)
   1596             return cur

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/snowflake/connector/cursor.py in execute(self, command, params, timeout, _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, _no_results, _use_ijson, _is_put_get, _raise_put_get_error, _force_put_overwrite)
    489                 if len(processed_params) > 0:
--> 490                     query = command % processed_params
    491                 else:

TypeError: not all arguments converted during string formatting

During handling of the above exception, another exception occurred:

DatabaseError                             Traceback (most recent call last)
<ipython-input-49-60b60380b42a> in <module>
     34 conn.cursor().execute("create or replace table testtbl(article_id string, link string, status string)")
     35 conn.cursor().execute("begin")
---> 36 df_data.to_sql('testtbl', con=conn, schema ='testdb.testschema', dtype='varchar', method=None, if_exists='append',index=False) #make sure index is False, Snowflake doesnt accept indexes
     37 
     38 ##use sqlalchemy instead

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2710             chunksize=chunksize,
   2711             dtype=dtype,
-> 2712             method=method,
   2713         )
   2714 

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
    516         chunksize=chunksize,
    517         dtype=dtype,
--> 518         method=method,
    519     )
    520 

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
   1747             dtype=dtype,
   1748         )
-> 1749         table.create()
   1750         table.insert(chunksize, method)
   1751 

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in create(self)
    639 
    640     def create(self):
--> 641         if self.exists():
    642             if self.if_exists == "fail":
    643                 raise ValueError(

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in exists(self)
    626 
    627     def exists(self):
--> 628         return self.pd_sql.has_table(self.name, self.schema)
    629 
    630     def sql_schema(self):

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in has_table(self, name, schema)
   1760         ).format(wld=wld)
   1761 
-> 1762         return len(self.execute(query, [name]).fetchall()) > 0
   1763 
   1764     def get_table(self, table_name, schema=None):

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1608                 "Execution failed on sql '{sql}': {exc}".format(sql=args[0], exc=exc)
   1609             )
-> 1610             raise_with_traceback(ex)
   1611 
   1612     @staticmethod

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/compat/__init__.py in raise_with_traceback(exc, traceback)
     45     if traceback == Ellipsis:
     46         _, _, traceback = sys.exc_info()
---> 47     raise exc.with_traceback(traceback)
     48 
     49 

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1593                 cur.execute(*args, **kwargs)
   1594             else:
-> 1595                 cur.execute(*args)
   1596             return cur
   1597         except Exception as exc:

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/snowflake/connector/cursor.py in execute(self, command, params, timeout, _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, _no_results, _use_ijson, _is_put_get, _raise_put_get_error, _force_put_overwrite)
    488                                  params, processed_params)
    489                 if len(processed_params) > 0:
--> 490                     query = command % processed_params
    491                 else:
    492                     query = command

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

Answer 1

As I see, Pandas to_sql function accepts "sqlalchemy.engine.Engine" and "sqlite3.Connection" objects as connection. When you try to use a snowflake connection object, it assumes that it's a sqlite3.Connection object, and therefore you get the following error:

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting

You need to use SQLAlchemy engine if you want to use Pandas to_sql function.