Unable to insert data frame into database table of snowflake

Question

I want to insert a data frame into my Snowflake database using the to_sql() method.

This is how I am trying to do it -

    dataframe.to_sql(table_name, engine, index=False, method=pd_writer, if_exists="append")

This is what my data frame looks like -

    event_id                  event_timestamp event_type user_name       client_ip  ... related_event_id instance_id user_id event company_id
0  1298203705 2020-11-05 10:47:43.981000+00:00      LOGIN  SHIVKANT  52.149.162.195  ...                0           2       4     1          1
1  1298207325 2020-11-10 10:31:00.263000+00:00      LOGIN  SHIVKANT  40.118.225.132  ...                0           2       4     1          1

The to_sql() method's argument if_exists creates a new table if the table with the provided name does not exist.

When I run the code stated above this is what it prints on the terminal -

CREATE TABLE login_history (
        event_id BIGINT, 
        event_timestamp TIMESTAMP, 
        event_type TEXT, 
        user_name TEXT, 
        client_ip TEXT, 
        reported_client_type TEXT, 
        reported_client_version TEXT, 
        first_authentication_factor TEXT, 
        second_authentication_factor TEXT, 
        is_success TEXT, 
        error_code FLOAT, 
        error_message TEXT, 
        related_event_id BIGINT, 
        instance_id BIGINT, 
        user_id BIGINT, 
        event BIGINT, 
        company_id BIGINT
)

1. The table being created does not have the primary_key field but I want it to have a Auto Increment, Primary Key field called id.
2. The above code does create a table but it returns an error while inserting the data into the table.

    snowflake.connector.errors.ProgrammingError: 000904 (42000): 0198a74a-0b5e-4dd5-0000-d5e5000a40f6: 

SQL compilation error: error line 1 at position 88
    invalid identifier '"event_id"'

Answer 1

Regarding your second error the problem may be a quoting-issue. Adding " " around your column-names should help here.