Python——连接数据库操作

一、数据库基础用法

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

创新互联公司长期为成百上千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为石屏企业提供专业的网站建设、做网站，石屏网站改版等技术服务。拥有十余年丰富建站经验和众多成功案例,为您定制开发。

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('','王智刚','男'),('','小明','男')
    '''
    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		1000.00
002		5000.00

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

请输入账号：001
请输入密码：
select * from account where account_id=001 and Account_passwd=
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','',1000.00),('002','',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','',1000.00),('002','',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_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库

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()

4、加入取钱功能

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

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()

5、加入存钱功能

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

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()

三、拓展-学生信息管理系统

创建student表：
('','张三','20','男','软件工程学院','2016','3'),
('','王杰','21','男','网络工程学院','2016','3'),
('','周顺','19','男','大气科学学院','2016','3'),
('','李伟','20','男','软件工程学院','2017','2'),
('','王丽','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('','张三','20','男','软件工程学院','2016','3'),
('','王杰','21','男','网络工程学院','2016','3'),
('','周顺','19','男','大气科学学院','2016','3'),
('','李伟','20','男','软件工程学院','2017','2'),
('','王丽','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 pymysql
class 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('学号已存在')
                break
        else:
            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('修改失败')
                break
        else:
            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 DB
    student = 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——连接数据库操作
本文路径：http://cqcxhl.com/article/dsojcee.html

重庆分公司，新征程启航