LOAD DATA LOCAL INFILE上的Python2.7 MySQL Connector错误 [英] Python2.7 MySQL Connector Error on LOAD DATA LOCAL INFILE
问题描述
我正尝试使用Python和MySQL Connector将普查数据动态加载到mysql数据库(从.csv文件)中.
I'm trying to dynamically load census data into a mysql database (from .csv files) using Python and MySQL Connector.
我不知道为什么会出现错误:
I can't figure out why I am getting the error:
Traceback (most recent call last):
File "miner.py", line 125, in <module>
cursor.execute(add_csv_file, csv_info)
File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 393, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 586, in cmd_query
statement))
File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 508, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/Users/afink/Documents/Research/Current Papers & Books/Youth Assessm' at line 1
执行前输出的字符串在同一用户下的MySQL命令行界面中运行良好.
The outputted string before execution works fine in MySQL command line interface under the same user.
似乎应该是一个简单的问题,但是我被卡住了!
Seems like it should be a simple problem, but I'm stuck!
def db_connect():
config = {
'user': 'username',
'password': 'pass',
'host': 'localhost',
'database': 'uscensus',
'raise_on_warnings': True,
}
from mysql.connector import errorcode
try:
cnx = mysql.connector.connect(**config)
return cnx
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
else:
cnx.close()
cursor = cnx.cursor()
os.chdir("./")
for files in glob.glob("*.csv"):
add_csv_file = ('''load data local infile '%(cwd)s/%(file_name)s' into table %(table_name)s
columns terminated by ','
optionally enclosed by '\"'
escaped by '\"'
lines terminated by '\\n';''')
# Insert CSV file information
csv_info = {
'cwd': os.getcwd(),
'file_name': files,
'table_name': 'SF1_' + files[2:-8],
}
print add_csv_file % csv_info # Temporary Debug
cursor.execute(add_csv_file, csv_info)
# Make sure data is committed to the database
cnx.commit()
cursor.close()
cnx.close()
谢谢您的帮助!
推荐答案
可以通过在连接中添加适当的客户端标志来轻松解决此问题,如下所示:
This is easily solved with adding the appropriate client flag in your connection as below:
import mysql.connector
from mysql.connector.constants import ClientFlag
cnx = mysql.connector.connect(user='[username]', password='[pass]', host='[host]', client_flags=[ClientFlag.LOCAL_FILES])
cursor = cnx.cursor()
这将授予MySQL访问您计算机上本地文件的权限,然后以下LOAD将起作用:
This will give permission for MySQL to access the local files on your machine and then the following LOAD will work:
LoadSQL = """LOAD DATA LOCAL INFILE '%s'
INTO TABLE %s
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(field1, field2, field3, field4)""" % (csvfile, tabl_name)
cursor.execute(LoadSQL)
cnx.commit()
cursor.close()
cnx.close()
这篇关于LOAD DATA LOCAL INFILE上的Python2.7 MySQL Connector错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!