96 lines
1.4 KiB
Markdown
96 lines
1.4 KiB
Markdown
|
||
## 記得安裝 pymysql
|
||
```
|
||
pip install pymysql
|
||
```
|
||
|
||
|
||
|
||
### 資料庫連線設定
|
||
```
|
||
import pymysql
|
||
db = pymysql.connect(host='140.125.21.65', port=3307, user='VIP125', passwd='@VIPvip125', db='VIP125',
|
||
charset='utf8')
|
||
```
|
||
|
||
|
||
### 建立操作游標
|
||
```
|
||
cursor = db.cursor()
|
||
```
|
||
|
||
### SQL語法（查詢資料庫版本）
|
||
```
|
||
sql = 'SELECT VERSION()'
|
||
```
|
||
|
||
### 執行語法
|
||
```
|
||
cursor.execute(sql)
|
||
```
|
||
|
||
### 選取第一筆結果
|
||
```
|
||
data = cursor.fetchone()
|
||
print("Database version : %s " % data)
|
||
|
||
```
|
||
|
||
### 資料讀取(全部)
|
||
```
|
||
sql = "select * from test_0525_01"
|
||
cursor.execute(sql)
|
||
data = cursor.fetchall()
|
||
print(f'ALL_data = {data}')
|
||
```
|
||
|
||
|
||
### 查詢資料(單獨)
|
||
```
|
||
sql = "SELECT user_id FROM test_0525_01;"
|
||
cursor.execute(sql)
|
||
data = cursor.fetchall()
|
||
print(f'user_id = {data}')
|
||
```
|
||
|
||
|
||
### 查詢資料(某筆)
|
||
```
|
||
sql = "SELECT * FROM test_0525_01 WHERE user_id='1000';"
|
||
cursor.execute(sql)
|
||
data = cursor.fetchall()
|
||
print(f'user_id=1000 : {data}')
|
||
|
||
```
|
||
|
||
### 刪除資料
|
||
```
|
||
sql = "DELETE FROM test_0525_01 WHERE user_id='1001';"
|
||
cursor.execute(sql)
|
||
db.commit()
|
||
```
|
||
|
||
|
||
### 更新資料
|
||
```
|
||
sql = "UPDATE test_0525_01 SET user_name='user_update_1000' WHERE user_id='1000';"
|
||
cursor.execute(sql)
|
||
db.commit()
|
||
```
|
||
|
||
|
||
### 資料寫入
|
||
```
|
||
test_data = ['1001', 'test_1001']
|
||
sql = "INSERT INTO test_0525_01 (user_id,user_name) VALUES (%s,%s);"
|
||
cursor.execute(sql, test_data)
|
||
db.commit()
|
||
```
|
||
|
||
|
||
### 關閉連線
|
||
```
|
||
db.close()
|
||
```
|
||
|