queries intermittently returns empty result

[Tom Egling]

Hey guys... having some problems with mysqldb and sql 5.1 I often get an empty
result set when running this code, but if I take the query and manually run it
through the mysql console, I always get the correct result.... and ideas would
be very helpful

Thanks

Tom

def get_node_tx_table(self,serial):

date_and_time = datetime.now()

date_last_disconnected = date_and_time.date()

time_last_disconnected = date_and_time.time()

query1 = "SELECT %s, %s, %s, %s, %s FROM %s WHERE %s = %s" %
(message_types.node_serial_number_col , message_types.node_tx_data_col
,message_types.node_message_type_col, message_types.node_poll_frequency_col
,message_types.node_message_id_col, message_types.node_tx_table ,
message_types.node_serial_number_col,str(serial))

try:

print "get_node_tx_table :getting data for serial number " + str(serial)

db = MySQLdb.connect(host=message_types.remote_sql_svr, user = "test",passwd =
"", db = "remote")

try:

Execute the SQL command

db.query(query1)

logger.base.debug("get_node_tx_table : Sucessfully connected and ran : " +
query1)

result = db.store_result()

print "get_node_tx_table :data for serial number " + str(serial) + " = " +
str(result)

if (result == None):

db.close()

return ""

else:

result_data = result.fetch_row(0,1) #0 = all rows, 1 = return as dictionary

db.close()

logger.base.debug("get_node_tx_table : results : " + str(result_data))

return result_data

db.close()

return result_data

except MySQLdb.Error, e:

Rollback in case there is any error

logger.base.debug("get_node_tx_table : Failed running : " + query1 + "Error #
:" + str(e.args))

db.rollback()

db.close()

return ""

except MySQLdb.Error, e:

Rollback in case there is any error

logger.base.critical("get_node_tx_table : Failed Connecting : Error # : " +
str(e.args))

db.close()

return -1

[Andy Dustman]

You shouldn't be using db.query. Use db.execute() instead. Read PEP-249.

[Tom Egling]

Hi Andy.

I am a bit of a newbie to the mysqldb python environment.

execute is a cursor method, not a connection method.

In the above code, i am using connection.query(statement)

so should i not be using connection.query(statement) and be using
cursor.execute(statement) ?

Thanks

Tom

[Andy Dustman]

Yes, that's what I meant. PEP-249
describes the standard Python DB-API. MySQLdb uses %s as the parameter
placeholder. Don't use string interpolation (i.e. the % operator) to insert
query parameters; with execute(), the parameters are passed separately, i.e.
c.execute(query, args). If you must insert table or column names into your
query, then that is a case where you can use % (or templates, or
concatenation, etc.).

[Andy Dustman]

A rough attempt to correct your original code (doesn't try to return data as
dictionaries, left as an exercise for the reader):

def get_node_tx_table(self,serial):

       date_and_time = datetime.now()
       date_last_disconnected = date_and_time.date()
       time_last_disconnected = date_and_time.time()

       query1 = "SELECT %%s, %%s, %%s, %%s, %%s FROM %s WHERE %s = %%s"
       table_info = (message_types.node_tx_table, message_types.node_serial_number_col)
       params =  (message_types.node_serial_number_col , message_types.node_tx_data_col,
         message_types.node_message_type_col, message_types.node_poll_frequency_col,
         message_types.node_message_id_col, serial)

       try:
           #print "get_node_tx_table :getting data for serial number "
+ str(serial)

           db = MySQLdb.connect(host=message_types.remote_sql_svr, user
= "test",passwd = "", db = "remote")
           c = db.cursor()

           try:
               # Execute the SQL command
               c.execute(query1 % table_info, params)
               logger.base.debug("get_node_tx_table : Sucessfully connected
and ran  : " + query1)
               result = c.fetchall()
               print "get_node_tx_table :data for serial number " + str(serial)
+ " = " + str(result)
               if (not result):
                   db.close()
                   return ""
               else:
                   result_data = result[0]
                   db.close()
                   logger.base.debug("get_node_tx_table : results  : "
+ str(result_data))
                   return result_data

               db.close()
               return result_data

           except MySQLdb.Error, e:
               # Rollback in case there is any error
               logger.base.debug("get_node_tx_table : Failed running   : "
+ query1 +  "Error # :" + str(e.args[0]))
               db.rollback()
               db.close()
               return ""

       except MySQLdb.Error, e:
           # Rollback in case there is any error
           logger.base.critical("get_node_tx_table : Failed Connecting   :
Error # : " + str(e.args[0]))
           db.close()
           return -1

[Tom Egling]

Hi Andy,

Cool ok have had a read through PEP-249. Is this a compatibility / portability
issue or is it going to make the python interpreter explode ? Is there any
affect on the server impact of the query between the conn and cursor methods ?

Thanks

Tom

[Andy Dustman]

It's a standard API for database modules. db.query(), db.store_result(), etc.
are all low-level implementation details that are specific to MySQL. Handling
result sets is not trivial; they have to be freed up before additional queries
can be executed. MySQLdb does this for you (including proper encoding of
parameters and decoding of results) if you use the DB-API.

[Tom Egling]

Cool ! ok, I now understand the difference between the two methods. Thanks for
taking the time to explain this to me and for pseudo-correcting my code :)

Tom