Add a user defined Sign function to SQLite using PySQLite (http://initd.org/projects/pysqlite). The utility of this is explained in this very nice tutorial: http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
Python, 20 lines
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | def _sign(val):
if val:
if val > 0: return 1
else: return -1
else:
return val
#get your db connection, conn
conn.create_function("sign", 1, _sign)
...
>>cur = c.conn.cursor()
>>cur.execute("select test, val from test")
>>cur.fetchall()
[(u'a', None)]
>>cur.execute("select sign(test), sign(val), sign(0), sign(-99), sign(99) from test")
>>cur.fetchall()
[(1, None, 0, -1, 1)]
|
This should return 1 when val is positive, -1 when negative, 0 when 0, and None when NULL.
My first cut was wrong: def _sign(val): ..if val: ....if val > 0: return 1 ....elif val < 0: return -1 ....else: return 0 ..else: ....return None
This incorrectly returned None for 0 because val was false for NULLs and 0, so now it just returns val when it evaluates to false.
Also, notice that you need to add this function to each connection you create---that is each time you connect(). The funcion is not persisted in any way.
Tags: database
