CODEWITHSUNDEEP
pythonsqloracle-databaseinsertcx-oracle
ashp
ashp

Reputation: 53

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:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.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:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.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

Upvotes: 0

Views: 628

Answers (2)

MT0
MT0

Reputation: 167972

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.

Upvotes: 1

astentx
astentx

Reputation: 6751

It shows you exact line: columns = [col_desc[0] for col_desc in cursor.description]. The only iterable here is cursor.description. If you check the documentation page, you'll find:

This attribute will be None for operations that do not return rows or if the cursor has not had an operation invoked via the execute() method yet.

INSERT does not return rows.

Upvotes: 0

Related Questions