Creating menu from field in a CSV file

[ennoil]

I have a csv file with 4 columns that I want to use to create menus.
For example, the second column has values such as "TEST", "QA", "PROD" and a couple of others. I want to take all the values from the second column, weed out the duplicates, then create a menu allowing the users to select all the records with, say, QA in the second column. How is the best way to go about this? (Later I want the user to be able to pick values from more than one column to pick selected records with)
Please keep in mind that I am very new to Python. I can do this in BASH with select/case construct but can not find how to do it in Python.
Thanks,
John

[sergb]

Here is one approach.
Assuming the data file is not very big, you can read the whole thing into memory and manipulate it from there. Otherwise, you may want to read just the second column from the csv file to produce the menu.

import csv

data = []                 #array to hold csv data
menu = set()              #menu items; set object will remove dups on its own
f = open("data.csv", "r") #open csv file
reader = csv.reader(f)    #create csv reader
for row in reader:        #for each line in csv file
    data.append(row)      #add it to the data array
    menu.add(row[1])      #add value of the second column to menu
f.close()                 #close csv file

#print out the menu
for item in menu:
    print item

#knowing the menu item user selected, you can print all records for it
selected = "..."
for row in data:
    if row[1] == selected:
        print row

Alternatively, if you have sqlite3 library installed (which comes with standard Python installation), you can read your csv data into a :memory: sqlite database and then simply run queries against the database to produce the desired results. You can run the DISTINCT query to get the list of menu items, and simple SELECT statement with a WHERE clause to filter results when user selects a menu item.

[ennoil]

sergb - Thanks. That gave a few tips on how to convert the menu to a choice.
[Sorry, the code below is BASH code and not Python.]
To expand my question, is it possible to have a numbered choice that the user enters? I am looking for something similar to select/case in BASH:

select use in Exit ALL Environment Description OS Architecture

produces the following:
$ ./diskspace.sh
1) Exit 3) Environment 5) OS
2) ALL 4) Description 6) Architecture
#?

Where the user chooses 3 and which uses the following BASH code:

select type in "Exit" `cat .servers.csv | cut -d, -f2 | sort -u`

To output:
I want to choose by Environment...
1) Exit
2) ADMIN
3) DEV
4) PROD
5) QA
6) TEST
#?
Choosing 5 will perform action x on the first field of all the records than have QA in field 2.
I can see where the menu choices are dynamic based on the csv file but I want it to be dummy proof (for people like me) requiring only numbered input.
Eventually, the user will be able to pick choices from more than one category (like all Red Hat boxes in the QA environment) from the menu.
Thanks,
John

[woooee]

You can read the entire file into a list, process the list once appending the record number to a dictionary of lists, with the "QA" field as the key. So if record #1 has QA=123 the dictionary would then be
index_dict[123] = [1]
for record #2 QA = 12
index dict[12] = 2
record #3 QA = 123 again
index_dict[123] = [1, 3]
You can then look up a specific QA value in the dictionary and know which records in the list to print. And how are you defining "duplicate record"? If it is subsequent records with the same QA value, then you can just store the original record in the dictionary instead of the record number. Post back if you want some help with this, or want to use SQLite which is the best solution for this.

[sergb]

Yes, that should be possible. That requires a little change in the code I posted previously. Namely, a change to the menu variable. It used to be a set , which is unordered. Now, we want to make it into a list and make use of the fact that arrays are indexed and its items are "numbered". So, all we need to do is ask user for the index of the menu item and then use that numeric index to get the value from the menu list.

import csv, sys

data = []                 #array to hold csv data
menu = []                 #menu items
f = open("data.csv", "r") #open csv file
reader = csv.reader(f)    #create csv reader
for row in reader:        #for each line in csv file
    data.append(row)      #add it to the data array
    v = row[1]            #get the value of the second column
    if v not in menu:     #if v is not in the menu list...
        menu.append(v)    #...add it
f.close()                 #close csv file

#print out the menu
print '0) Exit'
i = 1
for item in menu:
    print "%d) %s" % (i, item)
    i += 1

#read user input
value = raw_input("enter numeric value: ")
index = int(value) - 1
if index < 0 or index > (len(menu)-1):   #Exit or invalid item was selected
    sys.exit(0)
selected = menu[index]

#knowing the menu item user selected, you can print all records for it
for row in data:
    if row[1] == selected:
        print row

Of course, the code above lacks user input validation.

[ennoil]

That worked much better. I have added some validation to it.
The purpose of the script is to allow a user to run commands via ssh on servers defined by fields 1-3 (field 0 being the server name) of the .csv file (I will be using pexpect module for the passwords where needed). In the case above, the menu picks out all the servers in a specific environment, next I want the user to be able to pick servers by use (i.e. Oracle, web server, tomcat, etc). To do this will I need to redefine v and recreate the menu or can I set the above as a function and pass the field number to the function to create the menu.
At the end I will list the servers by the environment, use, OS, and architecture (all 64 bit RHEL Oracle servers in QA for example).
A line from the csv file looks like "<server name>,TEST,Oracle_Server,Red_Hat,64".
I am also wondering if I should read the file into an sqlite database in memory. The csv file should not have more than 100-200 records.
I know I may be inventing the wheel here but, as I said, I am trying to learn Python and this seems a good way to start.

[sergb]

Yes, you should absolutely write a function to accept the field number and produce the menu.

As for sqlite, you will probably not notice any performance gains (although there might be some) over reading a file with 100-200 records. But it would give you an easy way to select distinct items from one field. That is, it would simplify your code that creates the menu and selects records related to the selected menu item.