UNC 路径的 Python 和 MySQL 转义序列

Question

我在获取加载数据本地文件以使用 python 时遇到了很大的困难。我在 Python 3.6.5 中尝试了很多不同的方法:

sqlpath = '\\corp.domain.com\dept\DCGSI\Extracts\SIM\'
sqlpath = "\\corp.domain.com\dept\DCGSI\Extracts\SIM\"
sqlpath = '\\\\corp.domain.com\\dept\\DCGSI\\Extracts\\SIM\\'
sqlpath = '//corp.domain.com/dept/DCGSI/Extracts/SIM/'
sqlpath = os.path.join("\\corp.domain.com","dept","DCGSI","Extracts","SIM")
sqlpath = os.path("\\\\corp.domain.com\dept\DCGSI\Extracts\SIM\")

我似乎无法在下面使用转义来保存我的理智。我在这里阅读了至少 10 篇不同的帖子，并尝试了所有这些建议。我做错了什么，所以我可以在 loadQuery 中使用 unc 路径。

这是(当前)整个脚本:

import config
import os
import pymysql


username = config.username
dbpassword = config.dbpassword
dbhost = config.dburl
conn =  pymysql.connect(host=dbhost, port=3306,user=username,password=dbpassword,db='dcgsreports',autocommit=True,local_infile=1)
path = '//corp.domain.com/dept/DCGSI/Extracts/SIM'
tables = []
files = []

fileNames = os.listdir(path)
for fileNames in fileNames:
    if fileNames.endswith(".csv"):
        files.append(fileNames)
        tables.append(fileNames.split('.')[0])

for f,t in zip(files, tables) :
    truncQuery = '''TRUNCATE TABLE %s''' %t
    loadQuery = '''LOAD DATA LOCAL INFILE '//corp.domain.com/dept/DCGSI/Extracts/SIM/%s' INTO TABLE %s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES;''' %(f, t)
    print(loadQuery)
    cursor = conn.cursor()
    cursor.execute(truncQuery)
    cursor.execute(loadQuery)
    conn.commit()
conn.close()

该打印语句显示以下内容应作为查询传递(应该是正确的):将数据本地 INFILE '//corp.domain.com/dept/DCGSI/Extracts/SIM/SIM_Availability.csv' 加载到表 SIM_Availability 字段中，终止于 ',' 可选地包含于 '"' 行终止于 '' 忽略 1 行；

所有表都是空的，但这似乎表明只有 truncQuery 正在执行。

Answer 1

考虑 Python 与操作系统无关的 os.path.join() 并且不用担心反斜杠或正斜杠。并使用 string.format 甚至 Python 3.6 的新功能 f-string (像 Perl 的文字字符串插值和 Perl 的 $string)。不建议使用字符串插值的模运算符。请务必同时提交操作:

path = r"\\corp.domain.com\dept\DCGSI\Extracts\SIM"

for t, f in zip(tables, files) :

    truncQuery = "TRUNCATE TABLE `{t_name}`"
    loadQuery = """LOAD DATA LOCAL INFILE '{f_name}'
                   INTO TABLE `{t_name}` FIELDS TERMINATED BY ',' 
                   OPTIONALLY ENCLOSED BY '"' LINES 
                   TERMINATED BY '\r\n' IGNORE 1 LINES;"""

    print(loadQuery)

    cursor = conn.cursor()
    cursor.execute(truncQuery.format(t_name = t)
    cursor.execute(loadQuery.format(f_name = os.path.join(path, f),
                                    t_name = t))

    cursor.close()
    conn.commit()

带有 f 字符串

for t, f in zip(tables, files) :

    f_name = os.path.join(path, f)

    truncQuery = f"TRUNCATE TABLE `{t}`"
    loadQuery = f"""LOAD DATA LOCAL INFILE '{f_name}'
                    INTO TABLE `{t}` FIELDS TERMINATED BY ',' 
                    OPTIONALLY ENCLOSED BY '"' LINES 
                    TERMINATED BY '\r\n' IGNORE 1 LINES;"""

    print(loadQuery)

    cursor = conn.cursor()
    cursor.execute(truncQuery)
    cursor.execute(loadQuery)

    cursor.close()
    conn.commit()