时间戳

首页 > 解决方案 > PYODBC/SQL：协助包含多个 SQL 语句的字符串

python - PYODBC/SQL：协助包含多个 SQL 语句的字符串

问题描述

我正在尝试通过 cursor.execute 发送 SQL 语句，但在字符串中有多个语句时无法获得正确的语法。

我已经用简单的插入语句测试了 cursor.execute()，它很好地填充了 tsql 数据库表。

当试图将适用于我的包含多个语句的 cursor.execute() 字符串的语法应用到时，问题就出现了。

4/3 更新：所以我相信这是我需要解决的问题，看来我需要使用格式（“SQL 语句值（？，？，？）”，变量）。由于我没有引用列，因此如果我尝试使用会出现错误：'INSERT ([Incident_Id],[Incident_Type],[Priority]) VALUES (IncIncident_ID, IncIncident_Type, IncPriority)'

那么如何将这种语法（“SQL 语句值（？，？，？）”，变量）与其他 sql 语句合并到 cursor.execute 中？我试过了，但它出错了... 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]) 值 (?,?,?)", IncIncident_ID,IncIncident_Type,IncPriority ' 'WHEN MATCHED THEN ' 'UPDATE SET Target.[Incident_Type] = IncIncident_Type, Target.[Priority] = 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()

标签： pythontsqlpyodbc

解决方案

我认为您想将参数分组为一个序列，该序列是第二个参数execute()：

cursor_insert.execute("INSERT INTO dbo.jjy_table ([Incident_Id], 
    [Incident_Type],[Priority]) values 
    (?,?,?)", [IncIncident_ID,IncIncident_Type,IncPriority] )

PYODBC wiki上有更多信息。

基于使用merge语句的更新。即使您要复制变量，您也需要指定列表中的每个参数。请注意，下面的语句有 5秒，并且在初始 SQL 字符串之后 ?有 5 个参数。execute()

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 (?,?,?)
    WHEN MATCHED THEN
        UPDATE SET Target.[Incident_Type] = ?, Target.[Priority] = ?;
    """, IncIncident_ID, IncIncident_Type, IncPriority, IncIncident_Type, IncPriority)

推荐阅读