SQL Alchemy default function to simulate auto-increment within a unique group of parent-child records

Question

I have a small problem, which, it seems to me, should be easily handled by SQL Alchemy, but I can’t understand that this is correct. I have two tables, one of which is the parent, and the other is the child. Each child record requires a unique identifier, but only with the context of a unique parent record.

I use the Declarative Base approach.

I establish a parent child relation using FK and a relationship function. What I'm trying to achieve is to get something like a pseudo-auto-increment function that will look for the maximum CategoryID value in the group of unique category names and increase it. I tried using various functions by default, but the problem I am facing is the inability to specify CategoryUniqueName during insertion. I can’t find a way to pass the current CategoryItems.CategoryUniqueName value so that the search query has the correct filter applied when trying to select something like func.max (CategoryItems.CategoryID). If I hard-coded the request, it works fine. This is what I think should work, but, again, I cannot find a way to specify a unique value for the filter.

unique_group='my_group'
result=con.query(func.max(CategoryItems.CategoryID)).filter(and_(
        CategoryItems.CategoryUniqueName==unique_group, 
    )).one()

. , SQL-. , , SQL Alchemy, .

class Category(Base):
    __tablename__ = 'parent_table'
    __table_args__ = {'mysql_engine':'InnoDB', 'useexisting':True}

    CategoryUniqueName = Column(Unicode(255), primary_key=True)
    CategoryGroupName = Column(Unicode(255), nullable=False)
    CategoryGroupMemo = Column(UnicodeText)
    SortOrder = Column(Integer, index=True)
    IsLocked = Column(Boolean, default=0)

class CategoryItems(Base):
    __tablename__ = 'child_table'
    __table_args__ = {'mysql_engine':'InnoDB', 'useexisting':True}

    CategoryUniqueName = Column(Unicode(255), ForeignKey(Category.CategoryUniqueName), primary_key=True)
    CategoryID = Column(Integer, primary_key=True, autoincrement=False)
    CategoryName = Column(Unicode(255), nullable=False, index=True)
    CategoryMemo = Column(UnicodeText)
    CategoryImage = Column(Unicode(255))
    CategoryFlex1 = Column(Unicode(255), index=True)
    CategoryFlex2 = Column(Unicode(255), index=True)
    CategoryFlex3 = Column(Unicode(255), index=True)
    SortOrder = Column(Integer, index=True)

    category_group = relation(
        Category, 
        backref=backref(
            'items', 
            order_by=SortOrder, 
            collection_class=ordering_list('SortOrder'), 
            cascade="all, delete, delete-orphan"
    ))

Answer 1

3 :

• . mapper before_insert() hook, .
• Pass default. context : context.compiled_parameters[0]['CategoryUniqueName'], context.connection.
• Pass FetchedValue() server_default .

, ddaa. , , ( !). ( 500 -) .

, a CategoryID . CategoryUniqueName. 2 .

Answer 2

, . 1 2, . 2. , . , , - 1, . , - . 2 .

, :

def getNextId(context):
    unique_name=context.compiled_parameters[0]['CategoryUniqueName']
    sql = """
        SELECT MAX(CategoryID)
        FROM child_table
        WHERE CategoryUniqueName='%s'""" % (unique_name, )

    result = context.connection.execute(sql).fetchone()[0]
    if result > 0:
         return result + 1
    else:
        return 1 

Answer 3

, , CategoryItems CategoryUniqueName ?

, ( CategoryId , ) . : CategoryId.

Do you really need your CategoryId to grow separately? Why not just use the regular auto zoom feature. The CategoryId sequence for this CategoryUniqueName will have holes, but is this really a problem?

If you need consecutive sequence numbers, you will need to prevent the race condition using manual blocking.