asked on
write to a mysql database
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
Any help on this?
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?
JavaMySQL ServerPHP
Last Comment
for_yan
This is the critical part of the above code:
String url = "jdbc:mysql://mysql03.ixwebingrnet";
String driver = "com.mysql.jdbc.Driver";
String userName = "C261605";
String password = "Wri";
//save.setUser("C261605");
// save.setPassword("Wri");
Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url+dbName,userName,password);
PreparedStatement pst = conn.prepareStatement("insrt into mytable values( ?,?,?)");
for (int i = 0; i < dataTest.numInstances(); i++) {
double pred = cls.classifyInstance(dataTest.instance(i));
System.out.print("ID: " + dataTest.instance(i).value(0));
pst.setDouble(1,dataTest.instance(i).value(0));
System.out.print(", actual: " + dataTest.classAttribute().value((int) dataTest.instance(i).classValue()));
pst.setString(2, dataTest.classAttribute().value((int) dataTest.instance(i).classValue()));
System.out.println(", predicted: " + dataTest.classAttribute().value((int) pred));
pst.setString(3,dataTest.classAttribute().value((int) pred);
pst.excute();
}
conn.close();
LLook at this example below.
You can either use Statement and direct insert as shown in the code below,
or use PreparedStatement as I showed above - those are basically equivalent things - PreparedStatement mey sometimes be faster
http://www.roseindia.net/jdbc/jdbc-mysql/InsertValues.shtml
You can either use Statement and direct insert as shown in the code below,
or use PreparedStatement as I showed above - those are basically equivalent things - PreparedStatement mey sometimes be faster
http://www.roseindia.net/jdbc/jdbc-mysql/InsertValues.shtml
import java.sql.*;
public class InsertValues{
public static void main(String[] args) {
System.out.println("Inserting values in Mysql database table!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbctutorial";
String driver = "com.mysql.jdbc.Driver";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db,"root","root");
try{
Statement st = con.createStatement();
int val = st.executeUpdate("INSERT employee VALUES("+13+","+"'Aman'"+")");
System.out.println("1 row affected");
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
If you plan to do this for some time you need to read through this tutorial:
http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
and understand some basic points about JDBC
http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
and understand some basic points about JDBC
ASKER
Hello. Thanks very much. In your example you wrote:
pst.setDouble(1,dataTest.i
I think the 1 refers to the first question mark in
PreparedStatement pst = conn.prepareStatement("ins
I think the 0 in .value(0) refers to the first column of the database.
Also, I guess you were just giving an example because in the data output I wrote there would not be any Doubles right?
I think all pst.set statements would be strings. Is this correct?
pst.setDouble(1,dataTest.i
I think the 1 refers to the first question mark in
PreparedStatement pst = conn.prepareStatement("ins
I think the 0 in .value(0) refers to the first column of the database.
Also, I guess you were just giving an example because in the data output I wrote there would not be any Doubles right?
I think all pst.set statements would be strings. Is this correct?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Very approximately it should be something like below:
You need first to create the table if you want to write all the time to the same table
- if you don't want to create table every time you run this program
Open in new window