[sqlalchemy] Re: keyword-only arguments in entity constructor confuse mapper
The patch worked on 0.7.0 and i don't get warning from Python (3.2), so it seem to have addressed the issue correctly. (Well i don't get same error at least, once i finish with unittests i can either confirm or deny lack of side effects) Thanks! On Jul 29, 6:38 pm, Michael Bayer wrote: > On Jul 29, 2011, at 3:45 AM, Phazorx wrote: > > > Most of my entities accept various combinations of parameters and it > > makes sense for my to use keyword-only pattern of constructors: > > > class Person(Root_Entity): > > def __init__(self, session, *, first_name, last_name): > > > class Address(Root_Entity): > > def __init__(self, session, *, street, building, unit=None, > > zip=None, office=None, city="My City", region=None, country="My > > Country"): > > > however, in this case i get following from python while SQLA figures > > out relationships: > > ValueError: Function has keyword-only arguments or annotations, > > use getfullargspec() API which can support them > > > full traceback:http://dpaste.com/hold/581307/ > > > Everything is peachy as soon as i get rid of "*," in constructor > > obviously... but what can i do to preserve such constructors and still > > be able to use SQLA? > > So you're using some Python syntax I've never seen before, let's check > (checking...OK its new in Python 3, does not appear to be in the language > tutorial either, just in the PEP) and in the first case we'd have to use > getfullargspec() in that case when Py3 is in use, however we'd also have to > interpret the extended tuple returned by getfullargspec() correctly when we > establish instrumentation. > > If the following patch works, then we could commit once a test is written, > though looking at it I'm not optimistic that some significant extra work > might be needed to do this correctly. Until then this is an unsupported use > case. Ticket #2237 is addedhttp://www.sqlalchemy.org/trac/ticket/2237. > > diff -r 87a1dc569235 lib/sqlalchemy/util/compat.py > --- a/lib/sqlalchemy/util/compat.py Thu Jul 28 11:53:18 2011 -0400 > +++ b/lib/sqlalchemy/util/compat.py Fri Jul 29 10:35:23 2011 -0400 > @@ -90,6 +90,11 @@ > from urlparse import parse_qsl > > if py3k: > + from inspect import getfullargspec as inspect_getfullargspec > +else: > + from inspect import getargspec as inspect_getfullargspec > + > +if py3k: > # they're bringing it back in 3.2. brilliant ! > def callable(fn): > return hasattr(fn, '__call__') > diff -r 87a1dc569235 lib/sqlalchemy/util/langhelpers.py > --- a/lib/sqlalchemy/util/langhelpers.py Thu Jul 28 11:53:18 2011 -0400 > +++ b/lib/sqlalchemy/util/langhelpers.py Fri Jul 29 10:35:23 2011 -0400 > @@ -15,7 +15,7 @@ > import sys > import types > import warnings > -from compat import update_wrapper, set_types, threading > +from compat import update_wrapper, set_types, threading, > inspect_getfullargspec > from sqlalchemy import exc > > def _unique_symbols(used, *bases): > @@ -149,7 +149,7 @@ > 'apply_pos': '(self, a, b, c, **d)'} > > """ > - spec = callable(fn) and inspect.getargspec(fn) or fn > + spec = callable(fn) and inspect_getfullargspec(fn) or fn > args = inspect.formatargspec(*spec) > if spec[0]: > self_arg = spec[0][0] > > > > > -- > > 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 > > athttp://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] Need for ImplicitForeignKeyConstraint
So I had been working on this tiny project now and then. And here's the poc.
http://paste.pound-python.org/show/10578/
I think I'm somewhat misusing the _set_parent() here though.
On Sunday, July 24, 2011 06:52:45 PM Michael Bayer wrote:
> On Jul 24, 2011, at 8:39 AM, Fayaz Yusuf Khan wrote:
> > The problem with using different mixins is that you lose out on a lot of
> > code reusability. In my case, I have a 'user' column that appears in
> > almost all table declarations. To have a separate mixin class for each
> > joint-table inheritance would destroy the purpose of having a mixin
> > altogether.
>
> In your example you can simply use CMixin and TMixin separately instead of
> inheriting them from one another, then apply CMixin and TMixin directly to
> C individually.That makes more sense here since for every class X
> which you want to have "user", you'd apply CMixin explicitly. The more
> I look at this the more it seems completely correct to me. Mixins and
> declarative do a lot , and sticking to Python's regular rules for
> inheritance is what makes them great.
>
> > Perhaps, there should be a shorthand for implicitly creating columns
> > along with foreign key constraints?
> >
> > So something like
> >
> >ImplicitForeignKeyConstraint(
> >
> >['user', 'timestamp'],
> >['Timeline.user', 'Timeline.timestamp'], primary_key=True)
> >
> > should lead to the creation of
> >
> >Column('user', String, primary_key=True),
> >Column('timestamp',Integer, autoincrement=False, primary_key=True),
> >ForeignKeyConstraint(
> >
> >['user', 'timestamp'],
> >['Timeline.user', 'Timeline.timestamp'])
>
> Not something for core but certainly something you could provide yourself
> (use append_column()). SQLA's APIs try to remain explicit about
> things leaving "implicit helper" layers as an external task (hence
> relationship + ForeignKey, as opposed to the "all in one" demo I did at
> http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/ , etc)
--
Fayaz Yusuf Khan
Cloud developer and designer
Dexetra SS, Kochi, India
fayaz.yusuf.khan_AT_gmail_DOT_com
fayaz_AT_dexetra_DOT_com
+91-9746-830-823
signature.asc
Description: This is a digitally signed message part.
[sqlalchemy] data driven schema in sqlalchemy
Hi there, I have a data driven database schema that I am trying to implement in sqlalchemy. Here's how the tables look like: user user_id | | user_properties property_id | property_name | property_description user_properties_data user_id | property_id | property_value What I would like to do eventually is if I have u = User() , u. [some_propery_name] return the property_value if it exist for that user. Any suggestions on how to implement this? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] One to many, but only load one
Thanks,
Could you explain how to do contains_eager with an explicit query(). I tried
putting a query inside a call to contains_eager, but get an error:
ArgumentError: mapper option expects string key or list of attributes
Mark
On Aug 4, 2011, at 6:39 PM, Michael Bayer wrote:
> awkardly and inefficiently from a SQL perspective. contains_eager() with an
> explicit query() would produce better result
>
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> Base = declarative_base()
> import datetime
>
> class A(Base):
>__tablename__ = 'a'
>id = Column(Integer, primary_key=True)
>bs = relationship("B")
>
> class B(Base):
>__tablename__ = 'b'
>id = Column(Integer, primary_key=True)
>a_id = Column(Integer, ForeignKey('a.id'))
>date = Column(Date)
>
> A.latest_b = relationship(B,
>primaryjoin=and_(
>A.id==B.a_id,
>
> B.date==select([func.max(B.date)]).where(B.a_id==A.id).correlate(A.__table__)
>)
>)
>
> e = create_engine('sqlite://', echo=True)
> Base.metadata.create_all(e)
> s = Session(e)
>
> s.add_all([
>A(bs=[
>B(date=datetime.date(2011, 10, 5)),
>B(date=datetime.date(2011, 8, 4)),
>B(date=datetime.date(2011, 9, 17)),
>]),
>A(bs=[
>B(date=datetime.date(2011, 10, 5)),
>B(date=datetime.date(2011, 8, 4)),
>B(date=datetime.date(2011, 9, 17)),
>]),
> ])
> s.commit()
>
> for obj in s.query(A).options(joinedload(A.latest_b)):
>print obj.latest_b
>
>
>
> On Aug 4, 2011, at 5:55 PM, Mark Erbaugh wrote:
>
>> Table A has a one to many relationship with Table B. There may be zero or
>> more rows in B for each row in A.
>>
>> I would like to have a query that retrieves all the rows in table A joined
>> with the "first" related row in table B (if one exists). In this case, each
>> row in table B has a DATE field and I want to retrieve the row with the
>> latest date. Is this possible using joinedload?
>>
>> Thanks,
>> Mark
>>
>> --
>> 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.
Re: [sqlalchemy] One to many, but only load one
awkardly and inefficiently from a SQL perspective. contains_eager() with an
explicit query() would produce better result
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
import datetime
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship("B")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
date = Column(Date)
A.latest_b = relationship(B,
primaryjoin=and_(
A.id==B.a_id,
B.date==select([func.max(B.date)]).where(B.a_id==A.id).correlate(A.__table__)
)
)
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
A(bs=[
B(date=datetime.date(2011, 10, 5)),
B(date=datetime.date(2011, 8, 4)),
B(date=datetime.date(2011, 9, 17)),
]),
A(bs=[
B(date=datetime.date(2011, 10, 5)),
B(date=datetime.date(2011, 8, 4)),
B(date=datetime.date(2011, 9, 17)),
]),
])
s.commit()
for obj in s.query(A).options(joinedload(A.latest_b)):
print obj.latest_b
On Aug 4, 2011, at 5:55 PM, Mark Erbaugh wrote:
> Table A has a one to many relationship with Table B. There may be zero or
> more rows in B for each row in A.
>
> I would like to have a query that retrieves all the rows in table A joined
> with the "first" related row in table B (if one exists). In this case, each
> row in table B has a DATE field and I want to retrieve the row with the
> latest date. Is this possible using joinedload?
>
> Thanks,
> Mark
>
> --
> 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] One to many, but only load one
Table A has a one to many relationship with Table B. There may be zero or more rows in B for each row in A. I would like to have a query that retrieves all the rows in table A joined with the "first" related row in table B (if one exists). In this case, each row in table B has a DATE field and I want to retrieve the row with the latest date. Is this possible using joinedload? Thanks, Mark -- 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: Cascade Deletes
Hey,
Tried adding cascade to Rating's backref call like so:
subrating = relationship("SubRating", backref=backref("rating",
cascade="all, delete-orphan"
uselist=False))
This unfortunately doesn't work - when I delete a Rating, the
according Subratings are NOT removed.
What am I doing wrong? (Testing with SQLite)
On Jul 31, 9:01 am, Stefano Fontanelli
wrote:
> Il 30/07/11 23.24, Aviv Giladi ha scritto:
>
> > Sorry, but I am really confused.
> > Are you guys saying that on SQLite for example, cascade deletes don't
> > work at all? Or do they work, but are less efficient?
>
> ONUPDATE/ONDELETE cascade -> SQL expression: on SQLite and MySQL MyISAM
> doesn't work at all.
>
> Michael told you that you can reach the same result using the SQLA's
> relationship option:
>
> cascade='all, delete-orphan'
>
> This approach is less efficient because delete actions are performed by
> mapper at the application layer instead of the database: relationship
> collection objects must be loaded into memory then deleted.
>
> Regards,
> Stefano.
>
> --
> Ing. Stefano Fontanelli
> Asidev S.r.l.
> Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
> Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
> E-mail: s.fontane...@asidev.com Web:www.asidev.com
> Skype: stefanofontanelli
--
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] Populate sample data
On Aug 4, 2011, at 2:48 PM, Mark Erbaugh wrote: > Thanks again for the help. I decided to time the various approaches. My > original approach took 4:23 (minutes: seconds). Note: all my times included > data generation and insertion into a SQLite on-disk database. > > > > This took 3:36 17%... > This took 2:28 43%... > I did this with one session.execute for each table, rather than chunks. It > took 0:46 82% ! fairly nailed the last one at least >> The range of speedups here would be between 30% and 80%, with direct usage >> of connection/session .execute() with Table metadata giving you the 80%. -- 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] Populate sample data
On Aug 4, 2011, at 9:22 AM, Michael Bayer wrote: > > On Aug 3, 2011, at 8:38 PM, Mark Erbaugh wrote: > >> I'm using SA (with SQLite) with a schema like: >> >> A -< B -< C -< D >> >> where -< means that the tables have a one to many relationship >> >> I'm populating a sample data set where there are 25 rows in A, 25 rows in B >> for each row in A, 25 rows in C for each row in B and 25 rows in D for each >> row in C. This results in about 390k rows in D. The database itself is >> only about 12 MB, but it takes a long time (several minutes) to write the >> data to the file. >> >> I'm taking the approach of appending items to the table's relationship >> column. >> >> for i in range(25): >> x = A() >> session.add(A) >> for j in range(25): >> y = B() >> x.b.append(y) >> for k in range(25): >> z = C() >> y.c.append(z) >> for l in range(25): >> xx = D() >> z.d.append(xx) >> session.flush() Thanks again for the help. I decided to time the various approaches. My original approach took 4:23 (minutes: seconds). Note: all my times included data generation and insertion into a SQLite on-disk database. > The biggest speed variable in a mass INSERT operation is whether or not > individual cursor.execute() calls, or a small handful of cursor.executemany() > calls each with thousands of rows, are used. > > With the ORM, a mass executemany() is used for INSERT in the case that > primary key values are already present in the given objects. If not, the > ORM has to INSERT each A() row one at a time, get the new primary key value, > and then later populate 25*25 B() object's foreign key with the A.id value; > then this goes down to the B()->C() area, etc. > > So if A(), B(), C(), D() can be created with "id=5", "id=6", etc., assuming > "id" is the primary key, the ORM should be able to bunch lots of rows > together into one cursor.executemany() call and you'll see an immediate, > dramatic speedup. This took 3:36 > > The next level would be if you populated the "a_id", "b_id", > foreign-key-to-parent columns directly instead of using append().If you > did a profile on your script you'd see lots of time taken in many places, but > all those append() operations would be one of them, as well as lots of event > firing and bookkeeping that SQLAlchemy has to do when they occur, both at > append() time as well as within the flush() (populating the primary key > values to foreign key attributes). This took 2:28 > By far the fastest way to do this would be to use > session.execute(a_table.insert(), []), > session.execute(b_table.insert(), []), etc. That is, assemble the > whole set of A, B, C, D, directly in terms of the mapped table, or better > yet do it in chunks, perhaps drill down through B, C, D for a single A then > insert everything, etc.That way you optimize how these rows are > constructed in Python exactly to the pattern that corresponds directly to the > database structure, instead of having SQLAlchemy decode the database > structure from an object hierarchy. An insertmany is documented at > http://www.sqlalchemy.org/docs/core/tutorial.html#executing-multiple-statements. >The ORM Session has an execute() method just like Connection does. I did this with one session.execute for each table, rather than chunks. It took 0:46 > The range of speedups here would be between 30% and 80%, with direct usage of > connection/session .execute() with Table metadata giving you the 80%. Mark -- 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] Arrays with altered elements
I suppose then the simplest solution is to make a function in the database
that will execute a function on each element of an array and use:
.having(func.array_agg(metaphones.columns.mphone).op('<@')(func.metaphone_array(terms)))
This seems to work fine.
Thanks,
Jason
--
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/-/5Bh6iSulbSgJ.
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] Populate sample data
On Aug 4, 2011, at 1:20 PM, Mark Erbaugh wrote: > > > Originally, I thought transaction was from the standard Python library, but > upon research, it looks like it's from the transaction package that is part > of Zope. It's included in the Pyramid installation. Pyramid installs the zope transaction manager by default for SQLAlchemy usage ? Why do they keep saying Pyramid isn't zopey ? The TM is the zopiest zope there is. -- 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: Help using SqlSoup with database views
On Aug 4, 2011, at 1:08 PM, Randy Syring wrote:
> FWIW, I tried the map_to() method but still received the PK error.
> The following method, however, worked fine:
>
> ss = SqlSoup(db.engine)
> meta = ss._metadata
> tbl_vrmf = sa.Table("vRMF", meta, autoload=True)
> vrmf_pks = [tbl_vrmf.c.dateId, tbl_vrmf.c.ident, tbl_vrmf.c.mnum]
> vrmf = ss.map(tbl_vrmf, primary_key=vrmf_pks)
yeah SqlSoup is really kind of up in the attic for me, if you'd like to propose
some patches to make it work more nicely for things like that we are open.
>
> On Jun 10, 8:18 pm, Michael Bayer wrote:
>> On Jun 9, 2011, at 12:41 AM, Reece Hart wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> I'd like to use SqlSoup with an existing database that contains views.
>>> Accessing a table goes swimmingly, but accessing a view results in
>>> "PKNotFoundError: table '[viewname]' does not have a primary key
>>> defined..."
>>
>>> Do I correctly infer that SqlSoup does not work with database views (by
>>> default, at least)? I've been unable to find anything directly relevant
>>> on Google, SO, or the SqlAlchemy mailing list. If you were faced with
>>> this, how would you proceed if you wanted to access non-updatable views?
>>> I'm new to SQLAlchemy and SQLSoup.
>>
>>> Here's a specific example:
>>
>>>from sqlalchemy.ext.sqlsoup import SqlSoup
>>>u = SqlSoup('postgresql+psycopg2://pub...@unison-db.org:5432/unison')
>>>seq = u.pseq.filter(u.pseq.pseq_id==76).all() # okay
>>>aliases = u.pseqalias.filter(u.pseqalias.pseq_id==76).all()
>>>PKNotFoundError: table 'pseqalias' does not have a primary key
>>> defined...
>>
>> You would need to pass the columns to be considered part of the primary key
>> to the underlying mapper, using sqlsoup.map_to(), but unfortunately there is
>> not a simple interface for that at the moment, since you need the Table
>> object as well to get at the columns. So until this interface could be
>> improved, for now it would look like:
>>
>> metadata = u._metadata
>> t = Table("pseqaliases", metadata, autoload=True)
>>
>> u.map_to("pseqaliases", selectable=t, mapper_args={"primary_key":[t.c.col1,
>> t.c.col2]})
>>
>> This is just the "primary_key" argument to mapper, there are some examples
>> athttp://www.sqlalchemy.org/docs/orm/mapper_config.htmlnear the top.
>
> --
> 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] Populate sample data
On Aug 4, 2011, at 9:22 AM, Michael Bayer wrote: > The range of speedups here would be between 30% and 80%, with direct usage of > connection/session .execute() with Table metadata giving you the 80%. Thanks. I'll look into your suggestions > > I'm not sure what "transaction" is in transaction.begin() , if you're using a > regular SQLAlchemy Session in it is always "in a transaction" in that it uses > a single connection until rollback() or commit() is called. Originally, I thought transaction was from the standard Python library, but upon research, it looks like it's from the transaction package that is part of Zope. It's included in the Pyramid installation. Mark -- 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: Help using SqlSoup with database views
FWIW, I tried the map_to() method but still received the PK error.
The following method, however, worked fine:
ss = SqlSoup(db.engine)
meta = ss._metadata
tbl_vrmf = sa.Table("vRMF", meta, autoload=True)
vrmf_pks = [tbl_vrmf.c.dateId, tbl_vrmf.c.ident, tbl_vrmf.c.mnum]
vrmf = ss.map(tbl_vrmf, primary_key=vrmf_pks)
On Jun 10, 8:18 pm, Michael Bayer wrote:
> On Jun 9, 2011, at 12:41 AM, Reece Hart wrote:
>
>
>
>
>
>
>
>
>
> > I'd like to use SqlSoup with an existing database that contains views.
> > Accessing a table goes swimmingly, but accessing a view results in
> > "PKNotFoundError: table '[viewname]' does not have a primary key
> > defined..."
>
> > Do I correctly infer that SqlSoup does not work with database views (by
> > default, at least)? I've been unable to find anything directly relevant
> > on Google, SO, or the SqlAlchemy mailing list. If you were faced with
> > this, how would you proceed if you wanted to access non-updatable views?
> > I'm new to SQLAlchemy and SQLSoup.
>
> > Here's a specific example:
>
> > from sqlalchemy.ext.sqlsoup import SqlSoup
> > u = SqlSoup('postgresql+psycopg2://pub...@unison-db.org:5432/unison')
> > seq = u.pseq.filter(u.pseq.pseq_id==76).all() # okay
> > aliases = u.pseqalias.filter(u.pseqalias.pseq_id==76).all()
> > PKNotFoundError: table 'pseqalias' does not have a primary key
> > defined...
>
> You would need to pass the columns to be considered part of the primary key
> to the underlying mapper, using sqlsoup.map_to(), but unfortunately there is
> not a simple interface for that at the moment, since you need the Table
> object as well to get at the columns. So until this interface could be
> improved, for now it would look like:
>
> metadata = u._metadata
> t = Table("pseqaliases", metadata, autoload=True)
>
> u.map_to("pseqaliases", selectable=t, mapper_args={"primary_key":[t.c.col1,
> t.c.col2]})
>
> This is just the "primary_key" argument to mapper, there are some examples
> athttp://www.sqlalchemy.org/docs/orm/mapper_config.htmlnear the top.
--
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] Arrays with altered elements
PG ARRAY comparisons and such aren't automatically supported right now. Such
an expression needs to be coerced into a clause element of some kind. Perhaps
a ClauseList:
from sqlalchemy.sql.expression import ClauseList
terms = ClauseList(*terms)
which would produce a comma separated display.
There is also tuple:
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=tuple#sqlalchemy.sql.expression.tuple_
but that might not be exactly right here (sorry I haven't worked with PG
arrays, I don't know the specifics offhand).
If more syntactical coercion is needed I'd build a construct using @compiles:
http://www.sqlalchemy.org/docs/core/compiler.html
On Aug 4, 2011, at 12:15 PM, Jason wrote:
> I would like to use an array comparison in a query, but with each array
> element being the result of a function. I do this by making the array with:
> terms = [func.dmetaphone(t) for t in terms.split()]
>
> When I use this array in a comparison I get an error "can't adapt type
> 'Function'" because it is passing [ 0x1057b8310; dmetaphone>] as the array.
>
> My full query looks like:
>
> terms = [func.dmetaphone(t) for t in terms.split()]
> metaphones =
> Session.query(func.dmetaphone(func.unnest(func.string_to_array(Customer.name,
> ' '))).label('mphone'), Customer.id).subquery()
> customers = Session.query(metaphones.columns.id) \
>.group_by(metaphones.columns.id) \
>
> .having(func.array_agg(metaphones.columns.mphone).op('<@')(terms))
>
> From all the documentation I have read it this should work, but SQLAlchemy is
> not evaluating the func.dmetaphone call within the array. Is there a way to
> force it to do that?
>
>
> Thanks,
>
> Jason
>
>
> --
> 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/-/Rv742SjyArEJ.
> 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] Arrays with altered elements
I would like to use an array comparison in a query, but with each array
element being the result of a function. I do this by making the array with:
terms = [func.dmetaphone(t) for t in terms.split()]
When I use this array in a comparison I get an error "can't adapt type
'Function'" because it is passing [] as the array.
My full query looks like:
terms = [func.dmetaphone(t) for t in terms.split()]
metaphones =
Session.query(func.dmetaphone(func.unnest(func.string_to_array(Customer.name,
' '))).label('mphone'), Customer.id).subquery()
customers = Session.query(metaphones.columns.id) \
.group_by(metaphones.columns.id) \
.having(func.array_agg(metaphones.columns.mphone).op('<@')(terms))
>From all the documentation I have read it this should work, but SQLAlchemy
is not evaluating the func.dmetaphone call within the array. Is there a way
to force it to do that?
Thanks,
Jason
--
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/-/Rv742SjyArEJ.
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] How to optimize the max group wise query
Dear all, I have the following query that is made of 3 queries on only ONE TABLE: 1. Step simple normal query somequery=session.query(tab.columns['name'],tab.columns['id']).filter(tab.columns['value']=="6") 2. Step: finding the max serial number for each serie maxquery=session.query(func.max(tab.columns['serial_number']),datab.columns['serie']).group_by(datab.columns['serie']) 3. Step: find name and id of each row satisfying query from Step1 having maximal serial number in a serie finalquery=somequery.filter(tuple_(tab.columns['serial_number'],datab.columns['serie']).in_(fidq)) Is there any more efficient way to do this? I am especially not thrilled with the step 3 (creating tuples and then searching equivalents from the results of the query 2). I think it is very cost- intensive. Remark: All rows with the same name and id also have the same serie attribute. Thanks -- 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] Populate sample data
On Aug 3, 2011, at 8:38 PM, Mark Erbaugh wrote: > I'm using SA (with SQLite) with a schema like: > > A -< B -< C -< D > > where -< means that the tables have a one to many relationship > > I'm populating a sample data set where there are 25 rows in A, 25 rows in B > for each row in A, 25 rows in C for each row in B and 25 rows in D for each > row in C. This results in about 390k rows in D. The database itself is only > about 12 MB, but it takes a long time (several minutes) to write the data to > the file. > > I'm taking the approach of appending items to the table's relationship column. > > for i in range(25): > x = A() > session.add(A) > for j in range(25): > y = B() > x.b.append(y) > for k in range(25): > z = C() > y.c.append(z) > for l in range(25): > xx = D() > z.d.append(xx) > session.flush() The biggest speed variable in a mass INSERT operation is whether or not individual cursor.execute() calls, or a small handful of cursor.executemany() calls each with thousands of rows, are used. With the ORM, a mass executemany() is used for INSERT in the case that primary key values are already present in the given objects. If not, the ORM has to INSERT each A() row one at a time, get the new primary key value, and then later populate 25*25 B() object's foreign key with the A.id value; then this goes down to the B()->C() area, etc. So if A(), B(), C(), D() can be created with "id=5", "id=6", etc., assuming "id" is the primary key, the ORM should be able to bunch lots of rows together into one cursor.executemany() call and you'll see an immediate, dramatic speedup. The next level would be if you populated the "a_id", "b_id", foreign-key-to-parent columns directly instead of using append().If you did a profile on your script you'd see lots of time taken in many places, but all those append() operations would be one of them, as well as lots of event firing and bookkeeping that SQLAlchemy has to do when they occur, both at append() time as well as within the flush() (populating the primary key values to foreign key attributes). By far the fastest way to do this would be to use session.execute(a_table.insert(), []), session.execute(b_table.insert(), []), etc. That is, assemble the whole set of A, B, C, D, directly in terms of the mapped table, or better yet do it in chunks, perhaps drill down through B, C, D for a single A then insert everything, etc.That way you optimize how these rows are constructed in Python exactly to the pattern that corresponds directly to the database structure, instead of having SQLAlchemy decode the database structure from an object hierarchy. An insertmany is documented at http://www.sqlalchemy.org/docs/core/tutorial.html#executing-multiple-statements. The ORM Session has an execute() method just like Connection does. The range of speedups here would be between 30% and 80%, with direct usage of connection/session .execute() with Table metadata giving you the 80%. I'm not sure what "transaction" is in transaction.begin() , if you're using a regular SQLAlchemy Session in it is always "in a transaction" in that it uses a single connection until rollback() or commit() is called. -- 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] bidirectional attribute_mapped_collection not working (with example)
Hi Michael,
Thank you very much for your response!
I'll try to code some workaround, and post it here if it works.
Thank you again!
2011/8/3 Michael Bayer
>
> On Aug 3, 2011, at 12:32 PM, Pau Tallada wrote:
>
> Hi!
>
> I have a model with a many-to-many relation, similar to the
> Broker/Holding/Stocks example in the docs. I've set up relationships between
> both sides using the association object pattern. Finally, I've set up the
> collection class of those relationships to an
> attribute_mapped_collection. It is expected that when an instance of Holding
> is created, it should appear on BOTH attribute_mapped_collections, but the
> KEY is only present on one of them.
>
> Following there is a small adaptation of the Broker/Stocks/Holding model to
> explain the problem.
>
> from sqlalchemy import Column, ForeignKey, MetaData, Table
> from sqlalchemy import Integer, String, Numeric
> from sqlalchemy.orm import mapper, relationship
> from sqlalchemy.orm.collections import attribute_mapped_collection
> meta = MetaData()
> stocks_table = Table("stocks", meta,
>Column('symbol', String(10), primary_key=True),
>Column('last_price', Numeric)
> )
> brokers_table = Table("brokers", meta,
>Column('id', Integer,primary_key=True),
>Column('name', String(100), nullable=False)
> )
> holdings_table = Table("holdings", meta,
> Column('broker_id', Integer, ForeignKey('brokers.id'),
> primary_key=True),
> Column('symbol', String(10), ForeignKey('stocks.symbol'),
> primary_key=True),
> Column('shares', Integer)
> )
> class Broker(object):
> def __init__(self, name):
> self.name = name
> class Stock(object):
> def __init__(self, symbol):
> self.symbol = symbol
> self.last_price = 0
> class Holding(object):
> def __init__(self, broker=None, stock=None, shares=0):
> self.broker = broker
> self.stock = stock
> self.shares = shares
> mapper(Stock, stocks_table, properties={
> 'by_broker': relationship(Holding, back_populates="stock",
> collection_class=attribute_mapped_collection('broker'))
> })
> mapper(Broker, brokers_table, properties={
> 'by_stock': relationship(Holding, back_populates="broker",
> collection_class=attribute_mapped_collection('stock'))
> })
> mapper(Holding, holdings_table, properties={
> 'stock': relationship(Stock, back_populates="by_broker"),
> 'broker': relationship(Broker, back_populates="by_stock")
> })
> broker = Broker('paj')
> stock = Stock('ZZK')
> holding = Holding(broker, stock, 10)
> print stock.by_broker
> print broker.by_stock
>
> The expected behaviour would be:
>
> {<__main__.Broker object at 0xefce10>: <__main__.Holding object at
> 0xf00910>}
> {<__main__.Stock object at 0x1c74190>: <__main__.Holding object at
> 0xf00910>}
>
>
> But I get:
>
> {<__main__.Broker object at 0xefce10>: <__main__.Holding object at
> 0xf00910>}
> {*None*: <__main__.Holding object at 0xf00910>}
>
> Debugging in the code of sqlalchemy I assume the problem is that the
> addition of the Holding instance in the attribute_mapped_collections is done
> in two steps. During Holding object creation, when the first attribute
> (broker) is set, it fires an event and causes its addition to the
> broker.by_stock collection, but as stock is still not set, the key is None.
> Then, when the second attribute (stock) is set, it get correctly added to
> the collection as broker value is already set.
>
>
> that would appear to be correct.
>
> As far as a bug, mmm, how could such a thing be worked around in an
> automated fashion ? Attribute set events are just that. Either the
> backref events would not fire off at all, waiting for the database round
> trip to populate (just don't use back_populates to achieve that), or the
> backref events would be delayed until after constructor, but that is quite
> awkward and arbitrary, as the same kinds of events can happen in any other
> number of ways besides being set within the constructor.
>
> There's another behavior, which is that a backref populates the incoming
> record into the so-called "pending" collection for an unloaded collection,
> which populates upon access of the collection after the database population
> has occurred. This behavior is to save on SQL calls for backref events, and
> it does not kick in on a transient or pending object since there's no
> collection to load. Some awkward mechanics could allow this behavior to
> occur on a pending/transient, but then the moment you access the actual
> dictionary for a read, its loaded, and then you'd have this undesirable
> behavior again for subsequent populations of Holding. As long as the
> dictionary we're dealing with is just a plain dict and not some dynamic view
> type of thing (which is in fact a feature here), I'm not seeing how this
> kind of issue is avoidable (don't take that to mean there's no solution
> though...I can't say for sure).
>
> It would appear the simplest approach is to just popula
Re: [sqlalchemy] Populate sample data
Hi Mark, I don't know much about sqlite perfomance, but 390k inserts in 4 minuts are 1625 inserts/second which I think is pretty impressive :D One of the things that affects most insert and update performance is index set up on fields. For each indexed field inserted, the db must write the data record AND update the index. Removing unneeded indexes would certainly speed up something. Cheers, 2011/8/4 Mark Erbaugh > I'm using SA (with SQLite) with a schema like: > > A -< B -< C -< D > > where -< means that the tables have a one to many relationship > > I'm populating a sample data set where there are 25 rows in A, 25 rows in > B for each row in A, 25 rows in C for each row in B and 25 rows in D for > each row in C. This results in about 390k rows in D. The database itself > is only about 12 MB, but it takes a long time (several minutes) to write the > data to the file. > > I'm taking the approach of appending items to the table's relationship > column. > > for i in range(25): >x = A() >session.add(A) >for j in range(25): >y = B() >x.b.append(y) >for k in range(25): >z = C() >y.c.append(z) >for l in range(25): >xx = D() >z.d.append(xx) > session.flush() > > > The bulk of the delay seems to be the session.flush call. > > I'm using the Pyramid framework which used Python's transaction module. I > call transaction.begin() prior to adding the rows. According to the SQLite > FAQ, this should speed things up. > > Are there any suggestions on how to speed things up? > > Thanks, > Mark > > -- > 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. > > -- -- Pau Tallada Crespí Dep. d'Astrofísica i Cosmologia Port d'Informació Científica (PIC) Tel: +34 93 586 8233 -- -- 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.
