atom feed2 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: MySQL LOAD DATA INFILE
FromSent OnAttachments
Martin AspeliAug 11, 2007 3:09 pm 
jason kirtlandAug 12, 2007 12:37 pm 
Subject:[sqlalchemy] Re: MySQL LOAD DATA INFILE
From:jason kirtland (je@discorporate.us)
Date:Aug 12, 2007 12:37:59 pm
List:com.googlegroups.sqlalchemy

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)]