Python error updating a SQL DB

Question

I have some python code that looks like this

import pypyodbc
import pandas as pd
home="c:/SQL/"
df = pd.read_sql_query(sql4, conn3
for y1 in range(0 , k):
    ARCHIVE_SERNUM = (df['sernum']).iloc[y1]
    KQL=len(KIC53_QUERY_LIST)
    FOUND=False
    for y2 in range(0,KQL):
        if ARCHIVE_SERNUM == KIC53_QUERY_LIST[y2]:
            FOUND=True
            #do something then
            break
    if FOUND == False:
        print(FOUND,ARCHIVE_SERNUM,"This is STIME : ",STIME)
        CTIME=STIME
        cursor = conn3.cursor()
        cursor.execute("""
            UPDATE ENCOMPASS_DIA
            SET CTIME=%s
            WHERE SERNUM=ARCHIVE_SERNUM
            """, (STIME))

Its throwing an error and I cannot figure out what is going on. In this example both CTIME and STIME are equal to the same 17 character string.

File "c:/SQL/ConnectionTest8.py", line 212, in <module>
""", (STIME))

TypeError: Params must be in a list, tuple, or Row

e4c5 · Accepted Answer · 2017-05-31 08:47:51Z

1

An easy enough mistake to make.

 cursor.execute("""
        UPDATE ENCOMPASS_DIA
        SET CTIME=%s
        WHERE SERNUM=ARCHIVE_SERNUM
        """, (STIME, ))

There should be a trailing , after the STIME or (STIME) will be interpreted as a list instead of a tuple.

answered May 31, 2017 at 8:47

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

4 Comments

C0ppert0p Over a year ago

does the fact I'm using an access DB make a difference? Your solution resulted in this error message: ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '%s'.")

e4c5 Over a year ago

Sorry missed the pyodbc tag. Here you have to use ? instead of %s

e4c5 Over a year ago

Is this sorted out now?

C0ppert0p Over a year ago

Sorry, I discovered the error and posted the findings, right after found that that '%s' error, and then forgot to press the acceptance arrow.

C0ppert0p · Accepted Answer · 2017-06-01 09:01:03Z

1

In this case the correct Update statement is:

cursor.execute("""UPDATE ENCOMPASS_DIA SET CTIME=? WHERE SERNUM=?""", (SSTIME,ARCHIVE_SERNUM ))

answered Jun 1, 2017 at 9:01

C0ppert0p

7022 gold badges7 silver badges25 bronze badges

Collectives™ on Stack Overflow

Python error updating a SQL DB

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related