Python multiple MySQL Inserts

Question

I'm trying to do multiple inserts on a MySQL db like this:

p = 1
orglist = buildjson(buildorgs(p, p))
while (orglist is not None):
    for org in orglist:
        sid = org['sid']
        try:
            sql = "INSERT INTO `Orgs` (`sid`) VALUES (\"{0}\");".format(sid)
            cursor.execute(sql)
            print("Added {0}".format(org['title']))
        except Exception as bug:
            print(bug)
    conn.commit()
    conn.close()
    p += 1
    orglist = buildjson(buildorgs(p, p))

However I keep getting a bunch of 2055: Lost connection to MySQL server at 'localhost:3306', system error: 9 Bad file descriptor

How can I correctly do multiple inserts at once so I don't have to commit after every single insert. Also, can i only do conn.close()after the while loop or is it better to keep it where it is?

score 3 · Accepted Answer · edited May 23 '17 at 12:30

3

This may be related to this question and/or this question. A couple ideas from the answers to those questions which you might try:

Try closing the cursor before closing the connection (cursor.close() before conn.close(); I don't know if you should close the cursor before or after conn.commit(), so try both.)
If you're using the Oracle MySQL connector, try using PyMySQL instead; several people said that that fixed this problem for them.

edited May 23 '17 at 12:30

Community

1
1

answered Oct 12 '15 at 03:33

rmunn

34,942
10
74
105

Switching libraries gave me: Traceback (most recent call last): File "grabber.py", line 57, in poporgs() File "grabber.py", line 42, in poporgs conn.commit() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 718, in commit self._execute_command(COMMAND.COM_QUERY, "COMMIT") File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 956, in _execute_command raise err.InterfaceError("(0, '')") pymysql.err.InterfaceError: (0, '') – Bernardo Meurer Oct 12 '15 at 03:37
1

I see you just accepted this answer, but your last comment suggests that it hadn't been working for you. What changed? You should probably mention (in a comment, if nothing else) what you did differently that made this answer work for you, so that anyone else who finds this question later will know. – rmunn Oct 16 '15 at 18:16
I closed the cursor before closing the connection and I removed `conn.close()` from within the while loop to the very end of the code. Not sure which one of these fixed it but it started working. Also, thanks for the tip, I tend to forget commenting on what actually fixed the issue later on :) – Bernardo Meurer Oct 16 '15 at 18:20

Python multiple MySQL Inserts

1 Answers1