title: Pyramid, Python3, SQLAlchemy and MySQL
body: <p>I want Pyramid, Python3, SQLAlchemy and MySQL to play nice on a single machine. This question has been <a href="https://stackoverflow.com/a/14282839/">asked before</a>, but it was a long enough time ago that I'm hoping the answer has changed...</p>
  
  <p><strong>Questions:</strong></p>
  
  <p><em>What driver works for getting a Python 3 Pyramid app to talk nicely to MySQL via SQLAalchemy? Is this even possible?</em> </p>
  
  <p>I've gotten quite far in this project thinking it would be a simple thing to switch out databases, now I'm a bit stuck and downgrading to Python 2.7 is not something I want to have to go through. So my second question is:</p>
  
  <p><em>If there is no way to talk to MySQL nicely without downgrading, is there a database that Python 3 is ready for (besides SQLite)?</em></p>
  
  <p>Or lastly, <em>how would I overcome any of the problems mentioned in the 'Stuff I've Tried' section below?</em></p>
  
  <p><strong>Stuff I've Tried:</strong></p>
  
  <p><em>PyMySQL</em></p>
  
  <p>I have PyMySQL installed and I even got it to complain about incorrect passwords and a missing database so it's getting something right. But when I tried to initialize my database (using <code>initialize_foo_db</code>) everything went sour:</p>
  
  <pre><code>2013-02-06 17:18:31,282 INFO  [sqlalchemy.engine.base.Engine][MainThread] b'SELECT DATABASE()'
  2013-02-06 17:18:31,282 INFO  [sqlalchemy.engine.base.Engine][MainThread] ()
  Traceback (most recent call last):
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/pool.py", line 719, in _do_get
  return self._pool.get(wait, self._timeout)
  File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/util/queue.py", line 137, in get
  raise Empty
  sqlalchemy.util.queue.Empty
  
  During handling of the above exception, another exception occurred:
  
  Traceback (most recent call last):
    File "../bin/initialize_mega_db", line 9, in &lt;module&gt;
  load_entry_point('mega==0.0', 'console_scripts', 'initialize_mega_db')()
    File "/home/sheena/MegaCatalogue/env/mega/mega/scripts/initializedb.py", line 46, in main
  Base.metadata.create_all(engine)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/schema.py", line 2567, in create_all
  tables=tables)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/engine/base.py", line 2301, in _run_visitor
  conn = self.contextual_connect(close_with_result=False)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/engine/base.py", line 2492, in contextual_connect
  self.pool.connect(), 
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/pool.py", line 224, in connect
  return _ConnectionFairy(self).checkout()
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/pool.py", line 387, in __init__
  rec = self._connection_record = pool._do_get()
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/pool.py", line 741, in _do_get
  con = self._create_connection()
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/pool.py", line 188, in _create_connection
  return _ConnectionRecord(self)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/pool.py", line 273, in __init__
  pool.dispatch.first_connect.exec_once(self.connection, self)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/event.py", line 279, in exec_once
  self(*args, **kw)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/event.py", line 288, in __call__
  fn(*args, **kw)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/engine/strategies.py", line 168, in first_connect
  dialect.initialize(c)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/dialects/mysql/base.py", line 1999, in initialize
  default.DefaultDialect.initialize(self, connection)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/engine/default.py", line 177, in initialize
  self._get_default_schema_name(connection)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/dialects/mysql/base.py", line 1964, in _get_default_schema_name
  return connection.execute('SELECT DATABASE()').scalar()
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/engine/base.py", line 1449, in execute
  params)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/engine/base.py", line 1628, in _execute_text
  statement, parameters
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/engine/base.py", line 1691, in _execute_context
  context)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/SQLAlchemy-0.7.8-py3.2.egg/sqlalchemy/engine/default.py", line 333, in do_execute
  cursor.execute(statement, parameters)
    File "/home/sheena/MegaCatalogue/env/local/lib/python3.2/site-packages/PyMySQL3-0.5-py3.2.egg/pymysql/cursors.py", line 108, in execute
  query = query % escaped_args
  TypeError: unsupported operand type(s) for %: 'bytes' and 'tuple'
  </code></pre>
  
  <p><em>oursql</em></p>
  
  <p>I tried installing oursql using <code>pip install -r file</code></p>
  
  <p>where file contains:</p>
  
  <pre><code>#oursql==0.9.3 but need special Python 3 build https://bugs.launchpad.net/oursql/+bug/1051512
  https://launchpad.net/oursql/py3k/py3k-0.9.3/+download/oursql-0.9.3.zip
  pyramid==1.3.4
  </code></pre>
  
  <p>And I got:</p>
  
  <pre><code>oursqlx/compat.h:13:19: fatal error: mysql.h: No such file or directory
  compilation terminated.
  error: command 'gcc' failed with exit status 1
  </code></pre>
  
  <p><em>myconnpy</em></p>
  
  <p>This installs without error but I can't seem to find out what I should tell SQLAlchemy to look for. In the appropriate <code>settings.ini</code> file I have tried:</p>
  
  <pre><code>sqlalchemy.url = mysql+myconnpy://...
  </code></pre>
  
  <p>as well as a bunch of other things. Google is failing me on this one...</p>
  
  <p><strong>UPDATE - a semblance of progress</strong></p>
  
  <p>I've managed to install oursql build dependencies:</p>
  
  <pre><code>sudo aptitude install python-pip libmysqlclient-dev
  </code></pre>
  
  <p>I then downloaded oursql0.9.3 and ran:</p>
  
  <pre><code>setup.py install
  </code></pre>
  
  <p>The output was this:</p>
  
  <pre><code>cython not found, using previously-cython'd .c file.
  running install
  running build
  running build_ext
  running install_lib
  copying build/lib.linux-x86_64-3.2/oursql.cpython-32mu.so -&gt; /home/sheena/MegaCatalogue/env/lib/python3.2/site-packages
  running install_egg_info
  Writing /path/to/virtualenv/foo/lib/python3.2/site-packages/oursql-0.9.3.egg-info
  </code></pre>
  
  <p>I opened a shell and apparently the module doesn't exist - it can't be imported anyway. But for some reason it is listed if I say help() then modules</p>