10/22
6.5hours for Tasks
WHERE condition1
AND condition2
將某個欄位設成index
<span class="n">df</span><span class="o">.</span><span class="n">set_index</span><span class="p">(</span><span class="s1">'month'</span><span class="p">)</span>
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html
sql = "select Name, count(*) as count from Info group by Name order by count DESC;"
<span class="n">python3</span> <span class="o">-</span><span class="n">m</span> <span class="n">venv .</span>
wtform
http://docs.jinkan.org/docs/flask/patterns/wtforms.html
https://segmentfault.com/a/1190000002531677
Open and Connect SQL 指令
brew services start mysql
<code><span class="pln">mysql </span><span class="pun">-</span><span class="pln">u root</span></code>
10/23
5.5 hours Tasks
10/24
3.5 hours Tasks
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1292, “Incorrect datetime value: ‘1984’ for column ‘UpdateTime’ at row 1”)
sql = “select …….”
engine.execute(" sql + ’{update_time}’;)".format(update_time=update_time)
我寫了一個將sqlite 資料庫的欄位Column, Table, 資料 Data 抓出來的python (2 hours)
find . | grep -c sqlite
import pandas as pd
import sqlite3
conn = sqlite3.connect('population.db')
query = "SELECT country FROM Population WHERE population > 50000000;"
df = pd.read_sql_query(query, conn)
for country in df['country']:
print(country)
https://pythonspot.com/sqlite-database-with-pandas
# -*- coding: utf-8 -*-
import sqlite3
import pandas as pd
def to_csv(name):
path = "./{name}/".format(name=name)
filename = name
dbname = filename
con = sqlite3.connect('./{filename}/{dbname}.sqlite'.format(filename=filename, dbname=dbname))
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()
for table_name in tables:
table_name = table_name[0]
table = pd.read_sql_query("SELECT * from %s" % table_name, con)
table.to_csv(table_name + '.csv', index_label='index')
# if you dont want index, fix above to this
# change index = False / index_label = 'index'
table.to_csv(path + table_name + '.csv', index=False)
12/25
3 hours
https://treyhunner.com/2016/04/how-to-loop-with-indexes-in-python/
簡易從巨大json檔抓出主要欄位，寫進csv
# -*- coding: utf-8 -*-
import json
import csv
# 打開你的json檔
with open('example.json', encoding='utf-8') as data_file:
data = json.loads(data_file.read())
#開一個csv
with open('output.csv', 'w') as csvfile:
# 定義欄位
Columns = ['name', 'feature', 'place']
writer = csv.DictWriter(csvfile, fieldnames = Columns)
writer.writeheader()
# 希望抓出某個值在這以內
a_list = ['apple', 'banana', 'water', 'store']
# 我一開始讀的json是一大串list, 所以定義i為data的迴圈變數
for i in data:
for a in a_list:
if i['name'] == a:
name = a
feature = i['feature']
# 這邊做查找的動作，會有兩個以上結果，所以用List串起來
f_list = []
# 咱今天技能的大主角 ****enumerate****
# 假設 feature 有很多種 (這邊是list字串中查找)
for k, t in enumerate(feature):
# 判斷這些特徵有沒有在這裡
if t == "RED":
# 因為我想要取得RED 後面一個值XD
f_list.append(feature[k+1])
elif t =="CIRCLE":
f_list.append(feature[k+1])
place = i['place']
# 寫進csv啦！！！！
writer.writerow(
{'name': name, 'feature': f_list, 'place': place})
終於要進入今天學到的技能啦
大神教導的enumerate
學習新的 enumerate
是大神教我的!! 點擊新的技能樹!!!!
其實之前就看過大神寫過for迴圈兩個變數
但是到現在都還是不知道怎麼用qq
使用enumerate 是因為我們在從一串list判斷字，用if條件判斷某個字有出現重複時，取字的index值出現問題，導致我們不能使用他list給的值，就像上面的例子一樣
我們的list其實長這樣
a_list = ['apple', 'banana', 'water', 'store', 'apple', 'orange', 'apple']
一開始用的方法是
#有點忘了是不是這樣寫
for i in a_list:
if a_list[i]=="apple"
index = a_list.index(i)
...
但後來發現他只會辨識第一個apple，後面幾個的apple都抓不到
於是，咱的大神出馬!!
教我的enumerate
其實就是代兩個變數
t 代表的是項目
也就是本來在a_list內的每個項目 (所以他會每個項目去辨識)
k是重新賦予每個項目index值，等於重新排列
利用兩個變數去做辨識就更精準!!!
有沒有很magic，雖然我還是不大會qq，但我會努力的
create_list = []
for k, t in enumerate(a_list):
if t == "apple": #
create_list.append(a_list[k+1])
elif t =="store":
create_list.append(a_list[k+1])
附上學習網址
https://stackoverflow.com/questions/522563/accessing-the-index-in-for-loops
https://treyhunner.com/2016/04/how-to-loop-with-indexes-in-python/
好啦這週學習筆記就到這!
