CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
Mfreeman
Mfreeman

Reputation: 3958

Taking list of changed data from API, and inserting it into a database with other unchanged data PYTHON

This title may be a little misleading, but here is where i am at

My Goal:

Take data from a JIRA api and push it to a Postgresql database(using psycopg2). BUT need one of the column's data to be changed by a find and replace and then take that list and push it to the database along with the other data that is unchanged from the API.

What I have currently:

So i do a find and replace by:

creating a list from the API data that needs change.

data_change = list((item['status']) for item in data_table['issues'])

I then create a dictionary to map what needs to be changed.

   severity = {
        'Blocker':  'Emergency',
        'Critical': 'High',
        'Major':    'High',
        'Moderate': 'Medium',
        'Minor':      'Low',
        'Trivial':  'Low'
    }

then i create a new list with all the data i need to be entered into the database with the variable "result"

result = [severity.get(e, e) for e in data_change]

So now i need to take this list and push it to the database along with the other data.

    def insert_into_table_epic(data_table):

                query = """
                    INSERT into
                        table
                    (id, name, status)
                    VALUES
                        %s;
                    """
                values = list((item['id'],
                               item['name'],
                               result) for item in data_table['issues'])


                extras.execute_values(cur, query, values)

                conn.commit()

The Problem:

The problem lies with this line here:

values = list((item['id'],
                item['name'],
                result) for item in data_table['issues'])

The API has 50 different 'issues' so this adds one value into each row, resulting in 50 rows. I am passing 'result' as one of the values and this wont work because that variable is a list in itself and will insert the whole list for every row.

I was thinking of putting in a query to the database that does the find and replace after the data has been put it, but would like to know if i can do this by using this route:

API---->List---->Change data--->Insert into db with rest of data taken from API

The Question:

How do i change this variable 'result' and be able to pass it through 'value' without it taking the whole list for every row

Upvotes: 0

Views: 34

Answers (1)

klin
klin

Reputation: 121834

You can do the conversions directly (without data_change and result):

values = list((item['id'],
               item['key'],
               severity.get(item['status'], item['status'])
               ) for item in data_table['issues'])

Upvotes: 1

Related Questions