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.

Answer 1

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.