On 08/22/2016 02:50 PM, Rahul Ahuja wrote:
I'm having some trouble using Python string functions on VARCHAR columns
in MySQL (reflected using automap_base() and base.prepare). I'm getting
|
AttributeError:Neither'InstrumentedAttribute'objectnor
'Comparator'objectassociated withusers.middlename has an attribute 'replace'
|
when I try
|
base_match = sess.query(md_users.id, abms_biog.id).\
filter(match().non_conflict_middlename(md_users.middlename,
abms_biog.middlename) == 1).
|
with my non_conflict_middlename function which just does some basic
string matching:
|
def non_conflict_middlename(self, a, b):
a = a.replace('.','')
b = b.replace('.','')
if ((len(a.strip()) == 0 or len(b.strip()) == 0)
or (a.replace('-','').replace(' ','') ==
b.replace('-','').replace(' ',''))
or ((len(a) == 1 or len(b) == 1) and a[0] == b[0])
or ((len(a) > 1 and len(b) > 1) and (a.find(b) + b.find(a) >
-2))
or (((' ' in a) and a[0]+a.split(' ',1)[1][0] == b) or ((' '
in b) and b[0]+b.split(' ',1)[1][0] == a))
or ((('-' in a) and a[0]+a.split(' ',1)[1][0] == b) or (('-'
in b) and b[0]+b.split(' ',1)[1][0] == a))):
return 1
else:
return 0
|
Am I missing something here? Do I need to explicitly declare the tables
and data types with declarative base or can I somehow treat columns as
Python string objects while using reflected tables via automap? Thanks
in advance for your time!
when you deal with md_users.middlename and abms_blog.middlename, based
on the context shown above these are not Python strings. They are
SQLAlchemy attributes that ultimately resolve to Column objects that
represent a lexical token within a SQL string that is to be passed to
the database. Since you are working with SQL lexical tokens, evaluation
of the data they represent happens on the database side, not the Python
side. The SQLAlchemy Column object has support for converting a tiny
subset of Python operations to their SQL counterparts, but not string
functions split, strip, and replace.
You're looking here to have all of these translations occur within the
database as it selects rows and tests each one, so in this case the
functionality of non_conflict_middlename() would have to be implemented
in SQL.
Ideally for a function this elaborate I'd probably write a custom stored
procedure. Short of that, you'd have to pull the full set of data you
want to filter into Python first, then run your Python level criteria on
that dataset in memory. This might be all you need but of course it
will not scale past a few thousand source rows at the most. If the
table is from a large dataset, you still may be able to pre-filter for a
smaller subset of rows before loading into memory, then applying extra
filtering.
--
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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
