python有条件查询mysql_如何在单个sql查询中编写多个条件以获取数据-Python mysql

I am in a dilema that how could i writ such sql queries to make a seach. I have tried and posted it, but it not as expected when user enter data in multiple field of a form and make a search.

The query which i wrote form for a single form field and makes a search and display

#!/usr/bin/python

import cgi

import MySQLdb

class Table():

def __init__(self, host, user, passwd, name):

self.db = MySQLdb.connect(host = host, user = user, passwd = passwd, db = name)

self.cursor = self.db.cursor()

def getdata(self, fname, lname, age, gender):

self.fname = fname

self.lname = lname

self.age = age

self.gender = gender

def mysqlconnect(self):

sql = "select * from PERSON where F_Name = '%s' or L_Name = '%s' or Age = '%s' or Gender = '%s' " %(self.fname, self.lname, self.age, self.gender)

self.cursor.execute(sql)

result = self.cursor.fetchall()

for row in result:

print "
", row[0], row[1], row[2], row[3]

self.cursor.close()

self.db.close()

def main():

print "Content-type: text/html\n"

tableobj = Table("localhost", "root", "root", "Info")

form = cgi.FieldStorage()

f_name = form.getvalue('firstname', '')

l_name = form.getvalue('lastname', '')

age = form.getvalue('age', 0)

gender = form.getvalue('gender', '')

tableobj.getdata(f_name, l_name, age, gender)

tableobj.mysqlconnect()

if __name__ == "__main__":

main()

If suppose user enter data into FirstName field and in the Gender Field

Firstname: Jeremy

Gender: male

then it should display the record.

If supposer user enter data in Age field and Gender field

Age : 25

Gender: Female

It should display result of fewmale whose age is 25.

Likewise all the possible condition

解决方案

Jeremy,

To answer your question, Martijn did a great job of explaining here:

sql = "select * from PERSON where F_Name = %s or L_Name = %s or Age = %s or Gender = %s", > then self.cursor.execute(sql, (self.fname, self.lname, self.age, self.gender))

But, in your case, the best idea would be to use an ORM. This would save you a lot of trouble in the long run!

Writing your own SQL queries is fine, however, you open yourself to several problems. SQL-based attacks, as well as just having a harder time parsing your data, are two.

For Python, there are a few good ones. SQlAlchemy is the most known, but look around and see what you need.