[sqlalchemy] Re: Full connection pool close
On 24 Gen, 23:31, Rick Morrison rickmorri...@gmail.com wrote: Oh... i didn't explain myself... I mean that it's already empty at the first cycle of the loop... It would be normal to not enter the loop if you haven't yet opened any connections, as connections are opened on demand. Make sure your program issues at least one query during this test. If you are already issuing queries, then bundle up this as a simple test case as you can make, and we'll have a look at it. I was already issuing some queries... ( that's why sql server profiler tells me that there's an opened connection ). Here's a more complete example: import pyodbc pyodbc.pooling = False import sqlalchemy as sa sa_engine=sa.create_engine(dbUri, echo=False, strategy='threadlocal') sa_Session = sa.orm.scoped_session( sessionmaker( bind=sa_engine ) ) metadata = sa.MetaData(sa_engine) sa_session = sa_Session() # The table definition... could be any table anyway.. #stations = sa.Table(Stations, metadata, #sa.Column(name, sa.VARCHAR(20), primary_key=True), #sa.Column(description, sa.String(200)), #sa.Column(priority, sa.SmallInteger()), #autoload=aload) stations.select().execute().fetchall() #Sql Server Profilers tells me that a connection is opened sa_session.close() sa_Session.close_all() sa_engine.dispose() del sa_session del sa_Session del sa_engine PS: Is there any method, function, class or whatever in sqlalchemy to get all opened connection pool to the DB ? In this case my sqlalchemy connection is closed but che conn pool il still alive at the db --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL Expressions as Mapped Attributes
Thanks for that, I think I should have made my question a little clearer,
(it works for what I was asking but not what I want it to do).
spec_names = join(species_table, species_names_table,
and_(species_table.c.rank==species,
species_table.c.taxa_id==species_names_table.c.taxa))
mapper(Species, spec_names, properties={'names':column_property(
species_names_table.c.name)})
So far I have done this gives me kind of the correct result, except I get
multiple instances of a Species object back because it has multiple names.
Instead what I what to do is just create a single instance of the Species
object with a list of names in an attribute called names. How do I
accomplish this?
Also what do you mean by the correlated criterion of the column_property I
have looked for this and have been unable to find it.
Many thanks in advance,
Nathan
2009/1/26 Michael Bayer mike...@zzzcomputing.com
On Jan 26, 2009, at 12:50 PM, Nathan Harmston wrote:
Hi,
I am currently trying to use an SQL expression as a mapped attribute. I
have a table called species_table and a species_names_tables, there is a one
to many relationship between them on species_table.c.taxa_id and
species_names_table.c.taxa. So one species can have multiple names. I am
currently trying to make it so that a Species object has an attribute called
names which is a list of the names held in the species_table (there are
other attributes in this table, but I don't want any of them). So this is
what I ve tried to do:
mapper(Species, select([species_table],
species_table.c.rank=='species').alias('speciesfooalias'),
properties={'names':column_property(select([species_names_table.c.name],
species_table.c.taxa_id==species_names_table.c.taxa).label(names))})
#metadata.create_all(engine)
session = Session()
s =session.query(Species).filter(Species.taxa_id==7).one()
this is the problem, because now it says that
raise exceptions.InvalidRequestError('No rows returned for one()')
sqlalchemy.exceptions.InvalidRequestError: No rows returned for one()
however if I just do it using a simple mapper with no column_property:
mapper(Species, select([species_table],
species_table.c.rank=='species').alias('speciesfooalias'))
I get the correct output. I am using SQLAlchemy 0.4.8
I am pretty confused by this. Any help is very much appreciated.
your column_property() must be expressed in terms of the mapped selectable.
Therefore assign your select([species_table]) to a variable, map to that,
and also reference it in the correlated criterion of your column_property().
in this case, since you are mapping to a select anyway, its probably easier
and more efficient to map to a simple join of species_table and
species_names_table, including species_names_table.c.name in the list of
columns so that it is mapped directly, and forego the usage of the separate
column_propery().
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] find only loaded objects in relation collections
Hi,
I have a Subscriber and an Address table. Subscriber can have many
Addresses
mapper(Subscriber, subscriber_table, properties={
'addresses' : relation(Address, collection_class=Addresses,
backref='customer')})
From the a Subscriber object, I want to inspect all loaded objects in
any collections, but do it quietly - without causing any more to load.
class MyBase(object):
@reconstructor
def __my_init__(self):
self.rules = []
def get_all_rules_on_all_loaded_related_objects(self):
for collection in (p for p in object_mapper
(self).iterate_properties if type(p) is RelationProperty):
# How to access this collection without causing it to
load?
# I want to look at the 'rules' property on all loaded
objects
Thanks
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] get a SQL Query out of my SA mappings, possible?
I'm developing a small twisted/pyqt/sqlalchemy application (data acquisition soft and control). I have a GUI where I have to show some data from my beautiful SA model. This gui is built on PyQt. PyQt has a nice set of data access classes, but they use _raw_ SQL, that's OK for most people using Qt, since it's originally targeted for C++ developers but I'm not :( Some of my quieries have two or three inner joins, some ordering and some data formating (i.e. I have no DATETIME with microsecond precision in MySQL, so I have to store microseconds in a SmallInt, and some fields have to be shown in certain way, where I use FORMAT, RPAD, and some other SQL sting formating functions. This thing results in ten to fifteen lines of SQL between my GUI code... I don't want to have my project like this, where everything but the GUI uses SQLAlchemy for the data access, so I wonder if I could generate the SQL sentences for my table views. I'm not a SA expert, but I've found it very intuitive. What do you suggest? Nahuel --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mixing synonyms and properties with reflected tables..
Thanks very much for that detailed explanation which has proved very
helpful.
In the end it turns out that the particular construct I need is the simple:
mapper(MyClass, table, properties={
timeunits: synonym(time_units)
})
coupled with a data-descriptor for 'timeunits' on the object class which
translates to and from the database-backed attribute.
Careful reading of the docs here:
http://www.sqlalchemy.org/docs/04/sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_synonym
sheds further light. I'd like to suggest that the first and fourth
paragraph of that entry infer exactly the opposite meaning.
Paragraph 1: Set up name as a synonym to another mapped property.
Paragraph 4: name refers to the name of the existing mapped property,
which can be any other MapperProperty including column-based properties
and relations.
Paragraph 1 implies that the name passed to synonym is the name of the
synonym. This is not actually the case. It is the entity to which the
synonym refers.
Anyway.. thanks very much for your help on this. Problem solved and I'm
very grateful for the time and trouble you took, Micheal. Look forward
to another happy installation of SQLAlchemy here in London.
Regards,
Toby Bradshaw
Senior Networking Engineer,
Ideawork 3d, London, UK.
Michael Bayer wrote:
On Jan 26, 2009, at 1:29 PM, Toby Bradshaw wrote:
Michael Bayer wrote:
also your example should read like this:
a = session.query(A).all()[0]
print a.time_units
a.time_units = 1
print a.time_units
#print A.timeunits
#print A.time_units
Huh ? time_units is the column name in the database. I want to refer
to
that column through the attribute 'timeunits'. I also want to use
descriptors to do extra work when setting that attribute. The synonym
api docs say:
*def synonym(/name/, /map_column=False/, /descriptor=None/, /
proxy=False/)*
Set up name as a synonym to another mapped property.
So when I say:
mapper(A, table_A, properties = {
time_units : synonym(timeunits, map_column = True)
})
Am I not saying 'create an alias to column 'time_units' in table_A and
call it 'timeunits' ??
I think your confusion is focused on the concept of SQLAlchemy
instrumented descriptors, as it seems you're expecting the SQLAlchemy
column-mapped descriptor to wrap an existing descriptor. This is
not how it works. SQLAlchemy does not populate instance state
using getattr()/setattr(), in the default case it populates __dict__
directly. The dictionary which it uses can be wrapped with a user-
defined proxying dictionary but that's not an API you need to get
involved with.By moving all low-level operations to __dict__ and
all in-python operations to instrumented descriptors, the separation
of event-producing attribute access and direct en-masse state access
is clear, and the very high-volume activity of populating object state
is performed without the overhead of setattr() or event production.
The synonym model is provided so that a user-defined descriptor and
a SQLAlchemy-column mapped descriptor can co-exist, but instead of
being wrapped, they use different names. So usage of the model
means: one descriptor maps to the column and is entirely generated by
SQLAlchemy, the other is your custom descriptor and SQLAlchemy places
a proxy around it to provide class-level behavior like Foo.bar ==
somevalue (which you also might want to customize, but that's a
different issue). Your custom descriptor is the public face of the
attribute, and communicates with the value that exists in the database
using the column-mapped descriptor, which is usually treated as
private. Symmetric set/get behavior is provided by the user-defined
descriptor as the sole public interface.
So since you'd like to refer to the *translated* attribute as
timeunits, i think you'd want to configure this way:
mapper(MyClass, table, properties={
timeunits: synonym(time_units)
})
class MyClass(object):
...
timeunits = property(_get, _set)
mapper(MyClass, table, properties={
_timeunits:table.c.time_units,
timeunits: synonym(_timeunits)
})
which will map the original time_units column to the mapped
attribute _timeunits, and the timeunits descriptor will provide
class-level comparison behavior (i.e. MyClass.timeunits == 5). the
map_column flag does not apply here since you are naming your
descriptor something different than the original mapped column. The
column-mapped attribute _timeunits is generally treated as
private within the application space since it represents directly
the raw data that is populated/retrieved to/from the database.
you can also leave time_units mapped under its own name:
mapper(MyClass, table, properties={
mapper(MyClass, table, properties={
timeunits: synonym(time_units)
})
timeunits: synonym(time_units)
})
in which case your
[sqlalchemy] implicit vs explicit join
I have a question about implicit vs explicit joins in SA. I have three tables with an odd relationship (legacy DB issues). Assume tables 'accounts' and 'account_config' and a secondary table 'account_to_config'. The true nature of the relation is 1:1, however (again, it's an inherited schema...) I've defined the relationship thusly: On the Account object mapper: 'config': relation( AccountConfig, secondary=account_to_config, uselist=False, single_parent=True, cascade=all,delete-orphan, ) When I access the 'config' attribute of a loaded Account instance, the SQL takes the following approximate form: SELECT bunch of stuff FROM account_config, account_to_config WHERE %(param_1)s = account_to_config.account_id AND account_config.id = account_to_config.account_config_id This is an implicit join, and is equivalent to: SELECT bunch of stuff FROM account_config JOIN account_to_config ON account_config.id = account_to_config.account_config_id WHERE %(param_1)s = account_to_config.account_id My understanding is that some databases prefer the explicit JOIN syntax from an optimization POV, and of course the explicit JOIN syntax is ANSI SQL. Googlin' suggests that the ANSI syntax is preferred (perhaps even required) by some engines, but of course that's an unreliable source. I am curious why SA chooses to do it this way instead of with a JOIN. I don't think it is /WRONG/, I'm merely curious. -- Jon --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Creating Tables From Selects
(I thought this might be a FAQ, but I can't find it, and searching isn't turning up anything either...). Am I right n thinking it is not possible to do CREATE TABLE AS in SQLAlchemy? In other words, creating a table from a select statement? I'm using 0.4 and Oracle. If it is possible I would love to know how. I understand insert from select is a TODO (found in a recent post). So I guess my best bet is to just enter the entire SQL command to create the table literally?! (I am trying to duplicate some SQL that is currently run as a script; I tried replacing the table with an embedded select, which works, but is slower than expected. So I need to do this both for efficiency on one query and also because later queries are going to use the same table, so I don't want to duplicate work). Thanks, Andrew --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Creating Tables From Selects
there's nothing stopping you from just executing the SQL directly. I've never actually heard of CREATE TABLE AS before, but I just checked it out and you can certainly generate the SQL programmatically from a select, by inspecting each column in the select statement's c collection.It appears to be part of the SQL standard so is a syntax we would accept (it would be appropriate for the 0.6 series where we have CreateTable structures available). But SQLA is a toolkit and the tools are there already to automate this process without a pre-made feature. On Jan 27, 2009, at 11:17 AM, andrew cooke wrote: (I thought this might be a FAQ, but I can't find it, and searching isn't turning up anything either...). Am I right n thinking it is not possible to do CREATE TABLE AS in SQLAlchemy? In other words, creating a table from a select statement? I'm using 0.4 and Oracle. If it is possible I would love to know how. I understand insert from select is a TODO (found in a recent post). So I guess my best bet is to just enter the entire SQL command to create the table literally?! (I am trying to duplicate some SQL that is currently run as a script; I tried replacing the table with an embedded select, which works, but is slower than expected. So I need to do this both for efficiency on one query and also because later queries are going to use the same table, so I don't want to duplicate work). Thanks, Andrew --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: implicit vs explicit join
On Jan 27, 2009, at 10:14 AM, Jon Nelson wrote: My understanding is that some databases prefer the explicit JOIN syntax from an optimization POV, and of course the explicit JOIN syntax is ANSI SQL. Googlin' suggests that the ANSI syntax is preferred (perhaps even required) by some engines, but of course that's an unreliable source. I am curious why SA chooses to do it this way instead of with a JOIN. I don't think it is /WRONG/, I'm merely curious. an implicit join is certainly also ANSI SQL compliant. the JOIN keyword could be used here too but in fact historically, the JOIN keyword is the one with issues - its not supported on Oracle 8 and earlier, MySQL wants you to say INNER JOIN, etc.From a SQL rendering point of view the implicit join is easier to work with here since it is just an extension to the WHERE criterion, meaning a join on a secondary table works in various thorny situations such as EXISTS subqueries, etc. SQLA certainly has enough capability to support rendering it the other way but that explicit JOIN is preferred from an optimization pov seems like something I'd want more concrete confirmation on. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Creating Tables From Selects
On Tue, Jan 27, 2009 at 14:31, Michael Bayer mike...@zzzcomputing.com wrote: there's nothing stopping you from just executing the SQL directly. I've never actually heard of CREATE TABLE AS before, but I just checked it out and you can certainly generate the SQL programmatically from a select, by inspecting each column in the select statement's c collection.It appears to be part of the SQL standard so is a syntax we would accept (it would be appropriate for the 0.6 series where we have CreateTable structures available). But SQLA is a toolkit and the tools are there already to automate this process without a pre-made feature. I recently had to code something like that in a stored procedure (postgresql database): create TEMP table temp_boleto_guia as select (huge select); Does this CreateTable support temporary table syntax ? []s Roger --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Creating Tables From Selects
I found this to be an interesting little problem. The task can be
accomplished by using the text of a statement to construct a new
string then executing.
Here is a simple recipe using SQLite. I'm sure it can be extended
fairly easily, and should work in any standard SQL environment
import sqlalchemy
print 'SQLAlchemy version',sqlalchemy.__version__
from sqlalchemy import *
print '# set up some data'
engine = create_engine('sqlite:///', echo=False)
meta = MetaData(bind=engine)
t1 = Table('t1', meta,
Column('id', Integer, primary_key=True),
Column('data1', String),
Column('data2', String)
)
meta.create_all()
conn = engine.connect()
conn.execute(t1.insert(), [
dict(data1='r1-d1',data2='r1-d2'),
dict(data1='r2-d1',data2='r2-d2')])
print '# create new table with select'
# you could automate creating this select
stmt = select([t1.c.data1.label('data1')])
# Note: the label is important otherwise column is named t1.data1
from cStringIO import StringIO
buffer = StringIO()
print buffer, 'CREATE TABLE t2 AS'
print buffer, stmt
sql = buffer.getvalue()
print 'SQL statement is:\n',sql
conn.execute(text(sql))
print '# autoload the new table into SQLAlchemy metadata'
t2 = Table('t2', meta, autoload=True)
print '# show it is in metadata structure'
for tbl in meta.sorted_tables:
print tbl
for col in tbl.c:
print ' ',col
print '# look inside SQLite to verify table looks good'
for col in conn.execute(text('pragma table_info(t2)')):
print col
print '# select data via SQLAlchemy'
for row in conn.execute(select([t2])):
print row
On Jan 27, 12:59 pm, Roger Demetrescu roger.demetre...@gmail.com
wrote:
On Tue, Jan 27, 2009 at 14:31, Michael Bayer mike...@zzzcomputing.com wrote:
there's nothing stopping you from just executing the SQL directly.
I've never actually heard of CREATE TABLE AS before, but I just
checked it out and you can certainly generate the SQL programmatically
from a select, by inspecting each column in the select statement's c
collection. It appears to be part of the SQL standard so is a
syntax we would accept (it would be appropriate for the 0.6 series
where we have CreateTable structures available). But SQLA is a
toolkit and the tools are there already to automate this process
without a pre-made feature.
I recently had to code something like that in a stored procedure
(postgresql database):
create TEMP table temp_boleto_guia as
select (huge select);
Does this CreateTable support temporary table syntax ?
[]s
Roger
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Best way to count( some_relation )
im committing something that will make that exact phrase work (its in rev 5734). using a released version of SQLA, for now say id.in_(query.statement). I took at look at those changes and they look awesome! If I understand the changes correctly, however, then I wonder what purpose subquery() has. -- Jon --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] the return type of conn.execute(text())
Hi, Today I attempted to serialize the return value of the form result = conn.execute(text()) Till now I thought that the return type was a list of tuples, while in fact it is a list of objects of type class 'sqlalchemy.engine.base.RowProxy'. Hence cPickle refused to serialize till I did some conversion. Just wondering what the reason for this is. Regards, Faheem. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] and_ and or_ precedence
I recently ran into an issue. Is it a bug? No. However, it made my brain hurt for a little bit until I remembered the SQL precedence rules for AND and OR. The operator precedence of AND and OR is known (AND takes precedence). However, it can make humans hurt their brains a bit to see SQL without (perhaps unnecessary) parens, explicitly noting the desired order of operation. Perhaps a suggestion might be to always use parens? Approx. line 2168 of sql/expression.py: if self.group and self.operator is not against and operators.is_precedent(self.operator, against): What would the harm be in removing the final test (and operators.is_predecent...) ? It seems to me that SQLAlchemy might group things a bit more explicitly instead of relying on the precedence rules. Are there database which have precedence rules that are not the same as others (or are buggy)? -- Jon --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: the return type of conn.execute(text())
i have recently stumbled on similar - the rowproxy's __hash__ was missing. so i have to tuple() them before usage. Then there was Mike's question, what should the RowProxy emulate? the tuple of the row, or something else? Today I attempted to serialize the return value of the form result = conn.execute(text()) Till now I thought that the return type was a list of tuples, while in fact it is a list of objects of type class 'sqlalchemy.engine.base.RowProxy'. Hence cPickle refused to serialize till I did some conversion. Just wondering what the reason for this is. Regards, Faheem. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Best way to count( some_relation )
it still is needed if you explicitly need an alias() of your SELECT statement. joining to it, embedding it within aliased().haven't figured out yet if there's some way to make it automatic in all cases or if thats appropriate. also more controverially im thinking of adding a .c. to Query. but we'll see if that use case arises first. On Jan 27, 2009, at 4:34 PM, Jon Nelson wrote: im committing something that will make that exact phrase work (its in rev 5734). using a released version of SQLA, for now say id.in_(query.statement). I took at look at those changes and they look awesome! If I understand the changes correctly, however, then I wonder what purpose subquery() has. -- Jon --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: and_ and or_ precedence
On Jan 27, 2009, at 6:14 PM, Jon Nelson wrote: I recently ran into an issue. Is it a bug? No. However, it made my brain hurt for a little bit until I remembered the SQL precedence rules for AND and OR. The operator precedence of AND and OR is known (AND takes precedence). However, it can make humans hurt their brains a bit to see SQL without (perhaps unnecessary) parens, explicitly noting the desired order of operation. Perhaps a suggestion might be to always use parens? Approx. line 2168 of sql/expression.py: if self.group and self.operator is not against and operators.is_precedent(self.operator, against): What would the harm be in removing the final test (and operators.is_predecent...) ? It seems to me that SQLAlchemy might group things a bit more explicitly instead of relying on the precedence rules. Are there database which have precedence rules that are not the same as others (or are buggy)? well some databases react poorly to excessive parenthesis. Sqlite can't handle a phrase like select * from x join (y join z) for example, lots of DB's don't like to see ((a and b)) types of expressions either. if it makes you feel any better, I didn't write the full precedence code myself, came across an expression that was just A AND B OR C and i thought it was a bug for awhile. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: and_ and or_ precedence
theoretically - this might make a+b+c+d look like a+(b+(c+d)). Which isn't that bad, except that SA does not level-indent parenthesised expressions, and the sql is going to look like lisp program... but yes, u're right, SA relies on some python precedence and associativity being same as SQL ones. which may or may not be ok. as one can put brackets (a+b)+(c+d) where one needs explicitly, maybe this would only be applicable to SA-generated things, like implicit joins. But then again, if just for readability, indenting by level the parenthesises and subqueries helps much much much more. i had patch about this long ago but it doesnot work anymore... sigh. On Wednesday 28 January 2009 01:14:16 Jon Nelson wrote: I recently ran into an issue. Is it a bug? No. However, it made my brain hurt for a little bit until I remembered the SQL precedence rules for AND and OR. The operator precedence of AND and OR is known (AND takes precedence). However, it can make humans hurt their brains a bit to see SQL without (perhaps unnecessary) parens, explicitly noting the desired order of operation. Perhaps a suggestion might be to always use parens? Approx. line 2168 of sql/expression.py: if self.group and self.operator is not against and operators.is_precedent(self.operator, against): What would the harm be in removing the final test (and operators.is_predecent...) ? It seems to me that SQLAlchemy might group things a bit more explicitly instead of relying on the precedence rules. Are there database which have precedence rules that are not the same as others (or are buggy)? --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: the return type of conn.execute(text())
On Wednesday 28 January 2009 01:34:30 Michael Bayer wrote: On Jan 27, 2009, at 6:28 PM, a...@svilendobrev.com wrote: i have recently stumbled on similar - the rowproxy's __hash__ was missing. so i have to tuple() them before usage. that doesnt strike me as a similar issue. we should apply that patch someone had to detect python 2.6 and place some kind of callable for __hash__. Then there was Mike's question, what should the RowProxy emulate? the tuple of the row, or something else? RowProxy is not a buffered object in the general sense so im not thrilled making it evaluate itself for every tuple() type of access. ah, similar in the sense of some missing implicit protocol. in this case - __getstate__. nevermind, maybe document it that it's not a good thing to play with ... --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: the return type of conn.execute(text())
On Jan 27, 2009, at 6:28 PM, a...@svilendobrev.com wrote: i have recently stumbled on similar - the rowproxy's __hash__ was missing. so i have to tuple() them before usage. that doesnt strike me as a similar issue. we should apply that patch someone had to detect python 2.6 and place some kind of callable for __hash__. Then there was Mike's question, what should the RowProxy emulate? the tuple of the row, or something else? RowProxy is not a buffered object in the general sense so im not thrilled making it evaluate itself for every tuple() type of access. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: the return type of conn.execute(text())
[This message has also been posted.] On Wed, 28 Jan 2009 01:28:31 +0200, a...@svilendobrev.com a...@svilendobrev.com wrote: i have recently stumbled on similar - the rowproxy's __hash__ was missing. so i have to tuple() them before usage. Then there was Mike's question, what should the RowProxy emulate? the tuple of the row, or something else? Er, what question was that? Did I miss something? I'd like to add to my original question a request to make these objects pickleable as tuples. My understanding is that it just involves adding some method to the class. Regards, Faheem. Today I attempted to serialize the return value of the form result = conn.execute(text()) Till now I thought that the return type was a list of tuples, while in fact it is a list of objects of type class 'sqlalchemy.engine.base.RowProxy'. Hence cPickle refused to serialize till I did some conversion. Just wondering what the reason for this is. Regards, Faheem. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
