SQLAlchemy test

models.py

from sqlalchemy import create_engine, Column, ForeignKey
from sqlalchemy.dialects.mysql import (
    INTEGER,
    VARCHAR,
    TINYINT,
)
from sqlalchemy.orm import sessionmaker, relation
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Player(Base):
    __tablename__ = 'player'
    __table_args__ = {
        'mysql_engine':'InnoDB',
        'mysql_charset':'utf8',
    }

    id = Column('id', INTEGER(unsigned=True),
        primary_key=True,
        autoincrement=True)
    player_name = Column('player_name', VARCHAR(length=64),
        nullable=False)
    nationality = Column('nationality', VARCHAR(length=32),
        nullable=False)
    club = relation('Club', backref='player', uselist=False)

class Club(Base):
    __tablename__ = 'club'
    __table_args__ = {
        'mysql_engine':'InnoDB',
        'mysql_charset':'utf8',
    }

    club_name = Column('club_name', VARCHAR(length=64),
        primary_key=True,
        autoincrement=False)
    number = Column('number', TINYINT(unsigned=True),
        primary_key=True,
        autoincrement=False)
    country = Column('country', VARCHAR(length=32),
        nullable=False)
    player_id = Column('player_id', INTEGER(unsigned=True),
        ForeignKey('player.id'),
        unique=True,
        nullable=False)

test_models.py

import unittest
from models import Base, Player, Club

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

dsn = 'mysql+pymysql://%s:%s@%s/%s?charset=%s' % ('root', '', 'localhost', 'test', 'utf8')

player_list = [
        ('KAZU', 'Japan'),
        ('Ronaldo', 'Brazil'),
        ('Ronaldo', 'Portugal'),
        ('Diego Armando Maradona', 'Argentine')
]
club_list = [
        ('Verdy Kawasaki', 11, 'Japan', 1),
        ('Real Madrid', 9, 'Spain', 2),
        ('Real Madrid', 7, 'Spain', 3),
        ('Napoli', 10, 'Italy', 4),
]

class TestSQLAlchemy(unittest.TestCase):

    def setUp(self):
        self.engine = create_engine(
            dsn,
            echo=True
        )
        Base.metadata.drop_all(self.engine)
        Base.metadata.create_all(self.engine)
        Session = sessionmaker(bind=self.engine)
        self.session = Session()

    def tearDown(self):
        self.session.commit()
        self.session.close()
        Base.metadata.drop_all(self.engine)

    def test_insert(self):
        for p in player_list:
            player = Player(player_name=p[0], nationality=p[1])
            self.session.add(player)
        for c in club_list:
            club = Club(club_name=c[0], number=c[1], country=c[2], player_id=c[3])
            self.session.add(club)

    def test_insert_2(self):
        for p in player_list:
            player = Player(player_name=p[0], nationality=p[1])
            self.session.add(player)
        self.session.flush()
        for c in club_list:
            club = Club(club_name=c[0], number=c[1], country=c[2], player_id=c[3])
            self.session.add(club)
        self.session.flush()

    def test_insert_with_relation(self):
        for i, p in enumerate(player_list):
            player = Player(player_name=p[0], nationality=p[1])
            player.club = Club(club_name=club_list[i][0], number=club_list[i][1], country=club_list[i][2])
            self.session.add(player)

if __name__ == '__main__':
    unittest.main()