PYODBC/SQL: Assistance with string containing multiple SQL statements

Question

I'm trying to send SQL statements via cursor.execute but unable to get the proper syntax when having multiple statements in the string.

I've tested the cursor.execute() with simple insert statement and it populated the tsql database table fine.

The problem comes in when trying to apply the syntax that worked to my cursor.execute() string that contains multiple statements.

4/3 Update: So I believe this is the issue I need to resolve, it appears I need to use the format ("SQL statement values (?,?,?)",variables). Since I'm not referencing columns, I get an error if I try to use: 'INSERT ([Incident_Id],[Incident_Type],[Priority]) VALUES (IncIncident_ID, IncIncident_Type, IncPriority)'

So how can I incorporate this syntax ("SQL statement values (?,?,?)",variables) into the cursor.execute with the other sql statements? I tried this but it errors out... 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;')

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()

user212514 · Accepted Answer · 2019-04-04 05:05:25Z

1

I think you want to group your parameters as a sequence that is a second parameter to execute():

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

There's more info at the PYODBC wiki.

Update based on using a merge statement. You'll need to specify each of the parameters in the list, even if you're duplicating the variables. Note that the statement below has 5 ?s and there are 5 execute() parameters after the initial SQL string.

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)

edited Apr 4, 2019 at 5:05

answered Apr 3, 2019 at 15:42

user212514

3,1381 gold badge17 silver badges11 bronze badges

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

1 Comment

JJ Y Over a year ago

The INSERT statement by itself works fine, its trying to incorporate it in the MERGE, I added a few notes if you have a moment to review. I'm really in a pickle trying to get this to work !

Collectives™ on Stack Overflow

PYODBC/SQL: Assistance with string containing multiple SQL statements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related