Revision of SQLite 與 Streamlit st.data_editor 整合

timmy revised this gist 10 months ago. Go to revision

No changes

timmy revised this gist 11 months ago. Go to revision

No changes

timmy revised this gist 11 months ago. Go to revision

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 revised this gist 11 months ago. Go to revision

1 file changed, 126 insertions

sqlite_streamlit_join_example.py(file created)

		@@ -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()

Newer Older