python（二）mac环境（macOS10.12.6）下的python3.6使用mysql

首先到mysql官网下载最新版mysql ：

mysql-5.7.19-macos10.12-x86_64.dmg

双击一直下一步，中间过程中记录下密码（类似这样的一串：4nf6WXPB#!nj），用于后面更改

进入系统偏好设置启动mysql

vim ～/.bash_profile加入path

source ～/.bash_profile

mysql -uroot -p测试登陆

更改密码

显示数据库

与python3交互，pip mysql

写一个封装的helper类，用于crud

TGMysqlHelper.py

# coding:utf-8import pymysqlclass TGMysqlHelper:    def __init__(self, host, port, db, user, password, charset='utf8'):        self.host = host        self.port = port        self.db = db        self.user = user        self.passwd = password        self.charset = charset    def connect(self):        self.conn = pymysql.connect(host=self.host, port=self.port, db=self.db, user=self.user, passwd=self.passwd, charset=self.charset)        self.cursor = self.conn.cursor()    def close(self):        self.cursor.close()        self.conn.close()    def get_one(self, sql, params=()):        result = None        try:            self.connect()            self.cursor.execute(sql, params)            result = self.cursor.fetchone()            self.close()        except Exception as e:            print(e.message)        return result    def get_all(self, sql, params=()):        list = ()        try:            self.connect()            self.cursor.execute(sql, params)            list = self.cursor.fetchall()            self.close()        except Exception as e:            print(e.message)        return list    def insert(self, sql, params=()):        return self.__edit(sql, params)    def update(self, sql, params=()):        return self.__edit(sql, params)    def delete(self, sql, params=()):        return self.__edit(sql, params)    def execute(self, sql, params=()):        return self.__edit(sql, params)    def __edit(self, sql, params):        count = 0        try:            self.connect()            count = self.cursor.execute(sql, params)            self.conn.commit()            self.close()        except Exception as e:            print(e.message)        return count

测试代码

# coding:utf-8from TGMysqlHelper import *def createDB():    sql = 'drop database testDB; create database testDB charset=utf8; use testDB; create table students(id int auto_increment primary key,name varchar(10) not null, gender tinyint default 0);'    mysqlHelper = TGMysqlHelper('localhost', 3306, 'sys', 'root', 'password')    count = mysqlHelper.execute(sql)    if count == 1:        print('create ok')    else:        print('create error')def add():    sql = 'insert into students(name,gender) values(%s,%s)'    sname = input("请输入用户名:")    gender = input("请输入性别(1为男，0为女):")    params = [sname, int(gender)]    helper = TGMysqlHelper('localhost', 3306, 'testDB', 'root', 'password')    count = helper.insert(sql, params)    if count == 1:        print('add ok')    else:        print('add error')def one():    sql = 'select name,gender from students order by id desc'    helper = TGMysqlHelper('localhost', 3306, 'testDB', 'root', 'password')    one = helper.get_one(sql)    print(one)def all():    sql = 'select * from students order by id desc'    helper = TGMysqlHelper('localhost', 3306, 'testDB', 'root', 'password')    all = helper.get_all(sql)    print(all)def main():    createDB()    add()    # one()    all()if __name__ == "__main__":    main()

用户登陆时的密码测试

# coding:utf-8from hashlib import sha1from TGMysqlHelper import *def createDB():    sql = 'drop database testDB; \           create database testDB charset=utf8; \           use testDB; \           create table userinfos( \           id int primary key auto_increment, \           uname varchar(20), \           upwd char(40), \           isdelete bit default 0); \           insert into userinfos values(0,"targetcloud","40bd001563085fc35165329ea1ff5c5ecbdbbeef",0);'    mysqlHelper = TGMysqlHelper('localhost', 3306, 'sys', 'root', 'password')    count = mysqlHelper.execute(sql)    if count == 1:        print('create ok')    else:        print('create error')def main():    sname = input("请输入用户名：")    spwd = input("请输入密码:")    s1 = sha1()    s1.update(spwd.encode("utf8"))    spwdSha1 = s1.hexdigest()    sql = "select upwd from userinfos where uname=%s"    params = [sname]    sqlhelper = TGMysqlHelper('localhost', 3306, 'testDB', 'root', 'password')    userinfo = sqlhelper.get_one(sql, params)    if userinfo == None:        print('用户名错误')    elif userinfo[0] == spwdSha1:        print('登录成功')    else:        print('密码错误')if __name__ == "__main__":    createDB()    main()

运行效果