Re: [sqlalchemy] Re: Is it possible for alembic or other migration tool to handle case sensitivity or insensitivity across database engines
How to upgrade to a newer sqlalchemy version? I can not find a related document. Should I just use pip to install the current one? Will both version conflict? Thank you very much for your quick response and help! JP -- 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/-/4bhhsm_88s0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Is it possible for alembic or other migration tool to handle case sensitivity or insensitivity across database engines
Oh, I see, mine is not sqlalchemy 0.8. Thanks. -- 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/-/dvjzhmyWicoJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Is it possible for alembic or other migration tool to handle case sensitivity or insensitivity across database engines
versionadded is 0.8
On Dec 6, 2012, at 4:36 PM, junepeach wrote:
> I tested my code, and got below:
>
> Traceback (most recent call last):
> File "/usr/local/bin/alembic", line 9, in
> load_entry_point('alembic==0.4.0', 'console_scripts', 'alembic')()
> File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 255,
> in main
> CommandLine(prog=prog).main(argv=argv)
> File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 250,
> in main
> self.run_cmd(cfg, options)
> File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 241,
> in run_cmd
> **dict((k, getattr(options, k)) for k in kwarg)
> File "/usr/local/lib/python2.7/dist-packages/alembic/command.py", line 97,
> in revision
> script.run_env()
> File "/usr/local/lib/python2.7/dist-packages/alembic/script.py", line 191,
> in run_env
> util.load_python_file(self.dir, 'env.py')
> File "/usr/local/lib/python2.7/dist-packages/alembic/util.py", line 185, in
> load_python_file
> module = imp.load_source(module_id, path, open(path, 'rb'))
> File "alembic/env.py", line 18, in
> from dbmodule import SAINT8
> File "/home/bethesda/Documents/dbmodule/mytables.py", line 35, in
> Column('data', character_type(32)),
> File "/home/bethesda/Documents/dbmodule/mytables.py", line 21, in
> character_type
> VARCHAR(length, collation='utf8_general_ci'), 'mysql'
> TypeError: __init__() got an unexpected keyword argument 'collation'
>
> Did I miss importing a class related with 'collation'? I tried 'from
> sqlalchemy.types import *', and some others, but didn't get it resolved, what
> is wrong?
>
> Thanks,
> jp
>
> On Thursday, December 6, 2012 4:10:01 PM UTC-5, junepeach wrote:
> Thanks, this is nice, I will use it in my code.
>
> On Thursday, December 6, 2012 3:17:25 PM UTC-5, junepeach wrote:
>
> For case insensitive columns:
> MySQL - use utf8_general_ci
> SQLite - use NOCASE collation
>
>
>
> Can migration tool handle that for most databases or it should be better done
> in application code?
>
> Thanks and best regards,
>
> --
> 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/-/JvxaQU8BifsJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Is it possible for alembic or other migration tool to handle case sensitivity or insensitivity across database engines
I tested my code, and got below:
Traceback (most recent call last):
File "/usr/local/bin/alembic", line 9, in
load_entry_point('alembic==0.4.0', 'console_scripts', 'alembic')()
File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line
255, in main
CommandLine(prog=prog).main(argv=argv)
File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line
250, in main
self.run_cmd(cfg, options)
File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line
241, in run_cmd
**dict((k, getattr(options, k)) for k in kwarg)
File "/usr/local/lib/python2.7/dist-packages/alembic/command.py", line
97, in revision
script.run_env()
File "/usr/local/lib/python2.7/dist-packages/alembic/script.py", line
191, in run_env
util.load_python_file(self.dir, 'env.py')
File "/usr/local/lib/python2.7/dist-packages/alembic/util.py", line 185,
in load_python_file
module = imp.load_source(module_id, path, open(path, 'rb'))
File "alembic/env.py", line 18, in
from dbmodule import SAINT8
File "/home/bethesda/Documents/dbmodule/mytables.py", line 35, in
Column('data', character_type(32)),
File "/home/bethesda/Documents/dbmodule/mytables.py", line 21, in
character_type
VARCHAR(length, collation='utf8_general_ci'), 'mysql'
*TypeError: __init__() got an unexpected keyword argument 'collation'*
Did I miss importing a class related with 'collation'? I tried 'from
sqlalchemy.types import *', and some others, but didn't get it resolved,
what is wrong?
Thanks,
jp
On Thursday, December 6, 2012 4:10:01 PM UTC-5, junepeach wrote:
>
> Thanks, this is nice, I will use it in my code.
>
> On Thursday, December 6, 2012 3:17:25 PM UTC-5, junepeach wrote:
>>
>>
>> For case insensitive columns:
>> MySQL - use utf8_general_ci
>> SQLite - use NOCASE collation
>>
>>
>>
>> Can migration tool handle that for most databases or it should be better
>> done in application code?
>>
>> Thanks and best regards,
>>
>
--
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/-/JvxaQU8BifsJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Is it possible for alembic or other migration tool to handle case sensitivity or insensitivity across database engines
Thanks, this is nice, I will use it in my code. On Thursday, December 6, 2012 3:17:25 PM UTC-5, junepeach wrote: > > > For case insensitive columns: > MySQL - use utf8_general_ci > SQLite - use NOCASE collation > > > > Can migration tool handle that for most databases or it should be better > done in application code? > > Thanks and best regards, > -- 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/-/88nSK18O518J. 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] Is it possible for alembic or other migration tool to handle case sensitivity or insensitivity across database engines
On Dec 6, 2012, at 3:17 PM, junepeach wrote:
>
> For case insensitive columns:
> MySQL - use utf8_general_ci
> SQLite - use NOCASE collation
>
>
>
> Can migration tool handle that for most databases or it should be better done
> in application code?
sure:
def character_type(length):
return VARCHAR(length).with_variant(
VARCHAR(length, collation='utf8_general_ci'), 'mysql'
).with_variant(
VARCHAR(length, collation='NOCASE'), 'sqlite'
)
Table("sometable", metadata,
Column("some_text", character_type(200))
)
http://docs.sqlalchemy.org/en/latest/core/types.html?highlight=with_variant#sqlalchemy.types.String
http://docs.sqlalchemy.org/en/latest/core/types.html?highlight=with_variant#sqlalchemy.types.TypeEngine.with_variant
>
> Thanks and best regards,
>
> --
> 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/-/cgxdiM7P4yMJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To 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] Is it possible for alembic or other migration tool to handle case sensitivity or insensitivity across database engines
For case insensitive columns: MySQL - use utf8_general_ci SQLite - use NOCASE collation Can migration tool handle that for most databases or it should be better done in application code? Thanks and best regards, -- 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/-/cgxdiM7P4yMJ. 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] Using Guid as Primary Key
I use guid primary keys fairly heavily. There is a performance impact due to the size of the key; you just can't fit as much of the index in memory as you otherwise could. On the flip side, when you have multiple database shards, you never need to worry about keys being duplicated, so you can move data between shards at will. (Of course you can solve that problem with integer keys; it is just more complex, and hence more error prone.) In addition to their size (bloating both the rows and the indexes, causing fewer rows to fit in memory), there are a few more caveats: Guids are not necessarily random (it depends on how you generate the guid). Like Michael said, that can lead to worst-case performance, since hashing them naively can lead to many collisions. Either use mathematically random guids, or double-check your hash table performance, or both. Guids are much more random than auto-incrementing integers. Since databases tend to cluster on disk around their primary key, multiple inserts will spread themselves across the disk. That can be very good for performance, but it is bad if you tend to query for rows in order. With integer keys, when you page in one row, if you query for the next primary key, that data is likely to be sitting resident in database memory, or at least on the disk cache. I used guid primary keys in Microsoft's SQLServer (around 4-5 years ago), on a table that had high numbers of inserts and deletes. The database had trouble maintaining proper table statistics, which led to the query optimizer making some poor life choices, resulting in very poor performance. I think that is specific to the database and our poor high-churn architecture, but it is worth noting. I have not have that problem in MySQL or PostgreSQL. None of these problems are insurmountable, of course. It just pays to stay on your toes and test your performance regularly. On Tue, Dec 4, 2012 at 2:28 PM, Michael Bayer wrote: > > On Dec 4, 2012, at 4:46 PM, Wolfgang Keller wrote: > > Can I use the Guid as primary key? I am newbie to sql and mysql > management. > >>> > >>> Using such auto-generated surrogate keys is always a really bad idea > >>> and the straightest and shortest way to data inconsistency hell > >>> (especially through duplicates). > >> > >> you've seen two guids generate as duplicates ? > > > > You missed my point - *exactly*. ;-) > > > > The very point of "natural" (as opposed to "surrogate") keys is that any > > real world "thing" (such as e.g. a person) represented by a database > > entity must have exactly one *and only one* matching record in the > > corresponding database table. > > > > Auto-generated "GUID"s are exactly what does *not* prevent the > > generation of several records for one single real-world "thing". By > > simple multiple manual entry of the same data several times due to > > operator error. Identifying a "thing" by a natural identifier, i.e. one > > that is actually unique for each and every "thing" *in the real-world* > > does prevent such inconsistency. > > > > I once came across an article describing one of the probably worst > > cases of database havoc caused by surrogate keys in computer history - > > the EMR system of the US veterans health administration. According to > > this article, they face the problem today that each and every individual > > veteran has an arbitrary, unknown number of corresponding database > > records - and there's no way to reconcile this mess due to the sheer > > volume and various technical issues such as typos etc. > > oh, well this is the "natural keys are better" argument. Unfortunately, > surrogate integer PKs are necessary in most real-world installations as > they provide far better performance than a typical natural key, when you > consider that they are also copied out to all the referencing FK columns > and are present in many indexes. Even though SQLA totally supports > natural keys as well as it can, I don't generally use natural PKs in my > real-world contracts. The DBAs I work with won't allow them. > > I'd say any system that actually has any human being *manually entering* a > surrogate PK value as part of the application's user interface is > intrinsically broken. Especially a GUID value. > > I will often add a UNIQUE constraint to the "natural" PK of the table, > next to the surrogate PK, to avoid the data duplication issues you refer > to.I've used surrogate PKs for decades without data duplication issues. > > That said, I did have a bad experience with GUIDs, which were in fact > "natural" guids that were generated deterministically from other elements > of the data - which was that the performance suffered terribly, > specifically because of all that heavy GUID data copied out to all the > referencing FKs and the associated indexes. > > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@g
Re: [sqlalchemy] Automatic data partitioning using a custom Session class
On Dec 6, 2012, at 11:59 AM, Paul Johnston wrote:
> 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.
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
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] contains_eager + aliased
On Dec 6, 2012, at 11:14 AM, Julien Cigar wrote: > > Another thing I wondered is if there is a shortcut (not joinedload()) for: > > Bar.query.join(Foo).options(orm.contains_eager(Bar.foo)).filter(Foo.id==1) > > I thought something like: > > Bar.query.join(Foo, prop='foo').filter(Foo.id==1) > > (to be able to specify the property directly in the join) well yeah if we started adding all kinds of alternate shortcut-syntaxes for perceived use cases, then the whole API starts becoming a lot more crowded and hard to learn. Its already huge, and the direction is always one of having an open ended set of constructs that work as consistently as possible. So here, you can certainly make a subclass of Query that has the convenience methods you're looking for, like a "join_and_load" type of method. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] 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.
Re: [sqlalchemy] contains_eager + aliased
On 12/06/2012 16:02, Julien Cigar wrote: On 12/06/2012 15:45, Michael Bayer wrote: On Dec 6, 2012, at 7:47 AM, Julien Cigar wrote: Hello, Any idea why the following doesn't work ? : Topic2= orm.aliased(Topic) q= Occurrence.query.\ join(Occurrence.datasheet).\ options(orm.contains_eager(Occurrence.datasheet)).\ join(Topic2).\ options(orm.contains_eager(DataSheet.topic,alias=Topic2)).\ outerjoin(Occurrence.species).\ options(orm.contains_eager(Occurrence.species)) I get a: ArgumentError: Can't find property 'topic' on any entity specified in this Query. Note the full path from root (Mapper|Occurrence|occurrences) to target entity must be specified. and I don't understand why .. so "paths" work like this: query(Occurence).join(Occurrence.datasheet).join(Datasheet.topic).join(Occurrence.species) here are the major "paths" we've cerated: (Occurence, Occurrence.datasheet, ) (Occurence, Occurrence.datasheet, Datasheet, Datasheet.topic) (Occurence, Occurrence.species) There's actually "paths" for every attribute on all three of those classes worked up too, but for the purpose of relationship loading, what you send as an "eager loading" option has to match one of those paths. so: contains_eager(Occurrence.datasheet, Datasheet.topic, alias=Topic2) Thank you, it looks like I misunderstood how those "paths" worked Another thing I wondered is if there is a shortcut (not joinedload()) for: Bar.query.join(Foo).options(orm.contains_eager(Bar.foo)).filter(Foo.id==1) I thought something like: Bar.query.join(Foo, prop='foo').filter(Foo.id==1) (to be able to specify the property directly in the join) Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. <>
Re: [sqlalchemy] contains_eager + aliased
On 12/06/2012 15:45, Michael Bayer wrote: On Dec 6, 2012, at 7:47 AM, Julien Cigar wrote: Hello, Any idea why the following doesn't work ? : Topic2= orm.aliased(Topic) q= Occurrence.query.\ join(Occurrence.datasheet).\ options(orm.contains_eager(Occurrence.datasheet)).\ join(Topic2).\ options(orm.contains_eager(DataSheet.topic,alias=Topic2)).\ outerjoin(Occurrence.species).\ options(orm.contains_eager(Occurrence.species)) I get a: ArgumentError: Can't find property 'topic' on any entity specified in this Query. Note the full path from root (Mapper|Occurrence|occurrences) to target entity must be specified. and I don't understand why .. so "paths" work like this: query(Occurence).join(Occurrence.datasheet).join(Datasheet.topic).join(Occurrence.species) here are the major "paths" we've cerated: (Occurence, Occurrence.datasheet, ) (Occurence, Occurrence.datasheet, Datasheet, Datasheet.topic) (Occurence, Occurrence.species) There's actually "paths" for every attribute on all three of those classes worked up too, but for the purpose of relationship loading, what you send as an "eager loading" option has to match one of those paths. so: contains_eager(Occurrence.datasheet, Datasheet.topic, alias=Topic2) Thank you, it looks like I misunderstood how those "paths" worked Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. <>
Re: [sqlalchemy] contains_eager + aliased
In fact the problem is more that, with the following: http://pastie.org/5489005 I don't know how to tell SQLAlchemy that it should join the DataSheet relation only once for the query and not once per join(). One option could be to add a .options(orm.noload(MyObject.datasheet)) for every mapped object but it seems a bit lame. I thought doing somehting like: Foo = orm.aliased(DataSheet) q = Occurrence.query.\ join(Foo).\ join(DataSheet.topic).\ outerjoin(Occurrence.species).\ outerjoin(Occurrence.site).\ options( orm.contains_eager(Occurrence.datasheet, alias=Foo), orm.contains_eager(Species.datasheet, alias=Foo), orm.contains_eager(Site.datasheet, alias=Foo), orm.contains_eager(Occurrence.species, Occurrence.site) ) but it doesn't seems to work ... Any idea ? :) Thank you, Julien On 12/06/2012 13:47, Julien Cigar wrote: Hello, Any idea why the following doesn't work ? : Topic2= orm.aliased(Topic) q= Occurrence.query.\ join(Occurrence.datasheet).\ options(orm.contains_eager(Occurrence.datasheet)).\ join(Topic2).\ options(orm.contains_eager(DataSheet.topic,alias=Topic2)).\ outerjoin(Occurrence.species).\ options(orm.contains_eager(Occurrence.species)) I get a: ArgumentError: Can't find property 'topic' on any entity specified in this Query. Note the full path from root (Mapper|Occurrence|occurrences) to target entity must be specified. and I don't understand why .. Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. <>
Re: [sqlalchemy] contains_eager + aliased
On Dec 6, 2012, at 7:47 AM, Julien Cigar wrote: > Hello, > > Any idea why the following doesn't work ? : > > Topic2= orm.aliased(Topic) > q= Occurrence.query.\ >join(Occurrence.datasheet).\ >options(orm.contains_eager(Occurrence.datasheet)).\ >join(Topic2).\ >options(orm.contains_eager(DataSheet.topic,alias=Topic2)).\ >outerjoin(Occurrence.species).\ >options(orm.contains_eager(Occurrence.species)) > > I get a: > > ArgumentError: Can't find property 'topic' on any entity specified in this > Query. Note the full path from root (Mapper|Occurrence|occurrences) to > target entity must be specified. > > > and I don't understand why .. so "paths" work like this: query(Occurence).join(Occurrence.datasheet).join(Datasheet.topic).join(Occurrence.species) here are the major "paths" we've cerated: (Occurence, Occurrence.datasheet, ) (Occurence, Occurrence.datasheet, Datasheet, Datasheet.topic) (Occurence, Occurrence.species) There's actually "paths" for every attribute on all three of those classes worked up too, but for the purpose of relationship loading, what you send as an "eager loading" option has to match one of those paths. so: contains_eager(Occurrence.datasheet, Datasheet.topic, alias=Topic2) > > Thanks, > Julien > > -- > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Bug Report: A regression in the MSSQL Dialect in 0.8.x
On Dec 5, 2012, at 6:50 PM, Derek Harland wrote:
>
> I wonder if a solution here is to somehow allow the "schema" argument to also
> be given as a tuple. eg
>
> schema="x.y" would generate a DDL path as x.y
> schema=["x.y"] would generate a DDL path as [x.y]
> schema=["a.b", "x.y"] would generate a DDL path as [a.b].[x.y]
>
> Thus eg MSIdentifierPreparer.quote_schema could be something like:
>
>def quote_schema(self, schema, force=True):
>"""Prepare a quoted table and schema name."""
>if not isinstance(schema, (list, tuple)):
>schema = schema.split('.')
>result = '.'.join([self.quote(x, force) for x in schema])
>return result
>
> then _owner_plus_db could special case on whether schema is a list.
"schema" is a pretty major argument that I'd rather not dilute its datatype, it
seems more intuitive anyway that we'd just allow SQL-server style quoting to be
significant:
"C", schema="A.B"
"C", schema="[A.B]"
"C", schema="[A.B].[C.D]"
that way...it works exactly like SQL Server does.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] tsvector
cool, thanks a lot for the info, I'll check that.
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es
necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
From: mike...@zzzcomputing.com
Subject: Re: [sqlalchemy] tsvector
Date: Wed, 5 Dec 2012 18:31:37 -0500
To: sqlalchemy@googlegroups.com
sanjay's approach there is hardcoded SQL which isn't necessary with SQLAlchemy
(though always supported as a quick approach to something).
tsvector here as a type can be implemented with UserDefinedType:
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#sqlalchemy.types.UserDefinedType
and the operators I see at
http://www.postgresql.org/docs/8.3/static/functions-textsearch.html can be
implemented using op():
column.op('@@@')(func.to_tsvector("some value"))
that's a little verbose, so those operations can be implemented as operators
that are part of the tsvector type, using the techniques described at
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#redefining-and-creating-new-operators
.
All of this can and should be part of SQLAlchemy at some point, with external
types like these we usually rely on volunteers, such as the recent HSTORE
contribution. The HSTORE module here is a great place to see an example of a
richly functional type:
http://hg.sqlalchemy.org/sqlalchemy/file/4abc02fbc36b/lib/sqlalchemy/dialects/postgresql/hstore.py
On Dec 5, 2012, at 5:37 PM, Gery . wrote:I was searching in google about
tsvector (postgresql) implemented in sqlalchemy or geoalchemy but seems to me
that it's not implemented yet. After searching for it in [1], it didn't
matchanything. Sanjay, however, gives a way to do this [2] but seems confusing
to me, is it possible to use this tsvector as simple as someone using types
such as integer, small integer, etc etc? if so, it'd be cool to know how.
Thanks,
[1]
http://docs.sqlalchemy.org/en/rel_0_8/search.html?q=tsvector&check_keywords=yes&area=default
[2] https://groups.google.com/forum/?fromgroups=#!topic/sqlalchemy/Dns28KksD-4
__
Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es
necesario.
Think green - keep it on the screen. Do NOT print if it is NOT necessary.
Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie
NICHT, wenn es NICHT notwendig ist.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] contains_eager + aliased
Hello, Any idea why the following doesn't work ? : Topic2= orm.aliased(Topic) q= Occurrence.query.\ join(Occurrence.datasheet).\ options(orm.contains_eager(Occurrence.datasheet)).\ join(Topic2).\ options(orm.contains_eager(DataSheet.topic,alias=Topic2)).\ outerjoin(Occurrence.species).\ options(orm.contains_eager(Occurrence.species)) I get a: ArgumentError: Can't find property 'topic' on any entity specified in this Query. Note the full path from root (Mapper|Occurrence|occurrences) to target entity must be specified. and I don't understand why .. Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. <>
Re: [sqlalchemy] Re: Best practice for faves/likes counting?
On Wed, 2012-12-05 at 17:16:01 -0800, Hong Minhee wrote: > But for work in works: work.like_query.count() causes inefficient 1+N > queries even if we have the right indices for it. Of course I could query > like session.query(Work, count()).join(Work.like_set).group_by(Work) but > it’s somewhat complicated to read and write for me (is it only me?). I > want to reject such uses in ORM… > > Anyway I use PostgreSQL. But using JOINs is how relational DBs are meant to be used. I had similar mindset like yours not that long ago. I didn't really understand how the database was working, it was kind of blackbox to me. What helped me immensely was writing pure SQL queries for Postgres in one of my jobs. Not only that, but also reading EXPLAIN query plans, seeing how and when the indices were used, etc. There are still many things to learn, but I have much better understanding of relational DBs now. This also helped me to see why SQLAlchemy is as it is and appreciate it even more. Its exposure of SQL is a good thing, contrary to other ORMs, which try to hide it as much as possible, limiting their flexibility and power in result. So I strongly encourage you to learn more about database you are using and SQL in general. This will help you to write more efficient queries and better leverage the power of your RDBMS. In case of your JOIN query, even if you have the right indices, it could still be slow, if it's not selective enough (it selects big part of the table). In that case index simply won't be used by the query planner. -- Audrius Kažukauskas http://neutrino.lt/ pgpEoDeVdzBKx.pgp Description: PGP signature
