如何使用Flask检查数据在BigQuery的特定列中是否存在? [英] How to check whether data exists in specific column on BigQuery with Flask?

问题描述

我一直在用Flask制作api，该api接收带有参数(luid)的HTTP请求，获取参数并将其传递给SQL语句中的Bigquery，如果luid的记录在cv_date列中包含数据，则返回True.但是，当我尝试将请求发送到具有在BigQuery表中实际存在但在其中不存在的luid的此api时，我从他们两个都接受了True.如果要使用BigQuery中不存在的luid参数，我想使其返回False.尝试和例外似乎效果不好.谁能给我个主意?我为我的拙劣的代码感到抱歉.如果缺少任何信息，请告诉我.非常感谢.

I've been making api with Flask that receives a HTTP request with parameters(luid), takes the parameters and passes them to Bigquery within SQL statement and if the record of luid has data in cv_date column, it returns True. However, when I tried sending request in order to this api with luids that really exists in BigQuery table and does not exists in there , I accepted True from both of them . I want to make it to return False , if it takes luid parameter that doesn't exist in BigQuery. It seems try and exception don't work well . Could anyone give me idea ?? I'm so sorry for my poor code . If there is lack of any info , plese let me know . Thank you so much .

此外，我已经设置了export GOOGLE_APPLICATION_CREDENTIALS = Json.file并将IAM分配给该服务帐户.我通过gcloud应用程序日志尾部-s测试成功将SQL发送到BigQuery.

besides, I have already set export GOOGLE_APPLICATION_CREDENTIALS = Json.file and assign IAM to this service account. I succeeded in sending SQL to BigQuery by gcloud app logs tail -s test.

我引荐的网站如下 https://blog. morizyun.com/python/library-bigquery-google-cloud.html 接收具有变量，查询BQ和返回响应的HTTP请求

/home/user/api_dev/main.py

from flask import Flask, request
from google.cloud import bigquery


app = Flask(__name__)


@app.route('/')
def get_request():
    # luid = request.args.get('luid') or ''
    client = bigquery.Client()
    query = """SELECT EXISTS(SELECT cv_date
FROM `test-266110.conversion_log.conversion_log_2020*`
WHERE luid = `test-266110.conversion_log.conversion_log_2020*`.luid limit 1000)"""

    try:
        query_job = client.query(query)
        is_exist = len(list(query_job.result())) >= 1
        return "True"
    except:
        return "False"


if __name__ == "__main__":
    app.run()

BigQuery 

luid | pgid | cv_date | orderid 
Uxxxx| 1111 | 2020-08-01| 2222　if this luid, it returns True 
Uxxxx| 2222 | 　　　　　|        if this luid, it returns False 

推荐答案

当您执行与数据库中任何条目都不匹配的SELECT时，不会引发任何错误，因此不会执行您的except:.仅当从try:部分引发异常时，您的代码才会返回False.

When you perform a SELECT that does not match any entry in the DB, no error is thrown, therefore your except: is not executed. Your code returns False, only if an exception is thrown from your try: section.

您的逻辑似乎正确:is_exist应该用>=运算符返回的True或False值初始化.您只需要返回该值，而不是总是返回True.此外，您可以保留except:并假定如果在进程中引发异常，则没有条目与select语句匹配:

Your logic seems correct: is_exist shall be initialized with the True or False value returned by the >= operator. You just need to return that value instead of always returning True. More over, you can keep the except: and assume that no entry matched the select statement if an exception is thrown in the process:

[...]
    try:
        query_job = client.query(query)
        is_exist = len(list(query_job.result())) >= 1
        return str(is_exist)
    except:
        return False
[...]

这篇关于如何使用Flask检查数据在BigQuery的特定列中是否存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！