I am new in python and pysqlite. Right now, I am reallly trying to figure it out the way that I can make python function read the table in sqlite database and calculate data using SQL language.
For example:
I do have database name "CART.sqlite" with table name "oritab"
now I do write the function in python as follow (real code is indented)
************************************************** **********************
-
from pysqlite2 import dbapi2 as sqlite
-
conn = sqlite.connect('C:\CART.sqlite')
-
ex = cur.execute
-
-
def deviance(set):
-
SQL_AvgYj = "SELECT AVG(DurationTotal) As Results FROM set"
-
SQL_N = "SELECT COUNT(DISTINCT P_CODE) AS N FROM set"
-
SQL_Nmin = "SELECT MIN(P_CODE) AS Nmin FROM set"
-
SQL_Nmax = "SELECT MAX(P_CODE) AS Nmax FROM set"
-
AvgYj = ex(SQL_AvgYj).fetchall()
-
N = ex(SQL_N).fetchall()
-
Nmin = ex(SQL_Nmin).fetchall()
-
Nmax = ex(SQL_Nmax).fetchall()
-
count = Nmin
-
S = 0
-
while count <= Nmax:
-
actcount = 1
-
while actcount <= 30:
-
Yij = ex("SELECT SUM(DurationTotal) FROM set WHERE P_CODE=count AND ActCode=actcount")
-
S += (Yij-AvgYj)**2
-
actcount += 1
-
count += 1
-
return S
************************************************** ***************************
then I call function as follow
result = deviance(oritab)
************************************************** **************************
Seem like it wont work this way. python is not recognize the table name at all.
I really confuse how can I dump the table from sqlite into python function.
could any one can help me or guide me into the right direction pls
