CODEWITHSUNDEEP
sqlalchemysnowflake-cloud-data-platform
Jeet Patel
Jeet Patel

Reputation: 1241

Trying to insert data into Snowflake database table using SQLAlchemy

I have create a model using declarative_base() in sql alchemy as shown below.

class SchemaOnInstance(Base):

    __tablename__ = 'schema_on_instance'
    __table_args__ = {
        'extend_existing' : True,
        'schema' : 'SFOPT_TEST_SCHEMA'
    }

    id = Column(Integer, primary_key=True, autoincrement=True)
    created_on = Column(Time, nullable=True)
    name = Column(String(100), nullable=True)
    is_default = Column(String(50), nullable=True)
    is_current = Column(String(50), nullable=True)
    database_name = Column(String(200), nullable=True)
    owner = Column(String(100), nullable=True)
    comment = Column(Text, nullable=True)
    options = Column(String(100), nullable=True)
    retention_time = Column(Integer, nullable=True)
    instance_id = Column(Integer, nullable=True)

    def __repr__(self):
        return "<SchemaOnInstance({})>".format(self.id)

Then I migrated the same model to Snowflake database.

enter image description here

The model has a field id which is declared as primary_key=True and autoincrement=True. When I try to insert the data into table schema_on_instance using snowflake console. I have to provide id or else it won't insert the data and will return an error.

Query (executed successfully, where id is provided) -

INSERT INTO "SFOPT_TEST_SCHEMA".schema_on_instance (id, created_on, name, is_default, is_current, database_name, owner, comment, options, retention_time, instance_id)
VALUES (1, Null, 'Some Name', 'N', 'N', 'DEMO_DB', Null, 'Some comment', Null, 1, 1);

Query (executed successfully when I completely ignored the column id) -

INSERT INTO "SFOPT_TEST_SCHEMA".schema_on_instance (created_on, name, is_default, is_current, database_name, owner, comment, options, retention_time, instance_id)
VALUES (Null, 'Some Name', 'N', 'N', 'DEMO_DB', Null, 'Some comment', Null, 1, 1);

Query (execution failed, where id is provided as Null) -

INSERT INTO "SFOPT_TEST_SCHEMA".schema_on_instance (id, created_on, name, is_default, is_current, database_name, owner, comment, options, retention_time, instance_id)
VALUES (Null, Null, 'Some Name', 'N', 'N', 'DEMO_DB', Null, 'Some comment', Null, 1, 1);

and it returned an error -

NULL result in a non-nullable column

This method's job is to insert the data in the above stated database table.

def dump_schema(self):

    session = self.Session()
            
    schema_obj = []

    for each_schema in self.schema:

        schema_obj.append(SchemaOnInstance(created_on=each_schema[0], name=each_schema[1], is_default=each_schema[2], is_current=each_schema[3], database_name=each_schema[4], owner=each_schema[5], comment=each_schema[6], options=each_schema[7], retention_time=each_schema[8], instance_id=each_schema[9]))

    session.add_all(schema_obj)

    try:

        x = session.commit()
    
    except Exception as identifier:
    
        logging.error(identifier)

error from SQLAlchemy -

2020-11-23 08:01:02,215 :: ERROR :: dump_schema :: 95 :: (snowflake.connector.errors.ProgrammingError) 100072 (22000): 01987501-0b18-b6aa-0000-d5e500083d26: NULL result in a non-nullable column
[SQL: INSERT INTO "SFOPT_TEST_SCHEMA".schema_on_instance (id, created_on, name, is_default, is_current, database_name, owner, comment, options, retention_time, instance_id) VALUES (%(id)s, %(created_on)s, %(name)s, %(is_default)s, %(is_current)s, %(database_name)s, %(owner)s, %(comment)s, %(options)s, %(retention_time)s, %(instance_id)s)]
[parameters: {'id': None, 'created_on': datetime.datetime(2020, 11, 23, 0, 0, 58, 29000, tzinfo=<DstTzInfo 'America/Los_Angeles' PST-1 day, 16:00:00 STD>), 'name': 'INFORMATION_SCHEMA', 'is_default': 'N', 'is_current': 'N', 'database_name': 'DEMO_DB', 'owner': '', 'comment': 'Views describing the contents of schemas in this database', 'options': '', 'retention_time': '1', 'instance_id': 1}]

If we look at the query formed in the error returned by the SQLAlchemy, it has considered column id and its value is interpreted as None. How can I form the query without including column id and its value.

My end goal is to insert data into Snowflake database table using SQLAlchemy. I want Snowflake database table to auto increment the value of id.

How should I get rid of this error.

Upvotes: 1

Views: 1697

Answers (1)

NickW
NickW

Reputation: 9768

I think you need to include a Sequence when defining the table in order to get this to work: SQLAlchemy Auto-increment Behavior

A Sequence is a standalone object in Snowflake that needs to be created before the table is created and is then referenced in the CREATE TABLE statement: CREATE SEQUENCE

Upvotes: 1

Related Questions