python连接mysql，实战。实现ATM类似功能。

数据库，表建立。

create database `mydata`;
DROP TABLE IF EXISTS `mytab`;
CREATE TABLE `mytab` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(50) NOT NULL,
  `role` varchar(20) DEFAULT NULL,
  `money` int(11) DEFAULT NULL,
  `tel` int(11) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of mytab
-- ----------------------------
INSERT INTO `mytab` VALUES ('1', 'lm01', 'LiuM123', 'boss', null, null);
INSERT INTO `mytab` VALUES ('2', 'lm02', 'LiuM123', 'clerk', null, null);
INSERT INTO `mytab` VALUES ('3', 'lm03', 'LiuM123', 'admin', '134', null);
INSERT INTO `mytab` VALUES ('4', 'lm04', 'LiuM123', 'admin', '500', null);
INSERT INTO `mytab` VALUES ('5', 'lm05', 'LiuM123', 'admin', '3456', null);
INSERT INTO `mytab` VALUES ('6', 'lm06', 'LiuM123', 'admin', '231', null);
INSERT INTO `mytab` VALUES ('7', 'lm07', 'LiuM125', 'boss', '87', null);
INSERT INTO `mytab` VALUES ('8', 'lm08', 'LiuM121', 'admin', null, null);
INSERT INTO `mytab` VALUES ('9', 'lm09', 'LiuM123', 'admin', '741', null);
INSERT INTO `mytab` VALUES ('10', 'lm010', 'LiuM123', 'boss', '-333', null);

python连接数据库并实现ATM。


def mys(sql02,sql01="mydata",host="localhost",name="root",pwd="Wnxy100"): #mysql封装函数
    import pymysql
    data=None
    sql01="use "+sql01
    con=pymysql.connect(host,name,pwd) #1、连接数据库
    cur=con.cursor() #2、创建游标
    cur.execute(sql01) #选择库
    cur.execute(sql02) #3、执行SQL语句
    if sql02[:6]=="select" or sql02[:6]=="SELECT":
        data=cur.fetchall() #获取查询的存储信息
         # data=cur.fetchmany(3) #获取查询的存储信息,前三条。
    con.commit() #4、提交事务
    #5、关闭游标，连接
    cur.close()
    con.close()
    return data


def login(): #登录
    con,count=0,0
    while True:    
        if count>=3: #开关 密码
            print("密码错误输入次数超过三次！锁定。请联系客服。")
            break
        if con>=3: #开关 用户名
            print("用户名异常！锁定。请联系客服。")
            break
        use=input('请输入用户名：')
        sql="select password,userid from mytab where username='{0}'".format(use) #sql查询语句
        users=mys(sql) #调用mys函数（sql封装）
        if len(users)!=0: #判断用户名是否存在
            while count<3:
                pwd = input('请输入密码：')
                if users[0][0]==pwd: #判断密码是否正确
                    print('登录成功')
                    return use #返回用户名
                else:
                    count+=1 #统计错误次数
                    print('密码错误')
        else:
            print('用户名不存在')
            # reg()
            # break
            con+=1


def reg(): #注册
    while True:
        use=input('请输入注册的用户名(输入q,退出注册.)：').strip()
        if use=="q":
            break
        sql11="select username,password from mytab" #sql查询语句
        users=dict(mys(sql11)) #调用mys函数（sql封装）转字典
        if use not in users: #判断用户名是否存在
            pwd = input('请输入密码：')
            sql22="insert into mytab(username,password) value('{0}','{1}')".format(use,pwd) #sql插入语句
            mys(sql22)
            print("恭喜你！注册成功。")
            break
        else:
            print("您注册的用户名已存在，请重新注册。")

def save(): #存钱
    rem=query() #查询剩余的钱
    inp=int(input("请输入存入的金额："))
    res=rem[0] #存入前的金额
    ress=res+inp #存入后的金额
    sql="update mytab set money={0} where username='{1}'".format(ress,rem[1]) #更改数据库值
    mys(sql)
    print("已经存入{0}元金额，剩余{1}元金额。".format(inp,ress))

def fetch(): #取钱
    rem=query() #查询剩余的钱
    inp=int(input("请输入取出的金额："))
    res=rem[0] #取出前的金额
    if res>=inp:
        ress=res-inp #取出后的金额
        sql="update mytab set money={0} where username='{1}'".format(ress,rem[1]) #更改数据库值
        mys(sql)
        print("已经取出{0}元金额，剩余{1}元金额。".format(inp,ress))
    else:
        print("你无存款，并且负债{0}，已经进入我行征信黑名单，请尽快还款！".format(res))
    
def query(): #查询
    use=login()
    sql="select money from mytab where username='{0}'".format(use)
    rem=mys(sql)
    print("余额为{0}元金额".format(rem[0][0])) #剩余金额rem
    return rem[0][0],use

#---------------------------------------------函数体---分割线--------------------------

print("*"*52)
print("***************** 欢迎来到蜗牛ATM ******************")
print("*"*52)
print("0.退卡 1.登录 2.注册 3.取款 4.存钱 5.挂失 6.个人信息")

while True:
    inp=input("请输入对应数字：\n")
    if int(inp)==0:
        print("您已退出，欢迎再次光临！")
        break
    elif int(inp)==1:
        login() #登录
        break
    elif int(inp)==2:
        reg() #注册
        break
    elif int(inp)==3:
        fetch() #取款
        break
    elif int(inp)==4:
        save() #存款
        break
    elif int(inp)==5:
        print("挂失")
        break
    elif int(inp)==6:
        mag=query() #查询
        print("当前登录用户为：%s"%mag[1])
        break
    else:
        print("输入错误，请重新输入！")

#--------------数据库多条数据插入-----------------------------------

user=[
{"userid":1,"username":"lm01","password":"LiuM123","role":"boss"},
{"userid":2,"username":"lm02","password":"LiuM123","role":"clerk"},
{"userid":3,"username":"lm03","password":"LiuM123","role":"admin"},
{"userid":4,"username":"lm04","password":"LiuM123","role":"admin"},
{"userid":5,"username":"lm05","password":"LiuM123","role":"admin"},
{"userid":6,"username":"lm06","password":"LiuM123","role":"admin"},
{"userid":7,"username":"lm07","password":"LiuM123","role":"boss"},
{"userid":8,"username":"lm08","password":"LiuM123","role":"admin"},
{"userid":9,"username":"lm09","password":"LiuM123","role":"admin"},
{"userid":10,"username":"lm010","password":"LiuM123","role":"boss"},
]

for i in user:
    # q=i['userid']
    # w=i['username']
    # e=i['password']
    # r=i['role']
    a="insert into mytab(userid,username,password,role) value('{0}','{1}','{2}','{3}')".format(i['userid'],i['username'],i['password'],i['role'])
    #a="insert into mytab(userid,username,password,role) value('%s','%s','%s','%s')"%(q,w,e,r)
    mys(a) #封装的mysql函数