MySql update issue in Python

Question

I am trying to update a mysql table with for loop. It updates only one row, when I run my script and gives me the following mysql error;

Traceback (most recent call last): File "index.py", line 190, in main() File "index.py", line 167, in main pre_cursor.execute(query, (box_percentage, shelf_number, test_number)) File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 205, in execute self.errorhandler(self, exc, value) File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defau lterrorhandler raise errorclass, errorvalue _mysql_exceptions.InterfaceError: (0, '')

 for ip in ip_address:

    arg_list = []
    action ="QuotaInfo"
    arg_list.append(upnp_path)
    arg_list.append(' --action=')
    arg_list.append(action)
    arg_list.append(' --ip=')
    arg_list.append(ip)

    ip_address_count = ip_address_count - 1

    print "Ip adress is counting", ip_address_count

    if ip_address_count == 0:
        break


    command = ['python', arg_list]

    #Export the result of the subprocess to output variable

    p = Popen(command, shell=True, stdin=PIPE, stdout=PIPE, stderr=STDOUT)

    output = p.stdout.read()


    # max_size_search = re.search(r'(quotaInfoMaxsize)\W+:\W+(\d+)', output)

    # if max_size_search:
    #     max_size = max_size_search.group()
    #     max_size_digit = int(filter(str.isdigit, max_size))
    #     max_size_digit_gb = float(max_size_digit / 1048576)
    #     # print "Max size of the box is:" , max_size_digit


    # used_size_search = re.search(r'(quotaInfoUsedsize)\W+:\W+(\d+)', output)

    # if used_size_search:
    #     used_size = used_size_search.group()
    #     used_size_digit = int(filter(str.isdigit, used_size))
    #     used_size_digit_gb = float(used_size_digit / 1048576)
    #     # print "Used size of the box is: ", used_size_digit


    # box_in_use_percentage = int(math.ceil((used_size_digit_gb * 100) / (max_size_digit_gb)))

    # print "Box in use percentage:", box_in_use_percentage

    # box_percentage = 100 - box_in_use_percentage

    # print "box percentage", box_percentage


    box_percentage = 97

    test_shelf_no = re.search(r"(\d+$)+", ip)

    shelf_number = int(test_shelf_no.group(1))

    print "Shelf number", shelf_number



    #prepare a cursor object using cursor method
    pre_cursor = db.cursor()

    #Query must be string, execute method does not support tuples

    query = "UPDATE results_stbs SET pre_planner_percentage = %s WHERE shelf_no = %s AND results_test_id = %s"

    pre_cursor.execute(query, (box_percentage, shelf_number, test_number))

    db.commit()

    db.close()

Any helps will be appreciated.

You are closing the DB connection (not just the cursor) at the end of your loop. — Mike Scotty
– Mike Scotty, Commented Dec 23, 2016 at 9:45

Acepcs · Accepted Answer · 2016-12-23 09:47:31Z

1

Do not close your db connection inside the loop. And also you don't have to execute the commit inside the loop.

answered Dec 23, 2016 at 9:47

Acepcs

2741 silver badge10 bronze badges

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

Collectives™ on Stack Overflow

MySql update issue in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related