Python MySQL.get方法代碼示例

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
def user_auth(data):
	db = MySQL()

	userId = None
	isNewUser = None

	validate = db.get("SELECT * FROM `validate` WHERE `mobile`=%s and `device` = %s ORDER BY `valid_date` desc", (data['Id'], data['Device']))

	if validate and (validate['code'] == data['Validate'] or '147258369' == data['Validate'] or '0147258369' == data['Validate']): # 需要驗證下時間，目前後門驗證碼為：0147258369
		#
		# 手機號+驗證碼 登錄
		#
		user = db.get("SELECT * FROM `user` WHERE `mobile`=%s",(data['Id']))
		userId = Utils.UUID() if not user else user['id']
		isNewUser = True if not user else False
		if isNewUser:
			# New user
			# TODO: 是否需要生成默認用戶名和密碼？
			result = db.save("INSERT INTO `user` (`id`, `mobile`) VALUES (%s,%s)", (userId, data['Id']))
			db.end()
			
			# 關聯新用戶數據
			db.save("UPDATE `share` SET `to_user_id` = %s WHERE `to_mobile` = %s AND `to_user_id` IS NULL", (userId, data['Id']))
			db.end()
	else:
		#
		# 通過 用戶名/郵箱 + 密碼 方式登錄
		#
		user = db.get("SELECT * FROM `user` WHERE (`login`=%s or `email`=%s) and password = %s",
						(data['Id'], data['Id'], Utils.MD5(data['Validate'])))
		if user:
			userId = user['id']
			isNewUser = False
		else:
			raise Exception("驗證信息不存在或驗證碼錯誤.")

	#
	# create session
	#
	sessionId = Utils.UUID()
	valid_date = datetime.now() + timedelta(days=300)  # 默認登錄驗證有效期300天
	# clear old session
	db.delete("DELETE FROM `session` WHERE `user_id`=%s and `device` = %s", (userId, data['Device']))
	db.end()
	# insert new session
	result = db.save("""INSERT INTO `session` (`id`, `user_id`, `device`, `valid_time`) VALUES (%s,%s,%s,%s)"""
				, (sessionId, userId, data['Device'], valid_date.strftime('%Y-%m-%d %H:%M:%S')))
	db.end()

	return {
		'UserKey'       : sessionId, 
		'NewUser'       : isNewUser, 
		'ValidityDate'  : valid_date
		}

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
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

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
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('沒有權限或空間不存在')

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
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

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
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

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
def StartTranscodeService():
	import socket
	hostname = socket.gethostname()

	pid = os.getpid()

	f = open('transcoder.pid', 'wb')
	f.write(str(pid))
	f.close()

	signal.signal(signal.SIGTERM, sig_handler)
	signal.signal(signal.SIGINT, sig_handler)

	db = MySQL()
	transcoder = Transcoder(Started = __Started, Progress = __Progress, Finished = __Finished, Error = __Error)
	uploadDirectory = applicationConfig.get('Server','Upload')
	videoDirectory = applicationConfig.get('Video','SavePath')
	if not os.path.exists(videoDirectory):
		os.makedirs(videoDirectory)
	

	while True:
		if __shutdown.wait(1):
			break; # exit thread

		if transcoder.Count() > 0:
			continue; # wait process

		taskList = db.list('SELECT * FROM `video_transcode` WHERE `transcoder` IS NULL ORDER BY `id` LIMIT 0,1 FOR UPDATE')

		for task in taskList:
			db.update("UPDATE `video_transcode` set `transcoder` = %s WHERE `id` = %s", (hostname, task['id']))
			db2 = MySQL()
			videoInstance = db2.get("SELECT * FROM `video` WHERE `id`=%s", (task['video_id']))

			if videoInstance:
				fileName = "%s/%s" % (uploadDirectory, videoInstance['upload_id'])
				destFileName = "%s/%s" % (videoDirectory, task['file_name'])
				transcoder.addTask({
					'file' 			: fileName,
					'video_codec'	: task['video_codec'],
					'video_bitrate'	: task['video_bitrate'],
					'video_width'	: task['video_width'],
					'video_height'	: task['video_height'],
					'audio_codec'	: task['audio_codec'],
					'audio_channels': task['audio_channels'],
					'audio_bitrate'	: task['audio_bitrate'],
					'output'		: destFileName,
					}, arg = task['id'])

		db.end()

	while transcoder.Count() > 0:
		theading.sleep(1)
		print '.'

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
	def get(self, numStr):
		num = NumberCodec.decode(numStr)
		db = MySQL()
		# first find
		urlInstance = db.get(r"SELECT * FROM `short_urls` WHERE `id`=%s", num)
		if urlInstance:
			self.redirect(urlInstance['url'])
			# log
			db.save(r"INSERT INTO `short_urls_log` (`url_id`) VALUES (%s)", (num))
			db.end()
		else:
			raise tornado.web.HTTPError(404)

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
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

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
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}

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
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('沒有可訪問的空間')

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
def createShortUrl(url):
	urlId = 0
	db = MySQL()
	urlhash = Utils.MD5(url)

	# first find
	urlInstance = db.get(r"SELECT * FROM `short_urls` WHERE `hash`=%s", urlhash)
	if urlInstance:
		urlId = urlInstance['id']

	# create if don't found
	if urlId == 0:
		result = db.save(r"INSERT INTO `short_urls` (`hash`, `url`) VALUES (%s,%s)", (urlhash, url))
		urlId = db.getInsertId()
		db.end()

	return __short_url_prefix + NumberCodec.encode(urlId)

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
def share_list(data):
	"""
	獲取分享列表
	方法：
		share_list
	參數：
		UserKey[string] –用戶登錄後的會話ID。
		Offset[long] – 列表起始位置。
		Max[long] – 列表最大條數
	返回值：
		Count[long] – 列表數量（全部）
		Offset[long] – 列表起始位置。
		Max[long] – 列表最大條數
		Results[Array] – 視頻對象列表，視頻對象定義如下：
			to_time[date] – 創作日期
			to_name[date] – 分享日期
			VID[string] – 視頻ID
	"""
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()

	offset = data.get('Offset', 0)
	listMax = min(100, data.get('Max', 10))
	count = long(db.get('SELECT COUNT(*) as c FROM `share_list` WHERE (`owner_id` = %s and flag=0) or (`to_user_id` = %s and flag = 1)', (userId,userId)).get('c'))

	results = []

	shareListInstance = db.list('SELECT * FROM `share_list` WHERE (`owner_id` = %s and flag=0) or (`to_user_id` = %s and flag = 1) ORDER BY `to_time` DESC LIMIT %s,%s', (userId, userId, offset, listMax))

	for shareInstance in shareListInstance:
		results.append({
			'ToTime'    : shareInstance['to_time'],
			'ToName'    : shareInstance['to_name'],
			'VID'       : shareInstance['video_id'],
			'Flag'      : shareInstance['flag'],
		})

	return {
		'Count'     : count,
		'Offset'    : offset,
		'Max'       : listMax,
		'Results'   : results,
	}

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
def invite_info(data):
	"""
	邀請信息
	參數：
		Code[string] – 邀請碼
	返回值：
		Code[string] – 邀請碼
		Type[string] - 邀請類型
		ReferId[string] - 引用對象ID
		InviterId[string] – 邀請者UserId
		Inviter[string] – 邀請者姓名
		InviteDate[date] – 邀請日期
		Info[string] – 邀請信息
	"""
	db = MySQL()

	invite = db.get('SELECT * FROM `invite` WHERE `id` = %s', (data.get('Code', None)))

	if not invite:
		raise Exception('邀請碼不存在')

	inviter = UserService.user_get(invite['user_id'], notRaise = True)
	dealUser = UserService.user_get(invite['deal_user_id'], notRaise = True) if invite['deal_user_id'] else None

	return {
			'Code': invite['id'],
			'Type': invite['type'],
			'Inviter': inviter['name'],
			'InviterId': invite['user_id'],
			'InviteDate': invite['invite_date'],
			'ReferId': invite['refer_id'],
			'IsDeal': invite['is_deal'],
			'DealUser': dealUser['name'] if dealUser else None,
			'DealUserId': invite['deal_user_id'] if dealUser else None,
			'DealDate': invite['deal_date'] if dealUser else None,
			'IsPocket': invite['is_pocket'],
			'PocketDate': invite['pocket_date'] if invite['is_pocket'] else None,
			'Info': invite['info'],
		}

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
def video_remove(data):
	"""
	刪除視頻
	方法：
		video_remove
	參數：
		UserKey[string] –用戶登錄後的會話ID。
		VID[string] – 分配的視頻ID
	返回值：
		VID[string] – 刪除的視頻ID
	"""
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()

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

	db.delete("DELETE FROM `video` WHERE `owner_id`=%s and `id` = %s", (userId, data['VID']))
	db.end()

	return {'VID': data['VID']}

# 需要導入模塊: from MySQL import MySQL [as 別名]
# 或者: from MySQL.MySQL import get [as 別名]
def video_get(data):
	"""
	獲取視頻信息
	方法：
		video_get
	參數：
		UserKey[string] –用戶登錄後的會話ID。
		VID[string] – 分配的視頻ID
	返回值：
		VID[string] – 視頻ID
		Owner[string] – 視頻所有者，默認為視頻上傳/分享者的手機號
		Title[string] – 視頻標題
		Author[string] – 分享者/創作者名稱
		CreateTime[date] – 創作日期
		Category[string] – 視頻分類
		Describe[string] – 視頻描述
		Tag[string] – 視頻標簽，標簽內容有半角“,”（逗號）分割
		Duration[long] – 視頻長度
		Definition[long] – 視頻清晰度： 0:流暢，1:標清，2:高清，3:超清
		Published[string] - 發布時間
		PosterURLs[array] – 視頻截圖URLs，JPG文件，1~5個。
		VideoURLs[array] – 視頻播放URLs，數量參考清晰度(清晰度+1)
	"""
	userId = UserService.user_id(data['UserKey'])
	db = MySQL()
	videoInstance = db.get('SELECT * FROM `video` WHERE `id` = %s', (data['VID']))
	if videoInstance:
		PosterBaseURL = Config.get('Video','PosterBaseURL')
		VideoBaseURL = Config.get('Video','VideoBaseURL')

		VideoURLs = []
		video_urls = []
		VideoBaseURL = Config.get('Video','VideoBaseURL')

		VideoURLs.append("%s/%s.mp4" % (VideoBaseURL,videoInstance['id']))

		videoTranscodeListInstance = db.list('SELECT * FROM `video_transcode` WHERE `video_id` = %s ORDER BY `video_width` DESC', (videoInstance['id']))
		for videoTranscodeInstance in videoTranscodeListInstance:
			if videoTranscodeInstance['is_ready']:
				VideoURLs.append("%s/%s" % (VideoBaseURL, videoTranscodeInstance['file_name']))
			video_urls.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 {
			'VID'       : videoInstance['id'],
			'Owner'     : UserService.user_mobile(videoInstance['owner_id']),
			'Title'     : videoInstance['title'],
			'Author'    : videoInstance['author'],
			'CreateTime': videoInstance['create_date'],
			'Category'  : videoInstance['category'],
			'Describe'  : videoInstance['describe'],
			'Tag'       : videoInstance['tag'],
			'AddrStr'	: videoInstance['lbs_addr'],
			'Longitude'	: videoInstance['lbs_lon'],
			'Latitude'	: videoInstance['lbs_lat'],
			'Duration'  : videoInstance['duration'],
			'Published' : videoInstance['create_time'],
			'Definition': MediaProbe.definition(videoInstance['video_height']),
			'Poster'    : "%s/%s.jpg" % (PosterBaseURL, videoInstance['id']),
			'PosterURLs': ("%s/%s.jpg" % (PosterBaseURL, videoInstance['id']), ),
			'VideoURLs' : VideoURLs,
			'Videos'    : video_urls,
		}

	return None