Getting error while fetching the column values from cursor object in snowflake

userqpN61 0
Viewed 48

I am trying to fetch the column values from Cursor object and use them into select statement with aggregate function and group by clause and put the data into another table but getting error.

Code

sql10 = f"""SELECT col1,col2,col3,col4 FROM tablename ORDER BY col4 ;"""
select_snow =cs.execute(sql10).fetchall()
snow_col = [(c[1],c[2]) for c in select_snow]
cursor.fast_executemany = True
sql17 = f"INSERT INTO newtable(col11,col12) (SELECT ?,MAX(?) FROM select_snow GROUP BY ?);"

Error

"ERROR: Unexpected error! - ('Expected 3 parameters, supplied 2', 'HY000')"

snowflake-schema snowflake-cloud-data-platform
1 Answers

You can retrieve the column metadata using description method:

cur = conn.cursor()
cur.execute("SELECT * FROM test_table")
print(','.join([col[0] for col in cur.description]))

Have a look here for more information.

For your particular case:

query = """
select RIDE_ID,RIDEABLE_TYPE,STARTED_AT,ENDED_AT,START_STATION_NAME,END_STATION_NAME from citibike_trips order by ride_id limit 1;
"""

try:
    select_snow=cs.execute(query).fetchall()
    print(select_snow)
    print(','.join([col[0] for col in cs.description]))
finally:
    cs.close()

I get back:

[('000008096013E2D5', 'docked_bike', datetime.datetime(2021, 4, 21, 18, 59, 49), datetime.datetime(2021, 4, 21, 19, 11, 31), 'E 39 St & Lexington Ave', 'Christopher St & Greenwich St')]
RIDE_ID,RIDEABLE_TYPE,STARTED_AT,ENDED_AT,START_STATION_NAME,END_STATION_NAME

You can see that select_snow just holds values so you can't get the column names out of it. You need to use the description method.

Sergiu0
Related