As an example, I have the following..
<code>class Base( DeclarativeBase ):
pass
class A( Base ):
__tablename__ = "A"
id: Mapped[ int ] = mapped_column( primary_key = True )
a_value: Mapped[ str ] = mapped_column( String( 32 ) )
b_list: Mapped[ List[ "B" ] ] = relationship( "B" )
class B( Base ):
__tablename__ = "B"
__table_args__ = (
ForeignKeyConstraint( [ "a_id" ], [ "A.id" ] ),
)
id: Mapped[ int ] = mapped_column( primary_key = True )
a_id: Mapped[ int ]
b_value: Mapped[ str ] = mapped_column( String( 32 ) )
</code>
<code>class Base( DeclarativeBase ):
pass
class A( Base ):
__tablename__ = "A"
id: Mapped[ int ] = mapped_column( primary_key = True )
a_value: Mapped[ str ] = mapped_column( String( 32 ) )
b_list: Mapped[ List[ "B" ] ] = relationship( "B" )
class B( Base ):
__tablename__ = "B"
__table_args__ = (
ForeignKeyConstraint( [ "a_id" ], [ "A.id" ] ),
)
id: Mapped[ int ] = mapped_column( primary_key = True )
a_id: Mapped[ int ]
b_value: Mapped[ str ] = mapped_column( String( 32 ) )
</code>
class Base( DeclarativeBase ):
pass
class A( Base ):
__tablename__ = "A"
id: Mapped[ int ] = mapped_column( primary_key = True )
a_value: Mapped[ str ] = mapped_column( String( 32 ) )
b_list: Mapped[ List[ "B" ] ] = relationship( "B" )
class B( Base ):
__tablename__ = "B"
__table_args__ = (
ForeignKeyConstraint( [ "a_id" ], [ "A.id" ] ),
)
id: Mapped[ int ] = mapped_column( primary_key = True )
a_id: Mapped[ int ]
b_value: Mapped[ str ] = mapped_column( String( 32 ) )
Followed by the this which created a list of A’s each containing a list of B’s
<code>a_list = []
for a_id in range( 0, 3 ):
a = A( a_value = f"A_{a_id}" )
for b_id in range( 0, 3 ):
a.b_list.append(
B( b_value = f"B_{a_id}-{b_id}" )
)
a_list.append( a )
</code>
<code>a_list = []
for a_id in range( 0, 3 ):
a = A( a_value = f"A_{a_id}" )
for b_id in range( 0, 3 ):
a.b_list.append(
B( b_value = f"B_{a_id}-{b_id}" )
)
a_list.append( a )
</code>
a_list = []
for a_id in range( 0, 3 ):
a = A( a_value = f"A_{a_id}" )
for b_id in range( 0, 3 ):
a.b_list.append(
B( b_value = f"B_{a_id}-{b_id}" )
)
a_list.append( a )
Then I use session.add to put them in the database…
<code>with Session( engine ) as session, session.begin():
for a in a_list:
session.add( a )
</code>
<code>with Session( engine ) as session, session.begin():
for a in a_list:
session.add( a )
</code>
with Session( engine ) as session, session.begin():
for a in a_list:
session.add( a )
When I run this I get the following which clearly shows that it’s not performing a multi-values insert but inserting each row one at a time.
<code>BEGIN (implicit)
INSERT INTO `A` (a_value) VALUES (%(a_value)s)
[generated in 0.01637s] {'a_value': 'A_0'}
INSERT INTO `A` (a_value) VALUES (%(a_value)s)
[cached since 0.04846s ago] {'a_value': 'A_1'}
INSERT INTO `A` (a_value) VALUES (%(a_value)s)
[cached since 0.07575s ago] {'a_value': 'A_2'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[generated in 0.01316s] {'a_id': 1, 'b_value': 'B_0-0'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.04103s ago] {'a_id': 1, 'b_value': 'B_0-1'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.06817s ago] {'a_id': 1, 'b_value': 'B_0-2'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.09477s ago] {'a_id': 2, 'b_value': 'B_1-0'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.1232s ago] {'a_id': 2, 'b_value': 'B_1-1'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.1506s ago] {'a_id': 2, 'b_value': 'B_1-2'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.177s ago] {'a_id': 3, 'b_value': 'B_2-0'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.2036s ago] {'a_id': 3, 'b_value': 'B_2-1'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.2371s ago] {'a_id': 3, 'b_value': 'B_2-2'}
COMMIT
</code>
<code>BEGIN (implicit)
INSERT INTO `A` (a_value) VALUES (%(a_value)s)
[generated in 0.01637s] {'a_value': 'A_0'}
INSERT INTO `A` (a_value) VALUES (%(a_value)s)
[cached since 0.04846s ago] {'a_value': 'A_1'}
INSERT INTO `A` (a_value) VALUES (%(a_value)s)
[cached since 0.07575s ago] {'a_value': 'A_2'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[generated in 0.01316s] {'a_id': 1, 'b_value': 'B_0-0'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.04103s ago] {'a_id': 1, 'b_value': 'B_0-1'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.06817s ago] {'a_id': 1, 'b_value': 'B_0-2'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.09477s ago] {'a_id': 2, 'b_value': 'B_1-0'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.1232s ago] {'a_id': 2, 'b_value': 'B_1-1'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.1506s ago] {'a_id': 2, 'b_value': 'B_1-2'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.177s ago] {'a_id': 3, 'b_value': 'B_2-0'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.2036s ago] {'a_id': 3, 'b_value': 'B_2-1'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.2371s ago] {'a_id': 3, 'b_value': 'B_2-2'}
COMMIT
</code>
BEGIN (implicit)
INSERT INTO `A` (a_value) VALUES (%(a_value)s)
[generated in 0.01637s] {'a_value': 'A_0'}
INSERT INTO `A` (a_value) VALUES (%(a_value)s)
[cached since 0.04846s ago] {'a_value': 'A_1'}
INSERT INTO `A` (a_value) VALUES (%(a_value)s)
[cached since 0.07575s ago] {'a_value': 'A_2'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[generated in 0.01316s] {'a_id': 1, 'b_value': 'B_0-0'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.04103s ago] {'a_id': 1, 'b_value': 'B_0-1'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.06817s ago] {'a_id': 1, 'b_value': 'B_0-2'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.09477s ago] {'a_id': 2, 'b_value': 'B_1-0'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.1232s ago] {'a_id': 2, 'b_value': 'B_1-1'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.1506s ago] {'a_id': 2, 'b_value': 'B_1-2'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.177s ago] {'a_id': 3, 'b_value': 'B_2-0'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.2036s ago] {'a_id': 3, 'b_value': 'B_2-1'}
INSERT INTO `B` (a_id, b_value) VALUES (%(a_id)s, %(b_value)s)
[cached since 0.2371s ago] {'a_id': 3, 'b_value': 'B_2-2'}
COMMIT
I’d have expected something like
<code>INSERT INTO `B` (a_id, b_value) VALUES ( 1, "B_0-0" ), ( 1, "B_0-1" ), ( 1, "B_0-2" )
</code>
<code>INSERT INTO `B` (a_id, b_value) VALUES ( 1, "B_0-0" ), ( 1, "B_0-1" ), ( 1, "B_0-2" )
</code>
INSERT INTO `B` (a_id, b_value) VALUES ( 1, "B_0-0" ), ( 1, "B_0-1" ), ( 1, "B_0-2" )
So, what settings or approach do I need to change to make this insertion more performant and use multi-values inserts..