How to iterate over Postgresql rows in a Python script?

Question

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?

A two second search in google yield this: pygresql.org/contents/tutorial.html — Loïc Faure-Lacroix
– Loïc Faure-Lacroix, Commented Apr 14, 2016 at 7:41
My bad linking to plpgsql docs, but what DB-API module is that? For what I know psycopg for example supports cursors nicely. — Ilja Everilä
– Ilja Everilä, Commented Apr 14, 2016 at 7:41
@LoïcFaure-Lacroix Where do you see there option to do a for loop over the results of query? — java
– java, Commented Apr 14, 2016 at 7:43
@AnttiHaapala pg is a package for python. You can see it also in the link Loïc Faure-Lacroix gave... first row there is from pg import DB — java
– java, Commented Apr 14, 2016 at 7:44

Michał Niklas · Accepted Answer · 2016-04-14 10:59:39Z

2

I think it is the same, you must create cursor, call some fetch and iterate just like in MySQL:

import pgdb
pos = pgdb.connect(database=...,user=...,password=...,host=..., port=...)
sel = "select version() as x, current_timestamp as y, current_user as z"
cursor = db_conn().cursor()
cursor.execute(sel)
columns_descr = cursor.description
rows = cursor.fetchall()
for row in rows:
    x, y, z = row
    print('variables:')
    print('%s\t%s\t%s' % (x, y, z))
    print('\nrow:')
    print(row)
    print('\ncolumns:')
    for i in range(len(columns_descr)):
        print('-- %s (%s) --' % (columns_descr[i][0], columns_descr[i][1]))
        print('%s' % (row[i]))
    # this will work with PyGreSQL >= 5.0
    print('\n testing named tuples')
    print('%s\t%s\t%s' % (row.x, row.y, row.z))

edited Apr 14, 2016 at 10:59

answered Apr 14, 2016 at 7:46

Michał Niklas

54.5k19 gold badges76 silver badges125 bronze badges

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

5 Comments

java Over a year ago

it says AttributeError: cursor on the first line. pos doesn't have cursor() from what I can tell.

Michał Niklas Over a year ago

OK. You use classic pygres API. I think it is better to use it's DB API 2.0 interface. To do it you must import pgdb and change some names of parameters to connect().

java Over a year ago

Will I then be able to access data as : row['x'] rather than row[0]? (Like Dictonary). In MySQL I can do: connection.cursor(dictionary=True) don't know what is the equivelent here.

Michał Niklas Over a year ago

I don't think so. It is Python DB-API where you can read column name, type, etc. from cursor.description, see: python.org/dev/peps/pep-0249/#cursor-attributes and my edited code example

Michał Niklas Over a year ago

With PyGreSQL >= 5.0 rows are named tuples, so you can use row.x

João Pinto · Accepted Answer · 2016-04-14 07:53:57Z

1

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.

answered Apr 14, 2016 at 7:53

João Pinto

5,6475 gold badges23 silver badges36 bronze badges

2 Comments

java Over a year ago

Can it be changed to a dictonary? So I can access by column names rather than column indxes?

Elad Over a year ago

@java change it to: q.dictresult()

Collectives™ on Stack Overflow

How to iterate over Postgresql rows in a Python script?

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related