10 database programming exercises

First, create a database table  

  • Programming requirements

         Under the name   nudt   Create Course and Teacher tables under the database of. The table structure is as follows:

Course

Field nameField typeexplain
CnoCHAR(10)Course number (primary key)
CnameCHAR(100)Course name
TnoCHAR(10)Teacher number

Teacher form

Field nameField typeexplain
TnoCHAR(10)Teacher number (primary key)
TnameCHAR(100)Teacher name
TsexCHAR(10)Teacher gender

The mysql connection parameters are as follows:

host='localhost',port=3306,user='root',passwd='123123'

  • code implementation
import pymysql

if __name__ == '__main__':
    # **********begin********** #
    conn = pymysql.connect(host='localhost',port=3306,
                        user='root',passwd='123123',
                        charset='utf8',db='nudt')
    cursor = conn.cursor();
    sql = """
    CREATE TABLE Course(
    Cno CHAR(10) PRIMARY KEY,
    Cname CHAR(100),
    Tno CHAR(10)
    )ENGINE=innodb DEFAULT CHARSET=utf8;
    CREATE TABLE Teacher(
    Tno CHAR(10) PRIMARY KEY,
    Tname CHAR(100),
    Tsex CHAR(10)
    )ENGINE=innodb DEFAULT CHARSET=utf8;
    """
    cursor.execute(sql)
    cursor.close()
    conn.close()
    # **********end********** #
   

Second pass   Data update

  • Programming requirements

Insert Teacher data into Teacher table (Teacher)

Teacher numberTeacher nameTeacher gender
T1Zhou Haifangfemale
T2Jing Wen Zhoumale
T3Tan Chunjiaofemale
T4Chen Liqianmale
T5Li Huangmale
T6Mao Xiaoguangmale

Insert Course data into the Course

Course numberCourse nameTeacher number
C1Fundamentals of college computerT1
C2Fundamentals of college computerT2
C3Database principle and technologyT2
C4Fundamentals of college computerT5
C5ProgrammingT1
C6ProgrammingT4
C7digital image processingT1
C8Abstract algebraT6
C9discrete mathematics T5
C10software reliabilityT6

The database connection parameters are as follows:

host='localhost', port=3306,user='root', passwd='123123', db="nudt",charset='utf8'

  • code implementation
import pymysql

if __name__ == '__main__':
    # **********begin********** #
    conn = pymysql.connect(
        host = 'localhost',
        port = 3306,
        user = 'root',
        password = '123123',
        database = 'nudt',
        charset = 'utf8'
    )
    cursor = conn.cursor()
    sql = 'insert into Teacher(Tno,Tname,Tsex) values(%s,%s,%s);'
    data = [
        ('T1','Zhou Haifang','female'),
        ('T2','Jing Wen Zhou','male'),
        ('T3','Tan Chunjiao','female'),
        ('T4','Chen Liqian','male'),
        ('T5','Li Huang','male'),
        ('T6','Mao Xiaoguang','male')
    ]
    cursor.executemany(sql,data)
    conn.commit()
    sql = 'insert into Course(Cno,Cname,Tno) values(%s,%s,%s);'
    data = [
        ('C1','Fundamentals of college computer','T1'),
        ('C2','Fundamentals of college computer','T2'),
        ('C3','Database principle and technology','T2'),
        ('C4','Fundamentals of college computer','T5'),
        ('C5','Programming','T1'),
        ('C6','Programming','T4'),
        ('C7','digital image processing','T1'),
        ('C8','Abstract algebra','T6'),
        ('C9','discrete mathematics ','T5'),
        ('C10','software reliability','T6')
    ]
    cursor.executemany(sql,data)
    conn.commit()
    cursor.close()
    conn.close()
    # **********end********** #

Chapter 3 customs clearance table query

  • Programming requirements

        1. Table used for query: Teacher;

        2. Query the number of male teachers and assign the value to the variable maleNum;

        3. Query the number of female teachers and assign the value to the variable femaleNum;

        4. Output the variable maleNum and the variable femaleNum to the console in the format of -- > variable name: numeric value (use comma to splice instead of plus sign)

  • code implementation
import pymysql
import matplotlib

matplotlib.use('Agg')
import matplotlib.pyplot as plt
from pylab import mpl

mpl.rcParams['font.sans-serif'] = ['SimHei']

if __name__ == '__main__':
    # **********begin********** #
    # Get connection object
    conn = pymysql.connect(host = 'localhost',port = 3306,
                        user = 'root',passwd = '123123',
                        db='nudt',charset='utf8')
    # Get cursor
    cs = conn.cursor()
    # Query the number of male teachers and assign the value to the variable maleNum
    cs.execute("select* from Teacher where Tsex='male'")
    maleNum = len(cs.fetchall())
    # Query the number of female teachers and assign the value to the variable femaleNum
    cs.execute("select* from Teacher where Tsex='female'")
    femaleNum = len(cs.fetchall())
    # Output the variable maleNum and the variable femaleNum to the console in the format of -- > variable name: numeric value
    # For example: maleNum:100
    # Note: to output one variable per line, please output the variable maleNum first, followed by the variable femaleNum
    print("maleNum: %d" % maleNum)
    print("femaleNum: %d" % femaleNum)
    cs.close()
    conn.close()
    # Draw histogram
    plt.xticks(fontsize=12)
    plt.yticks([])  # Do not display y-axis scale
    plt.text(0, maleNum * 0.5, maleNum, ha='center', fontsize=30)
    plt.text(1, femaleNum * 0.5, femaleNum, ha='center', fontsize=30)
    plt.bar(range(2), (maleNum, femaleNum), color='gy', tick_label=('male', 'female'))
    plt.savefig("img/1.png")
    # close resource
    # **********end********** #

Fourth, multi table query

  • Programming requirements
  • Count the number of teachers' courses and reverse the order of teachers' names.

  • Obtain the result set, assign it to the variable results, traverse the result set, and output it to the console according to the format -- > teacher: number of courses.

The connection parameters are as follows:

  1. host='localhost', port=3306,
  2. user='root', passwd='123123',
  3. charset='utf8', db="nudt"

Course

Field nameField typeexplain
CnoCHAR(10)Course number (primary key)
CnameCHAR(100)Course name
TnoCHAR(10)Teacher number

The data in the table are as follows:

Teacher form

Field nameField typeexplain
TnoCHAR(10)Teacher number (primary key)
TnameCHAR(100)Teacher name
TsexCHAR(10)Teacher gender

The data in the table are as follows:

  • code implementation
import pymysql
import matplotlib

matplotlib.use('Agg')
import matplotlib.pyplot as plt
from pylab import mpl

mpl.rcParams['font.sans-serif'] = ['SimHei']

if __name__ == '__main__':
    # **********begin***********#

    # Get connection object
    conn = pymysql.connect(host = 'localhost',port = 3306,
                        user = 'root',passwd = '123123',
                        db='nudt',charset='utf8')

    # Get cursor
    cs = conn.cursor()

    # Execute SQL to count the number of teachers' courses and reverse the order according to the teachers' names
    sql = "select Tname,count(Cno) \
            from Teacher left outer join Course \
            on Teacher.Tno = Course.Tno \
            group by Tname \
            order by Tname desc"

    cs.execute(sql)

    # Get the result set and assign it to the variable results
    results = cs.fetchall()

    # Traverse the result set and output it to the console according to the format -- > teacher: number of courses
    for result in results:
        print("%s : %d" % (result[0],result[1]))
        
    # Draw histogram
    names, courseNum = zip(*results)
    plt.xticks(fontsize=16)
    plt.yticks([])  # Do not display y-axis scale
    for i in range(len(courseNum)):
        plt.text(i, courseNum[i] * 0.5, courseNum[i], ha='center', fontsize=20)
    plt.bar(range(len(courseNum)), courseNum, color='y', tick_label=names)
    plt.savefig("step4_img/1.png")

    # close resource
    cs.close()
    conn.close()

    # **********end**********#

Chapter V Information Management System

  • Programming requirements

         1. Complete the addCourse(cs) function to complete the function of adding course information.

        2. Complete the updateCourse(cs) function to complete the function of modifying course information.

        3. Complete the findCourseByCno(cs) function to complete the function of querying course information.

        4. Complete the deleteCourse(cs) function to complete the function of deleting course information.

        5. Complete the findCourseByTeacherName(cs) function to complete the function of querying the course name through the teacher name.

        6. Complete the selectTeacherByCname(cs) function to query the teacher name through the course name.

Course

Field nameField typeexplain
CnoCHAR(10)Course number (primary key)
CnameCHAR(100)Course name
TnoCHAR(10)Teacher number

The data in the table are as follows:

Teacher form

Field nameField typeexplain
TnoCHAR(10)Teacher number (primary key)
TnameCHAR(100)Teacher name
TsexCHAR(10)Teacher gender

The data in the table are as follows:

  • code implementation
import pymysql


# Add course information, and enter the format of course information as: Cno,Cname,Tno
def addCourse(cs):
    courseInfo = input()
    # **********begin********** #
    course = courseInfo.strip().split(",")
    sql="INSERT INTO Course(Cno,Cname,Tno) values(%s,%s,%s)"
    Cno = course[0]
    Cname = course[1]
    Tno = course[2]
    cs.execute(sql,[Cno,Cname,Tno])
    # **********end********** #


# Modify the course information (modify the course name through the course number), and enter the new course information in the format of Cno,Cname
def updateCourse(cs):
    courseInfo = input()
    # **********begin********** #
    course = courseInfo.strip().split(",")
    sql="UPDATE Course SET Cname=%s WHERE Cno=%s"
    Cno = course[0]
    Cname = course[1]
    cs.execute(sql,[Cname,Cno])
    # **********end********** #


# Query the course information (query the course information through the course number), and enter the course number Cno
# Print course information to the console
def findCourseByCno(cs):
    courseId = input()
    # **********begin********** #
    course = courseId.strip()
    sql="SELECT* FROM Course WHERE Cno=%s"
    cs.execute(sql,course)
    print(cs.fetchall())
    # **********end********** #


# Delete the course information (delete the course information through the course number), and enter the course number Cno
def deleteCourse(cs):
    courseId = input()
    # **********begin********** #
    course = courseId.strip()
    sql="DELETE FROM Course WHERE Cno=%s"
    cs.execute(sql,course) 
    # **********end********** #


# Query the course name through the teacher name, print it to the console, and enter the teacher name Tname
# The printing format is: course name (one line for each course name, excluding other characters)
def findCourseByTeacherName(cs):
    tname = input()
    # **********begin********** #
    tname = tname.strip()
    sql="""SELECT Cname FROM Teacher,Course 
        WHERE Teacher.Tno=Course.Tno
        AND Tname = %s"""
    cs.execute(sql,tname)
    for name in cs.fetchall():
        print(name[0])
    # **********end********** #


# Query the teacher name through the course name and print it to the console to output the course name Cname
# The printing format is: teacher name (one line for each teacher name, excluding other characters)
def selectTeacherByCname(cs):
    cname = input()
    # **********begin********** #
    cname = cname.strip()
    sql="""SELECT Tname FROM Course,Teacher
            WHERE Course.Tno = Teacher.Tno
                AND Cname = %s"""
    cs.execute(sql,cname)
    for name in cs.fetchall():
        print(name[0])
    # **********end********** #

def Test(cs):
    sql = "select * from Course"
    cs.execute(sql)
    courseInfo = cs.fetchall()
    print(courseInfo)


if __name__ == '__main__':
    conn = pymysql.connect(host='localhost', port=3306,
                           user='root', passwd='123123', db="nudt", charset='utf8')
    command = input()
    cs = conn.cursor()
    if command == '01':
        addCourse(cs)
    elif command == '02':
        updateCourse(cs)
    elif command == '03':
        findCourseByCno(cs)
    elif command == '04':
        deleteCourse(cs)
    elif command == '05':
        findCourseByTeacherName(cs)
    elif command == '06':
        selectTeacherByCname(cs)
    conn.commit()
    Test(cs)
    cs.close()
    conn.close()

Tags: Python Big Data

Posted on Sun, 21 Nov 2021 19:19:23 -0500 by MilesWilson