Why isn't this query working in python?

[erikcw]

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

[Dave Borne]

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]

On May 25, 10:51 am, "Dave Borne" <[e-mail]:

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]

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]

On May 25, 11:28 am, Carsten Haese <[e-mail]:

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

[Steve Holden]

erikcw wrote:

On May 25, 11:28 am, Carsten Haese <[e-mail]:

>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 [phone] [phone]
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 ----------------

[Carsten Haese]

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

[John Machin]

On May 27, 5:25 am, erikcw <[e-mail]:

On May 25, 11:28 am, Carsten Haese <[e-mail]:

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',).

[erikcw]

On May 26, 8:21 pm, John Machin <[e-mail]:

On May 27, 5:25 am, erikcw <[e-mail]:

On May 25, 11:28 am, Carsten Haese <[e-mail]:

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

[Steve Holden]

Steve Howell wrote:

--- erikcw <er***********@gmail.comwrote:

>>>('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 [phone] [phone]
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 ----------------

[Steve Holden]

erikcw wrote:

On May 26, 8:21 pm, John Machin <[e-mail]:

>On May 27, 5:25 am, erikcw <[e-mail]:

>>On May 25, 11:28 am, Carsten Haese <[e-mail]:
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!

mysqlSELECT 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 [phone] [phone]
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 ----------------

[davelist]

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

erikcw wrote:

>On May 26, 8:21 pm, John Machin <[e-mail]:

>>On May 27, 5:25 am, erikcw <[e-mail]:

On May 25, 11:28 am, Carsten Haese <[e-mail]:
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!

mysqlSELECT 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

[erikcw]

On May 27, 4:01 pm, Steve Holden <[e-mail]:

erikcw wrote:

On May 26, 8:21 pm, John Machin <[e-mail]:

On May 27, 5:25 am, erikcw <[e-mail]:

>On May 25, 11:28 am, Carsten Haese <[e-mail]:
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!

mysqlSELECT 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 [phone] [phone]
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
------------------ Asciimercial ---------------------
Get on the web: Blog, lens and tag your way to fame!!
holdenweb.blogspot.com squidoo.com/pythonology
tagged items: del.icio.us/steve.holden/python
All these services currently offer free registration!
-------------- Thank You for Reading ----------------

The rows I posted are just a small sample (the first 2). There are
tens of thousands of rows in the table.

Also, yes, the query does work when I run it manually against MySQL.

[Carsten Haese]

On Sun, 2007-05-27 at 16:39 -0400, da******@mac.com 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

[Steve Holden]

da******@mac.com wrote:

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

>erikcw wrote:

>>On May 26, 8:21 pm, John Machin <[e-mail]:
On May 27, 5:25 am, erikcw <[e-mail]:

On May 25, 11:28 am, Carsten Haese <[e-mail]:
>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!

mysqlSELECT 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 [phone] [phone]
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 ----------------

[Gabriel Genellina]

En Mon, 28 May 2007 14:53:57 -0300, Dennis Lee Bieber
<wl*****@ix.netcom.comescribió:

On Sun, 27 May 2007 20:35:28 -0400, Carsten Haese <ca*****@uniqsys.com>
declaimed the following in comp.lang.python:

>On Sun, 2007-05-27 at 16:39 -0400, da******@mac.com 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

[erikcw]

On May 28, 2:47 pm, "Gabriel Genellina" <[e-mail]>
wrote:

En Mon, 28 May 2007 14:53:57 -0300, Dennis Lee Bieber
<[e-mail]ó:

On Sun, 27 May 2007 20:35:28 -0400, Carsten Haese <[e-mail]>
declaimed the following in comp.lang.python:

On Sun, 2007-05-27 at 16:39 -0400, [e-mail] 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

[Steve Holden]

erikcw wrote:

On May 28, 2:47 pm, "Gabriel Genellina" <[e-mail]>
wrote:

>En Mon, 28 May 2007 14:53:57 -0300, Dennis Lee Bieber
<[e-mail]ó:

>>On Sun, 27 May 2007 20:35:28 -0400, Carsten Haese <[e-mail]>
declaimed the following in comp.lang.python:
On Sun, 2007-05-27 at 16:39 -0400, [e-mail] 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 [phone] [phone]
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 ----------------