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
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
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