[DB-SIG] MS SQL Server and odbc getting 'None'

Vernon Cole wnvcole at peppermillcas.com
Fri Aug 13 18:01:52 CEST 2004


Dave:
  The problem you may be having is that you must "commit" the transaction(s)
using the connection object after you perform them using the cursor object.
Look just above the "except ValueError" in the enclosed script. 

I was looking for a snippet to send you, but this program is not much longer
and is in production at my site, so I know it is complete and works.  The
following script is stored on a public share on the SQL server. It allows a
user to interactively select a row from a table, check its value, and
optionally modify a field.
vvvvvvvvvvvvvvvvvv cut here vvvvvvvvvvvvvvvvvvvvvvvvvv
""" pbOK800.py -- program to set/clear "OK800" flags in the Casino phonebook
"""
import sys
import adodbapi             #use the ADO data access toolkit

_OK800 = False
_c = None

def get_ext(e):
    global _OK800
    # ##########################
    # SQL command
    SQLcmd = """
    select extension, Casino, Department, PositionLine, OK800
     from tbldepartment
    where extension = '%s'
    """
    _c.execute(SQLcmd % e)
    rec = _c.fetchone()
    if not rec:
        print 'Extension',e, 'not found in PhoneBook'
        return None
    else:
        ext = rec[0]
        if rec[4].upper() == 'Y':
            _OK800 = True
        else:
            _OK800 = False
        print 'Extension=', ext, rec[1], rec[2], rec[3]
        print '    OK800=', _OK800
        return ext
    
def bool_input(prompt):
    while True:
        s = raw_input(prompt)
        if len(s) > 0:
            c = str()
            c = s[0].upper()
            
            if c == 'Y' \
            or c == '1' \
            or c == 'T' \
            or c == 'S':
                return True
            elif c=='N' \
            or c == '0' \
            or c == 'F':
                return False
            else:
                print 'Incorrect input. Try again...'

def OK():
    global _OK800
    global _c
    _computername="FRANKLIN" #name of computer with Data Warehouse SQL
Server
    _databasename="PhoneBook" #name of database
    connStrSQLServer = r"Initial Catalog=%s; Data Source=%s;
Provider=SQLOLEDB.1; Integrated Security=SSPI" %(_databasename,
_computername)
    # print connStrSQLServer
    print 'Connecting to:',_computername,_databasename

    s = adodbapi.connect(connStrSQLServer) #connect to datamine server "s"
    _c = s.cursor()   # create a cursor to send commands to "s"

    print """
    You may enter 1, Y, T or S for YES,
    enter 0, N or F for NO.

    """

    keep_trying = True
    while keep_trying:    
        try:
            ex = raw_input('Examine which Extension number? :')
            ext = get_ext(ex)
            if (ext):
                    go = bool_input('Change OK800 for this extension?
[Y/N]:')
                    if go:
                        if _OK800:
                            NewValue = 'N'
                        else:
                            NewValue = 'Y'
                            
                        u = "Update tblDepartment "\
                            "set OK800 = '%s' where extension = '%s'" % \
                            (NewValue, ext)
                        _c.execute(u)
                        s.commit()
                        check = get_ext(ext)
        except ValueError:
            print 'Incorrect input. Try again.'

        keep_trying = bool_input('Continue? [Y/N]:')

    _c.close()
    s.close()

if __name__ == '__main__':
    OK()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ end of clip ^^^^^^^^^^^^^^^^^^^^^^^^
This is called by executing the following which is actually stored as an
icon on the users desktop.
vvvvvvvvvvvvvvvvvvvvvvvvvvvv begin call script vvvvvvvvvvvvvvvvvvvvvvvv
p = "//franklin/PhoneBook/Utilities"
import sys
if p not in sys.path:
    sys.path.append(p)
from PbOK800 import OK
OK()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^ end call script ^^^^^^^^^^^^^^^^^^^^^^^^^
Good luck,
----
Vernon

-----Original Message-----
From: Carey David SSgt 109CF/SCBJ [mailto:David.Carey at nyscot.ang.af.mil]
Sent: Friday, August 13, 2004 8:35 AM
To: 'Vernon Cole'
Cc: 'db-sig at python.org'
Subject: RE: [DB-SIG] MS SQL Server and odbc getting 'None'


Thanks for the suggestion! I will take a look at it. mxODBC worked when
pulling the information, but when I tried to update or insert records, it
would act like it worked. Then when I went to the database nothing changed.

So I'm using odbc to push information and mxODBC to pull it with 2 different
connection strings. 

Maybe this will help me roll it in one.

Thanks for the info!
Dave 



More information about the DB-SIG mailing list