Connection.copy_records_to_table does not work with nan values

[midlewis]

• asyncpg version: 0.13.0
• PostgreSQL version: 9.6.2
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: pgAdmin 3
• Python version: 3.6.1
• Platform: linux terminal
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?: n/a
• Can the issue be reproduced under both asyncio and
  uvloop?: dont know

This issue is similar to: #153

I have a basic pandas data frame, one of the columns is a string. I am trying to upload it to postgres using connection.copy_records_to_table. Using the function below:

async def insert_df(data,table_name):
    conn = await asyncpg.connect(user='master', password=pw, database=db, host=host_name)
    result = await conn.copy_records_to_table(table_name, records=data)
    print(result)

When all of the values in the string column have an actual string value it works fine, but when one of the values in the column of strings has a value NaN I get the following error:

asyncio.get_event_loop().run_until_complete(insert_df(data=df,table_name=avl_import))

Traceback (most recent call last):

  File "<ipython-input-11-998c1420b8f7>", line 1, in <module>
    asyncio.get_event_loop().run_until_complete(insert_df(data=df,table_name=avl_import))

  File "/Applications/anaconda/lib/python3.6/asyncio/base_events.py", line 466, in run_until_complete
    return future.result()

  File "<ipython-input-3-c7a216ea9861>", line 13, in insert_df
    result = await conn.copy_records_to_table(table_name, records=tuples)

  File "/Applications/anaconda/lib/python3.6/site-packages/asyncpg/connection.py", line 652, in copy_records_to_table
    copy_stmt, records, intro_ps._state, timeout)

  File "/Applications/anaconda/lib/python3.6/site-packages/asyncpg/connection.py", line 774, in _copy_in_records
    copy_stmt, None, None, records, intro_stmt, timeout)

  File "asyncpg/protocol/protocol.pyx", line 477, in copy_in

  File "asyncpg/protocol/protocol.pyx", line 425, in asyncpg.protocol.protocol.BaseProtocol.copy_in

  File "asyncpg/protocol/codecs/base.pyx", line 161, in asyncpg.protocol.protocol.Codec.encode

  File "asyncpg/protocol/codecs/base.pyx", line 97, in asyncpg.protocol.protocol.Codec.encode_scalar

  File "asyncpg/protocol/codecs/text.pyx", line 29, in asyncpg.protocol.protocol.text_encode

  File "asyncpg/protocol/codecs/text.pyx", line 12, in asyncpg.protocol.protocol.as_pg_string_and_size

TypeError: expected str, got float

Because NaN is of type float, it doesn't work. I have found a workaround to this solution by casting everything in that column to a string, but it would be nice to not have to do that forever.

Let me know if you have any questions, thanks!

[elprans]

Implicit casting of arbitrary data into text is a bad idea that may mask all kinds of bugs. We made a decision not to do that in asyncpg: ccc5f7a. Basically, you need to ensure your data is clean before sending it in.

In other words, this is not a bug, and your workaround is the right way to do it. Performance-wise it shouldn't matter, as long as you cast your series in-place with astype(copy=False), and writing a generic "lax" dataframe insertion wrapper is trivial.