将变量路径导入 MySQL 查询到 Python 脚本中

在 Python 脚本中编写 MySQL 查询时，需要将一个变量路径导入查询中。然而，在使用变量路径时遇到了问题，要么路径会被解析成双反斜杠，要么完全没有解析。示例代码如下：

filepath = "M:/Users/Jonathan/Dropbox/BCHS_3015/Spatial Data/Cartographic Data/USA/acs_data/Sequence_Number_and_Table_Number_Lookup.csv"
cursor.execute ("""load data local infile '%s'
                into table Sequence_Table_Lookup
                fields terminated by ','enclosed by '"'
                lines terminated by '\r\n'
                ignore 1 lines
                (File_ID,Table_ID,Sequence_Number,Line_Number, Subject_Area)""" % filepath);

执行此代码会返回以下错误：

_mysql_exceptions.InternalError: (22, "File 'M:UsersJonathanDropbox\x08chs_3015spatial datacartographic datausaacs_dataSequence_Number_and_Table_Number_Lookup.txt' not found (Errcode: 22)")

2、解决方案：

为了解决这个问题，可以使用 os.path.join() 函数将路径中不同部分连接成一个完整的路径。示例代码如下：

import os

# 将路径段连接成一个完整的路径
filepath = os.path.join("M:", "Users", "Jonathan", "Dropbox", "BCHS_3015", "Spatial Data",
                       "Cartographic Data", "USA", "acs_data", "Sequence_Number_and_Table_Number_Lookup.csv")

# 使用完整的路径执行 MySQL 查询
cursor.execute ("""load data local infile '%s'
                into table Sequence_Table_Lookup
                fields terminated by ','enclosed by '"'
                lines terminated by '\r\n'
                ignore 1 lines
                (File_ID,Table_ID,Sequence_Number,Line_Number, Subject_Area)""" % filepath);

此代码将正确地将变量路径导入 MySQL 查询中，并不会产生错误。

完整且可运行的代码示例如下：

import MySQLdb
import os
import navigation

# 连接到 MySQL 数据库
conn = MySQLdb.connect (host = "localhost",
                           user = "user",
                           passwd = "pwd",
                           db = "gis_census_acs")

# 查找 census 目录
dropbox = navigation.get_dropbox_home()
acs_data = os.path.join(dropbox,'bchs_3015','spatial data','cartographic data','usa','acs_data');

# 遍历 census 目录中的文件
for filepath in navigation.get_filepaths(acs_data):
        filename = os.path.split(filepath)[1]
        if filename == 'Sequence_Number_and_Table_Number_Lookup.txt':
            print (filepath)
            tablename = filename.split('.')[0].replace(' ','_')[0:64]
            cursor = conn.cursor()
            cursor.execute ('create table if not exists ' + tablename + """(
                    File_ID varchar(255),
                    Table_ID varchar(255),
                    Sequence_Number varchar(255),
                    Line_Number varchar(255),
                    Start_Position varchar(255),
                    Total_cells_in_Table varchar(255),
                    Total_Cells_in_Sequence varchar(255),
                    Table_title text,
                    Subject_Area text
                    )""");
            # 使用连接好的路径执行 MySQL 查询
            cursor.execute ("""load data local infile '%s'
                    into table Sequence_Table_Lookup
                    fields terminated by ','enclosed by '"'
                    lines terminated by '\r\n'
                    ignore 1 lines
                    (File_ID,Table_ID,Sequence_Number,Line_Number, Start_Position, 
                    Total_cells_in_Table, Total_Cells_in_Sequence, Table_title, Subject_Area)""" % filepath);
            print ("Number of rows inserted: %d" % cursor.rowcount)
            cursor.close()
        else:
            print ("not the file")
conn.close ()