SQLAlchemy query AWS Redshift database with python

Ebeb

·

Follow

May 11, 2023

--

Listen

Share

First install psycopg2:
$ conda install psycopg2
 
$ vi example-sql-redshift.py

### python script start: sqlalchemy to select rows from AWS Redshift
####################################################################
 
import psycopg2
import sqlalchemy as db
 
engine = db.create_engine('postgresql://userid:password@xxxxxxxx.us-west-1.redshift.amazonaws.com:5439/dbname')
connection = engine.connect()
print("after engine.connect")
 
result = connection.execute("select * from public.tablename123 limit 20;")
print("after execute select * from table")
 
print(result, type(result))
print("rowcount", result.rowcount)
#for row in result:
#    print (row)
print(result.fetchall())
 
connection.close()
print("after connection.close...exiting")
#####################################################################
 
RUN THE PYTHON SCRIPT:
-------------------------------------
$ python example-sql-redshift.py

You should see results:
after engine.connect
after execute select * from
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x7f3e8e123450> <class 'sqlalchemy.engine.cursor.LegacyCursorResult'>

rowcount 20
[(row1,abc),
 (row2,def)
...........]