Python操作MySQL

优先安装pymysql

Python终端输入命令 pip install pymysql

创建数据

import pymysql
## 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
## 2.发送指令
### 方式一
cursor.execute("insert into admin(username,password,mobile) values('wupeiqi','qwe123','15155555555')")
conn.commit()
### 方式二  发送指令（千万不要用字符串格式化去做SQL的拼接，安全隐患SQL注入）

sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
cursor.execute(sql, ["韩超", "qwe123", "1999999999"])

sql = "insert into admin(username,password,mobile) values( %(n1)s, %(n2)s, %(n3)s)"
cursor.execute(sql, {"n1": "集宁", "n2": "qwe123", "n3": "1999999999"})
## 3.关闭
cursor.close()
conn.close()

实现用户输入新增数据

import pymysql
while True:
	    username = input("请输入用户名：")
	    if username.upper() == "Q":
	        break
	    password = input("请输入密码：")
	    mobile =input("请输入手机号：")
	    	    # 一：创建数据
	    # 1、链接MySQL
	    conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root123',charset='utf8',db='unicom')
	    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
	
	    # 2、发送指令
	    # sql ="insert into admin(username,password,mobile) values(%s,%s,%s)"
	    # cursor.execute(sql,[username,password,mobile])
	    sql = "insert into admin(username,password,mobile) values(%(n1)s,%(n2)s,%(n3)s)"
	    cursor.execute(sql, {"n1": username,"n2": password,"n3": mobile})
	    conn.commit()
	    # 3、关闭链接
	    cursor.close()
	    conn.close()
	

查询数据

import pymysql
# 一：查询数据
# 1、链接MySQL
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root123',charset='utf8',db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2、发送指令
# sql = "select * from admin where id >%s"
# cursor.execute(sql,[6])
# 获取所有数据 fetchall,列表里面套字典  如果没有数据是个空列表[]
# data_list = cursor.fetchall()
# for row_dict in data_list:
#     print(row_dict)

sql = "select * from admin where id >%(n)s"
cursor.execute(sql,{"n":4})

# 获取一条数据 fetchone  字典  如果没有数据，返回None
res = cursor.fetchone()
# ds = res['username']
# ds = res.get('username')
print(res)
for k in res.values():
    print(k)

for k in res.keys():
    print(k)

# 关闭链接
cursor.close()
conn.close()

删除数据

import pymysql
# 1、链接MySQL
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root123',charset='utf8',db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2、发送指令

# sql = "delete from admin where id =%s"
sql = "delete from admin where id =%(n2)s"
# cursor.execute(sql,[6])
cursor.execute(sql,{"n2":7})
conn.commit()

# 3、关闭链接
cursor.close()
conn.close()

# 修改数据
```python
import pymysql
# 1、链接MySQL
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root123',charset='utf8',db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2、发送指令
sql = "UPDATE admin set password =%s where id = %s"
# sql = "UPDATE admin set password =%(n3)s where id = %(n4)s"
cursor.execute(sql,[676,13])
# cursor.execute(sql, {"n3": 5656778,"n4": 8})
conn.commit()
# 3、关闭链接
cursor.close()
conn.close()

修改数据

import pymysql
# 修改数据
# 1、链接MySQL
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='root123',charset='utf8',db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2、发送指令
sql = "UPDATE admin set password =%s where id = %s"
# sql = "UPDATE admin set password =%(n3)s where id = %(n4)s"
cursor.execute(sql,[676,13])
# cursor.execute(sql, {"n3": 5656778,"n4": 8})
conn.commit()

# 3、关闭链接
cursor.close()
conn.close()