Re: [sqlalchemy] Mixing matching connectors and dialects in 0.6
Hi, I know this is a long time, but did you ever get SQLAlchemy to
successfully work with Vertica ?
Please let me know as I am interested in doing the same.
Thanks,
Femi Anthony
On Tuesday, February 2, 2010 11:37:15 AM UTC-5, Bo wrote:
Hi Michael,
Thanks for the tip on dialect creation; I have a working connection
and much of the low level library functionality appears to just
work.
I've just started digging into the internals to begin implementing
introspection support. One issue I've encountered is that PyODBC
happens to return boolean columns as strings with values 1 and 0.
This confuses the current type system. I have skimmed the
TypeDecorator documentation but am still confused about how to
associate that with this custom dialect. Any hints?
I have attached the dialect base.py and a few test cases (not sure
they're useful as Vertica is frustratingly secretive about everything)
if you have time to review and comment.
On Fri, Jan 15, 2010 at 2:57 PM, Bo Shi bs1...@gmail.com javascript:
wrote:
That's funny because Oracle and SQL server are utterly, totally
different
from a SQL quirks perspective. If I were to pick two dialects in SQLA
that were *most* different from each other and also non-standard, those
would be the two.
I was a bit puzzled by this also (granted this was from some early
press release I dredged up* from google). I'm still working through
their documentation and haven't run across any configuration that
might enable a compatability mode yet.
* improved compatibility with Oracle and SQLServer SQL dialects
http://www.vertica.com/company/news_and_events/Vertica-Analytics-Database-2.5-The-Most-Cost-Effective-Way-To-Modernize-Data-Warehousing
create_engine()
using 'mssql+pyodbc' seems to work but upon attempting to execute a
simple select statement, I get a programming error indicating the
following failed to run:
'SELECT user_name() as user_name;'
So it seems the dialect is getting some additional state under the
hood. lib/sqlalchemy/dialects/mssql/pyodbc.py appears to be hooking
PyODBCConnector and MSDialect together. Is it possible to specify
another dialect here? If so, is there any documentation on how to do
so?
you'd want to make yourself a vertica dialect module that imports the
PyODBCConnector and uses it as a mixin. I'd suggest copying one of the
existing dialects, and probably not the SQL server one unless you know
that vertica has a lot of the transact-SQL lineage that SQL server does
(the PG and SQLite dialects are the most barebones).To run it, add a
setup.py which configures your library as a setuptools entry point, in
this case the name would be vertica+pyodbc:
from setuptools import setup
setup(name=SQLAVertica,
description=...,
entry_points={
'sqlalchemy:plugins':
['vertica+pyodbc = mypackage.base:VerticaDialect']
}
then using create_engine('vertica+pyodbc://user:pw@host/dbname') will
load
in your dialect.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
sqlalchemy+...@googlegroups.com javascript:.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] is there a more proper way to chain dynamic or clauses ?
On Fri, Feb 22, 2013 at 1:31 AM, Jonathan Vanasco jonat...@findmeon.com wrote:
basd on a bunch of error messages, this example works...
criteria = ( ('male',35),('female','35) )
query = session.query( model.Useraccount )
ands = []
for set_ in criteria :
ands.append(\
sqlalchemy.sql.expression.and_(\
model.Useraccoun.gender == set_[0] ,
model.Useraccoun.age == set_[1] ,
)
)
query = query.filter(\
sqlalchemy.sql.expression.or_( *ands )
)
results= query.all()
this seems really awkward though. is there a better way to build up a
set of dynamic or criteria ?
It doesn't look too awkward to me - what sort of API would you prefer?
Note that and_ and or_ are available in the top-level sqlalchemy
namespace. Also, backslashes aren't necessary on the end of lines when
you're inside brackets, so your example could actually look like this:
import sqlalchemy as sa
criteria = (('male', 35), ('female', 35))
Useraccount = model.Useraccount
query = session.query(Useraccount)
ands = []
for gender, age in criteria:
ands.append(
sa.and_(
Useraccount.gender == gender,
Useraccount.age == age,
)
)
query = query.filter(sa.or_(*ands))
results= query.all()
It's entirely subjective, but I find that easier to read.
You can also use and | but I think you have to be a little
careful with operator precedence. You can probably write this:
ands.append((Useraccount.gender == gender) (Useraccount.age == age))
Simon
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: is there a more proper way to chain dynamic or clauses ?
thanks. i was really focused on the query.filter(sa.or_(*ands)) concept. that's what seemed kind of weird to me. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] is there a more proper way to chain dynamic or clauses ?
On 02/21/2013 07:31 PM, Jonathan Vanasco wrote:
basd on a bunch of error messages, this example works...
criteria = ( ('male',35),('female','35) )
query = session.query( model.Useraccount )
ands = []
for set_ in criteria :
ands.append(\
sqlalchemy.sql.expression.and_(\
model.Useraccoun.gender == set_[0] ,
model.Useraccoun.age == set_[1] ,
)
)
query = query.filter(\
sqlalchemy.sql.expression.or_( *ands )
)
results= query.all()
this seems really awkward though. is there a better way to build up a
set of dynamic or criteria ?
For this specific case, if your database supports it, you can use the
tuple_ construct:
criteria = (('male', 35), ('female', 35))
query = session.query(model.Useraccount)
query = query.filter(sa.tuple_(model.Useraccount.gender,
model.Useraccount.age).in_(criteria))
results = query.all()
It's cleaner and should give better index usage.
-Conor
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] is there a more proper way to chain dynamic or clauses ?
Using sa.tuple_ looks like the nicest solution here, but back to your
original question about building or clauses, expressions built with or_
can be passed as arguments to or_ to build more complex expressions. One
simple iterative way to do this is:
clauses = or_() # empty starting value
for something in criteria:
clauses = or_(clauses, next_clause)
# equivalent to: clauses = clauses | next_clause
query = query.filter(clauses)
But that's just a long-winded way to express a reduce operation*, so for
your example you could also write:
import sqlalchemy as sa
criteria = (('male', 35), ('female', 35))
Useraccount = model.Useraccount
query = session.query(Useraccount)
ands = [sa.and_(Useraccount.gender == gender, Useraccount.age == age)
for
gender, age in criteria]
or_clauses = reduce(sa.or_, ands)
query = query.filter(or_clauses)
* Yes, I know in python 3 they recommend a for loop instead of reduce, but
using it here prevents you from having to create an empty starting value,
and this way you don't have to use an inner loop to mutate a value outside
the loop on each iteration. If you might be dealing with empty ands lists
then you'd need reduce(sa.or_, ands, sa.or_()), which does lose readability.
On Fri, Feb 22, 2013 at 11:58 AM, Conor conor.edward.da...@gmail.comwrote:
On 02/21/2013 07:31 PM, Jonathan Vanasco wrote:
basd on a bunch of error messages, this example works...
criteria = ( ('male',35),('female','35) )
query = session.query( model.Useraccount )
ands = []
for set_ in criteria :
ands.append(\
sqlalchemy.sql.expression.and_(\
model.Useraccoun.gender == set_[0] ,
model.Useraccoun.age == set_[1] ,
)
)
query = query.filter(\
sqlalchemy.sql.expression.or_( *ands )
)
results= query.all()
this seems really awkward though. is there a better way to build up a
set of dynamic or criteria ?
For this specific case, if your database supports it, you can use the
tuple_ construct:
criteria = (('male', 35), ('female', 35))
query = session.query(model.Useraccount)
query = query.filter(sa.tuple_(model.Useraccount.gender,
model.Useraccount.age).in_(criteria))
results = query.all()
It's cleaner and should give better index usage.
-Conor
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
