def test_copy_with_file(self):

        conn = vertica_python.connect(**conn_info)
        cur = conn.cursor()
        init_table(cur)
    
        conn2 = vertica_python.connect(**conn_info)
        cur2 = conn.cursor()
    
        f = tempfile.TemporaryFile()
        f.write("1,foo\n2,bar")
        # move rw pointer to top of file
        f.seek(0)
        cur.copy(""" COPY vertica_python_unit_test (a, b) from stdin DELIMITER ',' """,  f)
        f.close()
    
        # verify this cursor can see copy data
        cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
        res = cur.fetchall()
        assert 1 == len(res)
        assert 1 == res[0][0]
        assert 'foo' == res[0][1]
    
        # verify other cursor can see copy data
        cur2.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2")
        res = cur2.fetchall()
        assert 1 == len(res)
        assert 2 == res[0][0]
        assert 'bar' == res[0][1]

    def test_copy_with_string(self):

        conn = vertica_python.connect(**conn_info)
        cur = conn.cursor()
        init_table(cur)
    
        conn2 = vertica_python.connect(**conn_info)
        cur2 = conn.cursor()
    
        cur.copy(""" COPY vertica_python_unit_test (a, b) from stdin DELIMITER ',' """,  "1,foo\n2,bar")
        # no commit necessary for copy
    
        # verify this cursor can see copy data
        cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
        res = cur.fetchall()
        assert 1 == len(res)
        assert 1 == res[0][0]
        assert 'foo' == res[0][1]
    
        # verify other cursor can see copy data
        cur2.execute("SELECT a, b from vertica_python_unit_test WHERE a = 2")
        res = cur2.fetchall()
        assert 1 == len(res)
        assert 2 == res[0][0]
        assert 'bar' == res[0][1]

    def create_new_conn(self,datasourceName,is_autocommit = True):
        try:
            #Load Data source by name
            dsource = next((ds for ds in self.datasources if ds.name == datasourceName),None)

            #load params incase of the data source type
            newConn_param = self.get_direct_con_settings(datasourceName)

            #Decide which class to instantuate
            if dsource.databaseType.lower() == 'postgresql' and dsource.connectionType.lower() == 'direct':
                newConn = psycopg2.connect(host=newConn_param['server'],port=newConn_param['port'],database=newConn_param['database'],user=newConn_param['uid'],password=newConn_param['pwd'])
                newConn.autocommit(is_autocommit)
            if dsource.databaseType.lower() == 'redshift' and dsource.connectionType.lower() == 'direct':
                newConn = psycopg2.connect(host=newConn_param['server'],port=newConn_param['port'],database=newConn_param['database'],user=newConn_param['uid'],password=newConn_param['pwd'])
                newConn.autocommit(is_autocommit)
            if dsource.databaseType.lower() == 'vertica' and dsource.connectionType.lower() == 'direct':
                newConn = vertica_python.connect(host=newConn_param['server'],port=newConn_param['port'],database=newConn_param['database'],user=newConn_param['uid'],password=newConn_param['pwd'])
                newConn.autocommit(is_autocommit)
            if dsource.databaseType.lower() == 'mssql' and dsource.connectionType.lower() == 'direct':
                newConn = pymssql.connect(host=newConn_param['server'],port=newConn_param['port'],database=newConn_param['database'],user=newConn_param['uid'],password=newConn_param['pwd'])
                newConn.autocommit(is_autocommit)
            else:
                newConn = pyodbc.connect(dsource.connectionString,autocommit=is_autocommit)
        except:
            if newConn is None:
                print("Connection could not be made please check your configuration settings or that the server is alive")
                print(sys.exc_info())
        finally:
            return newConn

    def connect(self):
        import vertica_python

        return vertica_python.connect(host=self.host, port=self.port, database=self.database,
                                      user=self.user, password=self.password,
                                      read_timeout=self.read_timeout,
                                      unicode_error='replace')

    def fetch_from_vertica_to_df(self, data_set, query, block_size=100000):
        data_set_query = self.get_meta_data(data_set, 'query')
        if data_set_query != query:
            connection = connect(self.connection_details)
            cursor_remote = connection.cursor()
            print 'Executing ', data_set, 'Query...'
            print query
            columns = get_column_names_from_sql_query(query)
            self.create_table(data_set, columns)
            cursor_remote.execute(query)

            while True:
                rows = cursor_remote.fetchmany(block_size)
                rows = [tuple([str(ele) for ele in row]) for row in rows]
                self.insert_rows_to_table(data_set, columns, rows)
                if len(rows) < block_size:
                    break

            self.db.commit()
            cursor_remote.close()
            if self.get_number_of_rows_in_table(data_set) == 0:
                raise (ValueError('SQL result in empty fetch!!'))
            else:
                self.split_table_into_features(data_set)
                self.set_meta_data(data_set, 'query', query)
                self.set_meta_data(data_set, 'columns', ','.join(columns))
                self.set_meta_data(data_set, 'built_features', '')

    def test_query_errors(self):
        conn = vertica_python.connect(**conn_info)
        cur = conn.cursor()
        init_table(cur)
        
        failed = False;
        # create table syntax error
        try:
            failed = False;
            cur.execute("""CREATE TABLE vertica_python_unit_test_fail (
                            a int,
                            b varchar(32),,,
                            ) ;
                        """)
        except errors.VerticaSyntaxError:
            failed = True;
        assert True == failed
    
        # select table not found error
        try:
            failed = False;
            cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa') """)
            cur.execute(""" SELECT * from vertica_python_unit_test_fail  """)
            #conn.commit()
        except errors.QueryError:
            failed = True;
        assert True == failed

        # verify cursor still useable after errors
        cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
        res = cur.fetchall()
        assert 1 == len(res)
        assert 1 == res[0][0]
        assert 'aa' == res[0][1]

    def test_update(self):

        conn = vertica_python.connect(**conn_info)
        cur = conn.cursor()
        init_table(cur)
    
        cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (5, 'cc') """)

        # validate insert count
        res = cur.fetchone()
        assert 1 == len(res)
        assert 1 == res[0]

        conn.commit()
    
        cur.execute(""" UPDATE vertica_python_unit_test SET b = 'ff' WHERE a = 5 """)

        # validate update count
        assert cur.rowcount == -1
        res = cur.fetchone()
        assert 1 == len(res)
        assert 1 == res[0]

        conn.commit()
    
        cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 5")
        res = cur.fetchall()
        assert 1 == len(res)
        assert 5 == res[0][0]
        assert 'ff' == res[0][1]

    def test_iterator(self):

        with vertica_python.connect(**conn_info) as conn:
            cur = conn.cursor()
            init_table(cur)
        
            cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa') """)
            cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (2, 'bb') """)
            cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (3, 'cc') """)
            conn.commit()
        
            cur.execute("SELECT a, b from vertica_python_unit_test ORDER BY a ASC")
        
            i = 0;
            for row in cur.iterate():
                if i == 0:
                    assert 1 == row[0]
                    assert 'aa' == row[1]
                if i == 1:
                    assert 2 == row[0]
                    assert 'bb' == row[1]
                if i == 2:
                    assert 3 == row[0]
                    assert 'cc' == row[1]
                i = i + 1

    def fetch_from_vertica_to_df(self, data_set, query):
        data_set_query = self.get_data_set_attribute(data_set, 'query')
        if data_set_query != query:
            connection = connect(self.connection_details)
            cursor = connection.cursor()
            print 'Executing ', data_set, 'Query...'
            print query
            columns = get_column_names_from_sql_query(query)
            cursor.execute(query)

            data = []
            while True:
                rows = cursor.fetchmany(10000)
                data.extend([[str(ele) for ele in row] for row in rows])
                if len(rows) <= 1:
                    break

            df = MetadataDataFrame(data=data, columns=columns, meta_info={'query': query, 'built_features': [], 'aggregate_values': {},
                                                               'columns': columns})

            cursor.close()
            if len(df) == 0:
                raise(ValueError('SQL result in empty fetch!!'))
            else:
                self.set_data_set_attribute(data_set, 'data', df)
                self.set_data_set_attribute(data_set, 'query', query)
                self.set_data_set_attribute(data_set, 'columns', columns)
                self.set_data_set_attribute(data_set, 'built_features', [])

    def conn(cls):
        """
        return the vertica connection handle to the configured server
        """
        config = cls.config
        try:
            conn_driver_dict = {}
            conf_to_driver_map = {'host':'host',
                                  'username':'user',
                                  'password':'password',
                                  'revision_db_name':'database',
                                  'port':'port'}
            for conf_key, conf_value in config.iteritems():
                try:
                    driver_key = conf_to_driver_map[conf_key]
                    driver_value = conf_value

                    # NOTE: Vertica Python driver requires non-unicode strings
                    if isinstance(driver_value, unicode):
                        driver_value = str(driver_value)

                    conn_driver_dict[driver_key] = driver_value

                except KeyError:
                    pass

            conn = vertica_python.connect(conn_driver_dict)
        except Exception, e:
            raise DbError("Cannot connect to Vertica Db: %s\n"
                          "Ensure that the server is running and you can connect normally"
                          % e.message)

def get_data(query):
    cnx = vp.connect(**config)
    cursor = cnx.cursor('dict')
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    cnx.close

    return results

    def test_with_conn(self):

        with vertica_python.connect(**conn_info) as conn:
            cur = conn.cursor()
            init_table(cur)
        
            cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa'); commit; """)
            cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
            res = cur.fetchall()
            assert 1 == len(res)

    def test_empty_query(self):

        conn = vertica_python.connect(**conn_info)
        cur = conn.cursor()
        init_table(cur)

        cur.execute("")
        res = cur.fetchall()

        assert 0 == len(res)

    def query_and_fetchall(self, query):
        """
        Creates new connection to vertica, executes query, returns all fetched results. Closes connection.
        :param query: query to execute
        :return: all fetched results as returned by cursor.fetchall()
        """
        with vertica_python.connect(**conn_info) as conn:
            cur = conn.cursor()
            cur.execute(query)

            return cur.fetchall()

    def test_conn_commit(self):

        conn = vertica_python.connect(**conn_info)
        cur = conn.cursor()
        init_table(cur)
    
        cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (5, 'cc') """)
        conn.commit()
        cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 5")
        res = cur.fetchall()
        assert 1 == len(res)

    def db_conn(self):
       
        connection = connect({
                              'host': '198.202.90.32',
                              'port': 5433,
                              'user': 'dbadmin',
                              'password': '3Nathan$',
                              'database': 'wellderly'

                              })
        
        return connection

    def test_inline_commit(self):

        conn = vertica_python.connect(conn_info)
        cur = conn.cursor()
        init_table(cur)
        
        cur.execute(""" INSERT INTO vertica_python_unit_test (a, b) VALUES (1, 'aa'); commit; """)
        cur.execute("SELECT a, b from vertica_python_unit_test WHERE a = 1")
        res = cur.fetchall()
        assert 1 == len(res)
        assert 1 == res[0][0]
        assert 'aa' == res[0][1]

    def run_query(self, query, user):
        import vertica_python

        if query == "":
            json_data = None
            error = "Query is empty"
            return json_data, error

        connection = None
        try:
            conn_info = {
                'host': self.configuration.get('host', ''),
                'port': self.configuration.get('port', 5433),
                'user': self.configuration.get('user', ''),
                'password': self.configuration.get('password', ''),
                'database': self.configuration.get('database', ''),
                'read_timeout': self.configuration.get('read_timeout', 600)
            }
            
            if self.configuration.get('connection_timeout'):
                conn_info['connection_timeout'] = self.configuration.get('connection_timeout')

            connection = vertica_python.connect(**conn_info)
            cursor = connection.cursor()
            logger.debug("Vetica running query: %s", query)
            cursor.execute(query)

            # TODO - very similar to pg.py
            if cursor.description is not None:
                columns_data = [(i[0], i[1]) for i in cursor.description]

                rows = [dict(zip((c[0] for c in columns_data), row)) for row in cursor.fetchall()]
                columns = [{'name': col[0],
                            'friendly_name': col[0],
                            'type': types_map.get(col[1], None)} for col in columns_data]

                data = {'columns': columns, 'rows': rows}
                json_data = json_dumps(data)
                error = None
            else:
                json_data = None
                error = "No data was returned."

            cursor.close()
        except KeyboardInterrupt:
            error = "Query cancelled by user."
            json_data = None
        finally:
            if connection:
                connection.close()

        return json_data, error

    def __init__(self):
	self.host = "127.0.0.1" 
	if len(sys.argv) == 2:
        	self.port = sys.argv[1]
	else:
        	self.port = 5433
	
        self.user = "user"
        self.password = "password"
	self.database = ""

	self.conn = connect(host=self.host, port=int(self.port), user=self.user, password=self.password, database=self.database)
	self.cur = self.conn.cursor()

    def connect(self, autocommit=False):
        """
        Creates a connection to a Vertica database using the supplied credentials.

        :param autocommit: whether the connection should automatically commit.
        :type autocmommit: bool
        """

        # vertica-python 0.5.0 changes the code for connecting to databases to use kwargs instead of a dictionary.
        # The 'database' parameter is included for DBAPI reasons and does not actually affect the session.
        connection = vertica_python.connect(user=self.user, password=self.password, host=self.host, port=self.port,
                                            database="", autocommit=autocommit, read_timeout=self.read_timeout)
        return connection