Python sql.DriverManager类代码示例

def jdbc_test():
    """LJG: zxJDBC를 사용하면 한글처리가 에러나는데 JDBC를 직접 사용하면 한글처리가 성공한다
    추후 시간을 갖고 한글관련 문제를 처리하자.오늘은 여기까지 새벽 3시다.. 정말 한글이 싫다.ㅋㅋ
    """
    import sys
    import java
    from java.lang import Class
    from java.sql import DriverManager, SQLException
    
    jdbc_user = "root"
    jdbc_password = "cloud1004"
    jdbc_url = "jdbc:mysql://14.63.161.143/cdp"
    
    DriverManager.registerDriver(Class.forName("com.mysql.jdbc.Driver").newInstance())
    
    connection = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_password)
    
    statement = connection.createStatement()
    
    sql = "select * from test_sample"
    
    rs = statement.executeQuery(sql)
    
    while rs.next():
      row = rs.getString(2)
      print row

    def __init__(self, connectURI, username, password, pool_name):
        self.connectionPool = GenericObjectPool(None)
        self._pool_name = pool_name

        source = BasicDataSource()
        source.setUrl(connectURI)
        source.setUsername(username)
        source.setPassword(password)
        source.setInitialSize(1) # Number of connections to start with
        source.setMinIdle(5)     # Allow a bottom of 5 idle connections
        source.setMaxActive(10)  # Max of 10 database connection
        source.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED)
        source.setMinEvictableIdleTimeMillis(500) 

        self.connectionFactory = DataSourceConnectionFactory(source)

        #  Now we'll create the PoolableConnectionFactory, which wraps
        #  the "real" Connections created by the ConnectionFactory with
        #  the classes that implement the pooling functionality.
        self.poolableConnectionFactory = PoolableConnectionFactory(self.connectionFactory,
                self.connectionPool,
                None,
                None,
                False,
                True)

        #  Finally, we create the PoolingDriver itself...
        Class.forName("org.apache.commons.dbcp.PoolingDriver")
        driver = DriverManager.getDriver("jdbc:apache:commons:dbcp:")

        #  ...and register our pool with it.
        driver.registerPool(self._pool_name, self.connectionPool)

def connect_db(jdbc_url, driverName):
    try:
        Class.forName(driverName).newInstance()
        dbConn = DriverManager.getConnection(jdbc_url)
    except Exception, msg:
        print msg
        sys.exit(1)

    def get_profiles(self):
        head, tail = os.path.split(os.path.abspath(__file__)) 
        settings_db = os.path.join(head, "GUI_Settings.db3")
        try: 
            Class.forName("org.sqlite.JDBC").newInstance()
            dbConn = DriverManager.getConnection("jdbc:sqlite:%s"  % settings_db)
            self.Error_Message.setText("Database opened")
        except SQLException as e:
            self.Error_Message.setText("Error Opening Settings DB!")
 
        try:
           stmt = dbConn.createStatement()
           SQL_Statement = "select profile_name from profiles where volatility_version = '" + self.Version_CB.getSelectedItem() + "';" 
           resultSet = stmt.executeQuery(SQL_Statement)
           profile_list = []
           while resultSet.next():
              profile_list.append(resultSet.getString("profile_name"))

           stmt.close()
           dbConn.close()
           return profile_list
        except SQLException as e:
            self.Error_Message.setText("Error Reading plugins")
            stmt.close()
            dbConn.close()
            return "Error"

def _jdbc_connect_jython(jclassname, jars, libs, *args):
    if _converters is None:
        from java.sql import Types
        types = Types
        types_map = {}
        const_re = re.compile('[A-Z][A-Z_]*$')
        for i in dir(types):
            if const_re.match(i):
                types_map[i] = getattr(types, i)
        _init_converters(types_map)
    global _java_array_byte
    if _java_array_byte is None:
        import jarray
        def _java_array_byte(data):
            return jarray.array(data, 'b')
    # register driver for DriverManager
    jpackage = jclassname[:jclassname.rfind('.')]
    dclassname = jclassname[jclassname.rfind('.') + 1:]
    # print jpackage
    # print dclassname
    # print jpackage
    from java.lang import Class
    from java.lang import ClassNotFoundException
    try:
        Class.forName(jclassname).newInstance()
    except ClassNotFoundException:
        if not jars:
            raise
        _jython_set_classpath(jars)
        Class.forName(jclassname).newInstance()
    from java.sql import DriverManager
    return DriverManager.getConnection(*args)

    def shutDown(self):
        noDupes = list(set(md5))
        try:
            if(filename):
                
                uniquePath = os.path.join(Case.getCurrentCase().getCaseDirectory(), "NewLowHangingFruit.txt")
                uniqueFile = open(uniquePath,'w')
                
                dbConn = DriverManager.getConnection("jdbc:sqlite:%s"  % filename)
                stmt = dbConn.createStatement()

                for line in noDupes:
                    resultSet = stmt.executeQuery("SELECT * FROM MD5 where md5 == '%s'" % line)
                    if(resultSet.next()):
                        temp = "Future Improvement"
                    else:
                        uniqueFile.write(line+'\n')

                stmt.close()
                dbConn.close()
                uniqueFile.close()
        except:
            allPath = os.path.join(Case.getCurrentCase().getCaseDirectory(), "AllLowHangingFruit.txt")
            allFile = open(allPath,'w')
            for line in noDupes:    
                allFile.write(line+'\n')           
            allFile.close()

    def check_Database_entries(self):
        head, tail = os.path.split(os.path.abspath(__file__)) 
        settings_db = os.path.join(head, "GUI_Settings.db3")
        try: 
            Class.forName("org.sqlite.JDBC").newInstance()
            dbConn = DriverManager.getConnection("jdbc:sqlite:%s"  % settings_db)
        except SQLException as e:
            self.Error_Message.setText("Error Opening Settings DB!")
 
        try:
           stmt = dbConn.createStatement()
           SQL_Statement = 'Select Setting_Name, Setting_Value from settings;' 
           resultSet = stmt.executeQuery(SQL_Statement)
           while resultSet.next():
               if resultSet.getString("Setting_Name") == "Program_Exec_Name":
                   self.Program_Executable_TF.setText(resultSet.getString("Setting_Value"))
                   self.local_settings.setSetting('ExecFile', resultSet.getString("Setting_Value"))
                   self.local_settings.setSetting('Exec_Prog_Flag', 'true')
                   self.Exec_Program_CB.setSelected(True)
                   self.Program_Executable_TF.setEnabled(True)
                   self.Find_Program_Exec_BTN.setEnabled(True)
           self.Error_Message.setText("Settings Read successfully!")
        except SQLException as e:
            self.Error_Message.setText("Error Reading Settings Database")

        stmt.close()
        dbConn.close()

    def process(self, file):
        try:
            if(filename):
                
                dbConn = DriverManager.getConnection("jdbc:sqlite:%s"  % filename)
                stmt = dbConn.createStatement()

                path = file.getParentPath()+file.getName()

                resultSet = stmt.executeQuery("SELECT * FROM META WHERE Path == '%s'" % path)
                
                if(resultSet.next()):
                    temp = "Future Improvement"                
                else:
                    art = file.newArtifact(BlackboardArtifact.ARTIFACT_TYPE.TSK_INTERESTING_FILE_HIT)
                    att = BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SET_NAME.getTypeID(), 
                        MatchMetaInfoIngestModuleFactory.moduleName, "Unknown Meta")
                    art.addAttribute(att)
  
                    IngestServices.getInstance().fireModuleDataEvent(
                        ModuleDataEvent(MatchMetaInfoIngestModuleFactory.moduleName, 
                            BlackboardArtifact.ARTIFACT_TYPE.TSK_INTERESTING_FILE_HIT, None));

                stmt.close()
                dbConn.close()
                
        except:
            pass
    


        return IngestModule.ProcessResult.OK

def shrink_db(db_path, tables):
    conn = DriverManager.getConnection(db_url(db_path), "", "")
    for table in tables:
        logger.info("Shrinking table %s" % table)
        cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', '%s', 1)" % table)
        cs.execute()
        cs.close()
    conn.close()

 def __init__(self, global_config):
     BasePlugin.BasePlugin.__init__(self, global_config, self.required_config, self.plugin_scope)
     self.dbfile = self.global_config[self.plugin_name]['output_file']
     self.dburl = "jdbc:sqlite:" + self.dbfile
     dbdriver = "org.sqlite.JDBC"
     Class.forName(dbdriver)
     if (os.path.isfile(self.dbfile) == True):
         #use existing db schema
         self.logger.info("%s already exists, will be appending to database" % (self.dbfile))
         self.db = DriverManager.getConnection(self.dburl)
         stmt = self.db.createStatement()
     else:
         #create db file
         self.logger.info("creating db file %s" % (self.dbfile))
         self.db = DriverManager.getConnection(self.dburl)
         stmt = self.db.createStatement()
         stmt.executeUpdate('''CREATE TABLE IF NOT EXISTS "hiccuplog" (ref INTEGER, type BOOLEAN, url TEXT, headers TEXT, content BLOB)''')

    def __findGeoLocationsInDB(self, databasePath, abstractFile):
        if not databasePath:
            return

        try:
            Class.forName("org.sqlite.JDBC") #load JDBC driver
            connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath)
            statement = connection.createStatement()
        except (ClassNotFoundException) as ex:
            self._logger.log(Level.SEVERE, "Error loading JDBC driver", ex)
            self._logger.log(Level.SEVERE, traceback.format_exc())
            return
        except (SQLException) as ex:
            # Error connecting to SQL databse.
            return

        resultSet = None
        try:
            resultSet = statement.executeQuery("SELECT timestamp, latitude, longitude, accuracy FROM CachedPosition;")
            while resultSet.next():
                timestamp = Long.valueOf(resultSet.getString("timestamp")) / 1000
                latitude = Double.valueOf(resultSet.getString("latitude"))
                longitude = Double.valueOf(resultSet.getString("longitude"))

                attributes = ArrayList()
                artifact = abstractFile.newArtifact(BlackboardArtifact.ARTIFACT_TYPE.TSK_GPS_TRACKPOINT)
                attributes.add(BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_GEO_LATITUDE, general.MODULE_NAME, latitude))
                attributes.add(BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_GEO_LONGITUDE, general.MODULE_NAME, longitude))
                attributes.add(BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME, general.MODULE_NAME, timestamp))
                attributes.add(BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME, general.MODULE_NAME, "Browser Location History"))
                # artifact.addAttribute(BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_VALUE.getTypeID(),moduleName, accuracy))
                # NOTE: originally commented out

                artifact.addAttributes(attributes);
                try:
                    # index the artifact for keyword search
                    blackboard = Case.getCurrentCase().getServices().getBlackboard()
                    blackboard.indexArtifact(artifact)
                except Blackboard.BlackboardException as ex:
                    self._logger.log(Level.SEVERE, "Unable to index blackboard artifact " + str(artifact.getArtifactTypeName()), ex)
                    self._logger.log(Level.SEVERE, traceback.format_exc())
                    MessageNotifyUtil.Notify.error("Failed to index GPS trackpoint artifact for keyword search.", artifact.getDisplayName())

        except SQLException as ex:
            # Unable to execute browser location SQL query against database.
            pass
        except Exception as ex:
            self._logger.log(Level.SEVERE, "Error putting artifacts to blackboard", ex)
            self._logger.log(Level.SEVERE, traceback.format_exc())
        finally:
            try:
                if resultSet is not None:
                    resultSet.close()
                statement.close()
                connection.close()
            except Exception as ex:
                # Error closing database.
                pass

def connect(dburl="jdbc:mysql://localhost:3306/mtgo", dbuser="root", dbpass=None):
    try:
        try:
            Class.forName("com.mysql.jdbc.Driver").newInstance()
        except NameError:
            raise ErrorHandler("Internal MySQL error")
        conn = DriverManager.getConnection(dburl, dbuser, dbpass)
    except SQLException, error:
        raise ErrorHandler("MySQL error: %s" % str(error))

 def startUp(self, context):
     try:
         if(filename):
             global dbConn
             global stmt
             dbConn = DriverManager.getConnection("jdbc:sqlite:%s"  % filename)
             stmt = dbConn.createStatement()
     except:
         pass

def change_text(event):

        lq1 = "SYSIBM"
        aq1 = "%s.SQLSCHEMAS" % lq1
        aq2 = "%s.SQLTABLES" % lq1
        aq3 = "%s.SQLCOLUMNS" % lq1

        aq4 = "select * from qsys2/systables select * from qsys2/syscolumns select * from qsys2/sysindexes"

        DriverManager.registerDriver(AS400JDBCDriver())
        logger.debug("Connecting...")

        server = "AS400XO.noceast.dws.disney.com"
        lq2 = "DVDSFIL"
        tq1 = "%s.CTCMPF" % lq2

        con = DriverManager.getConnection("jdbc:as400://%s/apilib;naming=sql;errors=full;date format=iso;extended dynamic=true;package=JDBCExa;package library=apilib" % server,
                                           name, pwd)

        logger.debug("Established Connection with: %s" % server)

        query = "SELECT TABLE_NAME, TABLE_TEXT FROM %s where TABLE_SCHEM = '%s'" % (aq2, lq2)

        #query = "SELECT COLUMN_NAME, COLUMN_TEXT, TYPE_NAME FROM %s where TABLE_SCHEM = '%s'" % (aq3, lq2)

        logger.debug("Query: %s" % query)

        stmnt = con.prepareStatement(query)

        results = stmnt.executeQuery()

        i = 0
        while results.next():
            i += 1
            #logger.debug("%s" % results)

            logger.debug("%s : %s" % (results.getString("TABLE_NAME"), results.getString("TABLE_TEXT")))

            #logger.debug("%s : %s [%s]" % (results.getString("COLUMN_NAME"), results.getString("COLUMN_TEXT"), results.getString("TYPE_NAME")))

            if i == 1000:
                exit()

        logger.debug("Success!")

 def dbOpen(self):
     self.database = "CustomMsg.db"
     driver = "org.sqlite.JDBC"
     self.url = "jdbc:sqlite:" + self.database
     Class.forName(driver)
     try:
         self.db = DriverManager.getConnection(self.url);
         self.stat = self.db.createStatement()
         self.stat.executeUpdate("CREATE TABLE IF NOT EXISTS " + self.TABLENAME + " ("+ self.HOST + " TEXT PRIMARY KEY, "+  self.MSG + " TEXT);");
     except SQLException, e:
         self.requestAdd("ERROR")