Useful Scripts

These scripts can be used to move data between MySQL and text files (csv, tsv, json).

mysqlToCSV.bash

Export MySQL data into a CSV

Usage:

./mysqlToCSV.bash database "sql select statement" [mysql_options ... ] > csv_file

Example:

./mysqlToCSV.bash fb20 "select * from messages_en" [mysql_options ... ] > myDump.csv

mysqlToJSON.py

Export MySQL data into JSON.

Usage:

mysqlToJSON.py db 'command' [dict | list] > json_file

Example (to JSON):

./mysqlToJSON.py twitterGH 'select message_id, message, created_time from messages_en limit 2' 
> {"created_time": "2009-06-12 02:38:26", "message": "@myfriend look at my fancy tweet", "message_id": "99999999"}
> {"created_time": "2009-06-12 02:38:26", "message": "love the tweet!", "message_id": "888888888"}

Example (to list):

./mysqlToJSON.py twitterGH 'select message_id, message, created_time from messages_en limit 2' list
> ["99999999", "@myfriend look at my fancy tweet", "2009-06-12 02:38:26"]
> ["888888888", "love the tweet!", "2009-06-12 02:38:26"]

csv2mySQL.py

Upload a CSV to MySQL.

Usage:

csv2mySQL.py FILE DATABASE TABLENAME '(mysql column description)' [IGNORELINES]

Example:

python csv2mySQL.py example.csv my_database my_new_table '(id int(10), name varchar(20))' 1

tsv2mySQL.py

Upload a TSV to MySQL/

Usage:

python tsv2mySQL.py FILE DATABASE TABLENAME '(mysql column description)' [IGNORELINES]

Example:

python tsv2mySQL.py example.tsv my_database my_new_table '(id int(10), name varchar(20))' 1

Requirements

The Python scripts use Python 2.7. Package dependencies include standard packages such as sys, os, json, time and datetime and the non-standard package MySQLdb. This can be installed via

pip install MySQL-python

License

Licensed under a GNU General Public License v3 (GPLv3)

Background

Developed by the World Well-Being Project based out of the University of Pennsylvania.