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

ติดตั้ง Graphviz บน Ubuntu 18.04

ติดตั้ง Graphviz

sudo apt install graphviz
sudo yum install graphviz

ดูเวอร์ชันที่ติดตั้ง

$ dot -V
dot - graphviz version 2.40.1 (20161225.0304)

ทดลองใช้งานทาง Command Line

echo 'digraph { a -> b }' | dot -Tsvg > output.svg
echo 'digraph { a -> b }' | dot -Tsvg -o output.svg

ใส่ข้อความและกำหนดสีของข้อความ (set a graph attribute -Gname[=value])

echo 'digraph { a -> b }' | dot -Tsvg -Gfontcolor=red -Glabel="My favorite letters" > output2.svg

กำหนดรูปแบบของโหนด (set a default node attribute -Nname[=value] )

echo 'digraph { a -> b }' | dot -Tsvg -Nfontcolor=red -Nshape=rect > output3.svg

กำหนดรูปแบบของ edge และลูกศร (set a default edge attribute -Ename[=value])

บันทึกไฟล์ output เป็น png หรือฟอร์แมตอื่นๆดู supported formats

echo 'digraph { a -> b }' | dot -Tpng -Ecolor=red -Earrowhead=diamond > output4.png

ติดตั้ง Git บน CentOS 7

ติดตั้ง Git

Install Git with Yum

sudo yum install git

Install Git with apt

sudo apt install git

ตรวจสอบการติดตั้ง

git --version

Set Up Git

git config --global user.name "Your Name"
git config --global user.email "you@example.com"

Checking Your Settings

git config --list

You can view all of your settings and where they are coming from using:

git config --list --show-origin

Your default branch name

By default Git will create a branch called master when you create a new repository with git init. From Git version 2.28 onwards, you can set a different name for the initial branch.

git config --global init.defaultBranch main

Getting a Git Repository

Initializing a Repository in an Existing Directory

cd /home/user/my_project
git init
git add *.c
git add LICENSE
git commit -m 'Initial project version'

Cloning an Existing Repository

You clone a repository with git clone <url>. For example, if you want to clone the Git linkable library called libgit2, you can do so like this:

git clone https://github.com/libgit2/libgit2

If you want to clone the repository into a directory named something other than libgit2, you can specify the new directory name as an additional argument:

git clone https://github.com/libgit2/libgit2 mylibgit

Recording Changes to the Repository

Checking the Status of Your Files

git status

ถ้าจะให้แสดง diff (staged) ด้วยก็

git status -v

หรือถ้าจะดูสรุปสั้นๆก็

git status -s
$ echo 'My Project' > README
$ git status
On branch master
Your branch is up-to-date with 'origin/master'.
Untracked files:
  (use "git add <file>..." to include in what will be committed)

    README

nothing added to commit but untracked files present (use "git add" to track)

Tracking New Files

git add README
$ git status
On branch master
Your branch is up-to-date with 'origin/master'.
Changes to be committed:
  (use "git restore --staged <file>..." to unstage)

    new file:   README

Ignoring Files

$ cat .gitignore
*.[oa]
*~

Viewing Your Staged and Unstaged Changes

To see what you’ve changed but not yet staged, type git diff with no other arguments:

git diff

If you want to see what you’ve staged that will go into your next commit, you can use git diff --staged or git diff --cached.

git diff --staged

หรือ

git status -v

Committing Your Changes

git commit -m "Story 182: fix benchmarks for speed"

คำสั่งพื้นฐาน Ubuntu 18.04

โปรแกรมน่าติดตั้ง

sudo apt update 
sudo apt install curl
sudo apt install tree

Uninstall

sudo apt-get remove --auto-remove libcurl4-openssl-dev

เปิด Terminal กด Ctrl-Alt-T

ดู hostname

hostnamectl

ดู current directory (print working directory)

pwd

get back to home directory:

cd

whoami command will remind you of your username

whoami

ls (list) command:

ls
ls -l
ls -a

Creating folders

mkdir /tmp/tutorial
mkdir dir1 dir2 dir3
$ mkdir --help
Usage: mkdir [OPTION]... DIRECTORY...
Create the DIRECTORY(ies), if they do not already exist.

Mandatory arguments to long options are mandatory for short options too.
  -m, --mode=MODE   set file mode (as in chmod), not a=rwx - umask
  -p, --parents     no error if existing, make parent directories as needed
  -v, --verbose     print a message for each created directory
  -Z                   set SELinux security context of each created directory
                         to the default type
      --context[=CTX]  like -Z, or if CTX is specified then set the SELinux
                         or SMACK security context to CTX
      --help     display this help and exit
      --version  output version information and exit

GNU coreutils online help: <http://www.gnu.org/software/coreutils/>
Full documentation at: <http://www.gnu.org/software/coreutils/mkdir>
or available locally via: info '(coreutils) mkdir invocation'
mkdir -p -v dir4/dir5/dir6

Creating files using redirection

ls > output1.txt
cat output1.txt

echo "This is a test1" > output2.txt
echo "This is a test2" >> output2.txt

cat output1.txt output2.txt
cat t* > combined.txt

When viewing a file through less you can use the Up Arrow, Down Arrow, Page Up, Page Down, Home and End keys to move through your file

less combined.txt

Moving and manipulating files

mv combined.txt dir1
mv dir1/* .

The mv command also lets us move more than one file at a time. If you pass more than two arguments, the last one is taken to be the destination directory and the others are considered to be files (or directories) to move.

mv combined.txt test_* dir3 dir2
cp dir4/dir5/dir6/combined.txt .
rmdir folder_*
rm -r folder_6

Word count

wc -l combined.txt
$ wc --help
Usage: wc [OPTION]... [FILE]...
  or:  wc [OPTION]... --files0-from=F
Print newline, word, and byte counts for each FILE, and a total line if
more than one FILE is specified.  A word is a non-zero-length sequence of
characters delimited by white space.

With no FILE, or when FILE is -, read standard input.

The options below may be used to select which counts are printed, always in
the following order: newline, word, character, byte, maximum line length.
  -c, --bytes            print the byte counts
  -m, --chars            print the character counts
  -l, --lines            print the newline counts
      --files0-from=F    read input from the files specified by
                           NUL-terminated names in file F;
                           If F is - then read names from standard input
  -L, --max-line-length  print the maximum display width
  -w, --words            print the word counts
      --help     display this help and exit
      --version  output version information and exit

GNU coreutils online help: <http://www.gnu.org/software/coreutils/>
Full documentation at: <http://www.gnu.org/software/coreutils/wc>
or available locally via: info '(coreutils) wc invocation'
ls ~ | wc -l
ls /etc | less
cat combined.txt | uniq | wc -l
man uniq | less
sort combined.txt | less
sort combined.txt | uniq | wc -l

tree

sudo apt install tree
$ tree /home/
/home/
└── jack
    ├── Desktop
    ├── Documents
    ├── Downloads
    ├── Music
    ├── Pictures
    ├── Public
    ├── Templates
    └── Videos

9 directories, 0 files
tree
tree -a

.NET Core ต่อ Database (Console#2)

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

> dotnet new console -o ConsoleApp

เพิ่มไฟล์ .gitignore (optional)

*.swp
*.*~
project.lock.json
.DS_Store
*.pyc
nupkg/

# Visual Studio Code
.vscode

# Rider
.idea

# User-specific files
*.suo
*.user
*.userosscache
*.sln.docstates

# Build results
[Dd]ebug/
[Dd]ebugPublic/
[Rr]elease/
[Rr]eleases/
x64/
x86/
build/
bld/
[Bb]in/
[Oo]bj/
[Oo]ut/
msbuild.log
msbuild.err
msbuild.wrn

# Visual Studio 2015
.vs/

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

> cd ConsoleApp
> dotnet run

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

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

ติดตั้ง Microsoft.Extensions.Configuration

> dotnet add package Microsoft.Extensions.Configuration --version 5.0.0

ติดตั้ง Microsoft.Extensions.Configuration.Json

> dotnet add package Microsoft.Extensions.Configuration.Json --version 5.0.0

ติดตั้ง Microsoft.Extensions.Configuration.Binder

> dotnet add package Microsoft.Extensions.Configuration.Binder --version 5.0.0

สร้างไฟล์ appsettings.json

{
    "DbConfig": {
        "ServerName": "localhost",
        "DatabaseName": "myDatabase",
        "UserName": "myUsername",
        "Password": "myPassword"
    }
}

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

using System;

namespace ConsoleApp.Model
{
    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.StudentDAL.cs

using ConsoleApp.Model;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApp.Data
{
    public class StudentDAL
    {
        private string _connectionString;

        public StudentDAL(IConfiguration config)
        {
            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");
            _connectionString = ($"Server={_server};Database={_database};User ID={_username};Password={_password};Trusted_Connection=False;MultipleActiveResultSets=true;");
        }
        public List<Student> GetList()
        {
            List<Student> students = new List<Student>();
            try
            {
                using (SqlConnection con = new SqlConnection(_connectionString))
                {
                    DataTable dt = new DataTable();
                    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);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return students;
        }
    }
}

แก้ไขไฟล์ Program.cs

using ConsoleApp.Data;
using Microsoft.Extensions.Configuration;
using System;
using System.IO;
using System.Text.Json;

namespace ConsoleApp
{
    class Program
    {
        private static IConfiguration _iconfiguration;
        static void Main(string[] args)
        {
            GetAppSettingsFile();

            var studentDAL = new StudentDAL(_iconfiguration);
            var students = studentDAL.GetList();
            students.ForEach(item =>
            {
                string jsonString = JsonSerializer.Serialize(item);
                Console.WriteLine(jsonString);
            });
        }

        static void GetAppSettingsFile()
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
            _iconfiguration = builder.Build();
        }
    }
}

.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 />

Python3

Set Python’s default version to 3.x on OS X

Open ~/.bash_profile file.

$ nano ~/.bash_profile

Then put the alias as follows:

alias python='python3'

Now save the file and then run the ~/.bash_profile file.

$ source ~/.bash_profile

Congratulation !!! Now, you can use python3 by typing python.

$ python --version
Python 3.8.1

Upgrade pip command

$ pip3 install --upgrade pip

Install virtualenv

$ pip3 install virtualenv

ใช้ brew ติดตั้ง python

$ brew install python