Ejemplo n.º 1
0
    def executeDDL(self,  databaseKey, sql,isolation=0):
        """
        Executes a SQL(INSERT , DELETE etc.) through JDBC.

        Args:
            databaseKey :sample_order(e.g.)
            sql         :SQL

            isolation
                0:'' (default isolation)
                1:'READ UNCOMMITTED'
                2:'READ COMMITTED'
                3:'REPEATABLE READ'
                4:'SERIALIZABLE'

        Returns:
            result code

        Raises:
            Exception: databaseKey connection error
        """
        #get MySQL Information
        mysql_info  = self._readDatabaseSection(databaseKey)
        schema      = mysql_info['schema']  # item etc.
        host        = mysql_info['host']
        port        = mysql_info['port']
        username    = mysql_info['username']
        password    = mysql_info['password']

        url = 'jdbc:mysql://%s:%s/%s' % (host, port, schema)

        jdbc = None
        try:
            if isolation==0:
                isolation=''
            if isolation==1:
                isolation='READ UNCOMMITTED'
            if isolation==2:
                isolation='READ COMMITTED'
            if isolation==3:
                isolation='REPEATABLE READ'
            if isolation==4:
                isolation='SERIALIZABLE'

            jdbc = JDBC(url, 'com.mysql.jdbc.Driver', username, password,isolation)

            # Execute SQL
            result=jdbc.execute(sql)

        except Exception, msg:
            logging.error(msg)
            raise
Ejemplo n.º 2
0
    def hasTable(self, databaseKey, table_name, parameters=None):
        """
        Check table existence through JDBC.

        Args:
            databaseKey   : sample_order(e.g.)
            table_name    : table name
            parameters    : SQL statement parameters

        Returns:
            hasTable : True or False
        """
        #get MySQL Information
        mysql_info  = self._readDatabaseSection(databaseKey)
        schema      = mysql_info['schema']  # item etc.
        host        = mysql_info['host']
        port        = mysql_info['port']
        username    = mysql_info['username']
        password    = mysql_info['password']

        hasTable    = True
        url = 'jdbc:mysql://%s:%s/%s' % (host, port, schema)
        logging.debug('JDBC URL=%s' % url)
        # Parameters not included

        sql  = "SHOW TABLES FROM `%s` LIKE '%s'" %  (schema,table_name)
        logging.debug('SQL=%s' % sql)

        jdbc = JDBC(url, 'com.mysql.jdbc.Driver', username, password)

        # Execute SQL
        cursor = jdbc.execute(sql, parameters)

        record = cursor.fetchone()
        if record is None:
            hasTable   = False

        return hasTable
Ejemplo n.º 3
0
    def executeSql(self, databaseKey, sql, parameters=None, isolation=0,fetch=100):
        """
        Execute a query and return the result set

        Args:
            databaseKey    :sample_order(e.g.)

            isolation
                0:'' (default isolation)
                1:'READ UNCOMMITTED'
                2:'READ COMMITTED'
                3:'REPEATABLE READ'
                4:'SERIALIZABLE'

            fetchmany : The number of record to be fetched as one time

        Note
            Python will recognize DB value up to JDBC Option(tinyInt1isBit,zeroDateTimeBehavior)
            Value(-128 - 127) for TINYINT(1) will be recognized by Python as follows

            Value(0000-00-00 00:00:00) for Timestamp will be recognized by Python as follows
                zeroDateTimeBehavior=exception(default): SQL error
                zeroDateTimeBehavior=convertToNull     : None
                zeroDateTimeBehavior=round             : 0001-01-01 00:00:00
        """
        #get MySQL Information
        mysql_info  = self._readDatabaseSection(databaseKey)
        schema      = mysql_info['schema']  # item etc.
        host        = mysql_info['host']
        port        = mysql_info['port']
        username    = mysql_info['username']
        password    = mysql_info['password']

        other      = '?tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull'

        url = 'jdbc:mysql://%s:%s/%s%s' % (host, port, schema,other)
        logging.debug('JDBC URL=%s' % url)
        # Parameters not included
        logging.debug('SQL=%s' % sql)

        jdbc = None
        try:
            if isolation==0:
                isolation=''
            if isolation==1:
                isolation='READ UNCOMMITTED'
            if isolation==2:
                isolation='READ COMMITTED'
            if isolation==3:
                isolation='REPEATABLE READ'
            if isolation==4:
                isolation='SERIALIZABLE'

            jdbc = JDBC(url, 'com.mysql.jdbc.Driver', username, password,isolation)

            # Execute SQL
            cursor = jdbc.execute(sql, parameters)

            result_list = []

            #fetchmany start
            records = cursor.fetchmany(fetch)
            while records:
                for record in records:
                    result_list.append(record)
                records = cursor.fetchmany(fetch)
            #fetchmany end

        except Exception, msg:
            logging.error(msg)
            raise