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