[sqlalchemy] Pool size question

[Mike Orr]

I have a few Pylons applications that share a SQL access log routine.
Yesterday I migrated it from MySQL to PostgreSQL, and I'm getting a
bunch of errors like this:

  connection = self.contextual_connect(close_with_result=True)
Module sqlalchemy.engine.base:1229 in contextual_connect
  return self.Connection(self, self.pool.connect(), 
 close_with_result=close_with_result, **kwargs)
Module sqlalchemy.pool:142 in connect
  return _ConnectionFairy(self).checkout()
Module sqlalchemy.pool:304 in __init__
  rec = self._connection_record = pool.get()
Module sqlalchemy.pool:161 in get
  return self.do_get()
Module sqlalchemy.pool:631 in do_get
  raise exc.TimeoutError(QueuePool limit of size %d overflow %d reached, 
 connection timed out, timeout %d % (self.size(), self.overflow(), 
 self._timeout))
TimeoutError: QueuePool limit of size 5 overflow 10 reached,
connection timed out, timeout 30

I assume this means I should set ``create_engine(pool_size=,
max_overflow=)`` to some large number. But what would be a good
starting point if it's currently at the default?  25? 50?  The sites
are like this:


Site #1: Highest traffic.  Failing at 6 requests/minute according to
the error reports in my inbox. Using Postgres only for the stats, but
soon will be using it for site stuff too (which is working fine with
SQLite).

Site #2: Second-highest traffic. Using Postgres both for the stats and
the site. No problems seen yet.

Site #3: The most critical site, but moderate traffic. Using
PostgreSQL for both the stats and the site. One user complaint.

Site #4: Small site, little used. Using PostgreSQL only for stats.

Each site has a separate engine for its site stuff and for the stats
(so one or two engines per site, going to different databases).

For now I'm going to set 'max_overflow=15' and see if that fixes it.

-- 
Mike Orr sluggos...@gmail.com

-- 
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: Pool size question

[Mike Orr]

On Fri, Oct 28, 2011 at 9:53 AM, Mike Orr sluggos...@gmail.com wrote:
 I have a few Pylons applications that share a SQL access log routine.
 Yesterday I migrated it from MySQL to PostgreSQL, and I'm getting a
 bunch of errors like this:

  connection = self.contextual_connect(close_with_result=True)
 Module sqlalchemy.engine.base:1229 in contextual_connect
  return self.Connection(self, self.pool.connect(), 
 close_with_result=close_with_result, **kwargs)
 Module sqlalchemy.pool:142 in connect
  return _ConnectionFairy(self).checkout()
 Module sqlalchemy.pool:304 in __init__
  rec = self._connection_record = pool.get()
 Module sqlalchemy.pool:161 in get
  return self.do_get()
 Module sqlalchemy.pool:631 in do_get
  raise exc.TimeoutError(QueuePool limit of size %d overflow %d reached, 
 connection timed out, timeout %d % (self.size(), self.overflow(), 
 self._timeout))
 TimeoutError: QueuePool limit of size 5 overflow 10 reached,
 connection timed out, timeout 30

 I assume this means I should set ``create_engine(pool_size=,
 max_overflow=)`` to some large number. But what would be a good
 starting point if it's currently at the default?  25? 50?  The sites
 are like this:


 Site #1: Highest traffic.  Failing at 6 requests/minute according to
 the error reports in my inbox. Using Postgres only for the stats, but
 soon will be using it for site stuff too (which is working fine with
 SQLite).

 Site #2: Second-highest traffic. Using Postgres both for the stats and
 the site. No problems seen yet.

 Site #3: The most critical site, but moderate traffic. Using
 PostgreSQL for both the stats and the site. One user complaint.

 Site #4: Small site, little used. Using PostgreSQL only for stats.

I checked the requests per hour since yesterday afternoon.

Site #1 stopped working at 6pm and has no requests logged until I
restarted it. Still, I got six of these exceptions every minute this
morning. And I could connect to the db via psql fine. On what occasion
does SQLA raise this error? Is it opening a new connection? Does it
try once, or try multiple times before it times out?  Should I set
'pool_recycle'?

Site #2 has between 590 - 1255 requests per hour in that time.

Site #3 has max 17 requests per hour. And I clarified that the user
may not have gotten an error on this site, I may have misunderstood
his report.

Site #4 has 0 requests per hour, which is typical.

-- 
Mike Orr sluggos...@gmail.com

-- 
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] Strange LIKE behavior with TypeDecorator

[Mike Orr]

I have the following TypeDecorator type to store a tuple of strings as
a delimited string.  It works fine but I discovered an abnormality
with LIKE.  The right side of a like expression is being passed to the
converter, so it has to be a one-item tuple instead of a string.  This
makes my model unintuitive.  Am I doing something wrong or is this
just a corollary of how TypeDecorator works?


 m.Chemical.synonyms.like((like,)) # Ugly

 q = q.filter(m.UN.synonyms.like((% + term + %,))) # Ugly


class MultiText(sa.types.TypeDecorator):
Store a tuple of string values as a single delimited string.

Legal values are a tuple of strings, or ``None`` for NULL.
Lists are not allowed because SQLAlchemy can't recognize in-place
modifications.

Note that during SQL queries (e.g., column LIKE %ABC%), the
comparision is against the delimited string.  This may cause unexpected
results if the control value contains the delimeter as a substring.


impl = sa.types.Text

def __init__(self, delimiter, *args, **kw):
Constructor.

The first positional arg is the delimiter, and is required.

All other positional and keyword args are passed to the underlying
column type.

if not isinstance(delimiter, basestring):
msg = arg ``delimiter`` must be string, not %r
raise TypeError(msg % delimiter)
self.delimiter = delimiter
sa.types.TypeDecorator.__init__(self, *args, **kw)

def process_bind_param(self, value, dialect):
Convert a tuple of strings to a single delimited string.

Exceptions:
``TypeError`` if the value is neither a tuple nor ``None``.
``TypeError`` if any element is not a string.
``ValueError`` if any element contains the delimeter as a substring.

if value is None:
return None
if not isinstance(value, tuple):
msg = %s value must be a tuple, not %r
tup = self.__class__.__name__, value
raise TypeError(msg % tup)
for i, element in enumerate(value):
if self.delimiter in element:
msg = delimiter %r found in index %d of %s: %r
tup = (self.delimiter, i, self.__class__.__name, value)
raise ValueError(msg % tup)
return self.delimiter.join(value)

def process_result_value(self, value, dialect):
Convert a delimited string to a tuple of strings.
if value is None:
return None
elif value == :
return ()
elements = value.split(self.delimiter)
return tuple(elements)

def copy(self):
return self.__class__(self.delimiter, self.impl.length)




-- 
Mike Orr sluggos...@gmail.com

--~--~-~--~~~---~--~~
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: unit testing idioms

[Mike Orr]

On Wed, Jun 24, 2009 at 3:11 PM, Chris Withersch...@simplistix.co.uk wrote:

 Hi All,

 I'm wondering what the common idiom is for unit testing w.r.t. data and
 transactions...

 So, coming from my ZODB background, in unit tests we usually:

 - set up the objects required
 - run the code to be tested (which might change, add or delete objects)
 - abort the current transaction so no changes are saved and everything
 goes back to how it was prior to the start of the test

 What's the equivalent in sqlalchemy-land?

 How do I get test data needed for a specific test to exist for only that
 test?

 How do I abort changes made by a test in a unittest.TestCase sublcasses
 tearDown method?

 What do I do if the app framework I'm using goes and does a commit as
 part of the unit test? (I wish it wouldn't... ;-) )

 (this is in the context of a Pylons app if that makes things easier/harder)

You generally want to use a test database rather than depending on a
rollback to preserve the live data, because who knows when something
might go wrong (e.g.,, a stray commit you didn't notice).  You can
define an alternate database in test.ini.

You can create and populate the database in the setUp function Nose
runs, and drop the tables in the tearDown function.  These functions
can be run for every test or only on on entering/exiting the module,
depending on how you name them them.

Some people create their database in websetup.py, which I think is run
during the default test configuration in Pylons.  This has its
advantages and disadvantages, because setup-app is a blunt instrument
that can only do one thing, whereas in some situations you may only
want to do part of that thing.  Beware that if you have configured
websetup.py this way, it will be run for every test unless you disable
that.

If your data is mainly added to rather than modified in place, you may
want to test against real data collected a month or two ago.  In that
case you may want the real database or a copy of it, but the database
may be too large to create/import multiple times during a test.  In
that case it gets a bit more difficult, plus you actually have to
import the data from somewhere rather than inserting fixed dummy data.
 Perhaps in this case you'll want to load it from a SQL dump file made
earlier, or a tab-delimited file.

If you put only your read-only tests in the Pylons app and keep your
read-write tests elsewhere, then it would be safer to run the
read-only tests against the live database.

-- 
Mike Orr sluggos...@gmail.com

--~--~-~--~~~---~--~~
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] Why do I have to begin a transaction?

[Mike Orr]

I have a standalone utility using a model based on Declarative on
MySQL using SQLAlchemy 0.5.2.  Most of the code is at the SQL level
but at one point I use the ORM to update or insert a summary record.
So I figured I'd use create_session because it's a single-threaded
utility.

===
sess = orm.create_session(bind=conn)
q = sess.query(model.Monthly).filter_by(...)
monthly = q.first()
if not monthly:
monthly = model.Monthly()
...
sess.add(monthly)
...
sess.commit()
===

That raises sqlalchemy.exc.InvalidRequestError: No transaction is
begun.  To work around that I have to put sess.begin() after
creating the session.  But why?  I don't have to do this when using
scoped_session in Pylons.  The SQLAlchemy docs seem to say that it
automatically manages transactions if you don't change the default
session arguments, and that this works identically with Session,
create_session, and sessionmaker.  So why is it behaving differently
here?

-- 
Mike Orr sluggos...@gmail.com

--~--~-~--~~~---~--~~
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] Long blobs

[Mike Orr]

I have a 113656-byte pickle I'm trying to put into a blob column in a
way that will work for both SQLite and MySQL.  SQLite has no problem
with it, but in MySQL I have to use the MSMediumBlob type because it
exceeds 65536 bytes.  But I'd like the same table to work with both
engines.  Is this possible?

I'm using a CompressedPickle class that looks like this:

class CompressedPickle(sa.types.TypeDecorator):
impl = sa.types.PickleType

def process_bind_param(self, value, dialect):
value = pickle.dumps(value, -1)
value = zlib.compress(value, 9)
return value

def process_result_value(self, value, dialect):
value = zlib.decompress(value)
value = pickle.loads(value)
return value

def copy(self):
return CompressedPickle(self.impl.length)




-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: SQLAlchemy 0.4.2b released

[Mike Orr]

On Jan 8, 2008 1:13 AM, Michael Bayer [EMAIL PROTECTED] wrote:

 the problem with 0.4.3, 0.4.4 etc. is that we assign those numbers as
 milestones in trac, and we do have a notion of a set of features that
 will be slowly rolled out over the course of the 0.4 series.

[nod]  That makes sense if you're assigning versions that way.
Actually, it looks like Pylons is the same.  I thought it was a
three-level system but the current version is 0.9.6.1.

 as far as the 0., im really glad that the 0.1 series wasnt called
 SQLAlchemy 1.0 , as well as that 0.2 wasnt SQLAlchemy 2.0, etc.
 0.1 and 0.2 were absolutely not major-version number products. 0.4 is
 starting to look more major versioned to me, but if we went thru 0.4
 and then jumped to 1.0, that would seem kind of brittle as well.

Well, 1.0 also implies a long-term commitment to the API, so you don't
want to do it while you're still deciding what you want.  I'm glad 0.3
wasn't called 1.  0.4 and its documentation are close to the quality
of a 1.0 release.  But you know better than I what SQLAlchemy 1.0
should contain.  Do you have a specific set of features you want in
it?  Or are you just going to wait until the changes slow down and
then make that version 1.0?

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: SQLAlchemy 0.4.2b released

[Mike Orr]

On Jan 7, 2008 12:20 PM, Michael Bayer [EMAIL PROTECTED] wrote:
 This is a bugfix release and is recommended for all users who are
 currently on 0.4.2 or 0.4.2a.

You really should bump the version number after a version has been
released.  'a' and 'b' look like alpha and beta.

I installed 0.4.2 this morning, and afterward easy_install -U
SQLAlchemy says I already have the latest version.  I had to install
it specifically:  easy_install SQLAlchemy==0.4.2b.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Matching a DateTime-field

[Mike Orr]

Can't you use a BETWEEN or = and  with two dates?

If your date column is indexed, as it should be if you're using it
frequently in where clauses, the overhead of DATE_FORMAT decreases
substantially.

On Dec 12, 2007 3:43 PM, Adam B [EMAIL PROTECTED] wrote:



 On Dec 11, 10:55 am, King Simon-NFHD78 [EMAIL PROTECTED]
 wrote:
  It may not matter to you, but I wouldn't have thought this would be a
  very efficient query, because the database is going to have to call the
  DATE_FORMAT function twice for every row in your table. I would have
  thought a more efficient version would be one that asks for all rows
  between the first of one month and the first of another month
  (especially if the date column is indexed).
 
  Something like:
 
  from datetime import date
  session.query(List).filter(
  and_(List.expire = date(2007, 12, 1),
   List.expire  date(2008, 1, 1))
  ).all()
 
  Adding one month to a date is pretty easy, but if you wanted to do any
  more complicated date calculations, the dateutil library is very good:
 
  http://labix.org/python-dateutil

 Ah yes, was so obsessed with the solution. Letting the mysql work is
 much
 more efficient. It will matter under heavy load.

 I will check out dateutil, thanks.

 br

 Adam



 




-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: LIMIT in queries

[Mike Orr]

On Dec 12, 2007 6:20 AM, King Simon-NFHD78 [EMAIL PROTECTED] wrote:

 Felix Schwarz wrote:
  Hi,
 
  after reading the docs [1] I thought that something like
  session.query(User).filter(User.c.id  3)[0]
  should work even if the filter clause does not return any rows.
 
  But - compliant with Python's behavior - SQLAlchemy raises an
  IndexError.
  (...)
 File
  /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py,
  line 577, in __getitem__
   return list(self[item:item+1])[0]
  IndexError: list index out of range
  I expected that [0] applied to query without results would
  return None.
 
  Did I miss another possibility for LIMIT'ing queries (using
  sqlalchemy.orm)?
 
  fs
 
  [1]
  http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping
  _querying

 I think being matching Python's behaviour is definitely the right way to
 go. If you want to get None specifically for index [0], you can use the
 'first' method on query.

 If you are happy get an empty list, you could use a slice and then call
 .all()

The issue is that [0] in Python superficially looks like [A:B] but is
in fact something different.
[A:B] was added to SQLAlchemy to represent .offset(A).limit(B-A).  [0]
came along for the ride but has incompatible semantics when the record
does not exist.  I guess I would favor None because
[N]'s closest equivalent is .fetchone() which does that, and if they
really wanted [] they should have done
[:1].

Raising IndexError is the native Python equivalent, but the whole
concept of operator overloading is that we do what makes sense for the
object.  A query is not a list.  If we define [N] as skip N-1 records
and do .fetchone(), that's defensible.  The only people who will be
bothered are existing programs that are expecting IndexError.  Are
there that many of those, given that I don't think [N] is even
documented?

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: LIMIT in queries

[Mike Orr]

On Dec 14, 2007 1:31 PM, Felix Schwarz [EMAIL PROTECTED] wrote:
 Mike Orr wrote:
  Are
  there that many of those, given that I don't think [N] is even
  documented?

 It is in the official documentation:

 http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_querying

OK.  But the doc says nothing about what happens if the record doesn't exist.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Type of calculated columns

[Mike Orr]

On Nov 30, 2007 9:28 PM, Michael Bayer [EMAIL PROTECTED] wrote:

 yes...add type_=DateTime to your coalesce() call -
 func.coalesce(date1, date2, type_=DateTime)

This doesn't work, I'm afraid.

# Table t_incident defined with
sa.Column(orr_id, sa.types.Integer, primary_key=True),
sa.Column(last_entry_date, sa.types.DateTime, nullable=True),
sa.Column(create_date, sa.types.DateTime, nullable=False),

# Standalone column definition
c_activity = sa.func.coalesce(
t_incident.c.last_entry_date,
t_incident.c.create_date,
type_=sa.types.DateTime,
).label(activity)


 sql = sa.select([tables.t_incident.c.orr_id, tables.c_activity,
tables.t_incident.c.create_date, tables.t_incident.c.last_entry_date],
limit=1)

 row = model.engine.execute(sql).fetchone()

 row
(6001L, '2007-05-30 23:24:46', datetime.datetime(2005, 9, 6, 17, 14,
34), datetime.datetime(2007, 5, 30, 23, 24, 46))

The second element should be a datetime rather than a string.

Using SQLAlchemy 0.4.1, Pylons dev, Python 2.5.1, Kubuntu Linux 2007.10

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Type of calculated columns

[Mike Orr]

This is on MySQL 5.0.45, BTW.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Type of calculated columns

[Mike Orr]

On Dec 3, 2007 1:33 PM, Michael Bayer [EMAIL PROTECTED] wrote:


 On Dec 3, 2007, at 4:05 PM, Mike Orr wrote:

 
  On Nov 30, 2007 9:28 PM, Michael Bayer [EMAIL PROTECTED] wrote:
 
  yes...add type_=DateTime to your coalesce() call -
  func.coalesce(date1, date2, type_=DateTime)
 
  This doesn't work, I'm afraid.

 oh...well actually in this case its because MSDateTime doesn't do any
 date/time conversion, since mysqldb returns datetime objects for us
 and we dont need to convert from strings...we only do it for sqlite
 right now.  if MySQL itself is returning a datetime, then MySQLdb
 should as well, is it possible this is a bug on the MySQLdb side ?

It looks like that's the case.

 import MySQLdb
 conn = MySQLdb.connect(...)
 c = conn.cursor()
 c.execute(SELECT orr_id, COALESCE(last_entry_date, create_date)
AS activity, last_entry_date, create_date FROM Incident LIMIT 1)
1L
 c.fetchone()
(6001L, '2007-05-30 23:24:46', datetime.datetime(2007, 5, 30, 23, 24,
46), datetime.datetime(2005, 9, 6, 17, 14, 34))

I looked in my last non-SA application, and it's using the coalesce
only for the select, and formatting the display date from the
underlying fields.  So I guess that's the best we can get unless I
want to parse the string back into a date.  I guess this would be a
good case for an ORM property since it's read-only.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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] Type of calculated columns

[Mike Orr]

I have a calculated column that takes the first non-NULL value among
three dates.  It should remain a datetime object but instead it's
being converted to a string.  Is it possible to tell SQLAlchemy to
treat this value like a DateTime column?

# Table t_incident defined with
sa.Column(last_entry_date, sa.types.DateTime, nullable=True),
sa.Column(create_date, sa.types.DateTime, nullable=False),

# Standalone column definition
c_activity = sa.func.coalesce(
t_incident.c.last_entry_date,
t_incident.c.create_date,
).label(activity)

# Mapping
orm.mapper(Incident, t_incident, properties={
activity: orm.column_property(c_activity)
})

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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] Getting the names of text columns

[Mike Orr]

What's the simplest way to get the names of all columns containing
character data (VARCHAR or TEXT).  I've got it down to this, which
works but is a bit obscure:

def get_text_fields(table):
substrings = [text, string, char]
ret = []
for c in table.columns:
name = c.type.__class__.__name__.lower()
for sub in substrings:
if sub in name:
ret.append(c.name)
break
return ret

Regarding my earlier problem with foreign keys on autoload tables,
I've been getting inconsistent behavior in my app, so I need to test
it some more and verify whether there is a SQLAlchemy bug or stability
issue before I can report back.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Foreign key error

[Mike Orr]

On 11/1/07, Michael Bayer [EMAIL PROTECTED] wrote:
 what happens if you autoload like the above, and then just say
 t_incident.join(t_entry) ?   it likely breaks.  then, what happens if
 you define t_entry before t_incident ?

Well, today it's working, or at least at this momement.  But I did
upgrade to Kubuntu 7.10 this morning so I have a different version of
MySQL and mysql-python.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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] Foreign key error

[Mike Orr]

I have two tables Incident and Entry with a 1:many relationship.
Incident.orr_id is a primary key.  Entry.entry_id is a primary key,
and Entry.orr_id is a foreign key.  (The column names are a legacy
tradition.)  I have the following model and classes:

t_incident = Table(Incident, meta,
autoload=True, autoload_with=engine)

t_entry = Table(Entry, meta,
Column('orr_id', types.Integer, ForeignKey(t_incident.c.orr_id)),
autoload=True, autoload_with=engine)

class Incident(object):
pass

class Entry(object):
@classmethod
def get(class_, entry_id):
return Session.query(class_).get(entry_id)

mapper(Entry, t_entry)

mapper(Incident, t_incident, properties={
'entries': relation(Entry, backref=incident),
})

If I run x = Entry.get(519010), I get an exception:

class 'sqlalchemy.exceptions.ArgumentError': Error determining
primary and/or secondary join for relationship 'Incident.entries
(Entry)'. If the underlying error cannot be corrected, you should
specify the 'primaryjoin' (and 'secondaryjoin', if there is an
association table present) keyword arguments to the relation()
function (or for backrefs, by specifying the backref using the
backref() function with keyword arguments) to explicitly specify the
join conditions. Nested error is Can't find any foreign key
relationships between 'Incident' and 'Entry'

However, if I run the same statement again, it works.

 x = Entry.get(519010)
 x.entry_id
519010L
 x.orr_id = 7704L
 x.incident
type 'exceptions.AttributeError': 'Entry' object has no attribute 'incident'

Oops, the backref doesn't work.  I was also getting an AttributeError
on the 'get' method earlier, though that may have cleared up.

But when I list all the columns rather than autoloading, the problems
seem to go away. (Knock on wood.)



-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: datetime objects unique by date(disregarding time)

[Mike Orr]

On 9/6/07, Pedro Algarvio, aka, s0undt3ch [EMAIL PROTECTED] wrote:

 How could one get only the unique dates from a datetime column, disregarding 
 the time part of the datetime object?

MySQL has a DATE() function that chops off the time part.  I don't
know if Postgres has the same.

 import datetime
 import sqlalchemy as sa
 e = sa.create_engine(mysql://...)
 e.execute(select date('2007-01-20 10:22:45')).fetchone()[0]
datetime.date(2007, 1, 20)
 sql = sa.select([sa.func.date('2007-01-20 10:22:45')])
 e.execute(sql).fetchone()[0]
datetime.date(2007, 1, 20)
 e.execute(sql).fetchone()[0] == datetime.date(2007, 1, 20)
True

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: prevent premature object flushing

[Mike Orr]

On 9/1/07, Haseeb [EMAIL PROTECTED] wrote:
 You're right - I am using Session.mapper which I took off the pylons
 tutorials. I'm mainly using Session.mapper to get the old assignmapper
 functionality of the 0.3x tree. Is there a way to get assignmapper
 functionality in 0.4x without losing the ability to control when an
 object gets added to the session?

It's a matter of thinking the opposite.  Because new objects are
automatically saved, you don't have to .save them to get them into he
database.  Instead you have to .expunge() the ones you *don't* want
saved in the database, and do that early enough that it doesn't get
written.

I agree that the autosaving is the most confusing part of
Session.mapper, and the interface should require it to be explicitly
enabled:
Session.mapper(A, B, autosave=True)

Both assign_mapper and Session.mapper have the assumption that of
course everybody wants autosave, but that's not true.  Some want the
.query attribute, some want autosaving, and some want both.  At least
the .query attribute you can ignore if you don't want it, but there's
no way to disable autosaving.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Testing for validity of a Connection

[Mike Orr]

On 8/31/07, Michael Bayer [EMAIL PROTECTED] wrote:
 however in most cases keeping a low pool_recycle should take care of
 most common issues in this area...

What it doesn't handle is if the database server is restarted.  That
doesn't happen very often with reliable database servers nowadays but
it is possible.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Testing for validity of a Connection

[Mike Orr]

On 8/30/07, Moshe C. [EMAIL PROTECTED] wrote:

 I was hoping there was something more elegant than just trying and
 catching a possible exception.
 The motivation is just simpler and more readable code like
 if not connection.is_valid():
get another one

The issue is that in a packet-based network, there's no way to tell if
the connection is alive without generating some traffic and seeing if
it succeeds.  SQLAlchemy could, and perhaps should, encapsulate this
in a method but it doesn't.  I've also argued that SQLAlchemy should
seamlessly retry a query if it finds a connection dead, but MikeB says
this is not safe in a transaction.

The SQLAlchemy Way is to set the 'pool_recycle' engine option to a
value lower than the database's timeout.  MySQL seems to be the main
culprit, and it has a default timeout of 8 hours or so, so setting
pool_recycle=3600 (one hour) is well within the limit.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: creating a database through SQLAlchemy

[Mike Orr]

On 8/30/07, Travis Kriplean [EMAIL PROTECTED] wrote:
 I'd like to use SQLAlchemy to connect to a db server, create a
 database, and then start using it. However, it appears that the
 SQLAlchemy api assumes the existence of a database to connect to. I'm
 able to connect to the server without a database specified:

  con = 'postgres://postgres:[EMAIL PROTECTED]'
  m = MetaData(bind=con)
  m.get_engine()
 sqlalchemy.engine.base.Engine object at 0x00E6B470

 However, when I try to execute a query to create the database, it
 fails because CREATE DATABASE cannot run inside a transaction block:

  c = m.get_engine().connect()
  c.execute('CREATE DATABASE test_db')
 Traceback (most recent call last):
   File stdin, line 1, in module
   File c:\python25\lib\site-packages\sqlalchemy-0.3.10-py2.5.egg
 \sqlalchemy\eng
 ine\base.py, line 517, in execute
 return Connection.executors[c](self, object, *multiparams,
 **params)
   File c:\python25\lib\site-packages\sqlalchemy-0.3.10-py2.5.egg
 \sqlalchemy\eng
 ine\base.py, line 532, in execute_text
 self._execute_raw(context)
   File c:\python25\lib\site-packages\sqlalchemy-0.3.10-py2.5.egg
 \sqlalchemy\eng
 ine\base.py, line 581, in _execute_raw
 self._execute(context)
   File c:\python25\lib\site-packages\sqlalchemy-0.3.10-py2.5.egg
 \sqlalchemy\eng
 ine\base.py, line 599, in _execute
 raise exceptions.SQLError(context.statement, context.parameters,
 e)
 sqlalchemy.exceptions.SQLError: (ProgrammingError) CREATE DATABASE
 cannot run in
 side a transaction block
  'CREATE DATABASE test_db' {}

That must be a Postgres-specific problem because it works with MySQL.

$ python
Python 2.5.1 (r251:54863, May  2 2007, 16:56:35)
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)] on linux2
Type help, copyright, credits or license for more information.
 import sqlalchemy as sa
 engine = sa.create_engine(mysql://root:[EMAIL PROTECTED])
 e = engine.connect().execute
 e(create database test2)
sqlalchemy.engine.base.ResultProxy object at 0x83811ac
 e(show databases).fetchall()
[('information_schema',), ('mysql',), ('rlink',), ('shields',), ('test2',)]
 e(show tables).fetchall()
Traceback (most recent call last):
...
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.OperationalError: (OperationalError) (1046, 'No
database selected') 'show tables' ()
 e(use test2)
sqlalchemy.engine.base.ResultProxy object at 0x846a36c
 e(show tables).fetchall()
[]
 e(drop database test2)
sqlalchemy.engine.base.ResultProxy object at 0x838124c
 e(show databases).fetchall()
[('information_schema',), ('mysql',), ('rlink',), ('shields',)]


Obviously it's perilous to switch databases in an existing engine,
especially if it's bound to a session or metadata.  MySQL
automatically commits the last transaction before running a
non-transactional command (which basically means any schema-changing
operation).  This may confuse the hell out of your session if you
don't commit + clear first.  I don't know if PostgreSQL does the same.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Too many database connections.

[Mike Orr]

On 8/28/07, caffecoder [EMAIL PROTECTED] wrote:
 sqlalchemy.exceptions.DBAPIError: (Connection failed)
 (OperationalError) FATAL: sorry, too many clients already

I've been getting a similar but not identical error after upgrading to
SQLAlchemy 0.4, and somebody else on the list also mentioned this
recently.  I don't have the traceback but it was  the error for
exceeding the 'max_overflow' number of connections.

I worked around it by setting the 'max_overflow' engine option from 10
to 30.  I haven't tried the 'threadlocal' pooling strategy, which is
supposed to minimize the number of connections per thread.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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] Autoload errors

[Mike Orr]

I'm upgrading a Pylons app from SQLAlchemy 0.3 to 0.4.  One table is defined as:

incidents = Table(IN_Incident, meta,
Column(orr_id, Integer, primary_key=True),
autoload=True, autoload_with=engine)

The table is a MySQL VIEW that contains a column 'is_top'.  The
application fails because the incidents.columns.is_top attribute does
not exist. Inspecting incident.columns in the Pylons debugger shows it
contains only the explicitly-defined column, not the autoloaded
columns:

 list(model.incidents.columns)
[Column('orr_id', Integer(), primary_key=True, nullable=False)]

In SQLAlchemy 0.3 it contained the autoloaded columns too.  Was this
an intentional change or did the autoloading fail somehow?  Without
the column attributes, I can't use the columns in a where expression.

Loading the table interactively produces the same problem:


$ python
Python 2.5.1 (r251:54863, May  2 2007, 16:56:35)
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)] on linux2
Type help, copyright, credits or license for more information.
 from sqlalchemy import *
 import sqlalchemy
 sqlalchemy
module 'sqlalchemy' from
'/usr/local/lib/python2.5/site-packages/SQLAlchemy-0.4.0beta4-py2.5.egg/sqlalchemy/__init__.pyc'
 engine = create_engine(mysql://..., echo=True)
 meta = MetaData()
 incidents = Table(IN_Incident, meta,
...   Column(orr_id, Integer, primary_key=True),
...   autoload=True, autoload_with=engine)
2007-08-23 14:41:41,907 INFO sqlalchemy.engine.base.Engine.0x..4c SHOW
VARIABLES LIKE 'character_set%%'
2007-08-23 14:41:41,907 INFO sqlalchemy.engine.base.Engine.0x..4c None
2007-08-23 14:41:41,909 INFO sqlalchemy.engine.base.Engine.0x..4c SHOW
VARIABLES LIKE 'lower_case_table_names'
2007-08-23 14:41:41,909 INFO sqlalchemy.engine.base.Engine.0x..4c None
2007-08-23 14:41:41,909 INFO sqlalchemy.engine.base.Engine.0x..4c SHOW
CREATE TABLE `IN_Incident`
2007-08-23 14:41:41,910 INFO sqlalchemy.engine.base.Engine.0x..4c None
 list(incidents.columns)
[Column('orr_id', Integer(), primary_key=True, nullable=False)]
 incidents.columns.orr_id
Column('orr_id', Integer(), primary_key=True, nullable=False)
 incidents.columns.is_top
Traceback (most recent call last):
  File stdin, line 1, in module
  File 
/usr/local/lib/python2.5/site-packages/SQLAlchemy-0.4.0beta4-py2.5.egg/sqlalchemy/util.py,
line 281, in __getattr__
raise AttributeError(key)
AttributeError: is_top
 incidents.columns.name
Traceback (most recent call last):
  File stdin, line 1, in module
  File 
/usr/local/lib/python2.5/site-packages/SQLAlchemy-0.4.0beta4-py2.5.egg/sqlalchemy/util.py,
line 281, in __getattr__
raise AttributeError(key)
AttributeError: name



The first time I tried this interactively I got another strange error,
but I can't reproduce it.  This was with beta3:

$ python
Python 2.5.1 (r251:54863, May  2 2007, 16:56:35)
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)] on linux2
Type help, copyright, credits or license for more information.
 import sqlalchemy as sa
 engine = sa.create_engine(mysql://..., echo=True)
 meta = MetaData()
Traceback (most recent call last):
  File stdin, line 1, in module
NameError: name 'MetaData' is not defined
 meta = sa.MetaData()
 incidents = Table(IN_Incident, meta,
... Column(orr_id, Integer, primary_key=True),
... autoload=True, autoload_with=engine)
Traceback (most recent call last):
  File stdin, line 1, in module
NameError: name 'Table' is not defined
 from sqlalchemy import *
 incidents = Table(IN_Incident, meta,
... Column(orr_id, Integer, primary_key=True),
... autoload=True, autoload_with=engine)
Traceback (most recent call last):
  File stdin, line 3, in module
  File 
/usr/local/lib/python2.5/site-packages/SQLAlchemy-0.4.0beta3-py2.5.egg/sqlalchemy/schema.py,
line 115, in __call__
autoload_with.reflecttable(table, include_columns=include_columns)
AttributeError: 'module' object has no attribute 'reflecttable'




-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Problem with subquery

[Mike Orr]

Er, where is it you're not supposed to use .c?  The code in MikeB's
example seems to have .c in every possible location.  How do you
access a column without .c?

On 8/10/07, Jeronimo [EMAIL PROTECTED] wrote:

 Excelent ! It works perfectly !!
 Thank you very much Michael. I was going crazy trying to figure how to
 move
 subquery to the from clause.



 On Aug 9, 8:06 pm, Michael Bayer [EMAIL PROTECTED] wrote:
  OK sorry, i didn't look carefully enough.  when you use a scalar
  subquery, you shouldn't access the c attribute on it.  I hadn't
  really realized that and maybe i should add an exception for that.
  when you access the c attribute, you're treating it like another
  relation to be selected from, so it gets stuck into the from clause.
  but here, its really just a column expression; so you don't join
  against one of the subqueries' columns, the subquery IS the thing to
  be compared against.  so heres the full testcase:
 
  
  create table node(id integer, parent_id integer, type_id integer);
  insert into node(1,NULL,1);
  insert into node values(1,NULL,1);
  insert into node values(2,1,1);
  insert into node values(3,1,2);
  insert into node values(4,1,1);
 
  SELECT node.id, node.parent_id, node.type_id
  FROM node
  WHERE node.id = (SELECT max(n1.id) FROM node AS n1 WHERE n1.type_id =
  node.type_id);
 
  
  from sqlalchemy import *
 
  meta = MetaData()
 
  node_table = Table('nodes', meta,
  Column('id', Integer),
  Column('parent_id', Integer),
  Column('type_id', Integer),
 
  )
 
  meta.bind = create_engine('sqlite://', echo=True)
  meta.create_all()
 
  node_table.insert().execute(id=1, type_id=1)
  node_table.insert().execute(id=1, type_id=1)
  node_table.insert().execute(id=2, parent_id=1, type_id=1)
  node_table.insert().execute(id=2, parent_id=1, type_id=1)
  node_table.insert().execute(id=3, parent_id=1, type_id=2)
  node_table.insert().execute(id=4, parent_id=1, type_id=1)
 
  n1 = node_table.alias('n1')
  sub_query = select([func.max(n1.c.id)],
  (node_table.c.type_id==n1.c.type_id), scalar=True)
 
  print
  node_table.select(node_table.c.id==sub_query).execute().fetchall()


 



-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: new session docs !

[Mike Orr]

On 8/10/07, Alexandre CONRAD [EMAIL PROTECTED] wrote:

 Okay Mike,

 about scoped_session(), I think I got it right this time. I was trying
 to get inspired from SAContext, how it's beeing wrapped around with
 Pylons. Since SAContext has not yet upgraded to SA 0.4, maybe I was just
 getting inspired from some different mechanism. Or SAContext it doing it
 wrong. SAContext says you have to call the clear() method on your
 session on each new request.

 
 *Important:* Put this line at the beginning of your base controller's
 .__call__ method (myapp/lib/base.py)::

  model.sac.session.clear()

 This erases any stray session data left from the previous request in
 this thread.  Otherwise you may get random errors or corrupt data.  Or
 del model.sac.session_context.current if you prefer.
 

 It sounds to me that the session is global and needs to be cleared
 everytime. Which I think is wrong (or SessionContext works differently).
 I think a *new* session should be created and attached to every new
 request. The session is then deleted automaticly when the request ends,
 rather than shared from a global obect (the Pylons' model) and cleared
 (which is not thread-safe as I now understand).

sac.session is not a global attribute, it's a property that returns
sac.session_context.current.  sac,session_context is a SessionContext,
which manages its .current property to provide a session local to the
current thread and application.  After del
sac.session_context.current, SessionContext automatically creates a
new session at the next access; this is a feature of SessionContext.

sac.session.clear() resets the thread-local session in place,
discarding any remnants of its previous use.  Both statements do
effectively the same thing, but I'm told that del
sac.session_context.current is more computationally efficient.

In SQLAlchemy 0.4, SessionContext is superceded by scoped_session,
which has a different API.  Assuming 'Session =
scoped_session(sessionmaker(...))', 'Session()' is the equivalent to
'session_context.current'.  MikeB says the preferred way to reset a
session in 0.4 is 'session.close()'.  This does the same as
'session.clear()' but also releases any network connections or other
resources that are being held.

*If* SAContext replaces .session_context with .session_factory
(equivalent to 'Session' above), the .session property would be
redefined to return self.session_factory()'.  Then you'd put this in
your controller method:
model.sac.session.close()
*after* the superclass call.  I'm thinking about adding convenience
methods sac.start_request() / sac.end_request() to avoid any
confusion, and also to isolate the controller from changes in the
session code.  But SAContext will not be upgraded until SQLAlchemy 0.4
beta comes out, because the SQLAlchemy API is changing too quickly for
me to keep up with.

If session.close() exists in SQLAlchemy 0.3, I didn't know about it.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: all() or list() ?

[Mike Orr]

On 8/1/07, Gaetan de Menten [EMAIL PROTECTED] wrote:

 On 8/1/07, Alexandre CONRAD [EMAIL PROTECTED] wrote:

  I'm realizing that I've been using .list() to query objects. But all the
  examples in the docs talk about .all().
 
  What's the difference ? Should I switch to .all() rather than .list() ?

  Will list be deprecated in 0.4 ?

 Exactly. list() is the old way, all() is the 0.4 way.

.list() is the old new way, or short-lived new way.  .all() is the
real new way.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Insert through ORM performance (was: Performance question)

[Mike Orr]

Andreas Kostyrka wrote:
 Correctly and quickly loading data is strongly depending upon the DB.
 E.g. For PostgreSQL you can achieve a magnitude of speedup by using COPY
 FROM STDIN;

 But the kinds hacks are out of scope for sqlalchemy.


On 7/19/07, Michael Bayer [EMAIL PROTECTED] wrote:
 Anyway, if the email is talking about batched inserts of this type
 being slow (i.e. non-ORM inserts):

 table.insert().execute({params1}, {params2}, {params3}, )

 thats because SA still does a lot of work on each batch of {params} to
 check for defaults and also to process bind parameters.   We might
 look into optimizing some of the redundant work which occurs within
 this process in 0.4, however as long as people still want their
 unicodes converted to utf-8, their datetimes converted to strings on
 sqlite, their binaries correctly massaged, their Python side defaults
 to fire off, this overhead will still be present for those types.

Andreas is pointing out that bulk inserts are intrinsically slow in
some database engines, which adds an additional level of overhead that
SQLAAlchemy has no control over.  MySQL suggests LOAD DATA INFILE
... for these situations, to read data from a tab-delimited or CSV
file (with SELECT INTO OUTFILE ... for writing).  PostgreSQL has the
equivalent but with different syntax.Unfortunately that means
putting the data in still *another* format which may have quirks, and
it will have to be an encoded bytestring rather than Unicode.

Perhaps SQLAlchemy could add a side feature to load/save data in this
manner, to smooth out the differences between engines.  But I'm not
sure that's worth much effort.  To do it with SQLAlchemy now you can
create a raw SQL string with the full path of the file to be
read/written.

I'm amazed at the speed of mysqldump and its reloading.  It packs a
bunch of rows into one INSERT statement.  I don't see why that's so
much faster than than executemany but it provides another potential
avenue for speed.  I'm not sure if MySQL is the only engine that does
this.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Misspell elixir in SAContext

[Mike Orr]

On 7/18/07, Olli Wang [EMAIL PROTECTED] wrote:
 I just found your SAContext has a misspell of elixir, you spell it as
 exilir,

Fixed in 0.3.3.  I tend to pronounce that word the other way so that's
how I spelled it.

http://sluggo.scrapping.cc/python/sacontext/

 Also, I have little question about how to use the ElixirStrategy. It said
 under

 pylons we should use sac = PylonsSAContext(), but the ElixirStrategy tells

 us to use sac = SAContext(strategy=ExilirStrategy), that
 way, it is not

 PylonsSAContext(), does it work fine with Pylons, too?

It should.  You'll need the strategy argument.

 And, could you tell me where to put the sqlalchemy config below?

 sqlalchemy.default.uri = mysql://[EMAIL PROTECTED]/mydb
 sqlalchemy.default.echo = true
 sqlalchemy.default.echo_pool = false
 sqlalchemy.default.pool_recycle = 3600

 I put it in development.ini but it seems doesn't work. :(

That's right.  It appears to be a bug in Pylons or PasteDeploy that I
haven't figured out; it loses the configuration in some circumstances.
 We can discuss it on the other thread in pylons-discuss.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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] SAContext 0.3.2 and future plans

[Mike Orr]

SAContext 0.3.2 has been released.
http://sluggo.scrapping.cc/python/sacontext/

Changes:

*  .add_engine and .add_engine_from_config now return the (engine,
metadata) pair created, in addition to registering them internally.
Requested by Andrey Petrov.

* New strategy ExilirStrategy contribued by beachcoder.  It's one way
to use Exilir wth SAContext, not necessarily the only way.

* Bugfix in ._check_engine_key.

Development is splitting into two branches.  The 0.3.2 API is now
stable (excluding ExilirStrategy) and will remain compatible with
SQLAlchemy 0.3.x and Pylons 0.9.6.

The 0.4 series (not written yet) will target SQLAlchemy 0.4.0 and
0.3.9, and will not be compatible with SQLAlchemy 0.3[678].  SAContext
and the strategies will remain the same.  PylonsSAContext will
probably be broken up into two functions: one to parse an engine
configuration to a dict, and one for the application scope.  These
functions may then be absorbed into Pylons or a future Pylons extras
package at some point.

I'll be out of town from Friday till the end of the month, with
limited Internet access, so development is off till August.  I'll be
in NYC this weekend and DC on Monday  Tuesday if anybody wants to do
a tech coffee.

Reminder: when upgrading from 0.2.x, remove the engine-adding
arguments from the constructor, and instead call .add_engine or
.add_engine_from_config after constructing.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: TEST POST

[Mike Orr]

On 7/18/07, Michael Bayer [EMAIL PROTECTED] wrote:

 this is a test.

Success.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: SAContext 0.3.0

[Mike Orr]

On 7/11/07, Mike Orr [EMAIL PROTECTED] wrote:
 On 7/11/07, Jose Galvez [EMAIL PROTECTED] wrote:
  Dear Mike,
  I've read the doc string in the new sacontext and was just wondering why for
  add_engine_from_config do you have to explicitly pass None for the default
  connection? it would make more sense to pass 'default' or better yet nothing
  all all and assume the default engine.  I understand that you are moving
  away from the the implicit to the explicit which is great, I just thought
  passing None to mean default is awkward when you could just as easily added
  None as the default in the method def. (the same could be said about
  add_engine)

 It is awkward but Python has no other built-in value for default.
 Using a string means people may spell it differently, and the
 .metadata and .engine properties require a fixed value.  Making it
 optional means the second positional argument would sometimes move to
 the first (like Pylons  render_response(/template.html) vs
 render_response(mako, /template.html), and I'm absolutely opposed
 to that.

I released 0.3.1 which accepts default, sacontext.DEFAULT, or None
interchangeably to refer to the default engine.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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] SAContext 0.3.0

[Mike Orr]

SAContext is a SQLAlchemy front end that organizes your engines,
metadatas, and sessions into one convenient object, while still
allowing access to the underlying objects for advanced use.

Version 0.3.0  attempts to handle and document all the use cases that
have been thrown at it: one database, multiple databases permanently
connected to tables, one engine per session, multiple engines per
session.  There are several backward-incompatible changes and
undoubtedly some new bugs.  Get it now at:

http://sluggo.scrapping.cc/python/sacontext/
http://cheeseshop.python.org/pypi/sacontext


The biggest change is that .__init__ no longer configures a default
engine, so you'll have to call .add_engine where you previously
didn't.  Likewise, PylonsSAContext.add_engine no longer reads the
Pylons config file; you'll have to call the new method
PylonsSAContext.add_engine_from_config for that.  Explicit is better
than implicit.  The default engine is keyed under None, so adding
engines is:

sac.add_engine(None, uri=...)
# default engine from explicit args
# The first arg is the engine key: None or a string
sac.add_engine_from_config(None)
# sqlalchemy.default.uri - default engine
# The first arg is the same as above
sac.add_engine_from_config(logs)
# sqlalchemy.logs.uri - logs engine
sac.add_engine_from_config(logs, config_key=db2)
# sqlalchemy.db2.uri - logs engine
sac.add_engine_from_config(None, verde)
# sqlalchemy.verde.uri - default engine

The module docstring is expanded to explain the various use cases.

SAContext._get_session_scope now returns the thread ident.  The
previous implementation was wrong and thread unsafe.
PylonsSAcontext._get_session_scope is unchanged; it uses the same
methodology as pylons.database.app_scope.

I haven't used Exilir or Tesla so I don't know how compatible
SAContext is with them.  A couple people have said they're trying it
but none have given me feedback yet.  (Hint)

SAContext is no longer supported under Pylons 0.9.5.  Pylons had some
internal changes I didn't realize, and I think our time would be
better spent preparing for 0.9.6.  So use PylonsSAContext with a
recent development version of Pylons.

SAContext will soon get an update soon for SQLAlchemy 0.4; in
particular the new 'bind' attributes/arguments.  I don't know if it
will remain compatible with SQLAlchemy 0.3.x after that.

We are discussing ways to incorporate SAContext into Pylons, possibly
for 0.9.6, but nothing is finalized yet.  It may be under
pylons.database or it may be in a separate distribution.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: query date field

[Mike Orr]

On 7/6/07, Andreas Jung [EMAIL PROTECTED] wrote:


 --On 6. Juli 2007 23:27:30 + jose [EMAIL PROTECTED] wrote:

 
  I've got a question that I can't find the answer to.  I have a table
  called seminars with a date field in it to hold the seminar dates.  I
  want to query the table to find all the dates for a specific year. I
  tried query(Seminars).filter(Seminars.c.date.year==2007) but this gave
  me an error stating that the col does not have a year property.  So
  how should I do this?
  Jose
 


  func.to_char(table.c.date, '') == '2007'

 or by using between(table.c.date, datetime(2007,1,1) , datetime(2007,31,
 12)) or something like that...

Or func.year(table.c.date) == 2007


-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: SAContext and transactions?

[Mike Orr]

On 6/29/07, voltron [EMAIL PROTECTED] wrote:

 I´m not sure if I should ask this here or the Pylons forum:

Here, but I haven't introduced SAContext here yet.

SAContext is a little top-level organizer for engines, metadatas, and
a session context.  It was written for Pylons due to the confusion
people were having setting up their models, but the SAContext class
itself can be used in any SQLAlchemy application.

http://sluggo.scrapping.cc/python/sacontext/

 How does one wrap a query in a transaction when using SAContext? Could
 someone post some example code?

A SQL query or an ORM query?  Let's start with what exactly you're
trying to do, and how it would be done without SAContext.

 Also, I read somewhere that SQL92 defined transactions eliminate the
 need to lock tables, is that right?

Can't say specifically but MySQL uses LOCK TABLES as a poor man's
transaction.  It prevents inconsistent concurrent writes but does not
guarantee the transaction won't be partly completed and partly not,
and it can't be rolled back at all.  It was good enough for MySQL AB's
needs but after deafening clamor from users they finally added InnoDB
with real transactions.  Anyway, you don't need to lock tables
explicitly when using transactions, because that's part of what
transactions do.  Transaction syntax is also more standardized across
database engines than table locking (which isn't standardized at all).

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: SAContext and transactions?

[Mike Orr]

On 6/29/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
  http://sluggo.scrapping.cc/python/sacontext/
 
 hmm, why have i invented same thing 8 months ago...
 Anyway. for a reason or another u dont have any destroy operations
 there.

It's only a container.  Its main job is to preserve the right
relationships between the engines/metadatas/sessions.  It doesn't do
any data modifications itself.

 def destroy_tables( me):
 me.metadata.drop_all()
 def create_tables( me):
 me.metadata.create_all()

In this case the user would explicitly do sac.metadata.drop_all().
I don't see a need to encapsulate this further.

 Here some i've found useful so far (sorry, not immediately usable):
 --
 def destroy( me, full =True):
 me.session.close()

 #SA caches/data
 sqlalchemy.clear_mappers()

 try: me.metadata.drop_all()
 except AttributeError: pass
 me.metadata = None
 if full:
 try: me.db.dispose()
 except AttributeError: pass
 me.db = None

 #from sqlalchemy.orm import mapperlib
 #mapperlib.global_extensions[:] = []
 #more?

 def detach_instances( namespace_or_iterable, idname ='id'):
 'useful to completely get rid of any SA sideeffects/artefacts,
 e.g. for testing'
 try: itervalues = namespace_or_iterable.itervalues() #if dict-like
 except AttributeError: itervalues = namespace_or_iterable
 for e in itervalues:
 try: del e._instance_key
 except AttributeError: pass
 setattr( e, idname, None)   #or delattr ??

These may be useful in a library of SQLAlchemy utility functions but
they're outside SAContext's scope.  SAContext doesn't contain mappers
so it shouldn't be clearing them.  Likewise it doesn't contain tables
or ORM object.  The reason it doesn't contain these is that SQLAlchemy
provides other nice constructs for those:
  - users normally have a global variable for each table
  - if you don't, you can retrieve a table with Table(tablename)
 - tables are heavily used in expressions, and
sac.tables[tablename].c is too verbose
  - users normally don't keep direct references to mappers
  - you can retrieve a mapper if you need to via some syntax I can't
be bothered to look up
  - ORM objects work fine as-is

However, there's no reason you can't write a SuperSAContext subclass
with lots of data-management features, and publish it if you like.

But don't these destroy everything scenarios mainly occur during
interactive debugging and experimentation?  Where in an application
would you want to clear mappers, detach an object with no traces left
behind (aren't session.clear() and session.expunge() good enough?), or
destroy everything?

 One thing i'm still not sure, if i can rebind a (now bound) metadata
 to another engine, how that affect mappers etc. i.e. how all these
 lifetimes (engines, metadatas, mappers) actualy interact.

In SAContext with the default strategy, the metadatas are expected to
remain bound to their original engines.  If you reconnect one to a
different engine, I don't know, maybe it'll work.  I can make an
engineless subclass with unbound metadatas if enough people really
need it.

As far as I understand it, when you do an operation that must access
the database *now*, it cascades down through the chain looking for a
Connection.  Starting with the method args, down through the session
to the mapped class to the mapper, to the table to the metadata to the
engine, which provides a Connection.  Of course you start at a
different level depending on what kind of method you call.  Various
points in the chain may contain an Engine, and if so it uses that one.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: SAContext and transactions?

[Mike Orr]

On 6/29/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:

 it's all okay. i just thought that if it is a Context, u should have
 some destructors - as u call many constructors. otherwise it is not
 reentrant - or at least repeatable.

I only call it context because Mike uses the word for session
contexts, and this is a wrapper around that.  To me context is a
meaningless word like bind or node or data: it can mean
anything.

 But if the idea is that this context will always be one-and-only,
 lives forever, and all db-stuff will happen within this one single
 instance, then, yeah, no point of destructing anything.

Yes, SAContext is pretty much once-per-application.  Another kind of
context would be needed for repeatable black-box testing.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Table updates using data mapping

[Mike Orr]

On 6/26/07, voltron [EMAIL PROTECTED] wrote:
 sess = create_session()
 allusers = sess.query(User).select()


 for user in allusers:
 user.group = contractor
 print x.name

This adds the overhead of creating a Python object for every row.  If
you already have many of the objects in memory anyway or the overhead
is too small to be noticeable, you can do it this way.  Rick's way
sends one small query to the database, which does it all internally.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Performing read-only operations without a session (avoiding leaks)

[Mike Orr]

On 6/15/07, Michael Bayer [EMAIL PROTECTED] wrote:
 create_session().query(Whatever).filter(...).all()

You added .all() when I wasn't looking?  :)

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Generative style on SQL-API layer

[Mike Orr]

.where() is OK.

On 6/6/07, svilen [EMAIL PROTECTED] wrote:
  q2 = q1.order_by(None)#used sometimes e.g. for count

This would be useful.  If a second .order_by can replace the ordering
(as opposed to appending to it), I don't see why it would be difficult
to delete it.  .order_by shouldn't add another join condition, and
even if it did and that can't be deleted, so what?  If the use wanted
an absolutely clean query, they should have constructed it cleanly in
the first place.

However, I like the way Query.count() ignores the order_by.This
allows my functions to return a Query, and the caller can call
.list(), .count(), selectone(), or another aggregate method as they
wish, and it does the right thing.

I'm concerned that we're building an elaborate API that is kinda like
Query but not identical.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Generative style on SQL-API layer

[Mike Orr]

On 6/6/07, Michael Bayer [EMAIL PROTECTED] wrote:
 just to note, I am leaning towards very simple generative method
 names for all the things we need, where(), having(), order_by(),
 group_by(), distinct(), etc.  I am going to have it do copy on
 generate by default.

If a generative default can be efficient, it would avoid the dilemma
of Generative or not?, while also being parallel with Query.

Otherwise, returning 'self' would be fine, and I promise to look the
other way. :)  Then I could do:
q.order_by(...)
instead of
q = q.order_by(...)
While those who prefer the latter can do that, and if you really need a copy:
q = q.clone().order_by(...)

Keep in mind that modifying the query is much more frequent than copying it.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Generative style on SQL-API layer

[Mike Orr]

On 6/5/07, Michael Bayer [EMAIL PROTECTED] wrote:

 I want to wake this thread up again.  can we get some arguments pro/
 con to converting select() to work in a generative style ?

 generative means either just the first, or both of these two things:

 - methods like append_whereclause() return a select object with
 which to call further genreative methods.  this is a good thing
 because you can say select.append_whereclause().order_by().group_by()
 etc.

I don't think it's necessary to allow method chaining just because
Query does.  One of our objectives is to make SQL select and ORM query
more distinct so they're not confused, especially if ORM .select() is
going to be sticking around for a while.  However, I expect there will
be overwhelming pressure to add this syntax so we might as well assume
it's inevitable.

I do think .append_whereclause should be changed to .append_to_where.
A SQL statement can have only one WHERE clause; what you're actually
appending is an AND operand.  .append_to_where seems to get that
across better than .append_whereclause or .append_where.  The word
clause is superfluous because all parts of a SQL statement are
called clauses.

 - the select object you get back is a *copy* of the object which you
 called.
   advantages include:
 * is more Pythonic (not sure why this is, Mike Orr said so,
 would like more exposition)

it's just bad style for a method to return self.  Perl classes do it
as a pattern, while Python libraries have always avoided it.  Python
methods normally return None, a new immutable object, or a calculated
value.  Although as I said before, maybe it's not that big a deal for
this special case where you have to call several methods all at once
in order to get a complete SQL statement.

 * you might want to reuse the original object differently (is that
 such a common pattern ?  seems weird to me..more exposition here too)

It *is* useful to prebuild part of a query, then let the caller modify
it.  I went to this pattern:

def list_incidents(top_only):
q = table1.select(table1.c.is_public)
if top_only:
q.append_whereclause(table1.c.is_top)
return q

Then the caller can add more restrictions or an order_by.  Because
every method modifies the query in place, I have to get a fresh select
by calling the factory again if I want to query the same table a
different way.  That's not a big deal, but that is the cost of
non-generative selects.  Adding a .clone() call would be convenient,
though for me it's just as easy to call my factory function again.

 * would be consistent with orm's Query() object which has made its
 choice on the copy side of things

Well, can we go all the way and duplicate Query's API completely?
Then there would be One Way to do it.

I suppose I should say something more about the pros/cons of .select
method chaining, but I've got a headache today so I can't think too
hard.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: like doesn't work with objects

[Mike Orr]

On 6/5/07, Techniq [EMAIL PROTECTED] wrote:

 I'm going through the wiki cookbook
 http://docs.pythonweb.org/display/pylonscookbook/SQLAlchemy+for+people+in+a+hurry
 and I'm discovering that even though 'model.class.c.column_name.like'
 is available it doesn't perform a LIKE in the query.

 from 'paster shell'

 In [20]:
 model.Accounting.select(model.Accounting.c.usr.like('TSmith'))
 Out[21]:
 [syslogsql.models.Accounting object at 0x2626d70,
  syslogsql.models.Accounting object at 0x2626ad0,
  syslogsql.models.Accounting object at 0x2626910]
 In [22]: model.Accounting.select(model.Accounting.c.usr.like('Smith'))
 Out[22]: []

 ...BUT...

 In [23]: model.Accounting.select(model.Accounting.c.usr.like('%Smith
 %'))
 Out[27]:
 [syslogsql.models.Accounting object at 0x262d670,
  syslogsql.models.Accounting object at 0x2626d70,
  syslogsql.models.Accounting object at 0x2626ad0,
  syslogsql.models.Accounting object at 0x262d770,
  syslogsql.models.Accounting object at 0x262d790,
  syslogsql.models.Accounting object at 0x262d7b0,
  syslogsql.models.Accounting object at 0x2626910]

 Should I have to add the '%' around the string?

What is the SQL in those cases?  (engine.echo = True)

Yes, you need the '%':  column.like('%Smith%')  The reason is that you
may prefer the wildcard in a different position: '%son', 'ra%s'.

It should work.  I'm using an ORM query like that now, and I think I
had a select query with .like before that.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature

[Mike Orr]

On 6/4/07, Rick Morrison [EMAIL PROTECTED] wrote:
 The use of scalar() here seems out of place with both the common CS usage of
 the word (e.g. scalar == single-valued), and the use of scalar() in the SQL
 layer. Single row results in the ORM are rows, not
 a single datatype. It's another potential point of
 confusion, like the ORM .select() is/was.

 I would say drop scalar() in the ORM namespace, and for
 single-row results, use

 .first() -- returns first row
 .one() -- returns first row, raise exception if more than one result

What if there are zero rows?  Return None or raise an exception?  I
find the former useful enough, but I imagine some people prefer the
latter.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: new setuptools vs local SA copy

[Mike Orr]

On 6/4/07, Paul Kippes [EMAIL PROTECTED] wrote:

 I found the thread about PYTHONPATH--interesting.

 It does seem that eggs are not only preventing the expected behavior,
 but they are also preventing the documented behavior.

 However, I don't think that using eggs is the best choice for a fast
 progressing library like SQLAlchemy--especially with this behavior.
 Plus, if the egg developer isn't participating in a discussion on
 this, why should that distribution method even be used?

Are you talking about this thread?

http://mail.python.org/pipermail/distutils-sig/2007-May/007513.html

I'm not sure that this is especially relevant to SQLAlchemy per se.
Python has a language-wide problem in that:

1) Setuptools has become a de-facto standard but is not bundled with
Python, forcing users to find and and install ez_setup.py.  Users also
have to do tricks with their site.py to get a local egg directory
separate from site-packages, or use workingenv.py or Virtual Python.
People who aren't Python programmers but just want to run an
application (e.g., sysadmins) don't understand why they should have to
do this -- it seems like a grave defect in the language and it turns
them off from Python.

2) The distutils code is apparently very patched up and in need of a
rewrite before setuptools is integrated, but there are no programmer
volunteers to do it.

3) A few people don't like the setuptools approach and do not want it
in the standard library.

4) Setuptools does not have an uninstall option or clean up old bin/
scripts.  You can have two versions of an egg installed simultaneously
but only one set of bin/ scripts, the latest-installed ones
overwriting the previous.  Over time you end up with a version mess
and have to start again with a fresh library directory to clean it up,
plus cleaning out the bin/ directory by hand.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature

[Mike Orr]

On 6/3/07, Michael Bayer [EMAIL PROTECTED] wrote:
 - the methods select(), selectfirst(), selectone(), select_by(),
 selectfirst_by(), selectone_by() and get_by() would be deprecated.
 this means they will remain present on the Query object but the
 documentation would be reorganized to talk only about filter(),
 filter_by(), list(), scalar(), and a new method called one() which is
 like scalar() but ensures that only one row was returned.

+1

 - by removing the word select from Query's API entirely, the
 timeless confusion of sql.select()? or query.select()? goes away.
 the identifier select at last loses its ambiguity.  this is a big win.

For this reason.  SQLAlchemy has too many ways to do the same thing,
and too many ways to access the same object.

I would suggest renaming .list() to .all().  It seems funny having a
method with the same name and same behavior as list(query) -- I can
never decide which to use.

There is the concern about building dozens of intermediate query
objects that you immediately throw away, but that would be a good
target for optimization.  For instance, I'm not sure if it clones a
query by rebuilding the criteria from scratch, or if it just copies
one list of immutable (sharable) objects.  If you can guarantee that
the existing query won't be used anymore you can just reassign (share)
the criteria as single unit, but I guess you can't guarantee that.

 - assignmapper would also keep all of its current methods with
 regards to selecting/filtering.  it seems like tools like Elixir are
 going to move away from assignmapper anyway which is a good thing.

It would still be worth a separate proposal to reform assignmapper;
i.e., delete the query methods .foo() that duplicate
MyClass.query().foo().  A lot of non-Exilir people use assignmapper,
and it's frustrating that .select() exists but .filter() doesn't, so
either add the missing methods or delete the redundant ones.  On the
other hand, this can be handled in the documentation by emphasizing
.query() and deprecating the query methods.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature

[Mike Orr]

On 6/3/07, Michael Bayer [EMAIL PROTECTED] wrote:
 im giong to look into optimizing the cloning.  as ive said,
 hibernate's criteria object behaves generatively but doesnt
 actually copy the object; several folks here seem to want the
 generativeness.   ive been considering sneaking in a flag/method
 that would turn off the generativeness but id have to make sure you
 dont notice it :).

Does that mean returning the results immediately, or modifying the
query in place and returning it?

Maybe modifying the query in place and returning it isn't such a bad
idea after all, considering that this is kind of a special case,
having to call so many methods to build up a query.  Most other OO
systems don't require so many method calls to build up a meaningful
object, but most other OO systems are not SQL queries either.   And if
it provides a way to get away from q = q.filter(...) in favor of
q.filter(...), that would be an advantage.  It gets tiring assigning
the same variable to itself again and again when assignment isn't
really the nature of what's going on.

  It would still be worth a separate proposal to reform assignmapper;
  i.e., delete the query methods .foo() that duplicate
  MyClass.query().foo().  A lot of non-Exilir people use assignmapper,
  and it's frustrating that .select() exists but .filter() doesn't, so
  either add the missing methods or delete the redundant ones.

 filter() and filter_by() were added in 0.3.8.

Hooray.  I've been following the trunk and reading the CHANGELOG but I
didn't notice that feature.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Weakly-referenced object error

[Mike Orr]

No weak-reference error for three days now so it looks like the
MySQLdb upgrade cured it.  Curious because I've been running other
sites sites with that same older version and never gotten that error.
But this is the first site that's mulththreaded (Pylons rather than
Quixote) so I bet that had something to do with it.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Bug in .query()[11:22]

[Mike Orr]

On 6/2/07, Michael Bayer [EMAIL PROTECTED] wrote:



 On Jun 1, 11:57 pm, Mike Orr [EMAIL PROTECTED] wrote:
  My point is, if the second number is lower than the first, shouldn't
  SQLAlchemy transform it into a query that returns no records?  I.e.,
  LIMIT 0, which MySQL at least allows.  Because that's what the Python
  equivalent would do:
 
   range()[1420:20]
  []

 this is like the argument with the empty in_() clause.  i like to err
 on the side of no silent failures / assumptions.  but i lost the
 argument with the in_() clause, so im willing to lose the argument
 here.  even though i really think python should be raising an error
 here too...why doesnt it ?

Because iterating when you're already past the end of something
produces an empty result throughout Python.

 range(10, 7)
[]
 range(10, 7, -1)
[10, 9, 8]

 it = iter([1, 2, 3])
 it.next()
1
 it.next()
2
 it.next()
3
 for i in it:
...   print Found, i
...


The last 'for' loop could raise an error, but it doesn't in Python or
other languages, it just doesn't loop at all.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: assign_mapper query methods

[Mike Orr]

I guess it depends how you look at it.   To me assign_mapper adds some
Query methods and not others; e.g., .select and .count but not .filter
.  I assume that's because .filter is so new.  But in the manual under
Generative Query Methods it implies that .select and .filter are
parallel; i.e., you can use either one depending on whether you want
the results now or you want to modify the query further.  With the
regular mapper it's easy to switch between the two by merely changing
one method name:

rslts = ctx.current.query(MyClass).select(...)
q  = ctx.current.query(MyClass).filter(...)

But with assign_mapper they are not parallel and you have to add or
delete an otherwise-useless .query() call (useless because it takes no
arguments):

rslts = MyClass.select(...)
q = MyClass.query().filter(...)

As the application's needs change, users will frequently have reason
to switch between .select style and .filter style.n  You've mentioned
earlier that you're not fond of Query.select() at all  because users
confuse it with Table.select(), and recommended .filter(...).list()
instead.  If people start doing this wholesale there will be a lot of
transformations from .select to .filter, and this same issue will come
up.

At the same time, I share your concern about adding too many methods
to the user class, especially since they may someday collide with one
of my database columns.  I would rather have parallel select/filter
than lots of user class methods.  I suppose I could just pretend
.select() and .count() don't exist, and use .query().select() and
.query().filter() and .query().count() instead -- if .query() is going
to be documented and supported long term.
I can see why it would be a pity to lose .get().  But on the other
hand, why should some Query methods be privileged and others not?

--Mike

On 5/31/07, Michael Bayer [EMAIL PROTECTED] wrote:

 heres the question.  Query gets 10 new methods one day.  do we then
 add 10 methods to assign_mapper() ?  must the user class be a total
 clone of Query ?  assign_mapper just bugs me for this reason.   hence
 i like entity.query() better.  im not sure which one youre saying you
 prefer ?

 On May 31, 5:46 pm, Mike Orr [EMAIL PROTECTED] wrote:
  What are future plans for the assign_mapper query methods?
 
  MyClass.select(...)
  -- works great.  A clear advantage for assign_mapper over the
  regular mapper.
 
  MyClass.filter(...)
  -- doesn't exist.
 
  MyClass.query().filter(...)
  -- works but is undocumented and requires a convoluted monkeypatch
  in the source.  Not so clear an advantage over the regular mapper
  because it's so verbose.
 
  The third is the one I've found most useful.  That way I can have
  functions that return a Query, and the caller can call .select(),
  .select(offset=,limit=), or .count() as they wish.
 
  --
  Mike Orr [EMAIL PROTECTED]


 



-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Weakly-referenced object error

[Mike Orr]

On 6/1/07, Michael Bayer [EMAIL PROTECTED] wrote:

 the latest version of MySQLDB does use weakrefs right at the heart of
 things, in connections.py and cursors.py.since we are catching the
 exception we cant see the full original stack trace but its possible
 that it would trace into mysqldb's source code.

 that doesnt rule out that we are doing something to cause this to
 happen in SA, but im pretty sure people are using pool_recycle with
 mysql successfully.

Well, IF the problem does not go away I guess I'll have to restart the
application every few hours.  But with debug=false an external program
can't tell if an Internal Server Error is this or something else, so
do I, um, grep the error log? ...  Hmm.  I could somehow catch the
error and deliver a slightly different error message (perhaps using
JJ's case-modified headers secret code :), but then I might as well
just restart the application... however one might do that within the
application.  Could I just refresh the connection pool or expire all
the connections without disrupting the rest of the application?  But I
guess all of MySQLdb would need to be reinitialized, wouldn't it.
What about a monitor process like --reload: could it somehow be made
to know when this error occurs and restart the subprocess if so?

Why can't SQLAlchemy catch this and the gone-away error, refresh the
connection, and redo the query like SQLObject does?  That's really
what one wants, not a pool_recycle time.  I think you said that's
incompatible with transactions but I don't see how.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: should append_whereclause() return the resulting select?

[Mike Orr]

On 6/1/07, Michael Bayer [EMAIL PROTECTED] wrote:
  What I'd rather see is either an error warning not to assign the
  result to anything, or to just have foo.append_whereclause() return
  the resulting foo.  Is that a reasonable request, or are there reasons
  it shouldn't be done?  Unnecessary, perhaps?
 

 making select() 'generative' has been under discussion.  questions
 include whether to copy the select each time before returning it as
 well as what the methods should be called, i.e. maybe where()
 instead of append_whereclause().

Returning None is a Python standard, see list.sort().  Making select
generative is OK, but it should either modify the select in place or
return a new one, not modify it and return it.  That's a Perlism.
There should be one-- and preferably only one --obvious way to do
it.

Something shorter than .append_whereclause() would be nice.
.append_where or just .where come to mind.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: assign_mapper query methods

[Mike Orr]

assign_mapper is doing five different things:
1   Hide the session context and session
2   .query()
3   shadowed query methods (get*/select*/count/join*/etc)
4   shadowed session methods (flush/delete/save/etc)
5  connecting a DynamicMetaData whenever it needs to

(1) we all agree is very useful.

(2) is more straightforward to the user than session.query(MyClass).

(3) is under probation.

(4) I haven't used so I'm not sure if it's better or worse than
session.* .  But grafting fewer rather than more methods onto the
mapped class makes sense.

(5) is maybe being done by the session_context rather than
assign_mapper, so perhaps it doesn't apply here.  I just saw a Pylons
recipe that said you can use a DynamicMetaData in your model *if* you
use assign_mapper; I'm not sure why.
http://docs.pythonweb.org/display/pylonscookbook/SQLAlchemy+for+people+in+a+hurry

Given that all this is in control of the session_context, why not make
assign_mapper a method of it, with boolean flags to enable method
decoration:

session_context.map(MyClass, table, query_methods=True, session_methods=True)

or:

session_context.query_methods = True
session_context.session_methods = True
session_context.map(MyClass, table)

If we hang the query methods off .query(), can we hang the session
methods off .session()?  Or .store.  (But not .objectstore, ugh.)

Michael Bayer wrote:
 as it turns out, assign_mapper's monkeypatched methods (and they are
 all monkeypatched, not sure why you singled out query())

.query() is a class method that's actually a lambda.  Very strange.
The other methods looked like they were assigned more
straightforwardly but maybe that's just a superficial appearance.
They don't use lambdas though.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: should append_whereclause() return the resulting select?

[Mike Orr]

On 6/1/07, Michael Bayer [EMAIL PROTECTED] wrote:
 the modify in
 place and return it thing is also how Hibernate criteria queries work.

I know nothing about Hibernate.  Why is it so great and why are we
imitating it?  According to Wikipedia it's a Java db framework.  So we
should make sure we're not borrowing Javaisms in the API without a
compelling reason.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Weakly-referenced object error

[Mike Orr]

No weak-reference error this morning, hooray.  I'll be watching it for
a few days.

Could using a BoundMetaData in my model be contributing to the
problem?  I have the following structure:

===
ctx = get_session_context()
# A custom function that creates a session_context like
# pylons.database, but reads more engine options from the
# config file.
engine = ctx.current.bind_to
meta = BoundMetaData(engine)

incidents = Table(..., meta, autoload=True)
ic = incident.columns
class Incident(object):
pass
assign_mapper(ctx, Incident, incidents)

def list_incidents(top_only):
q = Incident.query()
if top_only:
q = q.filter(Incident.c.is_top)
return q
===

So when list_incidents() is called it's in a different thread than
where all the variables were defined.  The engine and metadata are
thread safe, right?  Should I use a DynamicMetaData and temporarily
connect it to define the tables, and then connect it to None?  Or
would that just me making things more complicated for no reason?  As
far as I can tell there's only one engine shared throughout the
application anyway, so it shouldn't harm anything to use a
BoundMetaData.

Could we have session.engine be an alias for session.bind_to?  Pretty
please?  The only reason I leave a top-level 'engine' around is in
case I need it for something, because .bind_to is so non-obvious.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Weakly-referenced object error

[Mike Orr]

On 6/1/07, Michael Bayer [EMAIL PROTECTED] wrote:
 Mike Orr wrote:
  Could we have session.engine be an alias for session.bind_to?  Pretty
  please?  The only reason I leave a top-level 'engine' around is in
  case I need it for something, because .bind_to is so non-obvious.

 pylons is the reason pulling the engine off the session is even
 becoming popular, because it is actually using the bind_to feature,
 as well as that it didnt really create any easy way to get at the
 engine repository (not to mention the issues i raised on the pylons
 list).  things to note about bind_to is that the session may not be
 bound to anything, and also can be bound to *multiple* engines in the
 case that someone is making it do that.  which is why the official
 way to get the engine is session.get_bind(mapper).  i dont know
 what im saying here other than im getting a little antsy about
 session / engine /etc being muddied / TMTOWTDI.  theres too many
 choices but in this case people wanted them.

I assume you're referring to this thread:
http://groups.google.com/group/pylons-discuss/browse_thread/thread/747ac14d1e20f332/a650fb1011ec2387?lnk=gstq=michael+bayer+sqlalchemyrnum=1
Subject: Pylons Integration of SQLAlchemy config extremely broken
Date: 2007-05-25

A couple older threads which are slightly obsolete:
http://groups.google.com/group/pylons-discuss/browse_thread/thread/70fecb3d8da1aec8/a78e2fb66d8e4baa?lnk=gstq=michael+bayer+sqlalchemyrnum=5
Subject: SQLAlchemy best practices
Date: 2006-09-20

http://groups.google.com/group/pylons-discuss/browse_thread/thread/1f05fee97b1e5217/f424e9f51f7e3627?lnk=gstq=michael+bayer+sqlalchemyrnum=6#f424e9f51f7e3627
Subject: ANN: Pylons 0.9.4 released
Date: 2006-12-30

There is agreement in the Pylons group that pylons.database needs to
be improved.  Both you and I and others need to pass in more
create_engine options.  I'm contemplating a patch that would read all
currently-defined options from the config file, converting those known
to be ints or bool, and skipping those requiring non-scalar values.
That would solve a large chunk of people's problems.

Multiple engines make my head spin.  Why do you need that unless
you're connecting to two different databases in the same application?
And even if you did, wouldn't you define a second top-level
session_context to bind it to, with its own different metadata and
tables, and never the twain shall mix?  I don't want a registry of
engines or something in the 'g' object.  What I want is a simple
out-of-the box configuration for simple sites, but more robust than
what Pylons currently has.  Maybe we'll have to come up with separate
simple and advanced configurations if others need multiple engines and
whatnot.

I also don't like how pylons.database initializes a session_context at
import time rather than providing a create_session_context function,
so that's another thing to add to my patch.  Otherwise if you can't
use pylons.database.create_engine() for some reason, you have to
duplicate a lot of code to recreate or bypass the default
session_context, and this includes writing stub functions because
SessionContext takes a session factory function with takes a
create_engine function, so there's no way to customize the
create_engine from the SessionContext constructor directly.

Methinks SQLAlchemy is contributing to the problem with its long
hierarchy of engine - metadata - session - session_context, but I
don't have the expertise to say what might be better.  But certainly
it's annoying that:

- SessionContext doesn't take both create_engine and make_session
arguments, or arguments to pass through to those, and build your ideal
engine - session - session_context hierarchy on the fly.  Instead
you have to create a dummy make_session function just to tell it which
create_engine to use.  This is part of why overriding Pylons' default
session_context requires reimplementing three whole functions.

- This is a part of the previous, but SessionContext in the manual
says, A common customization is a Session which needs to explicitly
bind to a particular Engine.  Yes, so why doesn't SQLAlchemy provide
a way to handle this common case without the user having to define his
own make_session?  Again, SessionContext -- or a
create_session_context function -- could do this for you if you pass
an 'engine' argument.

- Metadata seems like an implementation detail.  I have to define a
metadata just to pass it to my Table's, then I never use it again.
There is global_connect() which hides it, but its use seems
discouraged.  Plus global_connect gets into all that DynamicMetaData
complication, such as whether it will autoconnect properly in the
other threads.  Perhaps what I'm asking for is a global_bound_connect
or something?  I can see why the metadata can't be subsumed into the
engine because you may want to connect the same metadata back and
forth if you're copying data from one database to another.  And I can
see why it can't be subsumed

[sqlalchemy] Re: Weakly-referenced object error

[Mike Orr]

On 6/1/07, Michael Bayer [EMAIL PROTECTED] wrote:
 pylons is the reason pulling the engine off the session is even
 becoming popular, because it is actually using the bind_to feature,
 as well as that it didnt really create any easy way to get at the
 engine repository (not to mention the issues i raised on the pylons
 list).  things to note about bind_to is that the session may not be
 bound to anything, and also can be bound to *multiple* engines in the
 case that someone is making it do that.  which is why the official
 way to get the engine is session.get_bind(mapper).

Well, I guess it's a Pylonsism but the session is always bound to an
engine at this point, and no mappers exist yet.

I'm not sure how your one engine registry or engine key would help
with this.  You're saying people should just get the engine through
pylons.database.engines[default] or something rather than going
through the session_context?

Alternatively, pylons.database could expose the global engine.

Or one could have a SQLAlchemy class with .create_engine(),
.make_session(), .create_session_context() methods.  Then it would be
easy to subclass that if you need to override one of the parts, and
the other parts would automatically use your new method.  Then the
default model would have:

from pylons database import SQLAlchemy
sqla = SQLAlchemy()
engine = sqla.create_engine()
ctx = sqla.session_context()

Does that sound like a good approach?  Could it be made to scale for
multiple engines by adding some engine key arguments?

Also, the create_engine method could hand back a cached engine if a
compatible one has already been created.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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] Bug in .query()[11:22]

[Mike Orr]

I'm transforming a query from .select(offset=start, limit=rpp)  to
.query()[:] syntax.
('rpp' means records per page.)  Stupidly I transformed it directly into:

.query()[start:rpp]

which in one transaction evaluates to:

.query()[1420:20]

This causes a SQL syntax error with the actual query containing:

...  LIMIT -1400 OFFSET 1420

Apparently a negative limit is illegal in MySQL.  Of course I should
have done it this way:

.query()[start:start+rpp]

because the second number is supposed to be one past the last index,
not the number of records to return.  This results in a much more
reasonable:

.query()[1420:1440]

...  LIMIT 20 OFFSET 1420

My point is, if the second number is lower than the first, shouldn't
SQLAlchemy transform it into a query that returns no records?  I.e.,
LIMIT 0, which MySQL at least allows.  Because that's what the Python
equivalent would do:

 range()[1420:20]
[]

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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] assign_mapper query methods

[Mike Orr]

What are future plans for the assign_mapper query methods?

MyClass.select(...)
-- works great.  A clear advantage for assign_mapper over the
regular mapper.

MyClass.filter(...)
-- doesn't exist.

MyClass.query().filter(...)
-- works but is undocumented and requires a convoluted monkeypatch
in the source.  Not so clear an advantage over the regular mapper
because it's so verbose.

The third is the one I've found most useful.  That way I can have
functions that return a Query, and the caller can call .select(),
.select(offset=,limit=), or .count() as they wish.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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] Weakly-referenced object error

[Mike Orr]

`.other_cause AS
`IN_Incident_other_cause`, `IN_Incident`.is_type_ptl_oil AS
`IN_Incident_is_type_ptl_oil`, `IN_Incident`.is_type_bio AS
`IN_Incident_is_type_bio`, `IN_Incident`.actl_is_mass AS
`IN_Incident_actl_is_mass`, `IN_Incident`.is_involve_dac AS
`IN_Incident_is_involve_dac`, `IN_Incident`.ptl_entered_min AS
`IN_Incident_ptl_entered_min` \nFROM `IN_Incident` \nWHERE
`IN_Incident`.is_top ORDER BY inews_date DESC \n LIMIT 99 OFFSET 0' []

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Weakly-referenced object error

[Mike Orr]

On 5/31/07, Mike Orr [EMAIL PROTECTED] wrote:
 I've been getting this on my Pylons site.

 sqlalchemy.exceptions.SQLError: (ReferenceError) weakly-referenced
 object no longer exists

In case it helps, the exception occurs at sqlalchemy/engine/base.py
line 583 (SQLAlchemy 0.3.7):

context.dialect.do_execute(context.cursor, context.statement,
context.parameters, context=context)

which presumably was calling sqlalchemy/databases/mysql.py lines
347-348 when the original exception thrown:

def do_execute(self, cursor, statement, parameters, **kwargs)
cursor.execute(statement, parameters)

So I'm not sure if the real error is happening in SQLAlchemy or
MySQLdb.  Inspecting the variables shows the expected SQL statement.

 --
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Weakly-referenced object error

[Mike Orr]

It happens only when the site has been idle for several hours; i.e.
overnight.  Once it starts it keeps happening every request until I
restart the application.

The first time it happened, somebody had gotten a MySQL server has
gone away error during the night, followed by a couple weak-reference
errors.  I noticed that pool_recycle wasn't being passed properly and
fixed it.

Since then I've gotten weak-reference errors almost every morning, but
without the server-gone error.  I restart the server and it runs the
rest of the day.

Today I upgraded from MySQLdb 1.2.0 to 1.2.2 so maybe that will fix it.

--Mike

On 5/31/07, Michael Bayer [EMAIL PROTECTED] wrote:

 nothing is weakly referenced within the block where that exception is
 being thrown.  the only place weakrefs are used outside of the ORM is
 a couple of dictionaries in pool.py.  you havent said what reproduces
 this problem ?  you just start the app, and it happens every time ?


 On May 31, 6:01 pm, Mike Orr [EMAIL PROTECTED] wrote:
  I've been getting this on my Pylons site.
 
  sqlalchemy.exceptions.SQLError: (ReferenceError) weakly-referenced
  object no longer exists
 
  It's trying to execute an ORM .select() call.  The first time was
  after a MySQL server has gone away error, so I assumed some mapped
  instances had been orphaned.  But now I'm still getting it even though
  I've set .pool_recycle to 1 hour and restarted the application several
  times.
 
  I've got SQLAlchemy 0.3.7 on the server, which is where the error is
  occuring.  My workstation has the trunk version, but I probably don't
  keep the app running long enough to give this a chance to happen.
 
  The full traceback follows.
 
  File '/mnt/data/www/apps/inews2/inews/controllers/main.py', line 14 in index
c.top = model.list_incidents(top_only=True)
  File '/mnt/data/www/apps/inews2/inews/models/__init__.py', line 60 in
  list_incidents
return q.select(order_by=order_by, offset=offset, limit=limit)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/query.py',
  line 319 in select
return self.select_whereclause(whereclause=arg, **kwargs)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/query.py',
  line 326 in select_whereclause
return self._select_statement(statement, params=params)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/query.py',
  line 927 in _select_statement
return self.execute(statement, params=params, **kwargs)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/query.py',
  line 831 in execute
result = self.session.execute(self.mapper, clauseelement, params=params)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/orm/session.py',
  line 183 in execute
return self.connection(mapper,
  close_with_result=True).execute(clause, params, **kwargs)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py',
  line 509 in execute
return Connection.executors[c](self, object, *multiparams, **params)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py',
  line 549 in execute_clauseelement
return self.execute_compiled(elem.compile(dialect=self.dialect,
  parameters=param), *multiparams, **params)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py',
  line 560 in execute_compiled
self._execute_raw(context)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py',
  line 573 in _execute_raw
self._execute(context)
  File 
  '/mnt/data/www/apps/inews2/wenv-inews/lib/python2.4/SQLAlchemy-0.3.7-py2.4.egg/sqlalchemy/engine/base.py',
  line 591 in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
  SQLError: (ReferenceError) weakly-referenced object no longer exists
  u'SELECT `IN_Incident`.is_type_other AS `IN_Incident_is_type_other`,
  `IN_Incident`.measure_shore AS `IN_Incident_measure_shore`,
  `IN_Incident`.ptl_search_min AS `IN_Incident_ptl_search_min`,
  `IN_Incident`.measure_burn AS `IN_Incident_measure_burn`,
  `IN_Incident`.actl_entered_max AS `IN_Incident_actl_entered_max`,
  `IN_Incident`.actl_search_min AS `IN_Incident_actl_search_min`,
  `IN_Incident`.is_type_drill AS `IN_Incident_is_type_drill`,
  `IN_Incident`.lead_ssc AS `IN_Incident_lead_ssc`,
  `IN_Incident`.measure_skim AS `IN_Incident_measure_skim`,
  `IN_Incident`.orr_id AS `IN_Incident_orr_id`, `IN_Incident`.jic AS
  `IN_Incident_jic`, `IN_Incident`.ptl_is_mass AS
  `IN_Incident_ptl_is_mass`, `IN_Incident`.actl_search_max AS
  `IN_Incident_actl_search_max`, `IN_Incident`.lon AS `IN_Incident_lon`,
  `IN_Incident`.notified_by

[sqlalchemy] Re: [Sqlalchemy-users] Objects with just some fields

[Mike Orr]

Regarding the slowness I was experiencing mapping a select (which
caused a nested SELECT in the SQL), I converted the inner select to a
MySQL VIEW and the speed improved dramatically.  The MySQL manual says
it manages a select against a view by merging them into a single
select if it can, but it doesn't seem to do that with nested selects.

EXPLAIN SELECT ... FROM the_view WHERE ...
shows it's actually selecting from the underlying table (i.e., it
lists the real table name), while eanwhile,
EXPLAIN SELECT ... FROM (SELECT ... FROM the_table) AS my_alias WHERE ...
does two selects rather than merging the SQL into one.

The one problem I discovered is that MySQL does not propagate the
primary key to the view, which causes SQLAlchemy to raise:
class 'sqlalchemy.exceptions.ArgumentError': Could not assemble any
primary key columns for mapped table 'IN_Incident'.
I had to explicitly tell SQLAlchemy which columns are primary keys:

  incidents = Table(IN_Incident, meta,
Column(orr_id, Integer, primary_key=True),
autoload=True)
entries = Table(IN_Entry, meta,
Column(entry_id, Integer, primary_key=True),
Column(orr_id, Integer, ForeignKey(incidents.c.orr_id)),
autoload=True)

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: MySQL warnings

[Mike Orr]

On 5/11/07, Mike Orr [EMAIL PROTECTED] wrote:
 I've got a MySQL warning that's sabotaging my table-update program.

The table.insert() way gave the same warning, but I finally got it to
work via plain MySQLdb:

conn = engine.connect().connection
cursor = conn.cursor()
sql = UPDATE Incident SET a=%(a)s ... WHERE ...
for i in session.query(Incident).select():
# Set local variables for the values to change.
try:
cursor.execute(sql, locals())
except _mysql_exceptions.Warning, e:
print Caught MySQLdb warning, e
cursor.execute(SHOW WARNINGS)
pprint.pprint(cursor.fetchall())

I thought maybe the problem was putting True and False in a TINYINT
column, but that works interactively, even with the ORM.  Or it may
have been putting NULL in a non-NULL field, but SQLAlchemy does
display a proper warning for this sometimes.  So I couldn't find an
error that definitively explained the warning.  My only guess is maybe
the the rows in the warning were somehow misaligned and it displayed
the wrong row, or it only displayed one warning row and didn't check
for more.

It worked with the same data on my workstation, which is Ubuntu and
something like MySQL 5.0.13.  The server is Gentoo and a slightly
older MySQL, 5.0.8 or so.  So perhaps there was a bugfix in MySQL.

Perhaps SQLAlchemy could raise a distinct SQLError subclass for
warnings, and provide the unprocessed warnings in an attribute so the
user could examine them.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: [Sqlalchemy-users] Objects with just some fields

[Mike Orr]

On 5/8/07, Michael Bayer [EMAIL PROTECTED] wrote:


 On May 8, 2007, at 8:47 PM, Mike Orr wrote:

 
  I noticed the ORM was taking a few seconds longer in my batch job.
 
  Today I converted my web application from Select to ORM so I could add
  some methods to the mapped classes, and even tried a relationship with
  a backreference (!).  That worked fine, but I notice it's doing nested
  selects again.

...

 I guess im giong to have to find a way to back this up, but its my
 belief that the database should be pretty good at optimizing nested
 selects such that it doesnt make a whole lot of difference.
 otherwise people would never use views.

I tested it today and the ORM version sometimes takes six seconds to
display a page while the non-ORM version always takes less than a
second.The longer times correspond to pages with the most result
records that haven't been viewed since the application (Pylons) was
restarted.  But sometimes the delays don't happen even under the same
conditions, so I'll have to monitor it for a while.  If it takes a few
extra seconds after the app is started, that's fine, as long as it
isn't happening all the time.

The non-ORM version has functions that create a base select with a
limited number of fields, then the caller adds clauses to it.  It also
doesn't have a relation, but the ORM SQL isn't actually using a
relation either.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: [Sqlalchemy-users] Objects with just some fields

[Mike Orr]

The actual SQL time is 1.04 second for this ORM-inspired query:

SELECT entries.category AS entries_category, entries.title AS
entries_title, entries.thumb200 AS entries_thumb200, entries.creator
AS entries_creator, entries.doctype AS entries_doctype,
entries.filename AS entries_filename, entries.content AS
entries_content, entries.entry_id AS entries_entry_id,
entries.entry_date AS entries_entry_date, entries.is_public AS
entries_is_public, entries.size AS entries_size, entries.orr_id AS
entries_orr_id FROM (SELECT `Entry`.entry_id AS entry_id,
`Entry`.orr_id AS orr_id, `Entry`.entry_date AS entry_date,
`Entry`.creator AS creator, `Entry`.title AS title, `Entry`.category
AS category, `Entry`.content AS content, `Entry`.filename AS filename,
`Entry`.thumb200 AS thumb200, `Entry`.doctype AS doctype, `Entry`.size
AS size, `Entry`.is_public AS is_public FROM `Entry` WHERE
`Entry`.is_public) AS entries WHERE entries.orr_id = 6153 and
entries.category in (1, 2, 3, 4, 6, 7, 8, 9, 10, 11);

vs 0.14 seconds for this non-ORM one:

SELECT `Entry`.entry_id, `Entry`.orr_id, `Entry`.category,
`Entry`.title, `Entry`.entry_date, `Entry`.filename, `Entry`.thumb200
FROM `Entry` WHERE (`Entry`.is_public AND `Entry`.orr_id = 6153)  AND
`Entry`.category IN (1,2,3,4,5,6,7,8,9,10,11) order by
`Entry`.entry_date DESC LIMIT 11;

vs 0.13 seconds for my manual equivalent:

select entry_id, orr_id, category, title, entry_date, filename,
thumb200 from Entry where is_public and orr_id = 6153 and category in
(1,2,3,4,5,6,7,8,9,10,11) order by entry_date desc limit 11;


vs 0.07 seconds for the same with *:

select * from Entry where is_public and orr_id=6153 and category in
(1,2,3,4,6,7,8,9,10,11);

I'm tempted to say ORM is good but not when based on a select, at
least not with this particular dataset.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: [Sqlalchemy-users] Objects with just some fields

[Mike Orr]

On 5/9/07, Michael Bayer [EMAIL PROTECTED] wrote:


 On May 9, 2007, at 2:46 PM, Mike Orr wrote:

 
  The actual SQL time is 1.04 second for this ORM-inspired query:
 
  SELECT entries.category AS entries_category, entries.title AS
  entries_title, entries.thumb200 AS entries_thumb200, entries.creator
  AS entries_creator, entries.doctype AS entries_doctype,
  entries.filename AS entries_filename, entries.content AS
  entries_content, entries.entry_id AS entries_entry_id,
  entries.entry_date AS entries_entry_date, entries.is_public AS
  entries_is_public, entries.size AS entries_size, entries.orr_id AS
  entries_orr_id FROM (SELECT `Entry`.entry_id AS entry_id,
  `Entry`.orr_id AS orr_id, `Entry`.entry_date AS entry_date,
  `Entry`.creator AS creator, `Entry`.title AS title, `Entry`.category
  AS category, `Entry`.content AS content, `Entry`.filename AS filename,
  `Entry`.thumb200 AS thumb200, `Entry`.doctype AS doctype, `Entry`.size
  AS size, `Entry`.is_public AS is_public FROM `Entry` WHERE
  `Entry`.is_public) AS entries WHERE entries.orr_id = 6153 and
  entries.category in (1, 2, 3, 4, 6, 7, 8, 9, 10, 11);
 
  vs 0.14 seconds for this non-ORM one:
 
  SELECT `Entry`.entry_id, `Entry`.orr_id, `Entry`.category,
  `Entry`.title, `Entry`.entry_date, `Entry`.filename, `Entry`.thumb200
  FROM `Entry` WHERE (`Entry`.is_public AND `Entry`.orr_id = 6153)  AND
  `Entry`.category IN (1,2,3,4,5,6,7,8,9,10,11) order by
  `Entry`.entry_date DESC LIMIT 11;
 
  vs 0.13 seconds for my manual equivalent:
 
  select entry_id, orr_id, category, title, entry_date, filename,
  thumb200 from Entry where is_public and orr_id = 6153 and category in
  (1,2,3,4,5,6,7,8,9,10,11) order by entry_date desc limit 11;
 
 
  vs 0.07 seconds for the same with *:
 
  select * from Entry where is_public and orr_id=6153 and category in
  (1,2,3,4,6,7,8,9,10,11);
 
  I'm tempted to say ORM is good but not when based on a select, at
  least not with this particular dataset.

 this test has many issues that prevents any comparison of the nested
 select - your non-ORM query has LIMIT 11 inside of it whereas your
 ORM query does not (use limit or limit() with query() for this).

The original query did have .query(...).select(limit=11), I just
forgot to paste that part into the console because I was so intent on
inlining the parameters.  Nevertheless, the non-limited queries
returned 21 records, which is pretty close.

 also I am assuming you are doing a full fetch - the ORM query has a
 lot more columns in it which will take time to be fetched

Of course, that's why I requested the 'autoload_columns' feature. :)

 (these
 columns can be set as deferred to not have them part of a default
 query).

True, but then I have to specify the columns I'm not interested in
rather than the ones I am. :) :)

Anyway, this is all just to provide feedback on how
ORM-based-on-select behaves in a real-world application.  Because the
non-ORM code is consistently fast while the ORM code is inconsistently
slow, I'd better just stick to non-ORM code for this application.  The
application is a bit unusual because of the need to exclude certain
rows and columns from all queries, which is the only reason I'm basing
it on a select -- so I can set the rule in one easy-to-audit place
rather than in every query.  I haven't tried a database view -- maybe
that will have better performance since it's built in into the
database engine.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: [Sqlalchemy-users] Objects with just some fields

[Mike Orr]

On 4/27/07, Michael Bayer [EMAIL PROTECTED] wrote:

 On Apr 27, 2007, at 2:19 PM, Mike Orr wrote:

  Is it possible to make a mapper class that loads  modifies only some
  fields in a table rather than all the fields, yet still autoloads the
  fields rather than having hardcoded column types?

 you want to use a deferred column :

 http://www.sqlalchemy.org/docs/
 adv_datamapping.html#advdatamapping_properties_deferred

That puts the burden in the wrong place: (1) I have to list all the
undesired columns by name, (2) SQLAlchemy has to go to the trouble of
making it a deferred column when I'd prefer it just ignore it
entirely.

 yeah when you map to a select, it treats that like a table, and
 selects from it so that it can be manipulated similarly.

Is it safe to do updates on an object mapped to a select?  E.g.,

_sel = select([... fields ...])
class Foo(object):  pass
mapper(Foo, _sel)
for i in session.query(Foo).select():
i.attr = Value
session.flush()

  What I'd like to do is pass a list of column names to the Table
  constructor and have it autoload those and ignore the others.  I
  couldn't find an argument for this.

 oh.  well that we haven't done yet.  the usual use case is that if
 you know the names of the columns already, why reflect them ?  but
 yes i understand the advantage of getting the data types and foreign
 key constraints reflected.  this wouldnt be a difficult feature to add.

Created ticket #561 suggesting:

Table(Foo, meta, autoload_columns=[foo_id, col2, col3])

  - Is there a supported way to add/remove fields from a query after
  it's been constructed?

 from a select(), it supports adding things.  there is append_column
 (), append_whereclause(), order_by(), etc.  this API needs docstrings
 and probably the names order_by/group_by should be named
 append_order_by() etc.but you can see the methods listed out in
 the HTML docstrings on the site (html docs also included with the dist).

 removing things is not stressed so much since the typical use case is
 building up a query from a core criterion/selectable, and we have
 the usual issue about decisions being made based on things being
 appended, which to support removal would mean a lot of new code
 (which id welcome, of course !) to support un-making those decisions.

.append_column just needs to be documented in the manual.
.remove_column would be useful, but it's not that important if it's
complicated to implement.  (I thought the column list was just a
simple list until the query was compiled.)  I wasn't suggesting
.remove_whereclause or .remove_order_by -- I don't see any point for
those, and how would one identify the element to remove anyway?

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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: Calculated columns

[Mike Orr]

On 2/12/07, Jonathan Ellis [EMAIL PROTECTED] wrote:

 Instead of mapping your table directly, map a select containing the coalesce:

 incidents_with_activity = select([incidents,
 func.coalesce(...).label('activity')]).alias('incidents_with_activity')
 assign_mapper(Incident, incidents_with_activity)

 then you can use the activity label anywhere in the ORM queries.

Thanks, that works perfect.  I just had to give my select an alias to
avoid a mapper exception, and cast the result to a date because it was
defaulting to a string.  For the record, here's my code now.

===MODEL===
import pylons.database
import sqlalchemy as sa
from sqlalchemy.ext.assignmapper import assign_mapper

ctx = pylons.database.session_context
engine = ctx.current.bind_to
meta = sa.BoundMetaData(engine)

incident_table = sa.Table(Incident, meta, autoload=True)
entry_table = sa.Table(Entry, meta, autoload=True)

inews_date_column = sa.func.date(
sa.func.coalesce(
incident_table.c.last_entry_date,
incident_table.c.activity_date,
incident_table.c.create_date,
)).label(inews_date)

incident_select = sa.select([
incident_table,
inews_date_column,
]).alias(incident_select)

class Incident(object):
pass

class Entry(object):
pass

assign_mapper(ctx, Incident, incident_select)
assign_mapper(ctx, Entry, entry_table)

===DATA RETRIEVAL===
cols = Incident.c
query = ctx.current.query(Incident)
recent = query.select(cols.is_public,
order_by=[sa.desc(cols.inews_date)], limit=recent_limit)

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---