Querying MySQL from Stata

Step 0: Install the ODBC driver

  1. Download and install the iODBC driver manager. The next step will fail if this isn't installed first.
  2. Download and install the ODBC driver.

Step 1: Set up ODBC driver

See these instructions. My ODBC configuration (~/.odbc.ini) looks like:

[ODBC Data Sources]
mysql = MySQL ODBC 5.3 ANSI Driver

[ODBC]
Trace         = 0
TraceAutoStop = 0
TraceFile     = 
TraceLibrary  = 

[mysql]
Driver      = /usr/local/lib/libmyodbc5a.so
Description = desc here
SERVER      = localhost
PORT        = 3306
USER        = root
PASSWORD    = passwordhere
DATABASE    = databasehere

Make sure your driver location is correct. It could also be something like /usr/local/mysql-connector-odbc-5.3.7-macos10.12-x86-64bit/lib/libmyodbc5a.so.

Step 2: Make a ODBC call from Stata

For example:

clear
set odbcdriver ansi
odbc list
// Option 1
odbc load, table("tablename") dsn("mysql") clear noquote
// Option 2
odbc load, exec("select * from tablename") dsn("mysql") clear noquote

Using a plugin is another option, but I haven't tried this.

Update: Loading in SQL from an external file

/*
Copyright 2017 Max Masnick
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
*/
cap drop program loadsql
program define loadsql
syntax using, dsn(string)
file open myfile `using', read
file read myfile line
local sql "`line'"
while r(eof) == 0 {
file read myfile line
local line=regexr(`"`line'"', "--.*", "")
local sql "`sql' `line'"
}
file close myfile
clear
set odbcdriver ansi
qui odbc list
odbc load, exec(`"`sql'"') dsn("`dsn'") clear noquote
end
view raw loadsql.ado hosted with ❤ by GitHub

This allows you to do something like:

cd "/path/to/your/folder"
clear
loadsql using your-sql.sql, dsn(mysql)

Literate Python setup with pweave and Atom

In the past I’ve used IPython/Jupyter notebooks for literate programming, but writing code in the browser is not a great experience and I was having terrible environment issues. I started looking at alternatives, and settled on trying out pweave. It’s the same idea as IPython, allowing me to intersperse code and output with prose, but rather than using the browser I can use a standard programming editor. (It also solves my environment problem.)

The setup for getting a pweave workflow that still has all the nice parts of an IPython notebook is a little involved, so here’s what I did:

  • Get the Atom editor (I generally use Sublime Text, but it can’t run Python inline)

  • Set up a virtualenv and pip install "ipython[notebook]" Pweave

  • Install the following Atom packages:
  • Make the following configuration changes:
    • Append to styles.less:

      // Hydrogen output - font size is too small  
      .hydrogen.output-bubble pre {  
        font-size: 16px !important;  
      }
      // Hydrogen - hack for 2x images  
      .hydrogen.output-bubble .bubble-result-container img {  
        width: 50% !important  
      }
      
    • Append to config.cson:

      ".md.pweave.source":  
        "expand-region":  
          commands: [  
            {
              command: "expand-region:select-inside-back-ticks"  
              recursive: true  
            }
          ]
      
    • Append to keymap.cson:

      '.platform-darwin atom-text-editor':  
        'cmd-enter': 'hydrogen:run',  
        'cmd-shift-space': 'expand-region:expand'
      
    • In the settings under Settings > Packages > Hydrogen:
      • Kernel Mappings (this enables support for .pmd files): {"pweave markdown": "Python 2"}

      • Startup Code:

        {"Python 2": "import matplotlib as matplotlib_import_only\nmatplotlib_import_only.use('Agg')\n%matplotlib inline\n%config InlineBackend.figure_format = 'retina'\npython=None"}
        

        This does a few hacky things:

        • import matplotlib as matplotlib_im ... rt_only.use('Agg'): fix for this issue with a bouncing rocketship dock icon
        • %matplotlib inline: display figures inside Atom rather than in a separate window
        • %config InlineBackend.figure_format = 'retina': turns on high-resolution figures, which are displayed by Atom as double-sized low-resolution figures without the hack in styles.less above
        • python=None: a hack to avoid an error when automatically selecting and running a block of Python code

Some of this configuration is necessary to just get hydrogen to run Python code.

The rest fixes a major problem with pweave: it’s easy to run one line of code, and it’s easy to run all the code in a file, but how do I run only one block of code? (This is equivalent to being able to running an entire cell in IPython with shift-enter.)

The solution is to use the expand-region plugin to select the current block of code, and the python=None hack to avoid an error when it selects the “python” in ```python.

Here’s what it looks like in action:


To convert the .pmd file to a .html that can be shared, simply run pweave -f md2html test.pmd. Here's what the output from the above example looks like.


Notes

  • Remember to open Atom from the command line after activating your virtualenv (see the "Usage" section here).
  • In order to get the pweave command to run with my virtualenv, I manually edited the shebang in /usr/local/bin/pweave to directly point to the Python binary in my virtualenv: #!/Users/max/.virtualenvs/data/bin/python

Disable "mirror screen" keyboard shortcut (Command+F1 or Command+Brightness down)

Thanks to this Ask Different post and Karabiner, I've been able to disable the very annoying "mirror screen" keyboard shortcut.

<?xml version="1.0"?>
<root>
<item>
<name>Disable Command-F1</name>
<identifier>private.disable.command.f1</identifier>
<autogen>--KeyToKey-- ConsumerKeyCode::BRIGHTNESS_DOWN, ModifierFlag::COMMAND_L, ConsumerKeyCode::BRIGHTNESS_DOWN</autogen>
<autogen>--KeyToKey-- ConsumerKeyCode::BRIGHTNESS_DOWN, ModifierFlag::COMMAND_R, ConsumerKeyCode::BRIGHTNESS_DOWN</autogen>
</item>
</root>
view raw private.xml hosted with ❤ by GitHub

Keyboard Maestro: Convert Markdown to JIRA Markup

JIRA uses its own plain-text markup syntax, which I don't like as much as Markdown.

Fokke Zandbergen has a great online service for converting between JIRA markup and Markdown, but it's too cumbersome to use this when you are living in JIRA comments.

I re-appropriated Fokke's JavaScript into a Keyboard Maestro macro:

This will take the Markdown in the clipboard and paste it as JIRA markup whenever you type ";jira".

You can download the macro here.

Update – May 26, 2016: I have added support for multi-level unordered lists – the updated code is below.

// https://github.com/FokkeZB/J2M/blob/master/script.js
function toJ(converted) {
converted = converted.replace(/^(.*?)\n([=-])+$/gm, function (match,content,level) {
return 'h' + (level[0] === '=' ? 1 : 2) + '. ' + content;
});
converted = converted.replace(/^([#]+)(.*?)$/gm, function (match,level,content) {
return 'h' + level.length + '.' + content;
});
converted = converted.replace(/([*_]+)(.*?)\1/g, function (match,wrapper,content) {
var to = (wrapper.length === 1) ? '_' : '*';
return to + content + to;
});
// Make multi-level bulleted lists work
converted = converted.replace(/^(\s*)- (.*)$/gm, function (match,level,content) {
var len = 2;
if(level.length > 0) {
len = parseInt(level.length/4.0) + 2;
}
return Array(len).join("-") + ' ' + content;
});
var map = {
cite: '??',
del: '-',
ins: '+',
sup: '^',
sub: '~'
};
converted = converted.replace(new RegExp('<(' + Object.keys(map).join('|') + ')>(.*?)<\/\\1>', 'g'), function (match,from,content) {
console.log(from);
var to = map[from];
return to + content + to;
});
converted = converted.replace(/~~(.*?)~~/g, '-$1-');
converted = converted.replace(/`{3,}(\w+)?((?:\n|[^`])+)`{3,}/g, function(match, synt, content) {
var code = '{code';
if (synt) {
code += ':' + synt;
}
code += '}' + content + '{code}';
return code;
});
converted = converted.replace(/`([^`]+)`/g, '{{$1}}');
converted = converted.replace(/\[([^\]]+)\]\(([^)]+)\)/g, '[$1|$2]');
converted = converted.replace(/<([^>]+)>/g, '[$1]');
return converted;
}
toJ(document.kmvar.jira_markdown);
view raw md2jira.js hosted with ❤ by GitHub

Move newest file in Downloads folder to the frontmost Finder window

Here's what you do:

  1. Open the folder where you want to move your newest download in Finder.
  2. Trigger the shell script below.

The file will magically be moved! You'll even get a Notification Center notification.

The shell script

#! /usr/bin/env bash
current_folder=`osascript -e 'tell application "Finder"' -e "if (${1-1} <= (count Finder windows)) then" -e "get POSIX path of (target of window ${1-1} as alias)" -e 'else' -e 'get POSIX path of (desktop as alias)' -e 'end if' -e 'end tell'`
newest_file=`mdls -name kMDItemFSName -name kMDItemDateAdded -raw /Users/YOUR_USERNAME_HERE/Downloads/* | xargs -0 -I {} echo {} | sed 'N;s/\n/ /' | sort --reverse | head -1 | sed -E "s/^.*\\+0000 //"`
mv "/Users/YOUR_USERNAME_HERE/Downloads/$newest_file" "$current_folder"
osascript -e "display notification \"$newest_file → $current_folder\" with title \"File moved\""
view raw move.sh hosted with ❤ by GitHub

Triggering the shell script

I use an AppleScript saved as a .app and triggered with LaunchBar. Here's the AppleScript:

do shell script "bash /Users/YOUR_USERNAME/path/to/script.sh"

Switching from TextExpander to Keyboard Maestro

The makers of TextExpander recently changed their pricing model with the release of v6 from paying once for each version to a subscription. I have no problem paying for software that makes me more productive, as TextExpander certainly does.

However, v6 of TextExpander started inserting non-breaking spaces rather than normal spaces at the beginning of Python code snippets: I would paste code into TextExpander with 4 space characters in a row, and it would come out with 3 NBSPs and one normal space. This effectively broke TextExpander for some of my most-used snippets.

Additionally, I've always had quality and stability issues with TextExpander and Smile's other product, PDFpenPro. For example, in TextExpander v5, it was impossible to tab from the snippet entry field to the abbreviation field. This infuriating problem was left unresolved for all of v5, but was thankfully fixed in v6. And PDFpenPro is simply much less stable than the alternatives (Preview.app and PDF Expert), with fairly frequent beachballs and freezes.

TextExpander v6 requires syncing to their central server. Given the software quality issues with their stand-alone apps, I'm very concerned about snippet loss from sync errors. I'm also not convinced that the QA issues with their native applications will ever be resolved, especially with the added burden of their new sync/subscription business model. And I am skeptical that their new business model will be sustainable long-term, given these other issues and the response of many users to their subscription pricing.

I decided it was time to look for an alternative.

Keyboard Maestro

Keyboard Maestro is general-purpose Mac automation software, which also has the capability to expand text snippets like TextExpander. I manually migrated my snippets over in about an hour (it can be done automatically too).

What I like

  • Keyboard Maestro (KM) seems generally more stable and performant than TextExpander (TE).
  • KM's two column UI for listing snippets is much more effective than TE's single column with collapsable headings.
  • KM allows multiple snippets with the same shortcut: when you type a conflicted shortcut, it shows a pop-up menu where you can select the snippet you want.
  • KM does lots of things besides snippets, like letting you ctrl-tab between multiple windows in the same application!

What I don't like

  • The KM quick search doesn't show the shortcut for the snippet (TE's quick search does).
  • Setting up new snippets in KM is a bit more complicated than with TE. This is because KM is a multi-purpose tool, and there are definitely benefits to specialization. But at least the tab key works as expected.

Other than these minor complaints, I've had zero problems with KM.

Setting up text expansion in Keyboard Maestro

Here's what my text expansion snippets ("macros" in KM parlance) look like – it seems like it's necessary to reset the system clipboard after expanding the snippet if you use the "paste" insert method rather than the "type" method:

Implications for this website

I've posted a number of tips on this site that use TextExpander. So far I have been able to convert these to Keyboard Maestro macros with minimal effort.

From now on, I will be posting KM macros rather than TE snippets.