Re: [sqlalchemy] Ubuntu install
easy_install.main(['-Zmad', sitePKG, sqlalchemy]) But this gets me 0.6.6. Gets 0.6.6 if I do the above on Ubuntu 10.10 Maverick, just did the same in a VirtualBox/Win 7 machine and I get 0.6.7. That's odd. Thank you for the feedback. -- Michael Trier http://michaeltrier.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.
Re: [sqlalchemy] Support for SPARSE columns of SQL SERVER
Is there a way to specify that a column of a table being created be SPARSE as supported by SQL SERVER ? You want a User Defined Type as described here: http://www.sqlalchemy.org/docs/core/types.html#creating-new-types It's simple to do and allows you to extend your column definition to add the SPARSE keyword. -- Michael Trier http://michaeltrier.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.
Re: [sqlalchemy] short question on Table
Does anyone know how I can add a Column to an existing not yet mapped Table? To add a column use append_column: http://www.sqlalchemy.org/docs/core/schema.html#sqlalchemy.schema.Table.append_column Note that doesn't do anything database schema wise for you. -- Michael Trier http://michaeltrier.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.
Re: [sqlalchemy] How to find columns being updated/inserted given a Update/Insert object
Given an Update(or Insert) object how can I find the columns being updated (or Inserted). I dont find any function that gives these? The attributes.get_history method will return this information to you as a History record for each attribute. See here: http://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.attributes.get_history -- Michael Trier http://michaeltrier.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.
Re: [sqlalchemy] Re: Context based execution
The idiomatic way to modify the insert/update/delete construct within a flush is to use MapperExtension before_insert/before_update/before_delete to modify the mapped object's state right before it's flushed. Is there a recipe/code which I can quickly refer to get started on before_insert and also how to modify a insert statement in before_insert? This is the area of the docs that describe before_insert and other MapperExtensions. It's laid out pretty clearly and quite easy to implement: http://www.sqlalchemy.org/docs/orm/interfaces.html#mapper-events -- Michael Trier http://michaeltrier.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.
Re: [sqlalchemy] SQLAlchemny SAWarning - which Session Query is giving this?
SQLAlchemnt 0.6.5 - in stderr log we continuously see the following
warning logged. My understanding is this is due to IN () generated
with EMPTY sequence.
However on turning on SQL Alchemny Log, I do not find ant statement
that has where clause with EMPTY IN sequence for predicate
suspiciousevents.rule_id. Any suggestions how to find due to which
session query this warning is coming?
You won't find that because under the hood SQLAlchemy is converting that
into x x. So that's what you would look for in the logs. For some
background on the issue see this post:
http://romain.dorgueil.net/en/database/2011/01/10/handling-empty-where-in-clauses-in-dbals.html
2011-04-12 15:23:01,835 INFO sqlalchemy.engine.base.Engine.0x...b6d0
('multicore', 2120)
/opt/python26/lib/python2.6/site-packages/SQLAlchemy-0.6.5-py2.6.egg/
sqlalchemy/sql/expression.py:1660: SAWarning: The IN-predicate on
suspiciousevents.rule_id was invoked with an empty sequence. This
results in a contradiction, which nonetheless can be expensive to
evaluate. Consider alternative strategies for improved performance.
return self._in_impl(operators.in_op, operators.notin_op, other)
--
Michael Trier
http://michaeltrier.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.
Re: [sqlalchemy] Execute a function on orphan
On Sat, Apr 9, 2011 at 10:32 PM, Hector Blanco white.li...@gmail.comwrote: Unassigning a user from its userGroup, indeed, puts the UserGroup in that user to NULL. But it turns out that the entity that controls the permissions is a third thing. That thing takes some fields of the User class (id, name...) and generates an instance of a third object which is in charge of controlling the permissions. That third entity is what effectively has the permissions to access (or not) the application. Then, when a user is unassigned from a group, I have to take that third entity corresponding to that user and remove its permissions. I'm with Mike. It seems the architecture is wrong. That said if you're on 0.7 you can use signals to handle this. On 0.6 you might want to look into AttributeExtension. http://www.sqlalchemy.org/docs/07/orm/events.html http://www.sqlalchemy.org/docs/orm/interfaces.html?highlight=attributeextension#sqlalchemy.orm.interfaces.AttributeExtension -- Michael Trier http://michaeltrier.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.
Re: [sqlalchemy] mssql 2000 Date type on sa 0.6 maps to non-existant DATE type
On Jun 8, 2010, at 5:38 PM, Clovis Fabricio wrote: I'm connecting to mssql server 2000 through pyodbc, via FreeTDS odbc driver, on linux ubuntu 10.04. Sqlalchemy 0.5 uses DATETIME for sqlalchemy.Date() fields. Now Sqlalchemy 0.6 uses DATE, but sql server 2000 doesn't have a DATE type. I'm aware that sqlalchemy 2005 has the DATE type, but sqlalchemy 2000 doesn't. according to our source, DATE is only available on 2008, not 2005 or 2000. I'm not 100% sure that is correct. Correct. On SQL 2000 and 2005 there was only datetime and smalldatetime. Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] SQLAlchemy 0.6beta2 released
Hello, On Mar 21, 2010, at 10:43 AM, Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: SQLAlchemy 0.6beta2 is now available.This beta may be the last before the 0.6.0 final release. We've hopefully gotten every largish change into the release as possible so that people can test. 0.6 is already running on a number of production servers and is already widely tested on mainstream platforms. Big new things in this release include: [...] Do you plan to implement ticket #877 for the 0.6.0 final release? What about ticket #1679, for SQL Schema support? #877 would need some tests to be considered. The patch doesn't seem to be available for #1679. I guess a result of the new server migration. Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: ms sql server schema.sequence
Hello Tan, On Mar 16, 2010, at 7:57 PM, Tan Yi wrote: I mean, I tried to debug a program, using session add() to insert new records to database after I deleted all the old records from a table. I used the schema.sequence() to build up a mapper Can anyone help? On 3月16日, 下午2时05分, Tan Yi tonytan198...@gmail.com wrote: Whenever I try to use schema.sequence() function on ms sql server, the return serial number (after flush()) is not started from 0, instead, it is started from a random number or something. wondering how to work aroud this issue. try to specify start = 0 in sequence function, but no luck With MSSQL whenever you delete records from a table it does not reset the IDENTITY. If you would like the identity to reset back to one (zero is not an option), then you need to truncate the table with: TRUNCATE TABLE tablename; If this is not the issue then it might be helpful if you provide a test case that demonstrates the issue you are having. It will be easier to diagnose that way. Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Can we use dates to define a relation with the mapper ?
Hello,
On Mar 4, 2010, at 10:50 PM, Richard Lopes wrote:
Hi,
I have this mapper defined:
mapper(Resource, resource_table,
properties = {'type' : relation(ResourceType,lazy = False),
'groups' : relation(Group, secondary =
model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date datetime.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
But for some reason, if I create a new row in the relation table and
change the end_date of the old row in the relation to an old date, the
property parent is not updated.
Also if a reload the resource row, the old relation with the old date
is displayed, so I am pretty sure it has to do with the date
comparison in the mapper.
If I replace the end_date by a flag column string or integer and do a
comparison on the flag I get the proper behaviour, but I do want to
use dates.
I imagine you're getting bitten because your datetime.now() is getting
evaluated at compile time. You might need to make it a callable. That said I'm
unsure about whether or not a callable will work with SQLAlchemy. I might be
able to write a test case tomorrow.
Michael
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] compressed version of a field
Hello Jinal, On Mar 1, 2010, at 7:39 PM, Jinal Jhaveri wrote: I would like to store a zlib compressed version of a table field. Whats the best way to handle this where all gets to that field decompress the content and all sets to that field compresses the content You probably want to look at a field type example. The first one I would suggest is the PickleType that is located in lib/sqlalchemy/types.py. They key items are the impl attribute, the bind_processor (sets), and the result_processor (gets). Some of the other types in that file might also be useful for you to look at. Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] SQLAlchemy 0.6beta1: AttributeError: 'Text' object has no attribute 'get_col_spec'
Hi, On Mar 1, 2010, at 8:31 PM, Gerry Reno wrote: Ok, I fixed all the boolean clause tests but now I'm getting this exception on a Text object: AttributeError: 'Text' object has no attribute 'get_col_spec' I see where the type system has been revamped but isn't get_col_spec still there? According to the CHANGES doc: - new UserDefinedType should be used as a base class for new types, which preserves the 0.5 behavior of get_col_spec(). Is that what you're looking for? Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] ticket #877
On Feb 28, 2010, at 11:21 AM, Manlio Perillo wrote: By the way, it seems there is a bug in the documentation: http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?highlight=create_engine#sqlalchemy.create_engine echo=False – if True, the Engine will log all statements as well as a repr() of their parameter lists to the engines logger, which defaults to sys.stdout. The echo attribute of Engine can be modified at any time to turn logging on and off. If set to the string debug, result rows will be printed to the standard output as well. This flag ultimately controls a Python logger; see dbengine_logging at the end of this chapter for information on how to configure logging directly. However there is no dbengine_logging section! This doc change has been committed. See http://www.sqlalchemy.org/trac/changeset/6872 Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] sqlalchemy 6beta1 error
Hello, On Feb 26, 2010, at 2:32 AM, karikris...@gmail.com wrote: I could not get from sqlalchemy.databases.mysql import MSBinary working on my windows XP as well as my Ubuntu 32 and 64 bit machines. Are they dropped? Here are summary ActivePython 2.6.0.0 (ActiveState Software Inc.) based on Python 2.6 (r26:66714, Nov 11 2008, 10:21:19) [MSC v.1500 32 bit (Intel)] on win 32 Type help, copyright, credits or license for more information. import sqlalchemy sqlalchemy.__version__ '0.6beta1' from sqlalchemy.databases.mysql import MSBinary Traceback (most recent call last): File stdin, line 1, in module ImportError: No module named mysql In 0.6 we moved the database backends to dialects. So it would be: from sqlalchemy.dialects.mysql.base import MSBinary Thanks, Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] server_version_info
Hello, On Feb 24, 2010, at 11:59 AM, Gregg Lind wrote: Is there a nice way to get server_version_info from an existing connection or engine? Right now it looks quite buried in (for pg): sqlalchemy.database.postgres.PGDialiect().server_version_info(myconnection). The dialect contains the server_version_info, so if you have a connection you can get to the dialect through: connection.dialect.server_version_info Also the Engine has the dialect. Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] backref questions
Hi,
On Feb 10, 2010, at 6:54 PM, David Ressman wrote:
Hello,
First of all, I'll apologize if this is a really basic question. I've not
been using SQLAlchemy for long, and I've only very recently picked up Python.
Even though I've looked everywhere I can think of for an answer to this
question, I'm almost certain that it is not a difficult one. I'm happy to
read through any documentation you can point me to, but I've not been able to
see what might be relevant to this particular question.
...
What I want is an easy way to access a user's n most recent Usage objects for
each filesystem. (For the purpose of this e-mail, we can take n=1.) It would
be easy enough for me to just take the last m records in the
user_obj.usage_data list (where m is the number of filesystems for which this
user has records), but that's not really what I want. One filesystem might be
storing records every hour, and another might be storing them every day. In
that case, it would be hard to know how many records I would need to take
from user_obj.usage_data to have the most recent record from each filesystem.
It wouldn't be hard to actually use a session object to build a query for
this, but I'd really like to have this all taken care of in the
objects/maps/relations/whatever themselves. I'd like to have some attribute
in the User object that's like user_obj.usage_data, but instead gives me a
list of only the most recent Usage object from each filesystem, so I'd see
something like:
user_obj.most_recent_usage
[Usage('user', 'fs1', 'some-date', foo:bar),
Usage('user', 'fs2', 'some-other-date', f0o:bAr),
Usage('user', 'fs3', 'some-third-date', fo0:b4r)]
I hope I was clear in my description. If I've left anything out, I'll be
happy to clarify.
What you likely want to dig into is Query enabled properties
(http://www.sqlalchemy.org/docs/mappers.html?highlight=property%20association#building-query-enabled-properties).
I used this a lot of times to tie what appears to be a relationship but that
is driven by an underlying query. I don't have time right now to whip
up the action property, but if you play with it a bit I'm sure you'll be able
to get there.
Michael
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] newbie to sqlalchemy :not null constraint
hello all, I am newbie in sqlalchemy.I am thrilled by the sqlachemy features. But i got struck in the how to write the not null for the following: create table organisation(orgcode varchar(30) not null,orgname text not null,primary key(orgcode)); I have written the using declarative base as follows but dont know how to impose not null constraint You want to use the nullable=False argument. http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html?highlight=nullable Thanks, Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: pyodbc - sqlserver in mac os x
On Feb 8, 2010, at 3:14 PM, Michael Bayer wrote: Domingo Aguilera wrote: Did you use iodbc or unixodbc ? OSX has some kind of odbc built in, I used that. I think there was a document somewhere regarding how it all works (maybe try googling mssql + osx). Yeah it's an implementation of iODBC and that's what I got working. I tried to use unixodbc on my new Mac but haven't got it working yet. Mainly due to some 64bit to 32bit incompatibilities. Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: 0.6b1 and pymssql
Hi, On Feb 7, 2010, at 9:59 AM, Domingo Aguilera wrote: I tried 0.8 with 0.6b1 but didn't work. Both 0.8 and 1.0.2 work with sqla 0.5.8 without problems. I began trying using pyodbc in ubuntu yesterday but can't even make it run unixodbc with freetds to reach a sqlserver . If someone has configuration tips ot make this work please let me know. This is on a pylons application, not that it matters. I'm using the following configuration. You can just translate it into the appropriate engine connection statement: sqlalchemy.url = mssql://user:passw...@server/database?driver=FreeTDSport=50008 sqlalchemy.convert_unicode = True sqlalchemy.encoding = 'latin1' My odbcinst.ini looks like: #: cat odbcinst.ini [FreeTDS] Description = TDS driver Driver = /usr/lib/odbc/libtdsodbc.so Setup = /usr/lib/odbc/libtdsS.so CPTimeout = CPReuse = I have nothing in my freetds.conf. It was a pain to get it working right. What I recommend is you test a piece at a time. Make sure you can connect with just straight FreeTDS. Then make sure you can connect with straight FreeTDS going through unixodbc, and so forth. Incidentally the configuration on my Mac is completely different. Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: 0.6b1 and pymssql
On Feb 7, 2010, at 12:52 PM, Domingo Aguilera wrote: Michael, I installed the tdsodbc package and now it's working in the ubuntu server. Tks a lot !! On Feb 7, 11:33 am, Domingo Aguilera domingo.aguil...@gmail.com wrote: Tks Michael, I never saw the setup directive. Did you compile driver and setup by yourself or used the anything from a debian or rpm package? Great. Yeah I didn't compile anything, just used the debian package. Glad you got it working. Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Behaviour of related queries
I have an object from the ORM called obj which has an attribute obj.related_objects. If I do for ro in obj.related_objects: does that do a query for each iteration? I have thousands of related_objects per obj and it is not as fast as i would like. It will if you don't eager load the related objects. See this section of the ORM tutorial for more information about this: http://www.sqlalchemy.org/docs/05/ormtutorial.html?highlight=lazy#working-with-related-objects Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: django like inspectdb
Hi, On Fri, Aug 7, 2009 at 1:41 AM, dusans dusan.smit...@gmail.com wrote: is there something similar to inspectdb in sqlalchemy where it returns orm classes for tables already in the db? - You mean like this: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode Also if you don't want the actual classes just use autoload. -- Michael Trier http://michaeltrier.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: More SA, pyodbc, *nux and MSSQL problems
Finally my stack is: Mac: SA - iODBC - FreeTDS - pyodbc - MSSQL Ubuntu: SA - unixODBC - FreeTDS - pyodbc - MSSQL Of course you can remove the xODBC part of the equation if you want, but the results are the same. Are you sure this is correct? I'd understood the order of my stack to be: Mac: SA - pyodbc - unixODBC - FreeTDS - MSSQL Ubuntu: SA - pyodbc - unixODBC - FreeTDS - MSSQL With pyodbc being a layer between SA and freetds, and as you said, xODBC being optional if you don't mind not using DSNs. Sorry my mistake. You are correct. -- Michael Trier http://michaeltrier.com/ http://thisweekindjango.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: More SA, pyodbc, *nux and MSSQL problems
Hi, On Wed, Jul 29, 2009 at 1:30 PM, Ed Singleton singleto...@gmail.com wrote: Stupidly forgot to attach the files. What I'm trying to achieve is: 1) Ability to insert non-ascii chars into the db on Linux 2) The above but for all column types (varchar, text, nvarchar, ntext) Absolutely any ideas will be appreciated. I'm not sure what to try next. For now I'm going to document how I set up Linux. Excellent. I'll dig into this tomorrow if I get a chance to see if I can help propel this along. -- Michael Trier http://michaeltrier.com/ http://thisweekindjango.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: More SA, pyodbc, *nux and MSSQL problems
Hi On Tue, Jul 28, 2009 at 3:14 PM, Ed Singleton singleto...@gmail.com wrote: On 26 Jul 2009, at 15:06, Michael Bayer wrote: i have freetds 0.82, pyodbc 2.1.4. except for binary it mostly works fine (with sqla 0.6). Is that on Mac, Linux or both? Did you do any particular configuration of character encodings? I've worked a lot recently in both of these environments. With Mac and Linux I've experienced the same behavior; that is that you can't pass unicode statements and you can't pass unicode parameters directly, like you can when working just with pyodbc on Windows. With Mac and Linux you need to ensure that: engine.dialect.supports_unicode = False engine.dialect.supports_unicode_statements = False Additional I've had to set convert_unicode to True and the encoding to Latin1. Finally one other import factor, pyodbc will not work properly as is on *nix environments like Mac or Ubuntu, because it won't accept UCS2 like it will directly on Windows. To counter this you need to do something like: class CustomString(MSString): MSSQL VARCHAR type, for variable-length non-Unicode data with a maximum of 8,000 characters. def bind_processor(self, dialect): if self.convert_unicode or dialect.convert_unicode: if self.assert_unicode is None: assert_unicode = dialect.assert_unicode else: assert_unicode = self.assert_unicode def process(value): if isinstance(value, unicode): return value.encode(dialect.encoding) elif assert_unicode and not isinstance(value, (unicode, NoneType)): if assert_unicode == 'warn': util.warn(Unicode type received non-unicode bind param value %r % value) return value else: raise exc.InvalidRequestError(Unicode type received non-unicode bind param value %r % value) else: return value return process else: return None from sqlalchemy.databases.mssql import MSString, MSText import sqlalchemy.util as util from sqlalchemy import exc class CustomText(MSText): MSSQL TEXT type, for variable-length text up to 2^31 characters. def bind_processor(self, dialect): if self.convert_unicode or dialect.convert_unicode: if self.assert_unicode is None: assert_unicode = dialect.assert_unicode else: assert_unicode = self.assert_unicode def process(value): if isinstance(value, unicode): return value.encode(dialect.encoding) elif assert_unicode and not isinstance(value, (unicode, NoneType)): if assert_unicode == 'warn': util.warn(Unicode type received non-unicode bind param value %r % value) return value else: raise exc.InvalidRequestError(Unicode type received non-unicode bind param value %r % value) else: return value return process else: return None This ensures that unicode gets converted properly. By default we ignore the convert_unicode when using pyodbc, but that won't work with freetds in the mix. We plan to correct this in 0.6 with the ability to pass additional dbapi information. Finally my stack is: Mac: SA - iODBC - FreeTDS - pyodbc - MSSQL Ubuntu: SA - unixODBC - FreeTDS - pyodbc - MSSQL Of course you can remove the xODBC part of the equation if you want, but the results are the same. -- Michael Trier http://michaeltrier.com/ http://thisweekindjango.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: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Mon, Jun 8, 2009 at 5:29 PM, Michael Bayer mike...@zzzcomputing.comwrote: I'd rather a flag, or better yet in 0.6 a freetds specific dialect/url, i.e. mssql+freetds://url. I personally like the freetds dialect idea because there's a lot more issues specific to freetds than just this one. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: Create a stored procedure using SQLAlchemy
Hello,
On Tue, May 19, 2009 at 2:23 PM, Daniel daniel.watr...@gmail.com wrote:
I have a stored procedure for SQL Server and I would like to be able
to execute the code to create the stored procedure using SA. Here's
the basic idea.
engine = sqlalchemy.create_engine('mssql://connectionString')
engine.execute(myStoredProcedure)
Where:
myStoredProcedure =
PRINT 'generate mySP stored procedure'
IF object_id('mySP') IS NOT NULL
BEGIN
DROP PROCEDURE mySP
END
GO
CREATE PROCEDURE mySP
AS
DECLARE @aVar VARCHAR(48)
BEGIN
SELECT TOP 1
@aVar = aVar
FROM [dbo].[someTable] (UPDLOCK)
WHERE
priority 0
ORDER BY
priority DESC
SELECT
@aVar AS aVar
END
GO
You will need to split this into two separate execute calls. Also this
stored procedure should be simplified. There's no reason for the local
variable. It can be simplified as:
CREATE PROCEDURE mySP
AS
SELECT TOP 1 aVar
FROM [dbo].[someTable] (UPDLOCK)
WHERE
priority 0
ORDER BY priority DESC
GO
--
Michael Trier
http://michaeltrier.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: Basic Search Engine
It may not be what you want but have you explored our full text search
support through the use of the match operator?
On May 10, 2009, at 6:13 AM, Nicholas Dudfield ndudfi...@gmail.com
wrote:
Greetings,
I have been using SQLA for a few months.
For admin CRUD index pages I have been using a naive search_keywords
function as
seen at end of message.
Instead of using a primitive shlex.split, which incidentally is not
unicode
friendly, and one crude search_type (AND|OR) I'd like to use something
that will
lex/parse a search string and build the queries.
eg.
((x or z or y) and q) or not h m
I imagine this would be a fairly common requirement however I can't
seem
to find
any implementation anywhere.
I used google code search with the query pyparsing sqlalchemy
lang:python
however found no useful results.
Before I set off attempting to learn about lexing/[py]parsing I was
wondering if
any one has some code like this laying about underneath their bed.
Cheers.
===
===
===
===
=
=
===
===
===
===
ensure_list = lambda l: l if isinstance(l, list) else [l]
def shlex_split(line):
shlex.split does not handle unicode properly so must be
codecd
if isinstance(line, unicode):
line = line.encode('utf-8')
return [ w.strip().decode('utf-8') for w in shlex.split(line) ]
def like_escape(s):
return ( s.replace('\\', '')
.replace('%', '\\%')
.replace('_', '\\_') )
def search_keywords(q, model, key_words='', fields=[],
search_type='and',
default_fields=[], **kw):
if not key_words:
return q
# Escape the search string
# TODO: this should be done by formencode validators
if isinstance(key_words, basestring):
key_words = shlex_split(key_words)
# Space delimited keyword search
key_words = ['%'+ like_escape(w) +'%' for w in key_words]
# Make sure fields is a list and if none specified use default
fields = ensure_list(fields) if fields else default_fields # TODO
# WHERE ($X OR $Y) AND|OR ($Z OR $Q) ...
search_type = and_ if search_type == 'and' else or_
if key_words:
q = q.filter (
search_type ( * (
or_( *( getattr(model, field).like(key_word,
escape=r'\\')
for field in fields ) )
for key_word in key_words
)
)
)
return q
===
===
===
===
=
=
===
===
===
===
--~--~-~--~~~---~--~~
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: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Apr 29, 2009, at 10:08 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Some additional info, and a possible fix:
I can reproduce this problem running the SQLAlchemy dialect unit
tests. Using a trunk (r5930) checkout, FreeTDS 0.82 with tds protocol
version 8.0, pyodbc 2.1.4, Python 2.5 and SQL Server 2005, I see three
test failures in dialect.mssql:
test_binary fails with:
DataError: (DataError) ('22018', '[22018] [FreeTDS][SQL Server]
Implicit conversion from data type varchar to varbinary is not
allowed. Use the CONVERT function to run this query. (257)
(SQLPrepare)') 'INSERT INTO binary_table (primary_id, data,
data_image, data_slice, misc, pickled, mypickle) VALUES
(?, ?, ?, ?, ?, ?, ?)' [1, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f80, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f60, read-only buffer for 0xb75e9c20, size -1,
offset 0 at 0xb75d00a0, 'binary_data_one.dat', read-only buffer for
0xb75c67a0, size -1, offset 0 at 0xb75d0180, read-only buffer for
0xb75d9d68, size -1, offset 0 at 0xb75d0100]
I'm going to ignore this for now, since it seems to be unrelated to my
problem.
This failure has started about a month ago and I haven't had time to
investigate.
However, test_fetchid_trigger and test_slice_mssql both fail with the
Invalid cursor state exception:
File /home/taw8/src/sqlalchemy-trunk/lib/sqlalchemy/engine/
base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO foo
(bar, range) VALUES (?, ?); select scope_identity()' [1, 1]
Here's a possible fix. The following patch to mssql.py corrects my
problems, as well as the test_fetchid_trigger and test_slice_mssql
failures:
Interesting fix. I'll apply and test against windows and pyodbc.
Index: lib/sqlalchemy/databases/mssql.py
===
--- lib/sqlalchemy/databases/mssql.py (revision 5930)
+++ lib/sqlalchemy/databases/mssql.py (working copy)
@@ -991,7 +991,7 @@
# We may have to skip over a number of result sets with
no data (due to triggers, etc.)
while True:
try:
-row = self.cursor.fetchone()
+row = self.cursor.fetchall()[0]
break
except pyodbc.Error, e:
self.cursor.nextset()
I.e., calling fetchall() instead of fetchone() seems to clean up the
cursor state.
Two caveats: (1) there are many other (non dialect) test failures with
and without my patch, although the patch does reduce the number. So
maybe there is something amok with my configuration. (2) I'm only
tried this on Debian--I have no idea what would happen on Windows.
--~--~-~--~~~---~--~~
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: pyodbc issues
On Apr 24, 2009, at 7:50 PM, Michael Mileusnich
justmike2...@gmail.com wrote:
Wow..your example worked for me. Could the kwargs the issue?
No. Likely you have some sort of conflict on the dbapi side.
If it's possible for you to send me your actual code
(mtr...@gmail.com) I'd be happy to try and figure out the issue.
On Fri, Apr 24, 2009 at 3:14 PM, mtrier mtr...@gmail.com wrote:
On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote:
I have formated my PC with Vista 32. I also Installed SQL
Server 2008
Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would
be willing
to have somebody overlook my python code. With echo on
everything looks
like it should be INSERTING.
I've written this script based on the information you have supplied.
It works fine for me:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('mssql://sprint:spr...@localhost/sprint',
echo=True)
metadata = MetaData(engine)
Session = scoped_session(sessionmaker(bind=engine, autoflush=False,
autocommit=True))
action_table = Table(
'ACTIONS', metadata,
Column('ACTIONID', String(48), primary_key=True),
Column('TITLE', String(128)),
Column('CMDLINE', String(512)),
Column('STDIN', Text),
Column('STARTINDIR', String(512)),
Column('PRIO', Integer),
)
class action(object):
def __init__(self, ACTIONID, CMDLINE):
self.ACTIONID = ACTIONID
self.CMDLINE = CMDLINE
def __repr__(self):
return action('%s', '%s') % (self.ACTIONID, self.CMDLINE)
mapper(action, action_table)
metadata.create_all()
session = Session()
new_action = action(ACTIONID = '500', CMDLINE = 'sol')
session.add(new_action)
session.flush()
session.expunge_all()
act = session.query(action).filter_by(ACTIONID='500').one()
assert new_action.ACTIONID == act.ACTIONID
--
Would you please try it and let me know what results you get. The
following is my output.
S:\sqlalchemy.git\libpython msssqlprob.py
2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT user_name() as user_name;
2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x...6110
[]
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
[u'dbo']
2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB
LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].
[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU
MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
['ACTIONS', u'dbo']
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
CREATE TABLE [ACTIONS] (
[ACTIONID] VARCHAR(48) NOT NULL,
[TITLE] VARCHAR(128) NULL,
[CMDLINE] VARCHAR(512) NULL,
[STDIN] TEXT NULL,
[STARTINDIR] VARCHAR(512) NULL,
[PRIO] INTEGER NULL,
PRIMARY KEY ([ACTIONID])
)
2009-04-24 16:10:30,630 INFO sqlalchemy.engine.base.Engine.0x...6110
()
2009-04-24 16:10:30,661 INFO sqlalchemy.engine.base.Engine.0x...6110
COMMIT
2009-04-24 16:10:30,693 INFO sqlalchemy.engine.base.Engine.0x...6110
BEGIN
2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110
INSERT INTO [ACTIONS] ([ACTIONID], [TITLE], [CMDLIN
E], [STDIN], [STARTINDIR], [PRIO]) VALUES (?, ?, ?, ?, ?, ?)
2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110
['500', None, 'sol', None, None, None]
2009-04-24 16:10:30,723 INFO sqlalchemy.engine.base.Engine.0x...6110
COMMIT
2009-04-24 16:10:30,723 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT TOP 2 [ACTIONS].[ACTIONID] AS [ACTIONS_ACTIO
NID], [ACTIONS].[TITLE] AS [ACTIONS_TITLE], [ACTIONS].[CMDLINE] AS
[ACTIONS_CMDLINE], [ACTIONS].[STDIN] AS [ACTIONS_STDI
N], [ACTIONS].[STARTINDIR] AS [ACTIONS_STARTINDIR], [ACTIONS].[PRIO]
AS [ACTIONS_PRIO]
FROM [ACTIONS]
WHERE [ACTIONS].[ACTIONID] = ?
2009-04-24 16:10:30,740 INFO sqlalchemy.engine.base.Engine.0x...6110
['500']
Michael Trier
http://michaeltrier.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: pyodbc issues
On Apr 24, 2009, at 5:36 PM, Michael Mileusnich
justmike2...@gmail.com wrote:
Mine code looks the same except for:
def __init__(self, **kwargs):
for my ACTIONS.
Should I try emailing pyodbc about this issue?
So does the script work on your system? What's the echo output from it?
On Fri, Apr 24, 2009 at 3:14 PM, mtrier mtr...@gmail.com wrote:
On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote:
I have formated my PC with Vista 32. I also Installed SQL
Server 2008
Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would
be willing
to have somebody overlook my python code. With echo on
everything looks
like it should be INSERTING.
I've written this script based on the information you have supplied.
It works fine for me:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('mssql://sprint:spr...@localhost/sprint',
echo=True)
metadata = MetaData(engine)
Session = scoped_session(sessionmaker(bind=engine, autoflush=False,
autocommit=True))
action_table = Table(
'ACTIONS', metadata,
Column('ACTIONID', String(48), primary_key=True),
Column('TITLE', String(128)),
Column('CMDLINE', String(512)),
Column('STDIN', Text),
Column('STARTINDIR', String(512)),
Column('PRIO', Integer),
)
class action(object):
def __init__(self, ACTIONID, CMDLINE):
self.ACTIONID = ACTIONID
self.CMDLINE = CMDLINE
def __repr__(self):
return action('%s', '%s') % (self.ACTIONID, self.CMDLINE)
mapper(action, action_table)
metadata.create_all()
session = Session()
new_action = action(ACTIONID = '500', CMDLINE = 'sol')
session.add(new_action)
session.flush()
session.expunge_all()
act = session.query(action).filter_by(ACTIONID='500').one()
assert new_action.ACTIONID == act.ACTIONID
--
Would you please try it and let me know what results you get. The
following is my output.
S:\sqlalchemy.git\libpython msssqlprob.py
2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT user_name() as user_name;
2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x...6110
[]
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
[u'dbo']
2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB
LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].
[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU
MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
['ACTIONS', u'dbo']
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
CREATE TABLE [ACTIONS] (
[ACTIONID] VARCHAR(48) NOT NULL,
[TITLE] VARCHAR(128) NULL,
[CMDLINE] VARCHAR(512) NULL,
[STDIN] TEXT NULL,
[STARTINDIR] VARCHAR(512) NULL,
[PRIO] INTEGER NULL,
PRIMARY KEY ([ACTIONID])
)
2009-04-24 16:10:30,630 INFO sqlalchemy.engine.base.Engine.0x...6110
()
2009-04-24 16:10:30,661 INFO sqlalchemy.engine.base.Engine.0x...6110
COMMIT
2009-04-24 16:10:30,693 INFO sqlalchemy.engine.base.Engine.0x...6110
BEGIN
2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110
INSERT INTO [ACTIONS] ([ACTIONID], [TITLE], [CMDLIN
E], [STDIN], [STARTINDIR], [PRIO]) VALUES (?, ?, ?, ?, ?, ?)
2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110
['500', None, 'sol', None, None, None]
2009-04-24 16:10:30,723 INFO sqlalchemy.engine.base.Engine.0x...6110
COMMIT
2009-04-24 16:10:30,723 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT TOP 2 [ACTIONS].[ACTIONID] AS [ACTIONS_ACTIO
NID], [ACTIONS].[TITLE] AS [ACTIONS_TITLE], [ACTIONS].[CMDLINE] AS
[ACTIONS_CMDLINE], [ACTIONS].[STDIN] AS [ACTIONS_STDI
N], [ACTIONS].[STARTINDIR] AS [ACTIONS_STARTINDIR], [ACTIONS].[PRIO]
AS [ACTIONS_PRIO]
FROM [ACTIONS]
WHERE [ACTIONS].[ACTIONID] = ?
2009-04-24 16:10:30,740 INFO sqlalchemy.engine.base.Engine.0x...6110
['500']
Michael Trier
http://michaeltrier.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: use of a column label in HAVING clause
On Thu, Apr 23, 2009 at 6:01 AM, Grimsqueaker grimsqueake...@gmail.comwrote:
I dont understand what I'm doing wrong in the following situation.
If I put this:
case(whens={exclude_table.c.minutes_limit != None:
exclude_table.c.minutes_limit}, else_=5000).label
(name='minutes_limit')
in the select clause of my query, I can't say:
having=(func.sum(cdr_table.c.duration) / 60) = 'minutes_limit',
later on as I get the following error:
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01722: invalid
number
I got around the problem by doing this:
stupid = case(whens={exclude_table.c.minutes_limit != None:
exclude_table.c.minutes_limit}, else_=5000).label
(name='minutes_limit')
HAVING in SQL requires the full construct on all databases that I have
experience with. So in other words you have to write HAVING COUNT(*) 1,
and can't refer to an alias as in HAVING ticket_count 1.
--
Michael Trier
http://blog.michaeltrier.com/
http://thisweekindjango.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: django middleware or signals for sqlalchemy Session
Hi, On Thu, Apr 23, 2009 at 4:05 PM, davidlmontgomery davidlmontgom...@gmail.com wrote: I would like to know if there is a consensus on the best way to set up and remove sqlalchemy Sessions in django. I figure I'm either going to use middleware, something like this thread: http://groups.google.com/group/django-users/browse_thread/thread/e6749f7eec1cc46c/ef9d9e27943af830 or I'm going to use signals, something like this post: http://translate.google.com/translate?hl=ensl=jau=http://d.hatena.ne.jp/perezvon/20071120/1195577831ei=GeOISeKPA4K2sQOw0c2YBgsa=Xoi=translateresnum=2ct=resultprev=/search%3Fq%3Ddjango%2Bsqlalchemy%2Bwsgi%2Bsessionmaker%26hl%3Den%26rlz%3D1B3GGGL_enUS293US293 Any clear advantages or disadvantages for the two approaches? Personally I find the Middleware approach cleaner and allows you to handle exceptions with rollbacks as indicated in the django-users thread. There was a project Tranquil (http://code.google.com/p/tranquil/) that expanded on this idea to inject more stuff into the request automatically. It's pretty much dead at this point from what I understand. Additionally I'll point out that I have a project called Django-SQLAlchemy (http://gitorious.org/projects/django-sqlalchemy) that has the aim of automatically making SQLAlchemy accessible through Django applications. Good luck. -- Michael Trier http://michaeltrier.com/ http://thisweekindjango.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: pyodbc issues
The trace looks fine to me. I'll try to test it myself tonight.
Sent from mobile
On Apr 20, 2009, at 10:45 PM, Michael Mileusnich
justmike2...@gmail.com wrote:
Any update on this?
On Fri, Apr 17, 2009 at 6:07 PM, Michael Mileusnich justmike2...@gmail.com
wrote:
Actually I made a mistake in running some of these py files
manually. The tables exist now and this is what I receive:
2009-04-17 19:04:59,780 INFO sqlalchemy.engine.base.Engine.0x...1230
()
2009-04-17 19:04:59,796 INFO sqlalchemy.engine.base.Engine.0x...1230
COMMIT
C:\Dev\pyschedpython createservers.py
D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy
\databases\ms
sql.py:977: DeprecationWarning: object.__new__() takes no parameters
return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs)
2009-04-17 19:05:07,203 INFO sqlalchemy.engine.base.Engine.0x...0270
BEGIN
2009-04-17 19:05:07,217 INFO sqlalchemy.engine.base.Engine.0x...0270
INSERT INTO
[SERVERS] ([SERVER], [IP], [PORT], [OS], [JSERVER], [STARTED],
[STDIN], [LOGIN]
, [CWD], [ASSIGNEDONLY]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2009-04-17 19:05:07,217 INFO sqlalchemy.engine.base.Engine.0x...0270
['agent1',
'192.168.0.100', 2000, 0, None, 1, 1, 1, 1, 0]
2009-04-17 19:05:07,217 INFO sqlalchemy.engine.base.Engine.0x...0270
COMMIT
On Fri, Apr 17, 2009 at 5:58 PM, Michael Trier mtr...@gmail.com
wrote:
The SERVERS table doesn't exist according to the trace. Did you
create your tables?
Sent from mobile
On Apr 17, 2009, at 7:31 PM, Michael Mileusnich justmike2...@gmail.com
wrote:
C:\Dev\pyschedpython createservers.py
D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy
\databases\ms
sql.py:977: DeprecationWarning: object.__new__() takes no parameters
return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs)
2009-04-17 18:29:10,421 INFO sqlalchemy.engine.base.Engine.0x...
0270 BEGIN
2009-04-17 18:29:10,437 INFO sqlalchemy.engine.base.Engine.0x...
0270 INSERT INTO
[SERVERS] ([SERVER], [IP], [PORT], [OS], [JSERVER], [STARTED],
[STDIN], [LOGIN]
, [CWD], [ASSIGNEDONLY]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2009-04-17 18:29:10,437 INFO sqlalchemy.engine.base.Engine.0x...
0270 ['agent1',
'192.168.0.100', 2000, 0, None, 1, 1, 1, 1, 0]
2009-04-17 18:29:10,500 INFO sqlalchemy.engine.base.Engine.0x...
0270 ROLLBACK
Traceback (most recent call last):
File createservers.py, line 9, in module
session.flush()
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
session.py, line 1351, in flush
self._flush(objects)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
session.py, line 1422, in _flush
flush_context.execute()
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
unitofwork.py, line 244, in execute
UOWExecutor().execute(self, tasks)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
unitofwork.py, line 707, in execute
self.execute_save_steps(trans, task)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
unitofwork.py, line 722, in execute_save_steps
self.save_objects(trans, task)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
unitofwork.py, line 713, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
mapper.py, line 1347, in _save_obj
c = connection.execute(statement.values(value_params), params)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 874, in _execute_clauseelement
return self.__execute_context(context)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0
], context=context)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidat
ed=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42S02',
[42S02] [Microsof
t][ODBC SQL Server Driver][SQL Server]Invalid object name
'SERVERS'. (208) (SQLE
xecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Statement(s)
could not be prepared. (8180
[sqlalchemy] Re: pyodbc issues
The SERVERS table doesn't exist according to the trace. Did you create
your tables?
Sent from mobile
On Apr 17, 2009, at 7:31 PM, Michael Mileusnich
justmike2...@gmail.com wrote:
C:\Dev\pyschedpython createservers.py
D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy
\databases\ms
sql.py:977: DeprecationWarning: object.__new__() takes no parameters
return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs)
2009-04-17 18:29:10,421 INFO sqlalchemy.engine.base.Engine.0x...0270
BEGIN
2009-04-17 18:29:10,437 INFO sqlalchemy.engine.base.Engine.0x...0270
INSERT INTO
[SERVERS] ([SERVER], [IP], [PORT], [OS], [JSERVER], [STARTED],
[STDIN], [LOGIN]
, [CWD], [ASSIGNEDONLY]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2009-04-17 18:29:10,437 INFO sqlalchemy.engine.base.Engine.0x...0270
['agent1',
'192.168.0.100', 2000, 0, None, 1, 1, 1, 1, 0]
2009-04-17 18:29:10,500 INFO sqlalchemy.engine.base.Engine.0x...0270
ROLLBACK
Traceback (most recent call last):
File createservers.py, line 9, in module
session.flush()
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
session.py, line 1351, in flush
self._flush(objects)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
session.py, line 1422, in _flush
flush_context.execute()
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
unitofwork.py, line 244, in execute
UOWExecutor().execute(self, tasks)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
unitofwork.py, line 707, in execute
self.execute_save_steps(trans, task)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
unitofwork.py, line 722, in execute_save_steps
self.save_objects(trans, task)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
unitofwork.py, line 713, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\orm\
mapper.py, line 1347, in _save_obj
c = connection.execute(statement.values(value_params), params)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 874, in _execute_clauseelement
return self.__execute_context(context)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0
], context=context)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File d:\python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg
\sqlalchemy\engi
ne\base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidat
ed=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42S02',
[42S02] [Microsof
t][ODBC SQL Server Driver][SQL Server]Invalid object name 'SERVERS'.
(208) (SQLE
xecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Statement(s)
could not be prepared. (8180)) u'INSERT INTO [SERVERS] ([SERVER],
[IP], [PORT]
, [OS], [JSERVER], [STARTED], [STDIN], [LOGIN], [CWD],
[ASSIGNEDONLY]) VALUES (?
, ?, ?, ?, ?, ?, ?, ?, ?, ?)' ['agent1', '192.168.0.100', 2000, 0,
None, 1, 1, 1
, 1, 0]
C:\Dev\pysched
On Fri, Apr 17, 2009 at 5:05 PM, Michael Trier mtr...@gmail.com
wrote:
On Apr 17, 2009, at 5:46 PM, Michael Mileusnich justmike2...@gmail.com
wrote:
First, thanks for being patient and assisting me. I am very
thankful.
MS SQL 2005 is the db I am running.
part of my db.py script:
try:
connection = config.get(db, connection)
except:
print No Database Specified
sys.exit(1)
engine = create_engine(connection)
metadata = MetaData(engine)
Session = scoped_session(sessionmaker(bind=engine, autoflush=False,
autocommit=True))
action_table = Table(
'ACTIONS', metadata,
Column('ACTIONID', String(48), primary_key=True),
Column('TITLE', String(128)),
Column('CMDLINE', String(512)),
Column('STDIN', Text),
Column('STARTINDIR', String(512)),
Column('PRIO', Integer),
Column('USERID', Integer, ForeignKey('USERS.USERID')))
...
more tables here
...
etc
mapper(action, action_table)
here is my create:
new_action = action(ACTIONID = '500', CMDLINE = 'sol')
session.add(new_action)
session.flush()
also how do I turn on echo?
On Fri, Apr 17, 2009 at 3:24
[sqlalchemy] Re: SA and python 26
On Sun, Apr 5, 2009 at 9:46 PM, Michael Bayer mike...@zzzcomputing.comwrote: and i would also suggest investigating if the DBAPI you're using is somehow different than the one you've used with 2.5, or unsupported on py2.6. On Apr 5, 2009, at 7:59 PM, Michael Mileusnich wrote: The code used to work in 2.5. A simple add and flush was all I needed to do. I tried to use a commit but that did not work either. Am I missing something here? On Sun, Apr 5, 2009 at 4:58 PM, Michael Bayer mike...@zzzcomputing.comwrote: this is not an error, it is only a warning.There should be no difference in behavior between py2.5 and 2.6. The code you have below may not necessarily create any data if you didn't commit your transaction. On Apr 5, 2009, at 6:05 PM, Michael Mileusnich wrote: I have been working with Python 2.5 and SQLAlchemy. I recently upgraded to Python 2.6 on my Windows machine and I receive the following message: D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy \databases\ms sql.py:977: DeprecationWarning: object.__new__() takes no parameters return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs) I have a script that creates my tables and inserts data. The tables get created however they are empty. Here is some example code that does not work: from sqlalchemy.orm import * from db import * session = getsession() new_serv = server(SERVER = test) session.add(new_serv) session.flush() I'm using the latest pyodbc with 2.6.1 version of python and all tests run fine. If you provide a test script I can dig into it. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: 0.5.2 seems to always roll back with mssql
yeah I know when it was done. we need Michael to fill in on that detail. On Mar 24, 2009, at 8:09 PM, davidlmontgomery wrote: The relevant change is the addition of the MSSQLDialect.do_begin method: def do_begin(self, connection): cursor = connection.cursor() cursor.execute(SET IMPLICIT_TRANSACTIONS OFF) cursor.execute(BEGIN TRANSACTION) This was first introduced three months ago to correct the savepoint tests http://bitbucket.org/mirror/sqlalchemy/changeset/985925326dc8/ and revised a couple months ago to use the cursor rather than the connection: http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/databases/mssql.py?rev=5641 With this method converted to a pass, 0.5.2 works properly for me on the three machines where before it always rolled back. Yeah that code was added to fix savepoint support. One of the problems with MSSQL with Implicit Transactions is that it only gets turned on once a DML statement gets executed. Unfortunately issuing a savepoint doesn't turn on the implicit transaction. Therefore we were in a situation where there was no begin transaction, but there was a savepoint issued. Originally we tried to implement this by keeping implicit transactions but checking at the time the savepoint was issued to begin the transaction if it wasn't present. That had issues and honestly I can't recall what it was. The way it is structured now seems to work fine in all the tests, so it's odd that you're having a problem. My only guess is that it's a TDS thing (I believe you said you were using that). The odd thing is that the list of items you're seeing across the wire is exactly what I'm getting as well and what should be expected. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: Auto-loading With Relationships
Hi, On Tue, Feb 17, 2009 at 2:50 PM, Gp gpm...@gmail.com wrote: We have sets of databases with several hundred tables per database. Most of these tables are linked using various foreign key relationships. Is there any sort of automatic SQL Alchemy generation? Either SQL Soup like loading or a script that gets run once that generates code for use. - There's this: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: Specifying descending order_by on a backref
On Tue, Feb 17, 2009 at 5:03 PM, Ken kkin...@gmail.com wrote: I'm using the declarative extension. When I specify a backref on a relation, I see the order_by argument and I'm pointing it to the column object. That works, but I need to sort the backref descendingly, not ascendingly. I don't see an option for that in the Relation()/backref() arguments. What am I missing? backref supports the same arguments as relation(), which include an order_by argument. http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html?highlight=backref#sqlalchemy.orm.relation -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: Specifying descending order_by on a backref
Hi, On Tue, Feb 17, 2009 at 5:22 PM, Ken kkin...@gmail.com wrote: On Feb 17, 3:13 pm, Michael Trier mtr...@gmail.com wrote: backref supports the same arguments as relation(), which include an order_by argument. http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html?highligh... Michael, I'm using the order_by argument. The problem is that it's sorting my values ascendingly instead of descendingly. Sorry, I misunderstood. You should be able to do a order_by=field.desc(). -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: Reg: Question on SqlAlchemy Speed
Hi,
On Fri, Feb 13, 2009 at 7:26 AM, MikeCo mconl...@gmail.com wrote:
The ORM will probably have a little bit of function call overhead compared
to the SQL Expression Language, and that may no longer be the case.
Regardless with the ORM you're going to want to use the update method as
stated by MikeCo along with the IN syntax so you get batch updating. With a
1000 items you will likely run into SQL command length issues so you'll
probably need to batch them as az said.
You should end up with something like:
bar = [1, 2, 3]
session.query(Post).filter(Post.id.in_(bar)).update({'title':'updated'},
synchronize_session=False)
3
I'd be interested to see what your analysis uncovers.
--
Michael Trier
http://blog.michaeltrier.com/
http://thisweekindjango.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: resultset print keys values
Hi, On Tue, Feb 10, 2009 at 3:18 PM, Lukasz Szybalski szybal...@gmail.comwrote: On Tue, Feb 10, 2009 at 1:52 PM, Michael Bayer mike...@zzzcomputing.com wrote: dir(instance) is preferable to __dict__.keys() - the latter will not give you deferred attributes, unloaded collections, or the expired version of each of those. dir() respects descriptors basically. but then dir() includes stuff like: '__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__', '__hash__', '__init__', '__module__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__str__', '__weakref__', '_sa_class_manager', '_sa_instance_state'] Which need to be filtered. Then for the remaining items need to loop through to get the value?! I figured there was a uniform function that would return dictionary of key/value pairs that is available on all the possible return objects. __dict__ is good enough for visual inspection for now. Wouldn't this do what you need: http://www.sqlalchemy.org/trac/wiki/FAQ#Whatsthebestwaytofigureoutwhichattributesarecolumnsgivenaclass -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: BUG: autoload for DateTime in SQL Server 2000 fails
On Mon, Feb 9, 2009 at 7:56 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 9, 2009, at 5:41 PM, Eric R. Palakovich Carr wrote: Is this a real bug or am I doing something wrong? there is ! you're reporting a bug against an 0.5 version prior to the current 0.5 release (I know this since you're referencing a line of code only present in prior versions of 0.5). this has been fixed. Yeah, I just double checked with your test case and it works in the recent version. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: print query with params
Is there anyway to print the query with the params in place? There's this Recipe (http://www.sqlalchemy.org/trac/wiki/DebugInlineParams ). -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: [ANN] Rum 0.2 has been released
Hi, On Mon, Jan 26, 2009 at 12:54 PM, Alberto Valverde albe...@toscat.netwrote: Hi, I'm proud to announce that we've just made the first public release of Rum and its SQLAlchemy plugin. Rum is an extensible WSGI web application to provide a RESTful interface for your app's model objects. You can think of it as an alternative to Django's admin for the non-django world. It is designed to be pluggable and support several data backends and views/form-generators but currently there are only two implemented: a data backend to handle SQLAlchemy mapped classes (RumAlchemy) and a form generator using tw.forms (tw.rum). Very nice work! I'm very excited about what you're doing. Thank you. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: MS SQL wrapper for pymssql DBAPI broken in 0.5.0
On Sat, Jan 10, 2009 at 9:54 PM, Michael Trier mtr...@gmail.com wrote: 1. Override do_begin so that it creates a cursor and then executes on the cursor: def do_begin(self, connection): cursor = connection.cursor() cursor.execute(SET IMPLICIT_TRANSACTIONS OFF) cursor.execute(BEGIN TRANSACTION) this would be appropriate since connection doesn't have an execute() method in DBAPI. Yeah I didn't even realize that pyodbc supports execute at the connection level especially since it is undocumented. I guess in my mind I thought I was dealing with the cursor at that point. So I should change this anyway even for pyodbc so we keep in line with the API. I just tested the change and it works fine. 2. Revert to the old behavior by doing: def do_begin(self, connection): pass This would only affect pymssql. Option 2 results in the greatest number of passed tests for straight orm usage, but causes the ever persistent hanging when working with transactional tests. This was the reason for the introduction of those statements (plus visitpoints). I want Mike Bayer to confirm that's the direction he wants to go in, before I proceed. This has been corrected in http://www.sqlalchemy.org/trac/changeset/5641. I actually did both things, since using the cursor is the right thing to do. Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: MS SQL wrapper for pymssql DBAPI broken in 0.5.0
1. Override do_begin so that it creates a cursor and then executes on the cursor: def do_begin(self, connection): cursor = connection.cursor() cursor.execute(SET IMPLICIT_TRANSACTIONS OFF) cursor.execute(BEGIN TRANSACTION) this would be appropriate since connection doesn't have an execute() method in DBAPI. Yeah I didn't even realize that pyodbc supports execute at the connection level especially since it is undocumented. I guess in my mind I thought I was dealing with the cursor at that point. So I should change this anyway even for pyodbc so we keep in line with the API. I just tested the change and it works fine. 2. Revert to the old behavior by doing: def do_begin(self, connection): pass This would only affect pymssql. Option 2 results in the greatest number of passed tests for straight orm usage, but causes the ever persistent hanging when working with transactional tests. This was the reason for the introduction of those statements (plus visitpoints). I want Mike Bayer to confirm that's the direction he wants to go in, before I proceed. OK so, this is again where everything works butSAVEPOINT breaks ? Yes SAVEPOINT would break. Everything works in the sense that it worked before. pymssql has tons of failures and one of the reasons why it's difficult to understand the impact of changes. Overall I think this is the approach we should take right now. This restores it back to the way it was before. With the approach in #1 things hang everywhere. I'll look into it, but for now the best approach in my opinion is to do #2 for now. As mentioned previously, pymssql support is no where near the level of support as pyodbc. In addition the maintainer has not updated this library for several years. Finally, Microsoft warns against it's use going forward, When writing new applications, avoid using DB-Library. When modifying existing applications, you are strongly encouraged to remove dependencies on DB-Library. Instead of DB-Library, you can use Microsoft ActiveX(R) Data Objects (ADO), OLE DB, or ODBC to access data in SQL Server. whats the story with adodbapi ? that library is also being actively maintained, why is pyodbc better ? It is now that Vern is behind it and it was included in pywin32. I've been playing with it the past several days just to see what issues are there. Overall it tests pretty well but there are certainly errors. My understanding is that it's a lot slower than pyodbc. Other than that I just don't know. I'd like to get it to full test passing as well. I think it's certainly possible. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: MS SQL wrapper for pymssql DBAPI broken in 0.5.0
Hi, On Fri, Jan 9, 2009 at 12:45 PM, t...@twopeasinabucket.com t...@twopeasinabucket.com wrote: On Jan 7, 10:41 pm, Michael Trier mtr...@gmail.com wrote: Hi, On Wed, Jan 7, 2009 at 10:43 PM, Jaimy Azle jaimy.a...@gmail.com wrote: Hi, it seems mssql wrapper for pymmsql DBAPI driver in sqlalchemy 0.5.0 (release) is broken. I haven't check it out with adodbapi, but pyodbc confirmed works. Work has been hectic this week. Anyway, I looked into this and pymssql doesn't support execute on the connection. It supports a straight up query at the module level or it supports execute on the cursor. So our options are: 1. Override do_begin so that it creates a cursor and then executes on the cursor: def do_begin(self, connection): cursor = connection.cursor() cursor.execute(SET IMPLICIT_TRANSACTIONS OFF) cursor.execute(BEGIN TRANSACTION) 2. Revert to the old behavior by doing: def do_begin(self, connection): pass This would only affect pymssql. Option 2 results in the greatest number of passed tests for straight orm usage, but causes the ever persistent hanging when working with transactional tests. This was the reason for the introduction of those statements (plus visitpoints). I want Mike Bayer to confirm that's the direction he wants to go in, before I proceed. I apologize for this oversight. I had a friend test on pymssql but because of the number of overall failures is much higher I think he missed the fact that this wasn't working right. As mentioned previously, pymssql support is no where near the level of support as pyodbc. In addition the maintainer has not updated this library for several years. Finally, Microsoft warns against it's use going forward, When writing new applications, avoid using DB-Library. When modifying existing applications, you are strongly encouraged to remove dependencies on DB-Library. Instead of DB-Library, you can use Microsoft ActiveX(R) Data Objects (ADO), OLE DB, or ODBC to access data in SQL Server. Michael Trier http://blog.michaeltrier.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: Error Connecting to MSSQL using pyodbc and SqlAlchemy 0.5.0
Hi, On Sat, Jan 10, 2009 at 12:10 AM, Lukasz Szybalski szybal...@gmail.comwrote: On Fri, Jan 9, 2009 at 4:46 PM, Rick Morrison rickmorri...@gmail.com wrote: The MSSQL connection string changed for the 0.5 final release. In particular, the dsn keyword is removed, and the pyodbc connection string now expects the DSN to be named where the host was previously placed, so the new connection URL would be: mssql://username:passw...@mydbodbc We actually left support for that in there just so we wouldn't break everyone's connection strings, but you should use the new syntax. ps. I miss the one page documentation(easier to search) , is that available or can be done with sphinx? Have you tried the sphinx search? I think it's the best thing ever. Very handy and fast. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: MS SQL wrapper for pymssql DBAPI broken in 0.5.0
Hi, On Wed, Jan 7, 2009 at 10:43 PM, Jaimy Azle jaimy.a...@gmail.com wrote: Hi, it seems mssql wrapper for pymmsql DBAPI driver in sqlalchemy 0.5.0 (release) is broken. I haven't check it out with adodbapi, but pyodbc confirmed works. Thanks for the test. I will look at this tomorrow. Michael --~--~-~--~~~---~--~~ 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: question re query vs select
Hi, On Sun, Jan 4, 2009 at 7:18 AM, robert rottermann rob...@redcor.ch wrote: hi there I have a class tblMembershiptypeTable which I defind using declarative notation whereas t = tblMembershiptypeTable.__table__ mt = session.execute(select(t, t.c.name == mtype)).fetchone() returns a tuple of values. how can I use select to return instances? You don't really unless you roll it yourself. But why would you do that. That's what the ORM is for. When you're using select() you're using the SQL Expression Language which is not mapped in any way to Objects. With the ORM it knows the mapping through the Mapper and handles that. I guess a larger question is what are you not able to achieve with the ORM that you can achieve with the SQL Expression Language? -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: mssql wrapper for numeric Datatype.
Hi, On Fri, Jan 2, 2009 at 11:56 PM, Jaimy Azle jaimy.a...@gmail.com wrote: On Saturday, January 3, 2009, 7:07:00 AM, Michael Trier wrote: Would it be possible for you to do up a test case demonstrating the problem? If so, we could get to it very quickly. sure, here it is: Corrected in r5602. Please confirm if you get a moment. Thanks. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.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: Empty ResultProxy, but SQL statement yields results
Record_count is never set. Michael On Dec 23, 2008, at 3:27 PM, Philip philwr...@gmail.com wrote: I am having a problem that I am not able to figure out. Maybe someone else can see what I am doing wrong. I am building a query using the SQL expression language. Then printing the SQL statement and counting the resulting records. When I run the generated SQL statement in the command-line MySQL client it yields 100's of results. The problem is that the count that the script prints out is 0. I have included the code below: sel = select([table]) sel = sel.select_from(table.join(listing, table.c.col_sysid == listing.c.sysid)) sel = sel.where(table.c.last_tr_date != listing.c.modified_date) print SQL: + sel result = conn.execute(sel) count = 0 for row in result: count = count + 1 print Count: + str(record_count) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
