title: How to insert VARIANT or ARRAY data types into Snowflake with pandas and sqlalchemy
body: <p>I have a Pandas dataframe that contains several columns that are lists. I'd like to insert them into my Snowflake table as <code>ARRAY</code> data types.</p>
  
  <p>Traditionally, I've worked with PostgreSQL, and simply inserted as <code>df_to_insert.to_sql(TABLE_NAME, sqlalchemy_connection, **other_parameters)</code>.</p>
  
  <p>However, I'm struggling to insert into Snowflake. When I have a column that is a list, the sqlAlchemy Snowflake dialect believes it is a string:</p>
  
  <blockquote>
    <p>Expression type does not match column data type, expecting ARRAY but
    got VARCHAR(2) for column NAME_OF_COLUMN_WITH_LISTS</p>
  </blockquote>
  
  <p>I've tried given the sqlAlchemy engine a hint that the column is not a string by adding in explicit dtypes:</p>
  
  <pre><code>df_to_insert.to_sql("MY_SNOWFLAKE_TABLE_NAME",
                       snowflake_connection,
                       if_exists="append",
                       index=False,
                       dtype={'NAME_OF_COLUMN_WITH_LISTS': sqlalchemy.types.JSON})
  </code></pre>
  
  <p>This only gets me:</p>
  
  <blockquote>
    <p>sqlalchemy.exc.StatementError: (builtins.AttributeError)
    'SnowflakeDialect' object has no attribute '_json_serializer'</p>
  </blockquote>
  
  <p>What's the best way to insert data into Snowflake tables of variant / array types and still use pandas / sqlalchemy? Or is there no good way to do this yet?</p>