树莓派Python用mysql.connector导数据报错：表不存在

API 探路者

2026-5-12

Fixing the LOAD DATA Table Not Found Error

The error you're seeing (Table 'd1.tblname' doesn't exist) happens because your LOAD DATA statement is using the literal strings tblname and finalpath instead of the actual values stored in your Python variables. MySQL doesn't recognize Python variables unless you explicitly insert their values into the SQL string.

Root Cause

In your original code, this line is the problem:

loadData= "LOAD DATA LOCAL INFILE 'finalpath' INTO TABLE tblname COLUMNS TERMINATED BY '\t' "

MySQL interprets tblname as a literal table name (not your variable) and finalpath as a literal file path—so it's looking for a table named tblname that doesn't exist, and a file named finalpath that also doesn't exist.

Solution

You need to interpolate your tblname and finalpath variables into the SQL query, just like you did for the CREATE TABLE statement. Here are two clean, working approaches:

Option 1: Using F-strings (Python 3.6+)

This is the most readable and modern way to format the query:

# Escape single quotes in the file path to avoid SQL syntax errors
escaped_path = finalpath.replace("'", "\\'")
loadData = f"LOAD DATA LOCAL INFILE '{escaped_path}' INTO TABLE {tblname} COLUMNS TERMINATED BY '\t'"

Option 2: Using String Formatting (Compatible with Older Python Versions)

If you're using an older Python version, use % formatting (matching how you created the table):

escaped_path = finalpath.replace("'", "\\'")
loadData = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s COLUMNS TERMINATED BY '\t'" % (escaped_path, tblname)

Key Notes

Escaping Single Quotes: We replace any single quotes in the file path with \' to prevent SQL syntax errors. While your path might not have quotes, this is a safe practice to avoid breaking the query.
SQL Injection Warning: Direct string interpolation can be risky if filenames come from untrusted sources. Since you're loading files from your own directory, this is acceptable here. For untrusted input, you'd need a different approach, but it's unnecessary in this case.

Optional: Skip Header Rows: If your text files have a header row, add IGNORE 1 LINES at the end of the query to skip it:

loadData = f"LOAD DATA LOCAL INFILE '{escaped_path}' INTO TABLE {tblname} COLUMNS TERMINATED BY '\t' IGNORE 1 LINES"

Full Corrected Loop Section

Here's how the loop part of your script should look after fixing:

for dirName, subdirList, fileList in os.walk(rootDir):
    for fname in fileList:
        tblname = os.path.basename(fname).split('.')[0]
        print(tblname)
        sql= 'CREATE TABLE %s (Datum varchar(12), Uhrzeit varchar(13), UpsACT_V varchar(6), UpsPRE_V varchar(6), IpsACT_A varchar(6), IpsPRE_A varchar(6), PpsACT_W varchar(6), PpsPRE_W varchar(10))' % tblname
        myCursor.execute(sql)
        myConnection.commit()
        finalpath= os.path.join(rootDir,fname)
        print(finalpath)
        # Fixed LOAD DATA statement
        escaped_path = finalpath.replace("'", "\\'")
        loadData = f"LOAD DATA LOCAL INFILE '{escaped_path}' INTO TABLE {tblname} COLUMNS TERMINATED BY '\t'"
        myCursor.execute(loadData)
        myConnection.commit()

This should resolve the "table not found" error, as the query will now use the actual table name and file path from your variables.

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

树莓派Python用mysql.connector导数据报错：表不存在

Root Cause #

Solution #