CODEWITHSUNDEEP
pythonpostgresqlpsycopg2amazon-redshift
user3140412
user3140412

Reputation: 97

Return SQL Query results as a variable in python

So, I'm working on a python scrip to fetch all rows from a specific column in redshfit. The idea is to then loop through each item from the column in a second SQL command to make alterations. For example, I'm thinking of pulling the table names from a specific schema, then executing an ALTER TABLE to change the owner of each table. Here's the code I've got:

def alter_table(schema,new_owner):
    select = "SELECT talbe_name FROM information_schema.tables WHERE talbe_schema = %s"
    args = (schema,)
    cur.execute(select,args)
    for table_name in cur:
        print table_name
        alter = "ALTER TABLE %s.%s OWNER TO %s"
        args2 = (table_name,schema,new_owner)
        cur.execute(alter,args2)
        cur.conn.commit()

Now, this almost works. The issue I'm having is passing the "table_name" from the select statement to the alter statement. The select statement gives the following output in the print command:

('TABLE_NAME',)

Which then gets passed to the alter statement like:

 ALTER TABLE ('TABLE_NAME',).schema OWNER TO new_Owner

The alter statement then fails with:

 syntax error at or near "("

What I think needs to happen is the special characters from the select statement need to be removed so that TABLE_NAME is passed down without them. Apparently my Googling skills are missing something so any ideas will be helpful. Thanks!

Upvotes: 0

Views: 1130

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125404

AsIs

args2 = (AsIs(schema), AsIs(table_name[0]), AsIs(new_owner))

Upvotes: 0

Niki van Stein
Niki van Stein

Reputation: 10734

You should use table_name[0], because table_name is a tuple and the string you want is at index 0.

So make it like

args2 = (table_name[0],schema,new_owner)

Upvotes: 1

Related Questions