def query_row(table):
    """Returns the next row of a query result set or None

        str -> tuple"""
    try:
        dbconfig = read_db_config()  # returns a dict of connection parameters
        print("Connecting " + dbconfig["user"] + " to " + dbconfig["database"] + "...")
        conn = MySQLConnection(**dbconfig)
        if conn.is_connected():
            print("Connection established.")
        else:
            print("Connection failed")

        cursor = conn.cursor(buffered=True)
        sql_command = "SELECT * FROM " + table
        print("Executed command: " + sql_command + ".")
        cursor.execute(sql_command)

        row = cursor.fetchone()
        return row

        # The fetchall method is similar but memory-consuming
        # rows = cursor.fetchall()
        # print('Total rows:', cursor.rowcount)
        # return rows

    except Error as e:
        print(e)
    finally:
        cursor.close()
        conn.close()

def mysql_fetchall():
	'''Query a MySQL database using fetchall()'''
	try:
		db_config = read_db_config()

		#Creating a new MySQLConnection object
		conn = MySQLConnection(**db_config)
		
		#Creating a new MySQLCursor object from the MySQLConnection object
		cursor = conn.cursor()

		#Selects all rows from the space table
		cursor.execute("SELECT * from space")

		#selects all the rows  in the cursor result set
		rows = cursor.fetchall()
		print('Number of rows: %d' % cursor.rowcount)
		
		#prints the rows out
		for row in rows:
			print(row)

	except Error as error:
		print(error)

	finally:
		cursor.close()
		conn.close()
		print('Connection closed.')

def mysql_fetchone():
	'''Query a MySQL database using fetchone()'''
	try:
		db_config = read_db_config()

		#Creating a new MySQLConnection object
		conn = MySQLConnection(**db_config)
		
		#Creating a new MySQLCursor object from the MySQLConnection object
		cursor = conn.cursor()

		#Selects all rows from the space table
		cursor.execute("SELECT * from space")

		#selects the next row in the cursor result set
		row = cursor.fetchone()
		
		#prints the row out and gets the next row
		while row is not None:
			print(row)
			row = cursor.fetchone()

	except Error as error:
		print(error)

	finally:
		cursor.close()
		conn.close()
		print('Connection closed.')

def ads():
    website = request.args.get('website', 'earthtravelers.com')
    user = app.config['DB_LOGIN']
    password = app.config['DB_PW']
    host = app.config['DB_HOST']
    database = app.config['DB_NAME']

    conn = MySQLConnection(user=user, password=password, host=host, database=database)
    conn.autocommit = True
    cursor = conn.cursor()
    args = (website,)
    try:
        cursor.callproc('AdMania.prc_GetAds', args)
    except Error as e:
        print e

    # In order to handle multiple result sets being returned from a database call,
    # mysql returns results as a list of lists.
    # Therefore, even if there is only one result set, you still have to get it from the list of lists.
    for result in cursor.stored_results():
        row_set = result.fetchall()

    result_set = []
    for row in row_set:
        result_set.append(row[0].decode().replace('##DOMAIN_ID##', '7782886'))

    cursor.close()
    conn.close()

    return render_template('T1.html',resultsSET=result_set)

def register_data(request):
    try:
        username = request.POST.get('username','')
        email = request.POST.get('email', '')
        phone = request.POST.get('phone', '')

        Error_dict = {}

        if(username == '' or email == "" or phone == "" ) :
            Error_dict['Invalid'] = "Enter Correct Values"

        else:
            dbconfig = {'password': 'root', 'host': 'localhost', 'user': 'root', 'database': 'login_database'}
            conn = MySQLConnection(**dbconfig)
            cursor = conn.cursor()
            cursor.execute("select COUNT(*) from client_info where  phone_no = " + str(phone) + " ")
            count = cursor.fetchone()
            count = count[0]
            if(count > 0):
                Error_dict['user_exist'] = "User Already Exist"
            else:
                cursor.execute("insert into client_info (Name , email, phone_no) values ('%s', '%s', '%s')" % (username, email, phone))
                conn.commit()
                return HttpResponseRedirect('register_success')
    except Error as e:
        print(e)

    return render_to_response('register_form.html',  Error_dict)

def login_data(request):
    try:
        username = request.POST.get('username','')
        phone = request.POST.get('phone', '')

        Error_dict = {}

        dbconfig = {'password': 'root', 'host': 'localhost', 'user': 'root', 'database': 'login_database'}
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        username = "'" + username + "'"
        cursor.execute("select COUNT(*) from client_info where Name = " + username + " AND phone_no = " + str(phone) + " ")
        count = cursor.fetchone()

        if(count[0] <= 0) :
            Error_dict['Wrong_values'] = "Wrong Username or Phone no"

        else :
            context_dict = {}
            context_dict['name'] = username
            return render_to_response('logged_in_user.html', context_dict)

    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()

    return render_to_response('login_form.html', Error_dict)

def login_page():
    error = ''
    gc.collect()
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cur = conn.cursor()
        if request.method == "POST":
            query = ("""SELECT * FROM users WHERE username = %s""")
            cur.execute(query, (request.form['username'],))
            userpw = cur.fetchone()[2]

            if sha256_crypt.verify(request.form['password'], userpw):
                session['logged_in'] = True
                session['username'] = request.form['username']
                session['user-ip'] = request.remote_addr
                if session['username'] == 'admin':
                    flash(Markup('The Dark Knight&nbsp;&nbsp;<span class="glyphicon glyphicon-knight"></span>'))
                else:
                    flash('Logged In')
                return redirect(url_for('blog'))
            else:
                error = "Invalid Credentials. Please try again."

        gc.collect()

        return render_template('login.html', error = error)

    except Exception as e:
        error = 'Invalid Credentials. Please try again.'
        return render_template('login.html', error = error)

def create_table(tbl_name_val):
    query = "CREATE TABLE IF NOT EXISTS %s ( " \
            "No int NOT NULL auto_increment," \
            "Site varchar(100)," \
            "Status varchar(30)," \
            "Email varchar(100)," \
            "Person varchar(100)," \
            "Phone varchar(25)," \
            "lockedby bigint,  " \
            "lockedat datetime," \
            "Trycount int default 0 , " \
            "Hostname varchar(50)," \
            "HFlag char(1) DEFAULT NULL," \
            "IP varchar(15) DEFAULT NULL," \
            "PRIMARY KEY (No)" \
            ") ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;" % tbl_name_val
    args = tbl_name_val

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query)
        print('New Table Created:' + str(tbl_name_val))

    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()

def dataDiri(user):
    pswd = getpass.getpass('Masukan password : ',stream = None)

    akses = "Anggota"
    alamat = raw_input("Masukan alamat valid : ")
    hp = input("Masukan nomor hp valid : ")    
    email = raw_input("Masukan email valid : ")
    saldo = 10000
    query = "INSERT INTO user (user,pswd,akses,saldo,alamat,hp,email)"\
            "VALUES (%s,%s,%s,%s,%s,%s,%s)"
    args = (user,pswd,akses,saldo,alamat,hp,email)

    try:
        dbConfig = bacaConfig()
        conn = MySQLConnection(**dbConfig)

        cursor = conn.cursor()
        cursor.execute(query,args)

        nilaiId = 100000
        if cursor.lastrowid:
            print('Sukses mendaftar')
        else:
            print("Last insert id not found")

        conn.commit()
        
    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close

def query_with_fetchone(Lista1,v2):
    try:
        conn = MySQLConnection(host=DB_HOST,user=DB_USER,password=DB_PASS,database=DB_NAME)
        cursor = conn.cursor()
        cursor.execute("SELECT nombre FROM clientes")

        row = cursor.fetchone()
        Lista1.delete(0,END)
        while row is not None:
            d=""
            for i in row:
                if i== "("or i==")"or i=="'"or i ==",":
                    pass
                else:
                    d = d+i
            Lista1.insert(END,d)
            row = cursor.fetchone()

    except Error as e:
        print(e)

    finally:
        Lista1.grid(column=1,row=2)
        update(v2)
        cursor.close()
        conn.close()

    def get_comments(self, article_id):
        try:
            conn = MySQLConnection(**self.db)

            query = "SELECT * FROM comment WHERE article_id=%s"

            cursor = conn.cursor()
            cursor.execute(query, (article_id,))

            list_of_comments = list()

            row_comment = cursor.fetchone()
            if row_comment is None:
                return None

            while row_comment is not None:
                list_of_comments.append(row_comment)
                row_comment = cursor.fetchone()
            return list_of_comments

        except Error as e:
            print(e)

        finally:
            cursor.close()
            conn.close()

def Connect():

    kwargs = ReadingMySQLConfig()
    MyConnection = MySQLConnection(**kwargs)
    try:
        if MyConnection.is_connected():
            print("Connected")
    except Error as e:
        print(e)
    finally:
        MyConnection.close()

def database_update():

    config=Config().config
    user = config['DB_LOGIN']
    password = config['DB_PW']
    host = config['DB_HOST']
    database = config['DB_NAME']
    cjauth = config['CJ_AUTH']
    cjurl = config['CJ_URL']

    conn = MySQLConnection(user=user, password=password, host=host, database=database)
    conn.autocommit = True
    cursor = conn.cursor()

    page_number = 0
    records_per_page = 100 # this is the max number allowed by the affiliate api per call.
    records_returned = records_per_page
    headers = {'authorization': cjauth}

    while records_returned == records_per_page:
        page_number += 1
        params = {'website-id': '7782886', 'link-type': 'banner', 'advertiser-ids': 'joined', 'page-number': page_number, 'records-per-page': records_per_page}

        result = requests.get(cjurl, headers=headers, params=params)
        result_xml = result.text

        root = ET.fromstring(result_xml.encode('utf8'))
        records_returned = int(root.find('links').get('records-returned'))

        for link in root.iter('link'):
            link_code_html = html.fromstring(link.find('link-code-html').text)
            height = int(link_code_html.xpath('//img/@height')[0])
            width = int(link_code_html.xpath('//img/@height')[0])

            mysql_args = (
                link.find('link-id').text,
                link.find('advertiser-id').text,
                link.find('advertiser-name').text,
                link.find('category').text,
                'None' if link.find('promotion-start-date').text == None else link.find('promotion-start-date').text,
                'None' if link.find('promotion-end-date').text == None else link.find('promotion-end-date').text,
                height,
                width,
                link.find('link-code-html').text)

            try:
                cursor.callproc('AdMania.prc_UpdateAd',mysql_args)

            except Error as e:
                print e

    cursor.close()
    conn.close()

def ReadImage(author_id, filename):
    kwargs = ReadingMySQLConfig()
    query = 'SELECT photo FROM authors WHERE id = %s'
    try:
        MyConnection = MySQLConnection(**kwargs)
        cursor = MyConnection.cursor()
        cursor.execute(query, (author_id,))
        photo = cursor.fetchone()[0]
        WriteFile(photo, filename)
    except Error as e:
        print(e)
    finally:
        MyConnection.close()

    def post(self):
        try:
            
            db_config = read_db_config()
            conn = MySQLConnection(**db_config)
            cursor = conn.cursor()
            cursor.callproc('sp_insertmember')

        except Error as e :
            return {'error': str(e)}
        finally:
            cursor.close()
            conn.close()

def create_database(cursor):
    try:
        conn = MySQLConnection(**db_data)
        cursor = conn.cursor()
        cursor.execute(
            "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))

    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()

def read_db():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM temperature;")
        row = cursor.fetchone()
        while row is not None:
            print(row)
            row = cursor.fetchone()
    finally:
        cursor.close()
        conn.close()

    def getAllUsers(self):

        try:
            conn = MySQLConnection(host='localhost', database='mydb', user='root', password='root')
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM users")
            rows = cursor.fetchall()
            return rows
        except Error as error:
            print error
        finally:
            cursor.close()
            conn.close()

def search():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM data_train")
        #row_train = cursor.fetchone()
        data_train =  list(cursor)
            
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM data_test")
        data_test = list(cursor)
        
        
        index1 = 0
        for x in range(len(data_train)) :
        
            canbera = 0
            index2 = 2
            
            for y in range(82):                
                temp1 = (data_test[0][index2] - data_train[index1][index2]) 
                temp2 = (data_test[0][index2] + data_train[index1][index2])

                #print "temp1 "+str(temp1)
                #print "temp2 "+str(temp2)
                                
                if temp2 == 0 :
                    hasil_temp = abs(temp1)
                else :
                    hasil_temp = float(abs(temp1)) / float(temp2)
                
                #print "hasil temp = " + str(hasil_temp)
                canbera = float(canbera) + float(hasil_temp)
                
                index2 += 1
            index1+=1
            
            print canbera
        
            
        #for x in range(len(gabungan)):
            #row_train[x]
        
 
    except Error as e:
        print(e)
 
    finally:
        cursor.close()
        conn.close()

def create_table(tables, db_data):
    try:
        dbconfig = db_data
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        for name, ddl in tables.iteritems():
            cursor.execute(ddl)

    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()