How do I convert an sqlalchemy hstore value to a string?
from sqlalchemy.dialects.postgresql import array, hstore
hs = hstore(array(['key1', 'key2', 'key3']), array(['value1', 'value2', 'value3']))
# this triggers sqlalchemy.exc.UnsupportedCompilationError
str(hs)
I expect something like "key1"=>"value1", "key2"=>"value2", "key3"=>"value3"
I would like to use an sqlalchemy api rather than write a custom string formatting function that approximates what I want. I'm working with a legacy code base that uses sqlalchemy: I need to preserve any internal quirks and escaping logic that formatting does.
However, the existing code base uses sqlalchemy via an ORM table insert, while I want to directly convert an sqlalchemy hstore value to a string?
UPDATE: I am trying to do something like this:
I have an existing table with schema
create table my_table
(
id bigint default nextval('my_table_id_seq'::regclass),
ts timestamp default now(),
text_col_a text,
text_col_b text
);
I want to get the following Python sqlalchemy code working:
str_value = some_function()
# Existing code is building an sqlalchemy hstore and inserting
# into a column of type `text`, not an `hstore` column.
# I want it to work with hstore text formatting
hstore_value = legacy_build_my_hstore()
# as is this triggers error:
# ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'hstore'
return db_connection.execute(
"""
insert into my_table(text_col_a, text_col_b) values (%s, %s)
returning id, ts
""",
(str_value, hstore_value).first()
Let Postgresql do the cast for you instead of trying to manually convert the hstore construct to a string, and SQLAlchemy handle the conversion to suitable text representation:
return db_connection.execute(
my_table.insert().
values(text_col_a=str_value,
text_col_b=cast(hstore_value, Text)).
returning(my_table.c.id, my_table.c.ts)).first()
As soon as you can, alter your schema to use hstore type instead of text, if that is what the column contains.