Message Boards Message Boards

GROUPS:

Accessing mySQL database using DatabaseReference[ ]?

Posted 9 days ago
221 Views
|
5 Replies
|
0 Total Likes
|

Hi All,

I'm struggling to understand the best way to access a mySQL database. I would like to execute SQL commands but just cant seem to get it to work. I use the following approach via entitystore;

connectionMYSQL = 
 DatabaseReference[{"Backend" -> "mysql", "Port" -> 3306, 
   "Host" -> "localhost", "Username" -> "root", "Password" -> "", 
   "Name" -> "socialdata"}]
rdb = RelationalDatabase@connectionMYSQL
es = EntityStore@rdb

I can see the tables in EntityStore but trying to get data via EntityValue doesn't seem to work. The following fails:

EntityValue["table1", {"col1", "col2....

The other way of doing this is:

conn = DatabaseReference[
  URL["mysql://root@localhost:3306/socialdata"]]
SQLExecute[conn, "SELECT * FROM .... 

which does not work!

Ideally I would like to execute SQL commands. are there any good examples showing this working (especially for a local database)?

POSTED BY: S G
Answer
5 Replies

Please provide the output you get and any error messages.

The following fails

doesn't seem to work

which does not work!

Posted 7 days ago
SQLExecute[DatabaseReference[
Association[
  "Backend" -> "mysql", "Name" -> "socialdata", "Port" -> 3306, 
   "Host" -> "localhost", "Username" -> "root", 
   "Password" -> ""]], "SELECT * FROM `table1` WHERE 1 LIMIT 10 ", 
 "ShowColumnHeadings" -> True]

The output is as above. There dont seem to be any error messages. The output seems to just repeat the previous command. Apologies, I'm sure this is something very simple, I'm just starting to get into Mathematica!

POSTED BY: S G
Answer

Hi S G

SQLExecute is defined in the DatabaseLink package so you have to load it first

Needs["DatabaseLink`"]

You need to open a connection using OpenSQLConnection and pass that to SQLExecute. Check the DatabaseLink User Guide for details.

Posted 5 days ago

Thanks for that. Adding the final fix as it might be useful for others trying to do sql queries on a local mySQL database.

The solution was to run the following;

Needs["DatabaseLink`"]
OpenSQLConnection[]

This opens up a database connection tool. Using this tool, I was able to insert the settings for the database and add it to the connections in the "connection tool". I called the database "SocialData" in the connection tool. Once the database had been added in the connections tool I could then run;

Needs["DatabaseLink`"]
SQLExecute[
 OpenSQLConnection[
  "SocialData"], "SELECT * FROM `table1` WHERE \
`test`='abc'  LIMIT 10 "]

This is working nicely now. Many thanks.

POSTED BY: S G
Answer

Glad you got it working. One word of caution. The DB connection tool is handy, however, it stores the connection information locally so if you want to share your notebook with someone else on your team they will not be able to use that connection information to connect to the database. Since your DB is hosted on localhost that may not be an issue for you.

You should see a file named SocialData.m here. You can copy the connection information from there into your notebook if needed.

FileNames["*.m", FileNameJoin@{$UserBaseDirectory, "DatabaseResources"}]
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract