Python - Generate a multiple row update query

Question

My goal is dynamically generate in Python a SQL query similar to,

UPDATE SURV_SCF_ID_STATUS
    SET AGE_STATUS = CASE NAME
        WHEN 'entityXp1' THEN '1'
        WHEN 'entityXp3' THEN '0'
        WHEN 'entityXpto1' THEN '1'
        WHEN 'entityXpto3' THEN '1'
    END
WHERE NAME IN ('entityXp1', 'entityXp3', 'entityXpto1', 'entityXpto3')

This is what I have so far, but still feels like an ugly hack,

logs_age_list = [['entityXp1', '1'], ['entityXp3', '0'], ['entityXp1', '1'], ['entityXpto3', '1']]
conditions_list = []
where_list = []
for entity, status in logs_age_list:
conditions_list.append("\t\tWHEN %(entity)s THEN %(value)s" % locals() )
where_list.append(entity)

conditions_string = '\n'.join(conditions_list)
where_string = ', '.join(where_list)

sql = '''
UPDATE SURV_SCF_ID_STATUS
     SET AGE_STATUS = CASE NAME
%(conditions_string)s
END
WHERE NAME IN (%(where_string)s)
''' % locals()

print sql

Is there some more obvious way to do it? I didn't find any Python module which allow this.

Thanks

Second line of the Zen of Python says "Explicit is better than implicit". I would avoid '' % locals() at any cost. — atomocopter
– atomocopter, Commented May 17, 2011 at 11:43
Doesn't it also says, "Beautiful is better than ugly / Simple is better than complex"? I started using it for readability purposes after reading wiki.python.org/moin/PythonSpeed/PerformanceTips and as the Zen states, "Readability counts" — Joao Figueiredo
– Joao Figueiredo, Commented May 17, 2011 at 15:31

atomocopter · Accepted Answer · 2011-05-17 12:07:17Z

You can build the condition_string via

conditions_string = ' '.join(["\t\tWHEN %s THEN %s\n" % (x, y) for x, y in logs_age_list])

and the where_string as

where_string = ', '.join([duo[0] for duo inlogs_age_list])

then do something like

sql = '''
UPDATE SURV_SCF_ID_STATUS
SET AGE_STATUS = CASE NAME
%s
END
WHERE NAME IN (%s)
''' % (conditions_string, where_string)

But in generall you should try to use the execute methods of the DB modules to avoid SQL injection.

Here is an example taken from http://docs.python.org/library/sqlite3.html

# Never do this -- insecure!
symbol = 'IBM'
c.execute("... where symbol = '%s'" % symbol)

# Do this instead
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

My module generates the data so it shoud be sanitized but how could I have a multiple update with those SLQ injection precautions? Thanks for the optimizations.

Collectives™ on Stack Overflow

Python - Generate a multiple row update query

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related