8U77CrdH

1from sqlalchemy.ext.declarative import declarative_base
2from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, DateTime, BOOLEAN
3from sqlalchemy.orm import sessionmaker
4from sqlalchemy import exc
5
6Base = declarative_base()
7
8sql_script = '''
9CREATE DATABASE IF NOT EXISTS university DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
10USE {};
11CREATE TABLE people(id INTEGER NOT NULL, surname CHAR(32), name CHAR(32), patronymic CHAR(32), burthday DATETIME, phone CHAR(32));
12CREATE TABLE student(id INTEGER NOT NULL, group CHAR(10), boss BOOLEAN, speciality CHAR(100), people_id INTEGER NOT NULL);
13ALTER TABLE people ADD PRIMARY KEY (id);
14ALTER TABLE student ADD PRIMARY KEY (id);
15ALTER TABLE student ADD FOREIGN KEY (people_id) REFERENCES people (id) ON DELETE CASCADE;
16'''
17
18
19class University:
20    def __init__(self, host='localhost', port=3306, user='root', password='python', base_name='university'):
21        self.engine = create_engine('mysql://{}:{}@{}:{}'.format(user, password, host, port), echo=False)
22        # создаем новую базу если ее не существует
23        with self.engine.begin() as conn:
24            if base_name not in [i[0] for i in conn.execute('show databases;').fetchall()]:
25                conn.execute(sql_script.format(base_name))
26                print('base university created successfully')
27            else:
28                conn.execute('USE {};'.format(base_name))