[sqlalchemy] where clause for calculated property with alias
I guess the solution to my problem is simple, although I did not
manage to find it.
The problem is as follows: I calculate the bray-curtis distance
between an input and the rows in my table and give the value an alias
('brayCurtis'). What I want is to order the resultSet by brayCurtis
and return only those rows where the value exceeds a given threshold.
Ordering is pretty simple
query = query.order_by(query.c.brayCurtis.desc())
The problem occurs if I want to add an additional where clause like
this:
query = query.where(query.c.brayCurtis = cutoff)
Suddenly the whole statement is duplicated. Maybe there is a more
concise and elegant solution for this problem.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not loca te column in row for column
On 2008-04-18 18:01, Rick Morrison wrote: Reading this thread, I keep wondering why you are trying to put all that connection setup configuration into the connection string... Such setting are normally configured in the odbc.ini file and then you just reference data source name in the connection string. That's the standard way of using ODBC and the reason why you have ODBC managers with nice setup GUIs. A DSN-less setup like the one created by SA bypasses the ODBC manager configuration. Only via an option: DSN connections have been supported for some time via the 'dsn' keyword, the OP seems to either not want that or can't get it to work. I know, but this should be the default rather than an option. SA should really adapt to the ODBC standard of using data source names, as it moves the connection configuration where it should be: Into the scope of the ODBC manager you are using to configure your ODBC drivers. SA already has a de-facto standard using a db-uri scheme that works with non-ODBC datasources as well. It makes sense for ODBC compliant SA database drivers to conform to that form, not to just displace it and force a config-file style of setup. The DSN style setup fits in nicely with the URI scheme... just use the host part of the URI as data source name - after all, the ODBC manager will connect to the data source and not the database itself. Setting up an ODBC driver via the existing GUI tools is a lot easier and less error prone than trying to figure out all the different options and trying to put them into your connection string. The direct approach should really only be the last resort for very special cases. -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Apr 23 2008) Python/Zope Consulting and Support ...http://www.egenix.com/ mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ mxODBC, mxDateTime, mxTextTools ...http://python.egenix.com/ Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Reflecting tables, Unicodecolumnames and orm.mapper()
Hi, i was reflecting mssql tables with german umlauts in their columnsnames and got an error, mapping these tables to classes: File c:\TTL\ttl\model\__init__.py, line 24, in init_model orm.mapper(MLFB, t_mlfb) File c:\python25\lib\site-packages\sqlalchemy-0.4.4-py2.5.egg \sqlalchemy\orm\ __init__.py, line 548, in mapper return Mapper(class_, local_table, *args, **params) File c:\python25\lib\site-packages\sqlalchemy-0.4.4-py2.5.egg \sqlalchemy\orm\ mapper.py, line 162, in __init__ self._compile_properties() File c:\python25\lib\site-packages\sqlalchemy-0.4.4-py2.5.egg \sqlalchemy\orm\ mapper.py, line 601, in _compile_properties self._compile_property(column_key, column, init=False, setparent=True) File c:\python25\lib\site-packages\sqlalchemy-0.4.4-py2.5.egg \sqlalchemy\orm\ mapper.py, line 669, in _compile_property setattr(self.class_, key, Mapper._CompileOnAttr(self.class_, key)) File c:\python25\lib\site-packages\sqlalchemy-0.4.4-py2.5.egg \sqlalchemy\orm\ mapper.py, line 539, in __init__ self.existing_prop = getattr(class_, key, None) UnicodeEncodeError: 'ascii' codec can't encode character u'\xdf' in position 6: ordinal not in range(128) Is there a way to tell orm.mapper() to encode the columnnames into iso-8859-1 rather than ascii? Thanks in advance, Tobias --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: where clause for calculated property with alias
On Apr 23, 2008, at 6:55 AM, Adrian wrote:
I guess the solution to my problem is simple, although I did not
manage to find it.
The problem is as follows: I calculate the bray-curtis distance
between an input and the rows in my table and give the value an alias
('brayCurtis'). What I want is to order the resultSet by brayCurtis
and return only those rows where the value exceeds a given threshold.
Ordering is pretty simple
query = query.order_by(query.c.brayCurtis.desc())
The problem occurs if I want to add an additional where clause like
this:
query = query.where(query.c.brayCurtis = cutoff)
Suddenly the whole statement is duplicated. Maybe there is a more
concise and elegant solution for this problem.
Im not sure what cutoff is, but if its a SELECT statement, try using
cutoff.as_scalar(). This would solve the issue of extra elements
being added to the FROM clause, if thats whats happening.
But also, if cutoff is in fact a subquery, and the issue is that a
labeled column from the columns clause is not appearing as that
label in the WHERE clause, the subquery needs to be rendered at that
point. SQL doesnt officially allow order by or WHERE criterion based
on labels in the columns clause of the select statement - it will fail
on some databases, so SQLA currently doesn't place labels (i.e. column
AS foobar) anywhere outside the columns clause. A good optimizer
should figure out that the columns clause and WHERE clause are the
same thing.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not loca te column in row for column
On Apr 23, 2008, at 8:07 AM, M.-A. Lemburg wrote: The DSN style setup fits in nicely with the URI scheme... just use the host part of the URI as data source name - after all, the ODBC manager will connect to the data source and not the database itself. Setting up an ODBC driver via the existing GUI tools is a lot easier and less error prone than trying to figure out all the different options and trying to put them into your connection string. The direct approach should really only be the last resort for very special cases. Hi Marc - for those of us without access to ODBC toolsets, can you illustrate an example of how this URL would look ? Using DSN within the host section seems fine to me, as long as it can be disambiguated from other non-DSN tokens that might be present there (i.e., when using pyodbc or mxODBC, is a DSN the *only* kind of host identifier you'd ever use?) - mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: where clause for calculated property with alias
I should have mentioned that - cutoff is simply a float, e.g.
query = query.where(query.c.brayCurtis = 0.8)
that's why I think there is a trivial solution. If I do the above, the
whole query will be added as a subquery and the where and order by
clauses duplicated. It works fine for the order_by statement though.
Is there a recommended strategy for adding (where) clauses to
calculated columns? Actually what I do is that:
abs(ar1-ar2).sum() / float( abs(ar1+ar2).sum() ) # NumPy notation
the corresponding SQL query is quite verbose, that's why I used a
column label.
On Apr 23, 2:01 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 23, 2008, at 6:55 AM, Adrian wrote:
I guess the solution to my problem is simple, although I did not
manage to find it.
The problem is as follows: I calculate the bray-curtis distance
between an input and the rows in my table and give the value an alias
('brayCurtis'). What I want is to order the resultSet by brayCurtis
and return only those rows where the value exceeds a given threshold.
Ordering is pretty simple
query = query.order_by(query.c.brayCurtis.desc())
The problem occurs if I want to add an additional where clause like
this:
query = query.where(query.c.brayCurtis = cutoff)
Suddenly the whole statement is duplicated. Maybe there is a more
concise and elegant solution for this problem.
Im not sure what cutoff is, but if its a SELECT statement, try using
cutoff.as_scalar(). This would solve the issue of extra elements
being added to the FROM clause, if thats whats happening.
But also, if cutoff is in fact a subquery, and the issue is that a
labeled column from the columns clause is not appearing as that
label in the WHERE clause, the subquery needs to be rendered at that
point. SQL doesnt officially allow order by or WHERE criterion based
on labels in the columns clause of the select statement - it will fail
on some databases, so SQLA currently doesn't place labels (i.e. column
AS foobar) anywhere outside the columns clause. A good optimizer
should figure out that the columns clause and WHERE clause are the
same thing.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
Look, relax: No one is suggesting that we *eliminate* DSN-less connections, only to come up with a reasonable *default* for ODBC connection specifications. A mechanism for non-DSN connections will certainly be provided. Well, Based on : http://www.4guysfromrolla.com/webtech/070399-1.shtml These tests showed that DSN-less connections were slightly faster than System DSN connections. The increase in performance was nothing monumental; the greatest performance boost was a mere 13% faster with 64 concurrent requests. For one, two, or four concurrent requests, there was virtually no performance improvement. In fact, no noticeable improvement is seen in a DSN-less connection over a System DSN until there are 10 or more concurrent connections. Also, I don't know how things work in hosted environments but if they charge for setting up system dsn then that might be another reason to use dsn-less connection. Also porting an application that that uses dsn-less connection is easier then porting an application that requires system dsn to be setup. Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple databases ?
On Tue, Apr 22, 2008 at 11:54 AM, Lukasz Szybalski [EMAIL PROTECTED] wrote:
Hello again,
So now that I have mssql connection ready and data filtered out and
processed I need to save it to a different database.
mssql - process data - save to mysql
I am wondering how should I create a second database connection? In
second database I will create a table and populate the records.
# First database
e = sqlalchemy.create_engine(mssql://user:[EMAIL
PROTECTED]:1433/dbname?driver=TDSodbc_options='TDS_Version=8.0')
#e.echo=True
metadata=sqlalchemy.MetaData(e)
#session stuff
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, transactional=True)
session = Session()
#table stuff
class th(object):
pass
th_table = sqlalchemy.Table('', metadata, autoload=True)
mapper(th,th_table)
# database number 2. Is this the way I should create second database
connection/session/mapper?
e2 = sqlalchemy.create_engine('mysql://user:[EMAIL PROTECTED]/dbname')
Do I create new metadata?
metadata2=sqlalchemy.MetaData(e2)
And then new session2?
Session2 = sessionmaker(bind=e2, autoflush=True, transactional=True)
What does this line do?
session = Session()
Is the above correct way of doing this?
I would like to keep these connections separate so there will be no
confusion of what I am using.
Thanks,
Lucas
Just an FYI.
Setting up 2 meta data, 2 sessions worked out pretty good.
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
