def connect_database(self):
# DB instance
self.db_instance = Database(self.config)
# Connect to database
self.db_connection = self.db_instance.connect()
self.mysql_instance = self.db_connection.get('mysql_instance')
self.mysql_connection = self.db_connection.get('mysql_connection')
self.mysql_ctx = self.db_connection.get('mysql_ctx')
def create_app(test_config=None):
# create and configure the app
app = Flask(__name__, instance_relative_config=True)
# app.config.from_mapping(
# SECRET_KEY='dev',
# DATABASE=os.path.join(app.instance_path, 'flaskr.sqlite'),
# )
if IS_MIGRATE == "FALSE":
# db instance
db_instance = Database()
# create connection
db = db_instance.connect()
# get mysql instance and connection and context
mysql_instance = db.get('mysql_instance')
mysql_connection = db.get('mysql_connection')
mysql_ctx = db.get('mysql_ctx')
app.mysql_instance = mysql_instance
app.mysql_connection = mysql_connection
app.mysql = mysql_ctx
app.mysql_ctx = mysql_ctx
app.mysql_lastrowid = None
app.db_instance = db_instance
app.environment = os.environ
# app.mysql_close_connection = db_instance.close_connection(app.mysql_connection, app.mysql)
# app.mysql.execute("show databases")
# for i in app.mysql:
# print(i)
if test_config is None:
# load the instance config, if it exists, when not testing
app.config.from_pyfile('config.py', silent=True)
else:
# load the test config if passed in
app.config.from_mapping(test_config)
# ensure the instance folder exists
try:
os.makedirs(app.instance_path)
except OSError:
pass
# a simple page that says hello
# @app.route('/hello')
# def hello():
# return 'Hello, World!'
return app
def connect(self):
db_instance = Database()
# create connection
db = db_instance.connect()
# get mysql instance and connection and context
mysql_instance = db.get('mysql_instance')
mysql_connection = db.get('mysql_connection')
mysql_ctx = db.get('mysql_ctx')
app.mysql_instance = mysql_instance
app.mysql_connection = mysql_connection
app.mysql = mysql_ctx
app.db_instance = db_instance
def db():
environ["POSTGRES_DB"] = "user-manager"
postgres_container = PostgresContainer("postgres:12.2-alpine")
with postgres_container as postgres:
environ["DATABASE_URL"] = postgres.get_connection_url()
database = Database(get_test_settings())
system("alembic upgrade head")
yield database
def db_session(db: Database):
with db.get_session() as session:
try:
yield session
finally:
session.rollback()
for table in reversed(Base.metadata.sorted_tables):
session.execute(table.delete())
session.commit()
session.close()
def db_session(settings: Settings = Depends(get_settings)):
db = Database(settings)
with db.get_session() as session:
yield session
async def load_db() -> Generator:
with Database() as db:
yield db
class Jobs(object):
db_instance = None
db_connection = None
mysql_instance = None
mysql_connection = None
mysql_ctx = None
config = {}
DB_NAME = None
TABLES = {}
def __init__(self, config=dict()):
super(Jobs, self).__init__()
# Set config
self.config = db_config()
# Override DB_NAME
self.config['database'] = config.get('database')
# Connect Database
self.connect_database()
# Prepare Tables
# self.prepare_tables()
# Create or Use database
# self.create_database()
# Create Tables
# self.create_table()
# Close Connection
# self.close_connection()
# print('here migration \n')
def connect_database(self):
# DB instance
self.db_instance = Database(self.config)
# Connect to database
self.db_connection = self.db_instance.connect()
self.mysql_instance = self.db_connection.get('mysql_instance')
self.mysql_connection = self.db_connection.get('mysql_connection')
self.mysql_ctx = self.db_connection.get('mysql_ctx')
def close_connection(self):
self.db_instance.close_connection(self.mysql_connection,
self.mysql_ctx)
def create_database(self, db_name):
# Create database
self.db_instance.create_database(self, db_name)
def drop_database(self, db_name):
# Drop database
self.db_instance.drop_database(self, db_name)
def create_table(self):
# Create table
for table_name in self.TABLES:
action_raw = self.TABLES.get(table_name)
action_name = action_raw.get('action')
action_command = action_raw.get('command')
self.db_instance.execute_command(self, table_name, action_name,
action_command)
def create_begin_process(self, title):
print("/******** Begin {} ********/".format(title))
def create_end_process(self, title):
print("/******** End {} ********/\n".format(title))
def execute_command(self, commands):
# Execute Command
for table_name in commands:
action_raw = commands.get(table_name)
action_name = action_raw.get('action')
action_command = action_raw.get('command')
self.db_instance.execute_command(self, table_name, action_name,
action_command)
def prepare_tables(self, tables):
if not tables:
self.TABLES = self.example_tables()
self.TABLES = tables
async def startup(ctx: Dict[str, Any]) -> None:
db = Database(ctx["config"].database_uri)
# Warning: This is running blocking IO and if this gets anymore heavy you should really run this in a thread pool
db.connect()
ctx["database"] = db
class Migrations(object):
db_instance = None
db_connection = None
mysql_instance = None
mysql_connection = None
mysql_ctx = None
config = {}
DB_NAME = None
TABLES = {}
def __init__(self, config = dict()):
super(Migrations, self).__init__()
# Set config
self.config = db_config()
# Override DB_NAME
self.config['database'] = config.get('database')
# Connect Database
self.connect_database()
# Prepare Tables
# self.prepare_tables()
# Create or Use database
# self.create_database()
# Create Tables
# self.create_table()
# Close Connection
# self.close_connection()
# print('here migration \n')
def connect_database(self):
# DB instance
self.db_instance = Database(self.config)
# Connect to database
self.db_connection = self.db_instance.connect()
self.mysql_instance = self.db_connection.get('mysql_instance')
self.mysql_connection = self.db_connection.get('mysql_connection')
self.mysql_ctx = self.db_connection.get('mysql_ctx')
def close_connection(self):
self.db_instance.close_connection(self.mysql_connection, self.mysql_ctx)
def create_database(self, db_name):
# Create database
self.db_instance.create_database(self, db_name)
def drop_database(self, db_name):
# Drop database
self.db_instance.drop_database(self, db_name)
def create_table(self):
# Create table
for table_name in self.TABLES:
action_raw = self.TABLES.get(table_name)
action_name = action_raw.get('action')
action_command = action_raw.get('command')
self.db_instance.execute_command(self, table_name, action_name, action_command)
def rename_table(self, before_name, after_name):
# Rename Table
self.db_instance.rename_table(self, before_name, after_name)
def execute_command(self, commands):
# Execute Command
for table_name in commands:
action_raw = commands.get(table_name)
action_name = action_raw.get('action')
action_command = action_raw.get('command')
self.db_instance.execute_command(self, table_name, action_name, action_command)
def prepare_tables(self, tables):
if not tables:
self.TABLES = self.example_tables()
self.TABLES = tables
def example_tables(self):
example_tables = {}
example_tables['employees'] = (
"CREATE TABLE `employees` ("
" `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
" `birth_date` date NOT NULL,"
" `first_name` varchar(14) NOT NULL,"
" `last_name` varchar(16) NOT NULL,"
" `gender` enum('M','F') NOT NULL,"
" `hire_date` date NOT NULL,"
" PRIMARY KEY (`emp_no`)"
") ENGINE=InnoDB")
example_tables['departments'] = (
"CREATE TABLE `departments` ("
" `dept_no` char(4) NOT NULL,"
" `dept_name` varchar(40) NOT NULL,"
" PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)"
") ENGINE=InnoDB")
example_tables['salaries'] = (
"CREATE TABLE `salaries` ("
" `emp_no` int(11) NOT NULL,"
" `salary` int(11) NOT NULL,"
" `from_date` date NOT NULL,"
" `to_date` date NOT NULL,"
" PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`),"
" CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) "
" REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
") ENGINE=InnoDB")
example_tables['dept_emp'] = (
"CREATE TABLE `dept_emp` ("
" `emp_no` int(11) NOT NULL,"
" `dept_no` char(4) NOT NULL,"
" `from_date` date NOT NULL,"
" `to_date` date NOT NULL,"
" PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`),"
" KEY `dept_no` (`dept_no`),"
" CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) "
" REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
" CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) "
" REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
") ENGINE=InnoDB")
example_tables['dept_manager'] = (
" CREATE TABLE `dept_manager` ("
" `dept_no` char(4) NOT NULL,"
" `emp_no` int(11) NOT NULL,"
" `from_date` date NOT NULL,"
" `to_date` date NOT NULL,"
" PRIMARY KEY (`emp_no`,`dept_no`),"
" KEY `emp_no` (`emp_no`),"
" KEY `dept_no` (`dept_no`),"
" CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) "
" REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
" CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) "
" REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
") ENGINE=InnoDB")
example_tables['titles'] = (
"CREATE TABLE `titles` ("
" `emp_no` int(11) NOT NULL,"
" `title` varchar(50) NOT NULL,"
" `from_date` date NOT NULL,"
" `to_date` date DEFAULT NULL,"
" PRIMARY KEY (`emp_no`,`title`,`from_date`), KEY `emp_no` (`emp_no`),"
" CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`)"
" REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
") ENGINE=InnoDB")
return example_tables