Design pattern for generating SQL string

Question

I have the following method in a python module which takes all default arguments, and generates a SQL query from those arguments in the following way:

Wondering, if there is a better (and elegant and pythonic) way to generate the above SQL query

def myquery(arg1=None, arg2=None, arg3=None):
    query = "SELECT column FROM table WHERE"
    params = []
    if arg1 is not None:
        query = query + " arg1 = " + arg1
        params.append(arg1)

    if arg2 is not None:
        if params is not None:
            query = query + "AND arg2 = " + arg2
        else:
            query = query + " arg 2 = " + arg2
        params.append(arg2)

    if arg3 is not None:
        if params is not None:
            query = query + "AND arg3 = " + arg3
        else:
            query = query + " arg 3 = " + arg3
        params.append(arg3)
    return query

+1 for Matti's comment. If you're writing SQL by hand like this there's an excellent chance you should be looking at higher-level solutions. — larsks
– larsks, Commented Dec 22, 2012 at 1:05
No, I dont use orm. But I was looking to replace that huge if block with something more elegant. Also, SQL was just an example. There are other parts of the code which use similar if else.. block to generate stats tracking and logging calls. Wondering if that if block can be replaced with something else — user462455
– user462455, Commented Dec 22, 2012 at 1:10

Joran Beasley · Accepted Answer · 2012-12-22 01:41:04Z

3

If i was gonna do this without an ORM I would do something like

def QueryWhere(table,where={}):
    params = ["%s=?"%key for key in where.keys()] #take advantage of builtin query placeholders (?)
    c = db.cursor()
    c.execute("SELECT * FROM %s WHERE %s"%(table," AND ".join(params)),where.values())
    return c.fetchall()

print QueryWhere("users",{"name":"johndoe","pass":"1c4$g"})

something like that anyway ... I would also recommend looking into using an ORM

answered Dec 22, 2012 at 1:41

Joran Beasley

114k13 gold badges167 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jdi Over a year ago

+1 because I think if you are constructing SQL strings, you are still supposed to allow the sql module to parameterize the values instead of formatting them into the string

user462455 Over a year ago

Thanks. I don't use ORM because most of application logic is distributed into modules and not into traditional classes/objects

loopbackbee · Accepted Answer · 2012-12-22 01:42:58Z

There's (what I believe to be) some minor problems with your code (spacing before the AND, wrong results if the function is called with a single arg2/arg3 argument, etc). Therefore, I'm answering this as I believe the function should behave - feel free to correct me.

I think the most pythonic way to do this would be to use a dictionary instead of function arguments - if you want to, you may use an argument dictionary to expose the same function behaviour as you have now. Further, there's a lot to be gained by using iteration, list comprehensions, and string formatting:

def myquery2(**args):
    query = "SELECT column FROM table WHERE"
    options= [" {0} = {1}".format(arg, value) for arg,value in args.iteritems()]
    return query + " AND".join( options )

Collectives™ on Stack Overflow

Design pattern for generating SQL string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related