Member Avatar for jchang

I built a function as following

def IsExists(fxfcustid):
     
     #global noduplicates
     
     db = db_connect("triumph","freight","vcpdb")
     cursor = db2.cursor()
     
     #*************************************************************************
     #***   Query to get entries made by the person acessing the system     *** 
     #*************************************************************************
               
     sql = '''select count(*) 
              from triumph.fxf_salescontest_entry
              where fxf_acct_nbr        = %(fxfcustid)s)
                 and (delete_flag is null or
                      delete_flag not in ('Y','y')) ''' % vars()
     try:
        cursor.execute( sql )
        noduplicates = cursor2.fetchall();
     except:
        noduplicates = [('None')]
     
     cursor.close()
     db.close()
     return

### later on, it was called by followiging code:
isExists(fxfcustid)
       if noduplicates != 0:
           print mainscreen(empno,'This customer has been entered. Request failed',0,'')         
      else:
        cursor.execute(insertSql % vars())

But it doesn't work. what's wrong with this code? is noduplicates number or string? any help is highly appreciated.

Thanks,

Jackie

Edit: code tags added by vegaseat

  • 2 Contributors
  • 1 Reply
  • 203 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by xav.vijay

Dani AI

Generated

Several independent bugs are present; fixing them will make the check reliable.

As noted, Python is case-sensitive — a function named IsExists will not be found if called as isExists. Beyond that, there are three definite runtime problems: db2 and cursor2 are used but never defined (typos), the function never returns a value yet the caller expects noduplicates, and the SQL text contains an extra ) after the placeholder. Also, fetchall() returns a list of tuples (e.g. [(3,)]), so comparing that result to 0 is incorrect. Formatting SQL with Python % into a string is brittle and opens the door to quoting/SQL-injection problems; pass parameters to execute() instead. The try/except that sets noduplicates = [('None')] hides real errors and also produces the wrong type.

Concrete fixes to apply:

  • Make the function name and the call match exactly (or rename consistently).
  • Return a simple value (an integer count or a boolean) instead of relying on a global.
  • Fix the db2/cursor2 typos so cursor = db.cursor() and cursor.fetchall() refer to the same object.
  • Remove the stray ) from the SQL and use parameterized execution (driver paramstyle may be %s or %(name)s).
  • Use cursor.fetchone()[0] to get the count, then check > 0.
  • Close cursor/connection in finally or use context managers; don’t swallow exceptions silently.

Example pattern (adjust placeholder style to the DB driver used):

def customer_exists(customer_id):
    conn = db_connect("triumph","freight","vcpdb")
    try:
        cur = conn.cursor()
        sql = ("SELECT COUNT(*) FROM triumph.fxf_salescontest_entry "
               "WHERE fxf_acct_nbr = %s AND (delete_flag IS NULL OR UPPER(delete_flag) <> 'Y')")
        cur.execute(sql, (customer_id,))
        return cur.fetchone()[0] > 0
    finally:
        cur.close()
        conn.close()

This returns a boolean, avoids globals, and fixes the scoping/typing issues that prevent the original code from working.

Member Avatar for xav.vijay

Well jchang, I am not exacly sure where the problem is ,but can u check on the following syntax in your code... Hope it helps

In the main code where u call this function

isExists(fxfcustid) is actually supposed to be "IsExists(fxfcustid)"
functions are case sensitive...But if you are not facing a problem here, maybe the next one could be bothering...

I have never used three ' ' ' ( and I have no idea how it works)
In your code

sql = '''select count(*)
from triumph.fxf_salescontest_entry
where fxf_acct_nbr = %(fxfcustid)s)
and (delete_flag is null or
delete_flag not in ('Y','y')) ''' % vars()


Here 'fxcustid' seems to be caught within the string, I mean it has become a part of the string... why dont u try

sql = "select count(*) from triumph.fxf_salescontest_entry where fxf_acct_nbr = %(" + fxfcustid + ")s) and (delete_flag is null or delete_flag not in ('Y','y'))" % vars()

I have very little knowledge in SQL Queries, I dont know whether the above syntax is right, hope it helps you in some way...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.