Skip to content

Instantly share code, notes, and snippets.

@ayancey
Created Feb 14, 2017
Embed
What would you like to do?
import records
db = records.Database('mysql://root:a6478a14@172.16.144.157/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);
""")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment