def add_user(user: UserModel):
connector = SqlController().sql_connector
cursor = connector.cursor()
handled = False
sql = "INSERT INTO user " \
"(realname, nickname, gender, location, email, tags, selfdescription) " \
"VALUES " \
"(%s, %s, %s, %s, %s, %s,%s)"
val = (user.name, user.nickname, user.gender,
user.location, user.email, user.tags, user.description)
try:
cursor.execute(sql, val)
connector.commit()
handled = True
return Errors.SUCCESS.name
except mysql.connector.errors.IntegrityError as err:
print(err.msg)
if not handled:
handled = True
return Errors.DUPLICATE.name
finally:
connector.rollback()
if not handled:
return Errors.FAILURE.name
def retrieve_user(field: str, value: str):
handled = False
connector = SqlController().sql_connector
cursor = connector.cursor()
sql = "SELECT * " \
"FROM user " \
"WHERE {field} = %s".format(field=field)
val = [value]
try:
cursor.execute(sql, val)
user_info = cursor.fetchone()
if not user_info:
handled = True
return Errors.MISSING.name
else:
handled = True
return_user = decode_string(str(user_info))
return_user.join_events = get_join(return_user.uid)
return_user.host_events = get_host(return_user.uid)
return return_user
except mysql.connector.errors as err:
print(err.msg)
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
def executeS(sql: str = None, args: dict = None):
ExceptLog = None
retVal = None
connector = None
cursor = None
try:
if sql is None:
raise Exception('Invalid Sql : None type')
connector, cursor = connect()
if args is None:
cursor.execute(sql)
else:
cursor.execute(sql, args)
connector.commit()
retVal = True
except Exception as Ex:
if ExceptLog is None:
ExceptLog = osLogger.cOSLogger(_pPrefix='Exception',
_pLevel='ERROR')
ExceptLog.writeLog('[osDB:executeS] ' + str(Ex))
ExceptLog.writeLog('SQL : \n' + sql)
ExceptLog.writeLog('Variable : ' | str(args))
if connector is not None:
connector.rollback()
retVal = False
finally:
close(connector, cursor)
return retVal
def edit_user(user: UserModel):
handled = False
connector = SqlController().sql_connector
cursor = connector.cursor()
sql = "UPDATE user " \
"SET realname = %s, nickname = %s, gender = %s, location = %s, " \
"email = %s, tags = %s, selfdescription = %s " \
"WHERE userid = %s"
val = (user.name, user.nickname, user.gender, user.location, user.email,
user.tags, user.description, user.uid)
try:
cursor.execute(sql, val)
connector.commit()
if cursor.rowcount == 0:
handled = True
return Errors.MISSING.name
else:
handled = True
return user.uid
except mysql.connector.errors.IntegrityError as err:
print(err.msg)
if not handled:
handled = True
return Errors.DUPLICATE.name
finally:
connector.rollback()
if not handled:
return Errors.FAILURE.name
def insert_into_online_shop(self, topic_cluster, word, score,
merchant_name, year, month):
connector = self.connector()
cursor = connector.cursor()
add_data_query = (
"INSERT INTO online_shop "
"(id, topic_cluster, word, score, merchant_name, created_at)"
"VALUES (%(id)s, %(topic_cluster)s, %(word)s, %(score)s, %(merchant_name)s, %(created_at)s)"
)
data = {
'id': str(uuid.uuid4()),
'topic_cluster': topic_cluster,
'word': word,
'score': float(score),
'merchant_name': merchant_name,
'created_at': date(year, month, 1)
}
try:
cursor.execute(add_data_query, data)
connector.commit()
except mysql.connector.Error:
connector.rollback()
cursor.close()
connector.close()
def delete_user(user_nick_name: str, user_email: str):
handled = False
connector = SqlController().sql_connector
cursor = connector.cursor()
sql = "DELETE " \
"FROM user " \
"WHERE nickname = %s AND email = %s"
val = (user_nick_name, user_email)
try:
cursor.execute(sql, val)
connector.commit()
handled = True
return "SUCCESS"
except mysql.connector.errors as err:
print(err.msg)
finally:
if not handled:
connector.rollback()
return Errors.FAILURE.name
def edit_user(user: UserModel):
connector = SqlController().sql_connector
cursor = connector.cursor()
sql = "UPDATE user " \
"SET realname = %s, nickname = %s, gender = %s, location = %s, " \
"email = %s, tags = %s, selfdescription = %s " \
"WHERE email = %s"
val = (user.name, user.nickname, user.gender, user.location, user.email,
user.tags, user.description, user.email)
try:
cursor.execute(sql, val)
connector.commit()
if cursor.rowcount == 0:
return Errors.MISSING.name
else:
return Errors.SUCCESS.name
finally:
connector.rollback()
return Errors.FAILURE.name
host='127.0.0.1',
database='trips')
cur = conn.cursor()
cur.execute("DROP TABLE TRIPS;")
# Create database:
try:
###Execute the SQL command
cur.execute("CREATE TABLE trips (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, organizator text, country text, city text, trip_date text, price text, "
"free_slots text, link text);")
# Commit your changes in the database
conn.commit()
except:
## Rollback in case there is any error
conn.rollback()
try:
cur.executemany("""INSERT INTO trips(organizator, country, city, trip_date, price, free_slots, link)
VALUES (%(Organizator)s,%(Country)s, %(City)s, %(Date)s, %(Price)s, %(Free slots)s, %(Link)s)""", db)
conn.commit()
except:
conn.rollback()
cur.execute("SELECT * FROM trips;")
print(cur.fetchall())
# close cursor object
cur.close()
# disconnect from server
conn.close()
def do_query(query, data):
try:
cursor.execute(query, data)
connector.commit()
except:
connector.rollback()
