Revision of SQLite 與 Streamlit st.data_editor 整合

timmy 已修改 10 months ago. 還原成這個修訂版本

沒有任何變更

timmy 已修改 11 months ago. 還原成這個修訂版本

沒有任何變更

timmy 已修改 11 months ago. 還原成這個修訂版本

1 file changed, 23 insertions, 10 deletions

sqlite_streamlit_join_example.py

			@@ -90,6 +90,8 @@ def main():
90	90		if st.button("更新資料庫"):
91	91		# 將編輯後的資料寫回資料庫
92	92		cursor = conn.cursor()
	93	+	updates = []
	94	+	errors = []
93	95		for index, row in edited_df.iterrows():
94	96		# 確保 OrderDate 是 datetime.date 類型，轉換為字串格式
95	97		if isinstance(row['OrderDate'], datetime):
			@@ -104,20 +106,31 @@ def main():
104	106		order_date = datetime.strptime(row['OrderDate'], '%Y-%m-%d').date()
105	107		order_date_str = order_date.strftime('%Y-%m-%d')
106	108		except ValueError:
107		-	st.error(f"無效的日期格式: {row['OrderDate']} (訂單編號: {row['OrderID']})")
	109	+	errors.append(f"無效的日期格式: {row['OrderDate']} (訂單編號: {row['OrderID']})")
108	110		continue
109	111		else:
110		-	st.error(f"無法解析的日期格式: {row['OrderDate']} (訂單編號: {row['OrderID']})")
	112	+	errors.append(f"無法解析的日期格式: {row['OrderDate']} (訂單編號: {row['OrderID']})")
111	113		continue
112	114
113		-	cursor.execute('''
114		-	UPDATE Orders
115		-	SET OrderDate = ?
116		-	WHERE OrderID = ?
117		-	''', (order_date_str, row['OrderID']))
118		-	conn.commit()
119		-	st.success("資料庫已更新！")
120		-	# Streamlit 會自動重新執行腳本，顯示更新後的資料
	115	+	updates.append((order_date_str, row['OrderID']))
	116	+
	117	+	if updates:
	118	+	try:
	119	+	cursor.executemany('''
	120	+	UPDATE Orders
	121	+	SET OrderDate = ?
	122	+	WHERE OrderID = ?
	123	+	''', updates)
	124	+	conn.commit()
	125	+	st.success("資料庫已更新！")
	126	+	except Exception as e:
	127	+	st.error(f"資料庫更新失敗: {e}")
	128	+
	129	+	if errors:
	130	+	for error in errors:
	131	+	st.error(error)
	132	+
	133	+	# 不需要手動重新執行腳本，Streamlit 會自動重新渲染並顯示更新後的資料
121	134
122	135		# 關閉資料庫連接
123	136		conn.close()

timmy 已修改 11 months ago. 還原成這個修訂版本

1 file changed, 126 insertions

sqlite_streamlit_join_example.py(檔案已創建)

		@@ -0,0 +1,126 @@
1	+	import sqlite3
2	+	import pandas as pd
3	+	import streamlit as st
4	+	from datetime import datetime, date
5	+
6	+	def initialize_database(conn):
7	+	cursor = conn.cursor()
8	+
9	+	# 建立 Customers 資料表
10	+	cursor.execute('''
11	+	CREATE TABLE IF NOT EXISTS Customers (
12	+	CustomerID INTEGER PRIMARY KEY,
13	+	CustomerName TEXT NOT NULL,
14	+	ContactName TEXT,
15	+	Country TEXT
16	+	)
17	+	''')
18	+
19	+	# 建立 Orders 資料表
20	+	cursor.execute('''
21	+	CREATE TABLE IF NOT EXISTS Orders (
22	+	OrderID INTEGER PRIMARY KEY,
23	+	CustomerID INTEGER,
24	+	OrderDate TEXT,
25	+	FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
26	+	)
27	+	''')
28	+
29	+	# 插入範例資料到 Customers（如果表格為空）
30	+	cursor.execute('SELECT COUNT(*) FROM Customers')
31	+	if cursor.fetchone()[0] == 0:
32	+	customers = [
33	+	(1, 'Alfreds Futterkiste', 'Maria Anders', 'Germany'),
34	+	(2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Mexico'),
35	+	(3, 'Antonio Moreno Taquería', 'Antonio Moreno', 'Mexico'),
36	+	(4, 'Around the Horn', 'Thomas Hardy', 'UK')
37	+	]
38	+	cursor.executemany('INSERT INTO Customers VALUES (?, ?, ?, ?)', customers)
39	+
40	+	# 插入範例資料到 Orders（如果表格為空）
41	+	cursor.execute('SELECT COUNT(*) FROM Orders')
42	+	if cursor.fetchone()[0] == 0:
43	+	orders = [
44	+	(101, 1, '2023-11-01'),
45	+	(102, 2, '2023-11-03'),
46	+	(103, 1, '2023-11-07'),
47	+	(104, 3, '2023-11-10'),
48	+	(105, 4, '2023-11-12')
49	+	]
50	+	cursor.executemany('INSERT INTO Orders VALUES (?, ?, ?)', orders)
51	+
52	+	conn.commit()
53	+
54	+	def get_joined_data(conn):
55	+	query = '''
56	+	SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderDate
57	+	FROM Customers
58	+	INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
59	+	ORDER BY Customers.CustomerID
60	+	'''
61	+	df = pd.read_sql_query(query, conn)
62	+	# 將 OrderDate 轉換為 datetime 類型
63	+	df['OrderDate'] = pd.to_datetime(df['OrderDate']).dt.date
64	+	return df
65	+
66	+	def main():
67	+	st.title("SQLite JOIN 範例與 Streamlit st.data_editor")
68	+
69	+	# 連接到 SQLite 資料庫（這裡使用本地檔案資料庫）
70	+	conn = sqlite3.connect('example.db', check_same_thread=False)
71	+	initialize_database(conn)
72	+
73	+	# 獲取 JOIN 查詢的資料
74	+	df = get_joined_data(conn)
75	+
76	+	st.header("JOIN 查詢結果")
77	+	edited_df = st.data_editor(
78	+	df,
79	+	num_rows="dynamic",
80	+	key="data_editor",
81	+	use_container_width=True,
82	+	column_config={
83	+	"CustomerID": st.column_config.NumberColumn("Customer ID", disabled=True),
84	+	"CustomerName": st.column_config.TextColumn("客戶名稱", disabled=True), # 禁用編輯
85	+	"OrderID": st.column_config.NumberColumn("訂單編號", disabled=True),
86	+	"OrderDate": st.column_config.DateColumn("訂單日期")
87	+	}
88	+	)
89	+
90	+	if st.button("更新資料庫"):
91	+	# 將編輯後的資料寫回資料庫
92	+	cursor = conn.cursor()
93	+	for index, row in edited_df.iterrows():
94	+	# 確保 OrderDate 是 datetime.date 類型，轉換為字串格式
95	+	if isinstance(row['OrderDate'], datetime):
96	+	order_date_str = row['OrderDate'].strftime('%Y-%m-%d')
97	+	elif isinstance(row['OrderDate'], pd.Timestamp):
98	+	order_date_str = row['OrderDate'].date().strftime('%Y-%m-%d')
99	+	elif isinstance(row['OrderDate'], date):
100	+	order_date_str = row['OrderDate'].strftime('%Y-%m-%d')
101	+	elif isinstance(row['OrderDate'], str):
102	+	# 嘗試解析字串為日期
103	+	try:
104	+	order_date = datetime.strptime(row['OrderDate'], '%Y-%m-%d').date()
105	+	order_date_str = order_date.strftime('%Y-%m-%d')
106	+	except ValueError:
107	+	st.error(f"無效的日期格式: {row['OrderDate']} (訂單編號: {row['OrderID']})")
108	+	continue
109	+	else:
110	+	st.error(f"無法解析的日期格式: {row['OrderDate']} (訂單編號: {row['OrderID']})")
111	+	continue
112	+
113	+	cursor.execute('''
114	+	UPDATE Orders
115	+	SET OrderDate = ?
116	+	WHERE OrderID = ?
117	+	''', (order_date_str, row['OrderID']))
118	+	conn.commit()
119	+	st.success("資料庫已更新！")
120	+	# Streamlit 會自動重新執行腳本，顯示更新後的資料
121	+
122	+	# 關閉資料庫連接
123	+	conn.close()
124	+
125	+	if __name__ == "__main__":
126	+	main()