How to retrieve data using a list of columns from a table in psql-CodePudding

I am using python and PSQL to extract data but what I would want to do is to retrieve data from a specific table using a list that contains the name of desired columns.

*args could have any number of names inside it so I was wondering how to do it.

Function to read table with a given query:

def read_table_data(table_name, query, conn):
    table_name = pd.read_sql_query(query, conn)
    return table_name

Function to extract the data:

def extract_variable_table_many_columns(table, *args):   
    df = pd.DataFrame()
    col = []
    for c in args:
        print(c)
        col.append(c)
    query_extract_variables = "SELECT {} FROM {};".format(str(col),str(table))
    df1 = read_table_data("{}".format(str(table)), query_extract_variables , conn)
    df = df.append(df1)
    return df

CodePudding user response：

I want to retrieve data from a specific table using a list that contains the names of desired columns.

So you want to dynamically construct your actual SQL statement, not just the usual parameter substitution. Since using string concatenation to build a SQL query is disaster waiting to happen, you should use the psycopg2.sql module. It provides a safe way to use identifiers (i.e. column names) from variables.

For more details see Passing table name as a parameter in psycopg2.

CodePudding user response：

Thanks again @SebDieBln, I was able to solve it as the following:

 qurey = sql.SQL("select {fields} from {table}").format(fields=sql.SQL(',').join([sql.Identifier(n) for n in columns]),table=sql.Identifier(table))