處理一些使用 pysimplegui 作為 UI 和 SQlite 進行資料排序的代碼。我正在使用 SQLite 的執行函式根據用戶在 UI 中通過變數輸入的資料來選擇資料。例如，用戶想要搜索零件名稱，他們在框中輸入全部或部分名稱，點擊搜索按鈕，然后運行我的“parts_search”方法，然后僅根據零件名稱過濾結果。或者用戶在多個框中輸入資訊，然后根據具有資訊的框進行過濾。
這是可運行的代碼，前提是您將檔案base1.db添加到與腳本本身相同的檔案夾位置
import PySimpleGUI as sg
import os.path
import sqlite3
# sql var
c = None
conn = None
setup = None
# list var
parts = []
def sql():
global setup
conn_sql()
c.execute("""CREATE TABLE IF NOT EXISTS parts (part_name TEXT, part_number TEXT, part_series TEXT,
part_size INTEGER, job_type TEXT)""")
conn.commit()
if conn:
conn.close()
def conn_sql():
global c
global conn
# SQL connection var
if os.path.isfile('./base1.db'):
conn = sqlite3.connect('base1.db')
c = conn.cursor()
def main_gui_parts():
global parts
layout = [[sg.Text('Part Name: '), sg.Input(size=(20, 1), key='-PName-'), sg.Text('Part Series:'),
sg.Input(size=(10, 1), key='-PSeries-')],
[sg.Text('Part Number:'), sg.Input(size=(20, 1), key='-PNumber-'), sg.Text('Part Size:'),
sg.Input(size=(10, 1), key='-PSize-')],
[sg.Checkbox('Fit', key='-PFit-'), sg.Checkbox('Weld', key='-PWeld-'),
sg.Checkbox('Assemble', key='-PAssemble-'),
sg.Button('Search', key='-PSearch-')],
[sg.Listbox(parts, size=(58, 10), key='-PParts-')], [sg.Button('Back', key='-PBack-')]]
window = sg.Window('parts list', layout, grab_anywhere=True)
sql()
while True:
event, values = window.read()
if event == 'Close' or event == sg.WIN_CLOSED:
break
# PART WINDOW
part_name = values['-PName-']
part_series = values['-PSeries-']
part_number = values['-PNumber-']
part_size = values['-PSize-']
fit = values['-PFit-']
weld = values['-PWeld-']
assemble = values['-PAssemble-']
if event == '-PSearch-':
print('search parts')
part_search(part_name, part_series, part_number, part_size, fit, weld, assemble)
if event == '-PBack-':
break
window.close()
def part_search(part_name, part_series, part_number, part_size, fit, weld, assemble):
global parts
conn_sql()
filter_original = """SELECT * FROM parts WHERE """
filter = filter_original
if part_name:
print('part name: ' part_name)
if filter == filter_original:
filter = """part_name LIKE ? """
else:
filter = """AND part_name LIKE ? """
if part_series:
print('part series: ' part_series)
if filter == filter_original:
filter = """part_series=(?) """
else:
filter = """AND part_series=(?) """
if part_number:
print('part number: ' part_number)
if filter == filter_original:
filter = """part_number LIKE ? """ ### DONT USE LIKE???
else:
filter = """AND part_number LIKE ? """ ### DONT USE LIKE???
if part_size:
print('part size: ' part_size)
if filter == filter_original:
filter = """part_size=(?) """
else:
filter = """AND part_size=(?) """
if fit:
print('job type: ' str(fit))
if filter == filter_original:
filter = """job_type = fit """
else:
filter = """AND job_type = fit """
if weld:
print('job type: ' str(weld))
if filter == filter_original:
filter = """job_type = weld """
else:
filter = """AND job_type = weld """
if assemble:
print('job type: ' str(assemble))
if filter == filter_original:
filter = """job_type = assemble"""
else:
filter = """AND job_type = assemble"""
print(filter)
#if filter != filter_original:
#c.execute(filter, ())
#else:
#c.execute("""SELECT * FROM parts""")
main_gui_parts()
問題：底部的注釋代碼是我無法弄清楚的地方（在“part_search”方法中）。我不會一直使用所有變數。僅使用用戶提供的變數進行過濾。這意味著元組應該只包含用戶輸入的變數。
如果使用了所有變數，這就是它的樣子。c.execute(filter, (part_name, part_series, part_number, part_size, fit, weld, assemble))但更多時候，不僅會使用其中一些變數，而且可能需要看起來像這樣。c.execute(filter, (part_name, part_series, weld))不知何故，我需要這里的變數是可洗掉的（因為沒有更好的詞）
我一直在學習很多關于 SQLite 的知識，但我可能會看到隧道視野，并且想不出另一種方法來解決這個問題。
uj5u.com熱心網友回復：
可能最簡單的處理方法是將所有過濾條件和值放入串列中，然后僅WHERE在過濾器串列的長度不為零時添加子句。例如：
query = """SELECT * FROM parts"""
filters = []
values = []
if part_name:
filters.append("""part_name LIKE ?""")
values.append(part_name)
...
if len(filters):
query = ' WHERE ' ' AND '.join(filters)
c.execute(query, tuple(values))
注意：如果您的過濾器包含OR條件，則需要在構建查詢時將它們括起來以確保正確操作，即
query = ' WHERE (' ') AND ('.join(filters) ')'
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/490809.html
標籤：Python sql python-3.x sqlite pysimplegui