example_1.py

import records

db = records.Database('mysql://root:[email protected]/bank')


# Create User table
db.query("""CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(100) NOT NULL,
  `middle_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `username` VARCHAR(15) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(100) NOT NULL,
  `enabled` TINYINT(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC));
)""")


# Create Login table
db.query("""CREATE TABLE `login` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user` INT NOT NULL,
  `datetime` DATETIME NOT NULL,
  `success` TINYINT(1) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `login_user`
    FOREIGN KEY (`user`)
    REFERENCES `user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
)""")


# Create BankAccount table
db.query("""CREATE TABLE `bankaccount` (
  `account_number` INT NOT NULL,
  `account_holder` INT NOT NULL,
  `type` INT NOT NULL,
  `enabled` TINYINT(1) NOT NULL,
  PRIMARY KEY (`account_number`),
  CONSTRAINT `bankaccount_user`
    FOREIGN KEY (`account_holder`)
    REFERENCES `user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
""")


# Create Transaction table
db.query("""CREATE TABLE `transaction` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `from_account` INT NOT NULL,
  `to_account` INT NOT NULL,
  `amount` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `transaction_bankaccount_from`
    FOREIGN KEY (`from_account`)
    REFERENCES `bankaccount` (`account_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `transaction_bankaccount_to`
    FOREIGN KEY (`to_account`)
    REFERENCES `bankaccount` (`account_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
""")