python - Python pd.read_sql 数据帧 KeyError

Question

我有一个 sql 查询来提取联系人记录。我正在遍历每条记录并将值分配给python中的变量。它适用于除 HMid 之外的所有值变量对。我无法为我的生活弄清楚为什么。当我打印 Updatedata 时，HMid 显示为像这样的数字“106594451”。非常感谢任何指导。

    updateSQL = """ Select c.mobilephone
        ,l.HMid         
        ,[firstname]
        ,[lastname]
        ,fullname
        ,[emailaddress1]     
    from contact c where and \ksl_communityid = '%s'
    

                        """  % (CommunityID)        
  
Updatedata = pd.read_sql(updateSQL, cnxnCS1)
print(Updatedata)


for i in range(len(Updatedata)):
    mobilephone = data.loc[i,'mobilephone']
    firstname = data.loc[i,'firstname']
    lastname = data.loc[i,'lastname']
    fullname = data.loc[i,'fullname']
    emailaddress1 = data.loc[i,'emailaddress1']
    HMid= data.loc[i,'HMid']

这是我得到的错误：

    ---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)get_loc(self, key, method, tolerance)    2645             try:
-> 2646                 return self._engine.get_loc(key)    2647             except KeyError:

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'HMid'

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last) <ipython-input-143-13351626f5cd> in <module>
     53     fullname = data.loc[i,'fullname']
     54     emailaddress1 = data.loc[i,'emailaddress1']
---> 55     HMid = data.loc[i,'HMid']
     56 
     57     print(HMid)

~\Anaconda3\lib\site-packages\pandas\core\indexing.py in
__getitem__(self, key)    1759                 except (KeyError, IndexError, AttributeError):    1760                     pass
-> 1761             return self._getitem_tuple(key)    1762         else:    1763             # we by definition only have the 0th axis

~\Anaconda3\lib\site-packages\pandas\core\indexing.py in
_getitem_tuple(self, tup)    1269     def _getitem_tuple(self, tup: Tuple):    1270         try:
-> 1271             return self._getitem_lowerdim(tup)    1272         except IndexingError:    1273             pass

~\Anaconda3\lib\site-packages\pandas\core\indexing.py in
_getitem_lowerdim(self, tup)    1418                     return section    1419                 # This is an elided recursive call to iloc/loc/etc'
-> 1420                 return getattr(section, self.name)[new_key]    1421     1422         raise IndexingError("not applicable")

~\Anaconda3\lib\site-packages\pandas\core\indexing.py in
__getitem__(self, key)    1765     1766             maybe_callable = com.apply_if_callable(key, self.obj)
-> 1767             return self._getitem_axis(maybe_callable, axis=axis)    1768     1769     def _is_scalar_access(self, key: Tuple):

~\Anaconda3\lib\site-packages\pandas\core\indexing.py in
_getitem_axis(self, key, axis)    1962         # fall thru to straight lookup    1963         self._validate_key(key, axis)
-> 1964         return self._get_label(key, axis=axis)    1965     1966 

~\Anaconda3\lib\site-packages\pandas\core\indexing.py in
_get_label(self, label, axis)
    618             # but will fail when the index is not present
    619             # see GH5667
--> 620             return self.obj._xs(label, axis=axis)
    621         elif isinstance(label, tuple) and isinstance(label[axis], slice):
    622             raise IndexingError("no slices here, handle elsewhere")

~\Anaconda3\lib\site-packages\pandas\core\generic.py in xs(self, key, axis, level, drop_level)    3535             loc, new_index = self.index.get_loc_level(key, drop_level=drop_level)    3536         else:
-> 3537             loc = self.index.get_loc(key)    3538     3539             if isinstance(loc, np.ndarray):

~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)    2646                 return self._engine.get_loc(key)    2647             except KeyError:
-> 2648                 return self._engine.get_loc(self._maybe_cast_indexer(key))    2649         indexer = self.get_indexer([key], method=method, tolerance=tolerance)  2650         if indexer.ndim > 1 or indexer.size > 1:

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'HMid'

标签： pythonsql