大蟒蛇python教程共享Python MySQL数据库基本操作及项目示例详解

目录

• 一、数据库基础用法
• 二、项目：银行管理系统
• 1、进行初始化操作
• 2、登录检查，并选择操作
• 3、加入查询功能
• 4、加入取钱功能
• 5、加入存钱功能

  #引入decimal模块  import pymysql    #连接数据库  db=pymysql.connect(host='localhost',user='root',password='1234',charset='utf8')    #创建一个游标对象（相当于指针）  cursor=db.cursor()    #执行创建数据库语句  cursor.execute('create schema wzg default charset=utf8;')  cursor.execute('show databases;')    #fetchone获取一条数据（元组类型）  print(cursor.fetchone())  #现在指针到了[1]的位置    #fetchall获取全部数据（字符串类型）  all=cursor.fetchall()  for i in all:      print(i[0])    #关闭游标和数据库连接  cursor.close()  db.close()

  import pymysql    #连接数据库，并打开wzg数据库（数据库已创建）  db=pymysql.connect(host='localhost',user='root',password='1234',charset='utf8',db='wzg')    #创建游标对象  cursor=db.cursor()    try:      #创建student表，并执行      sql='''create table student(         sno char(10),         sname varchar(20) not null,         ssex varchar(1),         primary key(sno)         )default charset=utf8;'''      cursor.execute(sql)  	      #插入一条数据，并执行      insert_sql='''      insert into student values('200303016','王智刚','男'),('20030001','小明','男')      '''      cursor.execute(insert_sql)  	      #将数据提交给数据库（加入数据，修改数据要先提交）      db.commit()  	      #执行查询语句      cursor.execute('select * from student')  	      #打印全部数据      all=cursor.fetchall()      for i in all:          print(i)    #发生错误时，打印报错原因  except exception as e:      print(e)    #无论是否报错都执行  finally:      cursor.close()      db.close()  

  select * from account where account_id=001 and account_passwd=123456  if cursor.fetchall():  登录成功  else:  登录失败

  import pymysql    # 连接数据库  db = pymysql.connect(host='localhost', user='root', password='1234', charset='utf8')  cursor = db.cursor()    # 创建bank库  cursor.execute('create database bank charset utf8;')  cursor.execute('use bank;')    try:      # # 创建表      # sql = '''create table account(      #    account_id varchar(20) not null,      #    account_passwd char(6) not null,      #    money decimal(10,2),      #    primary key(account_id)      #    );'''      # cursor.execute(sql)        # # 插入数据      # insert_sql = '''      # insert into account values('001','123456',1000.00),('002','456789',5000.00)      # '''      # cursor.execute(insert_sql)      # db.commit()        # # 查询所有数据      # cursor.execute('select * from account')      # all = cursor.fetchall()      # for i in all:      #     print(i)        # 输入账号和密码      z=input("请输入账号：")      m=input("请输入密码：")            # 从account表中进行账号和密码的匹配      cursor.execute('select * from account where account_id=%s and account_passwd=%s',(z,m))            # 如果找到，则登录成功      if cursor.fetchall():          print('登录成功')      else:          print('登录失败')    except exception as e:      print(e)    finally:      cursor.close()      db.close()  

  import pymysql    # 创建bank库  create_schema_sql='''      create schema bank charset utf8;      '''    # 创建account表  create_table_sql = '''      create table account(      account_id varchar(20) not null,      account_passwd char(6) not null,      # decimal用于保存精确数字的类型，decimal(10,2)表示总位数最大为12位，其中整数10位，小数2位      money decimal(10,2),      primary key(account_id)      ) default charset=utf8;      '''    # 创建银行账户  create_account_sql = '''      insert into account values('001','123456',1000.00),('002','456789',5000.00);      '''        # 初始化  def init():      try:          db = pymysql.connect(host='localhost',user='root',password='1234',charset='utf8')          cursor1 = db.cursor()          cursor1.execute(create_schema_sql)          db = pymysql.connect(host='localhost',user='root',password='1234',charset='utf8',database='bank')          cursor2 = db.cursor()          cursor2.execute(create_table_sql)          cursor2.execute(create_account_sql)          db.commit()          print('初始化成功')            except exception as e:      print('初始化失败',e)    finally:      cursor1.close()      cursor2.close()      db.close()              # 不让别人调用  if __name__ == "__main__":      init()    

  import pymysql    # 定义全局变量为空  db=none    # 创建account类  class account():      # 传入参数      def __init__(self,account_id,account_passwd):          self.account_id=account_id          self.account_passwd=account_passwd                # 登录检查      def check_account(self):          cursor=db.cursor()          try:              # 把输入账号和密码进行匹配（函数体内部传入参数用self.）              sql="select * from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd)              cursor.execute(sql)                            # 匹配成功返回true，失败返回false              if cursor.fetchall():                  return true              else:                  return false            except exception as e:              print("错误原因：",e)          finally:              cursor.close()        # 查询余额      # def query_money            # 取钱      # def reduce_money            # 存钱      # def add_money        def main():      # 定义全局变量      global db            # 连接bank库      db=pymysql.connect(host="localhost",user="root",passwd="1234",database="bank")      cursor=db.cursor()            # 输入账号和密码      from_account_id=input("请输入账号：")      from_account_passwd=input("请输入密码：")            # 输入的参数传入给account类，并创建account对象      account=account(from_account_id,from_account_passwd)            # 调用check_account方法，进行登录检查      if account.check_account():          choose=input("请输入操作：n1、查询余额n2、取钱n3、存钱n4、取卡n")          # 当输入不等于4的时候执行，等于4则退出          while choose!="4":              # 查询              if choose=="1":                  print("111")              # 取钱              elif choose=="2":                  print("222")              # 存钱              elif choose=="3":                  print("333")              # 上面操作完成之后，继续输入其他操作              choose = input("请输入操作：n1、查询余额n2、取钱n3、存钱n4、取卡n")          else:              print("谢谢使用！")      else:          print("账号或密码错误")      db.close()  main()  

  import pymysql  # 引入decimal模块  import decimal  db=none  class account():        def __init__(self,account_id,account_passwd):          self.account_id=account_id          self.account_passwd=account_passwd        # 登录检查      def check_account(self):          cursor=db.cursor()          try:              sql="select * from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd)              cursor.execute(sql)              if cursor.fetchall():                  return true              else:                  return false          except exception as e:              print("错误",e)          finally:              cursor.close()        # 查询余额      def query_money(self):          cursor=db.cursor()          try:              # 匹配账号密码，并返回money              sql="select money from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd)              cursor.execute(sql)              money=cursor.fetchone()[0]              # 如果账户有钱就返回金额，没钱返回0.00              if money:                  # 返回值为decimal类型，quantize函数进行四舍五入，'0.00'表示保留两位小数                  return str(money.quantize(decimal.decimal('0.00')))              else:                  return 0.00          except exception as e:              print("错误原因",e)          finally:              cursor.close()    def main():      global db      db=pymysql.connect(host="localhost",user="root",passwd="1234",charset="utf8",database="bank")      cursor=db.cursor()      from_account_id=input("请输入账号：")      from_account_passwd=input("请输入密码：")      account=account(from_account_id,from_account_passwd)      if account.check_account():          choose=input("请输入操作：n1、查询余额n2、取钱n3、存钱n4、取卡n")          while choose!="4":              # 查询              if choose=="1":                  # 调用query_money方法                  print("您的余额是%s元" % account.query_money())              # 取钱              elif choose=="2":                  print("222")              # 存钱              elif choose=="3":                  print("333")              choose = input("请输入操作：n1、查询余额n2、取钱n3、存钱n4、取卡n")          else:              print("谢谢使用")      else:          print("账号或密码错误")      db.close()  main()  

  import pymysql  import decimal  db=none  class account():        def __init__(self,account_id,account_passwd):          self.account_id=account_id          self.account_passwd=account_passwd        # 登录检查      def check_account(self):          cursor=db.cursor()          try:              sql="select * from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd)              cursor.execute(sql)              if cursor.fetchall():                  return true              else:                  return false          except exception as e:              print("错误",e)          finally:              cursor.close()        # 查询余额      def query_money(self):          cursor=db.cursor()          try:              sql="select money from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd)              cursor.execute(sql)              money=cursor.fetchone()[0]              if money:                  return str(money.quantize(decimal.decimal('0.00')))              else:                  return 0.00          except exception as e:              print("错误原因",e)          finally:              cursor.close()        # 取钱（注意传入money参数）      def reduce_money(self,money):          cursor = db.cursor()          try:              # 先调用query_money方法，查询余额              has_money=self.query_money()                            # 所取金额小于余额则执行（注意类型转换）              if decimal.decimal(money) <= decimal.decimal(has_money):                                # 进行数据更新操作                  sql="update account set money=money-%s where account_id=%s and account_passwd=%s" %(money,self.account_id,self.account_passwd)                  cursor.execute(sql)                                    # rowcount进行行计数，行数为1则将数据提交给数据库                  if cursor.rowcount==1:                      db.commit()                      return true                  else:                      # rollback数据库回滚，行数不为1则不执行                      db.rollback()                      return false              else:                  print("余额不足")          except exception as e:              print("错误原因",e)          finally:              cursor.close()                    # 存钱      # def add_money    def main():      global db      db=pymysql.connect(host="localhost",user="root",passwd="1234",charset="utf8",database="bank")      cursor=db.cursor()      from_account_id=input("请输入账号：")      from_account_passwd=input("请输入密码：")      account=account(from_account_id,from_account_passwd)      if account.check_account():          choose=input("请输入操作：n1、查询余额n2、取钱n3、存钱n4、取卡n")          while choose!="4":              # 查询              if choose=="1":                  print("您的余额是%s元" % account.query_money())              # 取钱              elif choose=="2":                  # 先查询余额，再输入取款金额，防止取款金额大于余额                  money=input("您的余额是%s元,请输入取款金额" % account.query_money())                  # 调用reduce_money方法，money不为空则取款成功                  if account.reduce_money(money):                      print("取款成功，您的余额还有%s元" % account.query_money())                  else:                      print("取款失败！")              # 存钱              elif choose=="3":                  print("333")              choose = input("请输入操作：n1、查询余额n2、取钱n3、存钱n4、取卡n")          else:              print("谢谢使用！")      else:          print("账号或密码错误")      db.close()  main()  

  import pymysql  import decimal  db=none  class account():      def __init__(self,account_id,account_passwd):          self.account_id=account_id          self.account_passwd=account_passwd        # 登录检查      def check_account(self):          cursor=db.cursor()          try:              sql="select * from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd)              cursor.execute(sql)              if cursor.fetchall():                  return true              else:                  return false          except exception as e:              print("错误",e)          finally:              cursor.close()        # 查询余额      def query_money(self):          cursor=db.cursor()          try:              sql="select money from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd)              cursor.execute(sql)              money=cursor.fetchone()[0]              if money:                  return str(money.quantize(decimal.decimal('0.00')))              else:                  return 0.00          except exception as e:              print("错误原因",e)          finally:              cursor.close()        # 取钱      def reduce_money(self,money):          cursor = db.cursor()          try:              has_money=self.query_money()              if decimal.decimal(money) <= decimal.decimal(has_money):                  sql="update account set money=money-%s where account_id=%s and account_passwd=%s" %(money,self.account_id,self.account_passwd)                  cursor.execute(sql)                  if cursor.rowcount==1:                      db.commit()                      return true                  else:                      db.rollback()                      return false              else:                  print("余额不足")          except exception as e:              print("错误原因",e)          finally:              cursor.close()        # 存钱      def add_money(self,money):          cursor = db.cursor()          try:              sql="update account set money=money+%s where account_id=%s and account_passwd=%s" %(money,self.account_id,self.account_passwd)              cursor.execute(sql)              if cursor.rowcount==1:                  db.commit()                  return true              else:                  db.rollback()                  return false          except exception as e:              db.rollback()              print("错误原因",e)          finally:              cursor.close()    def main():      global db      db=pymysql.connect(host="localhost",user="root",passwd="1234",charset="utf8",database="bank")      cursor=db.cursor()      from_account_id=input("请输入账号：")      from_account_passwd=input("请输入密码：")      account=account(from_account_id,from_account_passwd)      if account.check_account():          choose=input("请输入操作：n1、查询余额n2、取钱n3、存钱n4、取卡n")          while choose!="4":              # 查询              if choose=="1":                  print("您的余额是%s元" % account.query_money())              # 取钱              elif choose=="2":                  money=input("您的余额是%s元,请输入取款金额" % account.query_money())                  if account.reduce_money(money):                      print("取款成功，您的余额还有%s元" % account.query_money())                  else:                      print("取款失败！")              # 存钱              elif choose=="3":                  money=input("请输入存款金额：")                  if account.add_money(money):                      print("存款成功，您的余额还有%s元，按任意键继续n" % (account.query_money()))                  else:                      print("存款失败，按任意键继续")              choose = input("请输入操作：n1、查询余额n2、取钱n3、存钱n4、取卡n")          else:              print("谢谢使用！")      else:          print("账号或密码错误")      db.close()  main()