Pymysql returning same result instead last line from table via tornado websocket

Question

I need little advice. I have for one project app based on tornado framework where I use WebSocketHandler. Client side sending request every 10 seconds. Part of WS handler implementation goes like this:

class WebSocketHandler(tornado.websocket.WebSocketHandler):

    def __init__(self, *args, **kwargs):
        self.db_passwrod = kwargs.pop('password')
        self.db_user = kwargs.pop('user')
        self.db_name = kwargs.pop('db_name')
        self.db_connection = db.DbConnection(password=self.db_passwrod,
                                             user=self.db_user,
                                             db_name=self.db_name,
                                             cursor_type=True
                                             )
        super(WebSocketHandler, self).__init__(*args, **kwargs)

    def EnergyDB(self):
        return self.db_connection.fetch_row(table="Energy",
                                            value="ORDER BY Timestamp DESC LIMIT 1",
                                            flag=None)


    def convert(self, object):
        if isinstance(object, datetime.datetime):
            return object.__str__()

    def open(self, *args):
        print("Websocket up")
        self.write_message("coonection established")

    def on_message(self):
        self.write_message(json.dumps(self.EnergyDB))

    def on_close(self):
        print("websocket goes down")

Database class goes like this:

class DbConnection():

    def __init__(self,
                 host='localhost',
                 port=3306,
                 user='user',
                 password='password',
                 db_name=None,
                 cursor_type=None):
                 self.connection = pymysql.connect(host=host,
                                                   user=user,
                                                   passwd=password,
                                                   port=port,
                                                   database=db_name,
                                                   charset='utf8')
        if cursor_type is None:
            self.cursor = self.connection.cursor(pymysql.cursors.SSCursor)
        else:
            self.cursor = self.connection.cursor(pymysql.cursors.SSDictCursor)

    def fetch_row(self, data='*', table=None, vartype=None, value=None, flag=None):
        if flag is not None:
            if value is None:
                self.cursor.execute("SELECT %s FROM %s" %(data, table))
            else:
                self.cursor.execute("SELECT %s FROM %s WHERE %s" %(data, table, value))
        else:
            self.cursor.execute("SELECT %s FROM %s WHERE %s = %s" %(data, table, vartype, value))

         return self.cursor.fetchall()

And I ended returning same line, perhaps gathered at first ws call from client side, instead of last added. But every second is added new line from machine sensors connection. So if I get it right. Every created object of pymysql cursor works with image of table from moment of creation cursor for security reasons/preventing damage to db and ignoring changes after creating curosr? And therefore I need create new cursor, gather values and delete curosr every request? Becasue this was first approach and it worked fine but with massive cpu overload. Or am I missing something?

From your code, by calling `EnergyDB()` this SQL query with be passed to the DB: `SELECT * FROM Energy WHERE Timestamp ORDER BY Timestamp DESC LIMIT 1`. Is it correct that you are not specifying any condition for WHERE clause? — Fine, May 30 '18 at 09:58
It's typo. Actually It's passed: `SELECT * FROM Energy ORDER BY Timestamp DESC LIMIT 1`. — SparkyWolf, May 30 '18 at 10:15
So, assuming that your code is correct, there are 3 things: 1. It's quite hard to answer your question without knowing how your data is written to a DB". 2. There could be a race condition between data reading and writing, so in your situation I'd check DB logs and see the order of your inserts and selects to be sure that you are not querying data before it's written. 3. [This seems to be quite similar situation to yours.](https://stackoverflow.com/questions/33569457/pymysql-apparently-returning-old-snapshot-values-not-rerunning-query) — Fine, May 30 '18 at 10:54

score 0 · Answer 1 · answered Jun 01 '18 at 01:23

In your sample code, you're not actually calling the EnergyDB function. I assume the missing parentheses are just a typo.
The main issue here is that you're reusing a cursor and a transaction, so you're seeing a consistent snapshot of the database. You need to run self.connection.commit() or self.connection.abort() after each use of it to reset the connection and be able to see new data (as long as your queries are read-only, commit and abort are equivalent). I'm not sure if it's safe to reuse a cursor like this or whether you should create a new one every time (In my own code I always create a new cursor).
Don't use the % operator to construct SQL queries. This is vulnerable to SQL injection attacks. Instead, use the database driver's parameter substitution features as seen in pymysql's example

Pymysql returning same result instead last line from table via tornado websocket

1 Answers1