Python script to connect with Redshift on AWS with SCHEMA support.

redshift_connect.py

############ REQUIREMENTS ####################
# sudo apt-get install python-pip 
# sudo apt-get install libpq-dev
# sudo pip install psycopg2
# sudo pip install sqlalchemy
# sudo pip install sqlalchemy-redshift
##############################################

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker

#>>>>>>>> MAKE CHANGES HERE <<<<<<<<<<<<< 
DATABASE = "dbname"
USER = "username"
PASSWORD = "password"
HOST = "host"
PORT = ""
SCHEMA = "public"      #default is "public" 

####### connection and session creation ############## 
connection_string = "redshift+psycopg2://%s:%s@%s:%s/%s" % (USER,PASSWORD,HOST,str(PORT),DATABASE)
engine = sa.create_engine(connection_string)
session = sessionmaker()
session.configure(bind=engine)
s = session()
SetPath = "SET search_path TO %s" % SCHEMA
s.execute(SetPath)
###### All Set Session created using provided schema  #######

################ write queries from here ###################### 
query = "SELECT * FROM added_trip limit 2;"
rr = s.execute(query)
all_results =  rr.fetchall()

def pretty(all_results):
    for row in all_results :
        print "row start >>>>>>>>>>>>>>>>>>>>"
        for r in row :
            print " ----" , r
        print "row end >>>>>>>>>>>>>>>>>>>>>>"


pretty(all_results)


########## close session in the end ###############
s.close()

Thank you very much

Thanks a ton, clean and straightforward

You do great work! Thank you.

add connect_args={'sslmode': 'prefer'} to a.create_engine

connection issue attached screenshot

Print is a function in python 3 You are using python 3 . Example make print "start >>>" As print("start >>>")

…

On Mon, Aug 19, 2019, 5:19 PM Murari ***@***.***> wrote: connection issue attached screenshot [image: image] <https://user-images.githubusercontent.com/17945452/63263154-52f20400-c277-11e9-89e7-5cfa0a99d555.png> — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <https://gist.github.com/f6f1ad1a1cfd786f8e374d11fd3dbd4b?email_source=notifications&email_token=ABA34YLSOWLMFTRFDBLSFZTQFKCCZA5CNFSM4IM7GWSKYY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAFXHPK#gistcomment-3002101>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABA34YIGNYTYAZ3YZVVVPSDQFKCCZANCNFSM4IM7GWSA> .

yes sir,but connection issue redshift datatbase

How to inset data into the database using your logic. please provide example in the code. Thank you

How to inset data into the database using your logic. please provide example in the code. Thank you

query = "INSERT INTO mytable VALUES ('myvalue123');"
rr=s.execute(query)
s.commit()
s.close()

Please change the query appropriately.

I guess if the goal is to query redshift datatable, do you really need session maker? You can use Pandas read_sql after creating the engine instance as shown in the article
df = pd.read_sql('SELECT * FROM ;', engine)