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

Answer 1

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:

1. You used fetchone() instead of fetchall() so it's clear that you'll only ever get one result; that's what you asked for.
2. 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.