Python on Windows: large number of inserts using pyobbc causes memory leak

Question

I am trying to populate a MS SQL 2005 database using python on windows. I insert millions of lines, and for 7 million I use almost a gigabyte of memory. The test below consumes 4 megabytes of RAM for each inserted row of 100 thousand:

import pyodbc
connection=pyodbc.connect('DRIVER={SQL Server};SERVER=x;DATABASE=x;UID=x;PWD=x')
cursor=connection.cursor()
connection.autocommit=True
while 1:
    cursor.execute("insert into x (a,b,c,d, e,f) VALUES (?,?,?,?,?,?)",1,2,3,4,5,6)
mdbconn.close()

Hacking solution: I ended up creating a new process using the multiprocessing module to recover the memory. It is still confusing why inserting rows this way consumes so much memory. Any ideas?

Answer 1

, pyodbc : http://code.google.com/p/pyodbc/issues/detail?id=145

VALUES , .

Answer 2

.

50 XML 300 SQL Server 2005.

:

.

/

None.

, XML Process.

, IronPython - System.Data.SqlClient.

.

Answer 3

, ?

, , , !

Answer 4

. , . connection.commit .

- , time.sleep(0) , .

Answer 5

gc.collect() gc.

Another option might be to use cursor.executemany()and see if this fixes the problem. However, the unpleasant thing executemany()is that it takes a sequence, not an iterator (so you cannot pass a generator to it). First I will try the garbage collector.

EDIT: I just checked the code you posted and I don't see the same problem. Are you using an old version of pyobbc?