Python function for reading and manipulate sqlite table

[natachai]

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.sqlit e" 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

[bvdet]

Please use code tags when posting code. See our posting guidelines.

An initial observation - table name "oritab" is passed to function deviance() and assigned to identifier "set". You can use string formatting to pass the string "oritab" to pysqlite. Example:

SQL_AvgYj = "SELECT AVG(DurationTotal) As Results FROM %s" % (set)

BV - Moderator

[natachai]

Thank you for your help BV