Python - Generate a multiple row update query

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

Answers


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)

Need Your Help

Labeling points in R plot not Printing if Matrix instead of Data frame

r plot labels

OK... Silly simulated data... Names of subjects by initials, SAT scores, and income later in life in thousands of $'s. The entries are scaled and centered, and look like this:

Issue with passport-twitter strategy

javascript node.js passport.js koa passport-twitter

I have an app I've written in node with Koa. I'm using passport for authentication, passport-twitter for twitter oauth, and koa-passport to make it work with Koa. Up until recently, everything wi...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.