我试图通过SQL发送语句光标.执行但是当字符串中有多个语句时,无法获得正确的语法。在
我已经测试了光标.执行()使用简单的insert语句,它很好地填充了tsql数据库表。在
当尝试将适用于我的光标.执行()包含多个语句的字符串。在
4/3更新:所以我认为这是我需要解决的问题,似乎我需要使用格式(“SQL语句值(?)?,?,?)",变量)。因为我没有引用列,所以如果我试图使用“INSERT([Incident\uid],[Incident\utype],[Priority])值(incincincident_Id,IncIncident_Type,IncPriority)”,我会收到一个错误
那么,我如何合并这种语法(“SQL语句值(?)?,?,?)",变量)光标.执行用其他sql语句?我试过了,但结果错了。。。 光标_插入.执行(合并到dbo.jjy_表作为目标' '将事件全部用作源' '打开Target.Incident_Id目标事件= 来源:事件编号' '当目标不匹配时' '”插入([事件编号],[事件类型],[优先级]) 数值(?)?,?,?)",事件事件标识,事件类型,IncPriority' '当匹配时' '更新设置目标。[事件类型]=意外事件类型,目标。[优先级]= IncPriority;')
import pyodbc
import os.path
import logging
import datetime
import time
import os
import logging.handlers
conn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=TESTSERVER;"
"Database=ITSM;"
"Trusted_Connection=yes;"
"MARS_Connection=Yes;")
cursor=conn.cursor()
cursor_select = conn.cursor()
cursor_insert = conn.cursor()
if conn:
print('***** Connected to TESTSERVER *****')
select_str="SELECT TOP 5 Incident_ID,Incident_Type,Priority FROM
incidents_all WHERE incidents_all.Status NOT IN ('Closed','Resolved')"
cursor_select.execute(select_str)
while True:
row = cursor_select.fetchone()
if not row:
break
print(' Row: ', row)
IncIncident_ID= row[0]
IncIncident_Type=row[1]
IncPriority=row[2]
print('IncIncident_ID: ',IncIncident_ID)
print('IncIncident_Type: ',IncIncident_Type)
print('IncPriority: ',IncPriority)
# This works, I get the sytax, I have a statement and passing in the
#variables .
cursor_insert.execute("INSERT INTO dbo.jjy_table ([Incident_Id],
[Incident_Type],[Priority]) values
(?,?,?)",IncIncident_ID,IncIncident_Type,IncPriority)
#This Doesn't work in Python but the SQL code works in SQL if I remove
#the value (?,?,?)
# I believe issue is with Python syntax but not sure how to fix. How do
#I incorporate the syntax above into a string of multiple statements.
cursor_insert.execute("MERGE INTO dbo.jjy_table as Target USING
Incidents_All AS Source ON Target.Incident_Id = Source.Incident_ID WHEN
NOT MATCHED BY Target THEN "INSERT ([Incident_Id],[Incident_Type],
[Priority]) values (?,?,?)",IncIncident_ID, IncIncident_Type,
IncPriority" WHEN MATCHED THEN UPDATE SET Target.[Incident_Type] =
IncIncident_Type, Target.[Priority] = IncPriority;")
cursor.commit()
conn.close()
我想您应该将参数分组为一个序列,它是
execute()
的第二个参数:在the PYODBC wiki有更多信息。在
基于使用
^{pr2}$merge
语句进行更新。您需要指定列表中的每个参数,即使您复制了这些变量。注意,下面的语句有5个?
,在初始SQL字符串后面有5个execute()
参数。在相关问题 更多 >
编程相关推荐