On 7/2/14, 11:38 AM, Phillip Aquilina wrote:
> This worked as described. Thanks again. I have a followup question.
> It doesn't seem like there's an analog to
> table.create(checkfirst=True) for an Index. I found this issue
> <https://bitbucket.org/zzzeek/sqlalchemy/issue/527/indexcreate-should-take-checkfirst>
> that seems to mention having this functionality but it doesn't look
> like it's been implemented? Is there a normal workaround for this?
typically Index is bundled with its parent Table, and the conditional
aspect of it comes from the Table being created conditionally.
Otherwise, if the Index is added after the fact, typically people are
using migration tools to get that so that's where the conditional aspect
comes in. So the case where Index.create() really needs conditional
behavior is slim. You can for now use inspector:
from sqlalchemy import inspect
insp = inspect(engine)
for idx in insp.get_indexes('tablename'):
if idx['name'] == 'myname':
break
else:
Index('myname', x, y, z).create(engine)
>
> On Tuesday, July 1, 2014 10:03:40 AM UTC-7, Phillip Aquilina wrote:
>
> Ah! I'll give that a try. Thanks Mike.
>
> On Monday, June 30, 2014 10:23:13 PM UTC-7, Michael Bayer wrote:
>
> per the SO answer, you're looking for "CREATE INDEX ON
> publishers((info->>'name'));". Either you can emit this
> directly as a string, or use Index, just as it states:
>
> from sqlalchemy import create_engine, Integer, Index, Table,
> Column, MetaData
> from sqlalchemy.dialects.postgresql import JSON
>
> e = create_engine("postgresql://scott:tiger@localhost/test",
> echo=True)
>
> m = MetaData()
> publishers = Table('publishers', m, Column('id', Integer),
> Column('info', JSON))
> Index("foo", publishers.c.info
> <http://publishers.c.info>['name'].astext)
>
> m.create_all(e)
>
> output:
>
> CREATE TABLE publishers (
> id INTEGER,
> info JSON
> )
>
>
> CREATE INDEX foo ON publishers ((info ->> 'name'))
>
>
>
>
>
> On 7/1/14, 1:14 AM, Mike Bayer wrote:
>> I'm not familiar with any other style of index for this
>> column type. If you can show me at
>> http://www.postgresql.org/docs/9.4/static/datatype-json.html
>> <http://www.postgresql.org/docs/9.4/static/datatype-json.html> or
>> wherever what specific DDL you're looking for, you can simply
>> emit it using engine.execute("<ddl>").
>>
>>
>> On 6/30/14, 11:02 PM, Phillip Aquilina wrote:
>>> Thanks for replying. I've read through that doc and I still
>>> don't see how that addresses my question. Is there somewhere
>>> in there that describes how to create an index on a json
>>> field? It seems like to me it's simple to create an index
>>> on a column but this would be creating an index on nested
>>> data inside the column.
>>>
>>> - Phil
>>>
>>> On Monday, June 30, 2014 6:07:51 PM UTC-7, Michael Bayer wrote:
>>>
>>> SQLAlchemy's API allows CREATE INDEX via the Index
>>> construct:
>>>
>>> http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes
>>>
>>> <http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes>
>>>
>>>
>>> On 6/30/14, 6:21 PM, Phillip Aquilina wrote:
>>>> Using postgresql, I have a JSON type column. My
>>>> understanding from their docs was that only jsonb
>>>> columns could have an index created on them (a feature
>>>> of postgresql 9.4) but then I found an SO answer
>>>>
>>>> <http://stackoverflow.com/questions/17807030/how-to-create-index-on-json-field-in-postgres-9-3>
>>>> that
>>>> said otherwise. I haven't had the chance to test it
>>>> since I'm away from my dev environment, but the
>>>> sqlalchemy docs seem to support this idea
>>>>
>>>> <http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON>,
>>>> mentioning the availability of "Index operations."
>>>>
>>>> Unless I'm missing something obvious (very possible),
>>>> it seems like this can be done through sql, but is
>>>> there a way to create an index on a json field through
>>>> the sqlalchemy api? I can't seem to find a way to do this.
>>>>
>>>> Thanks,
>>>> Phil
>>>> --
>>>> You received this message because you are subscribed to
>>>> the Google Groups "sqlalchemy" group.
>>>> To unsubscribe from this group and stop receiving
>>>> emails from it, send an email to
>>>> sqlalchemy+...@googlegroups.com.
>>>> To post to this group, send email to
>>>> sqlal...@googlegroups.com.
>>>> Visit this group at
>>>> http://groups.google.com/group/sqlalchemy
>>>> <http://groups.google.com/group/sqlalchemy>.
>>>> For more options, visit
>>>> https://groups.google.com/d/optout
>>>> <https://groups.google.com/d/optout>.
>>>
>>> --
>>> You received this message because you are subscribed to the
>>> Google Groups "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails
>>> from it, send an email to sqlalchemy+...@googlegroups.com.
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>> Visit this group at
>>> http://groups.google.com/group/sqlalchemy
>>> <http://groups.google.com/group/sqlalchemy>.
>>> For more options, visit https://groups.google.com/d/optout
>>> <https://groups.google.com/d/optout>.
>>
>> --
>> You received this message because you are subscribed to the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from
>> it, send an email to sqlalchemy+...@googlegroups.com.
>> To post to this group, send email to sqlal...@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy
>> <http://groups.google.com/group/sqlalchemy>.
>> For more options, visit https://groups.google.com/d/optout
>> <https://groups.google.com/d/optout>.
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+unsubscr...@googlegroups.com
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
