Load Data Local Infile with SQLAlchemy and pymysql

Question

I'm trying to using LOAD DATA LOCAL INFILE but I continue to get:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "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 '' at line 1") [SQL: "LOAD DATA LOCAL INFILE 'file.csv' "]

The SQL code works in the Workbench (and it was pre-existing code that I used inside of VBA). Also note that I specify local_infile=1 in the engine creation.

Here is the code:

import pymysql as mysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

def main():
    engine=create_engine('mysql+pymysql://user:pwd@localhost:3306/db?charset=utf8&local_infile=1')
    SessionMaker = sessionmaker(bind=engine)
    session = SessionMaker()
    sql = "LOAD DATA LOCAL INFILE '/home/file.csv' " 
    "INTO TABLE settles "
    "FIELDS TERMINATED BY ',' "
    "lines terminated by '\n' "
    "IGNORE 1 LINES " 
    "(product_symbol, contract_month, contract_year, contract_day, contract, " 
    "product_desc, open_price, high_price, high_ab_ind, low_price, low_ab_ind, " 
    "last_price, last_ab_ind, settle_price, " 
    "pt_chg, est_volume, prior_settle_price, prior_volume, prior_int, @var1) " 
    "set trade_date = str_to_date(@var1, '%m/%d/%Y');"
    session.execute(sql)
    session.flush()
    engine.dispose()

if __name__ == "__main__":
    main()

This is on Ubuntu 16.04

Please help.

try to wrap your `sql` object with [`text`](http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.text) and write what happens — Azat Ibrakov, May 25 '17 at 15:25
is your file large? there is an [issue](https://github.com/PyMySQL/PyMySQL/issues/407) pointing that it may not work for large files — Azat Ibrakov, May 25 '17 at 15:31
is your file path correct? try to put it in some directory where `MySQL` has access like `/var/` — Azat Ibrakov, May 25 '17 at 16:27
@AzatIbrakov It was the "text" that you suggested. That works. Thanks. How do I make that the answer? I now have another issue (of course) but this appears solved. — kdragger, May 25 '17 at 16:29

Azat Ibrakov · Accepted Answer · 2017-05-25T16:39:05.127

the issue here was with wrapping raw SQL query with text

sql = text("""
    LOAD DATA LOCAL INFILE '/home/file.csv' 
    INTO TABLE settles
    FIELDS TERMINATED BY ','
    lines terminated by '\n'
    IGNORE 1 LINES 
    (product_symbol, contract_month, contract_year, contract_day, contract,
    product_desc, open_price, high_price, high_ab_ind, low_price, low_ab_ind, 
    last_price, last_ab_ind, settle_price, 
    pt_chg, est_volume, prior_settle_price, prior_volume, prior_int, @var1) 
    set trade_date = str_to_date(@var1, '%m/%d/%Y')
""")

which is helpful in such cases.

Recommended to read Using Textual SQL part of SQLAlchemy's SQL Expression Language Tutorial.

Also reference to text can be found at this question.

score 1 · Answer 2 · answered May 25 '17 at 02:49

You have to make sure your long sql query is correct with """:

sql = """
    LOAD DATA LOCAL INFILE '/home/file.csv' 
    INTO TABLE settles
    FIELDS TERMINATED BY ','
    lines terminated by '\n'
    IGNORE 1 LINES 
    (product_symbol, contract_month, contract_year, contract_day, contract,
    product_desc, open_price, high_price, high_ab_ind, low_price, low_ab_ind, 
    last_price, last_ab_ind, settle_price, 
    pt_chg, est_volume, prior_settle_price, prior_volume, prior_int, @var1) 
    set trade_date = str_to_date(@var1, '%m/%d/%Y')
"""

Thank you. I changed the code and received the exact same error. I believe that this has to do with the "LOCAL" designation. — kdragger, May 25 '17 at 12:13

Load Data Local Infile with SQLAlchemy and pymysql

2 Answers2