def create_new_session() -> SessionDto:
"""Создает новую запись в таблице 'sessions'"""
token = gen_random_str(20)
now = datetime.now()
c = connection.cursor()
c.execute(
"INSERT INTO sessions (token, created_at, updated_at) VALUES (%s, %s, %s);",
(token, now, now),
)
connection.commit()
return SessionsService.get_session_by_token(token)
def create_user(username: str, password: str) -> UserDto:
now = datetime.now()
c = connection.cursor()
password = get_password_hash(password)
c.execute(
"INSERT INTO users (username, password, created_at, updated_at) VALUES (%s, %s, %s, %s) RETURNING id;",
(username, password, now, now),
)
(user_id, ) = c.fetchone()
connection.commit()
return UsersService.get_user_by_id(user_id)
def edit_category_by_id(category_id, title) -> CategoryDto:
c = connection.cursor()
c.execute(
"""UPDATE categories SET title=%s WHERE id=%s;""",
(title , category_id)
)
connection.commit()
category = CategoriesService.get_category_by_id(category_id)
return category
def updateOnReviewConsultantLink(self):
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set Feature = %s, LastStep=%s, updated_at=%s Where UserId = %s and Id='" + last_inserted_id + "'"
cursor.execute(sql, (self.Feature, self.Steps,
datetime.datetime.now(), self.UserId))
connection.commit()
print("record on 7th step Updated a featured or not")
cursor.close()
finally:
return ""
def delete(sysrole_kode):
global cursor
try:
cursor = connection.cursor()
cursor.execute("""DELETE FROM sys_role WHERE sysrole_kode=%s""", sysrole_kode)
connection.commit()
return utility.give_response("00", "DELETE ROLE SUKSES")
except Exception as e:
return utility.give_response("01", str(e))
finally:
cursor.close()
def updateFirstStepConsultantLink(self):
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set Plan = %s, LastStep=%s, updated_at=%s Where UserId = %s and Id='" + last_inserted_id + "'"
cursor.execute(sql, (self.Plan, self.Steps,
datetime.datetime.now(), self.UserId))
connection.commit()
print("record from plan selection page")
cursor.close()
finally:
return ""
def insertPersonalLink(self):
try:
with connection.cursor() as cursor:
# Read a single record
sql = "INSERT INTO personallink ( priority,websiteurl,websitename,purpose,tags,type,market,visualize,createddate) VALUES (%s, %s,%s, %s,%s, %s,%s, %s, %s)"
cursor.execute(sql, (
self.priority, self.websiteurl, self.websitename, self.purpose, self.tags, self.type, self.market,
self.visualize, self.createddate))
connection.commit()
cursor.close()
finally:
return "Saved successfully."
def delete(sysuser_id):
global cursor
try:
cursor = connection.cursor()
cursor.execute("""DELETE FROM sys_user WHERE sysuser_id = %s""",
sysuser_id)
connection.commit()
return utility.give_response("00", "HAPUS USER SUKSES")
except Exception as e:
return utility.give_response("01", str(e))
finally:
cursor.close()
def createNewUrl(original_url: str):
""" Добавить новую запись в таблицу `links`"""
cur = connection.cursor()
code = getRandomString()
now = datetime.now()
cur.execute(
"INSERT INTO links (original_url, code, created_at) VALUES (%s, %s, %s) RETURNING id, code",
(original_url, code, now),
)
link_id, link_code = cur.fetchone()
connection.commit()
return link_id
def saveStudents(students):
sql = "INSERT INTO school(studentNumber,name,surname,birthday,gender) VALUES (%s,%s,%s,%s,%s)"
values = (students)
Student.cursor.executemany(sql, values)
try:
connection.commit()
print(f"{Student.cursor.rowcount} tane kayıt eklendi.")
except mysql.connector.Error as err:
print("hata: ", err)
finally:
connection.close()
print("database kapatıldı")
def updateProducts(id, name, surname):
sql = "update school set name=%s,surname=%s where id=%s"
value = (name, surname, id)
Student.cursor.execute(sql, value)
try:
connection.commit()
print(f"{Student.cursor.rowcount} tane kayıt güncellendi.")
except mysql.connector.Error as err:
print("hata: ", err)
finally:
connection.close()
print("database kapatıldı")
def create_new_cat(title) -> CategoryDto:
c = connection.cursor()
c.execute(
"""INSERT INTO categories (title)
VALUES (%s) RETURNING id;""",
(title,),
)
category_id, = c.fetchone()
connection.commit()
category = CategoriesService.get_category_by_id(category_id)
return category
def write_to_db(match, data):
if match:
try:
with connection.cursor() as cursor:
sql = """
INSERT INTO `post_request`.`request` (`first_name`, `last_name`) VALUES (%s,%s)
"""
# for x in request:
cursor.execute(sql, (data['firstname'], data['lastname']))
connection.commit()
finally:
connection.close()
def updateSecondStepConsultantLink(self):
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set Title = %s, LastStep=%s, updated_at=%s, JobCategory=%s, SubCategory=%s Where UserId = %s and Id='" + last_inserted_id + "' "
cursor.execute(
sql, (self.Title, self.Steps, datetime.datetime.now(),
self.JobCategory, self.SubCategory, self.UserId))
connection.commit()
print("record from 1st step title job ctgry and all")
cursor.close()
finally:
return ""
def updateFiveStepConsultantLink(self):
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set ImpSkills = %s, LastStep=%s, LookingSkills = %s, updated_at=%s Where UserId = %s and Id='" + last_inserted_id + "'"
cursor.execute(sql,
(self.ImpSkills, self.Steps, self.LookingSkills,
datetime.datetime.now(), self.UserId))
connection.commit()
print("record from 4th looking skills and etc")
cursor.close()
finally:
return ""
def deleteProducts(id):
sql = "delete from school where id=%s"
value = (id, )
Student.cursor.execute(sql, value)
try:
connection.commit()
print(f"{Student.cursor.rowcount} tane kayıt silindi.")
except mysql.connector.Error as err:
print("hata: ", err)
finally:
connection.close()
print("database kapatıldı")
def create_new_author(first_name, last_name) -> AuthorDto:
c = connection.cursor()
c.execute(
"""INSERT INTO authors (first_name,last_name)
VALUES (%s,%s) RETURNING id;""",
(first_name, last_name),
)
author_id, = c.fetchone()
connection.commit()
author = AuthorsService.get_author_by_id(author_id)
return author
def put(sysrole_kode):
global cursor
try:
sysrole_nama = request.form["sysrole_nama"]
cursor = connection.cursor()
cursor.execute("""UPDATE sys_role SET sysrole_nama=%s WHERE sysrole_kode=%s""",
(sysrole_nama, sysrole_kode))
connection.commit()
return utility.give_response("00", "UPDATE ROLE SUKSES")
except Exception as e:
return utility.give_response("01", str(e))
finally:
cursor.close()
def create_log(response, status):
cursor = connection.cursor()
now = datetime.datetime.now()
request_time = now.strftime("%Y-%m-%d %H:%M")
if not request.form:
request_form = json.dumps("none")
else:
request_form = json.dumps(request.form)
cursor.execute(
"""INSERT INTO logservice (uri,method,params,ip_address,request_time,response,status) VALUES (%s,%s,%s,%s,%s,%s,%s)""",
(request.base_url, request.method, request_form, request.remote_addr,
request_time, response, status))
connection.commit()
def updateSixStepConsultantLink(self):
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set JobCanSeenBy = %s, LastStep=%s, updated_at=%s Where UserId = %s and Id='" + last_inserted_id + "'"
cursor.execute(sql, (self.JobCanSeenBy, self.Steps,
datetime.datetime.now(), self.UserId))
connection.commit()
print("record from 5th step saved job can seen")
cursor.close()
except:
print("Execption in SQL at updateSixStepConsultantLink.")
finally:
return ""
def get():
global cursor
try:
cursor = connection.cursor()
cursor.execute("""SELECT * FROM sys_rmodul""")
result = [dict((cursor.description[i][0], value)
for i, value in enumerate(row)) for row in cursor.fetchall()]
connection.commit()
return utility.give_response("00", "GET RMODUL SUKSES", result)
except Exception as e:
return utility.give_response("01", str(e))
finally:
cursor.close()
def updateThirdStepConsultantLink(self):
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set Description=%s, LastStep=%s, FileName = %s, FileLocation = %s, FileData = %s, updated_at=%s Where UserId = %s and Id='" + last_inserted_id + "'"
cursor.execute(sql,
(self.Description, self.Steps, self.FileName,
self.FileLocation, self.FileData,
datetime.datetime.now(), self.UserId))
connection.commit()
print("record from 2nd step descrption last step and etc")
cursor.close()
finally:
return ""
def updateFourStepConsultantLink(self):
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set ProjectType = %s, LastStep=%s, Describes = %s, WorkType = %s, ApiToIntegrate = %s, ProjectStage = %s, updated_at=%s Where UserId = %s and Id='" + last_inserted_id + "'"
cursor.execute(
sql,
(self.ProjectType, self.Steps, self.Describes,
self.WorkType, self.ApiToIntegrate, self.ProjectStage,
datetime.datetime.now(), self.UserId))
connection.commit()
print("record from 3rd steps project type and all")
cursor.close()
finally:
return ""
def post():
global cursor
try:
sysrole_kode = request.form["sysrole_kode"]
sysrole_nama = request.form["sysrole_nama"]
cursor = connection.cursor()
cursor.execute("""INSERT sys_role (sysrole_kode,sysrole_nama) VALUES (%s,%s)""",
(sysrole_kode, sysrole_nama))
connection.commit()
return utility.give_response("00", "INSERT ROLE SUKSES")
except Exception as e:
return utility.give_response("01", str(e))
finally:
cursor.close()
def post():
global cursor
try:
sysrole_kode = request.form["sysrole_kode"]
sysmodul_kode = request.form["sysmodul_kode"]
cursor = connection.cursor()
cursor.execute("""DELETE FROM sys_rmodul WHERE sysrole_kode=%s AND sysmodul_kode=%s""",
(sysrole_kode, sysmodul_kode))
connection.commit()
return utility.give_response("00", "DELETE RMODUL SUKSES")
except Exception as e:
return utility.give_response("01", str(e))
finally:
cursor.close()
def get(self):
cursor = connection.cursor()
query = "SELECT * FROM items"
cursor.execute(query)
result = cursor.fetchall()
items = []
for row in result:
items.append({'name': row[1], 'price': row[2]})
connection.commit()
return {"items": items}
def FromSevenSaveExit(self, steps, userid):
Feature = self.Feature
print("FromSevenSaveExit Feature: ", Feature)
print("FromSevenSaveExit function")
try:
with connection.cursor() as cursor:
sql = "UPDATE consultant set Feature = %s, SaveExit = %s, LastStep=%s, updated_at=%s Where UserId = %s and Id='" + last_inserted_id + "'"
cursor.execute(
sql,
(Feature, '1', steps, datetime.datetime.now(), userid))
connection.commit()
print("record SAVE and exit")
cursor.close()
finally:
return ""
def delete_by_instance_id(instance_id):
"""
Удалить по id
:param instance_id: id элемента
:return: удаленного экземпляра
"""
logger = logging.getLogger("DiagramObjects")
logger.info("Delete diagramobject by instance_id...")
with connection.cursor() as cursor:
sql = "DELETE FROM `t_diagramobjects` WHERE `Instance_ID`=?"
result = get_by_id(
instance_id) # поиск по уникальному ключу добавленного элемента
cursor.execute(sql, (instance_id))
connection.commit()
return result
def update_by_id(object_id, diagram_id, instance_id):
"""
Обновление по ключу
:param object_id: id объекта
:param diagram_id: id диаграммы
:param instance_id: id объекта диаграммы
:return:
"""
logger = logging.getLogger("DiagramObjects")
logger.info("Update diagramobject by id")
with connection.cursor() as cursor:
sql = "UPDATE `t_diagramobjects` SET `Object_ID`=?, `Diagram_ID`=? WHERE Instance_ID=?"
cursor.execute(sql, (object_id, diagram_id, instance_id))
connection.commit()
result = get_by_id(instance_id)
return result
def post(self):
data = UserRegister.parser.parse_args()
if User.find_by_username(data['username']) :
return {"message": "A user with that username already exists"}, 400
cursor = connection.cursor()
query = "INSERT INTO users VALUES (NULL, %s, %s)"
cursor.execute(query, (data['username'], data['password']))
connection.commit()
return {
"message": "User created successfully."
}, 201
