I am retrieving a list of table names using pandas.read_sql and then trying to use a "for" loop to drop tables from the retrieved list. However, I am getting a 'NoneType' object is not iterable error (although the list is not empty).
print(type(out_tables_list))
<class 'list'>
print(out_tables_list is None)
False
tables = pd.read_sql("""
SHOW TABLES IN P_SMO_INP_T
""", con=connection)
tableName = tables.tableName
out_tables = tableName[tableName.str.contains('_sg_')]
out_tables_list = out_tables.to_list()
for name in out_tables_list:
pd.read_sql("DROP TABLE P_SMO_INP_T.{}".format(name), con=connection)
Can you please help me figure out what's my mistake? Thank you in advance!
LE: See below the full output I am receiving:
TypeError Traceback (most recent call last)
<ipython-input-46-1e7a629dac7d> in <module>
1 for name in out_tables_list:
----> 2 pd.read_sql("DROP TABLE P_SMO_INP_T.{}".format(name), con=connection)
c:\users\sgulunga\appdata\local\programs\python\python38-32\lib\site-packages\pandas\io\sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
481
482 if isinstance(pandas_sql, SQLiteDatabase):
--> 483 return pandas_sql.read_query(
484 sql,
485 index_col=index_col,
c:\users\sgulunga\appdata\local\programs\python\python38-32\lib\site-packages\pandas\io\sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
1726 args = _convert_params(sql, params)
1727 cursor = self.execute(*args)
-> 1728 columns = [col_desc[0] for col_desc in cursor.description]
1729
1730 if chunksize is not None:
TypeError: 'NoneType' object is not iterable
A DROP query returns nothing, so None, and so pandas can't iterate on that to build a dataframe.
See How do I drop a table in SQLAlchemy when I don't have a table object?
Note that when debugging be careful about the line raising the error, you said "although the list is not empty" but the line raising the error doesn't use the list