Referencing row values in pyodbc when column name contains dashes (hyphens)

Question

I am new to python and pyodbc

I try to print the first a row from a table from a progress openedge database. (Windows 7) Here is the code block that is not running:

cursor.execute("select my-nr, my-dt-my from mytable")
row = cursor.fetchone()
print(row.my-nr, row.my-dt-my)

This gives errors undefined name: 'nr' undefined name 'dt' undefined name 'my'

I guess it has something to do with the minus - symbols behind the dot . in print(row.my-nr, row.my-dt-my)

It was easy to print out the table names and column names from the database earlier but for some reason printing out rows is harder.

Any ideas how to get the rows printed?

Kiowa

I think you should fetchall() if you want more than 1 rows – Snapdragon

Answer 1

The most simple solution I can think of is this. First, columns containing hyphens need to be quoted in OpenEdge (see here). Second, you can alias the columns so they can be referenced as valid Python attributes. You'll need to do something like this:

cursor.execute('select "my-nr" as mynr, "my-dt-my" as mydtmy from mytable')
row = cursor.fetchone()
print(row.mynr, row.mydtmy)

Good luck!

Embus

Answer 2

pyodbc allows us to reference values in a pyodbc.Row object using the form row.column_name provided that the column names are legal Python identifiers. So, for example, we can do something like

row = crsr.fetchone()
print(row.city)

to print the value of the "city" column. Unfortunately, my-nr is not a legal Python identifier so if we try to print the value of the "my-nr" column using ...

row = crsr.fetchone()
print(row.my-nr)  # error

... Python parses that as "row.my minus nr" where row.my would be interpreted as a column in the Row object and nr would be interpreted as a Python variable.

To work around the issue we can grab a list of the column names, merge those names with the row values into a dictionary, and then refer to the values in the dictionary:

crsr.execute(sql)
col_names = [x[0] for x in crsr.description]

row = crsr.fetchone()
row_as_dict = dict(zip(col_names, row))
print(row_as_dict['my-nr'])  # no error

Deodand

This is a very good implementation and is versatile enough to fill many scenarios. In addition, this method gets my vote because it doesn't require me to go back and rename my columns to be more friendly towards python. I understand that if a person is using only a handful of columns then renaming the columns may be quicker; I tend to use 20-50 columns at a time which means going back to rename them ends up being a task in itself. – Rodriques

Answer 3

I beleive that you need to change the variable names of the database, make sure they don't contain any '-' characters. Variables can not contain characters reserved by python. For example you have to avoid hyphens(-), exclamation marks (!), colons (:) and so on.

According to this answer it seems like underscore (_) is the only character allowed in variable names.

Libove