Search code examples
pythonsql-serverdatabasepypyodbc

Python: Set param for columns and values pypyodbc - executemany

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()
Solution

  • 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: