psycopg2 Only Displays First Row of Postgresql Database

Question

I am running Python3.5 and Psycopg2 2.6 in a tkinter window. I have this function in a file called backend.py

import psycopg2


def search(year=0, month=0):
    '''Search the database for entries.'''
    conn = psycopg2.connect("dbname='birth_years'")
    cur = conn.cursor()
    cur.execute("SELECT * FROM birthdays WHERE year=year OR month=month;")
    row = cur.fetchone()
    conn.close()
    return row

I have frontend.py with this code.

from tkinter import *
import backend


def search_command(year, month):
    '''Run queries on the database.'''
    listbox1.delete(0, END)
    row = backend.search(year_text.get(), month_text.get())
    listbox1.insert(END, row)

window = Tk()
window.wm_title("Birthdays")
window.resizable(width=False, height=False)

Grid.rowconfigure(window, 0, weight=1)
Grid.columnconfigure(window, 0, weight=1)

b1 = Button(window, text="Search", width=15, command=search_command)
b1.grid(row=2, column=5)


window.mainloop()

Database:

    id year month

    1 1999 2
    2 2005 5
    3 1987 11
    4 1988 12
    5 1978 10

The postgresql database contains this data. PGAdmin2 is able to run the query fine and select only the row that I want. When I run a query for the year 1988 using the function all I get in the first row of the database.

1 1999 2

Errr, yeah, because you use row = cur.fetchone() and not fetchall(). You explicitly ask it to only return one value. — roganjosh
– roganjosh, Commented Jan 29, 2018 at 21:14
Although there is something else to the question, since you apparently query for 1988 and get back a result for 1999? Is that a typo or a real issue? — roganjosh
– roganjosh, Commented Jan 29, 2018 at 21:26
It does not matter if I use cur.fetchone() or cur.fetchall(), I get the same result. It only fetches the first row of the database. No typos, if I search for 2005 I get 1999. This does not make sense. — tluafed
– tluafed, Commented Jan 29, 2018 at 22:14

roganjosh · Accepted Answer · 2018-01-29 22:42:27Z

I think this is a logic error but I can't test specifically to be sure.

Your query is currently:

cur.execute("SELECT * FROM birthdays WHERE year=year OR month=month;")
row = cur.fetchone()

There's two issues with this:

You used fetchone() instead of fetchall() so it's clear that you'll only ever get one result; that's what you asked for.
Your query is just a simple string. You haven't actually passed any arguments to your function into the query. Every row has a value in year that is equal to that value in year (you're asking each value to compare itself to itself) meaning your query is always True for all rows (so you'll pull the whole database). Caveat: There may be NaN-type values where this is not true, but on-the-whole it is.

Try:

cur.execute("SELECT * FROM birthdays WHERE year=%s OR month=%s;", (year, month))
row = cur.fetchone()

You may need to enclose the %s placeholders in quotation marks, I can't test and don't know your database setup. This is a parameterized query, where year and month are dynamic based on the values passed to the function. You could do this through string formatting (you'll see a lot of people doing this in questions here) but that really exposes you to SQL injection: remember Bobby Tables.

Collectives™ on Stack Overflow

psycopg2 Only Displays First Row of Postgresql Database

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