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

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?

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

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

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

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

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)

1 Like