[Sqlalchemy-users] Batched Inserts (Lack of?)

[Michael Carter]

Hello,
I was just working on a script that uses sqlalchemy to insert 3.5 million rows into a database. It reads each line from a text file, then creates an object out of it. The outline of the code was something like this:

count = 0
while (linesAreLeft):
    count +=1
    line = readline
    makeObjFromLine(line)
    if count % 2000 == 0:
        print "committing..."
        objectstore.commit()
        objectstore.clear()
        print "count: %s" % count




I also have echo turned on for the database engine (mysql). The result of all this is that 2000 lines get read, then when commit is called, a series of 2000 insert statements are issued.

comitting...
[engine] insert ...
[engine] insert ...
[engine] insert ...
<2000 times>
count: 2000

 I thought that its possible to have a single insert statement that did something like: insert into table(col1,col2,col3) values ( (a,b,c), (d,e,f), (g, h, i) ); When I have some sort of relatedjoin between foo and bar, a bunch of calls like for bar in Bar.select(): foo.bars.append(bar), then i call objectstore.commit, a single insert is used.

Without going into the UOW code I can imagine how a uow could be structured as to make it hard to figure out what can be inserted in a single statement and what can't, but it seems possible. Or perhaps I missed something in the docs. Let me know.

Thanks,
Michael