7

How can I fetch data from MySQL database in Julia?

It looks like this package provides tools for connecting to any database, including MySQL. However, it looks like assuming I have to first setup ODBC DSN to connect to it (I don't know what it is in the first place, although I've used MySQL for two years). So I tried to configure it by reading this page at MySQL documentation... but hit the wall immediately.

One, I can't find a command named myodbc-installer in my OS X 10.8.5. Two, there is no such application called ODBC Administrator, and instead there is ODBC manager in my system - So I think that documentation is too outdated... right?

Also, even when I opened ODBC manager and tried to click on Add on User DSN tab, there are no driver detected on the screen. So how can I configure ODBC DSN and make use of MySQL in Julia?

Also, do I have to deal with such a tedious setup process? I'd like to use a package, if any, that is as intuitive and easy to use as RMySQL package in R, which doesn't force me to care about DSN - any such package in Julia?

I'm on 0.2.0-rc in Julia and already installed the package through Pkg.add("ODBC"). MySQL version is 5.3.6 and installed via MAMP.

Thanks.

Blaszard
  • 30,954
  • 51
  • 153
  • 233

1 Answers1

10

Jacob Quinn here, package maintainer for the ODBC package for Julia.

A few things to help you understand how the process works:

-ODBC is an API middle layer originally developed by Microsoft to create a common interface between DB systems and applications. This was useful because with the number of different DB systems, it would be hard to have applications that could reliably connect to any DB.

-ODBC has been ported to Linux/Unix/OSX systems through 2 main projects: iodbc (mainly OSX) and unixODBC (Linux)

-The basic components of the API are the ODBC Manager, which is the middle layer, the DB system, the application, and the ODBC driver, which is DB-specific and actually implements the communication between application-ODBC Manager and ODBC Manager-DB system.

-In the case of RMySQL, only the MySQL driver has been implemented with R wrapper functions which allow the connection to MySQL DB systems

-In the case of ODBC.jl (and the corresponding RODBC for R), a more generalized approach is taken where wrapper functions of the ODBC Manager are provided, which allow connection with any DB system, provided the user has the correct DB driver installed and connection string

Hopefully that helps understand the ODBC process a little better.

So for your case, it looks like you don't have the MySQL driver installed since it didn't show up in your ODBC Manager. You can find the driver here.

Once you have the driver installed, it should be pretty simple to set up a DSN (following either MySQL documentation or the excellent connectionstrings.com, MySQL Section).

You should then be able to start using ODBC in julia:

Pkg.add("ODBC")
using ODBC
ODBC.connect(dsn)
query("select * from customers")

If you run into any other issues or troubles getting things setup or installed, feel free to open an issue here and I'm more than willing to help troubleshoot the setup to get you going.

quinnj
  • 1,228
  • 9
  • 8
  • Thanks a lot for the great explanation. I think I installed the required driver successfully, but when I tried to run the commands you wrote in above, I still got an error `[ODBC] 08S01: [MySQL][ODBC 5.2(w) Driver]Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)`. I'm sure this happened because I originally installed and used MySQL via MAMP, where the default path of the socket is `/Applications/MAMP/tmp/mysql/mysql.sock`, instead of `/tmp/mysql.sock`. However, I don't understand where and how I can tell the correct socket path to the driver or function... – Blaszard Oct 18 '13 at 19:05
  • You might be missing a [client] section in your /etc/my.cnf. Add (or modify) the socket key key under that section with something like "socket=/var/lib/mysql/mysql.sock". HTH. – EdwardG Oct 18 '13 at 21:45
  • I already modified that section as `socket = /Applications/MAMP/tmp/mysql/mysql.sock` in `/Applications/MAMP/conf/my.cnf`, but isn't it sufficient? Or do I have to create `/etc/my.cnf`? (which I don't have right now) – Blaszard Oct 18 '13 at 21:51
  • I setup keyward-value pairs in my dsn on ODBC Manager, and now I see everything working well in Julia. Thanks. – Blaszard Oct 21 '13 at 19:53
  • For people reading this question in 2021, there is now the specific [MySQL.jl](https://github.com/JuliaDatabases/MySQL.jl) package that perhaps is simpler to install/use.. – Antonello May 07 '21 at 09:58