Chen-Chih Technical Note Blog

Tech Notes & Tutorials

0%

Posted on

我想分享一些基本的DB(MYSQL)語法。之前面試有考不管是在Amazon，或Microsoft 都有考過我DB。我那時有準備筆記，但後來就不知跑哪。今天想在這篇寫關於SQL基本指令，有機會再寫高階的語法。

我住前是用MYSQL的DB軟體，你們可以選用其他的如MogoDB, Microsoft SQL server，orlacle DB等等。基本上語法都差不多，我猜的

  • Table: employee:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    +----+-------+--------+--------------+
    | id | Name  | Salary | DepartmentID |
    +----+-------+--------+--------------+
    |  1 | Joe   | 85000  | 1            |
    |  2 | Henry | 80000  | 2            |
    |  3 | Sam   | 60000  | 1            |
    |  4 | Max   | 90000  | 1            |
    |  5 | Janet | 69000  | 1            |
    |  6 | Randy | 85000  | 1            |
    +----+-------+--------+--------------+

SELECT & FROM

select * from

select 可以選擇要印那一欄，如果加*可以把所有內如印出來，以下是指令:

select * from employee;

1
2
3
4
5
6
7
8
9
10
11
Table: employee;
+----+-------+--------+--------------+
| id | Name  | Salary | DepartmentID |
+----+-------+--------+--------------+
|  1 | Joe   | 85000  | 1            |
|  2 | Henry | 80000  | 2            |
|  3 | Sam   | 60000  | 1            |
|  4 | Max   | 90000  | 1            |
|  5 | Janet | 69000  | 1            |
|  6 | Randy | 85000  | 1            |
+----+-------+--------+--------------+

select Name 指定欄位

SELECT Name, Salary From Employee;

Read more »

Posted on Edited on In

An unorder collection of a unquie object. No duplicate value.
不可以有重複的直，一般都是用在帳號上面。

Create set

1
2
my_set= {1,2,3,4,5}
print(my_set)

output:

{1, 2, 3, 4, 5}

如果加重複會用最前面的

1
2
my_set= {1,2,3,4,5,5}
print(my_set)

output:

{1, 2, 3, 4, 5}

adding set

1
2
3
4
5
my_set= {1,2,3,4,5,5}
my_set.add(100)
my_set.add(2)
print(my_set)

output

‘{1, 2, 3, 4, 5, 100}`

Read more »

Posted on Edited on In

今天想分享如何用 dictionary 字典方式存資料。

key and Value 如何使用宣槓取直

  • collection of of {key:value} pairs
  • no duplicate
  • allow changeable and ordered

declare dictionary 宣告 dict

It's an unorder , it's not next to each other, so we can't use index like we use in list. Dictionary will have a a key and value look like this:

syntax:

declare: dictname= {key:value}
get key: print(dictname['key'])

Access and Get value 取我們的直:

1
2
3
4
dict={
    'keyA':1,
    'keyB':2
}

取 key 的直: print(dict['keyA']) => 1
取所有直 print(dict) => {'keyA': 1, 'keyB': 2}

宣告不同 data type

Read more »

Posted on Edited on In

Tuple is like list, but we can;t modify it, they are immutable. Tuple immutable 直，就是他的直不無法修改的，有些語法是用const類似這概念。

Create Tuple 建立tuple

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# Empty tuple
my_tuple = ()
print(my_tuple) #()

# Tuple having integers
my_tuple = (1, 2, 3) 
print(my_tuple) #(1,2,3)

# tuple with mixed datatypes
my_tuple = (1, "Hello", 3.4) 
print(my_tuple) #(1, "Hello", 3.4) 

# multiply value
x,y,z, *other = (1,2,3,4,5)
print(f"x: {x}, y:{y}, z:{z}, other:{other}")
#x: 1, y:2, z:3, other:[4, 5]

my_tuple = ("mouse", [8, 4, 6], (1, 2, 3))
print(my_tuple) #('mouse', [8, 4, 6], (1, 2, 3))

created without using parentheses

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
my_tuple = 3, 4.6, "dog"
print(my_tuple)

# tuple unpacking is also possible
a, b, c = my_tuple

print(a)      # 3
print(b)      # 4.6
print(c)      # dog

my_tuple = ("hello")
print(type(my_tuple))  # <class 'str'>

# Creating a tuple having one element
my_tuple = ("hello",)
print(type(my_tuple))  # <class 'tuple'>

# Parentheses is optional
my_tuple = "hello",
print(type(my_tuple))  # <class 'tuple'>

Access Tuple Elements

1
2
3
4
5
6
7
8
9
10
11
12
# Accessing tuple elements using indexing
my_tuple = ('p','e','r','m','i','t')

print(my_tuple[0])   # 'p' 
print(my_tuple[5])   # 't'

# nested tuple
n_tuple = ("mouse", [8, 4, 6], (1, 2, 3))

# nested index
print(n_tuple[0][3])  #'s'
print(n_tuple[1][1])  # 4

# Negative indexing for accessing tuple elements

1
2
3
4
5
6
7
8
my_tuple = ('p', 'e', 'r', 'm', 'i', 't')

# Output: 't'
print(my_tuple[-1])
# elements beginning to 2nd
# Output: ('p', 'r')
print(my_tuple[:-7])

Slicing

Read more »

Posted on Edited on In

python 有很多不同 function，因此我想在這篇寫所有 funtion 我會用到。我指的 function 不是傳 function，而是一些 libary function

Math

Datetime 時間

time

程式跑的時間

如果計算程式袍多就可以用這方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import time

# starting time
start = time.time()
# your code
for i in range(1,60):
    print(i)

#endtime
end = time.time()
runtime=end - start
print(" Runtime of the program is {runtime:%.8f}")
print(f"Runtime of the program is {runtime:.5f}")
#or
print(f"Runtime of the program is {(end - start):.5f}")

datetime

今天日期

  • date.today 取今天日期
Read more »

Posted on Edited on In

Pandas 筆記

今天我想分享我學的 pandas 筆記，我覺得對於分大數據很有幫助，他有點像 mysql。請先下載 module 或 package:pip install pandas`

Part1 基本語法

連線 CSV 檔案

可以去 github 找相關 csv 檔案，然後去 raw 檔案做連線

1
2
3
4
5
import pandas as pd
url = "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/tips.csv"
tips = pd.read_csv(url)

print(tips)

pandas 會回傳 DataFrame 格式。<class 'pandas.core.frame.DataFrame'>
下面是印出所有資料表內容。

1
2
3
4
5
6
7
8
9
10
11
12
     total_bill   tip     sex smoker   day    time  size
0         16.99  1.01  Female     No   Sun  Dinner     2
1         10.34  1.66    Male     No   Sun  Dinner     3
2         21.01  3.50    Male     No   Sun  Dinner     3
3         23.68  3.31    Male     No   Sun  Dinner     2
4         24.59  3.61  Female     No   Sun  Dinner     4
..          ...   ...     ...    ...   ...     ...   ...
239       29.03  5.92    Male     No   Sat  Dinner     3
240       27.18  2.00  Female    Yes   Sat  Dinner     2
241       22.67  2.00    Male    Yes   Sat  Dinner     2
242       17.82  1.75    Male     No   Sat  Dinner     2
243       18.78  3.00  Female     No  Thur  Dinner     2

Head and Tail

  • head(N): 是查前面 N 筆資料
Read more »

Posted on Edited on In

Create empty Excel File

1
2
3
4
import openpyxl
fn = 'new_excel.xlsx'
wb = openpyxl.Workbook()
wb.save(fn)

Create Sheet工作表的

Create Empty Sheet 新增工作

1
2
3
4
5
6
import openpyxl
fn = 'new_excel.xlsx'
wb = openpyxl.Workbook()
wb.create_sheet("Sheet1", 0) 
wb.create_sheet("Sheet2", 1)
wb.save(fn)

Read Excel Sheet 讀取excel檔案每個工作表的名稱

1
2
3
4
5
6
import openpyxl
fn = 'new_excel.xlsx'
wb = openpyxl.load_workbook(fn)
print(wb.sheetnames)
print(wb.active)
print(wb.active.title)

output: 

1
2
3
['Sheet1', 'Sheet2', 'Sheet']
<Worksheet "Sheet1">
Sheet1

edit Name of worksheet 修改工作表名稱

Read more »

Posted on Edited on In

list 列印

這裡會包含以下內內容:

  • 基本讀 list 搭配 for
  • *list (快速讀 list 不需要用到 for loop)
  • N 筆後換
  • 轉換 list 到字串(搭配 join)
  • enumerate 方法
  • Method:
    • split
    • slice
    • copy
    • zip
    • 轉換 list 理的 tuple 到 list

常用的方式印 list 內容

list : list = ['A','B','C','D','E','F']

我們可以用以下方法:

- for loop

1
2
3
4
list= [1,2,3]
for i in list:
    print(i, end=" ") #
print()

output:

1 2 3

- *list: 不需要 for loop ，可以 unpack list

Read more »

Posted on Edited on In

今天分享如何搜尋字串裡面的 substring 或如果有檔案(log)想找相關字來做分析。

split() 方式

我的 string 是:

st="[20221013.162853.788442][info]:[>>> DL- ingress traffic: 0.010799(Mbps), egress traffic: 0.087016(Mbps), ReTx: 0.000220(Mbps)]"

我想抓出時間和TPUT的直，如:
[20221013.162853.788442] 和 0.010799(Mbps) 同時要把 [] 和 (Mbps)移除，可以用下面方式:

  • Step1: 宣告我的 string
1
st="[20221013.162853.788442][info]:[>>> DL- ingress traffic: 0.010799(Mbps), egress traffic: 0.087016(Mbps), ReTx: 0.000220(Mbps)]"
  • Step2 抓取時間直
1
2
dateStr = st.split('[', 1)[1].split(']')[0]
print(dateStr) #20221013.162853.788442

output:

20221013.162853.788442

Read more »

Posted on Edited on In

Open Read Write File

open syntax:　

open( file, mode, encoding="utf-8")

mode 字串 說明
r Read only 讀取模式 ( 預設 )
r+ Read and write
w Write only 寫入模式，檔案若存在，會清空內容再寫入； 若檔案不存在，則建立新檔開啟寫入
w+ write+read
a Append only 附加模式，若檔案存在，則寫入內容會附加至檔 案尾端
a+ Append and Read (‘a+’)

Read

讀取方法 描述
read( ) 一次讀取檔案所有的內容，回傳為字串
readline( ) 只讀取一行內容
readlines( ) 將所有檔案內容，每行讀入回傳為串列 
1
2
3
fileObj = open("路徑檔名" , "r", encoding="utf-8") 
content = fileObj.read( ) 
fileObj.close( )

Write

1
2
3
fileObj = open("路徑檔名" , "w", encoding="utf-8")
fileObj.write(輸出資料)
fileObj.close( )

Example

Ex1. read file and print

Read more »