title: Proper use of MySQL full text search with SQLAlchemy
body: <p>I would like to be able to full text search across several text fields of one of my SQLAlchemy mapped objects. I would also like my mapped object to support foreign keys and transactions.</p>
  
  <p>I plan to use MySQL to run the full text search. However, I understand that MySQL can only run full text search on a MyISAM table, which does not support transactions and foreign keys. </p>
  
  <p>In order to accomplish my objective I plan to create two tables. My code will look something like this:</p>
  
  <pre><code>class User(Base):
      __tablename__ = 'users'
      id = Column(Integer, primary_key=True)
      name = Column(String(50))
      description = Column(Text)
  
  users_myisam = Table('users_myisam', Base.metadata,
                       Column('id', Integer),
                       Column('name', String(50)),
                       Column('description', Text),
                       mysql_engine='MyISAM')
  
  conn = Base.metadata.bind.connect()
  conn.execute("CREATE FULLTEXT INDEX idx_users_ftxt \
                on users_myisam (name, description)")
  </code></pre>
  
  <p>Then, to search I will run this:</p>
  
  <pre><code>q = 'monkey'
  ft_search = users_myisam.select("MATCH (name,description) AGAINST ('%s')" % q)
  result = ft_search.execute()
  for row in result: print row
  </code></pre>
  
  <p>This seems to work, but I have a few questions:</p>
  
  <ol>
  <li><p>Is my approach of creating two tables to solve my problem reasonable? Is there a standard/better/cleaner way to do this?</p></li>
  <li><p>Is there a SQLAlchemy way to create the fulltext index, or am I best to just directly execute "CREATE FULLTEXT INDEX ..." as I did above?</p></li>
  <li><p>Looks like I have a SQL injection problem in my search/match against query. How can I do  the select the "SQLAlchemy way" to fix this? </p></li>
  <li><p>Is there a clean way to join the users_myisam select/match against right back to my user table and return actual User instances, since this is what I really want?</p></li>
  <li><p>In order to keep my users_myisam table in sync with my mapped object user table, does it make sense for me to use a MapperExtension on my User class, and set the before_insert, before_update, and before_delete methods to update the users_myisam table appropriately, or is there some better way to accomplish this?</p></li>
  </ol>
  
  <p>Thanks,
  Michael</p>