Databases

Setup MongoDB on iOS

A simple guide to setting up MongoDB on iOS.

  1. Objectives:
      1. Install MongoDB on MacBook.
  2. Required Tools:
      1. Homebrew —  package manager for Mac
      2. MongoDB — MongoDB community version
      3. pymongo — python API for MongoDB.
  3. Steps (terminal command in blue):
      1. brew update
      2. brew install mongodb
      3. Create MongoDB Data directory (/data/db) with updated permission
        1. $ sudo mkdir -p /data/db
        2. $ sudo chown <user>/data/db
      4. Create/open bash_profile
        1. $ cd to users/<username>
        2. $ touch .bash_profile # skip if .bash_profile present
        3. $ open .bash_profile
      5. Insert command in  bash_profile for MongoDB commands to work in terminal
        1. export MONGO_PATH=/usr/local/mongodb
        2. export PATH=$PATH:$MONGO_PATH/bin
      6. Test: Run MongoDB
        1. terminal 1: mongod
        2. terminal 2: mongo.
      7. Install pymongo
        1. pip install pymongo

Further notes 

Advertisements

Rapid input data from list of files to SQLite DB

Suppose you wish to input all the data from a series of CSV files residing in a particular folder to a SQLite database, the following tools and and commands will make it a relatively fast and painless.

Each of the CSV file are assumed to be in the format where the first line is the headers and the subsequent lines are series of data. The headers need not be the same for each CSV file.

The following python modules are required.

  1. CSV module
  2. Pandas (0.15)
  3. SQLite3

The following lines of commands will input execute the following actions.

  1. Use list comprehension to get the list of file path from a particular folder.
  2. Convert the data from each CSV file into dict format using CSV DictReader and iterate over all the files, joining them to a list.
  3. Convert the list of dicts to a single Pandas Dataframe.
  4. Create a connection to a particular SQLite Database.
  5. Use Pandas to_sql() function to pass all the data to SQLite database.
  6. The code is displayed below.
<pre>import os
import csv
import pandas
import sqlite3 as lite

path = r'C:\folderpath\to\list\of\files'
## Get the full path of all the csv files.
full_path_list = [os.path.join(path,f) for\
				 f in os.listdir(path) if os.path.isfile(os.path.join(path,f)) ]

## Convert all data to list of dicts.
full_data_list =  [n for f in full_path_list for n in csv.DictReader(open(f,'r'))]

## SQL database name and initialize the sql connection.
db_filename = r'c:\data\sbrtemp3.db'
con = lite.connect(db_filename)

## Convert to dataframe and write to sql database.
pandas.DataFrame(full_data_list).to_sql('test', con, flavor='sqlite',
				schema=None, if_exists='replace', index=True,
				index_label=None, chunksize=None, dtype=None)

## Close the SQL connection
con.close()

The Pandas to_sql() function is able to handle issues of duplicates and can be called multiple times if users required to add additional data. In addition, converting to a dataframe from a list of dicts also allows the headers to be different for different CSV files. Besides saving to database, user can also choose to consolidate to a single csv file by using Pandas to_csv() function.