Newbie Question: python mysqldb performance question

[rurpy at yahoo.com]

On May 20, 5:55 pm, cjl <cjl... at gmail.com> wrote:
...snip...
> conn = MySQLdb.connect(db="database", host="localhost", user="root",
> passwd="password")
> c = conn.cursor()
>
> reader = csv.reader(open(sys.argv[1]))
> for row in reader:
>     data1, data2, data3, data4 = row
>     data = (data1,data2,data3,data4)
>     c.execute("""insert into datatable values (%s, %s, %s, %s)""",
> data)
>     conn.commit()
>
> This takes a really long time to execute, on the order of minutes.
> Directly importing the csv file into mysql using 'load infile' takes
> seconds.
>
> What am I doing wrong? What can I do to speed up the operation?

In addition to the previous poster's suggestions,
if you have indexes, foreign keys, or other constraints
on the table, and you are sure that you are loading
"good" data, you may want to drop them before doing
the inserts, and recreate them after.  Updating indexes
and checking contraints can be time consuming.

But you will probabably never get your code to run
as fast as the mysql "load" command, which (I'm
guessing here, don't use mysql much) skips all
the sql machinery and writes directly to the table.