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)