Python到SQL Server存储过程

Question

我试图从Python代码调用sqlserver存储过程，使用sqlalchemy。我发现python代码不会引发错误，存储过程也不会执行。在

样本代码：

def SaveData(self, aScrapeResult):
    sql = "EXECUTE mc.SaveFundamentalDataCSV @pSource='%s',@pCountry='%s',@pOperator='%s',@pFromCountry='%s',@pFromOperator='%s',@pToCountry='%s',@pToOperator='%s',@pSiteName='%s',@pFactor='%s',@pGranularity='%s',@pDescription='%s',@pDataType='%s',@pTechnology = '%s',@pcsvData='%s'"

    #   Need to convert the data into CSV
    util = ListToCsvUtil()
    csvValues = util.ListToCsv(aScrapeResult.DataPoints)

    formattedSQL = sql % (aScrapeResult.Source ,aScrapeResult.Country,aScrapeResult.Operator ,aScrapeResult.FromCountry ,aScrapeResult.FromOperator ,aScrapeResult.ToCountry ,aScrapeResult.ToOperator ,aScrapeResult.SiteName ,aScrapeResult.Factor ,aScrapeResult.Granularity ,aScrapeResult.Description ,aScrapeResult.DataType ,aScrapeResult.Technology ,csvValues)

    DB = create_engine(self.ConnectionString)
    DB.connect()

    result_proxy = DB.execute(formattedSQL)

    results = result_proxy.fetchall()

对格式化SQL的检查产生以下命令

^{pr2}$

使用的各种版本和软件如下： SQL Server 2008 R2版 Python 2.6.6 SQLAlchemy 0.6.7版

我在sqlservermanagementstudio中用相同的参数直接调用存储过程，没有问题。在

值得指出的是，Python版本和sqlserver版本是不可更改的。我对sqlalchemy没有强烈的忠诚，我愿意接受其他建议。在

如有任何建议，如有需要，可提供更多信息。在

Answer 1

现在已修复，但如果我使用的是最佳实践，请随时征求意见。我使用了sqlalchemy公开的“text”对象，工作代码如下：

def SaveData(self, aScrapeResult):
    sql = "EXECUTE mc.SaveFundamentalDataCSV @pSource='%s',@pCountry='%s',@pOperator='%s',@pFromCountry='%s',@pFromOperator='%s',@pToCountry='%s',@pToOperator='%s',@pSiteName='%s',@pFactor='%s',@pGranularity='%s',@pDescription='%s',@pDataType='%s',@pTechnology = '%s',@pcsvData='%s'"

    #   Need to convert the data into CSV
    util = ListToCsvUtil()
    csvValues = util.ListToCsv(aScrapeResult.DataPoints)

    formattedSQL = sql % (aScrapeResult.Source ,aScrapeResult.Country,aScrapeResult.Operator ,aScrapeResult.FromCountry ,aScrapeResult.FromOperator ,aScrapeResult.ToCountry ,aScrapeResult.ToOperator ,aScrapeResult.SiteName ,aScrapeResult.Factor ,aScrapeResult.Granularity ,aScrapeResult.Description ,aScrapeResult.DataType ,aScrapeResult.Technology ,csvValues)

    DB = create_engine(self.ConnectionString)
    conn = DB.connect()

    t = text(formattedSQL).execution_options(autocommit=True)

    DB.execute(t)

    conn.close()

希望这对其他人有帮助！在