Any examples of SQLalchemy 0.7, UPDATE using from_statement()
Question: I'm writing a quick one-off migration script that updates a single field in a
table with half a million rows.

Since I hadn't planned on writing out full models for the joins I'm doing to
fetch the initial ~25000 rows of data, I've been trying to figure out how to
do an UPDATE statement using a from_statement() call and using my own raw sql,
but I can't find any examples.

Along with that, SQLalchemy is throwing an error. Here's an example of my call
and error:

    
    
    mydb = self.session()
    mydb.query().from_statement(
        """
        UPDATE my_table
        SET settings=mysettings
        WHERE user_id=myuserid AND setting_id=123
        """).params(mysettings=new_settings, myuserid=user_id).all()
    

The error I get:

    
    
    Traceback (most recent call last):
      File "./sample_script.py", line 111, in <module>
        main()
      File "./sample_script.py", line 108, in main
        migrate.set_migration_data()
      File "./sample_script.py", line 100, in set_migration_data
        """).params(mysettings=new_settings, myuserid=user_id).all()
      File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 1267, in all
        return list(self)
      File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 1361, in __iter__
        return self._execute_and_instances(context)
      File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 1364, in _execute_and_instances
        result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none())
      File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/query.py", line 251, in _mapper_zero_or_none
        if not getattr(self._entities[0], 'primary_entity', False):
    IndexError: list index out of range
    

**UPDATE**

I'm using MySQL.

Per Samy's suggestion, I tried this:

    
    
    mydb.execute(
        "UPDATE mytable SET settings=:mysettings WHERE user_id=:userid AND setting_id=123",
        {'userid': user_id, 'mysettings': new_settings}
        )
    

This had no effect. I don't get any errors, but the statement doesn't seem to
actually execute, as the row does not change. If I manually cut and paste the
query that gets logged from the echo=True option, the row updates in the
database just fine.

**UPDATE - SOLVED**

Samy's suggestion was correct but the .execute() call only works on 'engine',
not 'session', so this worked just fine:

    
    
    self.engine.execute(
        "UPDATE mytable SET settings=:mysettings WHERE user_id=:userid AND setting_id=123",
        {'userid': user_id, 'mysettings': new_settings}
        )
    


Answer: Well this is rather strange, according to the docs, the
[from_statement](http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html?highlight=from_statement#sqlalchemy.orm.query.Query.from_statement)
is used for `SELECT` statements.

> Execute the given SELECT statement and return results.

I could be looking at the wrong function, or it may be possible to use other
type of statements, Im not really sure.

You could just use `execute` since it can do any type of statement, heres a
quick example.

    
    
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    
    session = sessionmaker(bind = create_engine('sqlite://'), autocommit = True)()
    
    _ = session.execute('CREATE TABLE my_table (user_id int, setting_id int, settings string)')
    for id in xrange(200):
        _ = session.execute('INSERT INTO my_table (user_id, setting_id) VALUES (:user_id, :setting_id)',
            {'user_id':id, 'setting_id':id})
    
    _ = session.execute(
    """
        UPDATE my_table
        SET settings = :mysettings
        WHERE user_id = :user_id AND setting_id = 123
    """, {'user_id':123, 'mysettings':'test'})
    
    r = session.execute('SELECT * FROM my_table WHERE user_id = :user_id', {'user_id':123}).fetchall()
    print r
    
    [(123, 123, u'test')]
    

note that this isn't really the best way to use `sqlalchemy`, which was
designed to create a dry environment, decoupled from a specific db backend,
though you probably have your reasons for using raw sql versus its ORM.

