Return SQL Query results as a variable in python

Question

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!

Niki van Stein · Accepted Answer · 2015-10-06 17:36:33Z

1

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)

answered Oct 6, 2015 at 17:36

Niki van Stein

10.8k3 gold badges33 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3140412 Over a year ago

Hi, thanks for the response! This, unfortunately, results in a syntax error as it looks like it encapsulates single quotes to each variable. The command ends up looking like ALTER TABLE 'table_name'.'schema' OWNER TO 'new_owner'. Apparently redshift doesn't like that formatting...

Clodoaldo Neto · Accepted Answer · 2015-10-06 17:39:32Z

0

AsIs

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

answered Oct 6, 2015 at 17:39

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

1 Comment

user3140412 Over a year ago

Hello, Thanks for the response! I had to make a change to the for loop so that the command iterates over each table name, but it works like a charm.

Collectives™ on Stack Overflow

Return SQL Query results as a variable in python

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related