Python SQL SELECT using “name LIKE %%s%”
mitch @ 2:26 pm on April 25, 2007
I know I haven’t written about much of the code I’ve written over the last couple years or so… as I don’t consider myself a “programmer”. I’m a Systems Administrator by trade but with a flare for scripting where I will write scripts to make my job easier (even so I no longer have to do my “job”). I’ve been learning python over the last year or so, and I think I’m really finally starting to get it and really be able to apply what I’ve learned to new scripts. Anyway, as a means to help me remember this and that maybe it could be helpful to someone else down the road. I’m not sure if there is a better way to do this or if this is the proper way. But what I was looking for was a way to be able to pass a generic name and do a LIKE sql search for it. My example is actually coming from using bacula at work, and I’m trying to get some better reporting and store some statistics(I will eventually post what I come up with…) Anyway, heres a bit of sample code to illustrate what I came up with(I run a postgresql database, but I don’t believe it would be any different for mysql other than the original module).
#!/usr/bin/env python
import sys, psycopg
DSN = "dbname='bacula' user='bacula'"
class pgDB:
def db_connect(self, DSN):
try:
conn = psycopg.connect(DSN)
except StandardError, err:
print "Error: Unable to connec tot the database, exiting...", err
return Exception
else:
return conn
def do_query(self, conn, query, args=[]):
cur = conn.cursor()
try:
cur.execute(query, args)
result = cur.fetchall()
return result
except StandardError, err:
print "Error Executing Query: ", error
def main(clientName):
DB = pgDB()
try:
myDBconn = DB.db_connect(DSN)
except:
sys.exit()
clientName = "%" + clientName + "%"
query = "SELECT * FROM client WHERE name LIKE %s"
result = DB.do_query(myDBconn, query, [clientName])
print result
if __name__ == "__main__":
main("hostname")
Assuming the spacing is all correct, that should work. The key being having to append the %’s to the word we are searching for before it is injected into the predefined query… kinda weird how it does that as i was trying to have the %’s in the query before the clientname was injected having the query look like “LIKE %%s%” which was causing problems… and I suppose I can understand why…
Anyway, as I said, I’m not a programmer by any means, this is just some notes for myself. Maybe they will help someone else too…
