Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 38 lines (32 sloc) 1.312 kb
8be53ab @tzuryby binding user defined functions to an sqlite database within the webpy da...
tzuryby authored
1 ---
2 layout: default
3 title: Integrating SQLite UDF (user-defined-functions) with webpy database layer
4 ---
5
6 # Integrating SQLite UDF (user-defined-functions) with webpy database layer
7
8 A user asked at the mailing list and I thought of putting it here for future use and reference.
9
10 You can add python functions to sqlite and have them called within your queries.
11
12 Example:
13 <pre>
14 >>> import sqlite3 as db
87f7272 misprint
ivand authored
15 >>> conn = db.connect(":memory:")
8be53ab @tzuryby binding user defined functions to an sqlite database within the webpy da...
tzuryby authored
16 >>> conn.create_function("sign", 1, lambda val: val and (val > 0 and 1 or -1))
17 >>> cur = conn.cursor()
18 >>> cur.execute("select 1, -1")
19 &lt;sqlite3.Cursor object at 0xb759f2c0&gt;
20 >>> print cur.fetchall()
21 [(1, -1)]
22 >>> cur.execute("select sign(1), sign(-1), sign(0), sign(-99), sign(99)")
23 &lt;sqlite3.Cursor object at 0xb759f2c0&gt;
24 >>> print cur.fetchall()
25 [(1, -1, 0, -1, 1)]
26 >>> conn.close()</pre>
27
28 In webpy, you can get a reference to the connection object via the cursor
29 i.e. db._db_cursor().connection
30
31 Example:
32 <pre>
33 >>> import web
34 >>> db = web.database(dbn="sqlite", db=":memory:")
35 >>> db._db_cursor().connection.create_function("sign", 1, lambda val: val and (val > 0 and 1 or -1))
36 >>> print db.query("select sign(1), sign(-1), sign(0), sign(-99), sign(99)").list()
37 [&lt;Storage {'sign(1)': 1, 'sign(-1)': -1, 'sign(99)': 1, 'sign(-99)': -1, 'sign(0)': 0}&gt;]
38 </pre>
Something went wrong with that request. Please try again.