Why isn't this query working in python?

[erikcw]

Hi all,

I'm trying to run the following query:

amember_db = MySQLdb.connect (host="localhos t", user="********* *",
passwd="*****", db="*******")
# create a cursor
self.amember_cu rsor = amember_db.curs or()
# execute SQL statement
sql = """SELECT payment_id FROM amember_payment s 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_cu rsor.execute(*s ql)
amember_result = self.cursor.fet chall()
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

[Dave Borne]

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

[erikcw]

Re: Why isn't this query working in python?

On May 25, 10:51 am, "Dave Borne" <dbo...@gmail.c omwrote:

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

[Carsten Haese]

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

[erikcw]

Re: Why isn't this query working in python?

On May 25, 11:28 am, Carsten Haese <cars...@uniqsy s.comwrote:

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.sour ceforge.net

I tried adding the comma to make it a sequence - but now change.

('SELECT payment_id FROM amember_payment s 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

[Steve Holden]

Re: Why isn't this query working in python?

erikcw wrote:

On May 25, 11:28 am, Carsten Haese <cars...@uniqsy s.comwrote:

>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.sour ceforge.net

>
I tried adding the comma to make it a sequence - but now change.
>
('SELECT payment_id FROM amember_payment s 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.blogs pot.com squidoo.com/pythonology
tagged items: del.icio.us/steve.holden/python
All these services currently offer free registration!
-------------- Thank You for Reading ----------------

[Carsten Haese]

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_payment s 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_payment s, 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

[John Machin]

Re: Why isn't this query working in python?

On May 27, 5:25 am, erikcw <erikwickst...@ gmail.comwrote:

On May 25, 11:28 am, Carsten Haese <cars...@uniqsy s.comwrote:
>
>
>

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.sour ceforge.net

>
I tried adding the comma to make it a sequence - but now change.
>
('SELECT payment_id FROM amember_payment s 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',).

[erikcw]

Re: Why isn't this query working in python?

On May 26, 8:21 pm, John Machin <sjmac...@lexic on.netwrote:

On May 27, 5:25 am, erikcw <erikwickst...@ gmail.comwrote:
>
>
>

On May 25, 11:28 am, Carsten Haese <cars...@uniqsy s.comwrote:

>

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.sour ceforge.net

>

I tried adding the comma to make it a sequence - but now change.

>

('SELECT payment_id FROM amember_payment s 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_paymen ts`
--

CREATE TABLE `amember_paymen ts` (
`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_TIMESTA MP on update
CURRENT_TIMESTA MP,
`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`,`pr oduct_id`),
KEY `tm_completed` (`tm_completed` ,`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 11020 ;

--
-- Dumping data for table `amember_paymen ts`
--

INSERT INTO `amember_paymen ts` 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_paymen ts` 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

[Steve Holden]

Re: Why isn't this query working in python?

Steve Howell wrote:

--- erikcw <erikwickstrom@ gmail.comwrote:

>>>('SELECT payment_id FROM amember_payment s WHERE

>member_id=%s AND

>>>expire_dat e 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.blogs pot.com squidoo.com/pythonology
tagged items: del.icio.us/steve.holden/python
All these services currently offer free registration!
-------------- Thank You for Reading ----------------

[Steve Holden]

Re: Why isn't this query working in python?

erikcw wrote:

On May 26, 8:21 pm, John Machin <sjmac...@lexic on.netwrote:

>On May 27, 5:25 am, erikcw <erikwickst...@ gmail.comwrote:
>>
>>
>>

>>On May 25, 11:28 am, Carsten Haese <cars...@uniqsy s.comwrote:
>>>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.sour ceforge.net
>>I tried adding the comma to make it a sequence - but now change.
>>('SELECT payment_id FROM amember_payment s 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_paymen ts`
--
>
CREATE TABLE `amember_paymen ts` (
`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_TIMESTA MP on update
CURRENT_TIMESTA MP,
`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`,`pr oduct_id`),
KEY `tm_completed` (`tm_completed` ,`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 11020 ;
>
--
-- Dumping data for table `amember_paymen ts`
--
>
INSERT INTO `amember_paymen ts` 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_paymen ts` 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!

mysqlSELECT expire_date, NOW() FROM amember_payment s;
+-------------+---------------------+
| 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_payment s 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.blogs pot.com squidoo.com/pythonology
tagged items: del.icio.us/steve.holden/python
All these services currently offer free registration!
-------------- Thank You for Reading ----------------

[davelist@mac.com]

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 <sjmac...@lexic on.netwrote:

>>On May 27, 5:25 am, erikcw <erikwickst...@ gmail.comwrote:
>>>
>>>
>>>
>>>On May 25, 11:28 am, Carsten Haese <cars...@uniqsy s.comwrote:
>>>>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
>>>>unfortunate ly, %s.
>>>>The OP is using parameter substitution correctly, though in an
>>>>obfuscate d 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.sour ceforge.net
>>>I tried adding the comma to make it a sequence - but now change.
>>>('SELECT payment_id FROM amember_payment s WHERE member_id=%s AND
>>>expire_dat e 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_paymen ts`
>--
>>
>CREATE TABLE `amember_paymen ts` (
> `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_TIMESTA MP on update
>CURRENT_TIMEST AMP,
> `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`,`pr oduct_id`),
> KEY `tm_completed` (`tm_completed` ,`product_id`)
>) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 11020 ;
>>
>--
>-- Dumping data for table `amember_paymen ts`
>--
>>
>INSERT INTO `amember_paymen ts` 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_paymen ts` 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!
>
mysqlSELECT expire_date, NOW() FROM amember_payment s;
+-------------+---------------------+
| 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_payment s 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

[erikcw]

Re: Why isn't this query working in python?

On May 27, 4:01 pm, Steve Holden <s...@holdenweb .comwrote:

erikcw wrote:

On May 26, 8:21 pm, John Machin <sjmac...@lexic on.netwrote:

On May 27, 5:25 am, erikcw <erikwickst...@ gmail.comwrote:

>

>On May 25, 11:28 am, Carsten Haese <cars...@uniqsy s.comwrote:
>>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.sour ceforge.net
>I tried adding the comma to make it a sequence - but now change.
>('SELECT payment_id FROM amember_payment s 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_paymen ts`
--

>

CREATE TABLE `amember_paymen ts` (
`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_TIMESTA MP on update
CURRENT_TIMESTA MP,
`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`,`pr oduct_id`),
KEY `tm_completed` (`tm_completed` ,`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 11020 ;

>

--
-- Dumping data for table `amember_paymen ts`
--

>

INSERT INTO `amember_paymen ts` 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_paymen ts` 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!
>
mysqlSELECT expire_date, NOW() FROM amember_payment s;
+-------------+---------------------+
| 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_payment s 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.blogs pot.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.

[Carsten Haese]

Re: Why isn't this query working in python?

On Sun, 2007-05-27 at 16:39 -0400, davelist@mac.co m wrote:

sql = """SELECT payment_id FROM amember_payment s 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

[Steve Holden]

Re: Why isn't this query working in python?

davelist@mac.co m wrote:

>
On May 27, 2007, at 4:01 PM, Steve Holden wrote:
>

>erikcw wrote:

>>On May 26, 8:21 pm, John Machin <sjmac...@lexic on.netwrote:
>>>On May 27, 5:25 am, erikcw <erikwickst...@ gmail.comwrote:
>>>>
>>>>
>>>>
>>>>On May 25, 11:28 am, Carsten Haese <cars...@uniqsy s.comwrote:
>>>>>On Fri, 2007-05-25 at 09:51 -0500, Dave Borne wrote:
>>>>>>>I'm trying to run the following query:
>>>>>>...
>>>>>>>member_i d=%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
>>>>>unfortunat ely, %s.
>>>>>The OP is using parameter substitution correctly, though in an
>>>>>obfuscat ed fashion. 'sql' is a misnamed tuple containing both the
>>>>>query
>>>>>string *and* the parameters, which is being unpacked with '*' into
>>>>>two
>>>>>argument s 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.sour ceforge.net
>>>>I tried adding the comma to make it a sequence - but now change.
>>>>('SELECT payment_id FROM amember_payment s WHERE member_id=%s AND
>>>>expire_da te 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_paymen ts`
>>--
>>>
>>CREATE TABLE `amember_paymen ts` (
>> `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_TIMESTA MP on update
>>CURRENT_TIMES TAMP,
>> `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`,`pr oduct_id`),
>> KEY `tm_completed` (`tm_completed` ,`product_id`)
>>) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 11020 ;
>>>
>>--
>>-- Dumping data for table `amember_paymen ts`
>>--
>>>
>>INSERT INTO `amember_paymen ts` 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_paymen ts` 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!
>>
>mysqlSELECT expire_date, NOW() FROM amember_payment s;
>+-------------+---------------------+
>| 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_payment s 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.blogs pot.com squidoo.com/pythonology
tagged items: del.icio.us/steve.holden/python
All these services currently offer free registration!
-------------- Thank You for Reading ----------------