Martin Aspeli wrote:
> Hi all,
>
> 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?
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)]
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
