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()