[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