Python MySQL.MySQL類代碼示例

def space_res_list(data):
	userId = UserService.user_id(data['UserKey'])
	if __test_auth_view(userId, data.get('Id', '')) > 0:
		db = MySQL()

		offset = long(data.get('Offset', 0))
		sort = max(1, min(3, int(data.get('Sort', 1))))
		order = int(data.get('Order', 0))
		listMax = min(100, data.get('Max', 10))

		resCount = db.get("SELECT COUNT(*) AS c FROM `space_resource` WHERE `space_id` = %s AND `res_type`=%s", (data.get('Id', ''), data.get('ResType', '')))['c']
		resList = db.list("SELECT * FROM `space_resource` WHERE `space_id` = %s AND `res_type`=%s", 
							(data.get('Id', ''), data.get('ResType', '')), sort='order_field%s'%sort, order='DESC' if order == 0 else 'ASC', offset=offset, pagesize=listMax )
		results = []
		for res in resList:
			results.append({
					'ResId': res['res_id'],
					'OrderField1': res['order_field1'],
					'OrderField2': res['order_field2'],
					'OrderField3': res['order_field3'],
				})

		return {
			'Id': data.get('Id', ''),
			'ResType': data.get('ResType', ''),
			'Count': resCount,
			'Offset': offset,
			'Max': listMax,
			'Sort': sort,
			'Order': order,
			'Results': results
		}
	else:
		raise Exception('沒有權限或空間不存在')

def video_poster(data):
	"""
	視頻截圖更新
	方法：
		video_poster
	參數：
		UserKey[string] –用戶登錄後的會話ID。
		VID[string] – 分配的視頻ID
		Time[float] – 截圖播放時間點
	返回值：
		VID[string] – 視頻ID
		Poster[string] – 視頻截圖地址
	"""
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()
	videoInstance = db.get('SELECT * FROM `video` WHERE `id` = %s', (data['VID']))

	if videoInstance:
		fileName = "%s/%s.mp4" % (videoDirectory, videoInstance['id'])

		PosterBaseURL = Config.get('Video','PosterBaseURL')
		PosterURL = "%s/%s.jpg" % (PosterBaseURL, videoInstance['id'])

		Transcoder.VideoPoster(fileName, ("%s/%s.jpg" % (videoDirectory, videoInstance['id'])), ss=float(data['Time']))
		return {
			'VID'       : videoInstance['id'],
			'Poster'    : PosterURL
		}

	return None

def invite_list(data):
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()

	inviteListInstance = db.list('SELECT * FROM `invite` WHERE `invite_date` >= NOW() - INTERVAL 60 DAY AND `is_pocket` = 0 AND `user_id` = %s AND `type` = %s', (userId, data.get('Type', None)), sort='invite_date', order='DESC')
	
	results = []
	for invite in inviteListInstance:
		dealUser = UserService.user_get(invite['deal_user_id'], notRaise = True) if invite['deal_user_id'] else None
		results.append({
				'Code': invite['id'],
				'InviteDate': invite['invite_date'],
				'ReferId': invite['refer_id'],
				'IsDeal': invite['is_deal'],
				'DealUserId': invite['deal_user_id'] if dealUser else None,
				'DealUser': dealUser['name'] if dealUser else None,
				'DealDate': invite['deal_date'] if dealUser else None,
				'IsPocket': invite['is_pocket'],
				'PocketDate': invite['pocket_date'],
			})

	return {
		'Type': data.get('Type', None),
		'Count': len(results),
		'Results': results,
	}

def video_ready(data):
	"""
	視頻處理狀態
	方法：
		video_ready
	參數：
		UserKey[string] –用戶登錄後的會話ID。
		VID[string] – 視頻ID
	返回值：
		VID[string] – 視頻ID
		Results[Array] – 視頻對象列表，視頻對象定義如下：
			Definition[string] - 清晰度
			Ready[boolean] - 是否準備就緒
			URL[string] – 視頻所有者，默認為視頻上傳/分享者的手機號
			Progress[float] – 處理進度
	"""
	result = []
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()
	videoInstance = db.get('SELECT * FROM `video` WHERE `id` = %s', (data['VID']))
	if videoInstance:

		VideoBaseURL = Config.get('Video','VideoBaseURL')
		videoTranscodeListInstance = db.list('SELECT * FROM `video_transcode` WHERE `video_id` = %s ORDER BY `video_width` DESC', (data['VID']))

		for videoTranscodeInstance in videoTranscodeListInstance:
			result.append({
				'Definition': MediaProbe.definitionName(videoTranscodeInstance['video_height'], videoTranscodeInstance['video_width']),
				'Ready'     : videoTranscodeInstance['is_ready'] == 1,
				'URL'       : "%s/%s" % (VideoBaseURL, videoTranscodeInstance['file_name']),
				'Progress'  : float(videoTranscodeInstance['progress']),
			})

	return result

def invite_pocket(data):
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()
	result = db.update("UPDATE `invite` SET `is_pocket` = 1, `pocket_date` = now() WHERE `user_id` = %s AND `id` = %s AND `is_pocket` = 0", (userId, data.get('Code', None)))
	db.end()
	if result > 0:
		return {
			'Code': data.get('Code', None)
		}
	else:
		raise Exception('邀請碼不存在或已處理')

def create(toUserId, notifyContent, sender=None, refId=None):
	notifyId = Utils.UUID()
	db = MySQL()
	db.save("INSERT INTO `user_notify` (`id`,`user_id`,`notify`,`sender`,`ref_id`) VALUES (%s,%s,%s,%s,%s)", (notifyId, toUserId, notifyContent, sender, refId))
	db.end()

	try:
		NotifyTCPServer.notify_server_has_new(toUserId)
	except Exception as e:
		print "Error: %s" % e

	return notifyId

def invite_code(data):
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()

	code = Utils.UUID()
	result = db.save("INSERT INTO `invite` (`id`, `user_id`, `type`, `refer_id`, `info`) VALUES (%s,%s,%s,%s,%s)", 
					(code, userId, data.get('Type', None), data.get('ReferId', None), data.get('Info', None)))
	db.end()

	return {
		'Code': code,
	}

def reverseShortUrl(shortUrl):
	result = None

	splitIdx = shortUrl.rindex('/')
	num = NumberCodec.decode(shortUrl[splitIdx+1:].split('?')[0]) if splitIdx >= 0 else NumberCodec.decode(shortUrl.split('?')[0])

	db = MySQL()
	# first find
	urlInstance = db.get(r"SELECT * FROM `short_urls` WHERE `id`=%s", num)
	if urlInstance:
		result = urlInstance['url']

	return result

def space_rename(data):
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()
	result = db.update("UPDATE `space` SET `name` = %s WHERE `id` = %s AND `user_id` = %s", (data.get('Name', ''), data.get('Id', ''), userId))
	db.end()

	if result > 0:
		return {
			'Id': data.get('Id', ''),
			'Name': data.get('Name', ''),
		}
	else:
		raise Exception('更新失敗或空間不存在')

def share_video(data):
	"""
	分享視頻
	方法：
		share_video
	參數：
		UserKey[string] –用戶登錄後的會話ID。
		VID[string] – 分配的視頻ID
		To[Array] – 分享對象列表，分享對象如下定義：
			Mobile[string] – 分享手機號，必填
			Name[string] – 分享姓名，可選
	返回值：
		sessionId[string] – 分配的分享會話ID
		Results[Array] – 分享結果對象列表，分享結果對象如下定義：
			Mobile[string] – 分享手機號
			Signup[boolean] – 是否注冊用戶
	"""
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()

	videoInstance = db.get('SELECT * FROM `video` WHERE `id` = %s', (data['VID']))
	if not videoInstance:
		raise Exception("視頻不存在.")

	sessionId = Utils.UUID()
	results = []
	for to in data.get('To', ()):
		toUserId = UserService.getUserIdByMobile(to.get('Mobile'))
		result = db.save("""INSERT INTO `share` (`session_id`,`owner_id`,`video_id`,`to_user_id`,`to_mobile`,`to_name`) VALUES (%s,%s,%s,%s,%s,%s)"""
					, (sessionId, userId, data['VID'], toUserId, to.get('Mobile'), to.get('Name')))
		db.end()

		if toUserId:
			# create app notify
			NotifyService.create(toUserId, Utils.json_dumps({
				'Type'	: 'share_video',
				'From'	: UserService.user_mobile(userId),
				'To'	: to.get('Mobile'),
				'Date'	: datetime.now(),
				'VID'	: data['VID'],
			}), sender = 'share_video', refId = sessionId)

		if result:
			results.append({
				'Mobile': to.get('Mobile'),
				'Signup': toUserId != None
				})

	return {'SessionId': sessionId, 'Results': results}

def __test_auth_view(userId, spaceId):
	db = MySQL()
	authorized = 0
	
	# TEST AUTHORIZE
	spaceInstance = db.get('SELECT * FROM `space` WHERE `id` = %s', (spaceId))
	if spaceInstance:
		if userId == spaceInstance['user_id']:
			authorized = 1
		else:
			authorized = db.get('SELECT COUNT(*) AS c FROM `space_authorize` WHERE `space_id`=%s AND `user_id` = %s', (data.get('Id', ''), userId))['c']
	else:
		authorized = -1

	return authorized

def user_validate(data):
	db = MySQL()
	
	code = str(randint(10000,99999))
	valid_date = datetime.now() + timedelta(seconds=180)

	#
	# TODO: 發送短信到 data['Mobile'] , 驗證碼為 code， 過期時間 90秒
	#

	result = db.save("INSERT INTO `validate` (`mobile`, `code`, `device`, `valid_date`) VALUES (%s,%s,%s,%s)", 
					(data['Mobile'], code, data['Device'], valid_date.strftime('%Y-%m-%d %H:%M:%S')))
	db.end()

	return valid_date

def space_unauthorize(data):
	userId = UserService.user_id(data['UserKey'])
	spaceInstance = space_get(data.get('Id', ''))
	if userId == spaceInstance['user_id']:
		db = MySQL()
		result = db.delete("DELETE FROM `space_authorize` WHERE `space_id`=%s AND `user_id`=%s", 
						(data.get('Id', ''), data.get('UserId', '')))
		db.end()
		if result > 0:
			return {
				'Id': data.get('Id', ''),
				'UserId': data.get('UserId', ''),
			}
		else:
			raise Exception('刪除失敗或授權不存在')
	else:
		raise Exception('沒有權限或空間不存在')

def space_authorized_resources(data):
	spaceIds = []
	if data.get('SpaceId', None):
		spaceIds.append(data.get('SpaceId'))
	else:
		ownerId = data.get('OwnerId', None)
		for space in space_authorized_spaces(data)['Results']:
			if space['OwnerId'] == ownerId:
				spaceIds.append(space['Id'])

	if len(spaceIds) > 0:
		offset = long(data.get('Offset', 0))
		sort = max(1, min(3, int(data.get('Sort', 1))))
		order = int(data.get('Order', 0))
		listMax = min(100, data.get('Max', 10))

		prefixCountSQL = 'SELECT COUNT(*) AS c FROM `space_resource` WHERE `space_id` IN (%s)' % ', '.join(list(map(lambda x: '%s', spaceIds)))
		prefixSelectSQL = 'SELECT * FROM `space_resource` WHERE `space_id` IN (%s)' % ', '.join(list(map(lambda x: '%s', spaceIds)))

		db = MySQL()
		resCount = db.get(prefixCountSQL + " AND `res_type`=%s", tuple(spaceIds) + (data.get('ResType', None),)) ['c']
		resList = db.list(prefixSelectSQL + " AND `res_type`=%s", 
							tuple(spaceIds) + (data.get('ResType', None),), sort='order_field%s'%sort, order='DESC' if order == 0 else 'ASC', offset=offset, pagesize=listMax)
		results = []
		for res in resList:
			spaceInstance = space_get(res['space_id'])
			results.append({
					'Id': res['space_id'],
					'Name': spaceInstance['name'],
					'ResId': res['res_id'],
					'OrderField1': res['order_field1'],
					'OrderField2': res['order_field2'],
					'OrderField3': res['order_field3'],
				})

		return {
			'ResType': data.get('ResType', ''),
			'Count': resCount,
			'Offset': offset,
			'Max': listMax,
			'Sort': sort,
			'Order': order,
			'Results': results,
		}
	else:
		raise Exception('沒有可訪問的空間')

def space_list(data):
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()

	spaceListInstance = db.list('SELECT * FROM `space` WHERE `user_id` = %s ORDER BY `index` ASC', (userId))
	
	results = []
	for space in spaceListInstance:
		results.append({
				'Id': space['id'],
				'Name': space['name'],
			})

	return {
		'Count': len(results),
		'Spaces': results,
	}