OperationalError | Help please

[Dynamixus]

I am trying to integrate your VoteBot commands to my personal server bot. Upon running though, I encounter this error:

Seems the error is on db. I have no prior experience with handling databases so I don't quite know what to ask or what to Google for. What can I do please or if you can't help personally, could you maybe link some resources that might help me out?

[ericthelemur]

It looks like either you haven't set the database URL correctly (set the DATABASE_URL environment variable) or the database hasn't been configured correctly. How have you set up the database so far?

[ericthelemur]

Also the update branch has a nearly complete rewrite to use discord buttons, and a few more options for dB, etc. though it was never quite finished

[Dynamixus]

Yeah I don't quite understand how to set it all up, I installed PostgreSQL, but I don't know where to go from there. My original bot is using aiosqlite for use with async commands and stuff so I thought I could do it by converting all your database related code to aiosqlite, but after so long working I feel like it won't work anyway.

Is there some way for me to just run the database locally instead? Like how asyncio or aiosqlite does it.
This is a sample of my commands currently in my bot using the aiosqlite, I didn't need to configure anything else with the database.

import os
import aiosqlite

DATABASE_PATH = f"{os.path.realpath(os.path.dirname(__file__))}/../database/database.db"

async def add_warn(user_id: int, server_id: int, moderator_id: int, reason: str) -> int:
    """
    This function will add a warn to the database.

    :param user_id: The ID of the user that should be warned.
    :param reason: The reason why the user should be warned.
    """
    async with aiosqlite.connect(DATABASE_PATH) as db:
        rows = await db.execute(
            "SELECT id FROM warns WHERE user_id=? AND server_id=? ORDER BY id DESC LIMIT 1",
            (
                user_id,
                server_id,
            ),
        )
        async with rows as cursor:
            result = await cursor.fetchone()
            warn_id = result[0] + 1 if result is not None else 1
            await db.execute(
                "INSERT INTO warns(id, user_id, server_id, moderator_id, reason) VALUES (?, ?, ?, ?, ?)",
                (
                    warn_id,
                    user_id,
                    server_id,
                    moderator_id,
                    reason,
                ),
            )
            await db.commit()
            return warn_id

Also here's an example of how I tried convert your existing command to the same format.

import os
import aiosqlite

DATABASE_PATH = f"{os.path.realpath(os.path.dirname(__file__))}/../database/database.db"

async def addVote(creator: discord.User, question: str, options: list[str], limit: int, guild: discord.Guild, channel: discord.TextChannel, stage: int = 0, type: int = 1, num_win: int = 1, title_pre: str = "Poll") -> tuple[int, str]:
	async with aiosqlite.connect(DATABASE_PATH) as db:
		rows = await db.execute(
			"INSERT INTO Votes (CreatorID, Question, VoteLimit, GuildID, ChannelID, PollStage, Type, NumWinners) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING VoteID;",
			(
				creator.id, question, limit, guild.id, channel.id, stage, type, num_win
				)
			)
		async with rows as cursor:
			result = await cursor.fetchone()

		title = f"{title_pre} `{vid}`: " + question
		await db.execute(
			"UPDATE Votes SET Question = %s WHERE VoteID = %s", 
			(
				title, vid
				)
			)

		await db.executemany(
			"INSERT INTO Options (VoteID, OptionNumb, Prompt) VALUES (%s, %s, %s);", 
			(
				(vid, i, p) for i, p in enumerate(options)
				)
			)

		return result, title

[ericthelemur]

Unfortunately this version is hard coded to use postgres, the rewrite uses SQL alchemy so can be used for anything.

Your attempt looks reasonable, though I can't say without exact details (make sure you remove/replace all the psycopg stuff, that's postgres only)