下載pymysql：
pip install pymysql
在MySQL中創建數據庫：unicom
create database unicom DEFAULT CHARSET utf8 COLLATE utf8_general_ci;use unicom;在unicom中創建數據表：admin
create table admin(id int not null primary key auto_increment,username varchar(16) not null,password varchar(64) not null,mobile char(11) not null
);?1.插入數據
import pymysql# 1.連接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="123456", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.發送指令
cursor.execute("insert into admin(username,password,mobile) values('doufuru','DFL123456','1888888888')")
conn.commit()# 3.關閉
cursor.close()
conn.close()
記得端口號、用戶名、用戶密碼要寫對
python中運行成功后，在mysql中查詢表中的數據：插入成功
發送指令的代碼，還有下面兩種方式（最好嚴格按照下面方式之一進行拼接，即cursor.execute自帶的參數方法）：
# 2.發送指令
sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
cursor.execute(sql,["貫一", "G123456", "1766666666"])
conn.commit()# 2.發送指令
sql = "insert into admin(username,password,mobile) values( %(n1)s, %(n2)s, %(n3)s)"
cursor.execute(sql, {"n1": "蘭亭", "n2": "L123456", "n3": "1597777777"})
conn.commit()結果：
2.查詢數據
import pymysql# 1.連接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="123456", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.發送指令
cursor.execute("select * from admin where id > %s", [2, ])data_list = cursor.fetchall()
for row_dict in data_list:print(row_dict)conn.commit()# 3.關閉
cursor.close()
conn.close()有變化的是發送指令這一步
cursor.fetchall()：獲取符合條件的所有數據，得到的是[字典,字典,]形式
cursor.fetchone()：獲取符合條件的第一條數據，字典形式
查詢結果：
3.刪除數據
import pymysql# 1.連接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="123456", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.發送指令
cursor.execute("delete from admin where id=%s", [2, ])
conn.commit()# 3.關閉
cursor.close()
conn.close()
上面指令是刪除id等于2的那一行
運行之前的表數據為：
運行之后的表數據為：
4.修改數據
import pymysql# 1.連接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="123456", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.發送指令
cursor.execute("update admin set mobile=%s where id=%s", ["1788888888", 8, ])
conn.commit()# 3.關閉
cursor.close()
conn.close()
將id為8的mobile修改為1788888888
運行前后數據表：
