[sqlalchemy] Composite primary key with nullable columns
Hi, I have a situation where I'm trying to us a composite primary key, where one of the columns can be null. However, when I try to update an object I get the following error: sqlalchemy.orm.exc.FlushError: Can't update table test using NULL for primary key value on column test.id2 Sample code here: http://dpaste.com/3Q8T09T Is this something SQLAlchemy can do? From some quick tests, it seems SQLite can do it, not sure about other databases. Paul -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] 10 Reasons to love SQLAlchemy
Hi guys, I decided to express my love of SQLAlchemy through a blog post: http://pajhome.org.uk/blog/10_reasons_to_love_sqlalchemy.html Enjoy, Paul -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Declarative: defining relationship and column in one line
Hi,
Sorry if this is a FAQ, but is it possible to define a relationship and its
column all at once. e.g. instead of:
type_id = db.Column(db.Integer, db.ForeignKey('linktype.id'))
type = db.relationship('LinkType')
Something like:
type = db.relationship('LinkType', colname='type_id')
In fact, it'd be good for the colname to default to xxx_id - although
allow overriding.
This certainly was possible with Elixir.
Paul
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Joinedload and duplicate relations
Hi,
I've just been debugging a slow running query:
products =
db.Product.query.options(sao.joinedload_all('variations.channels'),
sao.joinedload_all('variations.specifics')).all()
The second joinedload_all should just have been joinedload. It was causing
variations to be included twice in the joinedload, presumably causing a
cross product that made the query very slow.
I can't imagine there's any legitimate need to include a relation twice, so
it would be helpful if SQLAlchemy issued an error for this condition.
Many thanks,
Paul
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Recipe for text search across multiple fields
Hi,
Often you want to offer the user a text box which will search through
multiple fields. If the user is looking at the list of orders, they want a
search box that will search: order id, customer name, product names, etc.
I'm trying to put together a recipe for this, although it's becoming more
complicated than I planned.
The recipe will take three inputs: mapped class, list of fields, search
term. The list of fields will be like ['id', 'customer.name',
'products.name'] - where there is a dot in the field name, that indicates
the search should walk a relation. For starters the matching will be an
ilike with % characters put around the search term.
This is what I came up with so far:
def text_search(cls, fields, search):
queries = []
for field in fields:
query = cls.query.order_by(None)
parts = field.split('.')
cur_cls = cls
for part in parts[:-1]:
attr = getattr(cur_cls, part)
cur_cls = attr.property.mapper.class_
query = query.outerjoin(attr)
queries.append(query.filter(getattr(cur_cls,
parts[-1]).ilike('%'+search+'%')))
return queries[0].union(*queries[1:])
The problem is I'm getting PostgreSQL syntax errors, because some order_by
clauses are still appearing in the queries, which don't play nice with the
union. Any suggestions for fixing this would be welcome!
Once that's fixed, and with a few more refinements, I think this would be a
very handy recipe to keep around, or even put in the SQLAlchemy core.
Paul
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Recipe for text search across multiple fields
Hi, That's fixed it! I was so close :-) I was using mapper.order_by, which I'd hoped order_by(None) would cancel. No worries - it works now. This is in tw2.sqla DbListPage now, and I'll be putting updates in as I get round to it. Paul what's the purpose of cls.query.order_by(None) ? you're not using mapper.order_by i hope ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] zope.sqlalchemy - commit some objects on failure
Hi, I'm using zope.sqlalchemy in a web application (actually ToscaWidgets not Zope) so each request is wrapped in a transaction. If the request succeeds the transaction is committed; if there is an error it is rolled back. This works great. I have a log table where I log incoming XML to web callback methods (from eBay, 3DCart, etc.) Now, if there is an error what I want to happen is most things to be rolled back, but the log table still committed. This has left me scratching my head a bit. Any ideas on an elegant way to do this? Many thanks, Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZQATq5gFcgQJ. 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] Automatic data partitioning using a custom Session class
Hi Mike, we have a recipe that's all about the built in filter which also illustrates how to work around that existing criterion thing: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery Thanks for that - exactly what I needed. I'm still tweaking my app to use this approach, but it seems to be working really well. When (if) I get round to writing a tutorial I'll let you know. Hope you're keeping well, Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/QSyWHO39YgwJ. 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] Automatic data partitioning using a custom Session class
Hi,
I hope everyone's keeping well. It's been ages since I've been on the list.
I do use SQLAlchemy from time to time, but now it generally works so well,
that I don't have any questions to ask!
But I would appreciate some thoughts on the approach I've taken with a
multi-tennant SaaS web app. It's a multichannel stock management system for
online retailers. All the user data is attached to a merchant - products,
variations, categories, orders, etc. It's important that one merchant
cannot access data belonging to another merchant. When handling a request,
the active merchant can be determined from the logged-in user, which is
kept in thread local storage.
So I started with lots of code like:
db.Order.query.filter_by(merchant_id = twa.get_user().merchant_id)
Now, this is fine, but it's repetitive, and it's risky for security - it
just takes me to forget one filter_by merchant_id and we've got a security
vulnerability.
So, what I wanted to do is create a custom session that will do this
automatically. It needs to do two things:
1) Any query object against an entity that has a merchant_id property is
filtered on that
2) Any new object that has a merchant_id property has the property
automatically set
I don't think a session extension can do (1), so I created MySession
subclassing Session, and passed this as class_ to sessionmaker. Here's my
initial attempt at MySession:
class MySession(sa.orm.Session):
def query(self, *entities, **kwargs):
query = super(MySession, self).query(*entities, **kwargs)
for e in entities:
if e.tables[0].name == 'user':
continue
if e.has_property('merchant_id') and twa.get_user():
query = query.filter(e.class_.merchant_id ==
twa.get_user().merchant_id)
return query
Now, I faced on major problem - seeing these errors:
InvalidRequestError: Query.get() being called on a Query with existing
criterion.
As a temporary workaround, I edited query.py and disabled the check that
causes this. That's got me going for now, although obviously a proper fix
is needed. I haven't actually attempted (2) yet, but I will be trying that
shortly.
I'd really appreciate some feedback on this, particularly ideas to fix the
InvalidRequestError. I think this is a very powerful technique that would
be useful to many developers. Once my app is working I will see about
writing a tutorial on the matter.
Many thanks,
Paul
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/wK5ljrQ7z4cJ.
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] Efficiency of adding to M:M relations
Hi, I hope everyone's well here. It's been some time since I posted. Great to see it up to 0.6, and even more progress on MS-SQL (although I'm now unlikely to be using that). As always, I'm using SQLAlchemy as part of a web app. I have a set of checkboxes, which I'm saving into an M:M relation. The app receives a list of IDs from the client. To save these to the M:M, I need them as database objects. So I'm doing (roughly): myobj.relation = [OtherTable.get(i) for i in ids] The problem with this is it's causing a database query for each id. What I'd really like to do is somehow create a placeholder object with just the id, that doesn't cost a database query to create. After that, I'll trust flush() to do its magic as efficiently as possible. Paul -- 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] OT: Travel Blog
Hi all, Well, I finally set off on my travels, and it's turned out to be more than I ever dreamed of. If you're interested, I'm keeping a blog here: http://paj28.livejournal.com/ Hope everything's going well with SA. All the best, 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 -~--~~~~--~~--~--~---
[sqlalchemy] MSSQL Failing deletes with subquery + schema
Hi, Is there any chance someone can look at ticket 973? http://www.sqlalchemy.org/trac/ticket/973 This is quite important to me, hitting the bug with a production app. I've got a very hacky fix, which just disables the table aliasing, but I don't think that's good enough to commit. I have looked at this a few times and not figured it out, so any help would be appreciated. Best wishes, 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 -~--~~~~--~~--~--~---
[sqlalchemy] Replacement for query.mapper.class_
Hi, Hope everyone is well, not been around for a while. I've noticed one of my apps has broken with an SA upgrade. It was using query.mapper.class_ to determine the class from a query, but query.mapper no longer exists. What's the 0.5 way of doing 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 -~--~~~~--~~--~--~---
[sqlalchemy] Moving On
Hi, I've had fun over the last 18 months doing odd bits of work on SQLAlchemy. It works pretty damn well on MSSQL now, although I never did quite get all the unit tests nailed. It's been great seeing the library continue to evolve, and particularly satisfying to see things I've started (e.g. AutoCode) being taken forward. Just of late, I've been reassessing priorities in my life, and open source development isn't going to be a big one going forward. In fact, I may even be giving up the computer completely for a year or two and going travelling. I'll be unsubscribing from the mailing list in a couple of days, although I'm happy to receive SA related emails at my personal address, for the next couple of months at least. Thanks for the interesting times, 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlite PK autoincrement not working when you do a composite PK?
Hi, I don't think that is a very workable strategy in the long run :( There are far to many bogus restrictions in some databases, e.g. Oracle, for any meaningful program to be written to work on all platforms w/o support/wrapping/hiding of ugly details by SA. This is often a difficulty for libraries that provide a portable layer over different implementations. GUI toolkits are a good example. The library essentially has three choices: 1) Only expose functionality that exists on all the implementations 2) Expose the user to the slight differences between implementations 3) Expose consistent functionality, and where an implementation lacks support, fake it In practice, (1) is usually a poor option as it's too restrictive. SQLAlchemy currently takes approach (2). There is definitely consistency merit for approach (3), but it comes at a cost - there's more magic going on, which could be confusing in some circumstances. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Troublesome relation
Hi,
VulnResDesc.mapper.add_property('rawvulns', sao.relation(VulnRes.mapper,
primaryjoin = sa.and_(VulnRes.targetid == VulnResDesc.targetid,
VulnMap.vulndescid == VulnResDesc.id,
VulnMap.tool == VulnRes.tool,
VulnMap.toolvulnid == VulnRes.toolvulnid),
foreign_keys = [VulnResDesc.c.targetid, VulnResDesc.c.id],
viewonly = True,
uselist = True))
ultimately all relations distill the join condition into a set of pairs,
above it would be:
[ (VulnResDesc.targetid, VulnRes.targetid) ]
I still don't quite understand this I'm afraid, I though there were more
columns involved - VulnResDesc.id, VulnRes.tool, VulnRes.toolvulnid.
So using foreign_keys which only deals with these columns should probably
work by itself:
foreign_keys = [VulnResDesc.targetid]
The remote_side argument, if needed, would be remote_side=
[VulnRes.targetid] since that is the right side of the relation.
Ok, if I set foreign_keys like that, it doesn't ask me for a remote_side,
BUT when I come to actually use the relation, the query runs for a long
time, in fact my web app times out before it completes.
Have you got any other suggestions? I am pretty damn stuck here. And the
bugger is, this used to work just fine!
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Troublesome relation
Hi, I still don't quite understand this I'm afraid, I though there were more columns involved - VulnResDesc.id, VulnRes.tool, VulnRes.toolvulnid. So you know, this isn't urgent now. I've rewritten the mapper property as a property on my class, and that works straight off, without any voodoo. But I am still interested in any thoughts you have - obviously a proper relation is the way to do if I want to use the eagerloaded in future, for example. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to calculate the size of the Oracle and PostgreSQL schema !!!!!
Hi, My development team and me are working in a application to migrate Oracle databases to PostgreSQL databases, and we need this information to do this. I've done successful migrations using autocode, http://code.google.com/p/sqlautocode/ The procedure, roughly is: 1) Use autocode to generate sqlalchemy definitions from your existing database 2) Create the tables in a new database - which can be a different kind of database 3) Use a sqlalchemy program to copy the table data As for the program to use for (3), I've used a throwaway script, but I think there's something in dbcook (http://pypi.python.org/pypi/dbcook/0.2) you could use. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Troublesome relation
Hi,
I have had the following relation working ok for some time, but a recent
update of SQLAlchemy means it's now asking for a remote_side argument. I'm
really not too sure what too put in there - I've never really understood
that parameter, or foreign_keys.
VulnResDesc.mapper.add_property('rawvulns', sao.relation(VulnRes.mapper,
primaryjoin = sa.and_(VulnRes.targetid == VulnResDesc.targetid,
VulnMap.vulndescid == VulnResDesc.id,
VulnMap.tool == VulnRes.tool,
VulnMap.toolvulnid == VulnRes.toolvulnid),
foreign_keys = [VulnResDesc.c.targetid, VulnResDesc.c.id],
viewonly = True,
uselist = True))
If you'd like some explanation what it's doing, this is part of a tracking
system for security scans. VulnRes is each raw result from a tool, VulnDesc
(not used here) is a textual description of a finding, and VulnMap maps
VulnRes to VulnDesc. VulnResDesc is a view, that gets the distinct
VulnDesc's for each Target. The idea is that the rawvulns relation takes you
from a VulnDesc on a particular Target, to a list of the VulnRes that relate
to this. Not an easy relation :-)
So, what do I put in remote_side? I tried copying foreign_keys, and the app
started, but the relation is always empty.
Any help appreciated!
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Optimizing a slow query
Hi, create index viewforum on forum_post (topic_id, id); You probably want: create index forum_post_topic_id on forum_post (topic_id); 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Optimizing a slow query
Hi, create index forum_post_topic_id on forum_post (topic_id); are you sure? A key on topic_id and id makes my query much faster for low offsets, while without it the query takes even for low offsets 10 seconds. I didn't follow the beginning of this thread, so I'm not sure exactly your query, but if you're searching on topic_id, I'd expect the index I suggested to help (although I'm not sure how much), and the combined index you mentioned to make almost no difference. Did you try the one I suggested? But from your results, I wonder if the slowness is caused by something else, not particularly by the lack of an index. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
Hi, I thought the original impetus for scope_identity was not multiple execution contexts, but rather things being fouled up for some users where they had nested INSERTs being done via a trigger on the mapped table, and the brain-dead SELECT @@identity_insert wasn't able to pluck out the correct PK. Was there another reason I'm missing? You're right, that was the original motivation. I tried just changing @@identity for scope_identity(), which worked just fine on pymssql, but not on the other adapters. Did eventually get it working, but it involved pyodbc changes, that I was unable to do. Fortunately someone on the list volunteered, which was most appreciated. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
Hi, The SQL works in our SQL analyzer with or without the named parameter and with the semicolon. We tried it the way you mentioned too, using positional parameters, and got the same error. I apologize for forgetting to mention that in my first post. Dunno if this is related, but pyodbc and adodbapi execute each statement in a separate context. This caused a problem with scope_identity, as in the original implementation with pyodbc, scope_identity always returned null. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL dislikes schema= on table.create()
Hi, The specified schema name queer either does not exist or you do not have permi ssion to use it. This isn't an SQLAlchemy or DBAPI issue - you just need to create the schema with the correct permissions. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL dislikes schema= on table.create()
Hi, So now I'm of two minds about which module to use and if I should use a schema or not for these porposes. I'm using PyODBC and schemas, and things work just fine for me. Ok, some error messages aren't quite there, but it works well enough for me. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query Threading Issue
Hi Mike, ive thought about this, and the Query is not in fact tied very hard to a partcular Session (or at all). I think it would be workable for us to add a using_session() method to it, i.e.: The original workaround you suggested of using a callable works fine for now. A using_session method would be nice, avoid exposing this detail to the user of my widget. Although pretty soon after I made the change I found a separate advantage of using a callable, so I'll probably stay with that. I guess another option is to make the query bind to the scoped_session object, so it picks up an appropriate session when it actually uses it. Thanks for taking a look, 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: arbitrary information for sa objects
Hi, http://www.sqlalchemy.org/docs/04/sqlalchemy_schema.html#docstrings_sqlalchemy.schema_Table shows info as a parameter in the kwargs to a table. So it does, so it's tables and columns. The column info setting is in the same page as you sent across, just a bit further up. What other objects would you like it for? I'm likely to have a requirement for it on relations pretty soon. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Query Threading Issue
Hi, I'm writing a ToscaWidget to do a kind of data grid. The way I've designed it so far, you pass an SA query object to the Widget when it's created. Then, each time it's displayed, it applies a few extra filters to the query and fetches the results. I'm using scoped sessions, and the query ends up being used in several different threads. My initial thoughts are that there could be thread safety problems with this approach, although in practice it works just fine. How bad are the threading issues? And is there anything I can do to fix it, e.g. a step that clones a query and attaches it to the session for the current thread? Any ideas appreciated, 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: arbitrary information for sa objects
Alex, The info bucket is available just on columns at the moment, and it is a supported feature. I think we're open to adding it to other types, such as tables, if someone has a requirement. My desire for this was exactly the same as yours - the SA model serving as the authoritative master definition. Paul On Sat, May 3, 2008 at 9:47 AM, alex bodnaru [EMAIL PROTECTED] wrote: i read info is such a table option, but i'm not sure it's not deprecating? alex bodnaru wrote: hello friends, however sa stands at the model foundation of an application, i'd be interested to add additional information to some columns/tables/keys. is there a way to insert arbitrary information for sa objects? thanks in advance, alex --~--~-~--~~~---~--~~ 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: mssql, pyodbc and rowcount
Hi, meta.Session.execute(meta.metadata.tables.get(table).select()).rowcount -1 Can anybody explain what I have to do (if possible at all) to get the correct number of rows in my cursor? I don't think you can, short of reading all the results. The reason being that pyodbc streams them from the server, so when your query first returns, even the server may not know how many rows match. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: cant update mssql table
Hi, a table in a mssql db with a uniqueidentifier field as primary key and an integer field as identity,i am able to insert a row into the table but not update it Can you send the code you're using, and the error you are getting? I think this is an area we haven't particularly covered so far. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ODBC Connection is busy error
Hi,
Since our system went live we have been getting more more errors
like this: DBAPIError: (Error) ('HY000', '[HY000] [Microsoft][SQL
Native Client]Connection is busy with results for another command
(0)') u'SELECT ...snip valid SQL string...endsnip
I've seen this error too, in fact some of the unit tests trigger it. The
cause is that a single ODBC connection can only have one query active at
a time, even if you have multiple cursors. I believe this is different
to most other DBAPI drivers.
I have no idea how to fix this in SQLAlchemy, have thought about it a
bit without success. A workaround is to recode your app to it fetches
results right after each query.
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Extension proposal
Hi, the function so far seems pretty use-case specific. (only works in one direction, is hardcoded to the obj.mapper convention, sort of Ok, fair enough. It looks like Elixir will accept this, and Jonathan also made the observation that my approach is one-way. For the time being, I've updated the usage recipe which should keep Lele and Iain happy for now. If you do change your mind, my offer to produce a patch with unit tests stands. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Extension proposal
Hi,
I have a utility function that I keep using again and again in my web apps.
It takes a nested hash/list structure, something like:
data = \
{
*'id'*: 123,
*'name'*: *'Example customer'*,
*'contacts'*:
[
{*'id'*:12, name=*'Alice'*}
{*'id'*:23, name=*'Bob'*}
]
}
And it takes an SQLAlchemy object. It updates the object based on the
hash/list, in a deep way, in that it goes down relationships.
There's an old version of my code here:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ProcHash
Anyway, I keep using this, so can it be included as an SQLAlchemy extension?
If it's accepted, I will do the work to create unit tests for it.
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Extension proposal
Hi, In my case, the data is coming from FormEncode. I guess it could come from JSON, but that would be JSON client to server, which is not the usual way round. The proposal is for a standalone function, I'm thinking you'd do something like: from sqlalchemy.ext.proc_hash import proc_hash ... proc_hash(myobj, mydata) As this is just plumbing python data structures into database objects, I think it's fitting to the SA-core goals. Paul On Mon, Apr 14, 2008 at 3:00 PM, Michael Bayer [EMAIL PROTECTED] wrote: just two seconds ago someone asked about sticking a JSON plugin in SQLAlchemy (this looks essentially like JSON to me).is the extension proposal something that builds in using MapperExtension ? it seems like people are just looking for json-like functions for things (which is handy, though not an SA-core kind of feature). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy questions
Hi, I think you'll do well to switch to an open library, I certainly found it worthwhile when I made a similar switch 18 months ago. And that's despite getting side-tracked making SA support MSSQL more completely :-) On you specific points: 1. Be able to call custom functions when a field is updated, sometimes 2. Force a field to be lowered all the time. When we insert, update or 3. Handle multiple databases in the model.. We keep our user account You won't have much trouble doing those. 4. In the object model, hook into the object create, load, write/ update, and delete.. We've implemented an ACL type access on our objects, and would need to verify these ACLs for each action that touches the DB/.. Not 100%, but I think you can do that with a MapperExtension. 5. We also have a circumstance where we have 2 objects in difference Hmmm, that's harder. You probably can create a proxy class that hides the distinction, but I've got a feeling you'll have some fun making this work in all the corner cases. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL, pyodbc linux
Hi Lukasz,
Under:
http://www.sqlalchemy.org/docs/04/documentation.html#dbengine_establishing
after:
# oracle will feed host/port/SID into cx_oracle.makedsn
oracle_db = create_engine('oracle://scott:[EMAIL PROTECTED]:1521/sidname')
It's great to see you getting MSSQL to work on Unix. I know someone else
tried this and had some good success, the unit tests ran nearly as
cleanly as on Windows. As for the connection info, I suggest you add it
to http://www.sqlalchemy.org/trac/wiki/DatabaseNotes initially (it's a
wiki) - if this is useful to a lot of people it may make its way to the
official docs.
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL, pyodbc linux
Hi, eng = sqlalchemy.create_engine (mssql:///?dsn=mydsn,UID=myusername,PWD=mypass,module=pyodbc) Try this: eng = sqlalchemy.create_engine (mssql://myusername:mypass@/?dsn=mydsn,module=pyodbc) 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Suggestions for setup.py
Hi, h im not sure every DBAPI can get installed through easy_install tho. pyscopg2 comes to mind. Similarly you can't easy_install pyodbc at the moment. I was thinking that doing this would be motivation to do the work to make those packages easy_install'able. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Suggestions for setup.py
Hi, I've noticed that setuptools supports optional dependencies http://peak.telecommunity.com/DevCenter/setuptools#declaring-extras-optional-features-with-their-own-dependencies Would it be worth adding one of these for each database SA supports? You could then do easy_install sqlalchemy mssql to get SA + the appropriate dbapi. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
John, I am using unixodbc-2.2.11 as packaged by Ubuntu 7.10 (gutsy) with That sounds very promising, I have been meaning to have a go at this for a while. Can you do me a favor and run the unit tests using your current setup? Run alltests.py and append text_as_varchar=1 to the dburi (a few mssql tests rely on this). Save the stdout and stderr and send them to me. This would really help us gauge how much work on unix support is needed. For comparison, a run on windows with pyodbc has about 40 test failures. 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 -~--~~~~--~~--~--~---
[sqlalchemy] MSSQL Sprint
Hi, Would anyone like to join me in doing a one day sprint on MSSQL support in SQLAlchemy? (Rick - hope you can find some time) I feel we're at the point now where the last few niggles could be ironed out quite quickly. I've just lost steam a bit doing this on my own. The main points would be: 1) Add support for limit with offset 2) Fix the last few failing unit tests for pyodbc on Windows against SQL 2005 and 2000 3) Close off the remaining MSSQL tickets 4) Take a look at pyodbc on Unix Let me know if you're interested, we can figure out a date that suits everyone. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
Hi, ...and what happens if these methods are called and the tables already exist? With metadata.create_all, it only creates ones that don't exist. table.create() will error, or if you use the checkfirst option, will do nothing. What if they exist but don't match the spec that SA has created? SA doesn't know, so it continues until you hit a problem. We should really pull the code in tg-admin sql out into a standalone script. I think having a model vs database diff function would help you out a lot. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
Hi Chris, What happens when the schema expected by the mappers doesn't match up to the schema in the database? If the SQLAlchemy table definitions don't match the database, you will usually get SQL errors when you try to use them. The TurboGears admin tool can tell you the differences between the database and the SA table definitions. I use this quite a lot, to check the database is ok. To make changes, I first change the SA definitions, run tg-admin sql status, make the changes to the DB by hand, and run it again to check I did it right. This works for me; it could be improved, but that isn't greatly urgent. For getting started with an existing database, try this: http://code.google.com/p/sqlautocode/ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: InvalidRequestError
Hi, I do a session.clear(), all time, after of session.save_or_update([obj]) Can this be the problem? That is almost certainly your problem. Try removing the session.clear() and see if it then works. When is advisable do a session.clear() ? When you're done with a batch of processing. In web applications this fits nicely at the end of a request. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to MSSQL with a System DSN on Windows
Hi,
For some reason it didn't work for me (sqlalchemy 0.4.2p3). I tried
mssql://?dsn=MyDSN, and also mssql://?DSN=MyDSN. Oh well.
Sorry, it's
mssql:///?dsn=mydsn
This definitely works, just tested it
eng = sqlalchemy.create_engine('mssql:///?dsn=bob')
eng.execute('select 1')
sqlalchemy.engine.base.ResultProxy object at 0x00D17CB0
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to MSSQL with a System DSN on Windows
Hi, 'mssql://DSN=MyDSN'. How can I get this to work without specifying You were nearly there... mssql://?dsn=mydsn 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: connect sql server
Hi, How can I connect to sql server with windows authentication using sqlalchemy? Using pymssql I haven't find a way. You have to use adodbapi or pyodbc - just specify no username/password and it just works. I don't think it's possible with pymssql. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL and LIMIT/OFFSET
Hi, adding row_number will render a distinct clause useless... Actually, this isn't a problem, as the distinct goes in an inner query, and the row_number is only in the outer query. I did have this mostly working, just need a final push to get it finished and committed. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Issue with flush()
Hi,
Having just upgraded to the latest svn, I'm now getting the traceback below.
It looks like it's related to the attribute tracking changes. This is coming
from a fairly large program, but if it helps, I can extract a minimum test
case.
c:\sqlalchemy\lib\sqlalchemy\engine\base.py:1439: SADeprecationWarning:
Using String type with no length for CREATE TABL
E is deprecated; use the Text type explicitly
rec = (type_, type_.dialect_impl(self.dialect).result_processor(
self.dialect), i)
Traceback (most recent call last):
File C:\tsrweb\scripts\parse-results.py, line 20, in ?
turbogears.database.session.flush()
File c:\sqlalchemy\lib\sqlalchemy\orm\scoping.py, line 74, in do
return getattr(self.registry(), name)(*args, **kwargs)
File c:\sqlalchemy\lib\sqlalchemy\orm\session.py, line 693, in flush
self.uow.flush(self, objects)
File c:\sqlalchemy\lib\sqlalchemy\orm\unitofwork.py, line 215, in flush
flush_context.execute()
File c:\sqlalchemy\lib\sqlalchemy\orm\unitofwork.py, line 437, in
execute
UOWExecutor().execute(self, tasks)
File c:\sqlalchemy\lib\sqlalchemy\orm\unitofwork.py, line 927, in
execute
self.execute_save_steps(trans, task)
File c:\sqlalchemy\lib\sqlalchemy\orm\unitofwork.py, line 945, in
execute_save_steps
self.execute_dependencies(trans, task, False)
File c:\sqlalchemy\lib\sqlalchemy\orm\unitofwork.py, line 956, in
execute_dependencies
self.execute_dependency(trans, dep, False)
File c:\sqlalchemy\lib\sqlalchemy\orm\unitofwork.py, line 939, in
execute_dependency
dep.execute(trans, isdelete)
File c:\sqlalchemy\lib\sqlalchemy\orm\unitofwork.py, line 892, in
execute
self.processor.process_dependencies(self.targettask, [elem.state for
elem in self.targettask.polymorphic_tosave_elem
ents if elem.state is not None], trans, delete=False)
File c:\sqlalchemy\lib\sqlalchemy\orm\dependency.py, line 282, in
process_dependencies
self._process_key_switches(deplist, uowcommit)
File c:\sqlalchemy\lib\sqlalchemy\orm\dependency.py, line 285, in
_process_key_switches
switchers = util.Set(s for s in deplist if self._pks_changed(uowcommit,
s))
File c:\sqlalchemy\lib\sqlalchemy\orm\dependency.py, line 285, in
generator expression
switchers = util.Set(s for s in deplist if self._pks_changed(uowcommit,
s))
File c:\sqlalchemy\lib\sqlalchemy\orm\dependency.py, line 161, in
_pks_changed
return self.syncrules.source_changes(uowcommit, state)
File c:\sqlalchemy\lib\sqlalchemy\orm\sync.py, line 99, in
source_changes
if rule.source_changes(uowcommit, source):
File c:\sqlalchemy\lib\sqlalchemy\orm\sync.py, line 133, in
source_changes
prop = self.source_mapper._columntoproperty[self.source_column]
KeyError: Column('vulndescid', Integer(), ForeignKey('tsr.vulndesc.id'),
table=vulnmap)
Regards,
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: migrating to 0.4: Parent instance is not bound
Hi, sqlalchemy.exceptions.InvalidRequestError: Parent instance class ' doupy.model.objects.JobPosting' is not bound to a Session, and no contextual session is established; lazy load operation of attribute 'author' cannot proceed. Not 100% sure without seeing your model, but you probably want to use session.mapper in place of mapper. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Hi,
/me faces toward UK, where it's about midnight right now...
/me yells HEY PAUL!! YOU WATCHING THIS THREAD??
Ok, you got my attention :-) Not at my best right now after being out
drinking, but hey...
After a little tweak to the code (removing autoload=True, adding
metadata.create_all() ) I get this:
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) ('42000',
'[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion
failed when converting from a character string to uniqueidentifier.
(8169)') u'INSERT INTO jobs (identifier, section, start, stop, station)
VALUES (?, ?, ?, ?, ?)' ['TEST1', None, datetime.datetime(2007, 12, 10,
23, 40, 30,593000), None, None]
So, follow Rick's advice on fixing it. This does work with SQLite, but
that's an accident of SQLite's funky type system more than anything.
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Design: mapped objects everywhere?
Hi, A Sample may be created by the web application or fetched from the database. Later on, it may be disposed of, edited or checked back into the db. On the other hand, the requirements and coding of both classes are kinda different, and I find myself changing the properties of the mapped class for better mapping and making the use of the class in non-Db contexts more awkward. Sounds like you want your app to be mostly unaware of whether a class is saved in the db or not (i.e. persistent)? If so, I'd use a single class, design the properties so they work in non-persistent mode, and then they'll work in persistent mode as well. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Representation of boolean logic in a database
Hi, My problem is beeing able to represent and store relations between options and contents tables in a normalized way. I'd probably just store the relationship as a string. Do you have any particular querying requirements? Sometimes pragmatism beats elegance. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SA 0.4.1 and MS-SQL problem at create time
Hi, It seems that the mssql backend use a 'foreign_key' attribute on Column which does not exist anymore. Yes, it's now foreign_keys. This is fixed in the svn trunk. I still need to sort out a way to have MSSQL unit tests run periodically, so we can pick up this kind of issue before releases. 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 -~--~~~~--~~--~--~---
[sqlalchemy] MSSQL and LIMIT/OFFSET
Hi, Can someone (Mike?) give me a hand with this, I've hit the limits of my understanding of the query compiler. The patch I've done on #638 (mostly copied from Oracle) creates a subquery with row_number if there is an OFFSET. It aliases the query, because MSSQL is funny about that, and it also attempts to move ORDER BY from the inner to outer query (again, MSSQL is funny). Only problem is that it doesn't quite pick up the correct labelled name. Uncomment this line in the patch to see the problem: #limitselect._order_by_clause = select._order_by_clause Any ideas would be a help (and a query compiler 101 document would be fab!) 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: unicode support for MSSQL
Hi Florent, Just realised we'd gone quiet on this thread... humm What bothers me is that I already get this comportement when running my query program from a Linux host (using pyodbc same version) but need the above mentioned patch on a windows host so there is definitely a different behavior. Is this a difference in PyODBC or SQLAlchemy? I suspect the former, but good if you can confirm. From my point of view I am responsible to give the engine the right encoding when I instantiate it. At the moment I have a master database that provides me this info and so I feed it to the constructor at engine creation time. That sounds ok. I'd be happy to add a string_charset option or something that defaults to None which means no decoding. In fact, this wouldn't have to be MSSQL specific, it could apply to any DB. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: pyodbc and inserts... again
Hi, I've just noticed a remaining problem with the pyodbc/inserts with triggers/scope_identity()/set nocount on/nextset() thing ;-) (it's still a workaround if I understand correctly?) scope_identity is expected to be a permanent solution. The set nocount on is a bit of a hack and will ideally be removed some day. If you comment it out at the moment, it causes 12 additional unit tests failures, so a bit of work is needed to do this cleanly. new version, which keeps jumping as long as there are errors: Looks good to me, and doesn't cause any additional unit tests to fail. Committed in 3819. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Multiple inserts and last_inserted_ids
Hi, I just realised, MSSQL is not returning last_inserted_ids correctly when an insert is done with multiple sets of params for a single statement. I don't think this is particularly a problem, just wanted to check what the intention is. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: FYI: SQLAutocode 0.4.1 and 0.5 released
Hi, Could you perhaps explain the advantages/differences to autoload=True or perhaps SqlSoup? To me, it's quite a different approach to managing your database schema. Using autoload or sqlsoup, the database holds the master definition of the schema, and your program automatically loads that. The other approach is to have you python code be the master definition, and keep the database automatically synced to that. Tools like Migrate and AutoCode help people using this approach. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Deprecation error raised for query on singly inherited table query ?
Hi, The following is a stripped down use case that I have, where I use single table inheritance and run a query.first(), however I get a deprecation warning and I was wondering why ?? A minor bug, an instance of SA internally using the deprecated syntax. Fixed in r3766. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: unicode support for MSSQL
Hi, I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. Ok, so in this case you'd like SA to return a python unicode object when a VARCHAR is fetched, by decoding using the database's encoding? While I understand your requirement, this seems to me to be a special case. I think most people would expect a normal string in this case. I wonder if you should define a MyString class in your app and use that. Rick - do you have a feel on this one? If we do decide to implement this, does anyone know how python can find out what database encoding MSSQL is using? 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Can I have an integer primary keys that is not a sequence?
Hi, Is there a way to tell SQLAlchemy to create an ordinary integer primary key, without any associated sequence? Sure... autoincrement=False 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: unicode support for MSSQL
Hi, I have 10 different instances the each have their own collation names (latin1, greek, russian...) I have a master database that references all thos instances + their collation names. I use this master database to create the engines to the different dbs. I would like to be able to just pass the encoding for each engine at creation time an forget about it. Are your strings VARCHAR or NVARCHAR? If they're NVARCHAR, all this will just work as-is with SA and MSSQL - are you having any specific problems? If they're VARCHAR, then we need to think some more. I'm still not sure what the semantics should be when trying to save a unicode object in a non-unicode storage area. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: access mapped object attributes
Hi, Given a Message object, do I have a way to retrieve all the attributes that result from the database mapping? Try this: for col in Message.c: ... 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Add arbitrary information to some classes
Hi, sorry, i havent been following. two++ dicts ?! this is getting out of hand. if we have to have any dicts at all, it would be just one dict. and also, it should be proxied through a property so that if you dont access it, its never even created. I have just put a proposed patch on ticket #573. It uses info as the name and puts it on SchemaItem. Due to the way constructors are arranged for SchemaItem subclasses, I've explicitly put this in the constructor for Table and Column. Happy to take further comments on this; at least now we've got a straw man to pull apart. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: column and table functions from sqlalchemy.sql
Hi, Just noticed that from sqlalchemy import * imports all functions from sqlalchemy.sql.expression, except column and table - is this by intent or have these only be forgotten? It's to prevent confusion between column and Column - which are very different! 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: unicode support for MSSQL
Florent, I just added ticket #839 to the trac and attached a patch that enables unicode conversion for MSSQL dialects. I tested it with pyodbc but it should work the same with the other dialects. What's the benefit of doing this? I've found that you usually want to pass python unicode objects to PyODBC and adodbapi; utf-8 encoded strings don't work properly (they end up as literally that - utf-8 byte strings, stored in a 16-bit string container). 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to get list of relations
Hi, You are missing a compile call before you can iterate properties. try adding: Ah, that is indeed the problem. With that in place, iterate_properties does very nearly what I need. The only problem is that I need to get the name of the relation as well. For now, the following works: [(a,b) for a,b in getattr(obj.mapper, '_Mapper__props').items() if isinstance(b, sqlalchemy.orm.properties.PropertyLoader)] There's probably a better way, but my app is now working. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Add arbitrary information to some classes
Hi, Ah sure, so it's to be a namespace for namespaces, a shared dict() parking lot. Got it. How about having two dicts? One is purely for user data, libraries and such never touch it. I suggest userdata. The other is for use in extensions and stuff, say extdata. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to get list of relations
Mike,
use mapper.get_property(name) and mapper.iterate_properties(). I've
considered removing properties as a public accessor since it serves
no useful purpose.
This doesn't work for me - the following code outputs:
[Column('id', Integer(), primary_key=True, nullable=False)]
[Column('val', String(length=None,convert_unicode=False))]
I can do a test case without Elixir if needed, but I don't think that will
change the result.
from sqlalchemy import *
from elixir import *
__metadata__ = MetaData('mssql://./test')
class Paj(Entity):
val = Field(String)
class Bob(Entity):
paj = ManyToOne(Paj, primary_key=True, backref='bob')
silly = Field(Integer)
for a in Paj.mapper.iterate_properties:
print a.columns
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to get list of relations
Hi, use mapper.get_property(name) and mapper.iterate_properties (). I've considered removing properties as a public accessor since it serves no useful purpose. Ok, I found a hacky way that does what I need: [(n, getattr(obj, n)) for n in dir(obj) if isinstance(getattr(obj, n), sqlalchemy.orm.attributes.InstrumentedAttribute)] That'll do me for now. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to get list of relations
Hi, Ok, I found a hacky way that does what I need: [(n, getattr(obj, n)) for n in dir(obj) if isinstance(getattr(obj, n), sqlalchemy.orm.attributes.InstrumentedAttribute)] Ooops, not quite what I need. How do I go from a CollectionAttributeImpl to a mapper? 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLalchemy coding style
Hi, I have a python file for all the table and class definitions (model.py). In that I do from sqlalchemy import *, and that's ok as in that one file I know not to use names like Table. In my other python files I just import classes from model.py, and specific bits of sqlalchemy. Ok, occasionally I get lazy and just do an import * :) Paul On 10/29/07, McA [EMAIL PROTECTED] wrote: Hi all, I'm intersted in using sqlalchemy and started to read the manuals. I didn't find a hint for my question, so I'm asking here. I hope it's not too annoying. Most code examples in the documentation use something like this from sqlalchemy. import My question is: Is this the good/proper way to import the sqlalchemy stuff. I'm concerned about polluting the current namespace. I could assume that class names like 'Table' are too common to reserve them for the sqlalchemy classes. What would you recommend? How are the gurus out there using sqlalchemy? Thanks in advance. Best regards Andreas Mock --~--~-~--~~~---~--~~ 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 get list of relations
Hi, How do I get a list of the relations a mapper has? I've been using mapper.properties, but have just realised this doesn't pick up backrefs. Any ideas? Thanks, 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Two MSSQL databases on one engine?
Hi, Do have to define two engines to access two databases on the same database server? With MSSQL, at the moment you do. People have suggested changes to workaround this (using dbname.schema.table), but nothing has been implemented as yet. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SA (4.0) mssql table creation problems. No Python Errors?
Hi, Thanks for the help. I am still having those intermittent problems. I tried using just pyodbc, but the symptoms were the same. I tried a new machine, Windows 2003 Server, and installed SQL Server 2005 (SP2), Python, EZTools(py), Win32com(py), pyodbc(py), sqla(py), I've used MSSQL through PyODBC extensively and it's always worked fine for me. I realise, I've not used 2005 SP2, I wonder if they've introduced some security lockdown that's broken something. Might be worth trying with SP1. Do you get anything in you SQL Server log when this happens? 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: RLIKE, REGEXP
Hi,
Resource.select_by( foo=bar )
Untested, but I reckon this will work:
Resource.query.filter(Resource.foo.op('rlike')('bar'))
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to a Microsoft Access Database
Hi,
access:///VMCPDB.mdb
Three slashes! The hostname is empty. And you want the full path to the .mdb
file.
Paul
On 10/18/07, Eddie [EMAIL PROTECTED] wrote:
Latest Version still gives me problems
still in the same engine = create_engine('access://VMCPDB') line.
Debug looks like the newest rev installed correctly...
any help would be nice...
[ Things before line 105 are unrelated to SA.. besides the import
lines :) ]
Traceback (most recent call last):
File monitor.py, line 105, in module
engine = create_engine('access://VMCPDB')
File c:\python25\lib\site-packages\SQLAlchemy-0.4.1dev_r3640-
py2.5.egg\sqlalchemy\engine\__init__
.py, line 173, in create_engine
return strategy.create(*args, **kwargs)
File c:\python25\lib\site-packages\SQLAlchemy-0.4.1dev_r3640-
py2.5.egg\sqlalchemy\engine\strategi
es.py, line 67, in create
(cargs, cparams) = dialect.create_connect_args(u)
File c:\python25\lib\site-packages\SQLAlchemy-0.4.1dev_r3640-
py2.5.egg\sqlalchemy\databases\acces
s.py, line 208, in create_connect_args
connectors.append(Dbq=%s % opts[database])
KeyError: '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: Storing DB engine in session
Hi, Here's how it should work: every time the user logs in, SA creates a new engine with his log/pass or uses the one that was already opened and then uses it. Unless something has changed recently, this pattern is not particularly supported. Still, you could probably get it working with bound sessions. If the engine doesn't exist in the users, session, create the engine and save in the session. Don't know why the Pylons session save was failing, perhaps it doesn't allow arbitrary Python objects. You could keep your own dictionary, keyed on (username, password) tuples and avoid sessions altogether. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to a Microsoft Access Database
Hi, Thanks for the help. Looks like I'm still stuck though. Use the latest SVN version of SA 0.4, where I've fixed this issue. You'll need to do something like: svn checkout http://svn.sqlalchemy.org/sqlalchemy/trunk sqlalchemy cd sqlalchemy setup.py develop 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: FYI: AutoCode moved to a new repository
Hi, I don't say that everything should be integrated, just the reflection part (if at all useful in SA0.4) and the repr methods of the corresponding objects (*_repr in formatter.py) which should IMHO replace the current repr methods. That sounds good. One consideration is that autocode repr functions include line breaks and indentation, so the resulting code is easier to read. I hope this isn't too verbose for default repr functions. If it is we could make the behaviour switchable with a parameter. With this functionality in SA, autocode becomes somewhat shorter. I wonder if we could have a scripts directory in the main SA svn, where this could live? It would be good to have an admin script too (create tables, see status of database against SA definitions, etc). 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: FYI: AutoCode moved to a new repository
Hi, BTW, with SA 0.4, this script should be able to work with no database-specific hacks at all. If you're interesting in implementing this, I can explain more. Would be nice to hear more details about this. With 0.4, dialects have a table_names() method that will do the job of loader.py. Some versions of AutoCode (although not 0.4 by the look of it) have outputted generic types, rather than db-specific ones, e.g. Integer rather than MSInteger. This can be done in a database independent way, by going through the __mro__ of the specific type, until a type that appears in types.py is encountered. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to a Microsoft Access Database
Hi, I am very sorry for asking this question, but I was wondering if anyone could give me a short step by step process as to how to access a Microsoft Access Database using SQLAlchemy. Access support is experimental. Use the lastest 0.4 trunk, as I've committed a few fixes just now. Beyond that, all you do is use SQLAlchemy as with any other database. The DBURI is like access:///c:/my/file.mdb There's some more info here: http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#MicrosoftAccess 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: FYI: AutoCode moved to a new repository
Hi, It's really good to see this script progressing. BTW, with SA 0.4, this script should be able to work with no database-specific hacks at all. If you're interesting in implementing this, I can explain more. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Can't insert records into a MS SQL database
Hi, Try adding autoincrement=False to the t_year column. why would this fix the issue exactly ? Fair question. The explanation is a bit convoluted, not as nice and simple as the fix. MSSQL's equivalent of SERIAL/autoincrement is an identity flag on a column. To insert an explicit value into that, you have to issue set identity_insert table on. The MSSQL dialect does this for you automatically, and that was the statement failing in Paulino's example. The MSSQL dialect also automatically gives a column the identity flag if it's an integer primary key, and autoincrement is true. So it would have for t_year. Paulino is using table reflection, and I reckoned the real db table does not have the identity flag - it would certainly be odd to put that on a year column. Regards, 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Can't insert records into a MS SQL database
Hi,
ppi = Table('ttfvbs061100', metadata,
Column('t_year', Integer, primary_key=True),
Column('t_dimx', String(), primary_key=True),
Column('t_ceco', String(), primary_key=True),
autoload = True)
Try adding autoincrement=False to the t_year column.
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Supporting sybase backend through mx.ODBC driver
Hi, I was wondering if there is any development effort going on to support a sybase backend for sqlalchemy based on the mx.ODBC drivers from egenix (google didn't find anything and there was no relevant thread on this list) ? Are you hard set on mxODBC? PyODBC seems to be a good free alternative, and is already the preferred SQLAlchemy driver for MS-SQL, which may help get you started. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Built-in escape function?
Hi,
It doesn't, and that would be a reasonable addition.
I'm also thinking that startswith, endswith and contains should probably do
the escaping by default.
If you create a ticket I'll look at it sometime, although not for a couple
of weeks (I'm on holiday, woo :-)
Paul
On 9/21/07, Felix Schwarz [EMAIL PROTECTED] wrote:
Hi,
as several nice people from this list told me, SQLAlchemy uses bound
parameters by default so that ordinary SQL injections are not possible
anymore.
However, I want to escape search patterns in like-queries, e.g.:
User.c.username.like('%' + userinput + '%')
Of course, I can write my own function to escape all pattern characters
but as always it seems to be more secure to use existing functions.
After looking at the documentation for SQLAlchemy 0.3.10, I did not find
an escape function.
So just a quick question: Does SQLAlchemy come with a function to escape
patterns?
thank you very much
fs
--~--~-~--~~~---~--~~
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: MSSQL connection url format?
Hi, Should the hostname be the IP of the server or SQL Server's host\instance combination? Both work ok for me. Isn't the port normally 1433? Yup 2. I saw an archived email that said there was better support for MSSQL in 0.3 than in 0.4. Is that still the case? Support in 0.4 is pretty solid, main issue is update/delete statements on tables that have a schema. That should get fixed pretty soon. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Feature suggestion: Description attribute in Tables/Columns
Hi, For my documentation, it is very handy to gather the table definitions directly from my SQLAlchemy-based python code - that's relatively easy, I This would be useful for me too, and it would be good to have hooks to store arbitrary information. I'd be using this as hints for a form builder, just like the Django auto-admin does. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Weak Referencing Session
Hi, that the identity map within the Session again becomes weak referencing, the way it used to be back in 0.2 and part of 0.3. +0 from me. It sounds like a sensible change, but I only use short-lived sessions in web apps, so it makes little difference to me. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and assign_mapper: select
Hi, And if I wanted to select a year and group by year? select User.Year from User group by User.Year db.execute(select([User.Year]) ??? Have a look at http://www.sqlalchemy.org/docs/04/sqlexpression.html 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and assign_mapper: select
Hi, I am using turbogears with sqlalchemy and assign_mapper. How do I select one column only when doing a select statement? Can I ask why you want to do that? Unless you're really speed critical, selecting them all isn't too bad. But, if you're set on this, there are two approaches: 1) Abandon the ORM and do something like db.execute(select([Useraddress.Address_Sid], Useraddress.User_Sid == 30)) 2) Use deferred column loading (see the docs, advanced data mapping). 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Elixir performance
Hi, data. I did some benchmarks a while back to see how everything stacked up as I was wondering if I was doing everything the hard way (in C++) instead of using SqlAlchemy, etc. TurboEntity is the same as Great work Eric. I am quite surprised at the results. I would have thought ActiveMapper/TurboEntity would only be marginally slower than plain SQLAlchemy. And again, I'm surprised that SA is faster than MySQLdb. How does that work out? I though SA used MySQLdb??? Your use of query cache and best of three sounds sensible, but I've got a feeling we're seeing some kind of measurement effect in the results. If those numbers are correct though, I'd expect fairly simple changes to Elixir could bring the performance close to plain SA. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Testing for validity of a Connection
Hi, What it doesn't handle is if the database server is restarted. That doesn't happen very often with reliable database servers nowadays but it is possible. Yes, this is something of a problem for long-running apps that used pooled database connections. I did some work with Mike a few months back, so that SA notices a dropped connection exception and invalidates the connection. The current statement fails, but at least subsequent requests in that thread can succeed. You could put select 1 at the start of all your requests, acting as a ping. You can just ignore errors in the ping - SA will reconnect automatically. That's a bit of a performance hit, so I wonder if we can do any better. The reason for not retrying the failed statements is that there could be transactional state which would be lost. However, perhaps SA could keep track of whether there is transactional state. If there isn't any, it can retry the statement safely. Perhaps make this behaviour not the default and make people turn it on with an option. Considering that the problem will almost always happen on the first db query in a request, that will fix almost all cases without needing a ping. I might have a go at coding this up in the next couple of weeks. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Best way to handle in()
Hi,
foo = session.query(MainTable).filter_by(customer='CUSTNAME')
foo = foo.filter(ChildTable.c.othercolumn.in_('bar', 'baz', 'qux'))
You need to do the join; pass the name of the relation (not the target
table) to join:
foo = foo.join('childtable').filter(ChildTable.c.othercolumn.in_('bar', 'baz',
'qux'))
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: interface error with Decimal(0) in where clause
Hi, firebird which is the only other database engine supported by SA that is embeddable in a python application without the need of and external server. There is one other option, on Windows, the SA 0.4 beta supports Microsoft Access. I believe it works on all Windows systems (i.e. not just ones that have Access installed). 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: between_op() error with property expressions: is this a bug?
Hi, sounds like a bug. for now, call it from the Column object: MappedClass.c.age.between(1,2) (that should work). There seems to be a bug in sql/operators.py - between only has two arguments, not three. I've hit this as well. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using 0.4 in anger
Hi, Couple more things: 1) When specifying foreign_keys manually on a relation, you have to use table.c.column; table.column doesn't work. 2) I used to be able to do obj.delete() but now obj.query.delete() isn't available. You can make whatever changes needed to ActiveMapper, but keep in mind the whole extension itself is deprecated ;) (of course it will be around for awhile since turbogears depends on it) Changing AM to use scoped_session was pretty easy. However, this changes compatibility quite a lot - without the change, cls.get() works but gives a warning; with the change, cls.get() doesn't work. I still think the change is worthwhile, sticking closely to the 0.4 style, but I'll take your steer on whether this is to much change (we'll have to decide before 0.4 final though). All the best, 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 -~--~~~~--~~--~--~---
