Dynamic queries

Archived Topics
1

I’ve got a situation where my insert queries really need to be dynamic, and was wondering if I am going about this correctly. I won’t know the number of columns or data types until I actually execute the code.

This is what I have so far:

# This is some sample data that I can easily generate from another source. It's just a list of lists, containing the column name, data type, and data.

d = [['Col_1','%d',1],['Col_2','%d',2],['Col_3','%d',3],['Col_4','%d',4]]

# Using a list comprehension, I transpose the list to three tuples
col, dataType, data= [tuple([t[i] for t in d]) for i in range(0,len(d[0]))]

#If I apply the tuples to string formatting, I can see that everything makes sense so far. (I join the data types and col defs, and leave the data as a tuple):
print 'This is my data: '+','.join(dataType) % (data)
print 'These are my columns: %s' % (','.join(col))

But, when I embed these values into an fpmi.update query, I can’t seem to get them to all work at once.
fpmi.db.runUpdateQuery(‘INSERT INTO MyTable (’ + ‘,’.join(col) + ‘) VALUES (’+ ‘,’.join(dataType) +’)’ % data)

I am trying to resolve this into “INSERT INTO MyTable (Col_1,Col_2,Col_3,Col_4) VALUES (%d,%d,%d,%d)” % (1,2,3,4)

I assume that since ‘data’ is already a tuple, I don’t have to enclose it in (), but I tried that anyway. I also tried passing the column names in seperately with string formatting too, but no dice. I think I must be really close, but am missing something.

2

I would drop the data types and use a prepared statement (fpmi.db.runPrepStmt).

3

Thanks Carl. I can’t try it until later, but I just want to veryify something. Looking at the example in the help:

userText = event.source.parent.getComponent(“TextArea”).text
userName = fpmi.security.getUsername()
fpmi.db.runPrepStmt(“INSERT INTO Comments (Name, UserComment) VALUES (?,?)”, [userName, userText])

Can the “Name, UserComment” part of the string also be dynamic too, or would I just build the whole string and do something like this:

q= “INSERT INTO Comments (Name, UserComment) VALUES (?,?)”
fpmi.db.runPrepStmt(q, [userName, userText])

4

You could dynamically build the columns in the string part, and just make sure that the length of your parameter sequence matches the number of columns you added.