SAS中文论坛

标题: HOW TO DEFINE SQLITE FUNCTIONS WITHIN PYTHON [打印本页]

作者: shiyiming 时间: 2010-10-22 13:24
标题: HOW TO DEFINE SQLITE FUNCTIONS WITHIN PYTHON
From Wensui Liu's blog

################################################### # HOW TO DEFINE SQLITE FUNCTIONS WITHIN PYTHON # # ----------------------------------------------- # # REFERENCE: # # Chris Newman, SQLite, Sams Publishing # # ----------------------------------------------- # # WISH LIST: # # LOOKING FORWARD TO SUCH NEAT THING IN R # ################################################### import sqlite3 import math # USE IRIS DATA AS AN EXAMPLE con = sqlite3.connect("d:\mydb") cur = con.cursor() cur.execute("select * from mytab limit 3") for row in cur: print row # OUTPUT: # (5.0999999999999996, 3.5, 1.3999999999999999, 0.20000000000000001, u'setosa') # (4.9000000000000004, 3.0, 1.3999999999999999, 0.20000000000000001, u'setosa') # (4.7000000000000002, 3.2000000000000002, 1.3, 0.20000000000000001, u'setosa') # DEFINE A MOD() FUNCTION def mod(x, y): return math.fmod(x, y) # REGISTER THE FUNCTION con.create_function("mod", 2, mod) cur.execute("select sepal_length, mod(sepal_length, 2) from mytab limit 3") for row in cur: print row # OUTPUT: # (5.0999999999999996, 1.0999999999999996) # (4.9000000000000004, 0.90000000000000036) # (4.7000000000000002, 0.70000000000000018) # DEFINE A MEDIAN() AGGREGATE FUNCTION class median: def __init__(self): self.reset() def reset(self): self.values = [] def step(self, val): self.values.append(float(val)) def finalize(self): self.values.sort() n = len(self.values) if mod(n, 2) == 1: return self.values[n // 2] else: return (self.values[n // 2 - 1] + self.values[n // 2]) / 2 # REGISTER THE FUNCTION con.create_aggregate("median", 1, median) cur.execute("select species, median(sepal_length) from mytab group by species") for row in cur: print row # OUTPUT: # (u'setosa', 5.0) # (u'versicolor', 5.9000000000000004) # (u'virginica', 6.5)

欢迎光临 SAS中文论坛 (https://mysas.net/forum/)