我花了一段很长的时间来使用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正在执行。
发布于 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()
https://stackoverflow.com/questions/51163233
复制相似问题