Python3项目初始化6-->数据库改造
21、数据库操作修改
修改dbutils.py方法， 注意下面前4行，再下面的往右平移。
class DBConnection(object):
# @staticmethod // 静态调用
@classmethod # 定义静态方法
def execute_sql(cls, sql, args=(), fetch=True, one=False):
cnt, result = 0, None
模型修改，注释XX，引入类。
# from .dbutils import execute_sql
from .dbutils import DBConnection
如下，execute_sql统一调整为DBConnection.execute_sql方法。
def valid_login(name, password):
cnt, result = DBConnection.execute_sql(SQL_LOGIN, (name, password), one=True)
return dict(zip(SQL_USER_COLUMN, result)) if result else None
重启服务，效果一样的。
还有一种方法，使用Django自带的数据库配置信息。
settings.py设置全局信息。
# https://docs.djangoproject.com/en/2.0/ref/settings/#databases
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'db_name',
'USER': 'wang',
'PASSWORD': 'Test@123',
'HOST': '192.168.1.118',
'PORT': '3306',
'CHARSET': 'utf6mb4',
}
}
修改dbutils配置，
#encoding:utf-8
import traceback
from django.db import connection
class DBConnection(object):
# @staticmethod // 静态调用
@classmethod # 定义静态方法,下面的cls是什么东东，可以研究下
def execute_sql(cls, sql, args=(), fetch=True, one=False):
print('classmethod')
cnt, result = 0, None
conn, cur = None, None
try:
cur = connection.cursor()
cur.execute(sql, args)
if fetch:
result = cur.fetchone() if one else cur.fetchall()
else:
connection.commit()
except BaseException as e:
print(e)
print(traceback.format_exc())
finally:
if cur:
cur.close()
return cnt, result
F:\py3proj\07\cmdb>python manage.py migrate
F:\py3proj\07\cmdb>python manage.py makemigrations
F:\py3proj\07\cmdb>python manage.py runserver
重启服务，效果一样的。
定义用户类函数：models上操作
class User(object):
SQL_LOGIN = 'SELECT id,name,password,age,tel,sex FROM user2 where name=%s and password=%s LIMIT 1'
def __init__(self, id=None, name='', age=0, tel='', sex='', password=''):
self.id = id
self.name = name
self.age = age
self.tel = tel
self.sex = sex
self.password = password
@classmethod
def valid_login(cls, name, password):
args = (name, password, )
cnt, result = DBConnection.execute_sql(cls.SQL_LOGIN, args, one=True) #类里面的变量
return User(id=result[0],name=result[1], age=result[2],tel=result[3],sex=result[4]) if result else None
def as_dict(self):
return {
'id': self.id,
'name': self.name,
'age': self.age,
'tel': self.tel,
'sex': self.sex,
'password': self.password
}
视图views调整信息，
def login(request):
if 'GET' == request.method:
return render(request, 'user/login.html')
else:
name = request.POST.get('name')
password = request.POST.get('password')
user = User.valid_login(name, password)
if user:
request.session['user'] = user.as_dict()
return redirect('user:index')
else:
return render(request, 'user/login.html', {
'name': name,
'errors': {'default': '用户名或密码错误.'}
})
重启服务，一样也能实现用户登录。
下面配置用户列表展示，
继续补充model函数
@classmethod
def get_list(cls):
cnt, result = DBConnection.execute_sql(cls.SQL_LIST)
return [
# User(**dict(zip(cls.SQL_LIST_COLUMN, line)))
User(id=line[0],name=line[1],age=line[2],tel=line[3],sex=line[4])
for line in result
]
视图函数，
def index(request):
if not request.session.get('user'):
return redirect('user:login')
return render(request, 'user/index.html', {'users': User.get_list()})
访问，看到用户列表。
22、用户修改和添加删除
新增model配置，
SQL_GET_BY_ID = 'SELECT id,name,age,tel,sex FROM user2 where id=%s'
@classmethod
def get_by_id(cls, id):
cnt, result = DBConnection.execute_sql(cls.SQL_GET_BY_ID, (id,), one=True)
return User(id=result[0],name=result[1], age=result[2],tel=result[3],sex=result[4]) if result else None
视图调整，return返回改动即可。
def view(request):
if not request.session.get('user'):
return redirect('user:login')
uid = request.GET.get('uid', '')
return render(request, 'user/view.html', {'user': User.get_by_id(uid)})
以上是通过uid跳转，
下面设置保存。
调整model，
SQL_GET_BY_NAME = 'SELECT id,name,age,tel,sex FROM user2 where name=%s'
SQL_UPDATE = 'UPDATE user2 SET name=%s,age=%s,sex=%s, tel=%s where id=%s'
@classmethod
def get_by_name(cls, name):
cnt, result = DBConnection.execute_sql(cls.SQL_GET_BY_NAME, (name,), one=True)
return User(id=result[0],name=result[1], age=result[2],tel=result[3],sex=result[4]) if result else None
@classmethod
def valid_name_unique(cls, name, id=None):
user = cls.get_by_name(name)
if id is None:
return not user
else:
if user is None:
return True
else:
print(user.id)
return str(user.id) == str(id)
@classmethod
def valid_update(cls, params):
is_valid = True
user = User()
errors = {}
user.id = params.get('id', '').strip()
if get_user(user.id) is None:
errors['id'] = '用户信息不存在'
is_valid = False
user.name = params.get('name', '').strip()
if not cls.valid_name_unique(user.name, user.id):
errors['name'] = '用户名已存在'
is_valid = False
user.age = params.get('age', '0').strip()
if not user.age.isdigit():
errors['age'] = '年龄格式错误'
is_valid = False
user.tel = params.get('tel', '0')
user.sex = int(params.get('sex', '0'))
return is_valid, user, errors
def update(cls, self):
print("test..")
args = (self.name, self.age, self.sex, self.tel, self.id)
cnt, result = DBConnection.execute_sql(cls.SQL_UPDATE, args, fetch=False)
return True
设置视图部分，主要是下面几行，如有问题，使用print打印调试。
is_valid, user, errors = User.valid_update(request.POST)
print("user.id",is_valid, user.id)
if is_valid:
# update_user(user)
user.update(user)
return redirect('user:index')
删除操作，
@classmethod
def delete_by_id(cls, id):
DBConnection.execute_sql(cls.SQL_DELETE, (id,), fetch=False)
return True
视图操作，
def delete(request):
if not request.session.get('user'):
return redirect('user:login')
uid = request.GET.get('uid', '')
if uid.isdigit():
# delete_user(uid)
User.delete_by_id(uid)
return redirect('user:index')
验证用户删除ok
创建用户
model操作
@classmethod
def valid_create(cls, params):
is_valid = True
user = User
errors = {}
# users = get_users()
user.name = params.get('name', '').strip()
if user.name == "":
errors['name'] = '用户信息不存在'
is_valid = False
elif cls.get_by_name(user.name):
is_valid = False
errors['name'] = '用户名重复.'
user.age = params.get('age', '0').strip()
if not user.age.isdigit():
errors['age'] = '年龄格式错误'
is_valid = False
user.tel = params.get('tel', '0')
user.sex = int(params.get('sex', '0'))
user.password = params.get('password', '').strip()
if user.password == '' or params.get('other_password') != user.password:
is_valid = False
errors['password'] = '密码不能为空， 且两次输入密码必须相同.'
return is_valid, user, errors
def create(self):
args = (self.name, self.password, self.age, self.tel, self.sex)
cnt, result = DBConnection.execute_sql(self.SQL_CREATE, args, fetch=False)
return True
views操作
def create(request):
if not request.session.get('user'):
return redirect('user:login')
if 'GET' == request.method:
return render(request, 'user/create.html')
else:
is_valid, user, errors = User.valid_create(request.POST)
if is_valid:
user.create(user)
return redirect('user:index')
else:
return render(request, 'user/create.html', {
'user': user,
'errors': errors,
})
验证ok。
这里全局做一个备份。cmdb_08_12wuqianduan.rar
