Python Forum

Hello,
I want to display data from my "quote" table by selecting an author in my table author selected "author" in my combobox. The search must be strictly equal to the name of the author selected in my combobox "cmb_author".

Thanks for your help.
Should we use LIKE?
I tried in my request, but it doesn't work.


my combobox

def cmb_author(event=None):

    global data1
    connexion = sqlite3.connect('mnesis.db')
    cursor = connexion.cursor()
    cursor.execute("SELECT author_author FROM tb_author ORDER BY author_author")
    data1 = []
    for row in cursor.fetchall():
        data1.append(row[0])
    return data1

my request
error line 5

Quote: curseur.execute("SELECT * FROM tb_quote WHERE author_author LIKE \'%' + data1 + '%\'")
sqlite3.OperationalError: no such column: data1

def query_author():

    connexion = sqlite3.connect('mnesis.db')
    curseur = connexion.cursor()
    curseur.execute("SELECT * FROM tb_quote WHERE author_author LIKE \'%' + data1 + '%\'")
    resultDatabase = curseur.fetchall()  # query
    results = ''
    for row in resultDatabase:
        results += '\n'.join([
            'Auteur: {}'.format(row[1]),
            'Citation: {}'.format(row[2]),
            'Référence: {}'.format(row[3]),
            '-----------------------\n'
        ])

(Feb-02-2020, 07:15 AM)LagratteCchouette Wrote: [ -> ]The search must be strictly equal to the name of the author selected in my combobox "cmb_author".

Thanks for your help.
Should we use LIKE?

If the name must be strictly equal you must not use the keyword "LIKE".

Furtheron you seem to have problems with the SQL statement. Now I do not know Sqlite3, but I do know SQL. So I believe your query should be:

curseur.execute("SELECT * FROM tb_quote WHERE author_author LIKE '%" + data1 + "%\'")

But as we just agreed you should not use "LIKE" it would have to be:

curseur.execute("SELECT * FROM tb_quote WHERE author_author = '" + data1 + "'")

... Or even more pythonic use an f-string:

curseur.execute(f"SELECT * FROM tb_quote WHERE author_author = '{data1}'")

Thank you very much ... I understand better.
I rewrote the code (line 18), but I still get an error message

Quote:Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Users\.........\Python\Python37\lib\tkinter\__init__.py", line 1705, in __call__
return self.func(*args)

line 74, in <lambda> command=lambda: query_quote())

line 18, in query_quote
cursor.execute(f"SELECT * FROM tb_quote WHERE author_author = '{data1}'")
sqlite3.OperationalError: near "Achille": syntax error

Here is my code below, there is something I am missing.
can you please help me as i am a beginner and i love this language very much.

def cmb_author(event=None):
    """ Data recovery and insertion into combobox """
    global data1 # statement for use in the query query_quote()
    connexion = sqlite3.connect('mnesis.db')
    cursor = connexion.cursor()
    cursor.execute("SELECT auteur_auteur FROM tb_auteur ORDER BY auteur_auteur")
    data1 = []
    for row in cursor.fetchall():
        data1.append(row[0])
    return data1


#-----
def query_quote():
    """ Returns the values of the records of the Database """
    connexion = sqlite3.connect('mnesis.db')
    cursor = connexion.cursor()
    cursor.execute(f"SELECT * FROM tb_quote WHERE author_author = '{data1}'")
    resultDatabase = cursor.fetchall()
    results = ''
    for row in resultDatabase:
        results += '\n'.join({
            'Auteur: {}'.format(row[1]),
            'Citation: {}'.format(row[2]),
            'Référence: {}'.format(row[3]),
            '-----------------------\n'
        })

    connexion.close()
    return results

def result_quote():
    """ Returns the values of the database records """
    tpl_result = Toplevel()  # == Contructor Toplevel ==
    tpl_result.title(" Data display")
    tpl_result.geometry("1024x600")
    # Text area with scrollbar
    zonetext = tk.Text()
    zonetext = ScrolledText(tpl_result, width=120, height=35)
    zonetext.pack()
    resulreq = query_quote()
    zonetext.insert("0.0", resulreq)
    tpl_result.mainloop()


def search_data():
    """
    Search by selection of a combobox
    """
    global tpl_search

    tpl_search = Toplevel()  # == Constructor Toplevel ==
    tpl_search.title(" Search by selection of a combobox")
    screen_x = int(tpl_search.winfo_screenwidth())
    screen_y = int(tpl_search.winfo_screenheight())
    tpl_search_x = 400
    tpl_search_y = 100
    pos_x = (screen_x // 2) - (tpl_search_x // 2)
    pos_y = (screen_y // 2) - (tpl_search_y // 2)
    geo = "{}x{}+{}+{}".format(tpl_search_x, tpl_search_y, pos_x, pos_y)
    tpl_search.geometry(geo)
    tpl_search.resizable(width=False, height=False)  # Editable window True or False
    tpl_search.configure(bg='Gray79')
    # ------------
    attribution_label = Label(tpl_search, text="Author", bg='Gray79', font=("Arial", 11, "bold"))
    attribution_label.place(x=100, y=25)

    combo_attribution = ttk.Combobox(tpl_search, values=cmb_author(), width=20, height=30,
                                     font=("Arial", 10, "bold"))
    combo_attribution.bind('<<ComboboxSelected>>', cmb_author)
    combo_attribution.place(x=100, y=46)
    #----
    record_btn = Button(tpl_search, text='Launch', activebackground="SkyBlue1", font=("Arial", 8),
                        command=lambda: query_quote())
    record_btn.place(x=280, y=46)
    tpl_search.mainloop()

That is weird. In such cases I usually add debug statements. Like just before the "cursor.execute()" I would add:

print(f"SELECT * FROM tb_quote WHERE author_author = '{data1}'") #DEBUG

Then you see what it is really trying to execute in the database. What happens when you execute the result of that print statement directly in the database? Does it give the same error?
Something else: I don't see where data1 is defined. In function cmb_author() I see a line "global data1". But I don't see where data1 is globally defined. And should function query_quote() not have the same line "global data1"? On the other hand: the error message says: 'OperationalError: near "Achille"'. I bet that is the name of the author in "data1". Is that correct?

Thanks for your help.
This does not change the message.
So maybe as I begin, I did not take the right track.
If instead of retrieving "data1" I use the "cmb_author" function which returns "data1" to us.
In this case, can I write it like this:

def cmb_author(event=None):
    """ Data recovery and insertion into combobox """
    connexion = sqlite3.connect('mnesis.db')
    cursor = connexion.cursor()
    cursor.execute("SELECT auteur_auteur FROM tb_auteur ORDER BY auteur_auteur")
    data1 = []
    for row in cursor.fetchall():
        data1.append(row[0])
    return data1


#-----
def query_quote():
    """ Returns the values of the records of the Database """
    global data1
    connexion = sqlite3.connect('mnesis.db')
    cursor = connexion.cursor()
    print(f"SELECT * FROM tb_quote WHERE author_author = '{cmb_author()}'")  # DEBUG
    cursor.execute(f"SELECT * FROM tb_quote WHERE author_author = '{cmb_author()}'")
    resultDatabase = cursor.fetchall()
    results = ''
    for row in resultDatabase:
        results += '\n'.join({
            'Auteur: {}'.format(row[1]),
            'Citation: {}'.format(row[2]),
            'Référence: {}'.format(row[3]),
            '-----------------------\n'
        })

    connexion.close()
    return results

So here is the message with this code.
Is this the right method, because I do not know I do a lot of tutorials but I can not understand.

Quote:Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Users\....\Python\Python37\lib\tkinter\__init__.py", line 1705, in __call__
return self.func(*args)
File "C:/Users/...GnosisProject/main16.py", line 561, in <lambda>
command=lambda: query_quote())
File "C:/Users/..../GnosisProject/main16.py", line 19, in query_quote
cursor.execute(f"SELECT * FROM tb_quote WHERE quote_author = '{cmb_author()}'")
sqlite3.OperationalError: near "Achille": syntax error

SELECT * FROM tb_quote WHERE quote_author = '['Achille', 'Botero, Fernando', 'Cassandre', 'Euripide', 'Guitton, Jean', 'Hemingway, Ernest \n', 'Loperum', 'Nietzsche, Friedrich \n', 'Oscar Wilde', 'Pascal', 'Platon', 'Plutarque', 'Priam', 'Shaw, George Bernard', 'Socrate', 'Sophocle', 'Vercingétorix', 'Voltaire', 'toto\n']'

Process finished with exit code 0

That print statement explains a lot.

(Feb-02-2020, 05:21 PM)LagratteCchouette Wrote: [ -> ]SELECT * FROM tb_quote WHERE quote_author = '['Achille', 'Botero, Fernando', 'Cassandre', 'Euripide', 'Guitton, Jean', 'Hemingway, Ernest \n', 'Loperum', 'Nietzsche, Friedrich \n', 'Oscar Wilde', 'Pascal', 'Platon', 'Plutarque', 'Priam', 'Shaw, George Bernard', 'Socrate', 'Sophocle', 'Vercingétorix', 'Voltaire', 'toto\n']'

This is not valid SQL. I thought data1 would contain one author. But it contains a list. What is the intention? Must the query contain all authors? Then the right syntax would be:

SELECT * FROM tb_quote WHERE quote_author IN ('Achille', 'Botero, Fernando', 'Cassandre', 'Euripide', 'Guitton, Jean', 'Hemingway, Ernest \n', 'Loperum', 'Nietzsche, Friedrich \n', 'Oscar Wilde', 'Pascal', 'Platon', 'Plutarque', 'Priam', 'Shaw, George Bernard', 'Socrate', 'Sophocle', 'Vercingétorix', 'Voltaire', 'toto\n')

Or else you should loop over the list "data1" and use the old syntax.

excuse me
I will be more specific.
In any case, thank you for your kindness.

I want to start using my database.
To do this I want to search and display records contained in my database "tb_quote" in which there are fields "author" "quote" "theme" "reference".
When entering citations, the authors come from my "tb_author" using the combo box "cmb_author ()".

If I want to find a quote next, this is the code we trade for.
By the form "search_data ()" I select an author for example "Platon" and I want to find by the query sql in "query_quote ()" the correspondence and then display it in a text + scrollbar "result_quote ()" l ' set of quotes from Platon.
It is not always easy to express your need well.

In fact I want to start working on a multi-criteria search engine for Combobox fields and other "Entry" fields.
thanks for the help and i admit that i'm a novice.

Hello,
I have searched a lot, but I can't find a way to do it.
Please have an opinion.

My combobox

def cmb_author(event=None):
    """ Data recovery and insertion into combobox """
    global data1
    connexion = sqlite3.connect('mnesis.db')
    cursor = connexion.cursor()
    cursor.execute("SELECT author_author FROM tb_author ORDER BY author_author ")
    data1 = []
    for row in cursor.fetchall():
        data1.append(row[0])
    return data1

my query

Line 7 contains the DEBUG instruction and afterwards in quotation I put the message in the console.
Nothing happens on line 9. Nothing is displayed and I don't have an error message.

def query_quote():
    """ Returns the values of the records of the Database """

    connexion = sqlite3.connect('mnesis.db')
    cursor = connexion.cursor()
    
    print(f"SELECT * FROM tb_quote WHERE quote_author = 'data1'")  # DEBUG

    cursor.execute("SELECT * FROM tb_quote WHERE quote_author ='data1'")
    resultDatabase = cursor.fetchall()
    results = ''
    for row in resultDatabase:
        results += '\n'.join({
            'Author: {}'.format(row[1]),
            'Quote: {}'.format(row[2]),
            'Reference: {}'.format(row[3]),
            '-----------------------\n'
        })

    connexion.close()
    return results

Quote:SELECT * FROM tb_quote WHERE citation_auteur = 'data1'

hello,

I found a reproducible example of what I want to do, but I can't do it from my combobox

def callbackFunc(event):
    print(comboExample.get())

app = tk.Tk()
app.geometry('200x100')
labelTop = tk.Label(app,text="Choose your favourite month")
labelTop.grid(column=0, row=0)
comboExample = ttk.Combobox(app,values=["Socrate","Platon","Plutarque","Chruchill"])
comboExample.grid(column=0, row=1)
comboExample.bind("<<ComboboxSelected>>", callbackFunc)

app.mainloop()

Here's my code that does what I wanted
Well here is one of the solutions ... There is perhaps simpler and I remain at your disposal ...
Declare the global variables after the different imports at the beginning of the code.

# coding:utf-8
from varfunction15 import *
from tkinter import *
import tkinter as tk
import tkinter.ttk as ttk
from tkinter import Entry, Text
from PIL import Image, ImageTk  # module image
from tkinter.scrolledtext import *
from tkcalendar import Calendar, DateEntry  # Widget calendar
import webbrowser

# variable globale
cmb_attribution = None
rsltcmb = None

Then create the callback () function

# function callback
def callbackAuteur(event):
    global rsltcmb  # declare the variable globally again to prevent python from considering it locally
    rsltcmb = cmb_attribution.get()

My combobox

def cmb_auteursearch():
    """ Data recovery and insertion in combobox author_citation """
    connexion = sqlite3.connect('mnesis.db')
    cursor = connexion.cursor()
    cursor.execute('SELECT auteur FROM tb_auteur ORDER BY auteur')
    data = []
    for row in cursor.fetchall():
        data.append(row[0])
    return data

My query

def queryQuoteforauteur():
    """ Returns the values of the records of the Database """
    connexion = sqlite3.connect('mnesis.db')
    cursor = connexion.cursor()
    cursor.execute('SELECT * FROM tb_citation WHERE auteur=?', (rsltcmb,))
    results = ''
    for row in cursor.fetchall():
        results += '\n'.join({
            'Auteur: {}'.format(row[1]),
            'Citation: {}'.format(row[2]),
            'Référence: {}'.format(row[3]),
            '-----------------------\n'
        })
    connexion.close()
    return results

My Toplevel to view data

def result_quote():
    """ Returns the values of the database records """
    tpl_result = Toplevel()  # == Contructor Toplevel ==
    tpl_result.title(" Data display")
    tpl_result.geometry("1024x600")
    # Text area with scrollbar
    zonetext = tk.Text()
    zonetext = ScrolledText(tpl_result, width=120, height=35)
    zonetext.pack()
    resulreq = queryQuoteforauteur()
    zonetext.insert("0.0", resulreq)
    # end of the loop ---
    tpl_result.mainloop()

My search form.

def search_data():
    """
    Search by selection of a combobox
    """
    global tpl_search

    tpl_search = Toplevel()  # == Constructor Toplevel ==
    tpl_search.title(" Search by selection of a combobox")
    screen_x = int(tpl_search.winfo_screenwidth())
    screen_y = int(tpl_search.winfo_screenheight())
    tpl_search_x = 400
    tpl_search_y = 100
    pos_x = (screen_x // 2) - (tpl_search_x // 2)
    pos_y = (screen_y // 2) - (tpl_search_y // 2)
    geo = "{}x{}+{}+{}".format(tpl_search_x, tpl_search_y, pos_x, pos_y)
    tpl_search.geometry(geo)
    tpl_search.resizable(width=False, height=False)  # Editable window True or False
    tpl_search.configure(bg='Gray79')
    # ------------
    attribution_label = Label(tpl_search, text="Author", bg='Gray79', font=("Arial", 11, "bold"))
    attribution_label.place(x=100, y=25)
    global cmb_attribution # intérieur
    cmb_attribution = ttk.Combobox(tpl_search, values=cmb_auteursearch(), width=20, height=30,
                                   font=("Arial", 10, "bold"))
    cmb_attribution.bind('<<ComboboxSelected>>', callbackAuteur)
    cmb_attribution.place(x=100, y=46)
    # ----
    record_btn = Button(tpl_search, text='Launch', activebackground="SkyBlue1", font=("Arial", 8),
                        command=lambda: result_quote())
    record_btn.place(x=280, y=46)
    # end of the loop ---
    tpl_search.mainloop()