Re: [sqlalchemy] Calling stored procedures in SQLAlchemy
Yes. I tried adding the autocommit execution option and calling execute, as well as turning autocommit off and manually calling commit. Like I said, the stored procedure is getting run because the primary key is incrementing internally, (that is to say, that if my last id entry was 1, I run this code, and then on the next insert the id entry is 3) and the results are getting rolled back. On Thursday, May 31, 2012 7:01:53 PM UTC-7, Michael Bayer wrote: did you call Session.commit() ? otherwise you're still in an open transaction, assuming default settings. Session.execute() is not the same as engine.execute(), where the latter is autocommitting (assuming you also called execution_options(autocommit=True) for this particular text() construct). On May 31, 2012, at 9:23 PM, Will Orr wrote: Hello all! I'm having this *exact* bug from a few years ago wrt. calling stored procedures. https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/qA_ypVgJ1B0 What makes it worse, however, is that adding the autocommit execution option or explicitly starting and stopping a transaction do nothing. session.execute(text('call add_logentry(:username, :hostname, :action, \'-00-00 00:00:00\')'), { 'username': username, 'hostname': hostname, 'action' : action }) There's the code. It should insert some values into some tables, however those values are never inserted, though the primary key counter is incremented. I'm using SQLAlchemy with ZopeTransactionExtension. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/si1vqn5kmjoJ. 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/5y7_u014dn8J. 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] Calling stored procedures in SQLAlchemy
Hello all! I'm having this *exact* bug from a few years ago wrt. calling stored procedures. https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/qA_ypVgJ1B0 What makes it worse, however, is that adding the autocommit execution option or explicitly starting and stopping a transaction do nothing. session.execute(text('call add_logentry(:username, :hostname, :action, \'-00-00 00:00:00\')'), { 'username': username, 'hostname': hostname, 'action' : action }) There's the code. It should insert some values into some tables, however those values are never inserted, though the primary key counter is incremented. I'm using SQLAlchemy with ZopeTransactionExtension. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/si1vqn5kmjoJ. 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] Pool size question
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
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
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
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?
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
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
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
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
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
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
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
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
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
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
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
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
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
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)
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
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
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
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
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.
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
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
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 !
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() ?
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)
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
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
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
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
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
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
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?
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?
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?
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
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)
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
.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
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
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
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
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
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
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
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
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]
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
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
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?
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
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?
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
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
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
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]
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
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
`.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
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
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
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
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
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
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
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
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
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 -~--~~~~--~~--~--~---