Welcome, guest | Sign In | My Account | Store | Cart

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
 1
 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.

Created by David S on Fri, 29 Jul 2005 (PSF)
Python recipes (4591)
David S's recipes (1)

Required Modules

  • (none specified)

Other Information and Tasks