MacSanhe MacSanhe - 4 months ago 17
Python Question

python pyscopg2 unable to select data from AWS redshift

I have a postgres database on AWS redshift. Currently I use Python Pyscopg2 to interact with the database. I find that I can run:

curosr.execute("INSERT INTO datatype VALUES (%s, %s)", ("humidity", "some description"))
connect.commit()


but when I do:

for row in cursor.execute("SELECT * FROM datatype"):
print(row)


what ever I do, it always returns me None Type. Anyone can give me advice that what is the correct way to interact with redshift postgres?

Thank you

As required, here's the whole code

##encoding=utf8

from __future__ import print_function
import psycopg2

def connect():
conn = psycopg2.connect(host = "wbh1.cqdmrqjbi4nz.us-east-1.redshift.amazonaws.com",
port = 5439,
dbname = "dbname",
user = "user",
password = "password")
c = conn.cursor()
return conn, c

conn, c = connect()

c.execute("INSERT INTO table netatmo VALUES (%s, %s)", (1, 10.5))
conn.commit() # this works, and I can see the data in other db client software

for row in c.execute("SELECT * FROM netatmo").fetchall(): # this not working
print(row) # AttributeError: 'NoneType' object has no attribute 'fetchall'

Answer

you missed "fetchall()", when updating - you don't need it, but when selecting - you have to fetch the results http://initd.org/psycopg/docs/cursor.html

your code should look like this:

cursor.execute("SELECT * FROM datatype;")
for row in cursor.fetchall():
    print(row)
Comments