爬取名言插入MySQL时触发Data too long for column 'Quotes'错误的解决方法咨询

Let’s tackle this error head-on—it’s a super common issue when dealing with scraped text that outgrows your database column’s limits. Here’s exactly how to resolve it and get all 150 quotes into your quoteslist table:

1. Update Your MySQL Column to Support Longer Text #

The root cause is simple: your Quotes column is set to a type (like VARCHAR) with a fixed length that’s too short for some of the quotes on that page. Let’s fix that:

Step 1: Check your current column setup #

Run this SQL query in your MySQL client to see what type your Quotes column uses:

DESCRIBE quoteslist;

You’ll probably see something like VARCHAR(255)—way too small for longer quotes.

Step 2: Alter the column to use LONGTEXT #

LONGTEXT can store up to 4GB of text, which is more than enough for any quote. Run this query to update the column:

ALTER TABLE quoteslist MODIFY COLUMN Quotes LONGTEXT CHARACTER SET utf8mb4;

We keep utf8mb4 to ensure support for any special characters or emojis that might appear in the quotes.

2. Verify Your Scraping Logic (Optional but Smart) #

Before re-running your code, quickly check that you’re only scraping the actual quote text (not extra page content). Add a print statement to inspect a few entries:

for i, q in enumerate(quote[:5]):
    print(f"Quote {i+1}: Length = {len(q)} | Preview: {q[:100]}...")

This confirms if any quotes are unexpectedly long (maybe your selector pulled in extra HTML elements). Your current selector looks solid, but it never hurts to double-check.

3. Re-Run Your Insertion Code #

Once you’ve updated the column type, fire up your Python script again. The Data too long error should vanish, and all 150 quotes will be inserted successfully.

Bonus: Add Robust Error Handling (Optional) #

To prevent the whole script from crashing if one quote has an issue, wrap the insertion in a try-except block:

with MYSQL.Connect(**CONFIG) as db:
    mycursor = db.cursor()
    for q in quote:
        try:
            idx = q.split()[0]
            if idx[0].isdigit():
                text = q[len(idx):].strip()
                params = (idx.replace('.', ''), text)
                mycursor.execute(SQL, params)
        except Exception as e:
            print(f"Failed to insert quote: {q[:50]}... Error: {str(e)}")
    db.commit()

This way, problematic entries get logged instead of stopping the entire import.

内容的提问来源于stack exchange，提问作者user16622907