On 10/1/15 11:41 AM, Massimiliano della Rovere wrote:
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?
What is happening there with copy.copy and "if index not in indexes"
does not do anything. You are creating a new Index object, it is
guaranteed to not be in the table.indexes collection before you
construct it, even if its name and definition matches exactly that of
another Index object that is already in the collection; you'd have two
of the same index and the CREATE will then fail due to name conflict.
The Index object is not hashed on its definition or anything like that.
if you'd like to see if an index of a particular name already exists,
you can iterate through the names:
if "table_xyx_idx" in [idx.name for idx in table.indexes]:
# ...
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.
give your expression a simple label and this should be recognized
expr = table.c.foo.concat(table.c.bar).label('my_expr')
postgresql_ops={"my_expr", "gin_trgm_ops"}
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
<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.
