Python Mysql.commit方法代码示例

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import commit [as 别名]
 def get(self):
     enter_func(self)
     if not set(['id']).issubset(self.request.arguments.keys()):
         return leave_func(self, 1)
     id = int(self.request.arguments['id'][0])
     mysql_handle = Mysql().get_handle()
     mysql_cursor = mysql_handle.cursor(MySQLdb.cursors.DictCursor)
     sql = 'UPDATE entity_question SET state = "DISABLED" WHERE id = %d' % id
     LOG.info('mysql> %s' % sql)
     mysql_cursor.execute(sql)
     mysql_handle.commit()
     mysql_cursor.close()
     mysql_handle.close()
     leave_func(self, 0)
     return self.write(error_process(0))

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import commit [as 别名]
class MySQLDB(object):

    def __init__(self, host, db, user, password):
        self.connection = None
        self.host, self.db, self.user, self.password = host, db, user, password
        self.connect()

    def connect(self):
        if not self.connection:            
            self.connection = Mysql(host=self.host, db=self.db, user=self.user, password=self.password)
            self.connection.connect()
        return self.connection

    def insertSQL(self, table, columnValueDict):
        sql = 'INSERT INTO %s(%s) VALUES(%s)' % (table, ",".join(columnValueDict.keys()), ",".join(self.to_str(columnValueDict.values())))
        return self.connection.execute(sql)
    
    def insert(self, table, columns, data):        
        sql = 'INSERT INTO %s(%s) VALUES(%s)' % (table, ",".join(columns), ",".join(self.to_str(data)))
        return self.connection.execute(sql)

    def insertSQLIgnore(self, table, columnValueDict):
        sql = 'INSERT IGNORE INTO %s(%s) VALUES(%s)' % (table, ",".join(columnValueDict.keys()), ",".join(self.to_str(columnValueDict.values())))
        return self.connection.execute(sql)

    def execute(self, sql):
        return self.connection.execute(sql)

    def commit(self):
        self.connection.commit()

    def close(self):
        self.connection.commit()
        self.connection.close()
        self.connection = None

    def to_str(self, arr):
        a = arr[:]
        for i in range(len(arr)):
            a[i] = "'%s'" % arr[i]
        return a

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import commit [as 别名]

#.........这里部分代码省略.........
        question_html = self.request.arguments['html'][0]

        try:
            if not (type_id.isdigit() and int(type_id) and level_id.isdigit() and int(level_id) and group_id.isdigit() and question_id.isdigit() and int(question_id) and theme + chapter_id and question_json and question_html):
                return leave_func(self, 1)
            if chapter_id and not chapter_id.isdigit():
                return leave_func(self, 1)

            if Business.is_level(level_id) is False:
                LOG.error('invalid level_id[%s]' % level_id)
                return leave_func(self, 1)

            try:
                #question_json = urllib.unquote(question_json)
                encode_json = {}
                encode_json['content'] = json.loads(question_json, encoding = 'utf-8')
                #question_html = urllib.unquote(question_html)
                encode_html = {}
                encode_html['content'] = json.loads(question_html, encoding = 'utf-8')
            except:
                traceback.print_exc()
                LOG.error(sys.exc_info())
                return leave_func(self, 100)

            sql_list = []
            if chapter_id:
                sql_list.append('INSERT INTO link_question_chapter(question_id, chapter_id) VALUE(%s, %s) ON DUPLICATE KEY UPDATE chapter_id = %s' % (question_id, chapter_id, chapter_id)) # 生成更新章节关联信息的SQL

            if theme: # 主题
                sql_list.append('DELETE FROM link_question_topic WHERE question_id = %s' % question_id) # 生成删除原有主题关联的SQL
                for theme_id in theme.split(','): # 将传入的主题号按逗号切割
                    if Business.is_topic(theme_id) is False: # 判断主题号是否存在
                        LOG.error('invalid theme_id[%s]' % theme_id)
                        return leave_func(self, 1)
                    sql_list.append('INSERT INTO link_question_topic (question_id, topic_id) VALUES (%s, %s)' % (question_id, theme_id)) # 生成将新主题关联插库的SQL

            question_type = Business.is_type(type_id)
            if question_type is False: # 判断题目类型是否存在
                LOG.error('invalid type_id[%s]' % type_id)
                return leave_func(self, 1)

            answer_num = 0
            if 'answer' in encode_json['content'].keys():
                if type_id == '1':
                    answer_num = len(encode_json['content']['answer'])
                elif type_id == '2':
                    answer_num = len([int(answer_group['index']) for answer_group in encode_json['content']['answer']])

            sql_list.append('UPDATE entity_question SET difficulty = %s, update_time = now(), question_type = "%s", question_group = %s, answer_num = %s WHERE id = %s' % (level_id, question_type, group_id, answer_num, question_id)) # 生成更新题目属性的SQL

            mysql_handle = Mysql().get_handle()
            mysql_cursor = mysql_handle.cursor(MySQLdb.cursors.DictCursor)
            mysql_cursor.execute('SELECT question_docx, html FROM entity_question WHERE id = %s' % question_id) # 通过题目ID查询存储的json/html文件名
            result = mysql_cursor.fetchall()
            if not result:
                LOG.error('invalid question_id[%s]' % question_id)
                return leave_func(self, 1)

            qiniu = QiniuWrap()
            mongo = Mongo()
            mongo.connect('resource')

            if result[0]['question_docx'] and '.json' in result[0]['question_docx']:
                json_name = result[0]['question_docx']
                # 将七牛上的json文件删除后重新上传
                qiniu.bucket.delete("temp", json_name)
                qiniu.upload_data("temp", json_name, question_json)
                # 将MongoDB中的json文件删除后重新上传
                mongo.select_collection('mongo_question_json')
                LOG.debug('resource.mongo_question_json.remove( { question_id: %s } )' % question_id)
                mongo.remove( { "question_id" : int(question_id) } )
                encode_json['question_id'] = int(question_id)
                LOG.debug('resource.mongo_question_json.insert_one( %s )' % encode_json)
                mongo.insert_one(encode_json)

            if result[0]['html'] and '.html' in result[0]['html']:
                html_name = result[0]['html']
                # 将七牛上的html文件删除后重新上传
                qiniu.bucket.delete("temp", html_name)
                qiniu.upload_data("temp", html_name, question_html)
                # 将MongoDB中的html文件删除后重新上传
                mongo.select_collection('mongo_question_html')
                LOG.debug('resource.mongo_question_html.remove( { question_id: %s } )' % question_id)
                mongo.remove( { "question_id" : int(question_id) } )
                encode_html['question_id'] = int(question_id)
                LOG.debug('resource.mongo_question_html.insert_one( %s )' % encode_html)
                mongo.insert_one(encode_html)

            for sql in sql_list:
                LOG.info(sql)
                mysql_cursor.execute(sql)
            mysql_handle.commit()
            mysql_cursor.close()
            mysql_handle.close()

            leave_func(self, 0)
            self.write(error_process(0))
        except Exception, e:
            LOG.error(e)
            return leave_func(self, 100)