openshift_web.py

class Database(object):
    db = None

    def __init__(self):
        self.db = MySQLdb.connect(host=os.environ.get('OPENSHIFT_MYSQL_DB_HOST'),
                                  port=os.environ.get('OPENSHIFT_MYSQL_DB_PORT'),
                                  user=os.environ.get('OPENSHIFT_MYSQL_DB_USERNAME'),
                                  passwd=os.environ.get('OPENSHIFT_MYSQL_DB_PASSWORD'),
                                  db="alexander",
                                  charset='utf8')
        self.__create_tables()

    def __del__(self):
        self.db.close()

    def _get_cursor(self):
        return self.db.cursor()

    def __create_tables(self):
        self.sql('''CREATE TABLE IF NOT EXISTS USERS (
    ID INTEGER AUTO_INCREMENT NOT NULL,
    LOGIN VARCHAR(32) NOT NULL,
    PASSWORD CHAR(32) NOT NULL,
) CHARACTER SET=UTF8;''')
        self.sql('''CREATE TABLE IF NOT EXISTS MESSAGES (
    ID INTEGER AUTO_INCREMENT NOT NULL,
    TEXT VARCHAR(255) NOT NULL,
    AUTHOR_ID INTEGER NOT NULL,
    DATE_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(ID),
    FOREIGN KEY(AUTHOR_ID) REFERENCES USERS(ID)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) CHARACTER SET=UTF8;''')

    def sql(self, query):
        cursor = self._get_cursor()
        cursor.execute(query)
        self.db.commit()
        data = cursor.fetchall()
        cursor.close()
        return data