connect weka to the mysql database

[jackkk]

Hi,
can anybody please tell me how to connect weka to the mysql database?
I read the chapter 13 of wekamanual but I cannot connect the explorer of weka to the my database.
I cannot change the files DatabaseUtils.probs in weka.jar.

Please help!
Thanks

[Mark]

Hi,

You will need to unpack the weka-src.jar file (you can use any tool for zip archives to do this). In the weka/experiment directory you will find a "DatabaseUtils.props.mysql" file. This needs to be renamed to "DatabaseUtils.props" and placed into your home directory (Weka 3.6) or into wekafiles/props in your home directory (Weka >= 3.7.2). In this props file you should only need to edit the database URL line in order to point to your MySQL server.

Cheers,
Mark.

[jackkk]

thanks.....
I renamed the file and I put it in directory c: \program files\weka-3-6.
When I want to connect the explorer to the my database I have the error in the box info of sql viewer of weka.
This is what appears to me:

connecting to:jdbc:mysql://127.0.0.1:3306/mydatabase = false
exception: java.sql.SQLException: No suitable driver found for jdbc:mysql://127.0.0.1:3306/mydatabase

[Mark]

Hi,

c: \program files\weka-3-6 is the installation directory for Weka (visible to all users). Weka 3.6 will look in your home directory (as defined by the java property user.home) so that different users can have different setups for database connectivity. See the following article for where the home directory is for various OS's:

http://en.wikipedia.org/wiki/Home_directory

Cheers,
Mark.

[jackkk]

Hi,
I found my home directory with the command %UserProfile% in the prompt of command.
my home directory is: c: \users\user1.
I have to unpack the weka-src.jar file with winrar and I renamed the file DatabaseUtils.props.mysql in DatabaseUtils.props and I put it in directory c: \users\user1.
I have always the same error in the box info of sql viewer of weka:
connecting to:jdbc:mysql://127.0.0.1:3306/mydatabase = false
exception: java.sql.SQLException: No suitable driver found for jdbc:mysql://127.0.0.1:3306/mydatabase

[Mark]

You will also need the JDBC driver for MySQL - download this from the MySQL site. This jar file will need to be present in the classpath for Weka, so store the driver somewhere (it can go in the Weka installation directory if you wish) and then edit the RunWeka.ini file. At the bottom of this file you will see an example of how to make additional jar files available to Weka.

Cheers,
Mark.

[jackkk]

Thank you.
I had already downloaded the mysql driver............
Now I've edited the file RunWeka.ini as well as in the example at the bottom.
I have always the same error
connecting to:jdbc:mysql://127.0.0.1:3306/mydatabase = false
exception: java.sql.SQLException: No suitable driver found for jdbc:mysql://127.0.0.1:3306/mydatabase

[Mark]

Can you start the GUIChooser and go to "Systeminfo" under the "Help" menu and check your classpath (java.class.path)? Is the mysql driver jar file present?

Cheers,
Mark.

[jackkk]

Hi,
Thanks for the quick answer.
I clicked "SystemInfo" under "help" from the GUI chooser.
corresponding to java.class is:
./weka.jar;%CLASSPATH%
If I setting CLASSPATH variable as like at page 278 I have corresponding to java.class:
./weka.jar;./mysql-connector-java-5.1.17-bin.jar
because the driver JDBC are in the folder of weka.
But I have always the same error:
connecting to:jdbc:mysql://127.0.0.1:3306/mydatabase = false
exception: java.sql.SQLException: No suitable driver found for jdbc:mysql://127.0.0.1:3306/mydatabase

[Mark]

Your classpath is still not set correctly. If the mysql-connector-java-5.1.17-bin.jar does not appear in the java.class.path property under Systeminfo then it will not find the driver. I've just tested Weka 3.6.5 with the latest driver (mysql-connector-java-5.1.18-bin.jar) under Windows 7 and it works fine. I copied the driver jar file to the Weka installation directory and edited the last line of the RunWeka.ini file to read:

cp=%CLASSPATH%;./mysql-connector-java-5.1.18-bin.jar

Cheers,
Mark.

[jackkk]

thank you very much Mark.

[gauravraj]

Hi....

I tried to connect weka to database using above suggestions given by you. But i got an error:

exception: java.sql.SQLException: Access denied for user ''@'localhost' (using password: NO)

Is there is any way to give password in DatabaseUtils.props file itself?

Thanks.

[gauravraj]

Hey,

Can i use command line to connect weka to database and apply different algorithms on it to get desired result? Please tell in detail.

[Mark]

Not directly. You can use weka.experiment.InstanceQuery to execute an SQL query to extract rows from a database to an ARFF file. Then you can apply a classifier to this as normal from the command line.

Cheers,
Mark.

[gauravraj]

Hi,
I am doing a project. In this i will given a website having many users. I can even access database to get their profile details and activities done by them. Use data mining algorithm/tool, I have to suggest a friend to the user in which he may be interested. Can you please tell me which all algorithm of Weka will be useful for this purpose.

Thanks,
Gaurav

[Mark]

Probably association rule learning would be suitable for this sort of thing. That could be used to find other activities to suggest for a user. It could perhaps be combined with a nearest neighbor approach using profile details and activities to find similar users.

Cheers,
Mark.

[gauravraj]

I am trying an example in Weka (attached in this post). It consist of 5 attributes gender(nominal), age(integer), state(nominal), college(nominal) and friend(nominal). When i am trying to use apriori algorithm, I am unable to do so as i am not getting that option. Can you please tell me why it's happening so? Do i to change anything in my example?

Thanks.relation.pdf

[gauravraj]

Hi Mark,
In the previous problem, I am trying to solve it by using SMO algorithm. Here i am getting number of kernel evaluations and percentage cached in output. Can these be used to predict a friend for the user? Is there any algorithm using which can be used to filter the desired output from the complex output given by the SMO algorithm.
Once again thanks in advance.
Gaurav

[Mark]

Hi,

Your example data seems to be a bit different from the original problem you described - there are no "activities" listed. The data looks like a straight classification problem, where the boolean "friend" attribute is the target. You can use just about any of Weka's classifiers for predicting this (although I'm not too sure how much predictive potential the input attributes have). If you wan't to get predictions (in the form of a predicted class label or probability distribution) for each test instance in a data set, then turn on the output predictions option (under "More options") in the Classify panel of the Explorer.

Cheers,
Mark.

[gauravraj]

Hi,
The example which i sent to you, is just an example for my prototype model. In actual case, i have to consider recent activities too.
Now, i am trying to use two algorithms classify(decision tree or SMO) and clustering( simple k-means) as you said earlier. But i am not able to do so. Is it possible to use these algorithms together? As the output given by classifying algorithm(decision tree), can't be given as input to clustering(simple k-means) algorithm. I am using command line to access Weka algorithm. Can you please give me some suggestions for implementing these two algorithms together.
Thanks,
Gaurav

[gauravraj]

Hi Mark,

I wanted to know, how can we use both the algorithms classify and clustering successively which is required in my case.

Thanks in advance,
Gaurav

[Mark]

weka.filters.supervised.attribute.AddClassification can be used to add the predictions (labels or probability distributions) to an input data set as new attributes. You can then apply a clusterer to this so that the clusters take into account the predictions of the classifier.

Cheers,
Mark.

[gauravraj]

Hi Mark,

I guess weka.filters.supervised.attribute.AddClassification can't be used in my case as it is predicting a friend for user who is already there in his friend list. I think it can be used to find out a best friend for a user but it can not suggest a new friend to that user.

Is there any other method which can be used in my case?

Thanks,
Gaurav.

[Sanmi]

Hi Mark ,

i have done all you said previously but i get this error :
com.mysql.jdbc.exceptions.jdbc4.communicationsexception communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server

Please help!

[Mark]

I don't think this is a Weka problem, but rather some network issue. Perhaps you have a firewall or proxy in the way or something?

Cheers,
Mark.