TypeError: 'type' object is not subscriptable with pypyodbc (Python)

Question

What is wrong with my below python code ?

I want to connect to my database, select some information. These are in a list of list, I grab the list and I do a "select..from..where..IN" :

 import pypyodbc
 connection = pypyodbc.connect('Driver={SQL Server};'
                                    'Server=X;'
                                    'Database=Y;'
                                    'uid=X;pwd=Y')
 cursor = connection.cursor()

 NbFiche=0
 L=[[4702, 3095, 3543], [2040, 2030, 2020], []]
 for i in range(0,3):
    log=L[i]


    if (log is not None):
        if (len(log)==3):                

            SQLCommand = ("select count(*) from PRODUCTION where  ID_TV IN (?) ")
            cursor.execute(SQLCommand,(log,))
            results = cursor.fetchone()
            NbFiche += results[0]

This is the error :

 Traceback (most recent call last):
      File "//Srvaktct-bur02/telecontact/TCT TRAVAIL/Pôle Fichier/AMIRA/STATISTIQUES/nimp.py", line 18, in <module>
        cursor.execute(SQLCommand,(log,))
      File "C:\Users\admin_fichier\AppData\Local\Programs\Python\Python35\lib\site-packages\pypyodbc-1.3.3-py3.5.egg\pypyodbc.py", line 1470, in execute
        self._BindParams(param_types)
      File "C:\Users\admin_fichier\AppData\Local\Programs\Python\Python35\lib\site-packages\pypyodbc-1.3.3-py3.5.egg\pypyodbc.py", line 1275, in _BindParams
        if param_types[col_num][0] == 'u':
    TypeError: 'type' object is not subscriptable

New code (Edit):

 import pypyodbc
connection = pypyodbc.connect('Driver={SQL Server};'
                              'Server=x;'
                                    'Database=y;'
                                    'uid=x;pwd=y')

cursor = connection.cursor()

NbFiche=0
L=[[4702, 3095, 3543], [2040, 2030, 2020]]
for log in L:
    log=tuple(log) # I also tried with a list

    SQLCommand = ("select count(*) from PRODUCTION where  ID_TV IN (?) ")
    cursor.execute(SQLCommand,(log,))
    results = cursor.fetchone()
    NbFiche += results[0]

EDIT :

import pypyodbc
connection = pypyodbc.connect('Driver={SQL Server};'
                                  'Server=x;'
                                        'Database=y;'
                                        'uid=x;pwd=y')
cursor = connection.cursor()
NbFiche=0
L=[[4702, 3095], [2040, 2030, 2020]]
for log in L:
    SQLCommand = ("select count(*) from PRODUCTION where  ID_TV IN (?)")
    params = ','.join(map(str,log))
    cursor.execute(SQLCommand,params)
    results = cursor.fetchone()
    NbFiche += results[0]

Here the result :

Traceback (most recent call last):
  File "//Srvaktct-bur02/telecontact/TCT TRAVAIL/Pôle Fichier/AMIRA/STATISTIQUES/nimp.py", line 13, in <module>
    cursor.execute(SQLCommand,params)
  File "C:\Users\admin_fichier\AppData\Local\Programs\Python\Python35\lib\site-packages\pypyodbc-1.3.3-py3.5.egg\pypyodbc.py", line 1454, in execute
    raise TypeError("Params must be in a list, tuple, or Row")
TypeError: Params must be in a list, tuple, or Row

Iron Fist · Accepted Answer · 2016-06-29T14:02:58.090

0

I think because you have an empty list in:

L=[[4702, 3095, 3543], [2040, 2030, 2020], []]
                                           ^^

And when testing with if, even thought the list is empty, it passes the test, similar to the following example:

>>> l = []
>>> if l is not None:
        print('l is not empty')


l is not empty

Which is wrong in fact, so, to solve this, do as :

>>> if l:
        print('l is not empty')
    else:
        print('l is Empty')


l is Empty

So, change you test expression to:

if log:
    if len(log)==3:
        #...

EDIT:

If the length of log is is of variable length, then probably you will have to first build the string parameter then pass it to the cursor.execute method, this way:

SQLCommand = "select count(*) from PRODUCTION where ID_TV IN ({}) "
params = ','.join(map(str,log))
cursor.execute(SQLCommand.format(params))

EDIT2:

The previously proposed solution is exposed to SQL_injections vulnerability if the params are user input data. So the better approach is to pass them as parameters to the cursor.execute method:

L=[[4702, 3095, 3543], [2040, 2030, 2020]]
for log in L:
    if log:
        SQLCommand = "select count(*) from PRODUCTION where  ID_TV IN ?"
        cursor.execute(SQLCommand, tuple(log))

If you have multiple params:

SQLCommand = "select ?, ? from PRODUCTION where ID_TV IN ?"
cursor.execute(SQLCommand, (p1, p2, tuple(log)))

edited Jun 29 '16 at 14:02

answered Jun 28 '16 at 13:20

Iron Fist

10,739
2
18
34

Tanks for your answer! I tried to just remove the empty list and same problem! I edit my code with the new one – blabla Jun 28 '16 at 13:31
@amiraayadi , remove this line: `log=tuple(log)` and modify `cursor.execute(SQLCommand,(log,))` to `cursor.execute(SQLCommand, log)` – Iron Fist Jun 28 '16 at 13:38
'The SQL contains 1 parameter markers, but 3 parameters were supplied') – blabla Jun 28 '16 at 13:42
if i make this change : SQLCommand = ("select count(*) from PRODUCTION where ID_TV IN (?,?,?) ") IT'S WORK BUT in my real list, I deal with [[1,2,3],[1,2]] not the same len so I can't use the "in (?,?,?)" – blabla Jun 28 '16 at 13:43
So the length of params is variable not fixed? – Iron Fist Jun 28 '16 at 13:47
@amiraayadi .. Check my edit to you and respond back with the result, please – Iron Fist Jun 28 '16 at 14:06
here the results : "TypeError: Params must be in a list, tuple, or Row" – blabla Jun 28 '16 at 14:15
@amiraayadi .. no pblm...Please, can you edit your question with modified code and new Error message (Complete error message) ? – Iron Fist Jun 28 '16 at 14:17
@amiraayadi...I've updated my answer, please check the modification the `SQLCommand` and `cursor.execute` expressions – Iron Fist Jun 28 '16 at 14:56
It's wooooooork thank you sooo much !!! I saw you are from Algiers, I am too !! saha ftorek if you do ramadan :) – blabla Jun 28 '16 at 15:45
Saha ftourek :)...yea I knew it...that's why I kept helping you...but bear in your mind...that this is not a definitive solution...as you are now vulnerable to what is know as "SQL injections" .. will look more into it and hit back to you. – Iron Fist Jun 28 '16 at 16:39
thanks :) Little question if I had to change my sqlcommand like this : SQLCommand = "select count(*) from PRODUCTION where ID_TV IN ({}) and date=? and base=? " How can I change the executeCommand ? That will save me some execution time... – blabla Jun 29 '16 at 07:45
@amiraayadi .. I've updated my answer with the preferred and safe method. – Iron Fist Jun 29 '16 at 14:03
Hi! I couldn't answering earlier sorry, I had no access to the database ! i just try it and it didn't work here the error message: 'The SQL contains 1 parameter markers, but 3 parameters were supplied' i tried to change this "cursor.execute(SQLCommand, tuple(log))" to this "cursor.execute(SQLCommand, (tuple(log),))" (according to the pyodbc documentation) and here the Error message : 'TypeError: 'type' object is not subscriptable' but am in an internship and next week I will not have the right to enter the database thank you for all the time you spend helping me ! – blabla Jul 12 '16 at 12:52

TypeError: 'type' object is not subscriptable with pypyodbc (Python)

1 Answers1