Sqlite3 in Python retuns none

Question

0.00/5 (No votes)

See more:

i have a function which should show a value on the GUI created with Tkinter. I am using Python3 and also Sqlite3. The value that i want to show is stored in a variable and i get the value from an EtryBox.

The problem is i have tried many ways but still i get back none instead of the Actuall value that i am expecting to get.

the problem should be in the ( Cursor.execute ) line but i also do not get any kind of Erros !

i would rly appreciate any kind of suggestions thanks!

What I have tried:

Python

def weekly_cal():
      connect = sqlite3.connect('working_time_app.db')
      cursor = connect.cursor()

      cursor.execute("SELECT sum(fnishing - starting) FROM working_time_app_table
      WHERE date BETWEEN ? and ?",
                     (d_f_entry, d_u_entry))
      # the ( d_f_entry and d_u_entry ) are the variable that my value are beingstored


      sum_result = cursor.fetchone()


      show_weekly_cal_label = Label(cal_win, text=sum_result, font=("mv boli", 12),
                                     fg="white", bg="black")
      show_weekly_cal_label.grid(column=3, row=15, columnspan=5)



      connect.commit()
      connect.close()

Posted 17-Feb-20 7:36am

Korak-997

Updated 22-Feb-20 11:21am

Add a Solution

4 solutions

Solution 5

Thanks for all of your suggestions specially the ones who suggested debugging. I did debug was kind of a huge stress, but in the end finally i found my mistake !!!! 

The error does not have to do with any kind of misspelling or Data type. The problem was wrongly locating the d_u_entry variable and d_f_entry variable. here i will post again the correct form of the code . Again thanks for all of you and your time that you invested in helping me. Also sorry for bothering you all !!!!
 

 <pre lang="Python">

  cursor.execute("SELECT total(finishing - starting) FROM working_time_app_table WHERE date BETWEEN ? and ?",
                   (d_f_entry, d_u_entry))

The problem was i was Entering the ending Date before the beginning !!!

Posted 22-Feb-20 11:22am

Korak-997

Solution 4

well guys am sorry i bother you all but i still could not solve my problem !!!
down below i will post the whole code i have in that file and used for that part of my programm and hope you could find my mistake .

PS: the datas are added to the database as : yyyy-mm-dd
i also write the entry the same way !! so i guess u should have no problem in adding the datas

here is the code i have ::

Python

from tkinter import*
from second_win import*
from datetime import datetime
# the function i wrote to create the calculation window
def cal():
    cal_win = Toplevel()
    cal_win.title("Calculations Window")
    cal_win.geometry("600x600")
    cal_win.configure(background="black")

    global d_f_entry
    global d_u_entry


#the function which have to return the sum of working time between two dates
    def weekly_cal():
        connect = sqlite3.connect('working_time_app.db')
        cursor = connect.cursor()

        cursor.execute("SELECT sum(fnishing - starting) FROM working_time_app_table WHERE date BETWEEN ? and ? ",
                       (d_u_entry, d_f_entry))

        sum_result = cursor.fetchall()


        show_weekly_cal_label = Label(cal_win, text=sum_result, font=("mv boli", 12), fg="white", bg="black")
        show_weekly_cal_label.grid(column=3, row=15, columnspan=5)



        connect.commit()
        connect.close()

#the function which shows a daily calculation ( does not have any effect on the one which do the weekly calculation )
    def day_cal():

        h_sum = str( float(until_entry.get()) - float(from_entry.get()) )
        date_sum = str(date_entry.get())
        day_sum = str(day_entry.get())
        label_show_sum = Label(cal_win, text=(" you worked " + " "
                                              + h_sum + "H" + " \n " + "on" + " " + day_sum + " " + date_sum),
                               bg="black", fg="white", font=("mv boli", 20))
        label_show_sum.grid(column=3, row=4, columnspan=5)
#the function i created to show daily data and then be able to  calculate and show how many hours someone worked in a day
    def cal1():

        global date_entry
        global day_entry
        global from_entry
        global until_entry

        day_label = Label(cal_win, text="Day", font=("mv boli", 20), fg="white", bg="black")
        date_label = Label(cal_win, text="Date", font=("mv boli", 20), fg="white", bg="black")
        from_label = Label(cal_win, text="From", font=("mv boli", 20), fg="white", bg="black")
        until_label = Label(cal_win, text="Until", font=("mv boli", 20), fg="white", bg="black")

#this buttons shows the calculation of a daily datas ( how many hours of work per in that day )
        daily_cal_button = Button(cal_win, text="Show Daily Sum", fg="black", bg="white",
                                  font=("mv boli", 15), command=day_cal, width=15)
#here i created entry boxes so that the datas that i have in the data base each one will be added to its right place
        day_entry = Entry(cal_win, width=30)
        date_entry = Entry(cal_win, width=30)
        from_entry = Entry(cal_win, width=30)
        until_entry = Entry(cal_win, width=30)

        date_label.grid(column=1, row=2, pady=5)
        day_label.grid(column=3, row=2, pady=5)
        from_label.grid(column=1, row=3, pady=5)
        until_label.grid(column=3, row=3, pady=5)


        daily_cal_button.grid(column=1, row=5, pady=5, columnspan=2)


        day_entry.grid(column=4, row=2, pady=5)
        date_entry.grid(column=2, row=2, pady=5)
        from_entry.grid(column=2, row=3, pady=5)
        until_entry.grid(column=4, row=3, pady=5)

        connect = sqlite3.connect('working_time_app.db')
        cursor = connect.cursor()

        record_id = id_entry.get()
        cursor.execute("SELECT* from working_time_app_table WHERE oid=" + record_id)
        records = cursor.fetchall()

        connect.commit()
        connect.close()
#here this for loop if for inserting the datas into the right entry box
        for record in records:
            day_entry.insert(0, record[0])
            date_entry.insert(0, record[1])
            from_entry.insert(0, record[2])
            until_entry.insert(0, record[3])
# because that i created these things in the same window as others so i clean the screen with destry and forget function
        daily_button.destroy()
        monthly_sum_button.grid_forget()
        weekly_sum_button.grid_forget()
        date_from_entry.grid_forget()
        date_until_entry.grid_forget()
        until_date_label.grid_forget()
        from_date_label.grid_forget()

    id_label = Label(cal_win, text="ID", font=("mv boli", 15), fg="white", bg="black")
    id_entry = Entry(cal_win, width=30)
    id_entry.grid(column=3, row=1, pady=5)
    id_label.grid(column=2, row=1, pady=5)
#the button that get you in the daily calculations window
    daily_button = Button(cal_win, text="Daily Sum", font=("mv boli", 15),
                          fg="black", bg="white", width=10, command=cal1)
    daily_button.grid(column=3, row=2, columnspan=2)
#this button should show hours of work in the whole month and then the income also
#but yet did not created any function for it ( i want to finish the weekly function first )
    monthly_sum_button = Button(cal_win, text="Monthly Sum", fg="black", bg="white",
                                font=("mv boli", 15), width=15)
#thid button should do the weekly calculation ( which i have a problem with its function !!! )
    weekly_sum_button = Button(cal_win, text="Weekly Sum",
                               fg="black", bg="white",
                               font=("mv boli", 15), width=15, command=weekly_cal)

    monthly_sum_button.grid(column=3, row=7, pady=5, columnspan=2)
    weekly_sum_button.grid(column=3, row=8, pady=5, columnspan=2)



    from_date_label = Label(cal_win, text="From", font=("mv boli", 15), fg="white", bg="black")
    from_date_label.grid(column=2, row=5, pady=5)

#this is the entry box i created which allows the user to enter the begin date
    date_from_entry = Entry(cal_win, width=30)
    date_from_entry.grid(column=3, row=5, pady=5)

    until_date_label = Label(cal_win, text="Until", font=("mv boli", 15), fg="white", bg="black")
    until_date_label.grid(column=4, row=5, pady=5)
# this is the entry box i created that allow the user to enter the ending date
    date_until_entry = Entry(cal_win, width=30)
    date_until_entry.grid(column=5, row=5, pady=5)
#this two variable i created to get the data from the date entry boxes that i have and then use it in the weekly function
#in order to calculate the date in a range of days 
    d_u_entry = date_until_entry.get()
    d_f_entry = date_from_entry.get()

    cal_win.mainloop()

Posted 19-Feb-20 8:03am

Korak-997

Comments

Richard Deeming 19-Feb-20 14:50pm

This is not a "solution" to your question.

Assuming this is still the same question, then click the green "Improve question" link and edit your question to add the new information. Then delete this non-solution, and un-accept solution 3.

If it's a new question, then post it as a new question. You will still need to delete this non-solution.

Solution 2

SQL

cursor.execute("SELECT sum(fnishing - starting) FROM working_time_app_table

Are you sure that fnishing is the correct spelling?

Posted 17-Feb-20 22:03pm

Richard MacCutchan

Comments

Korak-997 18-Feb-20 14:26pm

well yes ! i know the spelling is wrong but was a misspelling thing when i first created the Database. Later was a bit lazzy to fix it so i kept writing it that way.

Richard MacCutchan 18-Feb-20 16:19pm

OK, sp that is one thing that we can ignore. Are you sure that your dates are in a form that can be easily compared? Take a look at SQLite BETWEEN Operator By Practical Examples[^] for some examples.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

markkuk · Accepted Answer · 2020-02-18T01:51:00

SQLite doesn't have builtin date or timestamp types, so date/time values are stored as ISO 8601[^] formatted strings. That's why your WHERE condition may not work the way you expect. Try using the julianday()[^] function to convert the date strings to floating point values for easy comparision:

SQL

WHERE julianday(date) BETWEEN julianday(?) AND julianday(?)

On the Python side, make sure that d_f_entry and d_u_entry are in correct ISO 8601 format and in UTC time.

Sqlite3 in Python retuns none

4 solutions

Solution 5

Solution 4

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us