pymysql模块应用
一、模块类型
内置模块
自定义模块
三方模块
> pip instsall 模块名称
二、虚拟环境 virtual env venv
作用: 避免开发环境混乱
特征:
1) 虚拟环境可重复使用的
2) 虚拟环境间是隔离的
1、创建虚拟环境
python -m venv 虚拟环境名称
E:\>python -m venv projectA_venv
E:\>python -m venv projectB_venv
2、进入虚拟环境
C:\Users\martin>e:
E:\>cd projectB_venv
E:\projectB_venv>cd Scripts
E:\projectB_venv\Scripts>activate
3、退出虚拟环境
(projectA_venv) E:\>cd projectA_venv
(projectA_venv) E:\projectA_venv>cd Scripts
(projectA_venv) E:\projectA_venv\Scripts>deactivate.bat
三、数据库编程
支持数据库类型:
MySQL, Oracle, PostgreSQL , SQLLITE
redis, memcached, mongodb, hbase
依赖pymysql模块
> pip install pymysql
整体流程:
1、创建数据库连接
2、基于连接创建游标 cursor
发送SQL语句,执行SQL, 接收结果
3、执行数据库操作
4、关闭游标
5、关闭数据库连接
1、使用pymysql查询数据
def selectData():
# 创建数据库连接
try:
dbconn = pymysql.connect(host="192.168.1.24", user="admin", password="redhat", database="testdb")
except Exception as e:
print("数据库连接建立失败,检测地址用户名")
print(e)
sys.exit()
# 基于连接创建游标
cr = dbconn.cursor()
test_sql = "select * from userinfo"
# 返回查询的结果数量
result = cr.execute(test_sql)
print(result)
# fetchall() 返回查询产生的所有数据
# data_01 = cr.fetchall()
# print(data_01)
#
# for user_id, user_name, user_pwd in data_01:
# print("用户编号: %s, 用户名称: %s, 用户密码: %s" % (user_id, user_name, user_pwd))
# print("--------" * 10)
# fetchmany(2) 返回结果中的前两行数据
# data_02 = cr.fetchmany(2)
# print(data_02)
# print(data_02[1][1])
# fetchone() 获取单条数据
data_03 = cr.fetchone()
print(data_03)
data_04 = cr.fetchone()
print(data_04)
cr.close()
dbconn.close()
2、添加数据
# 添加数据
def insertData():
dbconn = pymysql.connect(host="192.168.1.254", user="admin", password="redhat", database="testdb")
cr = dbconn.cursor()
insert_sql = "insert into userinfo(name, password) value('user01', '2222')"
result = cr.execute(insert_sql)
print(result)
# 提交修改
dbconn.commit()
cr.close()
dbconn.close()
3、更新数据
# 更新数据
def updateData():
dbconn = pymysql.connect(host="192.168.1.254", user="admin", password="redhat", database="testdb")
cr = dbconn.cursor()
update_sql = "update userinfo set password='www.1.com' where name='martin'"
result = cr.execute(update_sql)
print(result)
dbconn.commit()
cr.close()
dbconn.close()
4、删除数据
def removeData():
dbconn = pymysql.connect(host="192.168.1.254", user="admin", password="redhat", database="testdb")
cr = dbconn.cursor()
remove_sql = "delete from userinfo where name='user01'"
result = cr.execute(remove_sql)
print(result)
dbconn.commit()
cr.close()
dbconn.close()
示例: 用户信息管理
import pymysql
import sys
# 创建数据库连接
def dbCONN():
try:
dbconn = pymysql.connect(host="192.168.1.254", user="admin", password="redhat", database="testdb")
except Exception as e:
print("数据库连接建立失败")
print(e)
sys.exit()
return dbconn
# 用户注册功能
def userRegister(username, pwd1, pwd2):
dbconn = dbCONN()
cr = dbconn.cursor()
# 判断用户是否存在的SQL
check_user_exist_sql = "select * from userinfo where name='%s'" % username
check_user_result = cr.execute(check_user_exist_sql)
# 用户存在
if check_user_result != 0:
print("用户%s已注册!!!!" % username)
else:
# 用户不存在,判断密码是否一致
if pwd1 == pwd2:
# 注册用户
insert_user_sql = "insert into userinfo(name,password) values('%s', '%s')" % (username, pwd1)
cr.execute(insert_user_sql)
dbconn.commit()
print("用户%s注册成功!!!" % username)
else:
print("密码不一致")
cr.close()
dbconn.close()
# 用户登录功能
def userLogin(username, pwd):
dbconn = dbCONN()
cr = dbconn.cursor()
# 判断用户是否存在
check_user_exist_sql = "select * from userinfo where name='%s'" % username
# 获取对应用户密码的语句
get_password_sql = "select password from userinfo where name='%s'" % username
check_user_result = cr.execute(check_user_exist_sql)
# 获取对应用户密码
cr.execute(get_password_sql)
user_password = cr.fetchone()[0]
if check_user_result != 0 and user_password == pwd:
print("用户%s登录成功!!!" % username)
else:
print("用户名或密码错误!")
cr.close()
dbconn.close()
if __name__ == '__main__':
menu = """
用户信息管理
1. 用户注册
2. 用户登录
3. 退出
选择>>
"""
while True:
choice = input(menu).strip()
if choice == "1":
username = input("用户名: ")
pwd1 = input("密码: ")
pwd2 = input("确认密码: ")
userRegister(username=username, pwd1=pwd1, pwd2=pwd2)
elif choice == "2":
username = input("用户名:")
password = input("密码: ")
userLogin(username=username, pwd=password)
elif choice == "3":
sys.exit()
else:
print("输入有误,重新输入")