How to avoid the hard coded string values in python function

user_18114375 0
Viewed 182

I have written the below python function to create sql statements which i want to run inside google's BigQuery platform. When i look into the code, i see that many string literals are redundant, for example the usage of "_stg". How can i avoid these hard coded string values and redundant code? Also, is it normal to hard code sql queries like this in python code or is there a more better pythonic way of doing this?

def create_main_bq_table(database, table, dc, filter_column, cmt):
    try:
        create_statement = "create table if not exists " + database + "." + table
        select_statement = " partition by dt as select *,"
        from_statement = "from " + database + "_stg." + table
        if filter_column != 'none':
            if dc == '-1':
                query = create_statement + select_statement + "date(" + filter_column + ") as dt " + from_statement + "_stg"
            else:
                query = create_statement + "_" + dc + select_statement + "date(" + filter_column + ") as dt " + from_statement + "_" + dc + "_stg"

        elif filter_column == 'none' and cmt != 'snapshot':
            if dc == '-1':
                query = create_statement + select_statement + "date(last_run_time) as dt " + from_statement + "_stg"
            else:
                query = create_statement + "_" + dc + select_statement + "date(last_run_time) as dt " + from_statement + "_" + dc + "_stg"

        else:
            if dc == '-1':
                query = "create or replace table  " + database + "." + table + " as select * from " + database + "_stg." + table + "_stg"
            else:
                query = "create or replace table  " + database + "." + table + "_" + dc + " as select * from " + database + "_stg." + table + "_" + dc + "_stg"


    except Exception as e:
        print(e)
        raise
google-bigquery python-3.x python
1 Answers

I don't believe you can completely remove the hardcoded elements -- but here is a way to reduce the number of times you had them.

I wouldn't say this solution is more pythonic, but it does at least follow the principles of DRY and other best coding practices to reduce a lot of the strings. It seemed like you were essentially doing similar statements just with some tweaks.

def sql_query(create_statement, dc, select_statement, filter_statement, from_statement):
    if dc == '-1':
        return f"{create_statement}{select_statement}{filter_statement}{from_statement}_stg"
    else:
        return f"{create_statement}_{dc}{select_statement}{filter_statement}{from_statement}_{dc}_stg"


def create_main_bq_table(database, table, dc, filter_column, cmt):
    create_statement = f"create table if not exists {database}.{table}"
    select_statement = " partition by dt as select *,"
    from_statement = f"from {database}_stg.{table}"
    
    if filter_column != 'none':
        query = sql_query(create_statement, dc, select_statement, f'date({filter_column}) as dt ', from_statement)
    elif filter_column == 'none' and cmt != 'snapshot':
        query = sql_query(create_statement, dc, select_statement, f'date(last_run_time) as dt ', from_statement)
    else:
        query = sql_query(f"create or replace table  {database}.{table}", dc, ' as select * ', '', from_statement)

removed the try catch block because I didn't see why it would cause an error since it is string creation (you might have to try that on your end)

Another thing would be to add type hinting (couldn't do, because unsure as to all of the types of the function variables)

user_74518920
Related