Greetings,
I have two questions to the following code that works, but looks dirty to
me.
Being new to SQLAlchemy I'd like to ask for advices.
The database is PostgreSQL.
The following check_xyz function must check whether the index
"table_xyz_idx" exists in table "table".
The table surely already exist, but the index could not.
The index uses some features unique to PostgreSQL:
* it is a gin index
* it uses the special operator class gin_trgm_ops,
* it is composed by a concatenation of two fields and a string.
(The index definition is in the docstring below)
First question:
I use the copy.copy to check whether the index exists in the table.indexes
set.
I need to copy the table.indexes set because as soon as the index is
defined (yes, it refers the table columns), it's added to "table.idexes",
even though the index hasn't been created yet.
So: is there a way to avoid the copy.copy?
Second question:
The "postgresql_ops" parameter of the Index class [1] requires that the
keys of the dict must equal the .key attributes of the columns/expressions
used to define the index.
In my case the column is an expression, so it has not ".key" attribute.
So: is there a SQLAlchemy class/construct like:
X(name, expression)
so that I can write:
index = Index(
"go_anagrafica_nominativo_idx",
X("xyz", table.c.field1.concat(text("'
'")).concat(table.c.field2)),
postgresql_ops={"xyz": "gin_trgm_ops"},
postgresql_using="gin")
[1]
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=postgresql_ops#operator-classes
--- the code ---
[...]
def check_xyz(self):
"""
CREATE EXTENSION pg_trgm;
CREATE INDEX CONCURRENTLY table_xyz_idx
ON s.table
USING gin((field1 || ' ' || field2) gin_trgm_ops)
"""
# self.execute is a method of the calss that executes multiple
# raw commands in a single transaction.
self.execute(("CREATE EXTENSION IF NOT EXISTS pg_trgm",))
table = Table(
"table",
self.metadata,
schema="s",
autoload=True)
# FIRST DOUBT
indexes = copy.copy(table.indexes)
# SECOND DOUBT
expression = table.c.field1.concat(text("'
'")).concat(table.c.field2)
expression.key = "xyz"
index = Index(
"table_xyz_idx",
expression,
postgresql_ops={"xyz": "gin_trgm_ops"},
postgresql_using="gin")
if index not in indexes:
index.create()
--
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.
