Connect to MySQL database using Stata

Today the Stata Corp. blog outlined a new feature introduced to Stata 11.1 that allows one to connect to an ODBC database without setting up a DSN. This is a nice addition and simplifies the process of loading ODBC data for those one-off projects.

The blog post explains how to connect to database running on Microsoft’s SQL server. Below I’ll describe the process using a database on the open-source MySQL platform.

Step 0: Download/Install the MySQL ODBC Driver
If you haven’t done this already, you must install an ODBC driver to connect to MySQL servers. You can download the latest version of the MySQL ODBC driver and install it by following the prompts.

Step 1: Load data from your MySQL database using Stata
The following example code will connect to a MySQL database (worldstats) running on my local machine (localhost) and extract a unique ID and name using a SQL statement (‘sql’) from a table of country attributes (countries). This database requires a username (UID=andrew) and password (PWD=pass) but these may not be needed depending on how the server you are connecting to is configured.

local db "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=worldstats;UID=andrew;PWD=pass;"
local sql "SELECT ID, CountryName FROM countries"

odbc load, exec("`sql'") conn("`db'") clear

For more info about using -odbc- in Stata search the help files by typing “help odbc”. Good luck!

Comments on this article can be sent to me by e-mail..

3 Responses to “Connect to MySQL database using Stata”

  1. Stata-MySQL a first encounter « Stata Daily writes:

    […] to Andrew Dyck’s post Connect to MySQL database using Stata. Step 0 and Step 1 is exactly the second half of what I needed to do. But, as I had no idea how SQL […]

  2. Jay Dev writes:

    I have a data base name “schoolreport_data.MDF” (17 GB of size) with an another file “schoolreport_LOG.LDF”. I have no idea how to proceed. Is it a SQL SERVER database ? can above technique help me to extract those data? Please help.

  3. Andrew writes:

    I believe you are right about that file being used by SQL server. Unfortunately I’m unable to give you much help besides recommending that you ask whomever sent you the file to do a DB extraction and send you the data as a txt file, or more likely, a set of txt files for each table in the DB. Good luck!