Why isn't this query working in python?
erikcw
erikwickstrom at gmail.com
Mon Jun 25 15:13:33 EDT 2007
On May 27, 11:06 pm, Steve Holden <s... at holdenweb.com> wrote:
> davel... at mac.com wrote:
>
> > On May 27, 2007, at 4:01 PM, Steve Holden wrote:
>
> >>erikcwwrote:
> >>> 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 ----------------
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:"larry at israeltour.com";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:"kirtchristensen at gmail.com";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:"superbusiness at gmail.com";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:"sales at unusualonline.co.uk";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
More information about the Python-list
mailing list