參考:Python MySQL – mysql-connector 驅動 | 菜鳥教程
創建數據庫連接
import mysql.connector
mydb = mysql.connector.connect(
host="localhost", # 數據庫主機地址
user="yourusername", # 數據庫用戶名
passwd="yourpassword" # 數據庫密碼
)
print(mydb)
創建數據庫使用 "CREATE DATABASE" 語句,以下創建一個名為 runoob_db 的數據庫:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE runoob_db")
創建數據庫前也可以使用 "SHOW DATABASES" 語句來查看數據庫是否存在:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
或者可以直接連接數據庫,如果數據庫不存在,會輸出錯誤信息:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
創建數據表使用 "CREATE TABLE" 語句,創建數據表前,需要確保數據庫已存在,以下創建一個名為 sites 的數據表:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
也可以使用 "SHOW TABLES" 語句來查看數據表是否已存在:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
print(x)
創建表的時候我們一般都會設置一個主鍵(PRIMARY KEY),我們可以使用 "INT AUTO_INCREMENT PRIMARY KEY" 語句來創建一個主鍵,主鍵起始值為 1,逐步遞增。
如果我們的表已經創建,我們需要使用 ALTER TABLE 來給表添加主鍵:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
如果你還未創建 sites 表,可以直接使用以下代碼創建。
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
插入數據使用 "INSERT INTO" 語句:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("RUNOOB", "https://www.runoob.com")
mycursor.execute(sql, val)
mydb.commit() # 數據表內容有更新,必須使用到該語句
print(mycursor.rowcount, "記錄插入成功。")
批量插入使用 executemany() 方法,該方法的第二個參數是一個元組列表,包含了我們要插入的數據:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = [
('Google', 'https://www.google.com'),
('Github', 'https://www.github.com'),
('Taobao', 'https://www.taobao.com'),
('stackoverflow', 'https://www.stackoverflow.com/')
]
mycursor.executemany(sql, val)
mydb.commit() # 數據表內容有更新,必須使用到該語句
print(mycursor.rowcount, "記錄插入成功。")
如果想在數據記錄插入後,獲取該記錄的 ID ,可以使用以下代碼:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("Zhihu", "https://www.zhihu.com")
mycursor.execute(sql, val)
mydb.commit()
print("1 條記錄已插入, ID:", mycursor.lastrowid)
查詢數據使用 SELECT 語句:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchall() # fetchall() 獲取所有記錄
for x in myresult:
print(x)
也可以讀取指定的字段數據:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT name, url FROM sites")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
如果我們只想讀取一條數據,可以使用 fetchone() 方法:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchone()
print(myresult)
如果要讀取指定條件的數據,可以使用 where 語句:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name ='RUNOOB'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
也可以使用通配符 %:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
為了防止數據庫查詢發生 SQL 注入的攻擊,可以使用 %s 占位符來轉義查詢的條件:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
database="runoob_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name = %s"
na = ("RUNOOB", )
mycursor.execute(sql, na)
myresult = mycursor.fetchall()
for x in myresult:
print(x)