gcloud - Spanner not executing query with params in python - Stack Overflow

admin管理员组

文章数量:1435859

I'm making an iterative query using spanner and python, what I do is paginating over the data using LIMIT and OFFSET, these parameters I pass it through the execute_sql method and the params parameter.

When I execute my code it doesn't execute, It just get's stuck and doesn't show any error or message.

def print_test(namespace, field_id):
    try:
        table_id = f"{namespace.lower().strip()}_{field_id.lower().strip()}"
        page_size = 50000
        offset_param = 0
        while True:
            with database.snapshot() as snapshot:
                params = {
                    "offset_param": offset_param,
                    "page_size": page_size
                }
                query = f"""
                    SELECT
                        P.ML_ID,
                        S.VALUE,
                        S.SITE,
                        S.EXECUTION_ID
                    FROM `sync_{table_id}` S
                    INNER JOIN `people` P ON P.CUST_ID = CAST(S.VALUE AS INT64)
                    WHERE S.ML_ID IS NULL
                    LIMIT @page_size
                    OFFSET @offset_param;
                """
                params_types = {
                    "offset_param": spanner.param_types.INT64,
                    "page_size": spanner.param_types.INT64
                }
                print(params)
                print(query)
                results = snapshot.execute_sql(query, 
                    params=params,
                    param_types = params_types
                    )
                records_to_process = list(results)
                if not records_to_process:
                    break
                print(records_to_process)
                offset_param += page_size
    except Exception as e:
        print(f"Error occurred: {e}")

print_test(namespace, field_id)

I tried to execute the code passing the OFFSET an LIMIT parameters value directly to the query, and it executes successfully, but as far as I know it's more safe to pass the parameters through the execute_sql method.

Also I specified the params_types explicitly to make sure that the method handle the right params types.

It's rare becase I've already tested it using other queries and it execute successfully using the params properly.

Also for testing purposes I changed the INNER JOIN statement of the query for LEFT JOIN and it works perfectly.

I'm not sure if there's an specific issue with the params that goes to the method or if it's related to the INNER JOIN, I can execute the query successfully on spanner's console and it returns results.

I'm making an iterative query using spanner and python, what I do is paginating over the data using LIMIT and OFFSET, these parameters I pass it through the execute_sql method and the params parameter.

When I execute my code it doesn't execute, It just get's stuck and doesn't show any error or message.

def print_test(namespace, field_id):
    try:
        table_id = f"{namespace.lower().strip()}_{field_id.lower().strip()}"
        page_size = 50000
        offset_param = 0
        while True:
            with database.snapshot() as snapshot:
                params = {
                    "offset_param": offset_param,
                    "page_size": page_size
                }
                query = f"""
                    SELECT
                        P.ML_ID,
                        S.VALUE,
                        S.SITE,
                        S.EXECUTION_ID
                    FROM `sync_{table_id}` S
                    INNER JOIN `people` P ON P.CUST_ID = CAST(S.VALUE AS INT64)
                    WHERE S.ML_ID IS NULL
                    LIMIT @page_size
                    OFFSET @offset_param;
                """
                params_types = {
                    "offset_param": spanner.param_types.INT64,
                    "page_size": spanner.param_types.INT64
                }
                print(params)
                print(query)
                results = snapshot.execute_sql(query, 
                    params=params,
                    param_types = params_types
                    )
                records_to_process = list(results)
                if not records_to_process:
                    break
                print(records_to_process)
                offset_param += page_size
    except Exception as e:
        print(f"Error occurred: {e}")

print_test(namespace, field_id)

I tried to execute the code passing the OFFSET an LIMIT parameters value directly to the query, and it executes successfully, but as far as I know it's more safe to pass the parameters through the execute_sql method.

Also I specified the params_types explicitly to make sure that the method handle the right params types.

It's rare becase I've already tested it using other queries and it execute successfully using the params properly.

Also for testing purposes I changed the INNER JOIN statement of the query for LEFT JOIN and it works perfectly.

I'm not sure if there's an specific issue with the params that goes to the method or if it's related to the INNER JOIN, I can execute the query successfully on spanner's console and it returns results.

• python-3.x
• gcloud
• google-cloud-spanner

Share Improve this question Follow asked Nov 16, 2024 at 16:37 Miller LizarazoMiller Lizarazo 1111 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

If the exact same code returns data when you use a LEFT JOIN and query parameters, and returns no results when you use an INNER JOIN, then I suspect that the problem is not directly with your code or using query parameters, but with the data in your table. The table sync_{table_id} probably does not contain any data that has a value equal to a customer ID, which causes the query to return zero rows.

I would double check your other verifications:

1. Are you absolutely sure that you used the same table_id and field_id when you tried the query in the Spanner console?
2. Are you absolutely sure that you did not use a LEFT JOIN when you tried the query in the Spanner console?

本文标签： gcloudSpanner not executing query with params in pythonStack Overflow