一、 使用Python访问MySql数据库
1、 安装pymysql
2、 连接数据库,获取连接对象
3、 通过连接对象获取游标对象
4、 通过游标对象执行sql语句
5、 获取查询结果
6、 关闭游标

二、 连接步骤
1、 安装pymysql的数据库访问组件

2、 连接数据库获取游标对象

1获取连接对象

conn=pymysql.connect("localhost","root","java","studb")
print(type(conn))

2通过链接对象获取游标对象

cursor=conn.cursor()
print(type(cursor))

3、 执行增加修改删除操作

3执行sql

sql="INSERT INTO student(sname,sex,address,birthday,classid) VALUES('张飞','男','北京','1998-12-11',1)"

将传递的参数使用%s进行占位

sql="INSERT INTO student(sname,sex,address,birthday,classid) VALUES(%s,%s,%s,%s,%s)"

try:
cursor.execute(sql,('司马昭','男','长沙','1997-12-11','4'))
print("执行成功",cursor.rowcount)
conn.commit()#提交事务
except:
print("执行失败！")
finally:
conn.close()
Demo2.py
import pymysql#引入数据库组件

1获取连接对象

conn=pymysql.connect("localhost","root","java","studb")
print(type(conn))

2通过链接对象获取游标对象

cursor=conn.cursor()
print(type(cursor))

3执行sql

将传递的参数使用%s进行占位

sql="update student set sname=%s,sex=%s,address=%s,birthday=%s,classid=%s where sid=%s"

try:
cursor.execute(sql,('姜维','男','天水郡','1997-12-11','4','6'))
print("执行成功",cursor.rowcount)
conn.commit()#提交事务
except:
print("执行失败！")
finally:
conn.close()

Demo3.py
import pymysql#引入数据库组件

1获取连接对象

conn=pymysql.connect("localhost","root","java","studb")
print(type(conn))

2通过链接对象获取游标对象

cursor=conn.cursor()
print(type(cursor))

3执行sql

将传递的参数使用%s进行占位

sql="delete from student where sid=%s"

try:
cursor.execute(sql,('6'))
print("执行成功",cursor.rowcount)
conn.commit()#提交事务
except:
print("执行失败！")

4、 获取结果集对象

import pymysql#引入数据库组件

1获取连接对象

conn=pymysql.connect("localhost","root","java","studb")
print(type(conn))

2通过链接对象获取游标对象

cursor=conn.cursor()
print(type(cursor))

3执行sql

sql="select * from student"

try:
cursor.execute(sql)
print("执行成功",cursor.rowcount)
rst=cursor.fetchall()
print(type(rst))
#print(rst)
for row in rst:#从集合中获取记录
for i in range(0,6):#从记录中获取列值
print(row[i])#获取列值
print("=======================")
except:
print("执行失败！")

5、 将结果封装在学生的对象集合中
(1) 创建学生类student.py

class Student(object):
sid=0;
sname="无名氏"
sex="男"
address="未知"
birthday="1995-01-01"
classid="1"

def Student(self,sname,sex,address,sdate,cid):
    self.sname=sname
    self.sex=sex
    self.address=address
    self.birthday=sdate
    self.classid=cid

(2) 将结果及数据转为学生集合

import pymysql#引入数据库组件
from com.bean.student import Student #从com.bean.student文件中引入Student类

1获取连接对象

conn=pymysql.connect("localhost","root","java","studb")
print(type(conn))

2通过链接对象获取游标对象

cursor=conn.cursor()
print(type(cursor))

3执行sql

sql="select * from student"

try:
cursor.execute(sql)
print("执行成功",cursor.rowcount)
rst=cursor.fetchall()
print(type(rst))
lsst=[]#准备学生数组
#print(rst)
######封装学生数据到集合######################################
for row in rst:#从集合中获取记录
# 将表的字段值设置到学生对象中
st=Student()
st.sid=row[0]
st.sname = row[1]
st.sex = row[2]
st.address = row[3]
st.birthday = row[4]
st.classid = row[5]
#################################
lsst.append(st)#将学生对象设置到集合
##############################################################
for st in lsst:
print(st.sid,"\t",st.sname,"\t",st.sex,
"\t",st.address,"\t",st.birthday,"\t",st.classid)
print("=============================================")

print(lsst)

except:
print("执行失败！")

6、 编写数据库执行的工具类dbutil.py，处理数据库连接和各种sql语句

import pymysql # 引入数据库组件
class DbUtile(object):

#编写通用的sql的增删改方法
def executeUpdate(sql,parms):
    print(sql)
    print(parms)
    # 1获取连接对象
    conn = pymysql.connect("localhost", "root", "java", "studb")
    print(type(conn))

    # 2通过链接对象获取游标对象
    cursor = conn.cursor()
    print(type(cursor))
    try:
        if parms !=None and len(parms)>0:
            cursor.execute(sql,parms)
            conn.commit()#提交数据
            return cursor.rowcount #返回受影响的行数
        else:
            cursor.execute(sql)
            conn.commit()  # 提交数据
            return cursor.rowcount  # 返回受影响的行数
    except:

        return -1
    finally:
        conn.close()

#编写查询的方法
def excuteQuery(sql,parms):
    # 1获取连接对象
    conn = pymysql.connect("localhost", "root", "java", "studb")
    print(type(conn))

    # 2通过链接对象获取游标对象
    cursor = conn.cursor()
    print(type(cursor))
    try:
        if parms != None and len(parms) > 0:
            print("==有参数===")
            cursor.execute(sql, parms)
            rst=cursor.fetchall()

            return  rst# 返回结果集合
        else:
            print("====无参===")
            cursor.execute(sql)
            rst = cursor.fetchall()
            return rst  # 返回结果集合

    except:
        print("异常")
        return None

三、 项目实践，分层构建学生管理系统
1、 构建学生类student.py

class Student(object):
sid=0;
sname="无名氏"
sex="男"
address="未知"
birthday="1995-01-01"
classid="1"
cname="java1班"

def Student(self,sid,sname,sex,address,sdate,cid,cname):
    self.sid=sid
    self.sname=sname
    self.sex=sex
    self.address=address
    self.birthday=sdate
    self.classid=cid
    self.cname=cname

2、 构建通用数据处理类DBUtil.py

import pymysql # 引入数据库组件
class DbUtile(object):

#编写通用的sql的增删改方法
def executeUpdate(sql,parms):
    print(sql)
    print(parms)
    # 1获取连接对象
    conn = pymysql.connect("localhost", "root", "java", "studb")
    print(type(conn))

    # 2通过链接对象获取游标对象
    cursor = conn.cursor()
    print(type(cursor))
    try:
        if parms !=None and len(parms)>0:
            cursor.execute(sql,parms)
            conn.commit()#提交数据
            return cursor.rowcount #返回受影响的行数
        else:
            cursor.execute(sql)
            conn.commit()  # 提交数据
            return cursor.rowcount  # 返回受影响的行数
    except Exception as e:
        print(e)
        return -1
    finally:
        conn.close()

#编写查询的方法
def excuteQuery(sql,parms):
    # 1获取连接对象
    conn = pymysql.connect("localhost", "root", "java", "studb")
    print(type(conn))

    # 2通过链接对象获取游标对象
    cursor = conn.cursor()
    print(type(cursor))
    try:
        if parms != None and len(parms) > 0:
            print("==有参数===")
            cursor.execute(sql, parms)
            rst=cursor.fetchall()

            return  rst# 返回结果集合
        else:
            print("====无参===")
            cursor.execute(sql)
            rst = cursor.fetchall()
            return rst  # 返回结果集合

    except:
        print("异常")
        return None

3、 构建数据访问对象类studao.py,操作sql

from com.util.dbutil import DbUtile
from com.bean.student import Student
class StudentDao:
#保存的方法
def save(self,stu):
sql="INSERT INTO student(sname,sex,address,birthday,classid) VALUES(%s,%s,%s,%s,%s)";
stupair=(stu.sname,stu.sex,stu.address,stu.birthday,stu.classid)
row=DbUtile.executeUpdate(sql,stupair) #获取受到影响的行数
return row
#更新的方法
def update(self,stu):
sql="update student set sname=%s,sex=%s,address=%s,birthday=%s,classid=%s where sid=%s"
stupair = (stu.sname, stu.sex, stu.address, stu.birthday, stu.classid,stu.sid)
row = DbUtile.executeUpdate(sql, stupair)
return row

#删除的方法,根据编号删除
def delete(self,sid):
    sql = "delete from student  where sid=%s"
    stupair=(sid)
    row = DbUtile.executeUpdate(sql, stupair)
    return row

#根据编号查找学生信息
def findById(self,sid):
    sql="select s.*,c.cname from student s,clazz c where s.classid=c.cid and sid=%s";
    stupair = (sid)
    rst=DbUtile.excuteQuery(sql,stupair)
    for rs in rst:
        stu=Student()
        stu.sid=rs[0]
        stu.sname=rs[1]
        stu.sex=rs[2]
        stu.address=rs[3]
        stu.birthday=rs[4]
        stu.classid=rs[5]
        stu.cname=rs[6]
        return stu

#查找所有的学生信息
def findAll(self):
    sql="select s.*,c.cname from student s,clazz c where s.classid=c.cid and 1=1 order by sid"
    rst = DbUtile.excuteQuery(sql, None)
    lsst=[];
    for rs in rst:
        stu=Student()
        stu = Student()
        stu.sid = rs[0]
        stu.sname = rs[1]
        stu.sex = rs[2]
        stu.address = rs[3]
        stu.birthday = rs[4]
        stu.classid = rs[5]
        stu.cname = rs[6]

        lsst.append(stu)

    return lsst#返回学生集合

4、 构建业务类stubiz.py

from com.bean.student import Student
from com.dao.StudentDao import StudentDao

class StudentBiz:

def save(self,stu):
    sdao = StudentDao()
    row=sdao.save(stu)
    if row>0:
        print("保存成功！")
    else:
        print("保存失败！")

def update(self,stu):
    sdao = StudentDao()
    row = sdao.update(stu)
    if row > 0:
        print("更新成功！")
    else:
        print("更新失败！")

def delById(self,sid):
    sdao = StudentDao()
    row = sdao.delete(sid)
    if row > 0:
        print("删除成功！")
    else:
        print("删除失败！")

def findById(self,sid):
    sdao = StudentDao()
    stu=sdao.findById(sid)
    print("编号\t姓名\t性别\t地址\t生日\t班级")
    print("========================================================")
    print(stu.sid,"\t",stu.sname,"\t",stu.sex,"\t",stu.address,"\t",stu.birthday,"\t",stu.cname)
    print("========================================================")

def findAll(self):
    sdao = StudentDao()
    lsst=sdao.findAll()
    print("编号\t姓名\t性别\t地址\t生日\t班级")
    for stu in lsst:
        print(stu.sid, "\t", stu.sname, "\t", stu.sex, "\t", stu.address, "\t", stu.birthday, "\t", stu.cname)
    print("========================================================")

5、 构建交互动作类studentAction.py

from com.bean.student import Student
from com.biz.StudentBiz import StudentBiz

class StudentAction:
#定义录入学生信息的方法
def inputStuInfo(self):
stu = Student()
print("输入学生姓名")
stu.sname=input()

    print("输入学生性别")
    stu.sex = input()

    print("输入学生地址")
    stu.address = input()

    print("输入学生生日")
    stu.birthday = input()

    print("输入学生班级编号")
    stu.classid = input()

    return stu

def showMenu(self):
    stubiz=StudentBiz()
    while True:
        print("===学生信息管理===")
        print("1--添加")
        print("2--修改")
        print("3--删除")
        print("4--查找")
        print("5--显示")
        print("0--退出")
        print("===================")
        print("请输入您的选择0-5之间的数字：")
        code=input()
        op=int(code)
        if op==1:#添加
            stu=self.inputStuInfo()
            stubiz.save(stu)
        if op==2:#修改
            stu=self.inputStuInfo()
            print("请输入学生编号:")
            sid=input()
            stu.sid=sid;
            stubiz.update(stu)
        if op==3:
             print("请输入学生编号:")
             sid = input()
             stubiz.delById(sid)

        if op==4:
            print("请输入学生编号:")
            sid = input()
            stubiz.findById(sid)

        if op==5:
            stubiz.findAll()
        if op==0:
            print("系统结束，谢谢使用！")
            break;

if name == '__main__':
stuaction=StudentAction()
stuaction.showMenu()

项目的结构如下:

注意：(1)按照顺序和层次结构进行构建
(2)注意方法的编写规范