this result object does not return rows. it has been closed automatically

[Ni Wesley]

Hi all,

  Today I get an error as title.

I use sqlalchemy + mysql.

Here is my code snippet:

def dbquery(_table,whereclause):

    try:

        #_table=Table(tablename, metadata, autoload=True)

        #_table = tables[tablename]

        i=_table.select().where(whereclause) 

        if direct_engine:  #direct_engine is True

            res = engine.execute(i)

            return res

        else:

            session = scoped_session(sessionmaker(bind=engine))

            res = session.execute(i)

            return res

            session.close()

    except Exception,e:

        #dba_logger.log(40,'Exception when dbwriter:%s' % str(e))

        #dba_logger.log(20,'Exception detail:%s' % str(kwargs))

        exctrace('db','1','Error happened when querying db',dba_logger,'Exception when dbquery:%s' % str(e),'Exception detail:%s' % str(whereclause))

        #session.rollback()

        if not direct_engine:

            session.close()

Here is snippet in another file involving dbquery:

try:

     res = dbquery(tables['sessions_details'],whereclause=and_(tables['sessions_details'].c.app_key==self.app_key,tables['sessions_details'].c.device_token==self._devicetoken))

except Exception,e:

    exctrace('db','1','Error happened when querying db',dba_logger,'Exception when query session_details:%s' % str(e),'Exception detail:appkey is %s,devicetoken is %s' % (self.app_key,self._devicetoken))

    self.read_message()

    return   

if res is None:

   logger.log(40,'When query connection,mysql has gone or something, just skip db and go-on')

                        #here need to justify 0 or 1, later on

                        self.status='0'

                        self.read_message()

                        return

if res.first() is None:

   if json_obj['action'] == 'reg':

So, the line in pink above raises the exception.

Could anyone give some suggestion how this happened?

Thanks.

Wesley

[Michael Bayer]

when the DBAPI cursor has no .description object, it is determined to not be a result-row returning object.   The MySQLDB DBAPI has been observed to occasionally have issues in this area, when a connection gets into a bad state. There are likely patterns in how you’re calling it that lead it to have this issue but you might try a different DBAPI like mysql-connector-python just to see what’s different.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Ni Wesley]

It's not happening everytime.

And I am not using mysql-python, instead, I use pymysql.

So, the root cause is connection in bad state?

How to fix or work around from sqlalchemy side?

Wesley

在 2014年3月27日星期四UTC+8下午8时58分40秒，Michael Bayer写道：

[Ni Wesley]

It's not happening everytime.

And I am not using mysql-python, instead, I use pymysql.

So, the root cause is connection in bad state?

How to fix or work around from sqlalchemy side?

Wesley

在 2014年3月27日星期四UTC+8下午8时58分40秒，Michael Bayer写道：

when the DBAPI cursor has no .description object, it is determined to not be a result-row returning object.   The MySQLDB DBAPI has been observed to occasionally have issues in this area, when a connection gets into a bad state. There are likely patterns in how you’re calling it that lead it to have this issue but you might try a different DBAPI like mysql-connector-python just to see what’s different.

[Michael Bayer]

On Mar 27, 2014, at 9:14 AM, Ni Wesley <nis...@gmail.com> wrote:

It's not happening everytime.

And I am not using mysql-python, instead, I use pymysql.

So, the root cause is connection in bad state?

How to fix or work around from sqlalchemy side?

I don’t really know, I’d need to have a short script which replicates it in order to identify the pattern exactly.

if its pymysql, try another DBAPI.  though this may be some artifact of the MySQL protocol.

as always, if you’re doing anything with threads, concurrent use of a MySQL connection will usually lead to problems like this.  but there can be other reasons too.

[Ni Wesley]

MySQLdb has the same problem.

So, no concurrent, I just use engine(with default connection pool) execute.

Then, how to work around the problem, for my query operation, if catch this exception, query again?

在 2014年3月27日星期四UTC+8下午11时42分19秒，Michael Bayer写道：

[Michael Bayer]

I couldn’t tell you, it shouldn’t happen in the first place.  Would need code that illustrates the problem.