Error while passing arraylist as argument to INSERT data into Ignite cache using pyodbc

classic Classic list List threaded Threaded
8 messages Options
rishi007bansod rishi007bansod
Reply | Threaded
Open this post in threaded view
|

Error while passing arraylist as argument to INSERT data into Ignite cache using pyodbc

This post was updated on .
I have written following code to insert data into Ingite cache from python using ODBC driver. In this want to pass arraylist of strings(id_list) as argument. My list contains variable number of elements that is why I need to pass it as argument to query.

cursor = connection.cursor()
select_string= "INSERT INTO Person (_key, id,  firstName, lastName, salary) VALUES (322, ? , 'abcd', 'dhsagd', 1000)"

id_list = ['test1', 'test2', 'test3', 'test4']
cursor.execute(select_string, id_list)


But when I pass list of strings as parameter to query I am getting following error.

Traceback (most recent call last):
  File "pythonOdbclist.py", line 13, in <module>
    cursor.execute(select_string, id_list)
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 4 parameters were supplied', 'HY000')

I think ignite is not identifying parameter marker "?" in select_string
Igor Sapego Igor Sapego
Reply | Threaded
Open this post in threaded view
|

Re: Error while passing arraylist as argument to INSERT data into Ignite cache using pyodbc

Hi,

This is pyodbc error, not Ignite driver's one. But according to [1] it
seems that you should pass parameters as individual values or tuple,
not as array list.



Best Regards,
Igor

On Sat, May 20, 2017 at 11:32 PM, rishi007bansod <[hidden email]> wrote:
I have written following code to insert data into Ingite cache from python
using ODBC driver. In this want to pass arraylist of strings(id_list) as
argument. My list contains variable number of elements that is why I need to
pass it as argument to query.

cursor = connection.cursor()
select_string= "INSERT INTO Person (_key, id,  firstName, lastName, salary)
VALUES (322, ? , 'abcd', 'dhsagd', 1000)"

id_list = ['test1', 'test2', 'test3', 'test4']
cursor.execute(select_string, id_list)

But when I pass list of strings as parameter to query I am getting following
error.

Traceback (most recent call last):
  File "pythonOdbclist.py", line 13, in <module>
    cursor.execute(select_string, id_list)
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 4
parameters were supplied', 'HY000')





--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Error-while-passing-arraylist-as-argument-to-INSERT-data-into-Ignite-cache-using-pyodbc-tp13049.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

rishi007bansod rishi007bansod
Reply | Threaded
Open this post in threaded view
|

Re: Error while passing arraylist as argument to INSERT data into Ignite cache using pyodbc

This post was updated on .
If instead of id_list i.e. list of ids, if I pass single parameter(not list) as argument as follows,

cursor = connection.cursor()
select_string= "INSERT INTO Person (_key, id,  firstName, lastName, salary)
VALUES (322, ? , 'abcd', 'dhsagd', 1000)"

cursor.execute(select_string, 'test')

I am still getting following error,

Traceback (most recent call last):
  File "pythonOdbclist.py", line 13, in <module>
    cursor.execute(select_string, id_list)
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1
parameters were supplied', 'HY000')


I think either Ignite or pyodbc is unable to identify parameter marker "?" that I have provided

Thanks.
ezhuravlev ezhuravlev
Reply | Threaded
Open this post in threaded view
|

Re: Error while passing arraylist as argument to INSERT data into Ignite cache using pyodbc

Hi,
It's could be a problem with encoding, did you try to use utf8?

2017-05-23 7:58 GMT+03:00 rishi007bansod <[hidden email]>:
If instead of id_list i.e. list of ids, if I pass single parameter(not list)
as argument as follows,

*cursor = connection.cursor()
select_string= "INSERT INTO Person (_key, id,  firstName, lastName, salary)
VALUES (322, ? , 'abcd', 'dhsagd', 1000)"

cursor.execute(select_string, 'test')
*
I am still getting following error,

*Traceback (most recent call last):
  File "pythonOdbclist.py", line 13, in <module>
    cursor.execute(select_string, id_list)
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1
parameters were supplied', 'HY000')*

I think either Ignite or pyodbc is unable to identify paramere marker *"?"*
that I have provided

Thanks.



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Error-while-passing-arraylist-as-argument-to-INSERT-data-into-Ignite-cache-using-pyodbc-tp13049p13079.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

rishi007bansod rishi007bansod
Reply | Threaded
Open this post in threaded view
|

Re: Error while passing arraylist as argument to INSERT data into Ignite cache using pyodbc

This post was updated on .
Hi,
    I have set encoding/decoding to 'utf8' as,

cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(str, encoding='utf-8')
cnxn.setencoding(unicode, encoding='utf-8')


as mentioned at link https://github.com/mkleehammer/pyodbc/wiki/Unicode#mysql-and-postgresql. But still I am getting error.

Thanks.

ezhuravlev ezhuravlev
Reply | Threaded
Open this post in threaded view
|

Re: Error while passing arraylist as argument to INSERT data into Ignite cache using pyodbc

Have you tried to execute same sql script without "?" ? Is it works?

Could you check logs from ignite? Are there any exceptions?



2017-05-23 19:41 GMT+03:00 rishi007bansod <[hidden email]>:
Hi,
    I have set encoding/decoding to 'utf8' as,

*cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(unicode, encoding='utf-8')*

as mentioned at link
https://github.com/mkleehammer/pyodbc/wiki/Unicode#mysql-and-postgresql
<https://github.com/mkleehammer/pyodbc/wiki/Unicode#mysql-and-postgresql>  .
But still I am getting error.

Thanks.





--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Error-while-passing-arraylist-as-argument-to-INSERT-data-into-Ignite-cache-using-pyodbc-tp13049p13097.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

rishi007bansod rishi007bansod
Reply | Threaded
Open this post in threaded view
|

Re: Error while passing arraylist as argument to INSERT data into Ignite cache using pyodbc

SQL script without "?" works correctly
Igor Sapego Igor Sapego
Reply | Threaded
Open this post in threaded view
|

Re: Error while passing arraylist as argument to INSERT data into Ignite cache using pyodbc

Have you tried "select" queries? Do they work?

This is error of the pyodbc, not Ignite's. If that was because of the Ignite
you'd get different kind of error. Have you tried asking pyodbc guys?

Best Regards,
Igor

On Thu, May 25, 2017 at 8:01 AM, rishi007bansod <[hidden email]> wrote:
SQL script without "?" works correctly



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Error-while-passing-arraylist-as-argument-to-INSERT-data-into-Ignite-cache-using-pyodbc-tp13049p13132.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.