Problem when making inquiries in psycopg2

Question

Problem when making inquiries in psycopg2

Navigation

#1 by (1 votes)

0

good day I'm working on a project where I need to consult different databases and for that I use the psycopg2 extension of python, however at the time of making a query it only shows me the first data of it , despite the fact that there is a lot of repeated data

    import psycopg2
    import psycopg2.extensions
    import sys

    def main():
        proyecto="host='localhost' dbname='api' user='postgres' password='123456789'"
        obj= psycopg2.connect(proyecto)
        cur=obj.cursor()
        cura= "SELECT * FROM public.\"pensum\" WHERE \"Id.Carrera\" = %s "
        a= 1
        cur.execute(cura,[a])
        a2=cur.fetchone()
        print(a2)

    main()

which only results in me

    id.carrera |        clase
    -----------------------------
    ( 1         ,'TRABAJO DE GRADO')

However, when doing the SQL query, all the results are bounced:

    id.carrera |        clase
    -----------------------------
    1          ;"TRABAJO DE GRADO"
    1          ;"CATEDRA UDECINA"
    1          ;"CONSTITUCION Y DEMOCRACIA"
    1          ;"DEPORTES"

I would like to know why with python only one result is thrown and how to make me show all the data I need. thanks

sql python python-3.x postgresql

asked by kass 03.05.2017 в 22:37

source

1 answer

Help script listing disks Stored procedure from MYSQL to SqlServer

score 1 · Answer 1

You are using fetchone that returns only one row of the query result of each time. To obtain the complete query you have several possibilities:

Use fetchall :
```
cur.execute(cura,[a])
for a2 in cur.fetchall()
    print(a2)
```
Logically you should be careful with the memory you will end up using if the query returns a large number of rows.

Use fetchmany so that for each iteration you only get a maximum of x rows (5 in this example):

cur.execute(cura,[a])
while True:
    rows = cur.fetchmany(5)
    if not rows:
        break

    for row in rows:
        print(row)

Use fetchone but in a cycle:

cur.execute(cura,[a])
a2 = cur.fetchone()
while (a2 != None):
   print (a2)
   registro = cur.fetchone()

Since the cursor object is iterable we can iterate directly on it without using any method:
```
for a2 in cur.execute(cura,[a]):
    print (a2)
```