def execute_sql_file(sql_path):
db = Base()
with open(sql_path, 'r') as sql_file:
query = sql_file.read()
db.get_new_session().executescript(query)
db_logger.info(f'DB {db.db_path} ready')
sql_file.close()
def insert_data(self):
db_logger.info(self)
template = self.get_template('insert_into.sql')
row_dict = self.to_dict_without_primary()
sql = template.render(tablename=self.__tablename__,
fields=list(row_dict.keys()))
debug_logger.info(sql)
debug_logger.info(row_dict)
try:
self.get_new_session().execute(sql, row_dict)
except IntegrityError as error:
db_logger.error(f'Дубль по уникальному полю: {error}')
raise
finally:
self.conn.commit()
def delete_data(self):
db_logger.info(self)
template = self.get_template('delete_exp.sql')
id_field = self.get_primary()
where = self.kwargs_to_predicate_exp('and', **id_field)
sql = template.render(table=self.__tablename__, where_expression=where)
db_logger.info(sql)
db_logger.info(id_field)
self.get_new_session().execute(sql, id_field)
db_logger.info(f'{self} deleted')
self.conn.commit()
def select_expression(self, **kwargs):
template = self.get_template('select_exp.sql')
data = self.kwargs_to_predicate_exp('and', True, **kwargs)
sql = template.render(table=self.__tablename__, data=data)
db_logger.info(sql)
db_logger.info(kwargs)
result = self.get_new_session().execute(sql, kwargs).fetchall()
result_list = []
for result_row in result:
result_list.append(self.__construct__(result_row))
db_logger.info(result_list)
return result_list
def update_data(self):
db_logger.info(self)
template = self.get_template('update_exp.sql')
id_field = self.row[0].to_dict()
all_field = self.to_dict_without_primary()
# Добавляем к общему словарю последний элемент - тот, что будет в предикате
all_field.update(**id_field)
where = self.kwargs_to_predicate_exp('and', True, **id_field)
set_statement = self.kwargs_to_predicate_exp(',', **all_field)
sql = template.render(table=self.__tablename__,
set_expression=set_statement,
where_expression=where)
db_logger.info(sql)
try:
self.get_new_session().execute(sql, all_field)
except IntegrityError as error:
db_logger.error(f'Дубль по уникальному полю при апдейте: {error}')
raise
finally:
self.conn.commit()
db_logger.info(f'{self} updated')
def delete_opart(self, *args):
opart_ids = ', '.join([str(i) for i in args[0]])
db_logger.info(opart_ids)
sql = f'delete from op_art where id_opart in ({opart_ids})'
self.get_new_session().execute(sql)
self.conn.commit()
def delete_operation(id_op):
deleting_op = Operation()
deleting_op = deleting_op.select_expression(id_op=id_op)[0]
db_logger.info(deleting_op)
deleting_op.delete_data()
id_art=a,
quantity=random.randrange(1, 100))
op_art.insert()
oper = {}
id_op += 1
def execute_sql_file(sql_path):
db = Base()
with open(sql_path, 'r') as sql_file:
query = sql_file.read()
db.get_new_session().executescript(query)
db_logger.info(f'DB {db.db_path} ready')
sql_file.close()
create_db_sql_path = f'app/models/static_sql/create_db.sql'
if not db_exists():
start = datetime.now()
db_logger.info(f'Первоначальное наполнение БД...')
execute_sql_file(create_db_sql_path)
db_logger.info(
f'Залили create_db за {(datetime.now() - start).total_seconds()} секунд'
)
create_operations()
db_logger.info(
f'Успешно завершено за {(datetime.now() - start).total_seconds()} секунд'
)