CODEWITHSUNDEEP
pythonpython-2.7pymssql
pengz
pengz

Reputation: 2471

Pymssql insert multiple from list of dictionaries with dynamic column names

I am using python 2.7 to perform CRUD operations on a MS SQL 2012 DB.

I have data stored in a List of Dictionaries "NewComputers" (each dictionary is a row in the database).

This is working properly. However, the source data column names and the destination column names are both hard-coded (note that the column names are different).

Questions: Instead of hard-coding the data source column names, how can I loop over the dictionary to determine the column names dynamically? Also, instead of hard-coding the destination (database table) column names, how can I loop over a list of column names dynamically?

I would like to make this function re-usable for different data source columns and destination columns.

In other words:

"INSERT INTO Computer (PARAMETERIZED LIST OF COLUMN NAMES) VALUES (PARAMETERIZED LIST OF VALUES)"

Here is the function:

def insertSR(NewComputers):
    conn = pymssql.connect(mssql_server, mssql_user, mssql_pwd, "Computers")
    cursor = conn.cursor(as_dict=True)
    try:
        cursor.executemany("INSERT INTO Computer (ComputerID, HostName, Type) VALUES (%(computer_id)s, %(host_name)s, %(type)s)", NewComputers) # How to make the column names dynamic?
    except:
        conn.rollback()
        print("ERROR: Database Insert failed.")
    conn.commit()
    print("Inserted {} rows successfully".format(cursor.rowcount))
    conn.close()

Upvotes: 0

Views: 1448

Answers (1)

Loïc
Loïc

Reputation: 11942

You can't do what you'd like to.

Basically, your multiple insert SQL query will get translated to :

insert into table (column1, column2, column3) values (a1,a2,a3), (b1,b2,b3)

So as you can see, you'll at least have to make one different query per destination columns group.

Then on the data source side, (a1,a2,a3),(b1,b2,b3) in my example, you don't have to specify the column name, so you can have different data sources for a given destination.

On this part, I'd do something like this :

First build a correspondance dict, key is the destination field name, and values are the other names used for this field in the data source tables :

source_correspondance = {
          'ComputerID':['id_computer', 'computer_Id'],
          'HostName': ['host', 'ip', 'host_name'],
          'Type':['type', 'type']
}

Then iterate over your data source, and replace the column name by the key of your correspondance dict.

Then finally you can build your queries (1 executemany per destination 'type').

Upvotes: 1

Related Questions