Модуль pymysql выдает ошибку при втором вызове, как решить?

Question

[Shandy]

Вот код:

Код

import discord
import pymysql
import configparser
import asyncio
from discord.ext import commands

config = configparser.ConfigParser()
config.read("config.ini", encoding='utf-8')

con = pymysql.connect(
    host=config["DB"]["server"],
    user=config["DB"]["login"],
    password=config["DB"]["password"],
    database=config["DB"]["database"]
)

def get_prefix(bot, message):
    with con:
        cur = con.cursor()
        cur.execute(f"SELECT prefix FROM simple_voice.server_{message.guild.id};")
        row = cur.fetchone()
        return row

bot: commands.Bot = commands.Bot(command_prefix = get_prefix, instintents = discord.Intents.all())

@bot.event
async def on_ready():
    print(f'Авторизован под {bot.user}')

@bot.event
async def on_guild_join(guild):
    with con:
        cur = con.cursor()
        cur.execute(
f"""CREATE TABLE `simple_voice`.`server_{guild.id}` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `voice_channel` VARCHAR(45) NOT NULL DEFAULT 0,
  `prefix` VARCHAR(45) NOT NULL DEFAULT '/',
  `blocked_commands` VARCHAR(1024) NOT NULL DEFAULT '[]',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
""")

@bot.command()
async def info(ctx):
    await ctx.send('Test!')
    embed = discord.Embed(title='Simple Voice', description='Создание собственных каналов для каждого участника. Редактирование параметров канала через команды и многое другое.', url='https://github.com', colour=0x8691c8)
    embed.set_thumbnail(url='https:///ab1d22e5013aef1fb490a5f1e80c977a.png')
    embed.set_image(url='https://3ce580d24d84b65768beac66bbf12d92.png')
    embed.set_author(name='Shandy', url='https://github.com', icon_url='https:///9de56bb9dc3276a0b7cf678809097521.png')
    embed.set_footer(text=' © 2021')
    await ctx.send(embed=embed)

bot.run(config["Config"]["token"])

Первый раз все нормально, второй раз и далее выдает ошибку:

Ошибка

Ignoring exception in on_message
Traceback (most recent call last):
  File "c:\Users\MrShandy\Documents\GitHub\simple-voice\bot.py", line 20, in get_prefix
    cur.execute(f"SELECT prefix FROM simple_voice.server_{message.guild.id};")
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\pymysql\cursors.py", line 148, in execute
    result = self._query(query)
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\pymysql\cursors.py", line 310, in _query
    conn.query(q)
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\pymysql\connections.py", line 547, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\pymysql\connections.py", line 793, in _execute_command
    raise err.InterfaceError(0, "")
pymysql.err.InterfaceError: (0, '')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 942, in on_message
    await self.process_commands(message)
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 938, in process_commands
    ctx = await self.get_context(message)
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 852, in get_context
    prefix = await self.get_prefix(message)
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 797, in get_prefix
    ret = await discord.utils.maybe_coroutine(prefix, self, message)
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\utils.py", line 329, in maybe_coroutine
    value = f(*args, **kwargs)
  File "c:\Users\MrShandy\Documents\GitHub\simple-voice\bot.py", line 22, in get_prefix
    return row
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\pymysql\connections.py", line 360, in __exit__
    self.close()
  File "C:\Users\MrShandy\AppData\Local\Programs\Python\Python39\lib\site-packages\pymysql\connections.py", line 404, in close
    raise err.Error("Already closed")
pymysql.err.Error: Already closed

Comments

[o5a]

Создавать таблицу на каждый guild_id плохой дизайн. Правильнее делать такое разделение на уровне самой таблицы: т.е. таблица на все гильдии одна, а разделение за счет информации в поле guild_id.
Соответственно в запросах таблица остается одна, а для фильтрации (подзапросы WHERE) используется само поле.

Например, для выборки вместо

cur.execute(f"SELECT prefix FROM simple_voice.server_{message.guild.id};")

будет

cur.execute("SELECT prefix FROM simple_voice.server WHERE guild_id = %s", (message.guild.id, ))

[Shandy]

o5a, спасибо, переделаю

Answer 1

[Shandy]

Решил так:
Вместо

with con:
    cur = con.cursor()
    cur.execute(...)

Сделал так

with con.cursor() as cur:
        cur.execute(...)