Python to SQL Server Stored Procedure

Question

I am trying to call a SQL Server stored procedure from my Python code, using sqlalchemy. What I'm finding is that no error is raised by the python code and the stored procedure is not executing.

Sample code:

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

Examination of formatted SQL yields the following command

EXECUTE mc.SaveFundamentalDataCSV @pSource='PythonTest', @pCountry='UK',
  @pOperator='Operator', @pFromCountry='None', @pFromOperator='None', 
  @pToCountry='None', @pToOperator='None', @pSiteName='None', @pFactor='Factor', 
  @pGranularity='Hourly', @pDescription='Testing from python', 
  @pDataType='Forecast',@pTechnology = 'Electricity',
  @pcsvData='01-Jan-2012 00:00:00,01-Feb-2012 00:15:00,1,01-Jan-2012 00:00:00,01-Feb-2012 00:30:00,2';

The various versions and software in use is as follows: SQL Server 2008 R2 Python 2.6.6 SQLAlchemy 0.6.7

I have tested my stored procedure by calling it directly in SQL Server Management Studio with the same parameters with no problem.

It's worth stating that this point that the Python version and the SQL server version are non-changeable. I have no strong allegiance to sqlalchemy and am open to other suggestions.

Any advice would be greatly appreciated, more information can be provided if needed.

I have fixed this using the text() object of sqlalchemy. As a noob, I'm forbidden from answering my own question for 7 hours. Will post full solution once that time has expired. — GinjaNinja
– GinjaNinja, Commented Sep 18, 2012 at 14:53

GinjaNinja · Accepted Answer · 2012-09-19 08:50:21Z

2

Fixed now but open to opinion if I'm using best practice here. I've used the 'text' object exposed by sqlalchemy, working code below:

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

Hope this proves helpful to someone else!

answered Sep 19, 2012 at 8:50

GinjaNinja

7888 silver badges19 bronze badges

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

2 Comments

Cees Timmerman Over a year ago

It's not very clear in the documentation of SQLalchemy, but DB.execute(t) is unsafe like that. You should use bind variables in the SQL and a dictionary as second parameter to execute().

Cees Timmerman Over a year ago

Or with less code: engine.execute("insert into users values (?, ?)", 1, "john"). Reason being the escaping of dangerous characters like ' from your values.

Collectives™ on Stack Overflow

Python to SQL Server Stored Procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related