Error when using Pandas to write SQL to Oracle: TypeError: expecting string, unicode or buffer object

Question

Error when using Pandas to write SQL to Oracle: TypeError: expecting string, unicode or buffer object while trying to use:

Pandas v 0.15.2

SqlAlchemy v 0.9.8

merged.to_sql('name_of_table', ora, schema='schema', if_exists='append', index=False)

Here is my engine creation string:

ora = create_engine('oracle://name:pass@site.domain.com:1521/instance')

I had, had an issue before here on the site, but that was fixed with the help of Joris. After that was corrected it worked once and then started to prompt the error above.

I have tried this issue, and this one, but to no avail.

Here is the full error:

merged.to_sql('name_of_table', ora, schema='schema', if_exists='append', index=False)Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/dist-packages/pandas/core/generic.py", line 966, in to_sql
dtype=dtype)
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 538, in to_sql
chunksize=chunksize, dtype=dtype)
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1172, in to_sql
table.insert(chunksize)
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 717, in insert
self._execute_insert(conn, keys, chunk_iter)
File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 692, in _execute_insert
conn.execute(self.insert_statement(), data)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 729, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1162, in _handle_dbapi_exception
util.reraise(*exc_info)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 928, in _execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/dialects/oracle/cx_oracle.py", line 941, in do_executemany
cursor.executemany(statement, parameters)
TypeError: expecting string, unicode or buffer object

Funny enough, if I write it to a csv, then read the csv back into a new dataframe, it will begin write that to Oracle.

Example:

merged.to_csv('name.csv', sep=',', index=False, dtype=object)
merged1 = pd.read_csv('name.csv', dtype=object)

Then it will get further,but a new error pops up: sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00932: inconsistent datatypes: expected NUMBER got CLOB

Any help is appreciated. Thanks!

Answer 1

Answering my own question, even though it is a little late. This revolved around being a dtype issue between the database and the data frame. Resolving the data frame to similar dtypes as the table allowed it to write successfully.

Thanks again @Joris