Example code for connecting to and using a postgres database using discord.py and asyncpg.

example_db.py

# asyncpg docs: https://magicstack.github.io/asyncpg/current/

# This uses discord.py rewrite branch and .format(). If you are using the async branch of discord.py, it shouldn't matter much
# as only 'await ctx.send()' is something you should need to change. If you are using python 3.6+, you can use f strings as opposed to
# .format() for increased efficiency.

import discord
from discord.ext import commands
import asyncio
import asyncpg

async def run():
    description = "A bot written in Python that uses asyncpg to connect to a postgreSQL database."

    # NOTE: 127.0.0.1 is the loopback address. If your db is running on the same machine as the code, this address will work
    credentials = {"user": "USERNAME", "password": "PASSWORD", "database": "DATABSE", "host": "127.0.0.1"}
    db = await asyncpg.create_pool(**credentials)

    # Example create table code, you'll probably change it to suit you
    await db.execute("CREATE TABLE IF NOT EXISTS users(id bigint PRIMARY KEY, data text);")

    bot = Bot(description=description, db=db)
    try:
        await bot.start(config.token)
    except KeyboardInterrupt:
        await db.close()
        await bot.logout()

class Bot(commands.Bot):
    def __init__(self, **kwargs):
        super().__init__(
            description=kwargs.pop("description"),
            command_prefix="?"
        )

        self.db = kwargs.pop("db")

    async def on_ready(self):
        # .format() is for the lazy people who aren't on 3.6+
        print("Username: {0}\nID: {0.id}".format(self.user))

    # Example commands, don't use them
    # Don't even use this format, this is not an example of how to make commands.
    @commands.command()
    async def query(ctx):
        query = "SELECT * FROM users WHERE id = $1;"

        # This returns a asyncpg.Record object, which is similar to a dict
        row = await bot.db.fetchrow(query, ctx.author.id)
        await ctx.send("{}: {}".format(row["id"], row["data"]))

    @commands.command()
    async def update(ctx, *, new_data: str):
        # Once the code exits the transaction block, changes made in the block are committed to the db

        connection = await bot.db.acquire()
        async with connection.transaction():
            query = "UPDATE users SET data = $1 WHERE id = $2"
            await bot.db.execute(query, new_data, ctx.author.id)
        await bot.db.release(connection)

        await ctx.send("NEW:\n{}: {}".format(ctx.author.id, new_data))

loop = asyncio.get_event_loop()
loop.run_until_complete(run())

you shouldn't be using .format. btw why are you using that weird .commit thing in the update command? can't you just do bot.db.execute?

you shouldn't be using .format.

From the comment at the top of the file: "If you are using python 3.6+, you can use f strings as opposed to .format() for increased efficiency."

btw why are you using that weird .commit thing in the update command? can't you just do bot.db.execute?

Probably. It has been so long since I touched a Discord bot or Postgres code, and basically 5 years since I touched this gist, I'm not really sure. I think if you want to roll back SQL database operations you need to wrap them in a transaction. Also if you have multiple operations that should be performed as a unit they should be wrapped in a transaction together, so if one of them fails the whole transaction is thrown out and you are not left with in-between state that your application doesn't want to handle. There's only one SQL action there, so maybe I just put it there as an example of how to do transactions.

I also just noticed the person who asked about bot being used in places it's not declared. Yeah, this code probably doesn't actually work, huh. Probably supposed to use the self parameter in those commands, then access via self? Or maybe via ctx?

@Ender2K89 The old way for doing DB commits was to first stage them then commit changes. Not sure why, not sure if its changed, but that is how it used to work.