By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,189 Members | 2,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,189 IT Pros & Developers. It's quick & easy.

MYSql, CGI web page search code not working

P: n/a
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>'

Jan 28 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
>
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
Jan 28 '06 #2

P: n/a
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

Jan 28 '06 #3

P: n/a
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".)
Jan 28 '06 #4

P: n/a
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...

Jan 28 '06 #5

P: n/a
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.
Jan 28 '06 #6

P: n/a
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

Jan 28 '06 #7

P: n/a
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...

Jan 28 '06 #8

P: n/a
Fred wrote:
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
Jan 28 '06 #9

P: n/a
Perfect again Kirk! Now I will study all this so I actually understand
what is happening..

Thanks!

Fred

Jan 28 '06 #10

P: n/a
Dennis Lee Bieber wrote:
On Sat, 28 Jan 2006 10:14:44 -0800, Kirk McDonald <mo******@suad.org>
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
Jan 28 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.