首先,创建mysql用户users表
create table users (
id int NOT NULL primary key auto_increment,
username varchar(16) NOT NULL,
passwd varchar(32) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
看下表结构:
mysql> desc users; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(16) | NO | | NULL | | | passwd | varchar(32) | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.02 sec)
接下来,插入测试数据,passwd字段用md5()加密保存
insert into users(username,passwd)
values
('zl', md5('zl')),
('zhangliang', md5('zhangliang')),
('root', md5('zl9053')),
('test', md5('test')),
('admin', md5('admin'));
查询一下刚刚插入的几条数据:
mysql> select * from users; +----+------------+----------------------------------+ | id | username | passwd | +----+------------+----------------------------------+ | 1 | zl | c28cbd398a61e9022fd6a6835a57dc50 | | 2 | zhangliang | 1be558d6077347baaed6d4464495b360 | | 3 | root | bad149ab36b0887d21f1af59b544abba | | 4 | test | 098f6bcd4621d373cade4e832627b4f6 | | 5 | admin | 21232f297a57a5a743894a0e4a801fc3 | +----+------------+----------------------------------+ 5 rows in set (0.00 sec)
上代码:
#!/usr/bin/env python
# -*- coding:UTF-8 -*-
# Filename: user_auth.py
# Author: zhangliang - z_liang90@126.com
# Last modified: 2014-02-27 14:52
# Description:
import hashlib
import MySQLdb as mysqldb
import datetime
from getpass import getpass
from sys import exit
_username = raw_input('Enter username:').strip()
if not _username:
print u'用户名不允许为空!'
exit()
try:
#connect db
conn = mysqldb.connect(host='localhost', user='root', passwd='123456', port=3306, charset='utf8')
#select db
conn.select_db('python')
#create a cursor instance
cur = conn.cursor()
#if user exists, reutrn 1
user_sql = "select 1 from users where username = '%s'" % _username
result = cur.execute(user_sql)
#user password auth
if result:
_password = getpass('Password:').strip()
if not _password:
print u'密码不允许为空!'
else:
#将用户输出的密码转换成md5
e = hashlib.md5()
e.update(_password)
_pwd = e.hexdigest()
pwd_sql = "select passwd from users where username = '%s'" % _username
cur.execute(pwd_sql)
ret_pwd = cur.fetchone()[0]
if ret_pwd == _pwd:
print u'%s, 认证成功! [ 登陆时间:%s ] ' % (_username, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
else:
print u'密码不正确!'
else:
print u'用户名不存在!'
#close cursor, conn
cur.close()
conn.close()
except (mysqldb.MySQLError, Exception), e:
print 'Error: %s' % e
执行下程序,看看效果:
]# python user_auth.py Enter username:root Password: root, 认证成功! [ 登陆时间:2014-02-28 14:53:14 ] ]# python user_auth.py Enter username:ad 用户名不存在! ]# python user_auth.py Enter username:admin Password: 密码不允许为空!
转载于:https://blog.51cto.com/zliang90/1364964