Validating Excel cell value and inserting into SQL error HY000 Python

Question

I am trying to validating the excel data if it is > 12 chr length then i need to insert in a table (sql) with python code

I have tried with this code and i am getting the below error

'The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

Value in excel already with closed brackets like ('12ewrr334dgdgskngk')

and i ran the query in SSMS and it is working fine INSERT INTO #finalresultset1 ( VIN ) Values ('12ewrr334dgdgskngk')

import xlrd 
import pyodbc



book = xlrd.open_workbook(r'excelpath')
sheet = book.sheet_by_name(r'Sheet')


cnxn = pyodbc.connect('database connection')
cursor = cnxn.cursor()

query = """ INSERT INTO  #finalresultset1 ( VIN )  Values   """


VINSheet = sheet.ncols
for row in range(0,sheet.nrows):
    for col in range(0,VINSheet):
        cell_VIN = sheet.cell(row,col)
        if len(cell_VIN.value) >= 12:
           cursor.execute(query, cell_VIN.value)

        else:
            print('VIN Length must be greater than 17')

Tried cursor.execute(query, (cell_VIN.value, ))
This time i got the different error
pyodbc.ProgrammingError: ('42S02', 
"[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]
Invalid object name '#finalresultset1'. (208) (SQLExecDirectW); 
[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]
Statement(s) could not be prepared. (8180)") 
and I verified the temp table it is exist in my DB

EDIT

cursor.execute(" INSERT INTO #finalresultset1 ( product ) Values (?) ", 
                 cell_VIN.value)

It's unlikely that cell_VIN.value are a tuple ('12ewrr334dgdgskngk'). — stovfl
– stovfl, Commented Nov 5, 2018 at 21:12
I removed the close brackets and tried ,getting the same error — Rahul
– Rahul, Commented Nov 5, 2018 at 21:34
This time i got the different error pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#finalresultset1'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)") and I verified the temp table it is exist in my DB — Rahul
– Rahul, Commented Nov 5, 2018 at 21:53

Chris Curvey · Accepted Answer · 2018-11-07 13:18:21Z

0

query = """ INSERT INTO  #finalresultset1 ( VIN )  Values (?)"""

(Add the (?) after values)

edited Nov 7, 2018 at 13:18

answered Nov 5, 2018 at 22:54

Chris Curvey

10.5k14 gold badges53 silver badges75 bronze badges

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

4 Comments

Rahul Over a year ago

still getting first error pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

stovfl Over a year ago

@Rahul: Edit your Question and show your current query and .execute(.... Read pyodbc/wiki/Cursor

Rahul Over a year ago

import xlrd import pyodbc book = xlrd.open_workbook(r'excel path') sheet = book.sheet_by_name(r'Sheet') cnxn = pyodbc.connect('connection string') cursor = cnxn.cursor() VINSheet = sheet.ncols for row in range(0,sheet.nrows): for col in range(0,VINSheet): cell_VIN = sheet.cell(row,col) if len(cell_VIN.value) >= 12: # print(cell_VIN.value) cursor.execute(" INSERT INTO #finalresultset1 ( product ) Values (?) ", cell_VIN.value) else: print('VIN Length must be greater than 17')

Chris Curvey Over a year ago

which error? the error about the parameter count? or the error about the table not existing?

Gord Thompson · Accepted Answer · 2018-11-07 13:44:33Z

0

Invalid object name '#finalresultset1'

#finalresultset1 is a local temporary table because its name begins with #. You are opening your connection and then trying to insert into that table without creating it first. That will never work because local temporary tables only exist for the current session, and your session (created by the connect call) has not created that table.

answered Nov 7, 2018 at 13:44

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Collectives™ on Stack Overflow

Validating Excel cell value and inserting into SQL error HY000 Python

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