title: Unable to insert data into Snowflake database table using pandas to_sql() method
body: <p>I have a database <code>SFOPT_TEST</code> on my Snowflake instance. The database has two schemas <code>AUDITS</code> and <code>PARAMS</code>.</p>
  <p>The schema <code>AUDITS</code> has a table created like this using SQLAlchemy <code>declarative_base()</code>-</p>
  <pre><code>class AccountUsageLoginHistory(Base):
      
      '''
      This model will store the account parameters of the customers instances.
      '''
  
      __tablename__ = constants.TABLE_ACCOUNT_USAGE_LOGIN_HISTORY
      __table_args__ = {
          'schema' : os.environ.get('SCHEMA_NAME_AUDITS')
      }
  
      id = Column(Integer, Sequence('id_login_history'), primary_key=True, autoincrement=True)
      event_id = Column(Integer, nullable=True)
      event_timestamp = Column(TIMESTAMP, nullable=True)
      event_type = Column(String(100), nullable=True)
      user_name = Column(String(100), nullable=True)
      client_ip = Column(String(100), nullable=True)
      reported_client_type = Column(String(100), nullable=True)
      reported_client_version = Column(String(100), nullable=True)
      first_authentication_factor = Column(String(100), nullable=True)
      second_authentication_factor = Column(String(100), nullable=True)
      is_success = Column(String(100), nullable=True)
      error_code = Column(String(200), nullable=True) 
      error_message = Column(String(100), nullable=True)
      related_event_id = Column(Integer, nullable=True)
      event = Column(String(200), nullable=True)
      instance_id = Column(Integer, nullable=True)
      company_id = Column(Integer, nullable=True)
      user_id = Column(Integer, nullable=True)
      date_run = Column(Date, nullable=True)
  
      def __repr__(self):
          #return the class object.
          return &quot;&lt;LoginHistory({})&gt;&quot;.format(self.id)
  </code></pre>
  <p><strong>And this is how the table has been created on instance-</strong>
  <a href="https://i.stack.imgur.com/bNlaf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bNlaf.png" alt="enter image description here" /></a></p>
  <p><strong>I have a dataframe of columns as stated below which needs to be inserted into the table created above -</strong></p>
  <pre><code>Index(['event_id', 'event_timestamp', 'event_type', 'user_name', 'client_ip',
         'reported_client_type', 'reported_client_version',
         'first_authentication_factor', 'second_authentication_factor',
         'is_success', 'error_code', 'error_message', 'related_event_id',
         'instance_id', 'user_id', 'event', 'company_id', 'date_run'],
        dtype='object')
  </code></pre>
  <p>So to insert the dataframe I have used <code>to_sql()</code> method as shown below -</p>
  <p><code>dataframe.to_sql(table_name, self.engine, index=False, method=pd_writer, if_exists=&quot;append&quot;)</code></p>
  <p>Which returns me an error -</p>
  <pre><code>Traceback (most recent call last):
    File &quot;metadata_collection.py&quot;, line 59, in &lt;module&gt;
      y = x.collect_process_dump(sql='SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY;', table_name='account_usage_login_history')
    File &quot;metadata_collection.py&quot;, line 55, in collect_process_dump
      load_data = self.load_data.dump_data(table_name=table_name, dataframe=associate_df)
    File &quot;/snowflake-backend/snowflake/collect_metadata/load_data.py&quot;, line 16, in dump_data
      dataframe.to_sql(table_name, self.engine, index=False, method=pd_writer, if_exists=&quot;append&quot;)
    File &quot;/usr/local/lib/python3.7/site-packages/pandas/core/generic.py&quot;, line 2663, in to_sql
      method=method,
    File &quot;/usr/local/lib/python3.7/site-packages/pandas/io/sql.py&quot;, line 521, in to_sql
      method=method,
    File &quot;/usr/local/lib/python3.7/site-packages/pandas/io/sql.py&quot;, line 1317, in to_sql
      table.insert(chunksize, method=method)
    File &quot;/usr/local/lib/python3.7/site-packages/pandas/io/sql.py&quot;, line 755, in insert
      exec_insert(conn, keys, chunk_iter)
    File &quot;/usr/local/lib/python3.7/site-packages/snowflake/connector/pandas_tools.py&quot;, line 168, in pd_writer
      schema=table.schema)
    File &quot;/usr/local/lib/python3.7/site-packages/snowflake/connector/pandas_tools.py&quot;, line 135, in write_pandas
      copy_results = cursor.execute(copy_into_sql, _is_internal=True).fetchall()
    File &quot;/usr/local/lib/python3.7/site-packages/snowflake/connector/cursor.py&quot;, line 597, in execute
      errvalue)
    File &quot;/usr/local/lib/python3.7/site-packages/snowflake/connector/errors.py&quot;, line 124, in errorhandler_wrapper
      cursor.errorhandler(connection, cursor, error_class, error_value)
    File &quot;/usr/local/lib/python3.7/site-packages/snowflake/connector/errors.py&quot;, line 89, in default_errorhandler
      done_format_msg=error_value.get('done_format_msg'))
  snowflake.connector.errors.ProgrammingError: 100072 (22000): 0198d465-0b4e-b74d-0000-d5e5000b524a: NULL result in a non-nullable column
  </code></pre>
  <p>This error is because I have a field <code>id</code> as a <code>primary key</code> in my snowflake table which cannot be <code>null</code>. To <strong>auto increment</strong> it I have created a <strong>Sequence</strong> as shown above in <code>class AccountUsageLoginHistory</code>. Also, in the <strong>screenshot</strong> attached above the default for the <code>id</code> is <code>IDENTITY START 1 INCREMENT 1</code>. All other columns are <em><strong>nullable=True</strong></em> so the issue is with the <code>id</code> only.</p>
  <p>Still I am not able to insert the data into my table.</p>