supleiades commented 2 years ago

issue

https://github.com/mo9mo9study/discord.CodeWarehouse/issues/124

supleiades commented 2 years ago

カラム達・ギルドID ・メンバーID ・呼び名・性別・ツイッターID ・得意分野（specialty）・今までの学習・今後の学習

supleiades commented 2 years ago

guild_id
member_id
nickname
sex
twitter_id
specialty
before_study
after_study

supleiades commented 2 years ago

class Selfintroduction(DBBaseMixin, Base):
    guild_id = Column(String(20), unique=False)
    member_id = Column(String(20), unique=True, primary_key=True)
    nickname = Column(String(50))
    sex = Column(String(20))
    twitter_id = Column(String(50))
    specialty = Column(Text)
    before_study = Column(Text)
    after_study = Column(Text)

    def __init__(self,
                 guild_id=None,
                 member_id=None,
                 nickname=None,
                 sex=None,
                 twitter_id=None,
                 specialty=None,
                 before_study=None,
                 after_study=None):
        self.guild_id = guild_id
        self.member_id = member_id
        self.nickname = nickname
        self.sex = sex
        self.twitter_id = twitter_id
        self.specialty = specialty
        self.before_study = before_study
        self.after_study = after_study

supleiades commented 2 years ago

性別のカラム名をsexの文字列のところを、「gender」に変更したほうがよいと思い変更する生物的な性別でなく、このコミュニティ上の性別なのでこちらの方が適してるかなと思い。参考ページ（sexとgender）

supleiades commented 2 years ago

自己紹介のテーブルのguild.idとmember.idの2つにprimaryキー付けるこのBOTはひとつのギルドでしか使わないのでguild.idに主キーを付与する必要は無いが、今後拡張することを考えたりした際のためと、ここに主キーを入れることにコストやデメリットが無いため

supleiades commented 2 years ago

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'Selfintroduction' is not defined
>>> engine = get_db_engine()
>>> Selfintroduction.__table__.drop(engine)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'Selfintroduction' is not defined
>>> Selfintroduction.__table__.drop(engine)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'Selfintroduction' is not defined
>>> from mo9mo9db.dbtables import Selfintroduction
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/mo9mo9db/dbtables.py", line 102, in <module>
    class Membersexperience(DBBaseMixin, Base):
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/sqlalchemy/ext/declarative/api.py", line 76, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 131, in _as_declarative
    _MapperConfig.setup_mapping(cls, classname, dict_)
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 160, in setup_mapping
    cfg_cls(cls_, classname, dict_)
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 194, in __init__
    self._early_mapping()
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 199, in _early_mapping
    self.map()
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 695, in map
    self.cls.__mapper__ = mp_ = mapper_cls(
  File "<string>", line 2, in mapper
  File "<string>", line 2, in __init__
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 139, in warned
    return fn(*args, **kwargs)
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 723, in __init__
    self._configure_pks()
  File "/home/centos/work/discord.selfIntroduction-datadump/venv/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py", line 1409, in _configure_pks
    raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Mapper mapped class Membersexperience->membersexperience could not assemble any primary key columns for mapped table 'membersexperience'

supleiades commented 2 years ago

「現在使用していないテーブルがカラムidが存在しない」とエラー吐いてる問題を、不要なテーブルを削除することで解決

mysql> SHOW COLUMNS FROM selfintroduction;
+--------------+-------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type        | Null | Key | Default           | Extra                                         |
+--------------+-------------+------+-----+-------------------+-----------------------------------------------+
| guild_id     | varchar(20) | NO   | PRI | NULL              |                                               |
| member_id    | varchar(20) | NO   | PRI | NULL              |                                               |
| nickname     | varchar(50) | YES  |     | NULL              |                                               |
| gender       | varchar(20) | YES  |     | NULL              |                                               |
| twitter_id   | varchar(50) | YES  |     | NULL              |                                               |
| specialty    | text        | YES  |     | NULL              |                                               |
| before_study | text        | YES  |     | NULL              |                                               |
| after_study  | text        | YES  |     | NULL              |                                               |
| sendmsg_id   | varchar(20) | YES  |     | NULL              |                                               |
| mod_column   | varchar(20) | YES  |     | NULL              |                                               |
| created_at   | datetime    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at   | datetime    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+-------------+------+-----+-------------------+-----------------------------------------------+

supleiades commented 2 years ago

このテーブルの作成文

CREATE TABLE `selfintroduction` (
  `guild_id` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `member_id` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `nickname` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `gender` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `twitter_id` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `specialty` text COLLATE utf8mb4_bin,
  `before_study` text COLLATE utf8mb4_bin,
  `after_study` text COLLATE utf8mb4_bin,
  `sendmsg_id` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `mod_column` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`guild_id`,`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC |

supleiades commented 2 years ago

テスト環境で動かしてみて

Exception has occurred: TypeError
db_insert_selfintroduction() missing 1 required positional argument: 'member'
  File "/home/centos/work/discord.CodeWarehouse/Cogs/Managements/selfIntroduction.py", line 79, in on_member_join
    await self.db_insert_selfintroduction(member)
  File "/home/centos/work/discord.CodeWarehouse/tmp-run.py", line 18, in <module>
    bot.run(TOKEN)

これには on_readyにsession = Selfintroduction.session()を加える

mo9mo9study / discord.db

自己紹介用のテーブル作成と操作用のモジュール作成 #4

issue