On May 27, 4:01 pm, Steve Holden <[EMAIL PROTECTED]> 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) > > 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 ----------------
The rows I posted are just a small sample (the first 2). There are tens of thousands of rows in the table. Also, yes, the query does work when I run it manually against MySQL. -- http://mail.python.org/mailman/listinfo/python-list
