Articles with the sql tag

Sep 13 · Data Posts

Exploration of voopter airfare data

I’ve recently started working as a data science freelancer for voopter.com.br, helping them analyze the data generated by airfare searches on their website. Voopter is a metasearch engine for flights from and to Brazil. The first thing I did was to create an interactive dashboard in R and shiny for some explorative statistics of the millions of seaches performed by users of their website (which has already led to more specific business-driven questions).

The dashboard provides a quick and easy way to filter and aggregate the data, which is stored in an SQL database. The idea is …

Feb 02 · Data Posts

Sql to excel

A little python tool to execute an sql script (postgresql in this case, but should be easily modifiable for mysql etc.) and store the result in a csv or excel (xls file):

"""
Executes an sql script and stores the result in a file.
"""

import os, sys
import subprocess
import csv
from xlwt import Workbook


def sql_to_csv(sql_fnm, csv_fnm):
    """ Write result of executing sql script to txt file"""

    with open(sql_fnm, 'r') as sql_file:
        query = sql_file.read()
        query = "COPY (" + query + ") TO STDOUT WITH CSV HEADER"
        cmd = 'psql -c "' + query + '"'
        print cmd

        data = subprocess.check_output(cmd, shell=True)

        with open(csv_fnm, 'wb …