MySQL--python关联MySQL、练习题

╰半橙微兮° 2023-08-17 16:34 129阅读 0赞

1、python关联MySQL

pymysql：

安装：pip3 install pymysql

1.0：连接到数据库中

import pymysql
    
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='user',
        autocommit=True,
        charset='utf8'  # 编码的时候千万不要加上- 如果写成了utf-8-->就会直接报错 
    )
    cursor = conn.cursor(pymysql.cursors.DictCursor)  
    # 产生一个游标对象 以字典的形式返回查询出来的数据  
    # 键是表的字段  
    # 值是表的字段对应的值
    sql = "select * from teacher"
    
    cursor.execute(sql)  # 执行传入的sql语句
    
    print(cursor.fetchone())  # 只获取一条数据
    cursor.scroll(2, 'absolute')  # 控制光标移动  absolute相对于起始的位置往后移动几位
    cursor.scroll(1, 'relative')  # relative相对于当前位置  往后移动几位
    print(cursor.fetchall())  # 获取所有的数据  返回的结果只是一个列表

2.0： sql注入的问题

import pymysql
    
    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='user',
        autocommit=True,  # 这个参数配置后，增删改查都不许呀在手动进行添加conn.commit了
        charset='utf8'  # 编码的时候千万不要加上- 如果写成了utf-8-->就会直接报错
    )
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    user_name = input('user_name:>>').strip()
    password = input('password:>>').strip()
    sql = "select * from teacher where name=%s and password=%s"
    
    res = cursor.execute(sql, (user_name, password))  # 能够帮你自动过滤特殊符号 避免sql注入的问题
    # execute能够自动识别sql语句中的%s  然后帮你完成替换
    if res:
        print(cursor.fetchall())
    else:
        print("user-name or password error!!!")
        
        
    """
    sql注入就是利用注释等具有特殊意义的符号  来完成一些骚操作
    
    后续写的sql语句 不要手动拼接关键性的数据，而是可以选择让execute去完成
    """

增删改查：

# 增
    sql = "insert into user(username,password) values(%s,%s)"
    rows = cursor.excute(sql,('jason','123'))
    
    # 修改
    sql = "update user set username='jasonDSB' where id=1"
    rows = cursor.excute(sql)
    
    """
    增和改单单执行excute并不会真正影响到数据，需要再执行conn.commit()才可以完成真正的增改
    """
    
    # 一次插入多行记录
    res = cursor,excutemany(sql,[(),(),()]

练习题：

查询所有的课程的名称以及对应的任课老师姓名

SELECT
        course.cname,
        teacher.tname
    FROM
        course
    INNER JOIN teacher ON course.teacher_id = teacher.tid;

查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT
        student.sname,
        t1.avg_num
    FROM
        student
    INNER JOIN (
        SELECT
            student_id,
            avg(num) AS avg_num
        FROM
            score
        GROUP BY
            student_id
        HAVING
            avg(num) > 80
    ) AS t1 ON student.sid = t1.student_id;

查询没有报李平老师课的学生姓名

SELECT
        student.sname
    FROM
        student
    WHERE
        sid NOT IN (
            SELECT DISTINCT
                student_id
            FROM
                score
            WHERE
                course_id IN (
                    SELECT
                        course.cid
                    FROM
                        course
                    INNER JOIN teacher ON course.teacher_id = teacher.tid
                    WHERE
                        teacher.tname = '李平老师'
                )
        );

查询没有同时选修物理课程和体育课程的学生姓名

SELECT
        student.sname
    FROM
        student
    WHERE
        sid IN (
            SELECT
                student_id
            FROM
                score
            WHERE
                course_id IN (
                    SELECT
                        cid
                    FROM
                        course
                    WHERE
                        cname = '物理'
                    OR cname = '体育'
                )
            GROUP BY
                student_id
            HAVING
                COUNT(course_id) = 1
        );

查询挂科超过两门(包括两门)的学生姓名和班级

select student.sname,class.caption from class INNER JOIN student
        on class.cid = student.class_id
        WHERE student.sid in 
        (select student_id from score where num < 60
        GROUP BY student_id
        HAVING COUNT(course_id) >=2)
        ;

转载于:https://www.cnblogs.com/tulintao/p/11394442.html