CSV export of event data

Each year we need to export Pedalpalooza ride data to a CSV to share with Pedalpalooza organizers (so they can do things like assemble a print calendar, make a schedule to promote various rides, etc). It needs to include contact info which might not be public, so we can’t just take the public events API response and convert it from JSON to CSV (which would be straightforward).

So, I have a SQL query to pull all the data. The query is relatively simple — all non-cancelled rides between dates X and Y. that part is easy. I’ve adjusted the query to use friendlier column names and values in some cases (e.g. “Family friendly” instead of just “F”), but those tweaks are somewhat beside the point.

The tricky part is getting the SQL output into a usable CSV:

• I end up taking SQL’s plain text table output and doing some text munging to wrestle it into a CSV. This is tricky to do in a reliable way — values may contain line returns, the separator | character, unescaped quotes and spaces, etc. I’ve honed this process but it still usually involves a few attempts to accommodate a wayward comma or whatever.
• There’s a tool in the legacy part of the repo to export as CSV. I haven’t used it, but it looks like a bespoke tool which does essentially the same sort of text wrangling that I'm doing and probably has some of the same pitfalls. (I should try it out, though.)
• MySQL has a native way to send output to a CSV file, but writing files requires an additional permission which users don’t have by default (which is reasonable, for security reasons). I can’t figure out how to grant this permission, though — the default shift db user doesn’t have it, and doesn’t have permission to grant it. It looks like theroot or mysql users can grant the permission, but I can’t seem to log in as either. I found some tutorials for how to reset the root user password, but I was wary of diverging my local from production in some subtle way and also wasn’t totally sure this was the solution anyway.
• There’s a simple way to output a tab-separated file from the bash command line (e.g. ./shift mysqlpipe < commands.sql > out.tsv). This doesn’t require the MySQL file writing privilege, since it’s running as your OS user. But, since the values can contain line breaks it doesn’t import cleanly as a spreadsheet as-is. (I also saw some potential character encoding issues.) I did find some instructions for converting TSV to CSV, but that mostly just brings you back to bespoke text munging.
• Could try using the public events API JSON output and then smashing in just the contact fields. This wouldn’t entirely avoid the need for text munging, but it would reduce it to a smaller and potentially less complex set of fields.

I feel like MySQL’s native CSV output is probably the real solution, but I’ve hit a wall there. If we think this is the way to go, we could potentially add a new MySQL user with limited permissions just for this purpose if we don’t want to grant the default user more privileges. (Something like read-only + file writing, and only invoked from an export script.)

Start by reviewing the existing CSV tool in the legacy directory and the ./shift mysqlpipe command, then compare them with the issue’s SQL query and export constraints. Done means a repeatable export includes the required contact and ride data while correctly handling line breaks, separators, quotes, spaces, and character encoding without relying on ad hoc text munging.