SQL Server Database Handling

The package for working with SQL Server databases is pymssql:

db = pymssql.connect(host='localhost', database='KjSql', charset='cp936')

When no speicfic users or root credentials are configured, the connection defaults to Windows authentication.

Always set the encoding to "cp936" during connection establishment to prevent Chinese character display issues. When inserting or querying data, apply data.encode('cp936') operations to avoid character encoding problems and ensure proper retrieval of Chinese text.

For insertions, configure both data encoding as 'cp936' and use parameterized queries with %s placeholders. Pass the product_name as a parameter to the execute method. Using placeholders %s in queries and passing parameter values separately ensures proper handling of Chinese character sets and prevents encoding conflicts.

product_name = combo_input.get().encode('cp936')
cursor.execute("SELECT Product_id FROM Products WHERE Product_Name=%s", product_name)

The follownig approach works for English but fails with Chinese characters:

product_name = combo_input.get().encode('cp936')
cursor.execute(f"SELECT Product_id FROM Products WHERE Product_Name='{product_name}'")

There appear to be compatibility issues when using Pack, Place, and Grid together, though further investigation is needed.

MySQL Database Operations

import pymysql.cursors

# Establish database connection
connection = pymysql.Connect(
    host='localhost',  # Host name
    port=3306,         # Port number
    user='root',       # Database username
    passwd='123456',   # Password
    db='demodb',       # Database name
    charset='utf8'     # Character encoding
)

# Obtain cursor
cursor = connection.cursor()

# Execute SQL query (1) Retrieve all grades for student ID 10002
# Results include student ID, name, department, course ID, course name, and corresponding grade
query_1 = '''SELECT
    Students.StudentID,
    Students.Name,
    Students.Department,
    Enrollments.CourseID,
    Enrollments.Score,
    Courses.Title
FROM
    Students
JOIN
    Enrollments ON Students.StudentID = Enrollments.StudentID
JOIN
    Courses ON Enrollments.CourseID = Courses.CourseID
WHERE
    Students.StudentID = '10002';'''

result_set_1 = cursor.execute(query_1)
records_1 = cursor.fetchall()

print("(1) Fetch all grades for student ID 10002 including ID, name, department, course ID, course name, and scores.")
for record in records_1:
    print("Student ID:", record[0])
    print("Name:", record[1])
    print("Department:", record[2])
    print("Course ID:", record[3])
    print("Course Name:", record[5])
    print("Score:", record[4])
    print("")

# Query students with grades above 85
query_2 = '''SELECT Students.StudentID,Students.Name,Students.Department,Courses.CourseID,Courses.Title,Enrollments.Score
FROM Students
JOIN Enrollments ON Enrollments.StudentID=Students.StudentID
JOIN Courses ON Courses.CourseID=Enrollments.CourseID
WHERE Enrollments.Score>85;'''

result_set_2 = cursor.execute(query_2)
records_2 = cursor.fetchall()

print("(2) Fetch courses with scores above 85 for each student, including ID, name, department, course ID, course name, and score.")
for record in records_2:
    print("Student ID:", record[0])
    print("Name:", record[1])
    print("Department:", record[2])
    print("Course ID:", record[3])
    print("Course Name:", record[4])
    print("Score:", record[5])
    print("")

# Insert new enrollment record
print("Insert enrollment record for student ID 10005, Operating Systems (00004) with score 73.")
cursor.execute("INSERT INTO Enrollments VALUES('10004','00004',100)")
connection.commit()

cursor.execute("SELECT * FROM Enrollments")
all_records = cursor.fetchall()

for record in all_records:
    print("Student ID:", record[0])
    print("Course ID:", record[1])
    print("Score:", record[2])
    print("")

# Delete student with ID 10003 from tables
# First modify Enrollments table structure for foreign key cascading

connection.close()