Python: automated change in variable contents

Question

Hi,

I have a Python function which receives numerous variables, and builds an SQL query out of them:

def myfunc(name=None, abbr=None, grade=None, ...)

These values should build an SQL query. For that purpose, Those who equal None should be changed to NULL, and those who store useful values should be embraced with 's:

name="'"+name+"\'" if name else 'NULL'
abbr="'"+abbr+"\'" if abbr else 'NULL'
...
Lots of lines here - that's my problem!
...

And than,

query="""INSERT INTO table(name, abbr, ...)
         VALUES (%(name)s, %(abbr)s, ...) """ locals()
cur.execute(query)

Is there a nicer, more Pythonic way to change the variable contents according to this rule?

Adam

Answer 1

The best way to form a SQL query is not by string-formatting -- the execute method of a cursor object takes a query string with placeholders and a sequence (or dict, depending on the exact implementation you have of the DB API) with the values to substitute there; it will then perform the None-to-Null and string-quoting that you require.

I strongly recommend you look into that possibility. If you need string processing for some other purpose, however, you could do something like:

processed = dict((n, "'%s'" % v if v is not None else 'NULL')
                 for n, v in locals().iteritems())

and then use dictionary processed instead of locals() for further string-formatting.

Answer 2

You could define myfunc as follows:

def myfunc(*args, **kwargs)

Where kwargs is a dictionary holding all named parameters passed to the function.

To get the value of a query parameter, you would use kwargs.get(name_of_parameter, 'NULL'). To build the query, you would just iterate over all dictionary items. Note however, that any parameter passed as a named parameter to the function will end up in the query if you do it this way.

Answer 3

The correct way to pass arguments to psycopg2 is to use placeholders and let the driver handle the values. None are converted to NULL automatically and the correct string escaping is performed.

Concatenating string is a bad idea.