Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Python

Posted by Jeremy Bradrick
Jeremy Bradrick
6,731 Points

Trying to update a row in a MySQL table using pymysql

I have the following dict aws_attr = {'Creator_Name': 'jimbob', 'CR_Number': '18340', 'Company_Name': 'CMB', 'AWS_Acnt': '0000-0000-0000', 'VPC_Name': 'dev', 'Route_Domain': '15010', 'OSPF_ASN': '115', 'OSPF_VLAN': '807', 'OSPF_Subnet': '8.8.8.8', 'BGP_VLAN': '607', 'BGP_Subnet': '17.17.17.17', 'AWS_Expected_Subnet': '10.0.0.0/20', 'Lpbck_Int': 'lo207', 'Lpbck_IPAddr': '192.168.1.8', 'Tunnel_Num1': '15', 'Tunnel_Num2': '16', 'BGP_AuthKey': 'None', 'VPN_Tunnel1_Dest': 'None', 'VPN_Int1_CryptoKey': 'None', 'VPN_Tun1_IP': 'None', 'VPN_Tunnel2_Dest': 'None', 'VPN_Int2_CryptoKey': 'None', 'VPN_Tun2_IP': 'None', 'AWS_VPN_ConnectionID': 'None'}

Im trying to use the following to update a row in an existing table with the information in that dict using the following.

def mysql_update(mydict):
    connection = pymysql.connect(host="127.0.0.1",
                                 user="foo",
                                 passwd="foo",
                                 port=3306,
                                 database="directconnect")
    try:
        with connection.cursor() as cursor:
            for key, values in mydict.items():
                rowid=mydict['VPC_Name']
            placeholder = ", ".join(["%s"] * len(mydict))
            #sql = "UPDATE `{table}` SET ({columns}) VALUES ({values}) WHERE `idDirectConnect_ID`= idDirectConnect_ID;".format(table="main", columns=",".join(mydict.keys()), values=placeholder)
            sql = "UPDATE `main` SET '%s'='%s' WHERE `VPC_Name`= rowid;"
        cursor.execute(sql, list(mydict.values()))
        connection.commit()
    finally:
        connection.close()

I've been trying various things for several hours and just keep getting different errors. I think Im close, but I can't figure it out. The error Im currently getting based on the above is: pymysql.err.ProgrammingError: Cursor closed

I have no idea why its closed now. Im guessing it has something to do with the for loop.

Basically Im trying to come up with a unique variable in the row to put into the WHERE statement, so I just update a single row.

Any help would be greatly appreciated.

1 Answer

Jeremy Bradrick
Jeremy Bradrick
6,731 Points

Figured this out after several iterations and hours.

def mysql_update(self):
    connection = pymysql.connect(host="127.0.0.1",
                                 user="foo",
                                 passwd="foo",
                                 port=3306,
                                 database="foo")
    try:
        with connection.cursor() as cursor:
            key_list = []
            for key in list(self):
                placeholder = "{}=".format(key) + "%({})s".format(key)
                key_list.extend([placeholder])
            val_list = ",".join(key_list)
            sql = "UPDATE `{table}` SET {values} WHERE `idDirectConnect_ID`= %(idDirectConnect_ID)s;".format(table="main", values=val_list)
            cursor.execute(sql, self)
        connection.commit()
    finally:
        connection.close()

The cursor closed error was because I didn't have it indented right.