Search code examples
pythonsqlpostgresqlasyncpg

Change value from SQL command output to match python syntax


I'm a newbie regarding SQL and PostgreSQL and database management, but I need a database for a discord bot I'm making. I'm using asyncpg to manage a database with python

Now, let's say I have a command which has to find if username already exists in that database:

try:
            with open("creds.json", "r") as file:  
                creds = json.load(file)
            print("\n" + "Connecting to database")    
            host = creds["DB_HOST"]
            user = creds["DB_USER"]
            password = creds["DB_TOKEN"]
            database = creds["DB_NAME"]
            conn = await asyncpg.connect(host=host, user=user, password=password, database=database)
            print("\n"+ f"PostgreSQL connection to database stablished successfully")
            exists = await conn.fetch('''
SELECT EXISTS (
SELECT 1 
FROM table 
WHERE username=($1)); 
''', str(discordusername#1234)) 
            await conn.close()  
            print("Query finished, connection to database closed")        
            print(exists)

It either returns True or False in this form [<Record exists=False>] or [<Record exists=True>], so to check if its true or false I have to convert the 'exists' to a string and then compare it to either [<Record exists=False>] or [<Record exists=False>] as a string:

if str(exists) == "[<Record exists=False>]":
    ...
elif str(exists) == "[<Record exists=True>]"
    ...

Is there anyway to 'convert' [<Record exists=False>] to a plain False and [<Record exists=False>] to a plain True that Python can directly understand?

same goes when getting a value from a table, say, and int that have to be addressed the same way, returning, for example,

[<Record rowname=5>]

Solution

  • fetch() returns list of records, one record for each returned row.

        result = await conn.fetch(my_query)
    
        # print the value of column 'exists' of the first row (number 0)
        print(result[0]['exists'])
        
        # print the value of the first column of the first row
        print(result[0][0])
    

    Read more about asyncpg.Record.