On May 27, 11:06 pm, Steve Holden <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > On May 27, 2007, at 4:01 PM, Steve Holden wrote: > > >>erikcwwrote: > >>> 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? > > 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 ----------------
The SQL my script is generating is still returning an empty recordset. However, when I run SELECT * FROM amember_payments WHERE member_id=2124 AND expire_date > NOW() AND completed=1 AND (product_id >11 AND product_id <21) directly in phpMyAdmin - it works. Here is an updated subset of the database that should work better for testing: 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=11508 ; INSERT INTO `amember_payments` VALUES (10710, 970, 8, '2007-05-05', '2007-06-05', 'authorize_aim', '1424138558', '19.95', 1, '71.229.108.131', 'a:6:{s:15:"COUPON_DISCOUNT";N;s:10:"TAX_AMOUNT";N;s: 5:"TAXES";N;i:0;a:1:{s:12:"RENEWAL_ORIG";s:19:"RENEWAL_ORIG: 10325";}i: 1;a:25:{s:7:"x_Login";s:12:"478459b79866";s:9:"x_Version";s:3:"3.1";s: 12:"x_Delim_Data";s:4:"True";s:10:"x_Tran_Key";s: 16:"eeRay89XQNAic8KJ";s:12:"x_Delim_Char";s:1:"|";s: 13:"x_Invoice_Num";s:9:"10710-329";s:8:"x_Amount";s:5:"19.95";s: 15:"x_Currency_Code";s:3:"USD";s:10:"x_Card_Num";s:19:"**** **** **** 4020";s:10:"x_Exp_Date";s:4:"1007";s:6:"x_Type";s:12:"AUTH_CAPTURE";s: 16:"x_Relay_Response";s:5:"FALSE";s:7:"x_Email";s: 20:"[EMAIL PROTECTED]";s:13:"x_Description";s:11:"The AdTool-";s: 9:"x_Cust_ID";s:3:"970";s:12:"x_First_Name";s:5:"Larry";s: 11:"x_Last_Name";s:6:"Ritter";s:9:"x_Address";s:21:"107 E Mt Pleasant Ave";s:6:"x_City";s:10:"Livingston";s:7:"x_State";s:2:"NJ";s: 5:"x_Zip";s:5:"07039";s:9:"x_Country";s:2:"US";s:9:"x_Company";s: 27:"Israel Tour Connection, LLC";s:13:"x_Customer_IP";s: 13:"64.139.74.236";s:7:"x_Phone";s:10:"9735352575";}i:2;a:7:{s: 6:"RESULT";s:1:"1";s:10:"RESULT_SUB";s:1:"1";s:11:"REASON_CODE";s: 1:"1";s:7:"RESPMSG";s:35:"This transaction has been approved.";s: 3:"AVS";s:1:"Y";s:5:"PNREF";s:10:"1424138558";s:9:"CVV_VALID";s: 0:"";}}', '2007-05-06 10:52:25', 0, 'cc:d41d8cd98f00b204e9800998ecf8427e', 0, '2007-05-06 10:52:22', '2007-05-06 10:52:25', '0.00'); INSERT INTO `amember_payments` VALUES (10711, 2124, 18, '2007-05-05', '2007-06-26', 'authorize_aim', '1424138585', '97.00', 1, '71.229.108.131', 'a:8:{s:15:"COUPON_DISCOUNT";N;s:10:"TAX_AMOUNT";N;s: 5:"TAXES";N;i:0;a:1:{s:12:"RENEWAL_ORIG";s:19:"RENEWAL_ORIG: 10326";}i: 1;a:25:{s:7:"x_Login";s:12:"478459b79866";s:9:"x_Version";s:3:"3.1";s: 12:"x_Delim_Data";s:4:"True";s:10:"x_Tran_Key";s: 16:"eeRay89XQNAic8KJ";s:12:"x_Delim_Char";s:1:"|";s: 13:"x_Invoice_Num";s:9:"10711-845";s:8:"x_Amount";s:5:"97.00";s: 15:"x_Currency_Code";s:3:"USD";s:10:"x_Card_Num";s:19:"**** **** **** 6006";s:10:"x_Exp_Date";s:4:"0709";s:6:"x_Type";s:12:"AUTH_CAPTURE";s: 16:"x_Relay_Response";s:5:"FALSE";s:7:"x_Email";s: 25:"[EMAIL PROTECTED]";s:13:"x_Description";s:21:"Winner Alert Pro User";s:9:"x_Cust_ID";s:4:"2124";s:12:"x_First_Name";s:4:"Kirt";s: 11:"x_Last_Name";s:11:"Christensen";s:9:"x_Address";s:19:"3402 S. Melissa Dr.";s:6:"x_City";s:14:"Spokane Valley";s:7:"x_State";s: 2:"WA";s:5:"x_Zip";s:5:"99206";s:9:"x_Country";s:2:"US";s: 9:"x_Company";s:0:"";s:13:"x_Customer_IP";s:14:"67.185.167.174";s: 7:"x_Phone";s:12:"509-242-7867";}i:2;a:7:{s:6:"RESULT";s:1:"1";s: 10:"RESULT_SUB";s:1:"1";s:11:"REASON_CODE";s:1:"1";s:7:"RESPMSG";s: 35:"This transaction has been approved.";s:3:"AVS";s:1:"Y";s: 5:"PNREF";s:10:"1424138585";s:9:"CVV_VALID";s:0:"";}s: 16:"orig_expire_date";s:10:"2007-06-05";s:8:"prorated";s:7:"3, 8, 8";}', '2007-06-25 09:28:38', 0, 'cc:d41d8cd98f00b204e9800998ecf8427e', 0, '2007-05-06 10:52:26', '2007-05-06 10:52:28', '0.00'); INSERT INTO `amember_payments` VALUES (10712, 2006, 10, '2007-05-05', '2008-05-05', 'authorize_aim', '', '147.00', 0, '71.229.108.131', 'a:6: {s:15:"COUPON_DISCOUNT";N;s:10:"TAX_AMOUNT";N;s:5:"TAXES";N;i:0;a:1:{s: 12:"RENEWAL_ORIG";s:18:"RENEWAL_ORIG: 5711";}i:1;a:25:{s:7:"x_Login";s: 12:"478459b79866";s:9:"x_Version";s:3:"3.1";s:12:"x_Delim_Data";s: 4:"True";s:10:"x_Tran_Key";s:16:"eeRay89XQNAic8KJ";s: 12:"x_Delim_Char";s:1:"|";s:13:"x_Invoice_Num";s:9:"10712-465";s: 8:"x_Amount";s:6:"147.00";s:15:"x_Currency_Code";s:3:"USD";s: 10:"x_Card_Num";s:19:"**** **** **** 2785";s:10:"x_Exp_Date";s: 4:"0908";s:6:"x_Type";s:12:"AUTH_CAPTURE";s:16:"x_Relay_Response";s: 5:"FALSE";s:7:"x_Email";s:23:"[EMAIL PROTECTED]";s: 13:"x_Description";s:14:"AdTool Annual-";s:9:"x_Cust_ID";s:4:"2006";s: 12:"x_First_Name";s:9:"Dimitrios";s:11:"x_Last_Name";s:9:"Katsoulis";s: 9:"x_Address";s:18:"430 Briarvista Way";s:6:"x_City";s:7:"Atlanta";s: 7:"x_State";s:2:"GA";s:5:"x_Zip";s:5:"30329";s:9:"x_Country";s: 2:"US";s:9:"x_Company";s:0:"";s:13:"x_Customer_IP";s: 13:"24.98.190.106";s:7:"x_Phone";s:12:"404-634-0031";}i:2;a:7:{s: 6:"RESULT";s:1:"2";s:10:"RESULT_SUB";s:1:"1";s:11:"REASON_CODE";s: 1:"2";s:7:"RESPMSG";s:35:"This transaction has been declined.";s: 3:"AVS";s:1:"Y";s:5:"PNREF";s:10:"1424138607";s:9:"CVV_VALID";s: 0:"";}}', '2007-05-06 10:52:30', 0, '', 0, '2007-05-06 10:52:28', NULL, '0.00'); INSERT INTO `amember_payments` VALUES (10713, 1574, 8, '2007-05-06', '2007-06-06', 'authorize_aim', '', '19.95', 0, '71.229.108.131', 'a:6: {s:15:"COUPON_DISCOUNT";N;s:10:"TAX_AMOUNT";N;s:5:"TAXES";N;i:0;a:1:{s: 12:"RENEWAL_ORIG";s:19:"RENEWAL_ORIG: 10192";}i:1;a:25:{s: 7:"x_Login";s:12:"478459b79866";s:9:"x_Version";s:3:"3.1";s: 12:"x_Delim_Data";s:4:"True";s:10:"x_Tran_Key";s: 16:"eeRay89XQNAic8KJ";s:12:"x_Delim_Char";s:1:"|";s: 13:"x_Invoice_Num";s:9:"10713-206";s:8:"x_Amount";s:5:"19.95";s: 15:"x_Currency_Code";s:3:"USD";s:10:"x_Card_Num";s:19:"**** **** **** 9006";s:10:"x_Exp_Date";s:4:"0407";s:6:"x_Type";s:12:"AUTH_CAPTURE";s: 16:"x_Relay_Response";s:5:"FALSE";s:7:"x_Email";s: 25:"[EMAIL PROTECTED]";s:13:"x_Description";s:11:"The AdTool-";s:9:"x_Cust_ID";s:4:"1574";s:12:"x_First_Name";s:4:"John";s: 11:"x_Last_Name";s:9:"Flowerdew";s:9:"x_Address";s:14:"8A Oxted Green";s:6:"x_City";s:18:"Milford, Godalming";s:7:"x_State";s:2:"XX";s: 5:"x_Zip";s:7:"GU8 5DA";s:9:"x_Country";s:2:"GB";s:9:"x_Company";s: 0:"";s:13:"x_Customer_IP";s:14:"80.176.190.185";s:7:"x_Phone";s: 15:"0044 1483861858";}i:2;a:7:{s:6:"RESULT";s:1:"3";s: 10:"RESULT_SUB";s:1:"1";s:11:"REASON_CODE";s:1:"8";s:7:"RESPMSG";s: 28:"The credit card has expired.";s:3:"AVS";s:1:"P";s:5:"PNREF";s: 1:"0";s:9:"CVV_VALID";s:0:"";}}', '2007-05-06 10:52:31', 0, '', 0, '2007-05-06 10:52:30', NULL, '0.00'); Thanks for your help! Erik -- http://mail.python.org/mailman/listinfo/python-list