dlatk/usefulScripts

Helpful scripts for getting data between MySQL and text files (csv, tsv, json)

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.