Cleaner way to get results of a query into variable names that match column?

Ignition
,
1

Calling all code-golfers (@PGriffith I know you like these)

I am currently rewriting some stored procedures from the database into jython (to try to prove to my coworkers it’s a much easier to read 100 lines of jython vs 400 lines of SQL Server syntax to do the same thing).

Here is the situation I am trying to make a bit cleaner/closer to one line

		productData = system.db.runQuery("SELECT col1, col2, col3, col4 FROM table WHERE id = '%i'"%(recordId))
		col1 ,col2, col3, col4 = productData.getValueAt(0, 'col1'), productData.getValueAt(0, 'col2'), productData.getValueAt(0, 'col3'), productData.getValueAt(0, 'col4')
		print col1, col2, col3, col4

That second line is really the problematic one as I want to do the same thing for certain queries that might have 10 or more columns I can see using a list to populate the SELECT statement, but I don’t know how I could go from that, to dynamically making and assigning values to variables.

Not even sure if this is a good idea, but I would like to know if it’s possible as a proof of concept - to be bale to quickly to go from results = system.db.runQuery("SELECT col1, col2, .. colN FROM table WHERE id = %i"%(recordId)) to having variables with the names col1, col2, ... colN with the associated value from the database.

You can assume the query will only return a single row (the only case where it would be sensible to try to do this).

Anyone know how to do this?

2

It’s not really practical to dynamically assign variables like this, but the obvious thing to try seems like returning a dictionary? So you’d have to productData['col1'] - still some extra characters, but not much.

You’d have to decide how to feed the columns into this query, which would define your expected result.

1 Like
3

Yea I have a dictionary function already that does similar. I love dictionaries but not everyone I work with does, hence the question. This was more wondering if it was possible due to this reason. Good to know. Thanks @PGriffith

4

8 posts were split to a new topic: Goofin’ about keyboards

6

I use a wrapper for dictionaries that look up missing properties in the dictionary, with an automatic fallback to none. SmartMap In this util script module.

I instanciate from a dataset ds like so:

smap = shared.smartmap.SmartMap(zip(ds.columnNames, [ds.getValueAt(0, c) for c in range(ds.columnCount)]))

Then you can use smap.ColumnName in your code.

2 Likes
12

not really a super good idea but it works... aslong there are no spaces or weird characters

for name in productData.getColumnNames():
	 exec("%s = productData[0][name]"%name)

image
image1298×260 45.3 KB

1 Like