Google

Cloud SDK

YouTube

BigQuery

console.cloud.google.com/bigquery

Link

Magic Commands

Magic Command: %sh

%sh ps | grep 'java'

Magic Command: Other Languages

%scala

println("Hello Scala!")
%python

print("Hello Python!")
%r

print("Hello R!", quote=FALSE)
%sql

select "Hello SQL!"

Magic Command: %md

%md

Magic Command: %run

  • You can run a notebook from another notebook by using the Magic Command %run
  • All variables & functions defined in that other notebook will become available in your current notebook
%run "./Includes/Classroom-Setup"

Magic Command: %fs

It is a wrapper around dbutils.fs and it is the Magic Command known as %fs.

The following call is equivalent to the display( dbutils.fs.ls("/mnt/training") ) – there is no real difference between the two.

%fs ls /mnt/training
%fs head /mnt/training/pageviews_by_second.tsv

Python PIP

Check if PIP is Installed

python -m pip --version
pip --version

update pip

pip install --upgrade pip

ตัวอย่างติดตั้ง package camelcase

pip install camelcase

การใช้ Package camelcase · PyPI

import camelcase

c = camelcase.CamelCase()
txt = "hello world"

print(c.hump(txt))           # Hello World
from camelcase import CamelCase
c = CamelCase()
s = 'this is a sentence that needs CamelCasing!'
print (c.hump(s))
# This is a Sentence That Needs CamelCasing!

*ดูจาก output เหมือนจะเป็น PascalCase ?

Find Packages

PyPI · The Python Package Index

Remove a Package

> pip uninstall camelcase

List Packages

> pip list

Python Variables

Creating Variables

x = 5                # int
y = "John"           # str
z = True             # bool

Case-Sensitive

x = 5
X = 10.0
print(x == X)        # False

Casting

str(obj) แปลงเป็นสตริง
int(s) แปลงเป็นเลขจำนวนเต็ม
float(s)
แปลงเป็นเลขทศนิยม

x = str(3)           # x will be str
y = int(3)           # y will be int
z = float(3)         # z will be float
x = int(1)           # x will be 1
y = int(2.8)         # y will be 2
z = int("3")         # z will be 3
x = float(1)         # x will be 1.0
y = float(2.8)       # y will be 2.8
z = float("3.5")     # z will be 3.5

hex(i) แปลงเป็นเลขฐานสิบหก
oct(i) แปลงเป็นเลขฐานแปด

x = hex(16)          # x will be str 0x10
y = oct(8)           # y will be str 0o10

ord(c) แปลงอักขระเป็นรหัสแอสกี้

x = ord('A')         # x will be int 65

.NET 5 – dotnet new console

สร้างโปรเจ็กส์ด้วย Template แบบ Console Application

dotnet new console -f net5.0 -o Console5

จะได้ไฟล์

  1. Console5\Console5.csproj
  2. Console5\Program.cs

ไฟล์ Console5.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

</Project>

ไฟล์ Program.cs

using System;

namespace Console5
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
        }
    }
}

ปัญหา Error – lzma

UserWarning: Could not import the lzma module.

/home/ctlfw/.local/lib/python3.7/site-packages/pandas/compat/__init__.py:120: UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError.
  warnings.warn(msg)

ทางแก้ติดตั้ง lzma-dev

For ubuntu: sudo apt-get install liblzma-dev

For centos: sudo yum install -y xz-devel

เข้าไปในโฟลเดอร์ที่ใช้ติดตั้ง Python แล้วรัน

Then sudo configure && sudo make && sudo make install

Python3 ติดต่อดาต้าเบส MySQL

Create Connection

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword"
)

print(mydb)
# <mysql.connector.connection_cext.CMySQLConnection object at 0x000002A56602E948>

หรือระบุ database

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
)

Creating a Database

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

Show databases

mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")

for x in mycursor:
    print(x)

Creating a Table

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

Show table

mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)

Alter table

mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

Delete table

mycursor.execute("DROP TABLE customers")

Insert Into Table

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

Insert Multiple Rows

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
    ('Peter', 'Lowstreet 4'),
    ('Amy', 'Apple st 652'),
    ('Hannah', 'Mountain 21'),
    ('Michael', 'Valley 345'),
    ('Sandy', 'Ocean blvd 2'),
    ('Betty', 'Green Grass 1'),
    ('Richard', 'Sky st 331'),
    ('Susan', 'One way 98'),
    ('Vicky', 'Yellow Garden 2'),
    ('Ben', 'Park Lane 38'),
    ('William', 'Central st 954'),
    ('Chuck', 'Main Road 989'),
    ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")

Select From a Table

We use the fetchall() method, which fetches all rows from the last executed statement.

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

fetchone()

The fetchone() method will return the first row of the result:

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchone()

print(myresult)

					

NumPy

ตรวจสอบเวอร์ชันของ numpy

import numpy
print(numpy.version.version)
# 1.19.5
import numpy as np
print(np.__version__)
# 1.19.5
> conda list

Arrays

The array object in NumPy is called ndarray

import numpy as np
arr = np.array([1, 2, 3, 4, 5])
print(arr)
print(type(arr))
# [1 2 3 4 5]
# <class 'numpy.ndarray'>

0-D Arrays

import numpy as np
arr = np.array(5)
print(arr)
print(type(arr))
# 5
# <class 'numpy.ndarray'>

1-D Arrays

import numpy as np
arr = np.array([1,2,3])
print(arr)
print(type(arr))
# [1 2 3]
# <class 'numpy.ndarray'>

2-D Arrays

import numpy as np

arr = np.array([[1, 2, 3], [4, 5, 6]])
print(arr)
print(type(arr))
# [[1 2 3]
#  [4 5 6]]
# <class 'numpy.ndarray'>

3-D Arrays

import numpy as np
arr = np.array([[[1, 2, 3], [4, 5, 6]], [[7, 8, 9], [10, 11, 12]]])
print(arr)
print(type(arr))
# [[[1 2 3]
#   [4 5 6]]
# 
#  [[ 7  8  9]
#   [10 11 12]]]
# <class 'numpy.ndarray'>

Number of Dimensions

import numpy as np
a = np.array(5)
b = np.array([1, 2, 3])
c = np.array([[1, 2, 3], [4, 5, 6]])
d = np.array([[[1, 2, 3], [4, 5, 6]], [[7, 8, 9], [10, 11, 12]]])

print(a.ndim)
print(b.ndim)
print(c.ndim)
print(d.ndim)
# 0
# 1
# 2
# 3

Define the number of dimensions by using the ndmin argument.

import numpy as np
arr = np.array([1, 2, 3, 4], ndmin=5)

print(arr)
print('number of dimensions :', arr.ndim)
# [[[[[1 2 3 4]]]]]
# number of dimensions : 5

NumPy Data Types

NumPy has some extra data types, and refer to data types with one character, like i for integers, u for unsigned integers etc.

  • i – integer
  • b – boolean
  • u – unsigned integer
  • f – float
  • c – complex float
  • m – timedelta
  • M – datetime
  • O – object
  • S – string
  • U – unicode string
  • V – fixed chunk of memory for other type ( void )
import numpy as np
arr = np.array([1, 2, 3, 4])
print(arr.dtype)
# int32
import numpy as np
arr = np.array(['apple', 'banana', 'cherry'])
print(arr.dtype)
# <U6
import numpy as np
arr = np.array([1, 2, 3, 4], dtype='S')
print(arr)
print(arr.dtype)
# [b'1' b'2' b'3' b'4']
# |S1
import numpy as np
arr = np.array([1, 2, 3, 4], dtype='i4')
print(arr)
print(arr.dtype)
# [1 2 3 4]
# int32

Converting Data Type

import numpy as np
arr = np.array([1.1, 2.1, 3.1])
newarr = arr.astype('i') # newarr = arr.astype(int)
print(newarr)
print(newarr.dtype)
# [1 2 3]
# int32
import numpy as np
arr = np.array([1, 0, 3])
newarr = arr.astype(bool)
print(newarr)
print(newarr.dtype)
# [ True False  True]
# bool

Array Copy vs View

The main difference between a copy and a view of an array is that the copy is a new array, and the view is just a view of the original array.

import numpy as np
arr = np.array([1, 2, 3, 4, 5])
x = arr.copy()
arr[0] = 42
print(arr)
print(x)
# [42  2  3  4  5]
# [1 2 3 4 5]
import numpy as np
arr = np.array([1, 2, 3, 4, 5])
x = arr.view()
arr[0] = 42
print(arr)
print(x)
# [42  2  3  4  5]
# [42  2  3  4  5]

Check if Array Owns it’s Data

copies owns the data, and views does not own the data

Every NumPy array has the attribute base that returns None if the array owns the data.

import numpy as np
arr = np.array([1, 2, 3, 4, 5])
x = arr.copy()
y = arr.view()

print(arr.base)
print(x.base)
print(y.base)
# None
# None
# [1 2 3 4 5]

Array Shape

Array Shape

import numpy as np
arr = np.array([[1, 2, 3, 4], [5, 6, 7, 8]])
print(arr.shape)
print(type(arr.shape))
# (2, 4)
# <class 'tuple'>
import numpy as np
arr = np.array([1, 2, 3, 4], ndmin=5)

print(arr)
print('shape of array :', arr.shape)
# [[[[[1 2 3 4]]]]]
# shape of array : (1, 1, 1, 1, 4)

Array Reshaping

Reshape From 1-D to 2-D

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12])
newarr = arr.reshape(4, 3)
print(newarr)
# [[ 1  2  3]
#  [ 4  5  6]
#  [ 7  8  9]
#  [10 11 12]]

Reshape From 1-D to 3-D

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12])
newarr = arr.reshape(2, 3, 2)
print(newarr)
# [[[ 1  2]
#   [ 3  4]
#   [ 5  6]]
# 
#  [[ 7  8]
#   [ 9 10]
#   [11 12]]]

Reshape return view

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6, 7, 8])
print(arr.reshape(2, 4).base)
# [1 2 3 4 5 6 7 8]

Unknown Dimension

You are allowed to have one “unknown” dimension.

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6, 7, 8])
newarr = arr.reshape(2, 2, -1) # newarr = arr.reshape(2, 2, 2)
print(newarr)
# [[[1 2]
#   [3 4]]
# 
#  [[5 6]
#   [7 8]]]

Flattening the arrays

Flattening array means converting a multidimensional array into a 1D array.

import numpy as np
arr = np.array([[1, 2, 3], [4, 5, 6]])
newarr = arr.reshape(-1)
print(newarr)
# [1 2 3 4 5 6]

Array Iterating

import numpy as np
arr = np.array([1, 2, 3])
for x in arr:
    print(x)
# 1
# 2
# 3
import numpy as np
arr = np.array([[1, 2, 3], [4, 5, 6]])
for x in arr:
    print(x)
# [1 2 3]
# [4 5 6]
import numpy as np
arr = np.array([[1, 2, 3], [4, 5, 6]])
for x in arr:
    for y in x:
        print(y)
# 1
# 2
# 3
# 4
# 5
# 6

nditer() – Iterating Arrays Using nditer()

import numpy as np
arr = np.array([[[1, 2], [3, 4]], [[5, 6], [7, 8]]])
for x in arr:
    print(x)
# [[1 2]
#  [3 4]]
# [[5 6]
#  [7 8]]
import numpy as np
arr = np.array([[[1, 2], [3, 4]], [[5, 6], [7, 8]]])
for x in np.nditer(arr):
    print(x)
# 1
# 2
# 3
# 4
# 5
# 6
# 7
# 8

nditer() – Iterating Array With Different Data Types

import numpy as np
arr = np.array([1, 2, 3])
for x in np.nditer(arr, flags=['buffered'], op_dtypes=['S']):
    print(x)
# b'1'
# b'2'
# b'3'

nditer() – Iterating With Different Step Size

import numpy as np
arr = np.array([[1, 2, 3, 4], [5, 6, 7, 8]])
for x in np.nditer(arr[:, ::2]):
    print(x)
# 1
# 3
# 5
# 7

ndenumerate()

import numpy as np
arr = np.array([1, 2, 3])
for idx, x in np.ndenumerate(arr):
    print(idx, x)
# (0,) 1
# (1,) 2
# (2,) 3
import numpy as np
arr = np.array([[1, 2, 3], [4, 5, 6]])
for idx, x in np.ndenumerate(arr):
    print(idx, x)
# (0, 0) 1
# (0, 1) 2
# (0, 2) 3
# (1, 0) 4
# (1, 1) 5
# (1, 2) 6

NumPy Joining Array

Joining NumPy Arrays

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.concatenate((arr1, arr2))
print(arr)
# [1 2 3 4 5 6]

Join two 2-D arrays along cols (axis=0)

import numpy as np
arr1 = np.array([[1, 2], [3, 4]])
arr2 = np.array([[5, 6], [7, 8]])
arr = np.concatenate((arr1, arr2), axis=0)
print(arr)
# [[1 2]
#  [3 4]
#  [5 6]
#  [7 8]]

Join two 2-D arrays along rows (axis=1)

import numpy as np
arr1 = np.array([[1, 2], [3, 4]])
arr2 = np.array([[5, 6], [7, 8]])
arr = np.concatenate((arr1, arr2), axis=1)
print(arr)
# [[1 2 5 6]
#  [3 4 7 8]]

stack()

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.stack((arr1, arr2), axis=0)
print(arr)
# [[1 2 3]
#  [4 5 6]]
import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.stack((arr1, arr2), axis=1)
print(arr)
# [[1 4]
#  [2 5]
#  [3 6]]

hstack()

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.hstack((arr1, arr2))
print(arr)
# [1 2 3 4 5 6]

vstack()

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.vstack((arr1, arr2))
print(arr)
# [[1 2 3]
#  [4 5 6]]

dstack()

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.dstack((arr1, arr2))
print(arr)
# [[[1 4]
#   [2 5]
#   [3 6]]]

Splitting Array

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6])
newarr = np.array_split(arr, 3)

print(type(newarr))
print(newarr)
# <class 'list'>
# [array([1, 2]), array([3, 4]), array([5, 6])]

print(newarr[0])
print(type(newarr[0]))
# [1 2]
# <class 'numpy.ndarray'>
import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6])
newarr = np.array_split(arr, 4)
print(newarr)
# [array([1, 2]), array([3, 4]), array([5]), array([6])]
import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6])
newarr = np.array_split(arr, 5)
print(newarr)
# [array([1, 2]), array([3]), array([4]), array([5]), array([6])]

Splitting 2-D Arrays

import numpy as np
arr = np.array([[1, 2], [3, 4], [5, 6], [7, 8], [9, 10], [11, 12]])
print(arr)
# [[ 1  2]
#  [ 3  4]
#  [ 5  6]
#  [ 7  8]
#  [ 9 10]
#  [11 12]]

newarr = np.array_split(arr, 3) # newarr = np.array_split(arr, 3, axis=0)
print(newarr[0])
print(newarr[1])
print(newarr[2])
# [[1 2]
#  [3 4]]
# [[5 6]
#  [7 8]]
# [[ 9 10]
#  [11 12]]
import numpy as np
arr = np.array([[1, 2], [3, 4], [5, 6], [7, 8], [9, 10], [11, 12]])
print(arr)
newarr = np.array_split(arr, 3, axis=1)
print(newarr[0])
print(newarr[1])
# [[ 1]
#  [ 3]
#  [ 5]
#  [ 7]
#  [ 9]
#  [11]]
# [[ 2]
#  [ 4]
#  [ 6]
#  [ 8]
#  [10]
#  [12]]
import numpy as np
arr = np.array([[1, 2], [3, 4], [5, 6], [7, 8], [9, 10], [11, 12]])
newarr = np.hsplit(arr, 2)
print(newarr[0])
print(newarr[1])
# [[ 1]
#  [ 3]
#  [ 5]
#  [ 7]
#  [ 9]
#  [11]]
# [[ 2]
#  [ 4]
#  [ 6]
#  [ 8]
#  [10]
#  [12]]




ติดตั้ง SQL Server 2017 บน Ubuntu 18.04

Install SQL Server 2017

1.Import the public repository GPG keys:

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

2.Register the Microsoft SQL Server Ubuntu repository:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2017.list)"

3.Run the following commands to install SQL Server:

sudo apt-get update
sudo apt-get install -y mssql-server

4.After the package installation finishes, run mssql-conf setup and follow the prompts to set the SA password and choose your edition.

sudo /opt/mssql/bin/mssql-conf setup

5.Once the configuration is done, verify that the service is running:

systemctl status mssql-server --no-pager

Install the SQL Server command-line tools

sudo apt install curl 

1.Import the public repository GPG keys.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

2.Register the Microsoft Ubuntu repository.

curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

3.Update the sources list and run the installation command with the unixODBC developer package. For more information, see Install the Microsoft ODBC driver for SQL Server (Linux).

sudo apt-get update 
sudo apt-get install mssql-tools unixodbc-dev

4.Optional: Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Connect locally

sqlcmd -S localhost -U SA -P '<YourPassword>'
SELECT Name from sys.Databases
GO

.NET Core ต่อ Database (Blazor)

สร้างโปรเจ็กส์ Blazor

> dotnet new blazorserver -o BlazorApp --no-https

รันโปรเจ็กส์ด้วยคำสั่ง dotnet run

> cd BlazorApp
> dotnet run

หรือ แก้ไขหน้า Page ดูผลการแก้ไขได้เลย

> dotnet watch run

ติดตั้ง System.Data.SqlClient

> dotnet add package System.Data.SqlClient --version 4.8.2

เพิ่ม Config ที่ไฟล์ appsettings.json

{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft": "Warning",
            "Microsoft.Hosting.Lifetime": "Information"
        }
    },
    "DbConfig": {
        "ServerName": "localhost",
        "DatabaseName": "myDatabase",
        "UserName": "myUsername",
        "Password": "myPassword"
    },
    "AllowedHosts": "*"
}

เพิ่มไฟล์ Data.Student.cs

using System;

namespace BlazorApp.Data
{
    public class Student
    {
        public int id { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string email { get; set; }
        public string mobile { get; set; }
    }
}

เพิ่มไฟล์ Data.StudentService.cs

using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace BlazorApp.Data
{
    public class StudentService
    {
        private IConfiguration config;
        public StudentService(IConfiguration configuration)
        {
            config = configuration;
        }

        private string ConnectionString
        {
            get
            {
                string _server = config.GetValue<string>("DbConfig:ServerName");
                string _database = config.GetValue<string>("DbConfig:DatabaseName");
                string _username = config.GetValue<string>("DbConfig:UserName");
                string _password = config.GetValue<string>("DbConfig:Password");
                return ($"Server={_server};Database={_database};User ID={_username};Password={_password};Trusted_Connection=False;MultipleActiveResultSets=true;");
            }
        }

        public async Task<List<Student>> GetStudent()
        {
            List<Student> students = new List<Student>();
            DataTable dt = new DataTable();
            SqlConnection con = new SqlConnection(ConnectionString);
            string sql = @"
SELECT * 
FROM Student
ORDER BY id";
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                Student std = new Student();
                std.id = Convert.ToInt32(row["id"]);
                std.firstname = row["Firstname"] as string;
                std.lastname = row["Lastname"] as string;
                std.email = row["Email"] as string;
                std.mobile = row["mobile"] as string;
                students.Add(std);
            }

            return await Task.FromResult(students);
        }
    } // end class
}

สร้าง Razor Page ชื่อ Pages/StudentList.razor

@page "/student-list"
@using BlazorApp.Data
@inject StudentService stdService

<h1>Student</h1>

@if (students == null)
{
    <div>There is no student</div>
}
else
{
    foreach (Student std in students)
    {
        <div style="padding:15px;border-bottom:solid 1px #0094ff;">
            @std.id | @std.firstname | @std.lastname | @std.email | @std.mobile
        </div>
    }
}

@code {
    private List<Student> students;
    protected override async Task OnInitializedAsync()
    {
        students = await stdService.GetStudent();
    }
}

Register service ใน Startup.cs

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddRazorPages();
            services.AddServerSideBlazor();
            services.AddSingleton<WeatherForecastService>();
            services.AddSingleton<StudentService>();
        }

รันและเรียกไปที่ http://localhost:5000/student-list

หรือแปะไว้หน้า index โดยแก้ไขไฟล์ Pages/index.razor

@page "/"

<h1>Hello, world!</h1>

Welcome to your new app.

<SurveyPrompt Title="How is Blazor working for you?" />

<StudentList />