LOAD DATA LOCAL INFILE sqlalchemy和python到mysql db

发布时间：2020-12-20 11:42:35 所属栏目：Python 来源：网络整理

导读：我正在尝试使用sqlalchemy的LOAD LOCAL DATA INFILE将批处理文件上传到我的服务器.我编辑了我的/etc/ mysql/my.conf以在[mysql]和[mysqld]下包含local-infile == 1,但是当我运行程序时我得到了这个回溯. 相关的代码行是： Traceback (most recent call last)

我正在尝试使用sqlalchemy的LOAD LOCAL DATA INFILE将批处理文件上传到我的服务器.我编辑了我的/etc/ mysql/my.conf以在[mysql]和[mysqld]下包含local-infile == 1,但是当我运行程序时我得到了这个回溯.

相关的代码行是：

Traceback (most recent call last):
  File "main.py",line 48,in <module>
    con.execute(sql)
  File "/Users/eoddata/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 906,in execute
    return self._execute_text(object,multiparams,params)
  File "/Users/eoddata/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 1054,in _execute_text
    statement,parameters
  File "/Users/eoddata/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 1146,in _execute_context
    context)
  File "/Users/eoddata/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 1332,in _handle_dbapi_exception
    exc_info
  File "/Users/eoddata/venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py",line 199,in raise_from_cause
    reraise(type(exception),exception,tb=exc_tb)
  File "/Users/eoddata/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 1139,in _execute_context
    context)
  File "/Users/eoddata/venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py",line 442,in do_execute
    cursor.execute(statement,parameters)
  File "/Users/eoddata/venv/lib/python2.7/site-packages/MySQLdb/cursors.py",line 205,in execute
    self.errorhandler(self,exc,value)
  File "/Users/eoddata/venv/lib/python2.7/site-packages/MySQLdb/connections.py",line 36,in defaulterrorhandler
    raise errorclass,errorvalue
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1148,'The used command is not allowed with 
this MySQL version') [SQL: "LOAD DATA LOCAL
 INFILE 'stocks/AMEX_20150420.txt' INTO TABLE database.new_tablen                    
FIELDS TERMINATED BY ',' (Symbol,@Date,Open,High,Low,Close,Volume)n                    
SET Date = STR_TO_DATE(@Date,'%%Y%%m%%d');"]

我从文档中做了一些研究,但找不到修复此错误的方法.使用python MySQLdb(来自pip install MySQL-Python)将允许我传入本地infile参数.我在SQLAlchemy中找不到相应的东西.任何帮助表示赞赏.

编辑：我整晚都在努力,仍然没有取得任何进展 – 很多死路一条.有任何想法吗？

谢谢,贾里德

解决方法

您应该将local_infile = 1添加到连接字符串.

SQLALCHEMY_DATABASE_URI = "mysql://user:pass@localhost/dbname?charset=utf8&local_infile=1"

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!