[sqlalchemy] How to get the character limit of a string or unicode table column
Hi all, I'm trying to implement a character counter on all textarea fields in my Elixir-SQLA-Pylons app, need to find a way to determine the maximum number of characters a given column can hold, so I can supply the character counter with a Maximum characters value. Anyone know of a simple method of finding the size-limit of a column? --~--~-~--~~~---~--~~ 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] SQLAlchemy syntax for part of a datetime value in order_by.
Hi all, I have a table with a column called created_at which is a datetime field. I'm trying to construct a query where I would like the results ordered by just the date portion of the datetime field, but I can't find any reference to the correct syntax for such a query. This works: query = model.MyClass.q.order_by(model.MyClass.created_at).all() ...but it's not what I'm after. I need to order by only the date not by the date and time. Does anybody have any tips on how to do this? --~--~-~--~~~---~--~~ 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: How to specify NOLOCK queries in SA (mssql)
Hi Rick, This could involve quite a bit of investigation on our part, so it could take a while before we get to the bottom of it. We're running Windows 2003 as the OS, MS SQL Server 2005, via the SQL Server Native Driver, using PYODBC, Apache 2.2.6 mod_python 3.3.1. The app is based on Pylons, using SQLAlchemy-0.4.4dev_r3557-py2.5 Elixir-0.5.0dev_r2495-py2.5. Now for what it's worth, we made the change I mentioned above to our db. Before the change, we were getting 36 maximum concurrent locks in a day. after the change, that figure went down to 400. It also reduced the load on our CPUs by about 10%. So it helped. We will be doing more examination of what's happening with cursors next week...I'll keep you posted. :) On May 16, 2:20 am, Rick Morrison [EMAIL PROTECTED] wrote: Hi Bruce, I'm considering a switch from pymssql to pyodbc myself in the not-too-distance future, and this thread has me a bit curious about what's going on. This is a subject that may affect SQL more in the future when ODBC and JDBC drivers get more use. I think there's two distinct questions that need to be answered to get to the bottom of this. The first question is why are these queries being issued at all, and from where? Like Mike says, SQLA is playing no part in constructing or issuing these queries. From the bit of googling that I've done so far, it seems that the FMTONLY queries are issued behind the scenes by the data connector to fetch metadata regarding the query. While there's a lot of reasons a data connector might need to have metadata, there's two that seem especially likely when SQLA comes into play: a) There are un-typed bind parameters in the query, and the connector needs to know the data types for some reason. b) There is going to be a client-side cursor constructed, and result metadata is needed to allocate the cursor. From the description you give, I would bet that this is your main issue. If the cause is (a), a fix might be problematic, as SQLA issues all of its queries using bind parameters, and I'm not sure if type information is used for each. But if you're using explicit bind parameters, you may want to specify the type on those. As for the more likely cause (b) I would think this could be gotten around by making sure you specify firehose (read-only, forward-processing, non-scrollable) cursors for retrieval, but I'm not sure what the pyodbc settings for this might be. As a bonus, you'll probably see a bit of a performance boost using these types of cursors as well. The second question is more of a mystery to me: ok, so the data connector issues a FMTONLY queryif it's just fetching metadata, why would that cause database locks?. This one I can't figure out. Unless you're calling stored procedures or UDF's that have locking side effects, It's got to be a bug in the data connector. From what I read a FMTONLY query should be pretty fast (other than the round-trip network time), and should lock nothing. Are you running on Windows, or on Unix? What's your ODBC connector? Please post to the list as you work through this and let us know what you find... Rick --~--~-~--~~~---~--~~ 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: How to specify NOLOCK queries in SA (mssql)
Thanks for your speedy response Michael :) Yes, after more searching it does rather look like this is an issue that has to do with MSSQL, rather than SQLA. This post (http:// forums.microsoft.com/MSDN/ShowPost.aspx?PostID=570896SiteID=1) seems to point to a similar opinion, points the finger at db connector parameters. For anyone else who reads this looking for an answer to their own issue, so you know, we have decided to make changes to our database configuration options to remove the default locking of records during a query, as that looks like it may resolve our particular issue. We have the luxury of not having our data used in a way that requires locking. This approach may not be appropriate for other users, but it does suit our needs, so we'll try this out... On May 14, 11:53 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 13, 2008, at 9:46 PM, BruceC wrote: Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I cant locate the string FMTONLY within the MSSQL dialect at all. So this is not something SQLA is issuing. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? our MSSQL devs might be able to help here but also you might want to see what the source of the locking is (i.e. some (Py)ODBC thing); SQLA is not issuing any locking. --~--~-~--~~~---~--~~ 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: How to specify NOLOCK queries in SA (mssql)
Just to be a bit more specific, these are the changes we will try in our mssql db: ALTER DATABASE MyDBName SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDBName SET READ_COMMITTED_SNAPSHOT ON :) On May 14, 4:36 pm, BruceC [EMAIL PROTECTED] wrote: Thanks for your speedy response Michael :) Yes, after more searching it does rather look like this is an issue that has to do with MSSQL, rather than SQLA. This post (http:// forums.microsoft.com/MSDN/ShowPost.aspx?PostID=570896SiteID=1) seems to point to a similar opinion, points the finger at db connector parameters. For anyone else who reads this looking for an answer to their own issue, so you know, we have decided to make changes to our database configuration options to remove the default locking of records during a query, as that looks like it may resolve our particular issue. We have the luxury of not having our data used in a way that requires locking. This approach may not be appropriate for other users, but it does suit our needs, so we'll try this out... On May 14, 11:53 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 13, 2008, at 9:46 PM, BruceC wrote: Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I cant locate the string FMTONLY within the MSSQL dialect at all. So this is not something SQLA is issuing. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? our MSSQL devs might be able to help here but also you might want to see what the source of the locking is (i.e. some (Py)ODBC thing); SQLA is not issuing any locking. --~--~-~--~~~---~--~~ 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] How to specify NOLOCK queries in SA
Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I'm assuming that this is getting information about the model in preparation for another query (perhaps an insert). It returns the relevant column name, but no data , as 1 can obviously never equal 2. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? --~--~-~--~~~---~--~~ 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: ODBC Connection is busy error
We're not intentionally sharing a session between multiple threads... :) I'll check out the code to see if there is any sharing going on, but I'm pretty sure that's not the case. You mention lazy-loading as if it might be playing a part in this issue - is that right? We are using lazy eager loading in various parts of the app... On Apr 28, 2:04 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 28, 2008, at 12:02 AM, Michael Bayer wrote: On Apr 27, 2008, at 9:58 PM, BruceC wrote: I don't know whether this helps, but many thanks for looking at the issue :) unfortunately it doesnt say much at all. You're not sharing a Session between multiple threads, right ? and by sharing, I mean the Session, as well as any objects that are attached to it, since that stack trace illustrated specifically an attribute lazy-loading its contents from the database. --~--~-~--~~~---~--~~ 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: ODBC Connection is busy error
Hi Michael, Thanks for your persistence :) I've searched through my app, we don't seem to have any calls to fetchone(), so I'm not sure what else to look for, but I'll try to do some debugging with ResultProxys, see if that leads to any possible answers. On a side note, in our Pylons development.ini, we have these settings: sqlalchemy.default.max_overflow = -1 sqlalchemy.default.pool_size = 32 Is there anything there that could be having an effect on this? On Apr 25, 2:21 pm, Michael Bayer [EMAIL PROTECTED] wrote: only if youre leaving dangling ResultProxys opened with pending results, which should be a fairly rare occurence in a normal application. if you simply call fetchall() on results instead of fetchone(), that alone would eliminate any hanging cursors. --~--~-~--~~~---~--~~ 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: ODBC Connection is busy error
Our Pylons setup emails every traceback error to our developers,
with about 80 users, we've been getting a couple of hundred error
emails a day, almost all with this same error (Tomorrow we will have
about 1200 users on our system). The following is an excerpt of the
traceback message. Every error has at least these final lines of the
message in common:
Module sqlalchemy.orm.attributes:44 in __get__
if instance is None:
return self
return self.impl.get(instance._state)
def get_history(self, instance, **kwargs): return
self.impl.get(instance._state)
Module sqlalchemy.orm.attributes:279 in get
if passive:
return PASSIVE_NORESULT
value = callable_()
if value is not ATTR_WAS_SET:
return self.set_committed_value(state,
value) value = callable_()
Module sqlalchemy.orm.strategies:483 in __call__
q = q.filter(strategy.lazy_clause(instance))
result = q.all()
if strategy.uselist:
return result result = q.all()
Module sqlalchemy.orm.query:860 in all
This results in an execution of the underlying query.
return list(self) return list(self)
Module sqlalchemy.orm.query:921 in __iter__
if self._autoflush and not self._populate_existing:
self.session._autoflush()
return self._execute_and_instances(context)
def _execute_and_instances(self, querycontext): return
self._execute_and_instances(context)
Module sqlalchemy.orm.query:924 in _execute_and_instances
def _execute_and_instances(self, querycontext):
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper,
instance=self._refresh_instance)
return self.iterate_instances(result,
querycontext=querycontext) result =
self.session.execute(querycontext.statement, params=self._params,
mapper=self.mapper, instance=self._refresh_instance)
Module sqlalchemy.orm.session:605 in execute
engine = self.get_bind(mapper, clause=clause, **kwargs)
return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
def scalar(self, clause, params=None, mapper=None,
**kwargs): return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
Module sqlalchemy.engine.base:846 in execute
for c in type(object).__mro__:
if c in Connection.executors:
return Connection.executors[c](self, object,
multiparams, params)
else:
raise exceptions.InvalidRequestError(Unexecutable
object type: + str(type(object))) return Connection.executors[c]
(self, object, multiparams, params)
Module sqlalchemy.engine.base:897 in execute_clauseelement
else:
keys = None
return
self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
def _execute_compiled(self, compiled, multiparams=None,
params=None, distilled_params=None): return
self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
Module sqlalchemy.engine.base:909 in _execute_compiled
context.pre_execution()
self.__execute_raw(context)
context.post_execution()
self._autocommit(context) self.__execute_raw(context)
Module sqlalchemy.engine.base:918 in __execute_raw
self._cursor_executemany(context.cursor,
context.statement, context.parameters, context=context)
else:
self._cursor_execute(context.cursor,
context.statement, context.parameters[0], context=context)
def _execute_ddl(self, ddl, params, multiparams):
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
Module sqlalchemy.engine.base:962 in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters,
context=context)
except Exception, e:
self._handle_dbapi_exception(e, statement, parameters,
cursor)
raise
self._handle_dbapi_exception(e, statement, parameters, cursor)
Module sqlalchemy.engine.base:944 in _handle_dbapi_exception
if self.__close_with_result:
self.close()
raise exceptions.DBAPIError.instance(statement,
parameters, e, connection_invalidated=is_disconnect)
finally:
del self._reentrant_error raise
exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
DBAPIError: (Error) ('HY000', '[HY000] [Microsoft][SQL Native
Client]Connection is busy with results for another command (0)')
u'SELECT ...snip...'
I don't know whether this helps, but many thanks for
[sqlalchemy] Re: ODBC Connection is busy error
Thank you to everybody for your comments on this problem... Michael, re: your suggestion about result.close(), is this something that I could add to mssql.py, or do you think it's something that I would need to add throughout my application everytime I access the db? (It's a big application...) Cheers! On Apr 25, 4:15 am, Michael Bayer [EMAIL PROTECTED] wrote: So if you are using straight connection or engine result sets, and are not explicitly exhausting all rows, call result.close() to explicitly release the cursor. That will solve the problem. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pagination problems using SQLAlchemy MSSQL in Pylons - no OFFSET
Thank you all for looking into this - most appreciated! On Jul 10, 6:13 pm, Paul Johnston [EMAIL PROTECTED] wrote: Hi, if 2005 supports ROW_NUMBER() OVER ORDER BY like oracle does, then yes this could be a possible feature enhancement. Another approach that occured to me is doing TOP (limit+offset) then seeking the cursor. For server-side cursors this is probably nearly as efficient as LIMIT/OFFSET. I've raised #639 so we don't forget about this. Paul --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
