Python: Set param for columns and values pypyodbc - executemany

Question

I have this situation where I created a method that will insert rows in database. I provide to that method columns, values and table name.

COLUMNS = [['NAME','SURNAME','AGE'],['SURNAME','NAME','AGE']]
VALUES = [['John','Doe',56],['Doe','John',56]]
TABLE = 'people'

This is how I would like to pass but it doesn't work:

db = DB_CONN.MSSQL() #method for connecting to MS SQL or ORACLE etc.
cursor = db.cursor()
sql = "insert into %s (?) VALUES(?)" % TABLE 
cursor.executemany([sql,[COLUMNS[0],VALUES[0]],[COLUMNS[1],VALUES[1]]])
db.commit()

This is how it will pass query but problem is that I must have predefined column names and that's not good because what if the other list has different column sort? Than the name will be in surname and surname in name.

db = DB_CONN.MSSQL() #method for connecting to MS SQL or ORACLE etc.
cursor = db.cursor()
sql = 'insert into %s (NAME,SURNAME,AGE) VALUES (?,?,?)'
cursor.executemany(sql,[['John','Doe',56],['Doe','John',56]])
db.commit()

I hope I explained it clearly enough. Ps. COLUMNS and VALUES are extracted from json dictionary

[{'NAME':'John','SURNAME':'Doe','AGE':56...},{'SURNAME':'Doe','NAME':'John','AGE':77...}]

if that helps.

SOLUTION:

class INSERT(object):

    def __init__(self):
        self.BASE_COL = ''

    def call(self):
        GATHER_DATA =  [{'NAME':'John','SURNAME':'Doe','AGE':56},{'SURNAME':'Doe','NAME':'John','AGE':77}]
        self.BASE_COL = ''
        TABLE = 'person'

        #check dictionary keys
        for DATA_EVAL in GATHER_DATA:

            if self.BASE_COL == '': self.BASE_COL = DATA_EVAL.keys()
            else:
                if self.BASE_COL != DATA_EVAL.keys():
                    print ("columns in DATA_EVAL.keys() have different columns")
                    #send mail or insert to log  or remove dict from list
                    exit(403)

        #if everything goes well make an insert
        columns = ','.join(self.BASE_COL)
        sql = 'insert into %s (%s) VALUES (?,?,?)' % (TABLE, columns)
        db = DB_CONN.MSSQL()
        cursor = db.cursor()
        cursor.executemany(sql, [DATA_EVAL.values() for DATA_EVAL in GATHER_DATA])
        db.commit()


if __name__ == "__main__":
    ins = INSERT()
    ins.call()

If there is no way you can ensure order in the values, you can opt to using a dict. See the answers to this question stackoverflow.com/questions/9336270/… for some insights — Oluwafemi Sule
– Oluwafemi Sule, Commented Jul 6, 2017 at 7:11
hmm I'm not sure, because this, on a first look, looks hardly applicable on executemany, but I will check. — Dragon.M
– Dragon.M, Commented Jul 6, 2017 at 7:18

Oluwafemi Sule · Accepted Answer · 2017-07-06 07:39:29Z

2

You could take advantage of the non-random nature of key-value pair listing for python dictionaries. You should check that all items in the json array of records have the same fields, otherwise you'll run into an exception in your query.

columns = ','.join(records[0].keys())
sql = 'insert into %s (%s) VALUES (?,?,?)' % (TABLE, columns)
cursor.executemany(sql,[record.values() for record in records])

References:

https://stackoverflow.com/a/835430/5189811

answered Jul 6, 2017 at 7:39

Oluwafemi Sule

39.3k1 gold badge63 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dragon.M Over a year ago

Yeah, I see... So basically even if the dictionary keys are in different order in list ... At the moment when we make loop trough dictionaries they will be sorted the same way. Thank you, I have shared my code with others so that they can benefit from this answer to.

Collectives™ on Stack Overflow

Python: Set param for columns and values pypyodbc - executemany

1 Answer 1

References:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

References:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related