1

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()
Dragon.M
  • 249
  • 1
  • 4
  • 11
  • 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 https://stackoverflow.com/questions/9336270/using-a-python-dict-for-a-sql-insert-statement for some insights – Oluwafemi Sule Jul 06 '17 at 07:11
  • 1
    hmm I'm not sure, because this, on a first look, looks hardly applicable on **executemany**, but I will check. – Dragon.M Jul 06 '17 at 07:18

1 Answers1

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:

Oluwafemi Sule
  • 36,144
  • 1
  • 56
  • 81
  • 1
    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. – Dragon.M Jul 06 '17 at 08:26