Using cx_Oracle for Python and receiving TypeError: ‘NoneType’ object is not iterable when inserting data to table

I am trying to insert data into a Oracle database. I have to use a proxy to have insert permissions. Below is the code that I run, but I’m getting the error ‘TypeError: ‘NoneType’ object is not iterable’ at the line ”’,conn).

I’m not sure where to go from here. Does anyone know what I’m doing wrong? Full error message is below the query code.

dsn_tns = cx_Oracle.makedsn('hostname', 'port', service_name='service_name') 
conn = cx_Oracle.connect(user="username[proxy]", password=r'password', dsn=dsn_tns)

sqlQuery = pd.read_sql_query(
'''insert into intl.persons1(first_name, last_name) values ('sunshines','heffleflopper')
''', conn) 

df = pd.DataFrame(sqlQuery, columns = ['PERSON_ID','FIRST_NAME','LAST_NAME'])
print(df)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-36-454fb5b824e6> in <module>()
      4 sqlQuery = pd.read_sql_query(
      5 '''insert into intl.persons1(first_name, last_name) values ('sunshines','heffleflopper')
----> 6 ''', conn) 
      7 
      8 dfPOFullHistory = pd.DataFrame(sqlQuery, columns = ['PERSON_ID','FIRST_NAME','LAST_NAME'])

C:ProgramDataAnaconda3libsite-packagespandasiosql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize)
    312     return pandas_sql.read_query(
    313         sql, index_col=index_col, params=params, coerce_float=coerce_float,
--> 314         parse_dates=parse_dates, chunksize=chunksize)
    315 
    316 

C:ProgramDataAnaconda3libsite-packagespandasiosql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
   1434         args = _convert_params(sql, params)
   1435         cursor = self.execute(*args)
-> 1436         columns = [col_desc[0] for col_desc in cursor.description]
   1437 
   1438         if chunksize is not None:

TypeError: 'NoneType' object is not iterable

Answer

An INSERT statement does not return a result set.

You are using pandas.read_sql_query which:

Returns a DataFrame corresponding to the result set of the query string.

However, since your query is not returning a result set then this is the reason the error is generated; you are using the wrong method to insert values.

You appear to need to insert using cx_oracle rather than trying to do it through pandas.

Advertisement

Answer