Python Forum
Thread Rating:
  • 4 Vote(s) - 2.5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Not Working
#1
I'm a New Be and need help. I have a routine and it exasperates me....

I've executed a connection in a previous def and passed it . Why does this not work in the program?
Yet if I copy the the "print (SQL)" verbatim and use it in SQLite Editor in the "Execute Tab" it works.

computeNetPay(conn, ppeDate, intEmpID, intCmpID)
ppeDate = datetime(2019,2,23,0,0,0)
intEmpID = 1
intCmpID = 2

SQL = "SELECT * FROM Programs WHERE PPE_Date = '%s' AND EmployeeID = '%s' AND CompanyID = '%s'" % (ppeDate, intEmpID, intCmpID)
print (SQL)

NOTE: The print of the print (SQL) returns
"SELECT * FROM Programs WHERE PPE_Date = '2019-02-23 00:00:00' AND EmployeeID = '1' AND CompanyID = '2'"

cur = conn.cursor
cur.execute(SQL)
Reply
#2
Please use Python tags when posting code. It makes the post more readable.

Once executed, the data is stored in the cursor object. To fetch it, you need to call either the fetchone() method or the fetchall() method:

computeNetPay(conn, ppeDate, intEmpID, intCmpID)
ppeDate = datetime(2019,2,23,0,0,0)
intEmpID = 1
intCmpID = 2

SQL = "SELECT * FROM Programs WHERE PPE_Date = '%s' AND EmployeeID = '%s' AND CompanyID = '%s'" % (ppeDate, intEmpID, intCmpID)
print (SQL)

cur = conn.cursor
cur.execute(SQL)
print(cur.fetchall())
Reply
#3
it seems to fail on the "cur.execute" statements or the "cur,fetchall". I added a print statement after the cur.execute or the cur.fetchall() and they never execute.
Reply
#4
Are you receiving an error? Would you please post your whole code so we can evaluate for issues?
Reply
#5
Here;s the whole shabang............

def computeNetPay(conn, ppeDate, intEmpID, intCmpID):
"""
Insert Into Programs
:param conn: the Connection object
:param dtePpeDate:
:param intEmpID:
:param intCmpID:
:return:
"""

try:
sql = "SELECT * FROM Programs WHERE PPE_Date = '%s' AND EmployeeID = '%s' AND CompanyID = '%s'" % (ppeDate, intEmpID, intCmpID)
cur = conn.cursor
cur.fetchall(sql)
print (sql)

rows = cur.fetchall()
print (rows)


## netPay = netPay + row[9]
except:
traceback.format_exc()
finally:
return netPay



def create_connection():
""" create a database connection to the SQLite database
specified by the db_file
:param db_file: database file
:return: Connection object or None
"""
try:
filename = filedialog.askopenfilename(initialdir='\\\\MYBOOKLIVE\\Public',
title="Select file",filetypes=[("Database Files", "*.db")])
conn = sqlite3.connect(filename)
return conn
except Error as e:
print(e)

return None



def main():

# create a database connection
conn = create_connection()

ppeDate = datetime(2019,2,23,0,0,0)
intEmpID = 1
intCmpID = 2

with conn:
computeNetPay(conn, ppeDate, intEmpID, intCmpID)



if __name__ == '__main__':
main()
Reply
#6
Parentheses are missing from the conn.cursor() call. Try this:

def computeNetPay(conn, ppeDate, intEmpID, intCmpID):
    """
    Insert Into Programs
    :param conn: the Connection object
    :param dtePpeDate:
    :param intEmpID:
    :param intCmpID:
    :return:
    """

    try:
        sql = "SELECT * FROM Programs WHERE PPE_Date = '%s' AND EmployeeID = '%s' AND CompanyID = '%s'" % (ppeDate, intEmpID, intCmpID)
        cur = conn.cursor()
        cur.fetchall(sql)
        print (sql)

        rows = cur.fetchall()
        print (rows)
        ## netPay = netPay + row[9]
    except:
        traceback.format_exc()
    finally: return netPay

def create_connection():
    """ create a database connection to the SQLite database
    specified by the db_file
    :param db_file: database file
    :return: Connection object or None
    """
    try:
        filename = filedialog.askopenfilename(initialdir='\\\\MYBOOKLIVE\\Public', 
        title="Select file",filetypes=[("Database Files", "*.db")])
        conn = sqlite3.connect(filename)
        return conn
    except Error as e:
        print(e)
        return None

def main():
    # create a database connection
    conn = create_connection()

    ppeDate = datetime(2019,2,23,0,0,0)
    intEmpID = 1
    intCmpID = 2

    with conn:
        computeNetPay(conn, ppeDate, intEmpID, intCmpID)

if __name__ == '__main__':
    main()
Reply
#7
Thanks a lot. I knew I missed something simple.
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020