Wsrtjtyk

I have been using psycopg2 to manage items in my PostgreSQL database. Recently someone suggested that I could improve my database transactions by using asyncio and asyncpg in my code. I have looked around Stack Overflow and read though the documentation for examples. I have been able to create tables and insert records, but I haven't been able to get the execution feedback that I desire.

For example in my psycopg2 code, I can verify that a table exists or doesn't exist prior to inserting records.

def table_exists(self, verify_table_existence, name):

    '''Verifies the existence of a table within the PostgreSQL database'''

    try:

        self.cursor.execute(verify_table_existence, name)



        answer = self.cursor.fetchone()[0]



        if answer == True:

            print('The table - {} - exists'.format(name))

            return True

        else:

            print ('The table - {} - does NOT exist'.format(name))

            return False



    except Exception as error:



        logger.info('An error has occurred while trying to verify the existence of the table {}'.format(name))

        logger.info('Error message: {}').format(error)

        sys.exit(1)

I haven't been able to get the same feedback using asyncpg. How do I accomplish this?

import asyncpg

import asyncio



async def main():

conn = await asyncpg.connect('postgresql://postgres:mypassword@localhost:5432/mydatabase')



answer = await conn.fetch('''

SELECT EXISTS (

SELECT 1

FROM   pg_tables

WHERE  schemaname = 'public'

AND    tablename = 'test01'

); ''')



await conn.close()



#####################

# the fetch returns

# [<Record exists=True>]

# but prints 'The table does NOT exist'

#####################



if answer == True:

    print('The table exists')   

else:

    print('The table does NOT exist')





asyncio.get_event_loop().run_until_complete(main())

You used fetchone()[0] with psycopg2, but just fetch(...) with asyncpg. The former will retrieve the first column of the first row, while the latter will retrieve a whole list of rows. Being a list, it doesn't compare as equal to True.

To fetch a single value from a single row, use something like answer = await conn.fetchval(...).