[DB-SIG] retrieve data from mysql

[Andy Todd]

ronald lam wrote:
> I'm a newbie, I want to retrieve data from mysql server, all are normal 
> but non of datas could retrieve from database, it is because wrong 
> select syntags as below:
> 
> cursor.execute("SELECT descrip, pri, unit FROM code where descrip like 
> '%$siz%' limit 20")
> 
> Will any Masters help me fixing the sentence '%????%'
> 
> Rgds
> 
> -------------------------------------
> 
> #!/usr/local/bin/python
> 
> # import MySQL module
> import MySQLdb
> 
> # get user input
> siz = raw_input("Please enter size: ")
> #des = raw_input("Please enter description: ")
> 
> # connect
> db = MySQLdb.connect(host="fa", user="ron", passwd="xxxxx",
> db="hon")
> 
> # create a cursor
> cursor = db.cursor()
> 
> # execute SQL statement
> #cursor.execute("INSERT INTO animals (name, species) VALUES (%s, %s)",
> #(name, species))
> 
> cursor.execute("SELECT descrip, pri, unit FROM code where descrip like 
> '%$siz%' limit 20")
> result = cursor.fetchall()
> for record in result:
>     print record[0],'--->', record[1], record[2]
> 
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig

Ronald,

Change your cursor.execute line to

cursor.execute("SELECT descrip, pri, unit FROM code WHERE descrip LIKE 
%s LIMIT 20", ('%'+siz+'%',))

Then read the section of the DB-API on paramstyle 
(http://www.python.org/peps/pep-0249.html). MySQLdb uses the 'format' 
style, where you place a '%s' in your statement wherever you wish to 
substitute another value at run time. Its quite a powerful mechanism.

I'd also question whether you need to use a like for a column which 
looks like it is numeric. But that's just me.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/