Why isn't this query working in python?
davelist at mac.com
davelist at mac.com
Sun May 27 16:39:06 EDT 2007
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?
Dave
> doesn't stand a chance of returning any results unless you use a time
> machine to go back almost three years!
>
> regards
> Steve
More information about the Python-list
mailing list