How to iterate over Postgresql rows in a Python script?
I'm writing a script which selects from a DB table and iterates over the rows.
In MySQL I would do:
import MySQLdb
db_mysql=MySQLdb.Connect(user=...,passwd=...,db=..., host=...)
cur = db_mysql.cursor(MySQLdb.cursors.DictCursor)
cur.execute ("""SELECT X,Y,Z FROM tab_a""")
for row in crs.fetchall () :
do things...
But I don't know how to do it in PostgreSQL. Basically this question could be how to translate the above MySQL code to work with PostgreSQL.
This is what I have so far (I am using PyGreSQL).
import pg
pos = pg.connect(dbname=...,user=...,passwd=...,host=..., port=...)
pos.query("""SELECT X,Y,Z FROM tab_a""")
How do I iterate over the query results?
Answer
Retrieved from http://www.pygresql.org/contents/tutorial.html, which you should read.
q = db.query('select * from fruits')
q.getresult()
The result is a Python list of tuples, eardh tuple contains a row, you just need to iterate over the list and iterate or index the tupple.