amira ayadi amira ayadi - 5 months ago 47
Python Question

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

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

Answer

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)))