英文:
How to insert values into SQL Server table using Stored procedure in Python
问题
我是pytds的新手。在向SQL Server插入数据时遇到了TypeError: not enough arguments for format string的问题。
我遇到的问题是：
追踪回溯（最近的调用最后）：
文件 "c:/Users/mydesk/Desktop/test/test_pytds.py"，第 64 行，在中
connect_and_call_stored_procedure(query, val)
文件 "c:/Users/mydesk/Desktop/test/test_pytds.py"，第 48 行，在 call_sproc 中
cursor.execute(query, (tvp,))
文件 "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds_init_.py"，第 739 行，在 execute 中
self.execute(operation, params)
文件 "C:\Program Files (x86)\Python38-32\lib\site-packages\pytds_init.py"，第 701 行，在 _execute 中
operation = operation % names[0]
TypeError: not enough arguments for format string
我漏掉了什么。请提供您宝贵的意见。
我的完整代码：
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 + query
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)
英文:
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 <module>
> 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)
答案1
得分: -1
调用存储过程时，无需任何查询或DECLARE。只需按照文档中的说明使用callproc即可。
cursor.callproc("dbo.stproc", (tvp,))
或者，如果您真的想使用临时批处理，则可以执行以下操作：
cursor.execute("EXECUTE dbo.stproc %s", (tvp,))
英文:
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,))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论