Error when inserting and updating simultaneously in sql server via pyodbc

37 Views Asked by At

I receive data from Mongodb via python and want to throw it into the sql server via pyodbc, but I always encounter the same error

pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 5 parameters were supplied', 'HY000').

I tried to insert parameters directly into the request, without using markers, the result was the same

def UpdateRecords(records):
try:
    conn = pyodbc.connect("""Driver={SQL Server Native Client 11.0};
                               Server=****;
                               Database=****;
                               Trusted_Connection=yes;""")
    dbCursor = conn.cursor()
    reqstring = """"
    MERGE INTO mobile_cur as target
    USING(VALUES (?,?,?,?,?) ) as source (Sys,UserId,UserName,Lat,Lon)
    ON target.Sys = source.Sys
    WHEN MATCHED THEN
    UPDATE SET target.UserId = source.UserId,target.UserName = source.UserName,
    target.Lat = source.Lat,target.Lon = source.Lon
    WHEN NOT MATCHED BY target THEN
    INSERT (Sys,UserId,UserName,Lat,Lon)
    VALUES (source.Sys,source.UserId,source.UserName,source.Lat,source.Lon);"""
    dbCursor.executemany(reqstring,records)
    conn.commit()
    cursor.close()
    conn.close()

finally:
    conn.close()
    cursor.close()

records = [(1503257, 1503257, 'HGQ', 77.54119, 66.625061), (1503253, 1503253, 'ZXC', 11.473105, 33.727711), (1503250, 1503250, 'FSD', 11.532186, 44.641628)]

Here's the second option

def UpdateRecords(records):
try:
    conn = pyodbc.connect("""Driver={SQL Server Native Client 11.0};
                               Server=___;
                               Database=___;
                               Trusted_Connection=yes;""")
    dbCursor = conn.cursor()        
    reqstring = """"
    MERGE INTO mobile_cur as target
    USING(VALUES {} ) as source (Sys,UserId,UserName,Lat,Lon)
    ON target.Sys = source.Sys
    WHEN MATCHED THEN
    UPDATE SET target.UserId = source.UserId,target.UserName = source.UserName,
    target.Lat = source.Lat,target.Lon = source.Lon
    WHEN NOT MATCHED THEN
    INSERT (Sys,UserId,UserName,Lat,Lon)
    VALUES (source.Sys,source.UserId,source.UserName,source.Lat,source.Lon);""".format(','.join(['(?,?,?,?,?)' for _ in range(len(records))]))
    params = [item for sublist in records for item in sublist]
    dbCursor.execute(reqstring,params)
    conn.commit()
    cursor.close()
    conn.close()

finally:
    conn.close()
    cursor.close()

The error is like this

pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 35 parameters were supplied', 'HY000')
0

There are 0 best solutions below