The issue is that when using the information_schema table to get the column_name column index, you have no warranty that it will output it in the same order as it will be when doing the select * from tablename. Most of the time the orders are the sames, but it's dependent on how PostgreSQL choose to handle its memory internally, so you can sometime swap the columns in the ret variable.
To prevent this, it would be better to make use of the cur.description variable, which contains all the columns from the last request in the same order they are in the result.
The following is an example of a fix using this method :
def getTableCharacteristics(self, tableName=''):
"""Returns the full contents of the table characteristics
Return value is a dict indexed by column name: <br/>
{ '<colName>':
{
'av_rows_per_vals': 3.93149,
'av_uids_per_val': 0.468698,
'column_label': 'continuous',
'column_name': 'dropoff_latitude',
'column_type': 'real',
'max': '898.29382000000000',
'min': '-0.56333297000000',
'num_distinct_vals': 24216,
'num_rows': 95205,
'num_uids': 11350,
'std_rows_per_val': 10.8547,
'std_uids_per_val': 4.09688},
}
}
"""
if len(tableName) == 0:
# caller didn't supply a table name, so get it from the
# class init
tableName = self._p['table']
# Modify table name to the default for the characteristics table
tableName += '_char'
# Establish connection to database
db = getDatabaseInfo(self._p['rawDb'])
connStr = str(
f"host={db['host']} port={db['port']} dbname={db['dbname']} user={db['user']} password={db['password']}")
conn = psycopg2.connect(connStr)
cur = conn.cursor()
# Query it for table contents
sql = str(f"SELECT * FROM {tableName}")
try:
cur.execute(sql)
except psycopg2.Error as e:
print(f"Error: getTableCharacteristics() query: '{e}'")
self.cleanUp(cleanUpCache=False, doExit=True)
ans = cur.fetchall()
columns = [desc[0] for desc in cur.description] # Get a list of the columns in the same order they are in ans
key_value_ans = [dict(zip(columns, values)) for values in ans] # Create the association between column name and values [{col1:val1, col2:val2, ...}, {col1:val1, col2:val2, ...}, ...]
return {line["column_name"]:line for line in key_value_ans} # Get the same format as we previously had
As a side note, I've found this issue here, but I'm unsure if it's something that can be found in other places in the framework, so maybe there are other places with a similar issue in the code.
(Also feel free to ask for more details if required)
Hello, I'm writing this to report an issue that I've encountered while computing utility score with the framework. It concerns this function : https://github.com/gda-score/code/blob/e131c714ddc4c08aa853b3635c2b5038d851fee9/gdascore/gdaAttack.py#L701-L769
The issue is that when using the
information_schema
table to get thecolumn_name
column index, you have no warranty that it will output it in the same order as it will be when doing theselect * from tablename
. Most of the time the orders are the sames, but it's dependent on how PostgreSQL choose to handle its memory internally, so you can sometime swap the columns in theret
variable.To prevent this, it would be better to make use of the
cur.description
variable, which contains all the columns from the last request in the same order they are in the result.The following is an example of a fix using this method :
As a side note, I've found this issue here, but I'm unsure if it's something that can be found in other places in the framework, so maybe there are other places with a similar issue in the code.
(Also feel free to ask for more details if required)