python和java哪个更值得学 Python——连接数据库操作

一、数据库基础用法要先配置环境变量，然后cmd安装：pip install pymysql
1、连接MySQL，并创建wzg库#引入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表，并插入数据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()数据库中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))0011234561000.000024567895000.002、拓展：进行账号和密码的匹配
请输入账号：001
请输入密码：123456
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()1、进行初始化操作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()2、登录检查，并选择操作import pymysql# 定义全局变量为空DB=None# 创建Account类class Account():# 传入参数def __init__(self,account_id,account_passwd):self.account_id=account_idself.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，失败返回Falseif cursor.fetchall():return Trueelse:return Falseexcept Exception as e:print("错误原因：",e)finally:cursor.close()# 查询余额# def query_money# 取钱# def reduce_money# 存钱# def add_moneydef 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库
import pymysql# 引入decimal模块import decimalDB=Noneclass Account():def __init__(self,account_id,account_passwd):self.account_id=account_idself.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 Trueelse:return Falseexcept Exception as e:print("错误",e)finally:cursor.close()# 查询余额def query_money(self):cursor=DB.cursor()try:# 匹配账号密码，并返回moneySQL="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.00if money:# 返回值为decimal类型，quantize函数进行四舍五入，'0.00'表示保留两位小数return str(money.quantize(decimal.Decimal('0.00')))else:return 0.00except Exception as e:print("错误原因",e)finally:cursor.close()def main():global DBDB=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来执行数据库，还要注意取钱需要考虑余额是否充足的问题
import pymysqlimport decimalDB=Noneclass Account():def __init__(self,account_id,account_passwd):self.account_id=account_idself.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 Trueelse:return Falseexcept 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.00except 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 Trueelse:# rollback数据库回滚，行数不为1则不执行DB.rollback()return Falseelse:print("余额不足")except Exception as e:print("错误原因",e)finally:cursor.close()# 存钱# def add_moneydef main():global DBDB=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、加入存钱功能存钱功能和取钱功能相似，而且不需要考虑余额的问题，至此已完善当前所有功能
import pymysqlimport decimalDB=Noneclass Account():def __init__(self,account_id,account_passwd):self.account_id=account_idself.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 Trueelse:return Falseexcept 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.00except 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 Trueelse:DB.rollback()return Falseelse: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 Trueelse:DB.rollback()return Falseexcept Exception as e:DB.rollback()print("错误原因",e)finally:cursor.close()def main():global DBDB=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()三、拓展-学生信息管理系统创建student表：
('2016081111','张三','20','男','软件工程学院','2016','3'),
('2016061111','王杰','21','男','网络工程学院','2016','3'),
('2016071113','周顺','19','男','大气科学学院','2016','3'),
('2017081180','李伟','20','男','软件工程学院','2017','2'),
('2016081201','王丽','20','女','软件工程学院','2016','5')
实现功能：
1、查询所有学生的信息
2、按学号查询学生的信息
3、添加学生信息
4、修改学生信息
5、删除学生信息
6、退出
1、初始化import pymysql# 创建wzg库CREATE_SCHEMA_SQL = '''create schema wzg charset utf8;'''# 创建student表CREATE_TABLE_SQL = '''create table student(sno varchar(10) primary key,sname varchar(10),sage varchar(3),ssex enum('男', '女'),sadcademy varchar(20),sgrade varchar(4),sclass varchar(2))default charset=utf8'''# 插入数据CREATE_student_SQL = '''insert into student values('2016081111','张三','20','男','软件工程学院','2016','3'),('2016061111','王杰','21','男','网络工程学院','2016','3'),('2016071113','周顺','19','男','大气科学学院','2016','3'),('2017081180','李伟','20','男','软件工程学院','2017','2'),('2016081201','王丽','20','女','软件工程学院','2016','5')'''# 初始化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='wzg')cursor2 = DB.cursor()cursor2.execute(CREATE_TABLE_SQL)cursor2.execute(CREATE_student_SQL)DB.commit()print('初始化成功')except Exception as e:print('初始化失败', e)finally:cursor1.close()cursor2.close()DB.close()# 不让别人调用if __name__ == "__main__":init()2、主函数import pymysqlclass Student():#查询所有学生信息def all_student(self):cursor = DB.cursor()cursor.execute('select * from student;')for i in cursor.fetchall():print(i)#按学号查询学生信息def sno_student(self):cursor = DB.cursor()sno=input("请输入学号:")cursor.execute('select * from student where sno=%s;' % sno)a=cursor.fetchone()if a:print(a)else:print('该学号不存在')#添加学生信息def add_student(self):cursor = DB.cursor()a1 = input("请输入学号:")a2 = input("请输入姓名:")a3 = input("请输入年龄:")a4 = input("请输入性别:")a5 = input("请输入院系:")a6 = input("请输入:入学年份")a7 = input("请输入班级:")cursor.execute('select * from student;')for i in cursor.fetchall():if a1==i[0]:print('学号已存在')breakelse:sql='insert into student values("%s","%s","%s","%s","%s","%s","%s");' % (a1,a2,a3,a4,a5,a6,a7)cursor.execute(sql)DB.commit()#提交数据print('添加成功')#修改学生信息def set_student(self):cursor = DB.cursor()sno=input('请输入要修改的学号：')cursor.execute('select * from student;')for i in cursor.fetchall():if sno == i[0]:a = input('输入要修改的字段:\nsno,sname,sage,ssex,sadcademy,sgrade,sclass\n')b = input('修改为：')SQL = 'update student set %s="%s" where sno="%s";' % (a,b,sno)cursor.execute(SQL)if cursor.rowcount == 1:#行计数DB.commit()print('修改成功')else:DB.rollback()#数据库回滚print('修改失败')breakelse:print('学号不存在')#删除学生信息def del_student(self):cursor = DB.cursor()sno = input("请输入学号:")cursor.execute('delete FROM student WHERE sno=%s;' % sno)if cursor.rowcount==1:DB.cursor()print('删除成功')else:DB.rollback()print('该学号不存在')def main():global DBstudent = Student()DB = pymysql.connect(host="localhost", user="root", password="1234", database="wzg")cursor = DB.cursor()choose = input("*************\n请选择您的操作：\n1、查询所有学生的信息\n2、按学号查询学生的信息\n3、添加学生信息\n4、修改学生信息\n5、删除学生信息\n6、退出\n")while choose != "6":if choose=="1":student.all_student()elif choose=="2":student.sno_student()elif choose == "3":student.add_student()elif choose == "4":student.set_student()elif choose == "5":student.del_student()choose = input("*************\n请选择您的操作：\n1、查询所有学生的信息\n2、按学号查询学生的信息\n3、添加学生信息\n4、修改学生信息\n5、删除学生信息\n6、退出\n")else:print('已退出！')DB.close()main()【python和java哪个更值得学 Python——连接数据库操作】声明：未经许可，不得转载