CODEWITHSUNDEEP
pythonmysqldatabasems-accesspyodbc
aditseng
aditseng

Reputation: 99

'HY000', 'The SQL contains 31 parameter markers, but 32 parameters were supplied' with access mdb file

I'm copying from MySQL to MDB and I'm coming up against a strange issue. I have a query

cur.execute("""UPDATE SIGN_INVENTORY SET DISTRICT=?, COUNTY=?, CITY=?, ROUTE_ID=?, NEXT_INSPECTION_DATE=?, INSP_FREQ=?, GPS_LATITUDE=?, GPS_LONGITUDE=?,
FABRICATOR=?, CONTRACTOR=?, DATE_ERECTED=?, DATE_LAST_MODIFIED=?, BRIDGE_ID=?, STATE_PLANE=?, BEGIN_NODE=?, BEGIN_OFFSET=?,
SPAN_LENGTH=?, NUMBER_SIGNS=?, TOTAL_SIGN_AREA=?, WALKWAY_LENGTH=?, STRUCTURE_TYPE=?, MESSAGE_TYPE=?, VMS_TYPE=?, MATERIAL_TYPE=?,
CHORD_TYPE=?, POLE_TYPE=?, FOUNDATION_TYPE=?, LAST_INSPECTOR=?, LMOD_USERID=?, LMOD_DATE=?, LMOD_TIME=?
WHERE STRUCTURE_NUMBER=?""", (row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10],
                              row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20],
                              row[21], row[22], row[23], row[24], row[25], row[26], row[27], row[28], row[29], row[30],
                              row[31], row[0]))

I'm getting the error

'HY000', 'The SQL contains 31 parameter markers, but 32 parameters were supplied'

Now if I remove the last row[0] I get

'HY000', 'The SQL contains 32 parameter markers, but 31 parameters were supplied'

The columns in both databases are exactly the same. I'm doing very similar queries in multiple other places and they work fine.

Upvotes: 2

Views: 1532

Answers (2)

user6590287
user6590287

Reputation: 1

Hey buddy nothing need to do just pass 31 parameters not more then that

see 1 to 31 is total 31 after that u are passing row[0] and that makes the total parameter count is 32, that's why u are getting that error.

Upvotes: 0

aditseng
aditseng

Reputation: 99

I figured it out. In MySQL date fields are stored like

datetime.datetime(2002, 7, 22, 0, 0)

this was causing the parameter count to break. I needed to cast the values to char like so

cast(NEXT_INSPECTION_DATE as char)

Upvotes: 2

Related Questions