Message Boards

WOLFRAM COMMUNITY

2949 Views

5 Replies

0 Total Likes

View groups...

Follow this post

Share this post:

GROUPS:

Accessing mySQL database using DatabaseReference[ ]?

S G

Posted 2 years ago

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

5 Replies

Sort By:

S G

Posted 2 years 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

Rohit Namjoshi

Posted 2 years ago

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"}]

POSTED BY: Rohit Namjoshi

S G

Posted 2 years 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!

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

Rohit Namjoshi

Posted 2 years ago

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 BY: Rohit Namjoshi

Rohit Namjoshi

Posted 2 years ago

Please provide the output you get and any error messages. The following fails doesn't seem to work which does not work!

POSTED BY: Rohit Namjoshi

Reply to this discussion

Reply Preview

Attachments

Remove Add a file to this post

Follow this discussion

or Discard

Group Abstract

Feedback