Passing na_values and quoting doesn't work.
- Dominant language
- Python
- Stars
- 1k
- Forks
- 131
- PR merge metrics
- No merged PRs in 30d
Description
I am trying to read from a tab delimited file and setting na_values and quoting aren't working. I used the same options with pandas and it creates the dataframe correctly.
```
t = odo(file, "postgresql://(removed)",
encoding='utf-8',
delimiter='\t',
na_values="\\N",
quoting=3,
dshape=ds)
```
I was getting one warning when I running it, I fixed that as shown in #546 to make sure it wan't related and no change.
```
/usr/local/lib/python3.6/dist-packages/odo/backends/pandas.py:94: FutureWarning: pandas.tslib is deprecated and will be removed in a future version.
You can access NaTType as type(pandas.NaT)
@convert.register((pd.Timestamp, pd.Timedelta), (pd.tslib.NaTType, type(None)))
```
If i run as is I get an error because of quotes in my data messing up the columns. I noticed in the error message some of the options specified don't seem to be set in parameters, `quoting` doesn't show up at all and `na_values` shows up but isn't set to what I specified.
```
Traceback (most recent call last):
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute
cursor.execute(statement, parameters)
psycopg2.DataError: invalid input syntax for type boolean: "1996"
CONTEXT: COPY titlebasics, line 679626, column isAdult: "1996"
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "./makedb.py", line 42, in
main()
File "./makedb.py", line 36, in main
convert_csv()
File "./makedb.py", line 30, in convert_csv
dshape=ds)
File "/usr/local/lib/python3.6/dist-packages/odo/odo.py", line 91, in odo
return into(target, source, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/multipledispatch/dispatcher.py", line 164, in __call__
return func(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/odo/into.py", line 43, in wrapped
return f(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/odo/into.py", line 149, in into_string_string
return into(a, resource(b, **kwargs), **kwargs)
File "/usr/local/lib/python3.6/dist-packages/multipledispatch/dispatcher.py", line 164, in __call__
return func(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/odo/into.py", line 43, in wrapped
return f(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/odo/into.py", line 143, in into_string
return into(a, b, dshape=dshape, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/multipledispatch/dispatcher.py", line 164, in __call__
return func(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/odo/into.py", line 43, in wrapped
return f(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/odo/into.py", line 131, in into_object
return append(target, source, dshape=dshape, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/multipledispatch/dispatcher.py", line 164, in __call__
return func(*args, **kwargs)
File "/usr/local/lib/python3.6/dist-packages/odo/backends/sql_csv.py", line 233, in append_csv_to_sql_table
conn.execute(stmt)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 948, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
context)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for type boolean: "1996"
CONTEXT: COPY titlebasics, line 679626, column isAdult: "1996"
[SQL: '\n COPY titlebasics FROM %(path)s (\n FORMAT CSV,\n DELIMITER %(delimiter)s,\n NULL %(na_value)s,\n QUOTE %(quotechar)s,\n ESCAPE %(escapechar)s,\n HEADER %(header)s,\n ENCODING %(encoding)s\n )\n '] [parameters: {'path': (removed), 'delimiter': '\t', 'na_value': '', 'quotechar': '"', 'escapechar': '\\', 'header': True, 'encoding': 'utf-8'}] (Background on this error at: http://sqlalche.me/e/9h9h)
```
I can sidestep that by setting `quotechar='|',` which doesn't exist in my data and stops the quotes from breaking it but obviously I would rather use `quoting=QUOTE_NONE` so that it doesn't end up breaking if i end up with a `|`. But `na_values` still doesn't work.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.