调用存储过程python [英] Calling a stored procedure python

问题描述

我正在编写一个脚本来提取信息/更新 MsSQL 服务器，我可以让我的一个存储过程调用工作，但不能在 updateDB 函数中调用第二个.这是我的代码脚本运行良好没有错误代码

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()

推荐答案

大家都找到了答案，那是因为我为 MsSQL 2008 指定了旧驱动程序而不是较新的驱动程序，这里是代码

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)  

这篇关于调用存储过程python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！