pyodbc call stored procedure with parameter name; data type issue

Question

When running cursor_insert.execute(sql,params), I keep getting "[SQL ERROR] Error converting data type nvarchar to float" even though my source and target db table have the data defined as float, nvarchar and nvarchar along with my stored procedure.

Is setting my parameters to a new variable called 'params' causing this change in datatype? If so, how can I work around it? (In reading some Python documentation, it shouldn't have changed the datatype, correct?)

# Create cursor associated with connection
cursor=conn.cursor()
cursor_select = conn.cursor()
cursor_insert = conn.cursor()

if conn:
    print('***** Connected to DCPWDBS289 *****')

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]    # Float
    IncIncident_Type    = row[1]    # Str
    IncPriority         = row[2]    # Str

    sql = """EXEC ITSM.dbo.ITSM_LOAD @IncIncident_ID=?, 
    @IncIncident_Type=?,@IncPriority=?"""

    params = ('IncIncident_ID','IncIncident_Type','IncPriority')

    cursor_insert.execute(sql,params)

del cursor_insert
cursor.commit()
conn.close()

drec4s · Accepted Answer · 2019-04-03 18:51:00Z

1

You are not passing the parameter values, instead you are passing a string literal, try this:

# Create cursor associated with connection
cursor=conn.cursor()
cursor_select = conn.cursor()
cursor_insert = conn.cursor()

if conn:
    print('***** Connected to DCPWDBS289 *****')

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]    # Float
    IncIncident_Type    = row[1]    # Str
    IncPriority         = row[2]    # Str

    sql = """EXEC ITSM.dbo.ITSM_LOAD @IncIncident_ID=?, 
    @IncIncident_Type=?,@IncPriority=?"""

    params = (IncIncident_ID, IncIncident_Type, IncPriority)

    cursor_insert.execute(sql,params)

del cursor_insert
cursor.commit()
conn.close()

answered Apr 3, 2019 at 18:51

drec4s

8,1178 gold badges35 silver badges56 bronze badges

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

2 Comments

JJ Y Over a year ago

I realized that after I performed some troubleshooting and did a select, my columns were filled with the variable name, LOL. I implemented your suggestion. It worked, thank you. Only problem I have now is something in my loop is incorrect, it's putting in each of the 5 rows into the new table over 3million times each row. I should probably start new thread but you see anything glaring?

JJ Y Over a year ago

Thank you. I found the issue with the endless looping, it was an issue with my stored procedure.

Collectives™ on Stack Overflow

pyodbc call stored procedure with parameter name; data type issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related