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">
<p>Enter the name to find:
<p><input 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.conn ect(host = 'localhost', db = 'phone')
cursor=db.curso r()
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>'

[Diez B. Roggisch]

Re: MYSql, CGI web page search code not working

>[color=blue]
> db=MySQLdb.conn ect(host = 'localhost', db = 'phone')
> cursor=db.curso r()
> cursor.execute( "Select * from phone where name = name order by name")[/color]

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.paramst yle



Diez

[Fred]

Re: MYSql, CGI web page search code not working

print MySQLdb.paramst yle 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

[Kirk McDonald]

Re: MYSql, CGI web page search code not working

Fred wrote:[color=blue]
> No matter what I type in the form text box (or even if I leave it
> blank) I get all the records.[/color]

Try this:

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

db=MySQLdb.conn ect(host = 'localhost', db = 'phone')
cursor=db.curso r()
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".)

[Fred]

Re: MYSql, CGI web page search code not working

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

[Kirk McDonald]

Re: MYSql, CGI web page search code not working

Fred wrote:[color=blue]
> 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...
>[/color]

Oh, duh. I forgot something:

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

form = cgi.FieldStorag e()

db=MySQLdb.conn ect(host = 'localhost', db = 'phone')
cursor=db.curso r()
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.

[Fred]

Re: MYSql, CGI web page search code not working

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

[Fred]

Re: MYSql, CGI web page search code not working

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

[Kirk McDonald]

Re: MYSql, CGI web page search code not working

Fred wrote:[color=blue]
> 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...
>[/color]

This should work:

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

-Kirk McDonald

[Fred]

Re: MYSql, CGI web page search code not working

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

Thanks!

Fred

[Kirk McDonald]

Re: MYSql, CGI web page search code not working

Dennis Lee Bieber wrote:[color=blue]
> On Sat, 28 Jan 2006 10:14:44 -0800, Kirk McDonald <mooquack@suad. org>
> declaimed the following in comp.lang.pytho n:
>
>
>[color=green]
>>The comma is intentional: the MySQLdb wants the argument(s) as a tuple.[/color]
>
>
> The DB-API wants tuples... But my last perusal of the MySQLdb Python
> code showed that it would work with naked singletons...[/color]

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