Creating an Sqlalchemy table within a row to save chess move set

Question

I have a table of games and I want to save the moves made in each game within this table. The only way I can think of doing this(without using pickles) is to have another table for each game to save the moves set. My problem is that I'm not sure how to do that within Sqlalchemy and I could not find an answer elsewhere. Am I forced to use pickle types or is there a better way I'm not seeing?

Using Python 3.8.5 and the latest version of Sqlalchemy.

import datetime
from sqlalchemy import Column, DateTime, Integer, String, Date, ForeignKey, Float, Boolean, DateTime, PickleType
from sqlalchemy.ext.declarative import declarative_base
from flask_login import UserMixin


Base = declarative_base()


class Move(Base):
    __tablename__ = 'moves'
    id = Column(Integer, primary_key = True)
    move = Column(String)
    start_time = Column(Integer)
    end_time = Column(Integer)
    white = Column(Boolean)
    

class Game(Base):
    __tablename__ = 'games'
    id = Column(Integer, primary_key = True)
    white_id = Column(Integer)
    black_id = Column(Integer)
    board = Column(PickleType)
    move_table_name = Column(String)


class User(Base , UserMixin):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String(15), unique=True)
    email = Column(String(50), unique=True)
    password = Column(String(80))

This(^^^) is my database mode.

score 0 · Accepted Answer · answered Mar 29 '21 at 08:27

This is a question more about the basic database design. You could think how to design this kind of requirements with just database without sqlalchemy? Relationships between tables, here Game->Move, is a one-to-many relationship which should be declared in the database table also. See https://database.guide/the-3-types-of-relationships-in-database-design.

With SqlAlchemy relationships can be designed as follows:

class Move(Base):
    __tablename__ = 'moves'
    id = Column(Integer, primary_key = True)
    game_id = Column(Integer, ForeignKey('games.id'))
    move = Column(String)
    start_time = Column(Integer)
    end_time = Column(Integer)
    white = Column(Boolean)
    

class Game(Base):
    __tablename__ = 'games'
    id = Column(Integer, primary_key = True)
    white_id = Column(Integer, ForeignKey('users.id'))
    black_id = Column(Integer, ForeignKey('users.id'))
    board = Column(PickleType)

    white = relationship("User", foreign_keys=[white_id])
    black = relationship("User", foreign_keys=[black_id])

See more info: https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html

Creating an Sqlalchemy table within a row to save chess move set

1 Answers1