CODEWITHSUNDEEP
sqlpython-2.7amazon-redshift
user9484383
user9484383

Reputation:

How to run VACUUM in redshift using python?

Getting error:

next(df)

TypeError: DataFrame object is not an iterator

Here goes the code.

import pandas as pd
import os
import sys
import psycopg2
import csv



def redshift():

conn=psycopg2.connect(dbname= 'sdsdf', host='rghgj', 
    port= '5439', user= 'admin', password= 'assddfd')
df=pd.read_sql('select table_name from vacuum_process where vacuum_enable=1',conn)
conn.autocommit=True
print df
next(df)

    for i in df:

         curr=conn.cursor()
         query= "vacuum  %s "% i
         curr.execute(query)
         print 'success'

redshift()

Upvotes: 1

Views: 1321

Answers (2)

Elle
Elle

Reputation: 157

You are not iterating correctly over rows in the DataFrame. several good ways to iterate over DataFrame rows can be found in this link:How to iterate over DataFrame rows

Upvotes: 0

Red Boy
Red Boy

Reputation: 5729

You're missing to set the isolation_level=0 in your connection, hence code is begining the transaction and then executing the query.

Though I don't understand full(why to select if need to run vacuum), what you're trying to do here, but I'll not go into that details. Here is simplistic code that should work for table1.

import psycopg2



def redshift():

    conn=psycopg2.connect(dbname= 'a', host='host', port= '5439', user= 'user_name', password= '****')
    conn.set_isolation_level(0)
    cur = conn.cursor();

    cur.execute('vacuum table1')
    print('success')


redshift();

Edit 13/6/2018

Code to do Vaccum for multiple tables. This could be enhanced to read table names from DB/text files/CSV/property files etc.

tables = ["sales", "sales", "tab3", "tab3"];

i = 0

while i < len(tables):
    cur.execute('vacuum ' + tables[i])

    print('success ' + tables[i])
    i += 1

Upvotes: 1

Related Questions