tldr; After trying to insert a record into a table called json_schema using an ORM mapper class, I received the error: sqlalchemy.orm.exc.FlushError: Instance <JsonSchema at 0x10657f730> has a NULL identity key. Part of that error read "Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event.", and I'm not sure what an "inappropriate time" means in this context, or how it can further help me troubleshoot the issue.

In an sqlite database, I have a table called json_schema that looks like this. The primary key in this table is a 4-byte hexadecimal text string generated by (lower(hex(randomblob(4)))).

CREATE TABLE json_schema (
        id TEXT DEFAULT (lower(hex(randomblob(4)))) NOT NULL, 
        body JSON NOT NULL, 
        date_added DATETIME NOT NULL, 
        title TEXT NOT NULL, 
        PRIMARY KEY (id)
);

I attempted to insert a row in the following code block

# this function inserts one json schema into the json_schemas table for each
# file present in the json_schemas_folder
def load_json_schemas(session, json_schemas_folder):

    for obj in json_schemas_folder.glob('**/*'):

        if obj.is_file() and obj.suffix == '.json':
            
            title = obj.stem.split('_')[0]
            date_added_string = obj.stem.split('_')[1]
            date_added = datetime.strptime(date_added_string, '%Y-%m-%d') 
            body = obj.read_text()

            new_row = JsonSchema( # JsonSchema is an ORM-mapper class to the json_schema table
                title = title,
                body = body,
                date_added = date_added
            )
            
            # Added row to session here
            session.add(new_row)

engine = create_engine('sqlite:///my.db', echo = True)
Session = sessionmaker(bind=engine)
session = Session()

load_json_schemas(session, Path("../data/json_schemas"))
# session.flush() #<-uncommenting this does not resolve the error.
session.commit() 

The problem: when I execute this script, I'm met with the following error (which was referenced earlier in the tldr section of this question):

sqlalchemy.orm.exc.FlushError: 

Instance <JsonSchema at 0x10657f730> has a NULL identity key.  
If this is an auto-generated value, check that the database table allows generation 
of new primary key values, and that the mapped Column object is configured to expect 
these generated values.  Ensure also that this flush() is not occurring at an inappropriate 
time, such as within a load() event.

I checked on the first issue mentioned in this error – "check that the database table allows generation of new primary key values" – by testing an insert INSERT ( where the id was not specified) . This worked, so not the source of error.

sqlite> insert into json_schema(body,date_added,title) values ('test','test','test');
sqlite> select * from json_schema;
cee94fc1|test|test|test

Next, I checked whether "mapped Column object is configured to expect these generated values." in the ORM class. In the snippet below, you can see that the id column inhereted by JsonSchema does have the server_default set which leads me to believe this point is also already addressed.

@declarative_mixin
class DocumentMetadata:

    id = Column(Text, nullable=False, primary_key=True, server_default=text('(lower(hex(randomblob(4))))'))
    body = Column(JSON, nullable=False)
    date_added = Column(DATETIME, nullable=False)

    def __repr__(self):
        return f"<{self.__class__.__name__}{self.__dict__}>"

    @declared_attr
    def __tablename__(cls):
        return re.sub(r'(?<!^)(?=[A-Z])', '_', cls.__name__).lower()

class JsonSchema(Base, DocumentMetadata):
    title = Column(Text, nullable=False)

Lastly, the error reads "Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event." How can I determine if flush() is occurring at an "innapropriate time"?

CodePudding user response：

SQLAlchemy does not yet support RETURNING for SQLite, and the primary key in this case isn't handled by AUTOINCREMENT. In such a case, SQLAlchemy must execute the default-generating function itself and explicitly insert the generated value.

To achieve this, either:

1. change server_default=text('(lower(hex(randomblob(4))))') to default=text('(lower(hex(randomblob(4))))') in the column definition

   • the table definition will no longer contain the DEFAULT clause

2. add default=text('(lower(hex(randomblob(4))))') to the column definition, leaving server_default in place

   • the table will retain the DEFAULT clause, although SQLAlchemy will always override it.

This is documented in Fetching Server-Generated Defaults, in particular in the Case 4: primary key, RETURNING or equivalent is not supported section.

The new ORM-mapper class for the second approach:

@declarative_mixin
class DocumentMetadata:

    # this is the id column for the second approach. 
    #notice that `default` and `server_default` are both set
    id = Column(
        Text, 
        nullable=False, 
        primary_key=True, 
        default=text('(lower(hex(randomblob(4))))'), 
        server_default=text('(lower(hex(randomblob(4))))')
    )
    
    body = Column(JSON, nullable=False)
    date_added = Column(DATETIME, nullable=False)

    def __repr__(self):
        return f"<{self.__class__.__name__}{self.__dict__}>"

    @declared_attr
    def __tablename__(cls):
        return re.sub(r'(?<!^)(?=[A-Z])', '_', cls.__name__).lower()

class JsonSchema(Base, DocumentMetadata):
    title = Column(Text, nullable=False)