import sys
from PySide import QtSql, QtCore

def main():
    db = QtSql.QSqlDatabase.addDatabase("QMYSQL")
    db.setHostName("localhost")
    db.setDatabaseName("codeforce")
    db.setUserName("root")
    db.setPassword("")
    ok = db.open()
    if not ok:
        #QtGui.QMessageBox.warning(self, "Error", "Invalid database!")
        print "Invalid database!"
        return
    #create_table(db)
    insert(db)
    #select()
    #update(db)
    #delete(db)
    db.close()

def select():
    query = QtSql.QSqlQuery("SELECT * FROM developers")
    lname_nr = query.record().indexOf("last_name")
    while query.next():
        id = query.value(0)
        fname = query.value(1)
        lname = query.value(lname_nr)
        print "id = %d, fname = %s, lname = %s" % \
              (id, fname, lname)

def call_store_procedure():
    query = QtSql.QSqlQuery()
    query.prepare("CALL AsciiToInt(?, ?)")
    query.bindValue(0, "A")
    query.bindValue(1, 0, QtSql.QSql.Out)
    query.exec_()
    i = query.boundValue(1) # i is 65
    print i

def update(db):
    query = QtSql.QSqlQuery()
    sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 \
                              WHERE SEX = '%c'" % ('M')
    try:
        query.exec_(sql)
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()

def delete(db):
    query = QtSql.QSqlQuery()
    sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
    try:
        query.exec_(sql)
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()

def create_table(db):
    query = QtSql.QSqlQuery()
    query.exec_("DROP TABLE IF EXISTS EMPLOYEE")
    sql = """CREATE TABLE EMPLOYEE (
             FIRST_NAME  CHAR(20) NOT NULL,
             LAST_NAME  CHAR(20),
             AGE INT,
             SEX CHAR(1),
             INCOME FLOAT )"""
    query.exec_(sql)


def simple_insert(db):
    query = QtSql.QSqlQuery()
    sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
             LAST_NAME, AGE, SEX, INCOME)
             VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
    try:
        query.exec_(sql)
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()

if __name__ == '__main__':
    main()