sqlalchemy + postgresql hstore to string

Question

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()

The compilation error is due to SQLA using the default dialect, which you can avoid with explicitly compiling against the Postgresql dialect: hs.compile(dialect=postgresql.dialect()), but that leaves you with an SQL expression that has placeholders (as it should), not the values converted to SQL. But what is your actual problem? Why cannot you use that hstore construct with Core inserts (assuming that is what you're after)? What are you trying to accomplish? — Ilja Everilä
– Ilja Everilä, Commented Aug 31, 2017 at 21:22

Ilja Everilä · Accepted Answer · 2017-08-31 22:10:18Z

1

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.

edited Aug 31, 2017 at 22:10

answered Aug 31, 2017 at 21:44

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

clay Over a year ago

Can I do that with sql without a my_table ORM construction?

Ilja Everilä Over a year ago

To be exact that's Core, not the ORM, but if you're feeling particularly adventurous, then yes. Dig up the real psycopg2 connection (if you're using it) underlying the SQLAlchemy connection, use psycopg2.extras.register_hstore(db_connection.connection.connection) on it, and pass your hstore value as a dictionary instead of an SQLAlchemy hstore(), with required casts added to your textual SQL.

Collectives™ on Stack Overflow

sqlalchemy + postgresql hstore to string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related