pymysql模塊常用操作

pymysql安裝

pip install pymysql

鏈接資料庫、執行sql、關閉連接

import pymysql
user = input('請輸入用戶名請輸入密碼:').strip()
pwd= input("請輸入密碼:").strip()

# 建立連接
conn = pymysql.connect(
    host = '192.168.1.1',
    port = '3306',
    user = 'root',
    password = '123',
    db = 'myTestDB',
    charset = 'utf8',
)

# 獲取游標
cursor = conn.cursor()

# 執行sql語句
# sql = 'select * from USER_TABLE where user="%s" and pwd=%s' % (user,pwd) 自己拼接sql語句有安全風險
# rows = cursor.excute(sql)
sql = 'select * from USER_TABLE where user="%s" and pwd=%s'
rows = cursor.excute(sql,(user,pwd))

cursor.close()
conn.close()

if rows:
    print("登錄成功")
else:
    print("登錄失敗")

增刪改查操作

插入數據

import pymysql
user = input('請輸入用戶名請輸入密碼:').strip()
pwd= input("請輸入密碼:").strip()

# 建立連接
conn = pymysql.connect(
    host = '192.168.1.1',
    port = '3306',
    user = 'root',
    password = '123',
    db = 'myTestDB',
    charset = 'utf8',
)

# 獲取游標
cursor = conn.cursor()


sql = 'insert into USER_TABLE(user,pwd) values(%s,%s)'

# 插入單個數據
rows1 = cursor.excute(sql,(user,pwd))

# 插入多個數據
rows2 = cursor.excutemany(sql,[(user,pwd),('aaa','123'),('bbb','123')])
# 查看插入之前的資料庫數量
print(cursor.lastrowid)
conn.commit()
cursor.close()
conn.close()

查找數據

import pymysql
user = input('請輸入用戶名請輸入密碼:').strip()
pwd= input("請輸入密碼:").strip()

# 建立連接
conn = pymysql.connect(
    host = '192.168.1.1',
    port = '3306',
    user = 'root',
    password = '123',
    db = 'myTestDB',
    charset = 'utf8',
)

# 獲取游標
cursor = conn.cursor()

sql = 'select * from USER_TABLE;'
# 查詢
rows  = cursor.excte(sql)

# 取單個數據
single_data = cursor.fetchone()
# 取多個數據
multiple_data = cursor.fetchmany(2)
# 取出所有數據
all_data = cursor.fetchall()
# scroll 絕對位置移動
cursor.scroll(3,mode='absolute')
# scroll 相對位置移動
cursor.scroll(3,mode='relative')

conn.commit()
cursor.close()
conn.close()