MYSql, CGI web page search code not working

[Fred]

OK,

I can now successfully enter data into my MySQL database through my CGI
web page. I can click a button and retrieve all the records, but I can
not seem to get the search code to work.

Below is the web page code and then my Python script. When I click my
search button it just gives me all the records

I know this line is executing: cursor.execute(Select * from phone
where name = name order by name)

Because I played with the order by but it seems to ignore my where
clause.

No matter what I type in the form text box (or even if I leave it
blank) I get all the records.

I can hard code this line to: cursor.execute(Select * from phone where
name = 'Fred' order by name)

and it returns the one record corectly.

Any ideas?

Fred

--
form action=cgi-bin/searchdata.py
 pEnter the name to find:
pinput type=text name=name size=30
input type=submit value=Search
/form
--

#!/usr/local/bin/python
print Content-Type: text/html\n
import MySQLdb
import cgi

db=MySQLdb.connect(host = 'localhost', db = 'phone')
cursor=db.cursor()
cursor.execute(Select * from phone where name = name order by name)

result = cursor.fetchall()
for record in result:
   print 'p'
   print record[0]
   print '--'
   print record[1]
   print '--'
   print record[2]
   print '--'
   print record[3]
   print '/p'

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MYSql, CGI web page search code not working

[Diez B. Roggisch]

 
 db=MySQLdb.connect(host = 'localhost', db = 'phone')
 cursor=db.cursor()
 cursor.execute(Select * from phone where name = name order by name)

You don't parametrize the query. The where-clause thus is a tautology, 
as the name is always the name.

Do something like this:


cursor.execute(Select * from phone where name = ? order by name, (name,))


Actually it might be necessary to use something different from the ? to 
specify the parameter - that depends on the paramstyle of your DB-Api. 
Check that in the interpreter with

import MySQLdb
print mySQLdb.paramstyle



Diez
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MYSql, CGI web page search code not working

[Fred]

print MySQLdb.paramstyle returns: format

I found one example like this:

cursor.execute('''Select * from phone where name=%s order by
name''',(name))

But I get this in my Apache error log:
NameError: name 'name' is not defined

Like my last problem I posted, I am sure it is something very simple
that I am missing!!
Fred

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MYSql, CGI web page search code not working

[Kirk McDonald]

Fred wrote:
 No matter what I type in the form text box (or even if I leave it
 blank) I get all the records.

Try this:

#!/usr/local/bin/python
print Content-Type: text/html\n
import MySQLdb
import cgi

db=MySQLdb.connect(host = 'localhost', db = 'phone')
cursor=db.cursor()
cursor.execute(Select * from phone where name=%s order by name, (name,))

result = cursor.fetchall()
for record in result:
 print 'p'
 print record[0]
 print '--'
 print record[1]
 print '--'
 print record[2]
 print '--'
 print record[3]
 print '/p'

(Assuming the name of your text field is name.)
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MYSql, CGI web page search code not working

[Fred]

Yeah, I already tried that (except you have a , after name.

Your code produces the same error:

 NameError: name 'name' is not defined

I know I am close!! Just missing some small thing...

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MYSql, CGI web page search code not working

[Kirk McDonald]

Fred wrote:
 Yeah, I already tried that (except you have a , after name.
 
 Your code produces the same error:
 
  NameError: name 'name' is not defined
 
 I know I am close!! Just missing some small thing...
 

Oh, duh. I forgot something:

#!/usr/local/bin/python
print Content-Type: text/html\n
import MySQLdb
import cgi

form = cgi.FieldStorage()

db=MySQLdb.connect(host = 'localhost', db = 'phone')
cursor=db.cursor()
cursor.execute(Select * from phone where name=%s order by name,
 (form['name'].value,))

result = cursor.fetchall()
for record in result:
 print 'p'
 print record[0]
 print '--'
 print record[1]
 print '--'
 print record[2]
 print '--'
 print record[3]
 print '/p'

The comma is intentional: the MySQLdb wants the argument(s) as a tuple.
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MYSql, CGI web page search code not working

[Fred]

Thanks Kirk! That worked perfect! And makes perfect since now that I
see it...

Now that I have the main pieces working I can start expanding from
here!

Fred

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MYSql, CGI web page search code not working

[Fred]

OK one more... how would I do a LIKE instead of a = in this code?

 cursor.execute(Select * from phone where name=%s order by name,
 (form['name'].value,))

Right off I think:

 cursor.execute(Select * from phone where name like %%s% order by
name,
 (form['name'].value,))

But it blows up...

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MYSql, CGI web page search code not working

[Fred]

Perfect again Kirk! Now I will study all this so I actually understand
what is happening..

Thanks!

Fred

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: MYSql, CGI web page search code not working

[Kirk McDonald]

Dennis Lee Bieber wrote:
 On Sat, 28 Jan 2006 10:14:44 -0800, Kirk McDonald [EMAIL PROTECTED]
 declaimed the following in comp.lang.python:
 
 
 
The comma is intentional: the MySQLdb wants the argument(s) as a tuple.
 
 
   The DB-API wants tuples... But my last perusal of the MySQLdb Python
 code showed that it would work with naked singletons...

Ah! So it does. However, I still pass 'em as a tuple as a matter of 
course, since it's documented that way. *shrug* (Also, it saves that 
many keystrokes if I need to add arguments.)

-Kirk McDonald
-- 
http://mail.python.org/mailman/listinfo/python-list