MySQL LOAD DATA INFILE
Martin Aspeli
I have a use case where I need to execute a MySQL LOAD DATA INFILE
statement on an SQLAlchemy connection.
I've tried this is with an engine using a threadlocal strategy, using
engine.scalar() and passing a string that contains the (generated)
LOAD
DATA INFILE statement.
The statement works if typed manually into the MySQL console, so I'm
pretty sure it's right. I don't get any errors either (and I'm able
to
make it error by deliberately introducing a syntax error, so it must
reach MySQL) but no data ever ends up in the table.
Can anyone think of what I'm doing wrong?
Martin
jason kirtland
Hi Martin,
I'm guessing you're using a transactional storage engine like InnoDB for
this table? It looks like LOAD DATA INFILE isn't autocommiting at the
moment, and that seems like the most likely explanation. For the time
being, you can workaround this by using an explicit transaction for your
load:
import os
from sqlalchemy import create_engine
print open('/var/tmp/data.csv').read()
engine = create_engine('mysql:///test')
con = engine.connect()
trans = con.begin()
con.execute("LOAD DATA INFILE '/var/tmp/data.csv' "
"INTO TABLE testtable "
"FIELDS TERMINATED BY ','")
trans.commit()
print list(engine.execute('SELECT * FROM testtable'))
# 1,2
# 2,2
# 3,2
# 1,3
# 2,3
# 3,3
# [(1L, 2L), (2L, 2L), (3L, 2L), (1L, 3L), (2L, 3L), (3L, 3L)]