Mysql-Python complex query resulting in empty set

Question

We have a script that was running comfortably for past 4 years. It pulls a lot of data from a SugarCRM application and then prints some reports.

I am using Python-MySQLDB to pull the data.

  db = mdb.connect('localhost', db_user, db_pass, db_name)
  c = db.cursor()
  statement = "
select a.email_address
     , c.hits
     , c.activity_date
     , c.activity_type 
  from email_addresses a
     , campaign_log c
     , email_addr_bean_rel r
 where c.target_id = r.bean_id 
   and r.email_address_id = a.id 
   and c.activity_type = 'targeted' 
   and c.campaign_id ='%s'
"%x
  c.execute(statement)
  rows = c.fetchall()

Problem is the SQL query gets executed both on MySQL shell as well as on phpmyadmin throwing in correct values as results.

But on python shell

>>rows
>>()

Except that the SugarCRM version was updated a few weeks back, and even after post updating things were working fine.

What could be the problem? Is it that new version have some InnoDB tables?

I am stumped. Any guidance would be welcome.

Edit-- I had copied a SQL Query statement which executes correctly on both mysql shell as well as phpmyadmin window, and ran it, and results are the same. It is showing an empty tuple, and no SQL records are available.

I am certain the issue is not because of passing the string, as that is the direction which everyone seems to be suggesting.

@haifzhan: Using string interpolation instead of a parametrized query allows for the possibility of a SQL injection attack. — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented May 22, 2015 at 16:04
To clarify the command c.fetchall() even after I substitute the value for x with a value. Is there an issue with long running or complex SQL queries with mysql-python — ramdaz
– ramdaz, Commented May 22, 2015 at 17:03

Aleks Boev · Accepted Answer · 2015-05-22 16:01:55Z

2

If your SQL query need a parameter "c.campaign_id ='%s'", you should specify it in "c.execute(statement)", e.g.:

c.execute(statement, (42))

answered May 22, 2015 at 16:01

Aleks Boev

7301 gold badge8 silver badges16 bronze badges

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

2 Comments

Aleks Boev Over a year ago

"42" is example value, that means you query records with c.campaign_id =42. And "%x" after closing ' " ' not needed.

Haifeng Zhang Over a year ago

I was thinking it must be a tuple, I tried yours, it works :)

ramdaz · Accepted Answer · 2015-05-24 17:03:35Z

0

I figured out the issue. I upgraded python-MySql version to the latest. It at least managed to tell me that there is an error with one of the MySQL tables. Repaired MySQL Table, and everything is working fine.

For anyone who faces similar issue it is advisable to install latest MySQL-python using pip. Make sure you install before you pip install.

sudo apt-get install libmysqlclient-dev python-dev

answered May 24, 2015 at 17:03

ramdaz

1,8072 gold badges23 silver badges46 bronze badges

Collectives™ on Stack Overflow

Mysql-Python complex query resulting in empty set

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related