Calling a stored procedure python

Question

I am writing a script to pull info/update a MsSQL server and i can get my one stored procedure call to work but not my second one in the updateDB function. here is my code the script runs fine no error codes

import pyodbc 
import json
import argparse
import cgi, cgitb


#GLOBALS
    BUILDCODE = " "
    deptCODE = 0
    bldgCODE = " "
def getCodes(conn, building, department):
    #just for testing
    departmentt = 'COMPUTER SCIENCE'
    buildingt = 'PEARCE HALL'


    #geting the building code from db
    cursorBuild = conn.cursor()
    cursorBuild.execute("select * from dbo.building where name = '%s'" % buildingt)
    bldgRow = cursorBuild.fetchall() 
    cursorBuild.close()
    bldgCode = bldgRow.pop(0)

    global bldgCODE
    bldgCODE = bldgCode.code


    #getting the dept code
    cursorDept = conn.cursor()
    cursorDept.execute("execute dbo.GetCode_ByDepartment @department = '%s'" % departmentt)
    deptRow = cursorDept.fetchall()
    cursorDept.close()
    deptCode = deptRow.pop(0)

    global deptCODE
    deptCODE = deptCode.code
    print type(deptCODE)
    print deptCODE

    #returning the values
    return (bldgCode, deptCode)

    def updateDB(conn, tag, room, updater):
    #updating the database
    updateCursor = conn.cursor()

    print deptCODE
    print bldgCODE
        #this will pass params just has them hard codded for testing
    conn.execute("exec dbo.UpdateCheck_In @room = '400', @building = 'PE', @department = 307, @global_id = 'bacon', @tag = '120420'")


if __name__ == "__main__":
    #connectin to the db with SQL Authentification
    conn = pyodbc.connect(driver = '{SQL Server}', server = '(local)',
        database = 'Inventory', uid = 'sa', pwd = 'p@$$w0rd123')

#checking to see if you connected to the db or not 
    if (conn == False):
        print "Error, did not connect to the database"
    else:
        #NEEDS THIS cgitb.enable
        cgitb.enable()
        # Create instance of FieldStorage   
        form = cgi.FieldStorage()

        #get the data from the url that called it 
        tag = form.getvalue('tagnum')
        building = form.getvalue('build')
        roomNum = form.getvalue('room')
        department = form.getvalue('dept')
        updater = form.getvalue('update')

        #check to see if item is in the db 
        itemIsThere = conn.cursor()
        itemIsThere.execute("select * from dbo.check_in where tag = '120420';")
        print itemIsThere
        itemIsThere.close()
        #if the item in in the inventory 
        if (itemIsThere != None):
                #getting the codes
            getCodes(conn, building, department)
            #calling the update function
            updateDB(conn, tag, roomNum, updater)
        else :
                pass

    conn.close()

If you print the results bldgRow and deptRow what do you get? — Matt Alcock
– Matt Alcock, Commented Feb 23, 2012 at 20:53
i get 'PE' for bldgCODE and '307' for deptCODE, which is what im supposed to get — user1229126
– user1229126, Commented Feb 23, 2012 at 21:16
So the values are just not updated as expected after executing the update procedure? — Bryan
– Bryan, Commented Feb 24, 2012 at 21:12

Bryan · Accepted Answer · 2013-01-31 20:33:53Z

12

Commit your changes after the update procedure is run:

...
#this will pass params just has them hard coded for testing
conn.execute("exec dbo.UpdateCheck_In @room = '400', @building = 'PE', @department = 307, @global_id = 'bacon', @tag = '120420'")
conn.commit()

In pyodbc.connect() autocommit is disabled by default

edited Jan 31, 2013 at 20:33

answered Feb 24, 2012 at 21:22

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ceds Over a year ago

You don't know how long it took me to find out that you need to do a .commit()

user1229126 · Accepted Answer · 2012-02-28 18:44:29Z

2

found the answer everybody, it was because I specified the legacy driver instead of the newer one for MsSQL 2008 heres the code

    conn = pyodbc.connect(driver = '{SQL Server Native Client 10.0}', server = '(local)',            database = 'Inventory', uid = 'sa', pwd = 'p@$$w0rd123',autocommit=True)

answered Feb 28, 2012 at 18:44

user1229126

1471 gold badge1 silver badge6 bronze badges

1 Comment

Manfre Over a year ago

You also added "autocommit=True", which is the more likely fix, as mentioned by @beargle.

Collectives™ on Stack Overflow

Calling a stored procedure python

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related