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?

Community · Accepted Answer · 2017-05-23 12:30:14Z

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, 2017 at 12:30

CommunityBot

11 silver badge

answered Oct 12, 2015 at 3:33

rmunn

37k11 gold badges80 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bernardo Meurer Over a year ago

Switching libraries gave me: Traceback (most recent call last): File "grabber.py", line 57, in <module> 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, '')

rmunn Over a year ago

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.

Bernardo Meurer Over a year ago

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 :)

Collectives™ on Stack Overflow

Python multiple MySQL Inserts

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related