How to run VACUUM in redshift using python?

Question

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()

Red Boy · Answer 1 · 2018-06-13T12:49:15.470

1

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

edited Jun 13 '18 at 12:49

answered Jun 13 '18 at 09:08

Red Boy

5,429
3
28
41

To automate the vacuum process ,i am creating this code. yeah now i cleared that error but getting different error now. – Jun 13 '18 at 09:29
TypeError: DataFrame object is not an iterator – Jun 13 '18 at 09:30
OK, if it works, you could accept this answer. If other problem, you may want to ask different question. – Red Boy Jun 13 '18 at 09:31

score 0 · Answer 2 · answered Jun 13 '18 at 19:50

0

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

answered Jun 13 '18 at 19:50

Elle

117
1
4

How to run VACUUM in redshift using python?

2 Answers2