Data Discussions

February 28, 2011

Execute Python script from CLI and print output

Another example Python script. The idea here is to input a CSV file and kick out a SQL statement to quickly insert the data into a database table.

Updated - see 5)

1) Create a Python Script, call it example_script.py:
- sys.argv is taken from the command line
- the Print statement will output to the terminal

#! /usr/bin/python

import csv, sys

try:

    fileName= sys.argv[1]

    inFile = list(csv.reader( open(fileName, 'r') , delimiter=','))

    print str(inFile)

except IOError

    print 'Can\'t open file for reading.'

    sys.exit(0)

2) Make the file executable (taken from this forum post.)

$ sudo chmod +x example_script.py

3) Execute!
$ sudo ./example_script.py ~/Desktop/example_inputFile.csv

OR

$ sudo python example_script.py ~/Desktop/example_inputFile.csv

4) To pass additional arguments via the command line (e.g. database name) you can add them after the execute command and alter the code a bit.

Here's a more complete example using a script I've written to read a CSV and output an SQL insert statement:

Execute:
$ sudo ./example_script.py ~/Desktop/inputFile.csv [database] [table]

This script:

#! /usr/bin/python

import csv, sys


try:

    fileName= sys.argv[1]

    db = sys.argv[2]

    table = sys.argv[3]

    

    #Open CSV into a list

    in1 = list(csv.reader( open(fileName, 'r') , delimiter=','))

    

    #Write INSERT, db and table, column names and begin VALUES

    print "INSERT INTO {0}.{1} ".format(db, table) +"\n"

    print "(" + str(in1[0]).strip('[]') + ")\n" #First row must match table fields

    print "VALUES\n"

    #Write the data (i.e., rows 2:End

    for row in in1[1:]:

        if row != in1[-1]:

            print "(" + str(row).strip('[]') + "),\n"

        else:

            print "(" + str(row).strip('[]') + ");\n"

    

except IOError:

    print 'Can\'t open file for reading.'

    sys.exit(0)

5) To pipe the output to a text file (via the terminal) use this command:

$ sudo ./format_csv_cli_output.py ~/Desktop/inputFile.csv [database] [table] > ~/Desktop/cli_output.txt

Source: Comment by Tomosaur in this issue.

Posted by kkwaiser at 09:54 AM | Comments (0)

January 28, 2011

Script to form CSV for database insertion

My technique will almost assuredly become more refined as time passes but, for now, here is a simple script that takes in a CSV file and outputs an SQL statement.

#! /usr/bin/python

#execfile("/dir/format_csv.py")

import csv, sys

try:

    inDir ='/home/data/Desktop/'

    inFile = 'test_datafile.csv'

    db = "umbs" 

    table = "temptable"

    #Open CSV into a list

    in1 = list(csv.reader( open('{0}{1}'.format(inDir, inFile), 'r') , delimiter=','))

    out = open('{0}test_output.csv'.format(inDir), 'w') # output Document

    #See http://docs.python.org/tutorial/inputoutput.html for discussion on .format

    

    #Write INSERT, db and table, column names and begin VALUES

    out.write("INSERT INTO {0}.{1} ".format(db, table) +"\n")

    out.write("(" + str(in1[0]).strip('[]') + ")\n")#First row must match table fields

    out.write("VALUES\n")

    #Write the data (i.e., rows 2:End

    for row in in1[1:]:

        if row != in1[-1]:

            out.write("(" + str(row).strip('[]') + "),\n")

        else:

            out.write("(" + str(row).strip('[]') + ");\n")

    #Close the output file

    out.close()

except IOError:

    print 'Can\'t open file for reading.'

    sys.exit(0)

Posted by kkwaiser at 03:39 PM | Comments (0)