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-8

import pymysql


class 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-8

from 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-8

from hashlib import sha1
from 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()

运行效果