Rob wrote: > Hi, > > I have a question regarding the most efficient way of solving what > should be a fairly simple task. > I have (on a server) a database set up using SQLAlchemy to define a > joined-table (parent/child) inheritance structure (using a > discriminator field as per the documentation etc) > > On a client machine, I want to use SqlSoup to insert data (I don't > want to replicate the object model on the client machine) however, the > only way I can see to do this is along the following lines: > > parentRecord = db.parent.insert( [fields] ) > db.flush() > childRecord = db.child.insert(id=parentRecord.id, [fields]) > db.flush() > > A flush is required (at some point) to commit the data to the table, > but for multiple inserts the method above is horribly slow. > Am I missing something fundamental? Is there a faster/better > (possibly correct!) way to do this?
the fastest way to insert many rows is to execute a table.insert() using executemany syntax. Above, I'm fairly certain SqlSoup can also map to a join, which is probably what you'd want to do here. The inserts occur in a batch where it sends the id of the parent table into the child row before inserting that one. Alternatively, you could create the list of parentRecords first, do a single flush(), then get the list of all the child ids and populate those. If it were me I'd just use a simple joined-table inheritance model with declarative. Using reflected tables, its barely any more typing than what SqlSoup requires. SqlSoup is really just a typing saver in any case its still mapping classes to tables, just in a very rigid and difficult to customize way. > > Many thanks, > Rob > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
