Python call sql-server stored procedure with table valued parameter

by

On the basis of the comments to my question i’ve managed to get the stored procedure running with table valued parameters (and get the return values from the SP)
The final script is as follows:

import pandas as pd
import pytds
from pytds import login
import sqlalchemy as sa
from sqlalchemy import create_engine
import sqlalchemy_pytds

def connect():
    return pytds.connect(dsn='ServerName',database="DBName",autocommit=True, auth=login.SspiAuth())

engine = sa.create_engine('mssql+pytds://[ServerName]', creator=connect)
conn = engine.raw_connection()

with conn.cursor() as cur:
    arg = [["foo.ExternalInput","bar.ExternalInput"]]
    tvp = pytds.TableValuedParam(type_name="core.MatchColumnTable", rows=arg)
    cur.execute("EXEC test_proc @Target = N'[dbname].[tablename1]', @Source = N'[dbname].[table2]', @CleanTarget = 0, @UseColumnsFromTarget = 0, @MergeOnColumn = %s", (tvp,))
    result = cur.fetchall()
    print(result)

The autocommit is added in the connection (to commit the transaction in the cursor), the table valued parameter (marchcolumntable) expects 2 columns, so the arg is modified to fit 2 columns.

The parameters that are required besides the tvp are included in the exec string. The last param in the execute string is the name of the tvp parameter(mergeoncolumn) that is filled with the tvp.

optionally you can add the result status or row count as descripted in the pytds documentation:
https://python-tds.readthedocs.io/en/latest/index.html

Note!: in the stored procedure you have to make sure that the
SET NOCOUNT ON is added otherwise you wont get any results back to Python

More Related Contents:

  1. stored procedures with sqlAlchemy
  2. How to upsert pandas DataFrame to Microsoft SQL Server table?
  3. How do I connect to SQL Server via sqlalchemy using Windows Authentication?
  4. Connecting to SQL Server 2012 using sqlalchemy and pyodbc
  5. pyodbc.connect() works, but not sqlalchemy.create_engine().connect()
  6. Writing a connection string when password contains special characters
  7. Does SQLAlchemy have an equivalent of Django’s get_or_create?
  8. How to write DataFrame to postgres table
  9. Speeding up pandas.DataFrame.to_sql with fast_executemany of pyODBC
  10. CS50: LIKE operator, variable substitution with % expansion
  11. SQLAlchemy: What’s the difference between flush() and commit()?
  12. Group by & count function in sqlalchemy
  13. Debugging (displaying) SQL command sent to the db by SQLAlchemy
  14. Flask-SQLalchemy update a row’s information
  15. Error 28000: Login failed for user DOMAIN\\user with pyodbc
  16. Efficiently updating database using SQLAlchemy ORM
  17. SQLAlchemy ORDER BY DESCENDING?
  18. SQLAlchemy ON DUPLICATE KEY UPDATE
  19. Flask sqlalchemy many-to-many insert data
  20. How to delete a record by id in Flask-SQLAlchemy
  21. Using SQL Server stored procedures from Python (pyodbc)
  22. How to upsert pandas DataFrame to PostgreSQL table?
  23. How to do an upsert with SqlAlchemy?
  24. SQLAlchemy printing raw SQL from create()
  25. SQLAlchemy: how to filter date field?
  26. How to execute “left outer join” in SqlAlchemy
  27. How to close a SQLAlchemy session?
  28. Using SQLAlchemy models in and out of Flask
  29. SQLAlchemy column name with space
  30. using sqlalchemy to load csv file into a database

Leave a Comment