Accessing test PostgreSQL database from python

Question

I can't seem to correctly connect and pull from a test postgreSQL database in python. I installed PostgreSQL using Homebrew. Here's how I have been accessing the database table and value from the terminal:

xxx-macbook:~ xxx$ psql
psql (9.4.0)
Type "help" for help.

xxx=# \dn
 List of schemas
  Name  |  Owner  
--------+---------
 public | xxx
(1 row)

xxx=# \connect postgres
You are now connected to database "postgres" as user "xxx".
postgres=# SELECT * from test.test;
  coltest  
-----------
 It works!
(1 row)

But when trying to access it from python, using the code below, it doesn't work. Any suggestions?

########################################################################################
# Importing variables from PostgreSQL database via SQL commands

db_conn = psycopg2.connect(database='postgres',
                           user='xxx')

cursor = db_conn.cursor()

#querying the database
result = cursor.execute("""
Select * From test.test
""")

print "Result: ", result
>>> Result: None

It should say: Result: It works!

moooeeeep · Accepted Answer · 2015-01-06 13:23:56Z

1

You need to fetch the results.

From the docs:

The [execute()-]method returns None. If a query was executed, the returned values can be retrieved using fetch*() methods.

Example:

result = cursor.fetchall()

For reference:

Note that (unlike psql) psycopg2 wraps anything in transactions. So if you intend to issue persistent changes to the database (INSERT, UPDATE, DELETE, ...) you need to commit them explicitly. Otherwise changes will be rolled back automatically when the connection object is destroyed. Read more on that topic here:

edited Jan 6, 2015 at 13:23

answered Jan 6, 2015 at 12:25

moooeeeep

32.6k26 gold badges109 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Accessing test PostgreSQL database from python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related