python基于mysql的用户认证

首先，创建mysql用户users表
1
2
3
4
5
    
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;


看下表结构：
1
2
3
4
5
6
7
8
9
    
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()加密保存
1
2
3
4
5
6
7
    
insert into users(username,passwd)
values
('zl', md5('zl')),
('zhangliang', md5('zhangliang')),
('root', md5('zl9053')),
('test', md5('test')),
('admin', md5('admin'));


查询一下刚刚插入的几条数据：
1
2
3
4
5
6
7
8
9
10
11
    
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)


上代码：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
    
#!/usr/bin/env python
# -*- coding:UTF-8 -*-
# Filename: user_auth.py
# Author: zhangliang - [e-mail]
# 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


执行下程序，看看效果：
1
2
3
4
5
6
7
8
9
10
11
    
]# 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:
密码不允许为空！