UNC路径的Python和MySQL转义序列
UNC路径的Python和MySQL转义序列
提问于 2018-07-04 05:50:08
我花了一段很长的时间来使用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正在执行。
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-07-11 03:15:20
考虑一下Python与操作系统无关的os.path.join()，不要担心反斜杠或正斜杠。并使用string.format甚至Python3.6的新f-string (文字字符串插值，就像Perl和Perl的$string)。不建议使用模运算符进行字符串插值。请确保还要执行commit操作：
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-string
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()页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51163233
复制相关文章
相似问题