python+fastapi将csv数据导入mysql

python+fastapi将csv数据导入mysql
主要功能：

generate_test_csv：生成包含随机学生成绩的CSV文件。
create_student_table：在数据库中创建学生成绩表。
import_student_scores：将CSV文件中的数据导入到数据库表中。

step1:C:\Users\wangrusheng\PycharmProjects\FastAPIProject1\hello.py

import csv
import random
import pymysql.cursors

# Reuse existing database configuration
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'db': 'db_school',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

def generate_test_csv(file_path, num_rows=100):
    """Generate test CSV data"""
    rows = []
    for i in range(num_rows):
        student_id = 1001 + i
        name = f"Student{i + 1}"
        chinese = random.randint(60, 100)
        math = random.randint(60, 100)
        english = random.randint(60, 100)
        physics = random.randint(60, 100)
        chemistry = random.randint(60, 100)
        total_score = chinese + math + english + physics + chemistry
        rows.append({
            'student_id': str(student_id),
            'name': name,
            'chinese': chinese,
            'math': math,
            'english': english,
            'physics': physics,
            'chemistry': chemistry,
            'total_score': total_score
        })

    # Write to CSV file
    with open(file_path, 'w', newline='', encoding='utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=[
            'student_id', 'name', 'chinese', 'math', 'english',
            'physics', 'chemistry', 'total_score'
        ])
        writer.writeheader()
        writer.writerows(rows)
    return num_rows

def create_student_table():
    """Create student scores table"""
    connection = pymysql.connect(**DB_CONFIG)
    try:
        with connection.cursor() as cursor:
            # Create student scores table
            sql = """
            CREATE TABLE IF NOT EXISTS student_scores (
                student_id VARCHAR(20) PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                chinese INT NOT NULL,
                math INT NOT NULL,
                english INT NOT NULL,
                physics INT NOT NULL,
                chemistry INT NOT NULL,
                total_score INT NOT NULL
            ) CHARSET=utf8mb4
            """
            cursor.execute(sql)
        connection.commit()
    except Exception as e:
        connection.rollback()
        raise e
    finally:
        connection.close()

def import_student_scores(csv_path):
    """Import student score data"""
    # Read CSV file
    with open(csv_path, 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        data = [row for row in reader]

    # Connect to database
    connection = pymysql.connect(**DB_CONFIG)
    try:
        with connection.cursor() as cursor:
            # Bulk insert data
            sql = """
            INSERT INTO student_scores 
            (student_id, name, chinese, math, english, physics, chemistry, total_score)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            """
            # Prepare parameters
            params = [
                (
                    row['student_id'],
                    row['name'],
                    int(row['chinese']),
                    int(row['math']),
                    int(row['english']),
                    int(row['physics']),
                    int(row['chemistry']),
                    int(row['total_score'])
                )
                for row in data
            ]
            cursor.executemany(sql, params)
        connection.commit()
        return len(data)
    except Exception as e:
        connection.rollback()
        raise e
    finally:
        connection.close()

# Other existing functions remain unchanged...

if __name__ == '__main__':
    csv_path = r'C:\Users\wangrusheng\PycharmProjects\FastAPIProject1\student_scores.csv'

    try:
        # Create table
        create_student_table()
        print("Student scores table created successfully")

        # Generate test data (new feature)
        test_data_count = 50  # Generate 50 test records
        generate_test_csv(csv_path, test_data_count)
        print(f"Successfully generated {test_data_count} test records to: {csv_path}")

        # Import data
        inserted_count = import_student_scores(csv_path)
        print(f"Successfully inserted {inserted_count} student score records")

    except Exception as e:
        print(f"Operation failed: {str(e)}")

step2:C:\Users\wangrusheng\PycharmProjects\FastAPIProject1\student_scores.csv

student_id,name,chinese,math,english,physics,chemistry,total_score
1001,Student1,92,60,83,99,68,402
1002,Student2,75,86,90,84,63,398
1003,Student3,86,80,84,88,65,403
1004,Student4,96,85,67,64,80,392
 ......
1050,Student50,88,95,93,64,79,419

亲测可运行，成功执行，测试通过
end