-1

In this code I'm trying to filter my database to get certain months clicked by the user and it is held by a variable called current_month_value. Whenever I query the database with curso.execute("SELECT * FROM page3 WHERE Month=current_month_value "). It gives an error that no such column exist, please what can I modify this query to get only the clicked month.

from kivy.uix.screenmanager import ScreenManager,Screen
from kivy.app import App
from kivy.lang import Builder
from kivy.properties import ObjectProperty
from kivy.uix.recycleview import RecycleView
from kivy.uix.recycleview.views import RecycleDataViewBehavior
from kivy.uix.label import Label
from kivy.properties import BooleanProperty
from kivy.uix.recycleboxlayout import RecycleBoxLayout
from kivy.uix.behaviors import FocusBehavior
from kivy.uix.recycleview.layout import LayoutSelectionBehavior
from kivy.properties import BooleanProperty, ListProperty, ObjectProperty, StringProperty
from kivy.uix.recyclegridlayout import RecycleGridLayout

from kivy.uix.button import Button
import sqlite3 as sql
box=[]
butto=""
butt=""

# Paths
path = 'C:\\Users\\USA GADGETS\\PycharmProjects\\App\\first_page.db'
path1 = 'C:\\Users\\USA GADGETS\\PycharmProjects\\App\\second_page.db'
path2 = 'C:\\Users\\USA GADGETS\\PycharmProjects\\App\\third_page.db'
path3 = 'C:\\Users\\USA GADGETS\\PycharmProjects\\App\\fourth_page.db'
path4 = 'C:\\Users\\USA GADGETS\\PycharmProjects\\App\\fifth_page.db'

# connections

connec=sql.connect(path2) # conect to third page database
fourth_page_connec = sql.connect(path3) # conect to fourth page database
fifth_page_connec = sql.connect(path4) # conect to fifth page database

# Cursor
curso=connec.cursor() # Third page cursor
cursoo=fourth_page_connec.cursor() # Fourth page cursor
cursoor=fifth_page_connec.cursor() # Fifth page cursor

# pass a query
curso.execute("""CREATE TABLE IF NOT EXISTS page3(id integer primary key autoincrement,Month,Description text,Amount REAL)""") # Third Page Query
connec.commit() # Third Page Commit
cursoo.execute(""" CREATE TABLE IF NOT EXISTS page4(Items,Description text,Amount REAL)""")
fourth_page_connec.commit()
cursoor.execute(""" CREATE TABLE IF NOT EXISTS page5(id integer primary key autoincrement,Items,Description text,Amount REAL)""")
fifth_page_connec.commit()

# Close connection

connec.close()
fourth_page_connec.close()
fifth_page_connec.close()



# declaration of properties to modify objects from my.kv
finances= ObjectProperty()
month=ObjectProperty()
jan_action=ObjectProperty()
feb_action=ObjectProperty
mar_action=ObjectProperty()
apr_action=ObjectProperty
may_action=ObjectProperty()
jun_action=ObjectProperty
jul_action=ObjectProperty()
aug_action=ObjectProperty
sep_action=ObjectProperty()
oct_action=ObjectProperty
nov_action=ObjectProperty()
dec_action=ObjectProperty

# declaration of properties of second page
income_action=ObjectProperty()
budget_action=ObjectProperty
event_action=ObjectProperty()
analysis_action=ObjectProperty
report=ObjectProperty()

class FirstScreen(Screen):

    def Pressbutton(self,button):
        global butt
        butt=button

        return butt

 class SecondScreen(Screen):

    def Pressbutton2(self,button):
        global butto
        butto=button
        return butto.text

class ThirdScreen(Screen):
    desc_action = ObjectProperty()
    amount_action = ObjectProperty()
    data_items1=ListProperty(["S/N","MONTH","INCOME","AMOUNT"]) # Headings for columns in recycle viewer
      # To identify only the month chosen at the first instance
    def getFromIncome(self):

        def __init__(self, **kwargs):
            super(ThirdScreen, self).__init__(**kwargs)
        current_month_value = FirstScreen().Pressbutton(butt)
        connec = sql.connect("third_page.db")
    # Cursor
        curso = connec.cursor()
    # pass a query
        curso.execute("""INSERT INTO page3(
                id,Month,Description,Amount) VALUES (NULL,?,?,?) """,
                  (current_month_value,self.desc_action.text,self.amount_action.text))
        curso.execute("SELECT * FROM page3 WHERE Month=current_month_value")
        rows= curso.fetchall()
        connec.commit()
        for row in rows:
            for col in row:
                self.data_items1.append(col)
        # Close connection
        connec.close()
Simeoni
  • 1
  • 4

1 Answers1

0
curso.execute("SELECT * FROM page3 WHERE Month=?", (current_month_value,))

In your version you put current_month_value inside string constant so sqlite thinks that it's column name, just like Month. You should define this as a parameter, using question mark ("?") and bind this parameter to its value, passing it via current_month_value variable

Grisha
  • 713
  • 5
  • 13
  • 1
    Although this code might solve the problem, a good answer should also explain **what** the code does and **how** it helps. – BDL Oct 13 '20 at 08:58
  • On running the code you wrote, it gives sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied. – Simeoni Oct 13 '20 at 09:57
  • Is it possible that your variable is inside quotation marks and not outside? Did you close the string just after "?" sign? – Grisha Oct 13 '20 at 12:11
  • I edit the answer. The parameter must be passwed as tuple, as explained here: https://stackoverflow.com/questions/16856647/sqlite3-programmingerror-incorrect-number-of-bindings-supplied-the-current-sta – Grisha Oct 13 '20 at 16:08
  • It worked sir, thanks but there is a little problem. Firstly the value of the months I clicked isn't showing under the month table. Secondly Lets say I've been picking March as My Month and Suddenly I picked April, I expect it to give me data about the last selection which is April but I'm still seeing it carrying over Results of March into it. Is there anyway I can modify the query again sir ? – Simeoni Oct 13 '20 at 20:07
  • Dont worry sir, I already fixed the new issue by abstaining from using the variable curent_month_value but just calling FirstScreen.PressButton(button) – Simeoni Oct 13 '20 at 23:11