Prepare Dynamic Query with Dictionary and Table name in Python logic

This post is basically showing a logic to prepare an UPDATE query (by seeing this you can make INSERT and DELETE queries though) .

The inputs are table name and the dictionary with column names are keys and values.

This logic creates the reference to the table name and use it in entire query like

BIO_DATA bd

or

ADDRESS_DATA ad

Make sure the tables words are appended with “_” as database table guidelines. If you want you can still follow your own style and change the delimiter “.” or so.

The dictionary must have the corresponding table columns as keys but the values may or may not contains. If the values are presented they will be attached directly to the columns like

bd.REG_ID=’1234′

If the values are empty, then dynamic keys will be added like, in future if you want to add the values later. (This my not use for your query)

bd.REG_ID=:RED_ID

The Dictionary looks like the following:

{‘BIO_DATA_ID’:”,’REG_ID’:’1234′,’GENDER_TYP_ID’:”,’SRC_TYP_ID’:”,’BIRTHDAY’:’19761201′}

Finally the query looks like the following

UPDATE IDM_PER_REG.BIO_DATA bd SET bd.REG_ID=’1234′,bd.GENDER_TYP_ID=:GENDER_TYP_ID, bd.BIO_DATA_ID=:BIO_DATA_ID, bd.BIRTHDAY=’19761201′

Check for the following code ..

Note:- Follow the intends as python’s code format structure.  I pasted the code directly here, but you need to care when you run this. I also pasted the image, so you can arrange your code properly by seeing that image. Link to Code Format Image

def updateQuery(self,tableName,valuesDict):
dynQry = "UPDATE "
dynQry += tableName+" "
tableRef = ""
try:
if(tableName.split(".")[1].index("_")>=-1):
tableWords = tableName.split(".")[1].split("_")
for i in range(len(tableWords)):
for letter in str(tableWords[i]).split():
tableRef += letter[0].lower()
except Exception:
for letter in str(tableName.split(".")[1]).split():
tableRef += letter[0].lower()
dynQry += tableRef+" SET "
if(valuesDict is not None):
valueDict = dict(valuesDict)
count = 0
for key,value in valueDict.iteritems():
count+=1
if (value is None or len(value)<=0):
if(count < valueDict.__len__()):
dynQry += tableRef+"."+key+"=:"+key+","
else:
dynQry += tableRef+"."+key+"=:"+key
else:
if(count < valueDict.__len__()):
dynQry += tableRef+"."+key+"='"+value+"',"
else:
dynQry += tableRef+"."+key+"='"+value+"'"

return dynQry
Comments please..!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: