python - compare value with dictionary

Question

I wrote a small script to which exports data from queries to .csv file.

try:
    connection = pyodbc.connect ('DRIVER={SQL Server};SERVER=xxxx ;DATABASE=xxxx;UID=xxxx;PWD=xxxx')
    cursor = connection.cursor()
    conn = psycopg2.connect("dbname='xxxx' user='xxxx' host='xxxx' password='xxx'")
    cur=conn.cursor()

    try:

        query = """select .....;""" .format(yy,mm)
        cursor.execute(query)
        results1 = cursor.fetchall()

        query1 = """SELECT .....""".format(yy,mm)
        cur.execute(query1)
        results2 = cur.fetchall()
        results = results1 + results2
        cursor.close()
        cur.close()

for row in results:
    name = row[0].split('_',1)[0]
    print name

Last line slice result using sign '_' and i choose first element from it. So results will be like this :

1-GRB-809
1-HDX-252
539ZWU
EUM-505

Next part of code is responsible for exporting data to .csv

csv_file = open('errors.csv','wb')
myFile = csv.writer(csv_file)
myFile.writerows(results)
csv_file.close()

Before my code I created a function with dictionary.

def region(name):

dict  = {
'1-GRB-809':'EMEA',
'1-HDX-252':'EMEA',
'539ZWU':'NAM',
'EUM-5059':'NAM',
}

What i tried to do is compare values from sliced data with dictionary, replace its values and add result to new column in .csv file as well.(with relation to query results). How should i do that? I assume i need to add loop to function region for name in dict: but i dont know what i should do next :)

Answer 1

Sounds like you are trying to do the T (transform) in ETL.

I'd suggest doing the data manipulation in sql rather than python as sql code is declarative, and you could just write in your query:

select *, case when Name in ('1-GRB-809',''1-HDX-252'') then 'EMEA',
   case when Name in ('539ZWU','EUM-5059') then 'NAM' else null end as Region
from Table

That, or, if you insist on keeping the T in python, try a psuedo-case statement as referenced here: Replacements for switch statement in Python?