pymysql - 阿倫的秘密基地

pymysql是python連接MySQL時所使用的套件，如果有大量的資料須處理使用資料庫會更有效率

由於之前安裝的phpMyAdmin裡的內建test資料庫不是utf8編碼的，所以我先刪除並創建了一個testdb的資料表

連接到MySQL語法：

連接物件 = pymysql.connect(host='伺服器位置', port=埠號, user='帳號',
                       passwd='密碼', charset='utf8', db='資料庫名稱')

要使用SQL指令需用cursor()來新增一個 cursor 物件，搭配with來使用新增、修改、刪除、提交到資料庫等動作，最後在關閉連線。

with 連接物件.cursor() as cursor:
  cursor物件.execute(sql語法字串)
  連線物件.commit()
連線物件.close()

資料型態	說明	資料型態	說明
char(n)	文字，長度固定為n，不足n則以空白填補	int(n)	整數
nchar(n)	文字，支援Unicode，長度固定為n	timestamp	時間戳記
varchar(n)	文字，長度為n，根據資料長度跟改文字長度(短文字相對於char較省空間)	float	浮點數
nvarchar(n)	文字，支援Unicode，根據資料長度更改文字長度	boolean	布林值

參考資料：
https://ithelp.ithome.com.tw/articles/10213922
https://ithelp.ithome.com.tw/articles/10203456
https://www.gotop.com.tw/

import pymysql
# 連結資料庫，相關資料一資料庫設定
# 若使用者帳號無密碼則passwd=可省略掉
# 若沒有變更port則可省略
con = pymysql.connect(host='localhost', port=3306, user='root',
                      passwd='1234567', charset='utf8', db='testdb')

with con.cursor() as cursor:
    sql = """
    CREATE TABLE IF NOT EXISTS Scores (
      ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      Name varchar(20),
      Chinese int(3),
      English int(3),
      Math int(3)
    );
    """
    # 在pythondb資料庫新建一個Scores
    # NOT NULL表示非空值，AUTO_INCREMENT為自增值，PRIMARY KEY為主鍵設定
    cursor.execute(sql)  # 執行SQL指令(預備動作)
    con.commit()  # 提交資料庫(實際執行)

這時候資料庫就會創建一個testdb的資料表

新增資料：

新增資料SQL語法：

INSERT INTO 資料表 (欄位1, 欄位2, ...) VALUES (值1, 值2, ...)

import pymysql

con = pymysql.connect(host='localhost', port=3306, user='root', passwd='1234567',charset='utf8', db='testdb')

with con.cursor() as cursor:
    #對應欄位送資料過去
    sql = '''
    INSERT INTO scores (Name, Chinese, English, Math)
                VALUES
                ('野比大雄', 65, 62, 40),
                ('源靜香', 85, 90, 87),
                ('骨川小夫', 92, 90, 95),
                ('剛田武', 67, 80, 62)
    '''
    cursor.execute(sql)   #先把上面sql資料送過去

    #如果不先cursor.execute()的話後面的sql會把前面的覆蓋掉(也可使用不同變數)
    sql = '''
    INSERT INTO scores (ID, Name, Chinese, English, Math)
                VALUES
                (NULL, '野比二雄', 65, 62, 40),
                ('', '源靜二香', 85, 90, 87),
                ('', '骨川小二夫', 92, 90, 95),
                (NULL, '剛田武二', 67, 80, 62)
    '''
    #可用空值(NULL、''等)來讓自增值(ID)自動累加上去
    cursor.execute(sql)   #再把中間的SQL語法送過去
    # 如果沒有輸入VALUES則會照順序送值進去
    sql = '''
    INSERT INTO scores VALUES
        (NULL, '野比三雄', 65, 62, 40),
        ('', '源靜三香', 85, 90, 87),
        ('', '骨川小三夫', 92, 90, 95),
        ('', '剛田武二', 67, 80, 62)
    '''
    cursor.execute(sql)
    con.commit()    # 確認更改
con.close()       # 關閉連線

資料查詢：

SELECT 欄位1, 欄位2 FROM 資料表 WHERE 條件式

查詢的時候可省略 commit() 確認修改

import pymysql
con = pymysql.connect(host='localhost', port=3306, user='root',
                      passwd='1234567', charset='utf8', db='testdb')

with con.cursor() as cursor:
    #從資料表scores找出所有資料
    sql = 'SELECT * FROM scores'
    cursor.execute(sql)         #執行SQL指令
    datas = cursor.fetchall()   #取出所有資料至datas
    print(datas)
    print('----------fetchall----------')

    sql = 'SELECT * FROM scores'
    cursor.execute(sql)         #執行SQL指令
    data = cursor.fetchone()    #取出第一筆資料至data
    print(data)
    print('----------fetchone----------')

    #從資料表中找出所有姓野比的資料
    sql = 'SELECT * FROM scores WHERE Name like "野比%"'
    cursor.execute(sql)         #執行SQL指令
    datas = cursor.fetchall()   #取出所有符合WHERE條件資料
    print(datas)
    print('----------WHERE----------')

    #從資料表中找出"姓名" "數學成績"格式的資料，並以ID作為條件尋找
    sql = 'SELECT Name, Math FROM scores WHERE Name like "野比%"'
    cursor.execute(sql)         #執行SQL指令
    data = cursor.fetchall()    #取出符合WHERE條件資料
    print(data)
    print('----------Name_Math----------')

    #資料列表，使用儲存的變數
    print('姓名','數學成績')
    for d in data:
        print(d[0], d[1])
    print('----------for_Name_Math----------')

    #資料列表，直接使用fetchall
    cursor.execute(sql)          #直接使用fetchall必須再輸入一次
    #print(cursor.fetchall())
    #print(cursor.fetchall())    #讀取一次就沒了
    print('姓名', '數學成績')
    for d in cursor.fetchall():
        print(d[0], d[1])
con.close()

更新資料：

UPDATE 資料表 set 欄位1=值1, 欄位2=值2, ... WHERE 條件式

import pymysql

con = pymysql.connect(
    host='localhost', 
    port=3306, 
    user='root', 
    passwd='1234567', 
    charset='utf8', 
    db='testdb'
)

with con.cursor() as cursor:
    sql = 'SELECT * FROM scores WHERE Name LIKE "野比%"'
    cursor.execute(sql)         #執行SQL指令
    datas = cursor.fetchall()   #獲取的資料存入變數
    print(datas)

    sql = 'UPDATE scores set English = 80, Math = 90 WHERE Name LIKE "野比%"'
    cursor.execute(sql)         #執行SQL指令
    con.commit()                #確認修改
    
    sql = 'SELECT * FROM scores WHERE Name LIKE "野比%"'
    cursor.execute(sql)         #執行SQL指令
    datas = cursor.fetchall()   #確認修改
    print(datas)

con.close()

刪除資料：

DELETE FROM 資料表 WHERE 條件式

import pymysql

con = pymysql.connect(
    host='localhost', 
    port=3306, 
    user='root',
    passwd='1234567',
    charset='utf8',
    db='testdb'
)

with con.cursor() as cursor:
    sql = 'SELECT * FROM scores'        #搜尋全部資料
    cursor.execute(sql)
    datas = cursor.fetchall()
    print(datas)
    a = input("請輸入要刪除的ID:")

    #依照ID查找資料
    sql = 'SELECT * FROM scores WHERE ID = {:s}'.format(a)
    cursor.execute(sql)
    datas = cursor.fetchall()
    print(datas)

    input("我是暫停，確認一下資料")     #無意義輸入，當暫停用

    sql = 'DELETE FROM scores WHERE ID = {:s}'.format(a)
    cursor.execute(sql)
    con.commit()

    #搜尋修改後的資料
    sql = 'SELECT * FROM scores'
    cursor.execute(sql)
    datas = cursor.fetchall()
    print(datas)
con.close()

發佈留言取消回覆