9/12
看“資料視覺化，使用python與js”書
p313始
Review 昨日練習的程式
https://blog.csdn.net/yjk13703623757/article/details/77918633
if __name__ == "__main__"
直接運用時，會跳出結果，被當作model運用時，不會跳出結果
https://hk.saowen.com/a/7b4781d9034b94d7e09ae77580efcc1d8f800b5657b7bce836e39ed69f731d98
http://www.itfanr.cc/2017/01/06/use-sqlalchemy-by-python/
leetcode
https://leetcode.com/problemset/all/
Flask
夭壽簡單
跟Django比的話
flask-sqlalchemy
http://flask-sqlalchemy.pocoo.org/2.3/quickstart/
(早上2hr)
Try flask-SQLAlchemy in local db(30min)(Complete): To test Flask-sqlalchemy can work to local db
https://blog.techbridge.cc/2017/08/12/python-web-flask101-tutorial-sqlalchemy-orm-database-models/
Try flask-SQLAlchemy input data to mysql (從9/11下午4點-9/12下午)(Compete)
https://ithelp.ithome.com.tw/articles/10197702
錯誤找到：
usr = User('sara','sara@gmail')
db.session.add(usr) #usr 不小心寫成User
add資料參數寫錯
成就達成：利用Flask-SQLAlchemy 操作雲端上已存在mysql資料庫
(範例：已改過內容)
hello.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import datetime
import pymysql
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://usr:pw@ip:port/db'
db = SQLAlchemy(app)
class User(db.Model):
__tablename__ = 'User'
ID = db.Column(db.Integer, primary_key=True)
Name = db.Column(db.String(64))
Time = db.Column(db.DateTime)
Value = db.Column(db.Float)
Boolean = db.Column(db.Integer)
def__init__(self, name, time, value, boolean):
self.Name = name
self.Time = time
self.Value = value
self.Boolean = boolean
def error_info():
new_usr = User(u'安娜', datetime.datetime(2018, 1, 1), 4.4, 1)
print(db)
print(db.session)
db.session.add(new_usr)
db.session.commit()
@app.route('/api/usr')
def usr():
error_info()
return"OK"
$FLASK_APP=hello.py flask run
去瀏覽器打 http://localhost:5000/api/usr (看終端機上面運行什麼網址做修改)
若有看到回傳OK就是將資料上傳上去資料庫了
補充pymysql 傳輸雲端資料庫方法
hello.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql
from flask import Flask
app = Flask(__name__)
def insert_record():
# 接資料庫
conn = pymysql.connect(host='ip', port=portnum, user='usr', passwd='pw', db='dbname', charset='utf8')
# 自動傳送SQL
conn.autocommit(True)
cur = conn.cursor()
insert_usr = cur.execute("INSERT INTO User(Name, Time, Value, Boolean) VALUES ('Anna','20180101',3.5,6")
# 上傳
conn.commit()
cur.close()
# 關閉連接
conn.close()
@app.route('/api/sql')
def driver():
insert_record()
return"OK"
$FLASK_APP=hello.py flask run
http://localhost:5000/api/sql
看到回傳OK就是成功
下個任務：將固定資料改為接受參數 » 上傳至資料庫(9/12 15:37始)
http://flask.pocoo.org/docs/1.0/api/#incoming-request-data
Download Postman
成就達成：GET方式OK
使用Flask Request /POST達成任務，查詢資料時間(約3個小時/午4:00~晚7:00)
https://www.letiantian.me/2014-06-23-flask-get-url-params/
https://codehandbook.org/flask-restful-api-using-python-mysql/
9/13
先利用post方式接收參數，融合SQLAlchemy上傳資料進Mysql(5小時/早10~下午3:30)
**＊注意＊：**Debug技巧：嘗試一個一個函數慢慢加下去，不要一次寫完在測
卡住函數接收問題
解法：一個一個函數慢慢去放
https://developer.mozilla.org/zh-TW/docs/Web/HTTP/Status
＊注意＊：新學的DeBug語法/ ngrep
brew install ngrep
sudo ngrep -d any -W byline port [port number]
＊注意＊：新學的DeBug語法/ Try, Except, BaseException
http://www.runoob.com/python/python-exceptions.html
<strong>try:</strong>
new_usr = User(name, email, work, age)
print(db)
print(db.session)
db.session.add(new_usr)
db.session.commit()
print (request.values)
<strong>return "OK"</strong>
<strong>
except BaseException as e:</strong>
<strong>return str(e)</strong>
＊注意＊：資料欄位型別 / 測試資料輸入/ 查詢關鍵字下format
https://help.navicat.com/hc/en-us/articles/218303657-What-is-MySQL-s-default-DATE-DATETIME-format-
卡住最大問題：
-
Postman測試打錯
-
資料欄位型別DataTime輸入資料格式錯誤
將ErrorInfo 及 TestLogInfo 動態傳送/固定值傳送Pymysql & SQLAlchemy寫完(1小時20分 ~午5:20)
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import datetime
import pymysql
app = Flask(__name__)
#app.config.from_object(config)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://usr:pw@ip:port/dbname'
db = SQLAlchemy(app)
#Class Table 欄位及型態要跟雲端上的db一樣
class User(db.Model):
__tablename__ = 'User'
ID = db.Column(db.Integer, primary_key=True)
Name = db.Column(db.String(64))
Time = db.Column(db.DateTime)
Value = db.Column(db.Float)
Boolean = db.Column(db.Integer)
def__init__(self, name, time, value, boolean):
self.Name = name
self.Time = time
self.Value = value
self.Boolean = boolean
# 接收值，上傳db
def user_post():
if request.method =='POST':
name = request.args.get('Name')
time = request.args.get('Time')
value = request.args.get('Value')
boolean = request.args.get('Boolean')
#try:
#return str(request.values)
new_usr = User(name, time, value, boolean)
print(db)
print(db.session)
db.session.add(new_usr)
db.session.commit()
print (request.values)
return"OK"
# User 動態值上傳
@app.route('/api/usr', methods=['GET', 'POST'])
def usr():
user_post()
return "User Upload OK"
**＊注意＊：**若用Postman測資料，記得要丟符合欄位型態的資料！
#SQLAlchemy的大小寫變數不同喔！
#M要大寫
db.Model
#C要大寫
db.Column
筆記一下高手傳送的Youtube
建議我利用YouTube來找找學習的影片
https://www.youtube.com/watch?v=QJtWxm12Eo0
https://www.youtube.com/watch?v=UkPn3q_t4vU
https://www.youtube.com/watch?v=72Alzsz8RyI
要敢問才會比較快變厲害，同樣的情境要能自己解，就會有實質的進步！
https://github.com/mitsuhiko/flask-sqlalchemy
可以試試看挑戰不要定義一堆欄位名稱，想辦法詢問資料庫需要填哪些欄位，再串request
直接用for迴圈串
