How to insert values into SQL Server table using Stored procedure in Python

Question

I am new to pytds. Facing TypeError: not enough arguments for format string issue while inserting data into SQL Server.

Issue I am getting is:

Traceback (most recent call last): File "c:/Users/mydesk/Desktop/test/test_pytds.py", line 64, in connect_and_call_stored_procedure(query, val) File "c:/Users/mydesk/Desktop/test/test_pytds.py", line 48, in call_sproc cursor.execute(query, (tvp,)) File "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds_init_.py", line 739, in execute self.execute(operation, params) File "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds_init.py", line 701, in _execute operation = operation % names[0] TypeError: not enough arguments for format string

Something I missed. Let me know your valuable inputs.

My complete code:

import pytds 
def get_query() -> str:
    sql = '''
        DECLARE @tbl [dbo].[tbl]
        insert into @tbl
        (
            col1, col2, col3
        )
        values (%s,%s,%s)
    '''  
    query += '''
         EXECUTE [dbo].[stproc] 
         @tbl
    '''
    return sql

def call_sproc(query, val):
    server = 'my_server'
    user = 'my_user'
    password = 'secret'
    database = 'my_db'
    
    try:
        conn = pytds.connect(server=server, user=user, password=password, database=database)
        cursor = conn.cursor()

        tvp = pytds.TableValuedParam(type_name='dbo.tbl', rows=val)

        cursor.execute(query, (tvp,))

        conn.commit()

        cursor.close()
        conn.close()

    except pytds.Error as e:
        print("Connection issue:", e)

###
val=[('col1_val'), ('col2_val'), ('col3_val')]
my_sql = get_query()
call_sproc(my_sql, val)

cursor.callproc("dbo.stproc", (tvp,)) python-tds.readthedocs.io/en/latest/pytds.html or if you still want to use an ad-hoc batch then do cursor.execute("EXECUTE [dbo].[stproc] %s", (tvp,)) — Charlieface
– Charlieface, Commented Aug 9, 2023 at 10:56
Hi @Charlieface, Thank you. If I mention the stored proc in callproc my insert is not running. is anything to change ? — RSG
– RSG, Commented Aug 9, 2023 at 12:55
Well I'm somewhat confused by your current code. You hav a TVP already, why are you trying to create a new one using DECLARE...INSERT... why not just pass the TVP straight to the procedure? — Charlieface
– Charlieface, Commented Aug 10, 2023 at 8:06
yah got it Charlie. I removed Insert query and it is working fine. Thank you — RSG
– RSG, Commented Aug 10, 2023 at 8:13

Charlieface · Accepted Answer · 2023-08-10 08:17:03Z

-1

To call a stored procedure, you don't need any query or DECLARE. Just do what the docs say and use callproc

cursor.callproc("dbo.stproc", (tvp,))

Or if you really want to use an ad-hoc batch then do

cursor.execute("EXECUTE dbo.stproc %s", (tvp,))

answered Aug 10, 2023 at 8:17

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

How to insert values into SQL Server table using Stored procedure in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related