Support missing values in Boolean columns

[tanguycdls]

#80
Similar to the above issue, I ran into an issue with missing values in Boolean Columns. When parsing to pandas the conversion fails because we set the dtype to boolean while boolean does not support Na.

pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._read_rows()
pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._convert_column_data()
pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._convert_tokens()
pandas/_libs/parsers.pyx in pandas._libs.parsers.TextReader._convert_with_dtype()
ValueError: Bool column has NA values in column 3

Could we add a try except Value Error around

PyAthena/pyathena/result_set.py

Line 429 in d202135

df = pd.read_csv(io.BytesIO(response['Body'].read()),

If it fails we could raise a warning and try again the DataFrame creation with relaxed constraints:

• Either we follow pandas and convert to object the column involved in the issue (I'm not sure we can detect the faulty column, so we could convert all columns with dtypes that are not Na compatible.)
• Convert booleans (I understand from the PR above that int is already na safe ) to int and warn the user.

If needed I can write a PR once we agree on a solution !

Thanks

[laughingman7743]

I don't usually use Pandas. I don't know what solution is best.

[1] Either we follow pandas and convert to object the column involved in the issue (I'm not sure we can detect the faulty column, so we could convert all columns with dtypes that are not Na compatible.)

[2] Convert booleans (I understand from the PR above that int is already na safe ) to int and warn the user.

Which method do you think is better?

[laughingman7743]

Related: #100

[tanguycdls]

Hi, thanks for the prompt answer ! I did a PR that respects Pandas Type Promotion so bool will be casted to object dtypes and let the user decide what they want to do with it.

[laughingman7743]

Thank you for your PR. I understood what kind of implementation should be done.
I'm thinking of refactoring the Converter class to use it with PandasCursor.
https://github.com/laughingman7743/PyAthena/blob/master/pyathena/converter.py
I plan to try it on a weekend.

[laughingman7743]

I implemented it so that dtype and converter can be customized freely in the following branches.
Please make sure. 🙏
#104