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

On May 27, 4:01 pm, Steve Holden <s...@xxxxxxxxxxxxx> wrote:
erikcw wrote:
On May 26, 8:21 pm, John Machin <sjmac...@xxxxxxxxxxx> wrote:
On May 27, 5:25 am, erikcw <erikwickst...@xxxxxxxxx> wrote:

On May 25, 11:28 am, Carsten Haese <cars...@xxxxxxxxxxx> wrote:
On Fri, 2007-05-25 at 09:51 -0500, Dave Borne wrote:
I'm trying to run the following query:
...
member_id=%s AND expire_date > NOW() AND completed=1 AND (product_id
Shouldn't you be using the bind variable '?' instead of '%s' ?
The parameter placeholder for MySQLdb is, indeed and unfortunately, %s.
The OP is using parameter substitution correctly, though in an
obfuscated fashion. 'sql' is a misnamed tuple containing both the query
string *and* the parameters, which is being unpacked with '*' into two
arguments to the execute call.
The only problem I see is that the parameters should be a sequence, i.e.
(self.uid,) instead of just (self.uid).
HTH,
--
Carsten Haesehttp://informixdb.sourceforge.net
I tried adding the comma to make it a sequence - but now change.
('SELECT payment_id FROM amember_payments WHERE member_id=%s AND
expire_date > NOW() AND completed=1 AND (product_id >11 AND product_id
<21)', (1608L,))
()
What else could it be?
Possibly a type mismatch. How is member_id declared in the CREATE
TABLE? For diagnostic purposes, try passing in (1608,) and ('1608',).

Here is a copy of the table schema and the first 2 rows.

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
--http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 27, 2007 at 11:29 AM
-- Server version: 5.0.27
-- PHP Version: 4.4.2
--
-- Database: `lybp_lybp`
--

-- --------------------------------------------------------

--
-- Table structure for table `amember_payments`
--

CREATE TABLE `amember_payments` (
`payment_id` int(11) NOT NULL auto_increment,
`member_id` int(11) NOT NULL default '0',
`product_id` int(11) NOT NULL default '0',
`begin_date` date NOT NULL default '0000-00-00',
`expire_date` date NOT NULL default '0000-00-00',
`paysys_id` varchar(32) NOT NULL default '',
`receipt_id` varchar(32) NOT NULL default '',
`amount` decimal(12,2) NOT NULL default '0.00',
`completed` smallint(6) default '0',
`remote_addr` varchar(15) NOT NULL default '',
`data` text,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`aff_id` int(11) NOT NULL default '0',
`payer_id` varchar(255) NOT NULL default '',
`coupon_id` int(11) NOT NULL default '0',
`tm_added` datetime NOT NULL default '0000-00-00 00:00:00',
`tm_completed` datetime default NULL,
`tax_amount` decimal(12,2) NOT NULL default '0.00',
PRIMARY KEY (`payment_id`),
KEY `member_id` (`member_id`),
KEY `payer_id` (`payer_id`),
KEY `coupon_id` (`coupon_id`),
KEY `tm_added` (`tm_added`,`product_id`),
KEY `tm_completed` (`tm_completed`,`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11020 ;

--
-- Dumping data for table `amember_payments`
--

INSERT INTO `amember_payments` VALUES (423, 107, 1, '2004-10-01',
'2004-10-21', 'authorize_aim', '5687944', 3.95, 1, '', NULL,
'2004-11-30 19:21:43', 0, '', 0, '2004-11-30 19:21:43', '2004-11-30
19:21:43', 0.00);
INSERT INTO `amember_payments` VALUES (422, 107, 1, '2004-10-22',
'2004-11-21', 'authorize_aim', '5873225', 9.95, 1, '', NULL,
'2004-11-30 19:22:18', 0, '', 0, '2004-11-30 19:20:13', '2004-11-30
19:20:13', 0.00);

Thanks for your help!
Erik

I feel obliged to point out that there ARE no rows meeting the criteria
you query specified!

mysql> SELECT expire_date, NOW() FROM amember_payments;
+-------------+---------------------+
| expire_date | NOW() |
+-------------+---------------------+
| 2004-10-21 | 2007-05-27 15:59:21 |
| 2004-11-21 | 2007-05-27 15:59:21 |
+-------------+---------------------+
2 rows in set (0.02 sec)

mysql>

So I am not sure how you managed to get a manual query to work, but do
be sure that the Python query you mentioned at the start of the thread

sql = """SELECT payment_id FROM amember_payments WHERE
member_id=%s AND expire_date > NOW() AND completed=1 AND (product_id
> >11 AND product_id <21)""", (self.uid)

doesn't stand a chance of returning any results unless you use a time
machine to go back almost three years!

regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
------------------ Asciimercial ---------------------
Get on the web: Blog, lens and tag your way to fame!!
holdenweb.blogspot.com squidoo.com/pythonology
tagged items: del.icio.us/steve.holden/python
All these services currently offer free registration!
-------------- Thank You for Reading ----------------

The 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.

.

Relevant Pages

  • CSVDE export of users: how to make whenCreated query the currentda
    ... I'm exporting users in AD using CSVDE command line base on a manual query ... How to make the whenCreated query to use the current date without hardcoding ...
    (microsoft.public.win2000.active_directory)
  • Re: Problem with SQL and Recordset
    ... This assumes that the name of the subform control on the parent form is the ... NextSuffix field from the "query" to be entered into the Suffix field on ... Dim mySQL As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: [Q]Problems related to the MySQL linked Server.
    ... The error messge when I query data with following SQL. ... select @v_intUserNo = uno ... where userid = 'testid' ... I have two questions about the linked server for the MySQL. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Performance MySQL
    ... schicke erst den Query an die Datenbank ab, ... Möglichkeit von Concurrent Inserts nur MyISAM Storages ein. ... > MySQL bis jetzt noch nichts zu bieten. ... Welcher Tabellentyp? ...
    (de.comp.datenbanken.mysql)
  • Re: where to get mySQL questions answered?
    ... After resolving some performance issues with mysql in building initial ... soon as I add an OR to my WHERE x LIKE '%blah%' type queries. ... My php code dynamically builds an sql query. ...
    (comp.lang.php)