Re: Why isn't this query working in python?
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 '-00-00',
> >>> `expire_date` date NOT NULL default '-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 '-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?
>
Re: Why isn't this query working in python?
erikcw wrote: > On May 28, 2:47 pm, "Gabriel Genellina" <[EMAIL PROTECTED]> > wrote: >> En Mon, 28 May 2007 14:53:57 -0300, Dennis Lee Bieber >> <[EMAIL PROTECTED]> escribió: >> >>> On Sun, 27 May 2007 20:35:28 -0400, Carsten Haese <[EMAIL PROTECTED]> >>> declaimed the following in comp.lang.python: On Sun, 2007-05-27 at 16:39 -0400, [EMAIL PROTECTED] wrote: >> 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) >>>It's confusing, as the example shown is not the common form for >>> parameter passing on the .execute() call -- without the .execute() it is >>> unclear. I presume the .execute() is using *sql to unpack the tuple... >> Yes, the original message said self.amember_cursor.execute(*sql) >> It IS confusing... >> >> -- >> Gabriel Genellina > > This is how I've always writing my queries. I learned it from some > tutorial I found on Google when I started - what is the preferred/ > pythonic way to write this query? > Use separate names for the SQL statement and the argument tuple, then pass them as two separate arguments to cursor.execute(). Apart from anything else this makes it somewhat easier to use the same statement with different arguments. But it is no different in principle than what you are doing now, so the change won't remove your bug, I'm afraid. Could you show us a query (with actual results, if you can publish them) that works manually but doesn't work in Python? There has to be something pretty simple wrong here. 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.comsquidoo.com/pythonology tagged items: del.icio.us/steve.holden/python All these services currently offer free registration! -- Thank You for Reading -- http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
On May 28, 2:47 pm, "Gabriel Genellina" <[EMAIL PROTECTED]> wrote: > En Mon, 28 May 2007 14:53:57 -0300, Dennis Lee Bieber > <[EMAIL PROTECTED]> escribió: > > > On Sun, 27 May 2007 20:35:28 -0400, Carsten Haese <[EMAIL PROTECTED]> > > declaimed the following in comp.lang.python: > > >> On Sun, 2007-05-27 at 16:39 -0400, [EMAIL PROTECTED] wrote: > >> > > 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) > > >It's confusing, as the example shown is not the common form for > > parameter passing on the .execute() call -- without the .execute() it is > > unclear. I presume the .execute() is using *sql to unpack the tuple... > > Yes, the original message said self.amember_cursor.execute(*sql) > It IS confusing... > > -- > Gabriel Genellina This is how I've always writing my queries. I learned it from some tutorial I found on Google when I started - what is the preferred/ pythonic way to write this query? Thanks! Erik -- http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
En Mon, 28 May 2007 14:53:57 -0300, Dennis Lee Bieber <[EMAIL PROTECTED]> escribió: > On Sun, 27 May 2007 20:35:28 -0400, Carsten Haese <[EMAIL PROTECTED]> > declaimed the following in comp.lang.python: > >> On Sun, 2007-05-27 at 16:39 -0400, [EMAIL PROTECTED] wrote: >> > > 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) > > It's confusing, as the example shown is not the common form for > parameter passing on the .execute() call -- without the .execute() it is > unclear. I presume the .execute() is using *sql to unpack the tuple... Yes, the original message said self.amember_cursor.execute(*sql) It IS confusing... -- Gabriel Genellina -- http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
[EMAIL PROTECTED] wrote:
>
> 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 '-00-00',
>>> `expire_date` date NOT NULL default '-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 '-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
Re: Why isn't this query working in python?
On Sun, 2007-05-27 at 16:39 -0400, [EMAIL PROTECTED] wrote: > > 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? No. The poster is using parameter passing, not string formatting. -- Carsten Haese http://informixdb.sourceforge.net -- http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
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 '-00-00',
> > `expire_date` date NOT NULL default '-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 '-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
Re: Why isn't this query working in python?
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 '-00-00',
>> `expire_date` date NOT NULL default '-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 '-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
Re: Why isn't this query working in python?
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 '-00-00',
> `expire_date` date NOT NULL default '-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 '-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.comsquidoo.com/pythonology
tagged items: del.icio.us/steve.holden/python
All these services currently offer free registration!
-- Thank You for Reading
--
http://mail.python.org/mail
Re: Why isn't this query working in python?
Steve Howell wrote:
> --- erikcw <[EMAIL PROTECTED]> wrote:
('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,))
()
>> Here is a copy of the table schema and the first 2
>> rows.
>>
>
> Does your table actually contain any rows that meet
> the criteria that expire_date is in the future,
> completed is 1, product id is between 11 and 21, etc.?
>
> Have you tried debugging the SQL outside of Python?
>
This thread all started because a manual query was claimed to succeed
when a Python-based one was claimed not to.
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.comsquidoo.com/pythonology
tagged items: del.icio.us/steve.holden/python
All these services currently offer free registration!
-- Thank You for Reading
--
http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
--- erikcw <[EMAIL PROTECTED]> wrote:
> >
> > > ('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,))
> > > ()
> >
>
> Here is a copy of the table schema and the first 2
> rows.
>
Does your table actually contain any rows that meet
the criteria that expire_date is in the future,
completed is 1, product id is between 11 and 21, etc.?
Have you tried debugging the SQL outside of Python?
Ready
for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/
--
http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
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 '-00-00',
`expire_date` date NOT NULL default '-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 '-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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
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',).
--
http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
On Sat, 2007-05-26 at 12:25 -0700, erikcw wrote:
> 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?
We won't know until you post more information. Show us the create table
statement for amember_payments, show us the query you're running
manually and the results you're getting from running the query manually.
Maybe that'll give us a clue. (Include the member_id, expire_date,
completed, and product_id columns in your manual query, please.)
--
Carsten Haese
http://informixdb.sourceforge.net
--
http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
erikcw 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?
>
> Thanks!
> Erik
>
It *could* be that there aren't any data meeting the criteria in your
query. Is there any chance that the current date/time has passed the
expire date since you ran the query manually?
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.comsquidoo.com/pythonology
tagged items: del.icio.us/steve.holden/python
All these services currently offer free registration!
-- Thank You for Reading
--
http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
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?
Thanks!
Erik
--
http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
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 Haese http://informixdb.sourceforge.net -- http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
On May 25, 10:51 am, "Dave Borne" <[EMAIL PROTECTED]> 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' ? > (I'm asking because I'm not entirely sure how the execute command is > doing the substitution) > > -Dave Hi Dave, I'm not sure. I've been using this format for all of my other queries without issue. What would the query look like with the bind variable instead? Erik -- http://mail.python.org/mailman/listinfo/python-list
Re: Why isn't this query working in python?
> 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' ? (I'm asking because I'm not entirely sure how the execute command is doing the substitution) -Dave -- http://mail.python.org/mailman/listinfo/python-list
Why isn't this query working in python?
Hi all, I'm trying to run the following query: amember_db = MySQLdb.connect(host="localhost", user="**", passwd="*", db="***") # create a cursor self.amember_cursor = amember_db.cursor() # execute SQL statement 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) print sql self.amember_cursor.execute(*sql) amember_result = self.cursor.fetchall() print amember_result When I manually run the SQL query in mysql, I get a result, but for some reason I get an empty result in python. Here are some notes that may be of importance. -I have 2 connections open to MySQL in this script (they are both in seperate objects of course) - self.uid = 1972L -print amember_result = () Any ideas? Thanks! Erik -- http://mail.python.org/mailman/listinfo/python-list
