Python-資料庫-mysql-pymysql

Python
資料庫
mysql-pymysql

安裝：
linux、mac：pip3 install PyMySql
windows    : 之後補充

import pymysql.cursors

connection = pymysql.connect(host='ip',
user='root', password='password', db='database_name',charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)

#    CREATE TABLE `users` (
#    `id` int(11) NOT NULL AUTO_INCREMENT,
#    `email` varchar(255) COLLATE utf8_bin NOT NULL,
#    `password` varchar(255) COLLATE utf8_bin NOT NULL,
#    PRIMARY KEY (`id`)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
#AUTO_INCREMENT=1 ;


with connection.cursor() as cursor:
    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
    cursor.execute(sql, ('ab123ab456g@example.com', 'very-secret'))

connection.commit()

with connection.cursor() as cursor:
    sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('ab123ab456g@example.com',))
    result = cursor.fetchone()
    print(result)


with connection.cursor() as cursor:
    sql = "Update  `password`=%s FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('new_password','ab123ab456g@example.com'))

with connection.cursor() as cursor:
    sql = "Delete FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('ab123ab456g@example.com'))

connection.close()

程式碼說明

匯入module
建立connection
＃字號後面建立table的SQL語法
第一個cursor，Insert
第二個cursor，Search
第三個cursor，Update
第四個cursor，Delete
sql：sql語法
cursor：指標的概念
execute：執行指令
close：一定要關閉

這邊在看的重點是，如何建立連結、如何執行指令