Why isn't this query working in python?

[Steve Holden]

davelist at mac.com wrote:
> 
> On May 27, 2007, at 4:01 PM, Steve Holden wrote:
> 
>> erikcw wrote:
>>> On May 26, 8:21 pm, John Machin <sjmac... at lexicon.net> wrote:
>>>> On May 27, 5:25 am, erikcw <erikwickst... at gmail.com> wrote:
>>>>
>>>>
>>>>
>>>>> On May 25, 11:28 am, Carsten Haese <cars... at uniqsys.com> wrote:
>>>>>> On Fri, 2007-05-25 at 09:51 -0500, Dave Borne wrote:
>>>>>>>> I'm trying to run the following query:
>>>>>>> ...
>>>>>>>> member_id=%s AND expire_date > NOW() AND completed=1 AND 
>>>>>>>> (product_id
>>>>>>> Shouldn't you be using the bind variable '?' instead of '%s' ?
>>>>>> The parameter placeholder for MySQLdb is, indeed and 
>>>>>> unfortunately, %s.
>>>>>> The OP is using parameter substitution correctly, though in an
>>>>>> obfuscated fashion. 'sql' is a misnamed tuple containing both the 
>>>>>> query
>>>>>> string *and* the parameters, which is being unpacked with '*' into 
>>>>>> two
>>>>>> arguments to the execute call.
>>>>>> The only problem I see is that the parameters should be a 
>>>>>> sequence, i.e.
>>>>>> (self.uid,) instead of just (self.uid).
>>>>>> HTH,
>>>>>> -- 
>>>>>> Carsten Haesehttp://informixdb.sourceforge.net
>>>>> I tried adding the comma to make it a sequence - but now change.
>>>>> ('SELECT payment_id FROM amember_payments WHERE member_id=%s AND
>>>>> expire_date > NOW() AND completed=1 AND (product_id >11 AND product_id
>>>>> <21)', (1608L,))
>>>>> ()
>>>>> What else could it be?
>>>> Possibly a type mismatch. How is member_id declared in the CREATE
>>>> TABLE? For diagnostic purposes, try passing in (1608,) and ('1608',).
>>>
>>> Here is a copy of the table schema and the first 2 rows.
>>>
>>> -- phpMyAdmin SQL Dump
>>> -- version 2.9.0.2
>>> -- http://www.phpmyadmin.net
>>> -- 
>>> -- Host: localhost
>>> -- Generation Time: May 27, 2007 at 11:29 AM
>>> -- Server version: 5.0.27
>>> -- PHP Version: 4.4.2
>>> -- 
>>> -- Database: `lybp_lybp`
>>> -- 
>>>
>>> -- --------------------------------------------------------
>>>
>>> -- 
>>> -- Table structure for table `amember_payments`
>>> -- 
>>>
>>> CREATE TABLE `amember_payments` (
>>>   `payment_id` int(11) NOT NULL auto_increment,
>>>   `member_id` int(11) NOT NULL default '0',
>>>   `product_id` int(11) NOT NULL default '0',
>>>   `begin_date` date NOT NULL default '0000-00-00',
>>>   `expire_date` date NOT NULL default '0000-00-00',
>>>   `paysys_id` varchar(32) NOT NULL default '',
>>>   `receipt_id` varchar(32) NOT NULL default '',
>>>   `amount` decimal(12,2) NOT NULL default '0.00',
>>>   `completed` smallint(6) default '0',
>>>   `remote_addr` varchar(15) NOT NULL default '',
>>>   `data` text,
>>>   `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update
>>> CURRENT_TIMESTAMP,
>>>   `aff_id` int(11) NOT NULL default '0',
>>>   `payer_id` varchar(255) NOT NULL default '',
>>>   `coupon_id` int(11) NOT NULL default '0',
>>>   `tm_added` datetime NOT NULL default '0000-00-00 00:00:00',
>>>   `tm_completed` datetime default NULL,
>>>   `tax_amount` decimal(12,2) NOT NULL default '0.00',
>>>   PRIMARY KEY  (`payment_id`),
>>>   KEY `member_id` (`member_id`),
>>>   KEY `payer_id` (`payer_id`),
>>>   KEY `coupon_id` (`coupon_id`),
>>>   KEY `tm_added` (`tm_added`,`product_id`),
>>>   KEY `tm_completed` (`tm_completed`,`product_id`)
>>> ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11020 ;
>>>
>>> -- 
>>> -- Dumping data for table `amember_payments`
>>> -- 
>>>
>>> INSERT INTO `amember_payments` VALUES (423, 107, 1, '2004-10-01',
>>> '2004-10-21', 'authorize_aim', '5687944', 3.95, 1, '', NULL,
>>> '2004-11-30 19:21:43', 0, '', 0, '2004-11-30 19:21:43', '2004-11-30
>>> 19:21:43', 0.00);
>>> INSERT INTO `amember_payments` VALUES (422, 107, 1, '2004-10-22',
>>> '2004-11-21', 'authorize_aim', '5873225', 9.95, 1, '', NULL,
>>> '2004-11-30 19:22:18', 0, '', 0, '2004-11-30 19:20:13', '2004-11-30
>>> 19:20:13', 0.00);
>>>
>>> Thanks for your help!
>>> Erik
>>>
>> I feel obliged to point out that there ARE no rows meeting the criteria
>> you query specified!
>>
>> mysql> SELECT expire_date, NOW() FROM amember_payments;
>> +-------------+---------------------+
>> | expire_date | NOW()               |
>> +-------------+---------------------+
>> | 2004-10-21  | 2007-05-27 15:59:21 |
>> | 2004-11-21  | 2007-05-27 15:59:21 |
>> +-------------+---------------------+
>> 2 rows in set (0.02 sec)
>>
>> mysql>
>>
>> So I am not sure how you managed to get a manual query to work, but do
>> be sure that the Python query you mentioned at the start of the thread
>>
>>              sql = """SELECT payment_id FROM amember_payments WHERE
>> member_id=%s AND expire_date > NOW() AND completed=1 AND (product_id
>>>> 11 AND product_id <21)""", (self.uid)
>>
> 
> 
> And doesn't the above comma, need to be a percent symbol?
> 
Nope.

> Dave
> 
> 
>> doesn't stand a chance of returning any results unless you use a time
>> machine to go back almost three years!
>>
>> regards
>>   Steve
> 
> 
> 


-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC/Ltd           http://www.holdenweb.com
Skype: holdenweb      http://del.icio.us/steve.holden
------------------ Asciimercial ---------------------
Get on the web: Blog, lens and tag your way to fame!!
holdenweb.blogspot.com        squidoo.com/pythonology
tagged items:         del.icio.us/steve.holden/python
All these services currently offer free registration!
-------------- Thank You for Reading ----------------