Copy_from not working in aiopg

Here's a query that I'm trying to execute in aiopg and the data. I have no idea why it fails.

The script works fine, except the table has no rows in it.

If I comment the drop table line, it raises an exception (table already exists). If I drop the teable in PgAdmin, the script creates it normally. Question is why no rows are inserted. I tried both with and without sep=',', no difference.

I tried turning log level in postgresql.conf to notice, still no effect. Nothing is logged when the queries are executed.

cols = ['geopos', 'build_year', 'serial', 'floors_max', 'address']
buff = io.StringIO("""
geopos,series,build_year,address,floors_max
POINT (37.954441 55.725681),ааацуке544,1900,"г. Москва, г. Зеленоград, д. 1306, к. 1",321
POINT (37.657889 55.834376),Индивидуальный,2014,"г. Москва, пр-кт. Мира, д. 188 Б, к. 1",58
POINT (37.527903 55.723237),Индивидуальный,2011,"г. Москва, ул. Мосфильмовская, д. 8",53
POINT (37.511625 55.71232),индивидуальный,1959,"г. Москва, ул. Мосфильмовская, д. 33",1960
POINT (37.520671 55.79848),Индивидуальный,2006,"г. Москва, пер. Чапаевский, д. 3",57
POINT (37.258022 55.964569),,,"обл. Московская, г. Химки, мкр. Сходня, ул. Ленинградская, д. 3",54
POINT (37.427408 55.879187),,,"обл. Московская, г. Химки, ул. Панфилова, д. 15",173"""
)

dsn = 'dbname=wand_driver_dev host=localhost user=culebron password=culebron'

async with aiopg.create_pool(dsn) as pool:
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute('drop table if exists test_table')
            await cur.execute('create table test_table (geopos geometry, series text, build_year integer, address text, floors_max integer)')

            buff.seek(0)
            cur.copy_from(buff, 'test_table', sep=',', columns=cols)
            await cur.execute('select * from test_table')
            print(list(cur.fetchall()))  # prints an empty list: []
            conn.commit()

I tried adding this line before doing queries:

await cur.execute("set log_min_error_statement TO 'debug1';")

still saw nothing. I've set everything to debug1 in postgresql.conf, and saw only this:

culebron@wand_driver_dev STATEMENT:  create table loaded_table (address text, serial text, geopos geometry, build_year integer, floors_max integer)

Probably copy_from works in a different way than execute. But if I make create table statement sync, it fails:

            await cur.execute('drop table if exists test_table')
            cur.execute('create table test_table (geopos geometry, series text, build_year integer, address text, floors_max integer)')
            buff.seek(0)
            cur.copy_from(buff, 'test_table', sep=',', columns=cols)

Postgres driver raises an exception:

psycopg2.ProgrammingError: relation "loaded_table" does not exist
LINE 1: select * from loaded_table

So, it does try to load data into the table.

I wonder if it silently fails to read the CSV format. But have no clue what's wrong.

cur.copy_from is not supported with asynchronous cursors:

@asyncio.coroutine
def copy_from(self, file, table, sep='\t', null='\\N', size=8192,
              columns=None):
    raise psycopg2.ProgrammingError(
        "copy_from cannot be used in asynchronous mode")

To raise the error add an await to your code:

await cur.copy_from(buff, 'test_table', sep=',', columns=cols)

Without an await, you just don't see the error in the main loop.

Instead, use regular psycopg.