### 1.安裝 [安裝地址](/1.kaifahuanjing/15-cun-chu-ku-de-an-zhuang/151-pymysqlde-an-zhuang.md) ### 2.連接數據庫 在連接數據庫之前，確保mysql已經打開了 ``` import pymysql # pymysql使用connect()方法連接數據庫 db = pymysql.connect(host='127.0.0.1',user='root',password='123456',port=3306) # 獲取mysql的操作游標 cursor = db.cursor() # 使用execute()方法執行sql語句 cursor.execute('select version()') # 使用fetchone()方法來獲得第一條數據 # fetchall()獲取全部數據 data = cursor.fetchone() print("database version:",data) # 創建數據庫并設置默認編碼為utf-8 cursor.execute("create database spiders DEFAULT CHARACTER set utf8") db.close() ``` ### 3.創建表結構 | 字段名 | 含義 | 類型 | | :--- | :--- | :--- | | name | 名字 | varchar | | age | 年齡 | int | ``` import pymysql db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders') cursor = db.cursor() # 判斷在數據庫是否存在students表并創建name和age兩個字段 # create table if not exists students(name varchar(255) not null,age int not null,primary key(name)) sql = 'create table if not exists students (name VARCHAR(255) not null,age int NOT NULL,PRIMARY KEY (NAME ))' cursor.execute(sql) db.close() ``` ### 4.插入數據 ``` mysql插入語句:insert into students(name,age) values('miku',18); ``` ``` import pymysql # 定義數據 name = 'miku' age = 18 db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders') cursor = db.cursor() sql = 'insert into students(name,age) VALUES (%s,%s)' try: cursor.execute(sql,(name,age)) # 提交 db.commit() except: # 回滾 db.rollback() db.close() ``` 根據字典動態構造sql語句 ``` import pymysql data = { 'name':'angle', 'age':18, } db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders') cursor = db.cursor() table = "students" keys = ','.join(data.keys()) values = ','.join(["%s"] * len(data)) sql = "insert into {table}({keys}) VALUES ({values})".format(table=table,keys=keys,values=values) try: if cursor.execute(sql,tuple(data.values())): print("成功") db.commit() except: print("失敗") db.rollback() db.close() ``` ### 5.更新數據 更新name字段值為angle的age字段，更改為235 ``` import pymysql db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders') cursor = db.cursor() sql = "update students set age = %s where name = %s" try: cursor.execute(sql,('235','angle')) db.commit() except: db.rollback() db.close() ``` 根據字典動態構造sql更新語句 ``` import pymysql data = { "name":"angle", "age":18, } table = "students" keys = ','.join(data.keys()) values = ','.join(["%s"]*len(data)) db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders') cursor = db.cursor() # insert into students(name,age) VALUES (%s,%s) ON DUPLICATE KEY UPDATE name = %s, age = %s # ON DUPLICATE KEY UPDATE:如果主鍵存在就執行更新操作 sql = "insert into {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE".format(table=table,keys=keys,values=values) update = ",".join([" {key} = %s".format(key=key) for key in data]) sql += update print(sql) try: if cursor.execute(sql,tuple(data.values())*2): print("成功") db.commit() except: print("失敗") db.rollback() db.close() ``` ### 6.刪除數據 ``` mysql語句:delete from table where 條件 ``` ``` import pymysql db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders') cursor = db.cursor() table = "students" condition = "age = 18" sql = "delete from {table} where {condition}".format(table=table,condition=condition) try: cursor.execute(sql) db.commit() except: db.rollback() db.close() ``` ### 7.查詢數據 ``` 手動添加兩條數據 mysql> insert into students values("angle",18); Query OK, 1 row affected (0.06 sec) mysql> insert into students values("miku",18); Query OK, 1 row affected (0.09 sec) ``` ``` import pymysql db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders') cursor = db.cursor() table = "students" condition = "age = 18" sql = "select * from students where age = 18" try: cursor.execute(sql) # 獲取數據的數目 print("count:",cursor.rowcount) # fetchone()方法獲取第一條數據 one = cursor.fetchone() print("one data:",one) # 調用fecthall()方法獲取所有數據 results = cursor.fetchall() print("results:",results) print("results type:",type(results)) for row in results: print(row) db.commit() except: print("error") # db.rollback() db.close() ``` 可以使用fetchone\(\)循環獲取所有數據，注意fetchone\(\)方法，獲取一條數據后，當前的第一條數據被取出來了就沒有了，數據的指針會指向下一條數據