Passing parameters not being recognized and throws SQL error when executing raw query (on SQL-Server database and Pymssql) with SqlAlchemy

Question

I'm trying to execute simple raw SQL query, on SQL-Server db with SqlAlchemy (and Pymssql as provider).

Here is my first try (using execute method of connection and passing parameters in **kwargs way):

provider = DataProvider().engine
q = "select url from Crawler.CrawlSource where source=@source"
query = text(q)
result = provider.connect().execute(query, source ='mysource')

I passed the parameters in any way shown in tutorials (passing as kwargs and passing as dict) but neither of them worked and when execute method is called an exception is thrown that says 'Must declare the scalar variable' @source as if no parameter is passed to execute method, it seems ORM (or maybe data provider (pymssql in this case) ) doesn't recognize parameters passed to execute method and passes only query (without parameters) to db engine (which causes the exception).

I guess there might be some issue with MSSQL-Server provider (Pymssql) since SQL-Server is not first class citizen in SqlAlchemy and Python family, but have no direct clue what could causes this.

as I said above I also tried other ways as well

Here is my second try (using execute method of connection and passing parameters as dict):

provider = DataProvider().engine
q = "select url from Crawler.CrawlSource where source=@source"
query = text(q)
result = provider.connect().execute(query, {source :'mysource'})

My third try (using execute method of engine object and passing parameters in **kwargs way):

provider = DataProvider().engine
q = "select url from Crawler.CrawlSource where source=@source"
query = text(q)
result = provider.execute(query, source ='mysource')

My forth try (using execute method of engine object and passing parameters as dict):

provider = DataProvider().engine
q = "select url from Crawler.CrawlSource where source=@source"
query = text(q)
result = provider.execute(query, {source :'mysource'})

My fifth try (creating a session and using execute method of session and passing parameters as dict):

provider = DataProvider().engine
session = sessionmaker(bind=provider)()
q = "select url from Crawler.CrawlSource where source=@source"
query = text(q)
result = session.execute(query, {source :'mysource'})

My sixth try (creating a session and using execute method of session and passing parameters in **kwargs way):

provider = DataProvider().engine
session = sessionmaker(bind=provider)()
q = "select url from Crawler.CrawlSource where source=@source"
query = text(q)
result = session.execute(query,  source='mysource')

but as I mentioned earlier none of the effortsabove worked and all of them led to the same exception mentioned above

Any help would be appreciated

Answer 1

The mssql+pymssql dialect appears to support the "pyformat" paramstyle. This works for me:

import sqlalchemy as sa

engine = sa.create_engine("mssql+pymssql://@localhost:49242/myDb")

sql = "SELECT word FROM vocabulary WHERE language = %(lang)s"
params = {'lang': 'Greek'}
with engine.begin() as conn:
    result = conn.execute(sql, params).fetchall()
    print(result)
    # [('γιορτή',), ('ηλεκτρονικός υπολογιστής',)]

We can also use the "named" paramstyle if we use a SQLAlchemy text object:

sql = sa.sql.text("SELECT word FROM vocabulary WHERE language = :lang")
params = {'lang': 'Greek'}
with engine.begin() as conn:
    result = conn.execute(sql, params).fetchall()
    print(result)
    # [('γιορτή',), ('ηλεκτρονικός υπολογιστής',)]

The text object allows us to consistently use the "named" paramstyle regardless of the native paramstyle supported by the DB-API layer (e.g., %s for pymssql, ? for pyodbc).