Change SQLLite to PyMySQL

Another task is to change the database from SqlLite to MySQL (Hey, we need to use real database here). And since I'm using SQLAlchemy

Changes in development.ini

Change

sqlalchemy.url = sqlite:///%(here)s/xyzdb.db

To

sqlalchemy.url = mysql+pymysql://root:system@localhost/xyz?charset=utf8

The charset=utf8 is important for unicode

And for sure, you need to install pymysql before

% easy_install pymysql

Why I use PyMySql? Long story make short, since it's pure Python MySQL client. For another DB client library (with SQLAlchemy), check http://docs.sqlalchemy.org/en/latest/dialects/mysql.html

test-connection.py :

import sqlalchemy
from sqlalchemy.sql import select
from sqlalchemy import Table, MetaData


def init():
    try:
        server = 'localhost'
        db = 'pyramid'
        login = 'root'
        passwd = 'toordrowssap'
        engine_str = 'mysql+pymysql://{}:{}@{}/{}'.format(login, passwd, server, db)
        engine = sqlalchemy.create_engine(engine_str, echo=False, encoding='utf-8')
        connection = engine.connect()
        metadata = MetaData()
        t_servers = Table('auth_permission', metadata, autoload=True, autoload_with=engine)
        s = select([t_servers])
        result = connection.execute(s)
        for row in result:
            print(row['name'])
    except Exception:
        raise
    finally:
        connection.close()
if __name__ == '__main__':
    init()