I have several windows where I am writing separate scripts for each window to accomplish the same task. Which gets old! I have looked around on the forum but haven’t found any examples. Does anyone have a example of this type of script? Something to point me in the write direction. I have written some simple shared scripts for a popup window message and some basic math functions just to tested it out, but that’s it.
What you would need depends on how simple or complicated your updates are. Are they doing one table or does the table change? Are they updating one column, the same set of columns or does the column count vary? Are you inserting/updating one row or multiple rows at one time?
Depending on what your doing an how consistent it is, you could probably do it with a named query easier than a script. If your going for something more complex, a script can be more flexible but that depends on the time you take to work out everything you want it to be able to do.
It would have to be more flexible. Several different tables. Some times I am inserting and updating parent and child tables, different column counts etc. Sometimes I am updating one row but needs the capability to do multiple rows. I know it will be challenging but in the long run it will be faster.
I wrote a serializer (might be the wrong terminology) that goes through a window and looks for components that meets my naming convention and then puts the appropriate value (indicated by naming convention) into a dictionary. I use this on almost every window.
I then put this into a SQL Query maker I also wrote for insert/update functions.
I’ve tested this for speed and it is much slower than say using a named query to do 1000’s of inserts, but for forms on windows doing a single update or insert it’s very serviceable and makes my life a lot easier. It could also probably be modified to work with named queries easily.
Is this the sort of thing you want?
I am not for sure, I don’t think a name query is what I am after. I am not doing 1000’s of inserts, simple forms on windows some inserts from drop downs, texted boxes, etc. Sometimes I do inserts from table components which takes a for loop to accomplish the insert.
I don’t have the time to go through all of it at once but here is something that may get you started. You feed it the table name you want to insert into and a dataset. The header for the dataset has to match the column names you want to insert into but it will create a single insert query for all the data in the dataset. I’m sure it can be cleaned up more but threw it together real quick. Hopefully it helps get you started.
def in_query(tName,dVals):
#tName is the table name you want to insert into, dVals is a dataset. The headers must match the column names you want to insert into.
colr = system.dataset.getColumnHeaders(dVals)
cols = ','.join([str(elem) for elem in colr])
cCnt = len(colr)
dVals = system.dataset.toPyDataSet(dVals)
iVals = None
for x in dVals:
rData = "("
for y in range(cCnt):
if y == 0:
rData += str(x[y])
else:
rData += "," + str(x[y])
rData += ")"
if iVals:
iVals += ',' + rData
else:
iVals = rData
query = "INSERT INTO %s (%s) VALUES %s" % (tName,cols,iVals)
system.db.runQuery(query)
** I tested that the string it creates looks right but can’t do a database insert currently to test that part of it.
Does sort of sound like I have what you need. I’ll lay it out for you here and you can take it or leave it.
def parseProperties(prop, prefix="_out_"):
"""
Helper function for createDictionaryFromComponents
Args:
prop: a property on a container
Returns:
tuple, (column, value) - to be used in dictionary
"""
propName = str(prop)
if prefix in str(prop):
column = propName.split(prefix)[1]
value = prop.getValue()
return (column, value)
else:
return (None, None)
def parseComponent(component, prefix="_out_"):
"""
Helper function for createDictionaryFromComponents
Args:
component: a component, or a proeprty on a container
Returns:
tuple, (column, value) - to be used to add to the dictonary
"""
try:
componentName = str(component.name)
if prefix in componentName:
# print "goign to save component %s to db"%(componentName)
columnAndType = componentName.split(prefix)[1]
column = '_'.join(columnAndType.split("_")[:-1])
dataType = columnAndType.split("_")[-1].lower()
# print "column name in db: " + str(column)
# print "property to get off component: " + str(dataType)
if dataType == 'text':
value = component.text
elif dataType == 'float':
value = component.floatValue
elif dataType == 'floatround':
value = round(component.floatValue, 2)
elif dataType == 'selectedvalue':
value = component.selectedValue
elif dataType == 'selected':
value = component.selected
elif dataType == 'integer':
value = component.intValue
elif dataType == 'date':
value = component.date
elif dataType == 'data':
value = component.data
else:
value = None
for i in component.properties:
# print str(i)
if str(i).lower() == dataType.lower():
value = component.getPropertyValue(str(i))
break
return (column, value)
else:
return (None, None)
except Exception, e:
print "fe: " + str(e)
def getDataVision(container, prefix="_out_"):
"""
The final step in automation of the CRUD cycle. How to use this -
To make sure your component is set properly in the dictionary to be send to the database
it MUST follow this format.
OthertextAboutComponenthere_out_dbcolumnname_propertyToGrab
So for example for a text field whos text property I want to save to a column "name" in the db,
textfield_out_name_text
Properties we can handle:
_text
_selectedValue
_float
_int
_date
And we can also handle custom properties, which you would just have to give the full name of
textfield_out_name_customPropertyNameHere
Args:
container: can be used on any container, but doesn't do a deep search
into child containers of containers (yet). Most likely a Root Container,
or a Form Container.
Returns:
dictionary: keys are column names of database, values are values to be updated/inserted
"""
import system.gui
ds = {}
# iterate through root container customer properties first, often things to take out of there
for prop in container.properties:
columnName, value = parseProperties(prop, prefix=prefix)
if columnName is not None and value is not None:
ds[columnName] = value
componentsInContainer = container.components
while len(componentsInContainer) != 0:
currentComponent = componentsInContainer.pop()
try:
columnName, value = parseComponent(currentComponent, prefix=prefix)
except Exception, e:
print "e: " + str(e)
if columnName is not None and value is None:
print "Error getting value for column %s"%(columnName)
ds[columnName] = None
elif columnName is None and value is None:
pass
elif columnName is not None and value is not None:
ds[columnName] = value
return ds
How would you use this? Put all three functions into a script module.
Then, in a vision window, most likely on the form submit button, you would do this
import serializer
data = serializer.getVisionData(event.source.parent)
With all your components, you need to follow the naming convention I laid out in the documentation of the getVisionData function. You can do stuff like naming dd_out_myDropDown_To_Go_Out_selectedValue and this would appear in the data dictionary as
{'myDropDown_To_Go_Out':4} if the selected value of the dropdown was 4.
What I normally do is make the component that end up being the key in the dictionary also be the name of the column in the database so I can then feed this dictionary to my SQL Query builder that crafts the SQL and runs the insert/update.
Note too this function will also grab any values on the root container properties of the form _out_somePropIWant and put them in the dictionary as {'somePropIWant':'someValue'}.
This works for datasets etc. Of course you cannot just insert datasets, but it allows you easily to get all component values you want to work with instead of having to do 50 lines of variables equaling some component property, something I got tired of doing which is why I wrote this one day. It obviously could be improved in terms of error handling instead of just silently failing. The SQL Query builder script I wrote is much more robust in a way to make up for the quick scripting I did here lol.
If this does seem like what you want and are interested I can post the SQL Query builder script as well.
This looks like it is a insert from a table component only? If I am looking at it correctly, I will try this out. Then I assume I could do something similar for inserts from a form style layout.
So looking over this, it looks like it works for table components and or form style components. I would like to see sql query builder script as well, to see what that looks like.
The table component uses a dataset so it would work directly from it, assuming the column names aren’t changed when you query it from the database. For a form or something else, you can create a dataset with the values then pass them over. Something like:
dv1 = 12
dv2 = 52
dv3 = 'abc'
header = [col1,col2,col3]
d = [dv1,dv2,dv3]
ds = system.dataset.toDataSet(header,d)
If you had multiple rows to do at once, you can create the values portion of it by using:
d.append([dv1,dv2,dv3])
It all comes down to how you script it though. Same as in my original post, I assumed you would use one of the values in a check against the table to see if it already exists and auto create the statements for insert and update both at the same time dependent on if an entry already exists or not. If I had more time to put on it today, I’d give an example for how you could build both at once but it shouldn’t be to hard to take what I had there, and add an if in create both statements at once. I like combining inserts and updates as much as I can to limit traffic.
Ok Thanks for the help. I will try to see what I can come up with.
sqlBuilder.zip (5.9 KB)
Some notes - you’ll see uPrint all over the place - this was a solution we came up with to print in both vision and perspective no matter where the function was called from. You can replace or delete them.
The end of insert/update you’ll see a check function - you can ignore this. I had a client who did not believe things were being saved properly and wanted us to SELECT from the database right after writing to it to compare it to the data dictionary that was fed in. You can ignore or delete those lines as they’re probably unnecessary.
As I recall as well, right now the DB checks makes everything in your dictionary matches some column, and also that every column is provided some value. So even if your form does not have a component related to a column, you’ll either in scripting want to append some value to the dictionary or have a root container property with some default.
ok, I will take a look at it all. Thanks for the help