Python MySQL数据库基本操作及项目示例详解

一、数据库基础用法

要先配置环境变量，然后cmd安装：pip install pymysql

1、连接MySQL，并创建wzg库

?12345678910111213141516171819202122232425#引入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()

2、创建student表，并插入数据

?12345678910111213141516171819202122232425262728293031323334353637383940414243import 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()

数据库中char和varchar的区别：

char类型的长度是固定的，varchar的长度是可变的。

例如：存储字符串'abc'，使用char(10)，表示存储的字符将占10个字节（包括7个空字符），

使用varchar(10)，表示只占3个字节，10是最大值，当存储的字符小于10时，按照实际的长度存储。

二、项目：银行管理系统

完成功能：1.查询 2.取钱 3.存钱 4.退出

练习：创建信息表，并进行匹配

1、创建数据库为(bank)，账户信息表为(account)

account_id(varchar(20)) Account_passwd(char(6)) Money(decimal(10,2)) 001 123456 1000.00 002 456789 5000.00

2、拓展：进行账号和密码的匹配

请输入账号：001

请输入密码：123456

?12345select * from account where account_id=001 and Account_passwd=123456if cursor.fetchall():登录成功else:登录失败 ?12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152import 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()

1、进行初始化操作

?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647import 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()

2、登录检查，并选择操作

?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677import 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()

3、加入查询功能

存在银行里的钱可能会产生利息，所以需要考虑余额为小数的问题，需要用到decimal库

?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071import pymysql# 引入decimal模块import decimalDB=Noneclass 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()

4、加入取钱功能

取钱存钱要用update来执行数据库，还要注意取钱需要考虑余额是否充足的问题

?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104import pymysqlimport decimalDB=Noneclass 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()

5、加入存钱功能

存钱功能和取钱功能相似，而且不需要考虑余额的问题，至此已完善当前所有功能

?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112import pymysqlimport decimalDB=Noneclass 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()<font face="Arial, Verdana, sans-serif"><span style="white-space: normal;"> </span></font>

以上就是Python MySQL数据库基本操作及项目示例详解的详细内容，更多关于Python MySQL数据库操作的资料请关注脚本之家其它相关文章！

您可能感兴趣的文章: