Error when controlling mysql with python class

Question

I am trying to update a mysql database using class system but cannot get the update part to work. It was all ok the old way but I wanted to use the class system with exception error control. Can someone please let me know what I am doing wrong. At the moment for this script I am just trying to send the variable Boileron to the database column office.

import MySQLdb

class DBSolar:
    conn = None

    def connect(self):
        try:
            self.conn = MySQLdb.connect("192.xxx.x.x", "exxxxx", "Oxxxx", "hxxxx")
        except (MySQLdb.Error, MySQLdb.Warning) as e:
            print (e)
            self.conn = None
        return self.conn

    def query(self, sql):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
        return cursor

    def update(self, task):
        boilerState = task
        try:
            sql = "UPDATE dashboard SET office = ? WHERE id = 1", (boilerState)
            cursor = self.conn.cursor()
            cursor.execute(sql)
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
        return

while 1:

    BoilerOn = 1
    print BoilerOn

    dbSolar = DBSolar()
    connSolar = dbSolar.connect()

    if connSolar:
        dbSolar.update(BoilerOn)

Below is the error report from putty console

 Traceback (most recent call last):
   File "test2.py", line 47, in <module>
     dbSolar.update(BoilerOn)
   File "test2.py", line 29, in update
     cursor.execute(sql)
   File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 223, in execute
     self.errorhandler(self, TypeError, m)
   File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorvalue
 TypeError: query() argument 1 must be string or read-only buffer, not tuple

user2669997 · Accepted Answer · 2018-04-18 08:22:02Z

1

Got this working changed the update to the following

def update(self, task):
    try:
        cursor = self.conn.cursor()
        cursor.execute("UPDATE dashboard SET office = %s WHERE id = 1", [task])
        self.conn.commit()
    except (MySQLdb.Error, MySQLdb.Warning) as e:
        print (e)
        self.connect()
        #cursor = self.conn.cursor()
        #cursor.execute(sql)
        #self.connect.commit()
    return

answered Apr 18, 2018 at 8:22

user2669997

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

Comments

Jimms Hsieh · Accepted Answer · 2018-04-18 06:29:06Z

0

In the MySQL Developer document

Since by default Connector/Python turns autocommit off, and MySQL 5.5 and higher uses transactional InnoDB tables by default, it is necessary to commit your changes using the connection's commit() method. You could also roll back using the rollback() method.

You need to add self.conn.commit() after cursor.execute(sql) to commit all the changes.

Or turn on the autocommit which is described in Python mySQL Update, Working but not updating table

answered Apr 18, 2018 at 6:29

Jimms Hsieh

262 bronze badges

1 Comment

user2669997 Over a year ago

I assume you mean like this cursor.execute(sql) self.conn.commit() which I have tried but it still comes up with an error. I will update my original post with the error

hcheung · Accepted Answer · 2018-04-18 08:09:42Z

0

Your sql creata a tuple, but cursor.execute(statement, params) is expecting a string for the statement and either a tuple or a dictionary for params.

For MySQL UPDATE, you also need to commit the execution.

Therefore try:

self.conn.cursor.execute("UPDATE dashboard SET office = %s WHERE id = 1", (str(task), ))
self.conn.commit()

I would suggest you read the 'MySQL Connector/Python Development Guide better understating of using MySQL in python.

edited Apr 18, 2018 at 8:09

answered Apr 18, 2018 at 7:29

hcheung

4,4283 gold badges16 silver badges28 bronze badges

2 Comments

user2669997 Over a year ago

This has stopped the tuple error but it is not updating the office row in the database. i will have to do some more research on this

hcheung Over a year ago

Run a direct SQL on your MySQL to see whether you have the same problem

Collectives™ on Stack Overflow

Error when controlling mysql with python class

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related