On May 27, 2007, at 4:01 PM, Steve Holden wrote: > erikcw wrote: >> On May 26, 8:21 pm, John Machin <[EMAIL PROTECTED]> wrote: >>> On May 27, 5:25 am, erikcw <[EMAIL PROTECTED]> wrote: >>> >>> >>> >>>> On May 25, 11:28 am, Carsten Haese <[EMAIL PROTECTED]> 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 -- http://mail.python.org/mailman/listinfo/python-list
