iterate a dataframe

Viewed 33

I'm trying to iterate a dataframe to call queries in mongodb from a list and save each query in a csv file. I have the connection with no errors, but when I iterate it just creates the frist file (0.csv) and I have an error for the second row of the dataframe.

This is my code:

    sql = [
        ('tran','transactions',{"den": "00100002773060"}),
        ('tran','Data',{'name': 'john'}), 
    ]
    
    df = pd.DataFrame(sql, columns = ["database", "entity", "sql"])
   
   for i in range(len(df)):    
      database = df.iloc[i]["database"]
      entity=df.iloc[i]["entity"]
      myquery=df.iloc[i]["sql"]
    
      collection = client[database][entity]

      try:
          mydoc = list(collection.find(myquery))
          if len(mydoc) > 0:        
              df = pd.DataFrame(mydoc)
              df.pop("_id")        
              df.to_csv(str(i) + '.csv')
              print("file saved")
      except:
          print("error on file")

and this the error

Traceback (most recent call last):
  File "/home/r/Desktop/table_csv/entorno_virtual/lib/python3.8/site-packages/pandas/core/indexes/base.py", line 3629, in get_loc
    return self._engine.get_loc(casted_key)
  File "pandas/_libs/index.pyx", line 136, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/index.pyx", line 163, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/hashtable_class_helper.pxi", line 5198, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas/_libs/hashtable_class_helper.pxi", line 5206, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'database'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "getSql.py", line 12, in <module>
    database = df.iloc[i]["database"]
  File "/home/r/Desktop/table_csv/entorno_virtual/lib/python3.8/site-packages/pandas/core/series.py", line 958, in __getitem__
    return self._get_value(key)
  File "/home/r/Desktop/table_csv/entorno_virtual/lib/python3.8/site-packages/pandas/core/series.py", line 1069, in _get_value
    loc = self.index.get_loc(label)
  File "/home/r/Desktop/table_csv/entorno_virtual/lib/python3.8/site-packages/pandas/core/indexes/base.py", line 3631, in get_loc
    raise KeyError(key) from err
KeyError: 'database'
1 Answers

from what I can see here you are changing your df variable here

          df = pd.DataFrame(mydoc)

probably just rename it

Related