一咪惠、起因
? 在開發(fā)過程中来屠，需要使用Mybatis頻繁的生成SQL查詢數(shù)據(jù)庫承二，但是因?yàn)镸ybatis打印出的SQL分為兩部分拴清，一部分是Parameters，另一部分是Preparing褥琐。
? 其中Parameters為具體的條件传趾，而Preparing為SQL的架子识啦，自己在開發(fā)過程中，無法直接在SQL編輯器中運(yùn)行神妹，每次都需要手動替換颓哮，費(fèi)時(shí)費(fèi)力，故想寫一個(gè)小程序鸵荠，只需要傳入Parameters和Preparing即可生成真實(shí)的SQL冕茅。
image.png
如上圖所示：Parameters與Preparing分離，故很難組裝好了SQL自己測試蛹找，驗(yàn)證SQL正確與否姨伤。還需要手動拼接條件，將Preparing中的問號替換出來庸疾。
Parameters:
江蘇(String), 松下(String), 16.01(String), 200(String), 大連鎖(String), 波輪(String), 滾筒(String), 3400~3599(String), 4300~4399(String), 2000~2199(String), 1600~1799(String), 8.0-8.9kg(String), 7.0-7.9kg(String), 6.0-6.9kg(String), 變頻(String), 定頻(String), 江蘇(String), 16.01(String), 200(String), 大連鎖(String), 波輪(String), 滾筒(String), 3400~3599(String), 4300~4399(String), 2000~2199(String), 1600~1799(String), 8.0-8.9kg(String), 7.0-7.9kg(String), 6.0-6.9kg(String), 變頻(String), 定頻(String)
Preparing:
SELECT brand_sum.brand_current_sum_volume AS "volume", brand_sum.brand_current_sum_sales AS "sales", ROUND(brand_sum.brand_current_sum_sales / brand_sum.brand_current_sum_volume, 2) AS avgPrice, ROUND(brand_sum.brand_current_sum_volume::NUMERIC / industry_sum.industry_current_sum_volume::NUMERIC, 4) * 100 AS "salesMarketShare", ROUND(brand_sum.brand_current_sum_sales::NUMERIC / industry_sum.industry_current_sum_sales::NUMERIC, 4) * 100 AS "volumeMarketShare" FROM ( SELECT '1' AS type, SUM(volume) AS brand_current_sum_volume, SUM(sales) AS brand_current_sum_sales FROM wm_offline_monthly_fct WHERE province = ? AND brand_name = ? AND month_code = ? AND price_segment_group = ? AND channel_type = ? AND product_type in(?,?) AND price_segment in (?,?,?,?) AND capacity_segment in (?,?,?) AND product_tech in (?,?) ) AS brand_sum JOIN( SELECT '1' AS type, SUM(volume) AS industry_current_sum_volume, SUM(sales) AS industry_current_sum_sales FROM wm_offline_monthly_fct WHERE province = ? AND month_code = ? AND price_segment_group = ? AND channel_type = ? AND product_type in(?,?) AND price_segment in (?,?,?,?) AND capacity_segment in (?,?,?) AND product_tech in (?,?) ) AS industry_sum ON brand_sum.type = industry_sum.type
代碼如下：
from tkinter import *
import tkinter.messagebox
import re
class MainWindow:
def buttonListener1(self, event):
data1 = self.text_parameter.get("0.0", "end")
data2 = self.text_preparing.get("0.0", "end")
data1 = re.split('\([\w\s]+\),\s', data1)
data1[len(data1)-1] = re.sub('\(.*\)\\n', "", data1[len(data1)-1])
print(data1)
print(data2)
j = 0
ttsql = ''
for s in data2:
if s != '?':
ttsql = ttsql + s
else:
ttsql = ttsql + '\'' + data1[j] + '\''
j = j + 1
print(ttsql)
self.text_result.delete(0.0, tkinter.END)
self.text_result.insert(INSERT, ttsql)
def selectText_preparing(self, event):
self.text_preparing.tag_add(tkinter.SEL, "1.0", tkinter.END)
return 'break'
def selectText_parameter(self, event):
self.text_parameter.tag_add(tkinter.SEL, "1.0", tkinter.END)
return 'break'
def selectText_result(self, event):
self.text_result.tag_add(tkinter.SEL, "1.0", tkinter.END)
return 'break'
def __init__(self):
self.frame = Tk()
self.frame.geometry("1024x768")
self.label_parameter = Label(self.frame, text="Parameters:")
self.label_preparing = Label(self.frame, text="Preparing:")
self.label_result = Label(self.frame, text="Result:")
self.text_parameter = Text(self.frame, height="8", width=100, background="grey", highlightbackground="orange")
self.text_preparing = Text(self.frame, height="8", width=100, background="grey", highlightbackground="orange")
self.text_result = Text(self.frame, height="14", width=100, background="grey")
self.label_parameter.grid(row=0, column=0)
self.label_preparing.grid(row=1, column=0)
self.button_ok = Button(self.frame, text="ok", width=10)
self.button_cancel = Button(self.frame, text="cancel", width=10)
self.text_parameter.grid(row=0, column=1)
self.text_preparing.grid(row=1, column=1)
self.button_ok.grid(row=3, column=0)
self.button_cancel.grid(row=3, column=1)
self.label_result.grid(row=4, column=0)
self.text_result.grid(row=4, column=1)
self.button_ok.bind("<ButtonRelease-1>", self.buttonListener1)
self.text_preparing.bind("<Command-Key-a>", self.selectText_preparing)
self.text_result.bind("<Command-Key-a>", self.selectText_result)
self.text_parameter.bind("<Command-Key-a>", self.selectText_parameter)
self.frame.mainloop()
frame = MainWindow()
此為Python3的腳本乍楚，因?yàn)楸救擞玫氖荕ac，故只有Mac用戶才可以Command+A進(jìn)行全選届慈，若為Windows用戶需要手動將代碼中的Command替換為Control即可實(shí)現(xiàn)全選徒溪。
image.png
如上圖所示：傳入Parameters和Preparing，點(diǎn)擊ok金顿，即可生成真實(shí)的SQL臊泌。
image.png
Format之后，可以看到整體的輪廓揍拆，其中綠色的為替換的條件缺虐。
