troubleshooting Question

write to a mysql database

Avatar of onyourmark
onyourmark asked on
JavaPHPMySQL Server
6 Comments1 Solution711 ViewsLast Modified:
Hello

I was working with the code attached. It is writing to a database but there are two problems.
(1) it seems to want to create a new table and does not write to an existing table.
(2) the current code takes a csv file and writes it to a database. What I need to do is to write the code from line 134 to 138 to the database. The code in these lines create a list of three fields like this (this list has 14 rows and so I would be writing 14 entries to the database) :
ID: 0.0, actual: no, predicted: no
ID: 0.0, actual: no, predicted: no
ID: 1.0, actual: yes, predicted: yes
ID: 2.0, actual: yes, predicted: yes
ID: 2.0, actual: yes, predicted: yes
ID: 2.0, actual: no, predicted: no
ID: 1.0, actual: yes, predicted: yes
ID: 0.0, actual: no, predicted: no
ID: 0.0, actual: yes, predicted: yes
ID: 2.0, actual: yes, predicted: yes
ID: 0.0, actual: yes, predicted: yes
ID: 1.0, actual: yes, predicted: yes
ID: 1.0, actual: yes, predicted: yes
ID: 2.0, actual: no, predicted: no

import weka.core.Instance;
import weka.core.Instances;
import weka.core.converters.ArffLoader;
import weka.classifiers.bayes.NaiveBayesUpdateable;
 //next three are for writing to the database. Probably you don't need the others since you have '*'
 import weka.core.*;
  import weka.core.converters.*;
  import java.io.*;

import java.io.File;


import weka.classifiers.Evaluation;
import weka.classifiers.trees.J48;
import java.io.BufferedReader;
import java.io.FileReader;
import weka.core.converters.ConverterUtils.DataSource;
import weka.core.Instances;
import weka.experiment.InstanceQuery;

/**
* This example trains NaiveBayes incrementally on data obtained
* from the ArffLoader.
*
* @author FracPete (fracpete at waikato dot ac dot nz)
*/
public class JavaWekaJ48TestTrainPredReadWriteSQL { 


 /**
  * Expects an ARFF file as first argument (class attribute is assumed
  * to be the last attribute).
  *weather.nominalTest.arff
  * @param args        the commandline arguments
  * @throws Exception  if something goes wrong
  */
  
  public static void writeToDB(String string){


try{
	
       /*BufferedReader readerTest = new BufferedReader(
         new FileReader("C:/Program Files/Weka-3-7/data/weather.nominalTest.arff"));
        Instances dataTest = new Instances(readerTest);
        readerTest.close();
		
			DataSource sourceTest = new DataSource("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 Instances dataTest = sourceTest.getDataSet();	

 
	     //Instances data = new Instances(new BufferedReader(new FileReader(args[0])));
		 */
		 DataSource source = new DataSource(string);
 Instances data = source.getDataSet();	
 
      data.setClassIndex(data.numAttributes() - 1);
 
      DatabaseSaver save = new DatabaseSaver();
      save.setUrl("jdbc:mysql://mysql03.ixwebingrnet");
      save.setUser("C261605");
      save.setPassword("Wri");
      save.setInstances(data);
      save.setRelationForTableName(false);
      save.setTableName("weather");
      save.connectToDatabase();
      save.writeBatch();
   }
catch(Exception ex){

ex.printStackTrace();
}	
	
	}
 public static void main(String[] args) throws Exception {



     /*   BufferedReader readerTrain = new BufferedReader(
         new FileReader("C:/Program Files/Weka-3-7/data/weather.nominal.arff"));
        Instances dataTrain = new Instances(readerTrain);
        readerTrain.close();
	
//If csv do it this way:	
		DataSource sourceTrain = new DataSource("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 Instances dataTrain = sourceTrain.getDataSet();
 
 */
 
  InstanceQuery queryTR = new InstanceQuery();
 queryTR.setUsername("C261605");
 queryTR.setPassword("Re");
 queryTR.setQuery("select * from weatherSample");
 // You can declare that your data set is sparse
 // query.setSparseData(true);
 Instances dataTrain = queryTR.retrieveInstances();
		
        /*BufferedReader readerTest = new BufferedReader(
         new FileReader("C:/Program Files/Weka-3-7/data/weather.nominalTest.arff"));
        Instances dataTest = new Instances(readerTest);
        readerTest.close();
		
			DataSource sourceTest = new DataSource("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 Instances dataTest = sourceTest.getDataSet();	
*/

  InstanceQuery queryTS = new InstanceQuery();
 queryTS.setUsername("C261605");
 queryTS.setPassword("Re");
 queryTS.setQuery("select * from weatherSample");
 // You can declare that your data set is sparse
 // query.setSparseData(true);
 Instances dataTest = queryTS.retrieveInstances();

 if (dataTrain.classIndex() == -1)
dataTrain.setClassIndex(dataTrain.numAttributes() - 1);
if (dataTest.classIndex() == -1)
dataTest.setClassIndex(dataTest.numAttributes() - 1);


        J48 cls = new J48();
        cls.buildClassifier(dataTrain);




Evaluation eval = new Evaluation(dataTest);
eval.evaluateModel(cls, dataTest);
System.out.println(eval.toSummaryString("\nResults\n======\n", false));
        



 for (int i = 0; i < dataTest.numInstances(); i++) {
   double pred = cls.classifyInstance(dataTest.instance(i));
   System.out.print("ID: " + dataTest.instance(i).value(0));
   System.out.print(", actual: " + dataTest.classAttribute().value((int) dataTest.instance(i).classValue()));
   System.out.println(", predicted: " + dataTest.classAttribute().value((int) pred));
        } 


	JavaWekaJ48TestTrainPredReadWriteSQL.writeToDB("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 }
}

Any help on this?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros