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

 

[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

 

[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

 

[Kirk McDonald]

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

 

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

 

[Kirk McDonald]

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.

 

[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

 

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

 

[Kirk McDonald]

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

This should work:

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

-Kirk McDonald

 

[Fred]

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

Thanks!

Fred

 

[Dennis Lee Bieber]

cursor.execute("Select * from phone where name like %%s% order by
name",

Because % is a special meaning in the formatting scheme, it acts
like quotes or \ in some ways -- to put one of those into the literal
you need to double it.

Untested: %%%s%%

Ugly, isn't it <G> The first % is a translation flag, the second %
then says "I really do want a % in the output"; the third is another
translation flag, the "s" says put a string from the arguments here, and
the last two are the same behavior as the first two.


HOWEVER -- I suspect this will NOT work for DB-API queries (the
behavior is correct for "Python string formatting"). DB-API queries are
supposed to determine the proper quoting for the argument and add that
to the parameter before substitution. The result you'd get would have

%"something"%

and you need

"%something%"

so you have to add the % to the argument value FIRST:

"%" + argument + "%"

--

 

[Dennis Lee Bieber]

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

 

[Kirk McDonald]

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