2021-01-06

在这里插入代码片
import pymysql
#1.安装pymysql

#2.导入包

#3.连接对象建立<class 'pymysql.connections.Connection'>
    #con=pymysql.connect('localhost','root','123456','mysql',charset='utf8')

#4.获取游标<class 'pymysql.cursors.Cursor'>
    # con1=con.cursor()

#5.执行sql（用游标来执行）
    # sql='xxxx'
    #con1.excute(sql)
    #如果是查询，要con1.fetchxxx()接受返回的结果

#6.提交事务
    # con.commit()

#7.关闭连接
    # con.close()

# import pymysql
# con=pymysql.connect('localhost','root','123456','mysql',charset='utf8')
# # print(type(con))
# con1=con.cursor()
# print(type(con1))
# sql='show databases'
# sql='create table firest3333(id int(10),name char(10))'
# sql = "insert into  firest1111 values(22,'szjs')"
# sql="update firest1111 set id=id+1 where name='szjs'"
# sql="delete from firest1111 where name='szjs'"
# sql='select * from firest2222'
# n=con1.execute(sql)
# print(n)
# 查询数据库返回的是数据库的个数
# 创建表返回的是0，
# 插入数据，返回1；
# 修改删除查询数据，返回的是改变的行数


# 接受查询的结果是要用游标的fet函数，其他的增删改只是返回改的行数
# r=con1.fetchall()
# s=con1.fetchmany()
# print(r)#查询结果返回的是二维元组（每一行一个索引）
#
# con.commit()
# con.close()




import pymysql
#连接对象和游标
def get_con(host,dbname,user='root',pwd='123456'):
    con = pymysql.connect(host, user, pwd, dbname, charset='utf8')
    con1 = con.cursor()
    return con,con1

#sql执行
def sql_do(sql,ip='localhost',dbname='mysql'):
    # ip='127.0.0.1'
    # dbname='mysql'
    con,con1=get_con(host=ip,dbname=dbname)
    # try:
    #     n = 0
    # except:
    #     n=0
    # else:
    n=con1.execute(sql)
    con.commit()
    con.close()
    return con1,n

#增删改执行
def change_do(sql,ip='localhost',dbname='mysql'):
    con1,n=sql_do(sql,ip,dbname)
    if n:
        print('成功')
        return n
    else:
        print('失败')

#查询执行
def select_do(sql,ip='localhost',dbname='mysql'):
    con1,n=sql_do(sql,ip,dbname)
    if n:
        select_all=con1.fetchall()
        # print('查询成功')
        return select_all
    # else:
    #     print('查询失败')


# sql='create table user_atm(id int(10),username varchar(20),password int(20),money int(100))'
# sql = "insert into  firest1111 values(555,'szjs')"
# sql="update firest1111 set id=id+1 where name='szjs'"
# sql="delete from firest1111 where id='556'"
# sql='drop table user_atm'

# change_do(sql)
# sql='select * from user_atm'
# select_do(sql)

def login():
    user = input('你的用户名：')
    passwd = input('你的密码：')
    check(user, passwd)

def check(user,password):
    if (user!='' and password!=''):
        passwd=int(password)
        sql = f"select * from user_atm where username='{user}' and password={passwd}"
        a = select_do(sql)#没查询结果返回None
        global flag
        if a:
            print('登录成功！')
            index(sql,user)
        else:
            flag += 1
            print(f'登录失败,用户名或密码错误,请重新输入(你还有{5-flag}次机会)')
            if flag<=4:
                login()
            else:
                print('登录失败,退出系统')
                exit()
        return a
    else:
        print('输入格式错误')
def index(sql,user):
    print(f'--------------------------欢迎{user}进入蜗牛ATM系统------------------------')
    print('-----请输入选项：1.查询余额 2.转账 3.取款 4.存款 5.返回主菜单 6.退出--------')
    print('------------------------------------------------------------------')
    a=int(input('选项是：'))
    if a==1:#余额查询
        money=dis_money(user)
        print(f'余额：{money}元')
    elif a==2:#转账
        change_money(user)
    elif a==3:#取款
        s = int(input("取款金额："))
        out_money(user,s)

        money = dis_money(user)
        print(f'余额：{money}元')
    elif a==4:#存款
        s = int(input("存款金额："))
        add_money(user,s)

        money = dis_money(user)
        print(f'余额：{money}元')
    elif a==5:
        Index()
    elif a==6:
        exit()
    index(sql, user)
#查询余额
def dis_money(user):
    sql2 = f"select money from user_atm where username='{user}'"
    s2 = select_do(sql2)
    # print(s2[0][0])
    # print(f'余额：{s2[0][0]}')
    return s2[0][0]

#取款
def out_money(user,money):

    old_money=dis_money(user)
    if money>old_money:
        print('余额不足')
    else:
        new_s=old_money-money
        sql = f"update user_atm set money={new_s} where username='{user}'"
        change_do(sql)

#存款：
def add_money(user,money):
    # s = int(input("存款金额："))
    old_s=dis_money(user)
    new_s=old_s+money
    sql = f"update user_atm set money={new_s} where username='{user}'"
    change_do(sql)
#转账：
def change_money(user):
    new_user=input('转入的用户名：')
    money=int(input('转入的金额：'))
    sql=f"select * from user_atm where username='{new_user}'"
    a=select_do(sql)
    if a:
        sql1 = f"select money from user_atm where username='{user}'"
        money1=select_do(sql1)
        if money1[0][0]<money:
            print("余额不足")
        else:
            #转入账户变化 类似存款
            add_money(new_user, money)
            #自身账户变化 类似取款
            out_money(user, money)
            # new_s = money1[0][0] - money
            # sql = f"update user_atm set money={new_s} where username='{user}'"
            # change_do(sql)
    else:
        print('用户不存在！')
    #
    # sql = f"update user_atm set money={s} where username='{user}'"
    # change_do(sql)

#注册
def register():
    user = input('请输入用户名')
    password = input('请输入密码')
    if (user!='' and password!=''):
        password=int(password)
        password2 = int(input('请再次输入密码'))
        if password2==password:
            sql1=f"select * from user_atm where username='{user}'"
            a=select_do(sql1)
            if a:
                print('该用户已注册')
            else:
                sql=f"insert into user_atm values('','{user}',{password},5000)"
                n=change_do(sql)
                if n:
                    print('注册成功！')
                    Index()
        else:
            print('两次密码不相同')
    else:
        print('输入为空，格式错误')
#主界面
def Index():
    print('--------------------------欢迎进入蜗牛ATM系统------------------------')
    print('--------------请输入选项：1.登录 2.注册 3.退出----------------------')
    print('---------------------------------------------------------------')
    a=int(input())
    if a==1:
        login()
    elif a==2:
        register()
    elif a==3:
        exit()
    else:
        print('输入错误')

flag=1
Index()
# sql = f"select money from user_atm where username='qwe'"
# # new_s=int(s2)-s
# # sql = f"update user_atm set money={new_s} where username='{user}'"
# a=select_do(sql)
# print(a[0][0])

# sql1="select * from user_atm where username='qwe'"
# a=select_do(sql1)
# print(a)
# sql="select money from user_atm where username='qwe'"
# print(select_do(sql))