mysql在pycham连接和使用

  pycharm 其实对数据库操作的工作就两部分。 
只争朝夕，pycharm 是只管朝夕 
朝：

    from pymysql import *
    conn = connect(host='localhost',port=3306,user='root',passwd='设置的密码',db='数据库名称',charset='utf8')
    cursor = conn.cursor()

夕：

    conn.commit() （查询不需要提交）
    cursor.close()
    conn.close()

至于中间你想干啥，随你咯（滑稽）

    sqlstr = ‘你想干的事儿’
    cursor.execute(sqlstr)






封装一个类

    import pymysql
    import hashlib
    
    class dbHelper:
        def __init__
    
    (self, host, user, password, port, database):
                self.host = host
                self.user = user
                self.password = password
                self.port = port
                self.database = database
        
            def connect(self):
                self.conn = pymysql.connect(host=self.host, user=self.user, password=self.password, port=self.port,
                                            database=self.database)
        
            self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
    
        # 關閉
        def close(self):
            self.cursor.close()
            self.conn.close()
    
        # 封装增删改
        def __oper(self, sql, params):
            row = 0
            try:
                self.connect()
                row = self.cursor.execute(sql, params)
                self.conn.commit()
                self.close()
            except Exception as e:
                print(e)
    
            return row
    
        def insert(self, sql, params=[]):
            return self.__oper(sql, params)
    
        def delete(self, sql, params=[]):
            return self.__oper(sql, params)
    
        def update(self, sql, params=[]):
            return self.__oper(sql, params)
    
        def find_one(self, sql, params=[]):
            data = None
            try:
                self.connect()
                self.cursor.execute(sql, params)
                data = self.cursor.fetchone()
                self.close()
            except Exception as e:
                print(e)
    
            return data
    
        def find_all(self, sql, params=[]):
            datas = None
            try:
                self.connect()
                self.cursor.execute(sql, params)
                datas = self.cursor.fetchall()
                self.close()
            except Exception as e:
                print(e)
    
            return datas
        def secret_sha256(self,ww):
            x =hashlib.sha256(ww.encode('utf-8'))
            return x.hexdigest()


调用

    from sql_fengzhaung import dbHelper
    from datetime import date
    from datetime import datetime
    
    
    # 创建数据库工具类对象
    db = dbHelper(host='10.0.102.21', user='root', password='123456', port=3306, database='root1')
    class AA:
        #登录
        def denglu(self):
            username = input('用户名:')
            password = input('密码:')
            if username and password:
                # 数据库查询
                sql = 'select * from login where username=%s'
                data = db.find_one(sql, (username,))  # {'id':1,'username':'admin','password':'....',...}
                if data:
                    # 此人注册过
                    db_password = data.get('password')
                    password = db.secret_sha256(password)
                    if password == db_password:
                        sql = 'update login set islogin=1 where id=%s'
                        db.update(sql, (data.get('id')))
                        print(data.get('id'))
                        print('用户登录成功！')
                    else:
                        print('用户名或者密码有误！')
                else:
                    print('输入的用户名有误！')
        #注册
        def zhuce(self):
            username = input('用户名:')
            password = input('密码:')
            repassword = input('确认密码:')
            phone = input('手机号码:')
            email = input('邮箱:')
            # 查询
            if username:
                sql = 'select * from login where username=%s or phone=%s or email=%s'
                data = db.find_one(sql, params=(username, phone, email))
                if data:
                    print('此用户名或者手机号码或者邮箱已被占用！')
                else:
                    # 注册
                    if password and repassword:
                        if password == repassword:
                            password = db.secret_sha256(password)
                            sql = 'insert into login(username,password,phone,email,rdate) values(%s,%s,%s,%s,%s)'
                            row = db.insert(sql, (username, password, phone, email, date.today()))
                            if row:
                                print('注册成功！')
                            else:
                                print('注册失败！')
                        else:
                            print('两次密码不一致！')
                    else:
                        print('密码不能为空～')
            else:
                print('用户名不能为空！')
    
        #发表文章
        def fabiao(self):
            username = input('用户名:')
            if username:
                # 数据库查询
                sql = 'select * from login where username=%s'
                data = db.find_one(sql, (username,))  # {'id':1,'username':'admin','password':'....',...}
                if data:
                    islogin = data.get('islogin')
                    if islogin:
                        # 发表文章
                        title = input('请输入文章标题:')
                        content = input('输入内容:')
    
                        # 构建sql
                        sql = 'insert into blog(title,content,pdate,uid) values(%s,%s,%s,%s)'
                        row = db.insert(sql, (title, content, datetime.now(), data.get('id')))
                        if row:
                            print(username + '发表文章成功')
                        else:
                            print('文章发表失败！')
    
                    else:
                        print('此用户还没有登陆，请登陆....')
                else:
                    print('用户名有误！')
    
        #退出
        def tuichu(self):
            username = input('用户名:')
            if username:
                # 数据库查询
                sql = 'select * from login where username=%s'
                data = db.find_one(sql, (username,))  # {'id':1,'username':'admin','password':'....',...}
                if data:
                    sql = 'update login set islogin=0 where id=%s'
                    row = db.update(sql, (data.get('id'),))
                    if row:
                        print(username + '成功退出！')
                    else:
                        print('退出失败！')
                else:
                    print('输入的用户名有误！')
            else:
                print('用户名不能为空！')
    
        #查询文章
        def liulan(self):
            pass
    
    
    #入口
    def main():
        a = AA()
        while True:
            choice = int(input('请选择:1.登陆 2.注册 3.发表博客 4.退出登陆 5.浏览文章 6. 查看用户文章 7.忘记密码 '))
            if choice==1:
                a.denglu()
            if choice ==2:
                a.zhuce()
            if choice==3:
                a.fabiao()
            if choice==4:
                a.tuichu()
                break
            if choice==5:
                a.liulan()
    if __name__ == '__main__':
        main()