Re: [sqlalchemy] More efficient Insert mechanism
Michael Bayer wrote: matiskiva wrote: Naturally, the problem is that SA collects the generated id, that is why every statement is executed alone, and than the generated id is retrieved. I was wondering though, is there some kind of a more efficient mechanism? At least on MySQL, under InnoDB [or with table locking] all the items can be inserted, and individual Id's can be calculated. Is there any development there? within the ORM side, very unlikely. DBAPI supports no generalized system of returning newly generated primary key identifiers across a multi-insert, and the ORM does not use the same INSERT statement for every row - some may have values missing for which those columns are omitted from the ultimate INSERT so as to allow a server-side default to fire off. In the case you describe, I assume you mean that the IDs can be guessed by assuming 30K new rows means max(id)max(id) + 3. I'm not aware of another system, but let me know if there's some feature of InnoDB at play here. If indeed that's the idea, that scheme relies upon specifics not just of your database but also the scenario, that is your 30K inserts are all of the identical set of columns, using a sequential integer generator with no skipping of ids which may already exist out of sequence within the table (i.e. that the inserts really start at max(id) and not just available slots), with no concurrent inserts elsewhere that may change the sequencing as it goes (or the table were locked entirely). All fine things but nothing that the ORM can assume. Also, if you're inserting 30K rows, I doubt you really *need* the newly generated IDs or live objects at that point, it sounds much more like you're just dumping a huge amount of rows to be individually accessed at some later time. If you *do* actually assume all these things, then you really should just be using an executemany - particularly since you're assuming you know the exact structure of rows to be inserted. I'm assuming you're familiar with executemany, or if not, its described here: http://www.sqlalchemy.org/docs/sqlexpression.html#executing-multiple-statements Regards, Mati -- 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. Thank you for your feedback. In the case I mentioned, I cannot just dump the 30K items. Because I need their generated id's for other inserted rows (I have relations) In more detail, I am inserting resource items, and also mission items, each mission connects to a resource via id. So dumping all the data, without connecting python object to id is not an option. About the strategy for resolving the matter - I need to have some assumptions, such as table locking. Obviously this is not a good general approach, however in many cases a functionality of bulk-insert-and-acquire-generated-id is desired. If the functionality requires a special configuration and error handling, than we guarantee that the developers are not surprised by failures. So, I would like to implement it. Hopefully with your guidance. As I see it, I need to perform the following steps: * group insert actions by destination table (and order, not breaking dependencies) this will allow me to perform bulk operations, as I am working on a list of items, rather than one item. * perform configuration and environment defendant SQL operations, or withdraw to original insert operation I need your help in the followings: * some description of how the session.add results in insert commands * a pointer to the code that deals with the inserts Regards, Mati This mail was sent via Mobileye Mail-SeCure system. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] UserDefinedType and result_processor
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
On Feb 1, 2010, at 8:31 PM, Manlio Perillo wrote:
Hi.
I have defined a custom type for PostgreSQL ltree support.
[...]
This works, when I create the table and insert some values.
However when I select data from the database, result_processor is not
called.
nothing unusual above and UserDefinedType.result_processor is covered in
tests (which fail if I change result_processor, so its called), so you'll
have to provide more specifics.
Ok, sorry again for the noise.
The cause was a literal value ('*') specified in the column clause in
the select statement.
Unfortunately SQLAlchemy is not (yet?) smart enough to deduce the types
for literal queries.
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktoDekACgkQscQJ24LbaUTSLQCgjc8egOSlx3Qq8spcyczRjKpJ
HkcAoItDI4+sCGSTUaMkbBL7JDGxBT95
=kG1g
-END PGP SIGNATURE-
--
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] session commit and the GIL
Hello, I'm running sqlalchemy on python 2.4.3, with postgresql. My application is multi-threaded, commits and queries are protected by a python thread lock. I suspect that when running session.commit(), the python global interpreter lock (GIL) remains owned by this thread until the commit completes, such that other threads can not run until the commit action completes (even thread that have nothing to do with the database). Is this correct? Thanks, Eyal -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Access property in query.
Thanks, that worked like a charm. Look forward to your PyCon
presentation.
On Feb 2, 12:56 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 1, 2010, at 9:24 PM, Andy (Zenom) wrote:
I don't know if my terminology is correct etc. But the basic idea is
the following.
In my model I have something like
from mylib.helpers import slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key)
title = Column(Unicode)
description = Column(Unicode)
@property
def slug(self):
return slugify(self.title)
Now I want to be able to do something like.
session.query(MyModel.id, MyModel.title, MyModel.slug).all()
The table obviously doesn't have a slug column, I just want to return
my title as a pseudo-column named slug, with it passed through my
slugify method first.
I tried to do something like the following:
@synonym_for(title)
@property
def slug(self):
return slugify(self.title)
And this did not work. It seems to just return another column just
like title (not sent through slugify first). Any idea how I can do
what I am looking for?
part of the approach depends on if slugify can be defined as a SQL function
or if it must be executed in Python. If the latter, you probably want
MyModel.slug to resolve into a column that also defines a custom type.
Assuming its a Python function, note that your column can't be used
effectively in a comparison operation, like MyModel.slug == 'foo', since
the slugify() operation would have to be applied in aggregate.
Anyway assuming non-SQL function, here's a hack I came up with that will do
it:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import _UnaryExpression
from sqlalchemy.types import UserDefinedType
Base = declarative_base()
def slugify(text):
return SLUG ! + text
class MySlugType(UserDefinedType):
def result_processor(self, dialect, coltype):
return slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key=True)
title = Column(Unicode)
description = Column(Unicode)
slug = column_property(_UnaryExpression(title, type_=MySlugType()))
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(MyModel(title=u'title', description=u'description'))
session.commit()
print session.query(MyModel.title, MyModel.slug).all()
--
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
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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: session.add() vs session.merge() and delete child
avdd wrote: On Feb 2, 2:52 am, Michael Bayer mike...@zzzcomputing.com wrote: the behavior you first illustrated, that of merge() and add() not acting the same regarding pending changes, was a behavior that was somewhat in the realm of a bug. I mentioned the other day it was fixed in r6711. Well no, not in 0.5 (r6712). its not an entirely backwards compatible change, since it is add()ing an object that otherwise would not be added, thus causing an error if that object is attached to some other session. I'd recommend upgrading to 0.6, beta1 release is any day this week I get a chance to do it. -- 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] session commit and the GIL
Le mardi 02 février 2010 à 01:08 -0800, Eyal Gordon a écrit : Hello, I'm running sqlalchemy on python 2.4.3, with postgresql. My application is multi-threaded, commits and queries are protected by a python thread lock. I suspect that when running session.commit(), the python global interpreter lock (GIL) remains owned by this thread until the commit completes, such that other threads can not run until the commit action completes (even thread that have nothing to do with the database). Touching any Python object requires holding the GIL. The GIL can only be released when issueing a query to the database (that query can be COMMIT of course), which I suppose the various database drivers already do (if they don't, you can certainly issue a bug/feature request to them). Antoine. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] More efficient Insert mechanism
Mati Skiva wrote: Thank you for your feedback. In the case I mentioned, I cannot just dump the 30K items. Because I need their generated id's for other inserted rows (I have relations) In more detail, I am inserting resource items, and also mission items, each mission connects to a resource via id. So dumping all the data, without connecting python object to id is not an option. About the strategy for resolving the matter - I need to have some assumptions, such as table locking. Obviously this is not a good general approach, however in many cases a functionality of bulk-insert-and-acquire-generated-id is desired. If the functionality requires a special configuration and error handling, than we guarantee that the developers are not surprised by failures. So, I would like to implement it. Hopefully with your guidance. As I see it, I need to perform the following steps: * group insert actions by destination table (and order, not breaking dependencies) this will allow me to perform bulk operations, as I am working on a list of items, rather than one item. * perform configuration and environment defendant SQL operations, or withdraw to original insert operation I need your help in the followings: * some description of how the session.add results in insert commands * a pointer to the code that deals with the inserts I think if you familiarize yourself with the workings of the unit of work, you'll see that inserts are already grouped about as much as they can be. Your series of steps does not take into account the main issue I raised, that the list of insert statements are not all of the same structure, thus making insertmany impossible regardless of primary key fetching unless each statement were carefully grouped by what parameters or embedded SQL expressions are present - a procedure that will usually just add needless overhead, since executemany() can almost never be used except in this very rare lock the tables and assume sequential ids scenario. The biggest reason to keep exotic edge cases out of the core ORM is that everything you want to do is already possible outside of the ORM. You can apply your guess the generated IDs scheme on top of an executemany yourself. I can show you the public API that would allow you to mark your inserted objects as persistent/clean as well after the insert so that it would look like a flush() just occurred. Regards, Mati This mail was sent via Mobileye Mail-SeCure system. -- 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] session commit and the GIL
Eyal Gordon wrote: Hello, I'm running sqlalchemy on python 2.4.3, with postgresql. My application is multi-threaded, commits and queries are protected by a python thread lock. I suspect that when running session.commit(), the python global interpreter lock (GIL) remains owned by this thread until the commit completes, such that other threads can not run until the commit action completes (even thread that have nothing to do with the database). Is this correct? at most it would only remain under the GIL while the psycopg2 connection is performing its own commit(), which is a tiny portion of the session.commit() procedure. If OTOH you are applying a mutex around Session.commit() yourself (which is what commits and queries are protected by a python thread lock sounds like), then its your own mutex that would be serializing the Session.commit() operation across threads. Thanks, Eyal -- 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Access property in query.
The one thing that is frustrating is it is re-querying the database.
So if I create a simple server side function how would I do that? I am
currently using trunk.
On Feb 2, 8:57 am, Andy andy.hol...@belator.com wrote:
Thanks, that worked like a charm. Look forward to your PyCon
presentation.
On Feb 2, 12:56 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 1, 2010, at 9:24 PM, Andy (Zenom) wrote:
I don't know if my terminology is correct etc. But the basic idea is
the following.
In my model I have something like
from mylib.helpers import slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key)
title = Column(Unicode)
description = Column(Unicode)
@property
def slug(self):
return slugify(self.title)
Now I want to be able to do something like.
session.query(MyModel.id, MyModel.title, MyModel.slug).all()
The table obviously doesn't have a slug column, I just want to return
my title as a pseudo-column named slug, with it passed through my
slugify method first.
I tried to do something like the following:
@synonym_for(title)
@property
def slug(self):
return slugify(self.title)
And this did not work. It seems to just return another column just
like title (not sent through slugify first). Any idea how I can do
what I am looking for?
part of the approach depends on if slugify can be defined as a SQL
function or if it must be executed in Python. If the latter, you probably
want MyModel.slug to resolve into a column that also defines a custom type.
Assuming its a Python function, note that your column can't be used
effectively in a comparison operation, like MyModel.slug == 'foo', since
the slugify() operation would have to be applied in aggregate.
Anyway assuming non-SQL function, here's a hack I came up with that will do
it:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import _UnaryExpression
from sqlalchemy.types import UserDefinedType
Base = declarative_base()
def slugify(text):
return SLUG ! + text
class MySlugType(UserDefinedType):
def result_processor(self, dialect, coltype):
return slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key=True)
title = Column(Unicode)
description = Column(Unicode)
slug = column_property(_UnaryExpression(title, type_=MySlugType()))
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(MyModel(title=u'title', description=u'description'))
session.commit()
print session.query(MyModel.title, MyModel.slug).all()
--
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
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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Access property in query.
Andy (Zenom) wrote:
The one thing that is frustrating is it is re-querying the database.
So if I create a simple server side function how would I do that? I am
currently using trunk.
how to create the server side function or how to use it from SQLA ? the
column_property() thing is where you'd use func.foo().
On Feb 2, 8:57 am, Andy andy.hol...@belator.com wrote:
Thanks, that worked like a charm. Look forward to your PyCon
presentation.
On Feb 2, 12:56 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 1, 2010, at 9:24 PM, Andy (Zenom) wrote:
I don't know if my terminology is correct etc. But the basic idea is
the following.
In my model I have something like
from mylib.helpers import slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key)
title = Column(Unicode)
description = Column(Unicode)
@property
def slug(self):
return slugify(self.title)
Now I want to be able to do something like.
session.query(MyModel.id, MyModel.title, MyModel.slug).all()
The table obviously doesn't have a slug column, I just want to
return
my title as a pseudo-column named slug, with it passed through my
slugify method first.
I tried to do something like the following:
@synonym_for(title)
@property
def slug(self):
return slugify(self.title)
And this did not work. It seems to just return another column just
like title (not sent through slugify first). Any idea how I can do
what I am looking for?
part of the approach depends on if slugify can be defined as a SQL
function or if it must be executed in Python. If the latter, you
probably want MyModel.slug to resolve into a column that also defines
a custom type.
Assuming its a Python function, note that your column can't be used
effectively in a comparison operation, like MyModel.slug == 'foo',
since the slugify() operation would have to be applied in aggregate.
Anyway assuming non-SQL function, here's a hack I came up with that
will do it:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import _UnaryExpression
from sqlalchemy.types import UserDefinedType
Base = declarative_base()
def slugify(text):
return SLUG ! + text
class MySlugType(UserDefinedType):
def result_processor(self, dialect, coltype):
return slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key=True)
title = Column(Unicode)
description = Column(Unicode)
slug = column_property(_UnaryExpression(title,
type_=MySlugType()))
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(MyModel(title=u'title', description=u'description'))
session.commit()
print session.query(MyModel.title, MyModel.slug).all()
--
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
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 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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Access property in query.
I got the server side one created in schema cms, function name is
slugify.
I am trying to figure out how to let sqlalchemy know about the
function and the schema its in. On postgresql I can do like SELECT
cms.slugify(title) FROM cms.tablename, and it returns the slugified
title, but to get that in sqlalchemy? Sorry should have been more
clear. I was assuming I needed to somehow create my own sqlalchemy
function, but havn't found the right answer yet.
On Feb 2, 10:50 am, Michael Bayer mike...@zzzcomputing.com wrote:
Andy (Zenom) wrote:
The one thing that is frustrating is it is re-querying the database.
So if I create a simple server side function how would I do that? I am
currently using trunk.
how to create the server side function or how to use it from SQLA ? the
column_property() thing is where you'd use func.foo().
On Feb 2, 8:57 am, Andy andy.hol...@belator.com wrote:
Thanks, that worked like a charm. Look forward to your PyCon
presentation.
On Feb 2, 12:56 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 1, 2010, at 9:24 PM, Andy (Zenom) wrote:
I don't know if my terminology is correct etc. But the basic idea is
the following.
In my model I have something like
from mylib.helpers import slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key)
title = Column(Unicode)
description = Column(Unicode)
@property
def slug(self):
return slugify(self.title)
Now I want to be able to do something like.
session.query(MyModel.id, MyModel.title, MyModel.slug).all()
The table obviously doesn't have a slug column, I just want to
return
my title as a pseudo-column named slug, with it passed through my
slugify method first.
I tried to do something like the following:
@synonym_for(title)
@property
def slug(self):
return slugify(self.title)
And this did not work. It seems to just return another column just
like title (not sent through slugify first). Any idea how I can do
what I am looking for?
part of the approach depends on if slugify can be defined as a SQL
function or if it must be executed in Python. If the latter, you
probably want MyModel.slug to resolve into a column that also defines
a custom type.
Assuming its a Python function, note that your column can't be used
effectively in a comparison operation, like MyModel.slug == 'foo',
since the slugify() operation would have to be applied in aggregate.
Anyway assuming non-SQL function, here's a hack I came up with that
will do it:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import _UnaryExpression
from sqlalchemy.types import UserDefinedType
Base = declarative_base()
def slugify(text):
return SLUG ! + text
class MySlugType(UserDefinedType):
def result_processor(self, dialect, coltype):
return slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key=True)
title = Column(Unicode)
description = Column(Unicode)
slug = column_property(_UnaryExpression(title,
type_=MySlugType()))
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(MyModel(title=u'title', description=u'description'))
session.commit()
print session.query(MyModel.title, MyModel.slug).all()
--
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
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 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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Access property in query.
Andy (Zenom) wrote:
I got the server side one created in schema cms, function name is
slugify.
I am trying to figure out how to let sqlalchemy know about the
function and the schema its in. On postgresql I can do like SELECT
cms.slugify(title) FROM cms.tablename, and it returns the slugified
title, but to get that in sqlalchemy? Sorry should have been more
clear. I was assuming I needed to somehow create my own sqlalchemy
function, but havn't found the right answer yet.
so that's column_property(func.cms.slugify(title)) .
On Feb 2, 10:50 am, Michael Bayer mike...@zzzcomputing.com wrote:
Andy (Zenom) wrote:
The one thing that is frustrating is it is re-querying the database.
So if I create a simple server side function how would I do that? I am
currently using trunk.
how to create the server side function or how to use it from SQLA ? the
column_property() thing is where you'd use func.foo().
On Feb 2, 8:57 am, Andy andy.hol...@belator.com wrote:
Thanks, that worked like a charm. Look forward to your PyCon
presentation.
On Feb 2, 12:56 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 1, 2010, at 9:24 PM, Andy (Zenom) wrote:
I don't know if my terminology is correct etc. But the basic idea
is
the following.
In my model I have something like
from mylib.helpers import slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key)
title = Column(Unicode)
description = Column(Unicode)
@property
def slug(self):
return slugify(self.title)
Now I want to be able to do something like.
session.query(MyModel.id, MyModel.title, MyModel.slug).all()
The table obviously doesn't have a slug column, I just want to
return
my title as a pseudo-column named slug, with it passed through
my
slugify method first.
I tried to do something like the following:
@synonym_for(title)
@property
def slug(self):
return slugify(self.title)
And this did not work. It seems to just return another column
just
like title (not sent through slugify first). Any idea how I can
do
what I am looking for?
part of the approach depends on if slugify can be defined as a
SQL
function or if it must be executed in Python. If the latter, you
probably want MyModel.slug to resolve into a column that also defines
a custom type.
Assuming its a Python function, note that your column can't be used
effectively in a comparison operation, like MyModel.slug == 'foo',
since the slugify() operation would have to be applied in aggregate.
Anyway assuming non-SQL function, here's a hack I came up with that
will do it:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import _UnaryExpression
from sqlalchemy.types import UserDefinedType
Base = declarative_base()
def slugify(text):
return SLUG ! + text
class MySlugType(UserDefinedType):
def result_processor(self, dialect, coltype):
return slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key=True)
title = Column(Unicode)
description = Column(Unicode)
slug = column_property(_UnaryExpression(title,
type_=MySlugType()))
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(MyModel(title=u'title', description=u'description'))
session.commit()
print session.query(MyModel.title, MyModel.slug).all()
--
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
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 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.
--
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.
--
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
[sqlalchemy] Re: Access property in query.
I do this, and the query it spits out in the error works fine in the
postgresql command line, but not in sqlalchemy.
Traceback:
http://pylonshq.com/tracebacks/9b322f31095beda23f5d846e43399d21
query:
'SELECT DISTINCT cms.niches.title AS cms_niches_title,
cms.niches.niche_id AS cms_niches_niche_id, cms.slugify
(cms.niches.title) AS slug \nFROM cms.niches JOIN cms.dvd_sites_niches
ON cms.niches.niche_id = cms.dvd_sites_niches.niche_id ORDER BY
cms.niches.title ASC'
Here is my function:
CREATE OR REPLACE FUNCTION cms.slugify(character varying)
RETURNS character varying AS
$BODY$
import re
import unicodedata
value = unicode(args[0])
value = value.replace(, )
value = value.replace(_, -)
value = unicodedata.normalize(NFKD, value).encode(ascii,
ignore)
value = unicode(re.sub([^\w\s-], , value).strip().lower())
return re.sub([-\s]+, -, value)
$BODY$
LANGUAGE 'plpythonu' VOLATILE;
Again the function works in like pgadmin3 etc, but not in sqlalchemy.
On Feb 2, 11:01 am, Michael Bayer mike...@zzzcomputing.com wrote:
Andy (Zenom) wrote:
I got the server side one created in schema cms, function name is
slugify.
I am trying to figure out how to let sqlalchemy know about the
function and the schema its in. On postgresql I can do like SELECT
cms.slugify(title) FROM cms.tablename, and it returns the slugified
title, but to get that in sqlalchemy? Sorry should have been more
clear. I was assuming I needed to somehow create my own sqlalchemy
function, but havn't found the right answer yet.
so that's column_property(func.cms.slugify(title)) .
On Feb 2, 10:50 am, Michael Bayer mike...@zzzcomputing.com wrote:
Andy (Zenom) wrote:
The one thing that is frustrating is it is re-querying the database.
So if I create a simple server side function how would I do that? I am
currently using trunk.
how to create the server side function or how to use it from SQLA ? the
column_property() thing is where you'd use func.foo().
On Feb 2, 8:57 am, Andy andy.hol...@belator.com wrote:
Thanks, that worked like a charm. Look forward to your PyCon
presentation.
On Feb 2, 12:56 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 1, 2010, at 9:24 PM, Andy (Zenom) wrote:
I don't know if my terminology is correct etc. But the basic idea
is
the following.
In my model I have something like
from mylib.helpers import slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key)
title = Column(Unicode)
description = Column(Unicode)
@property
def slug(self):
return slugify(self.title)
Now I want to be able to do something like.
session.query(MyModel.id, MyModel.title, MyModel.slug).all()
The table obviously doesn't have a slug column, I just want to
return
my title as a pseudo-column named slug, with it passed through
my
slugify method first.
I tried to do something like the following:
@synonym_for(title)
@property
def slug(self):
return slugify(self.title)
And this did not work. It seems to just return another column
just
like title (not sent through slugify first). Any idea how I can
do
what I am looking for?
part of the approach depends on if slugify can be defined as a
SQL
function or if it must be executed in Python. If the latter, you
probably want MyModel.slug to resolve into a column that also defines
a custom type.
Assuming its a Python function, note that your column can't be used
effectively in a comparison operation, like MyModel.slug == 'foo',
since the slugify() operation would have to be applied in aggregate.
Anyway assuming non-SQL function, here's a hack I came up with that
will do it:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import _UnaryExpression
from sqlalchemy.types import UserDefinedType
Base = declarative_base()
def slugify(text):
return SLUG ! + text
class MySlugType(UserDefinedType):
def result_processor(self, dialect, coltype):
return slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key=True)
title = Column(Unicode)
description = Column(Unicode)
slug = column_property(_UnaryExpression(title,
type_=MySlugType()))
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(MyModel(title=u'title', description=u'description'))
session.commit()
print session.query(MyModel.title, MyModel.slug).all()
--
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
[sqlalchemy] Re: Access property in query.
Scratch the last, helps if I have my development.ini pointed to the
proper database server :) Its working fine. Thanks for all the help :)
On Feb 2, 11:24 am, Andy (Zenom) stress...@gmail.com wrote:
I do this, and the query it spits out in the error works fine in the
postgresql command line, but not in sqlalchemy.
Traceback:http://pylonshq.com/tracebacks/9b322f31095beda23f5d846e43399d21
query:
'SELECT DISTINCT cms.niches.title AS cms_niches_title,
cms.niches.niche_id AS cms_niches_niche_id, cms.slugify
(cms.niches.title) AS slug \nFROM cms.niches JOIN cms.dvd_sites_niches
ON cms.niches.niche_id = cms.dvd_sites_niches.niche_id ORDER BY
cms.niches.title ASC'
Here is my function:
CREATE OR REPLACE FUNCTION cms.slugify(character varying)
RETURNS character varying AS
$BODY$
import re
import unicodedata
value = unicode(args[0])
value = value.replace(, )
value = value.replace(_, -)
value = unicodedata.normalize(NFKD, value).encode(ascii,
ignore)
value = unicode(re.sub([^\w\s-], , value).strip().lower())
return re.sub([-\s]+, -, value)
$BODY$
LANGUAGE 'plpythonu' VOLATILE;
Again the function works in like pgadmin3 etc, but not in sqlalchemy.
On Feb 2, 11:01 am, Michael Bayer mike...@zzzcomputing.com wrote:
Andy (Zenom) wrote:
I got the server side one created in schema cms, function name is
slugify.
I am trying to figure out how to let sqlalchemy know about the
function and the schema its in. On postgresql I can do like SELECT
cms.slugify(title) FROM cms.tablename, and it returns the slugified
title, but to get that in sqlalchemy? Sorry should have been more
clear. I was assuming I needed to somehow create my own sqlalchemy
function, but havn't found the right answer yet.
so that's column_property(func.cms.slugify(title)) .
On Feb 2, 10:50 am, Michael Bayer mike...@zzzcomputing.com wrote:
Andy (Zenom) wrote:
The one thing that is frustrating is it is re-querying the database.
So if I create a simple server side function how would I do that? I am
currently using trunk.
how to create the server side function or how to use it from SQLA ? the
column_property() thing is where you'd use func.foo().
On Feb 2, 8:57 am, Andy andy.hol...@belator.com wrote:
Thanks, that worked like a charm. Look forward to your PyCon
presentation.
On Feb 2, 12:56 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 1, 2010, at 9:24 PM, Andy (Zenom) wrote:
I don't know if my terminology is correct etc. But the basic idea
is
the following.
In my model I have something like
from mylib.helpers import slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key)
title = Column(Unicode)
description = Column(Unicode)
@property
def slug(self):
return slugify(self.title)
Now I want to be able to do something like.
session.query(MyModel.id, MyModel.title, MyModel.slug).all()
The table obviously doesn't have a slug column, I just want to
return
my title as a pseudo-column named slug, with it passed through
my
slugify method first.
I tried to do something like the following:
@synonym_for(title)
@property
def slug(self):
return slugify(self.title)
And this did not work. It seems to just return another column
just
like title (not sent through slugify first). Any idea how I can
do
what I am looking for?
part of the approach depends on if slugify can be defined as a
SQL
function or if it must be executed in Python. If the latter, you
probably want MyModel.slug to resolve into a column that also defines
a custom type.
Assuming its a Python function, note that your column can't be used
effectively in a comparison operation, like MyModel.slug == 'foo',
since the slugify() operation would have to be applied in aggregate.
Anyway assuming non-SQL function, here's a hack I came up with that
will do it:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import _UnaryExpression
from sqlalchemy.types import UserDefinedType
Base = declarative_base()
def slugify(text):
return SLUG ! + text
class MySlugType(UserDefinedType):
def result_processor(self, dialect, coltype):
return slugify
class MyModel(Base):
__tablename__ = testing
id = Column(Integer, primary_key=True)
title = Column(Unicode)
description = Column(Unicode)
slug = column_property(_UnaryExpression(title,
type_=MySlugType()))
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(MyModel(title=u'title', description=u'description'))
Re: [sqlalchemy] Mixing matching connectors and dialects in 0.6
Hi Michael,
Thanks for the tip on dialect creation; I have a working connection
and much of the low level library functionality appears to just
work.
I've just started digging into the internals to begin implementing
introspection support. One issue I've encountered is that PyODBC
happens to return boolean columns as strings with values 1 and 0.
This confuses the current type system. I have skimmed the
TypeDecorator documentation but am still confused about how to
associate that with this custom dialect. Any hints?
I have attached the dialect base.py and a few test cases (not sure
they're useful as Vertica is frustratingly secretive about everything)
if you have time to review and comment.
On Fri, Jan 15, 2010 at 2:57 PM, Bo Shi bs1...@gmail.com wrote:
That's funny because Oracle and SQL server are utterly, totally different
from a SQL quirks perspective. If I were to pick two dialects in SQLA
that were *most* different from each other and also non-standard, those
would be the two.
I was a bit puzzled by this also (granted this was from some early
press release I dredged up* from google). I'm still working through
their documentation and haven't run across any configuration that
might enable a compatability mode yet.
* improved compatibility with Oracle and SQLServer SQL dialects
http://www.vertica.com/company/news_and_events/Vertica-Analytics-Database-2.5-The-Most-Cost-Effective-Way-To-Modernize-Data-Warehousing
create_engine()
using 'mssql+pyodbc' seems to work but upon attempting to execute a
simple select statement, I get a programming error indicating the
following failed to run:
'SELECT user_name() as user_name;'
So it seems the dialect is getting some additional state under the
hood. lib/sqlalchemy/dialects/mssql/pyodbc.py appears to be hooking
PyODBCConnector and MSDialect together. Is it possible to specify
another dialect here? If so, is there any documentation on how to do
so?
you'd want to make yourself a vertica dialect module that imports the
PyODBCConnector and uses it as a mixin. I'd suggest copying one of the
existing dialects, and probably not the SQL server one unless you know
that vertica has a lot of the transact-SQL lineage that SQL server does
(the PG and SQLite dialects are the most barebones). To run it, add a
setup.py which configures your library as a setuptools entry point, in
this case the name would be vertica+pyodbc:
from setuptools import setup
setup(name=SQLAVertica,
description=...,
entry_points={
'sqlalchemy:plugins':
['vertica+pyodbc = mypackage.base:VerticaDialect']
}
then using create_engine('vertica+pyodbc://user:p...@host/dbname') will load
in your dialect.
--
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.
--
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.
base.py
Description: Binary data
vertica.py
Description: Binary data
Re: [sqlalchemy] More efficient Insert mechanism
Mati Skiva wrote:
I believe I took these issues into account.
But just to be sure, maybe you can highlight something for me.
I assume the followings about the process of session.add:
* after session.add is called, the objects are placed in a to-do pool
(maybe)
* for self-generating-id objects, a special process is used, one that
fetches the generated id after the insert
* for no self-generating-id objects, a regular process of insert is used
I came to this conclusion, because otherwise, after each insert all the
data of the row is retrieved and placed inside the object. Which covers
the newly generated id.
Since you're interested in hacking SQLAlchemy internals, it would helpful
if you could familiarize yourself fully with the SQL expression and
execution API, all of which is public and documented. id generation is
a standard feature of this system and occurs outside of the ORM. Fetching
the ID for a new row with the SQLA expression API is a matter of calling a
ResultProxy method which the ORM only calls if it doesn't already know the
full set of primary key columns for a specific object. The backend
implementation of id fetching varies wildly across all dialects as well
as versions of each database in use.
The flush procedure updates or expires those fields of the object which
were known to be generated by the database. Fields which are immediately
updated include some or all of the primary key columns. Fields which are
expired include any other columns which were generated via server-side SQL
expressions or known schema-level defaults - these are fetched when the
object's expired attributes are next accessed, if at all.
The SQL compiler procedure is aware of these columns when it generates an
individual insert() construct, given a list of parameters which are to be
bound literals and others which are to be embedded SQL expressions. When
executed, the ResultProxy API provides these details fully - you should
familiarize yourself with all of its methods.
I would also point you to http://www.sqlalchemy.org/trac/ticket/1518 ,
which suggests that we should open up Mapper to being subclassable (for
much more exotic purposes than what you have here). However, this
subclassing is possible now, which is where you can have your insertmany
functionality rolled in a rudimental way for your immediate use case
without burdening SQLAlchemy core with the need to have a fully
tested/supported/performant feature. See the attached example.
--
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.
from sqlalchemy.orm.mapper import Mapper
class OptimizedInsertMapper(Mapper):
def _save_obj(self, states, uowtransaction, postupdate=False, post_update_cols=None, single=False):
# determine which of the incoming states are inserts
inserts = [s for s in states if not s.key]
# split off into inserts, updates
states = list(set(states).difference(inserts))
# get max ID of the table
max_id = uowtransaction.session.scalar(select([func.max(self.primary_key[0])]))
if not max_id:
max_id = 0
# insertmany(). assume the incoming states are all of the same format.
uowtransaction.session.execute(
self.local_table.insert(),
[s.dict for s in inserts]
)
# guess ids - establish state on the objects.
for i, s in enumerate(inserts):
s.dict['id'] = max_id + i + 1
# send the updates to the regular _save_obj
Mapper._save_obj(self, states, uowtransaction, postupdate=postupdate, post_update_cols=post_update_cols, single=single)
if __name__ == '__main__':
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(mapper=OptimizedInsertMapper)
class Widget(Base):
__tablename__ = 'widget'
id = Column(Integer, primary_key=True)
data = Column(String)
related = relation(Related)
def __init__(self, data):
self.data = data
self.related = [Related() for i in xrange(5)]
class Related(Base):
__tablename__ = 'related'
id = Column(Integer, primary_key=True)
widget_id = Column(Integer, ForeignKey('widget.id'))
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
sess = sessionmaker(e)()
widgets = [Widget(data=widget %d % i) for i in xrange(10)]
sess.add_all(widgets)
sess.commit()
# assert everything was generated
for w in widgets:
assert w.id
for r in w.related:
assert
[sqlalchemy] relations and multiple declarative bases
Hi All,
The script at the end of this message blows up with:
sqlalchemy.exc.InvalidRequestError: When compiling mapper
Mapper|Employee|employee, expression 'Organisation' failed to locate a
name (name 'Organisation' is not defined). If this is a class name,
consider adding this relation() to the class '__main__.Employee' class
after both dependent classes have been defined.
...unless the line preceded with #yuk is present.
Why does declarative store this information in its own registry rather
than using the MetaData to do so?
Is there a nicer way I can achieve this result? I have multiple bases
since Michael suggested this was the right way to go to have base
classes for mapped classes that do nothing but contain common field
definitions and functionality...
cheers,
Chris
from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String, DateTime
engine = create_engine('sqlite://')
metadata = MetaData()
Base1 = declarative_base(metadata=metadata)
Base2 = declarative_base(metadata=metadata)
# yuk
Base2._decl_class_registry=Base1._decl_class_registry
class Employee(Base1):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(1000), nullable=False, index=True)
org = relation(Organisation)
org_id = Column(Integer, ForeignKey('organisation.id'))
class Organisation(Base2):
__tablename__ = 'organisation'
id = Column(Integer, primary_key=True)
name = Column(String(50))
employees = relation(Employee)
metadata.create_all(engine)
o = Organisation(name='SomeOrg')
--
Simplistix - Content Management, Batch Processing Python Consulting
- http://www.simplistix.co.uk
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] any way to pre cook a monster query?
Michael Bayer wrote: Piecing together a select() construct and compiling to a string is a relatively inexpensive operation.The vast majority of time in Query is spent about 1/3rd-half on the database side and the rest in fetching rows and instantiating/populating objects. Fair enough. has to traverse mapped classes to generate. But its a fraction of what it takes to fetch rows, and in that area we'd ultimately like to reimplement that part in C. I hope the use of the C part always remains thoroughly optional, since C extensions cause a world of pain in a lot of situations... snip All of that said we are always welcome to patches and new core developer candidates since a really well designed implementation of such is certainly something we can look for - particularly if it could be a pluggable option that people don't have to use, which would allow it to become mature on its own as its used by people without impacting the current userbase. Thanks for the thorough coverage of this, it's verymuch appreciated. Now that I understand more about what's going on during the query process, I'm in total agreement with you :-) cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] insert with scalar select statament
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I have written a small class in order to support PostgreSQL tsearch type:
class TextSearchVector(UserDefinedType):
Support for full-text search
See
http://www.postgresql.org/docs/8.4/static/datatype-textsearch.html
def get_col_spec(self):
return 'tsvector'
The problem with this code is that a string is converted to a tsearch
type without first normalizing it; this can also cause a syntax error.
The solution is to use the `to_tsearch` function.
In SQL (tested with PostgreSQL and SQLite, at least) I can do:
INSERT INTO test (text) values(to_tsvector('some text'));
That is, it is possible to specify a scalar select statement for each value.
Is this possible to do with SQLAlchemy?
It seems to me that it is not possible.
It is not possible to specify an SQL scalar select statement in a custom
type bind_processor method, and it is not possible to specify an SQL
scalar select statement in an insert statement.
Can this be solved using SQLAlchemy?
Or should I simply use triggers?
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktoc0IACgkQscQJ24LbaUQ/EgCeIKThU9dV8DZT0qampIR1iHRx
bP4AoI1/DPoEXRyewZGHLs6LF8DdCRZp
=YKtw
-END PGP SIGNATURE-
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] relations and multiple declarative bases
Chris Withers wrote:
Hi All,
The script at the end of this message blows up with:
sqlalchemy.exc.InvalidRequestError: When compiling mapper
Mapper|Employee|employee, expression 'Organisation' failed to locate a
name (name 'Organisation' is not defined). If this is a class name,
consider adding this relation() to the class '__main__.Employee' class
after both dependent classes have been defined.
...unless the line preceded with #yuk is present.
Why does declarative store this information in its own registry rather
than using the MetaData to do so?
MetaData is not an ORM aware object. it doesn't store class names.
Is there a nicer way I can achieve this result? I have multiple bases
since Michael suggested this was the right way to go to have base
classes for mapped classes that do nothing but contain common field
definitions and functionality...
der, OK if I did actually say that, its obsolete information. Use
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DeclarativeMixins . Yes
the recipe will probably be built in someday once we have time to cover
all contingencies.
Or reference Organization directly without using a string for its name.
cheers,
Chris
from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String, DateTime
engine = create_engine('sqlite://')
metadata = MetaData()
Base1 = declarative_base(metadata=metadata)
Base2 = declarative_base(metadata=metadata)
# yuk
Base2._decl_class_registry=Base1._decl_class_registry
class Employee(Base1):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(1000), nullable=False, index=True)
org = relation(Organisation)
org_id = Column(Integer, ForeignKey('organisation.id'))
class Organisation(Base2):
__tablename__ = 'organisation'
id = Column(Integer, primary_key=True)
name = Column(String(50))
employees = relation(Employee)
metadata.create_all(engine)
o = Organisation(name='SomeOrg')
--
Simplistix - Content Management, Batch Processing Python Consulting
- http://www.simplistix.co.uk
--
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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] More efficient Insert mechanism
Michael Bayer wrote: Mati Skiva wrote: I believe I took these issues into account. But just to be sure, maybe you can highlight something for me. I assume the followings about the process of session.add: * after session.add is called, the objects are placed in a to-do pool (maybe) * for self-generating-id objects, a special process is used, one that fetches the generated id after the insert * for no self-generating-id objects, a regular process of insert is used I came to this conclusion, because otherwise, after each insert all the data of the row is retrieved and placed inside the object. Which covers the newly generated id. Since you're interested in hacking SQLAlchemy internals, it would helpful if you could familiarize yourself fully with the SQL expression and execution API, all of which is public and documented. id generation is a standard feature of this system and occurs outside of the ORM. Fetching the ID for a new row with the SQLA expression API is a matter of calling a ResultProxy method which the ORM only calls if it doesn't already know the full set of primary key columns for a specific object. The backend implementation of id fetching varies wildly across all dialects as well as versions of each database in use. The flush procedure updates or expires those fields of the object which were known to be generated by the database. Fields which are immediately updated include some or all of the primary key columns. Fields which are expired include any other columns which were generated via server-side SQL expressions or known schema-level defaults - these are fetched when the object's expired attributes are next accessed, if at all. The SQL compiler procedure is aware of these columns when it generates an individual insert() construct, given a list of parameters which are to be bound literals and others which are to be embedded SQL expressions. When executed, the ResultProxy API provides these details fully - you should familiarize yourself with all of its methods. I would also point you to http://www.sqlalchemy.org/trac/ticket/1518 , which suggests that we should open up Mapper to being subclassable (for much more exotic purposes than what you have here). However, this subclassing is possible now, which is where you can have your insertmany functionality rolled in a rudimental way for your immediate use case without burdening SQLAlchemy core with the need to have a fully tested/supported/performant feature. See the attached example. Thank you for the pointers, as well as the example. I attempted using it, however quickly enough I was forced to dig into the internals. I shall explain why. from inspecting mapper.py's _save_obj method, I found that after every insert the following items are required: * value_params - used by _postfetch * params - used by _postfetch * ResultProxy.last_inserted_params() - used by _postfetch * ResultProxy.postfetch_cols() - used within _postfetch * ResultProxy.prefetch_cols() - used within _postfetch * ResultProxy.last_inserted_ids() - used by by _save_obj All of these items shared the same problem, they are all dictionaries or lists. One for each insert, making them single-dimension data (in respect to the insert). But when executing many, they became either None or kept their single-dimension property (i.e. for two inserts, only one list is returned, when I expect a list of lists) That alone prevented me from overloading _save_obj. Because I am required to have these items for every inserted row. So, I set upon transforming these items into multi-dimensional upon execute-many. It brought me to default.py's __process_defaults where I implemented the followings: def __process_defaults(self): generate default values for compiled insert/update statements, and generate last_inserted_ids() collection. if self.executemany: if len(self.compiled.prefetch): drunner = self.dialect.defaultrunner(self) params = self.compiled_parameters for param in params: # assign each dict of params to self.compiled_parameters; # this allows user-defined default generators to access the full # set of bind params for the row self.compiled_parameters = param for c in self.compiled.prefetch: if self.isinsert: val = drunner.get_column_default(c) else: val = drunner.get_column_onupdate(c) if val is not None: param[c.key] = val self.compiled_parameters = params if self.isinsert: self._last_inserted_ids = [] self._last_inserted_params = [] for compiled_parameters in self.compiled_parameters: drunner =
Re: [sqlalchemy] insert with scalar select statament
Manlio Perillo wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I have written a small class in order to support PostgreSQL tsearch type:
class TextSearchVector(UserDefinedType):
Support for full-text search
See
http://www.postgresql.org/docs/8.4/static/datatype-textsearch.html
def get_col_spec(self):
return 'tsvector'
The problem with this code is that a string is converted to a tsearch
type without first normalizing it; this can also cause a syntax error.
The solution is to use the `to_tsearch` function.
In SQL (tested with PostgreSQL and SQLite, at least) I can do:
INSERT INTO test (text) values(to_tsvector('some text'));
That is, it is possible to specify a scalar select statement for each
value.
Is this possible to do with SQLAlchemy?
It seems to me that it is not possible.
It is not possible to specify an SQL scalar select statement in a custom
type bind_processor method, and it is not possible to specify an SQL
scalar select statement in an insert statement.
Can this be solved using SQLAlchemy?
Or should I simply use triggers?
OK you mean SQL expression during compilation. This is ticket #1534
which is not yet implemented. Right now you'd have to set attributes to
the to_tsvector() expression directly, note that the ORM and such accept
SQL expressions as values.Which means if you're dealing just with ORM,
you can use a @validates or similar (and maybe a comparator too) that
wraps incoming values into to_tsvector(). The example in
examples/postgis/postgis.py illustrates these techniques.
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktoc0IACgkQscQJ24LbaUQ/EgCeIKThU9dV8DZT0qampIR1iHRx
bP4AoI1/DPoEXRyewZGHLs6LF8DdCRZp
=YKtw
-END PGP SIGNATURE-
--
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.
--
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] engine.execute() vs text().execute()
Hi. I'm trying to track down a problem, and I'm wondering if the following two statements are appreciably different? I would have thought they were equivalent: (1) result = engine.execute(myQuery) (2) result = text(myQuery, engine).execute() myQuery is a string containing a big, hairy SQL query that can take a couple of hours to run under Oracle 10 before returning a ResultProxy object. When using the first statement above, I'm always getting this Oracle error: File /swu/lib/python2.6/site-packages/sqlalchemy/engine/base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01652: unable to extend temp segment by 128 in tablespace TEMP The query always runs fine, however, when I use the second statement. |ouglas P.S. I fully understand that the phenomenon I'm observing could be completely coincidental, as it's difficult to confirm the correlation when each attempt takes several hours. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] More efficient Insert mechanism
Mati Skiva wrote: from inspecting mapper.py's _save_obj method, I found that after every insert the following items are required: * value_params - used by _postfetch * params - used by _postfetch * ResultProxy.last_inserted_params() - used by _postfetch * ResultProxy.postfetch_cols() - used within _postfetch * ResultProxy.prefetch_cols() - used within _postfetch * ResultProxy.last_inserted_ids() - used by by _save_obj All of these items shared the same problem, they are all dictionaries or lists. One for each insert, making them single-dimension data (in respect to the insert). But when executing many, they became either None or kept their single-dimension property (i.e. for two inserts, only one list is returned, when I expect a list of lists) the cols() methods return lists of column objects which correspond to the structure of the insert. Those apply to executemany() as well as execute(), since both functions receive a single insert() construct of a fixed form. The value_params() similarly are fixed for the single insert construct and do not change.last_inserted_ids() is not used in your recipe - you are calculating primary key values en masse - DBAPI has no way to give you this information. The only value here that may be of usage is last_inserted_params(). The only data in that collection which is not present before the insert is executed are Python side defaults which you may have configured on your Table.The full list of everything inserted post-calculation of python-side defaults is present at resultproxy.context.compiled_parameters. Use 0.6, the code example you mention is obsolete 0.5 code, and the compiled_parameters collection is undisturbed. I was forced to make one extra change, for sqlite.py's post_exec method. Basically, populating the self._last_inserted_ids attribute. But for some reason self.cursor.lastrowid is None for execute-many (while it can Here is the reason: http://www.python.org/dev/peps/pep-0249/ Cursor Attribute .lastrowid This read-only attribute provides the rowid of the last modified row (most databases return a rowid only when a single INSERT operation is performed). If the operation does not set a rowid or if the database does not support rowids, this attribute should be set to None. The semantics of .lastrowid are undefined in case the last executed statement modified more than one row, e.g. when using INSERT with .executemany(). Also be aware that .lastrowid is not implemented at all for: psycopg2, pg8000, cx_oracle, kintersbasdb, pyodbc, pymssql, adodbapi, etc. I'm strongly opposed to forcing a multi-row version of last_inserted_ids() into dialects. It cannot work consistently or predictably, and is not supported by DBAPI. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] insert with scalar select statament
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
[...]
In SQL (tested with PostgreSQL and SQLite, at least) I can do:
INSERT INTO test (text) values(to_tsvector('some text'));
That is, it is possible to specify a scalar select statement for each
value.
Is this possible to do with SQLAlchemy?
[...]
OK you mean SQL expression during compilation. This is ticket #1534
which is not yet implemented.
After some tests I got something that works
http://paste.pocoo.org/show/173099/
The documentation seems to confirm this:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.insert
SQLAlchemy still continues to surprise me!
I need to download the documentation in PDF format, and take some time
study it.
I don't know if an example of this feature is available in the tutorial;
if not, it should be added.
Right now you'd have to set attributes to
the to_tsvector() expression directly, note that the ORM and such accept
SQL expressions as values.Which means if you're dealing just with ORM,
you can use a @validates or similar (and maybe a comparator too) that
wraps incoming values into to_tsvector().
It is 4 years that I don't touch the ORM ;-).
The example in
examples/postgis/postgis.py illustrates these techniques.
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAktogfkACgkQscQJ24LbaUSjwgCfevuSHb0YjMGNMFXhm+imJHqY
YSsAn1wypa/GG86TfGHMIGbFvf9lctVN
=eqiB
-END PGP SIGNATURE-
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] insert with scalar select statament
Manlio Perillo wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Bayer ha scritto:
[...]
In SQL (tested with PostgreSQL and SQLite, at least) I can do:
INSERT INTO test (text) values(to_tsvector('some text'));
That is, it is possible to specify a scalar select statement for each
value.
Is this possible to do with SQLAlchemy?
[...]
OK you mean SQL expression during compilation. This is ticket #1534
which is not yet implemented.
After some tests I got something that works
http://paste.pocoo.org/show/173099/
The documentation seems to confirm this:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.insert
SQLAlchemy still continues to surprise me!
I need to download the documentation in PDF format, and take some time
study it.
I don't know if an example of this feature is available in the tutorial;
if not, it should be added.
errr, you can put SQL expressions into insert.values(), but not in the
params sent to execute() (that was discontinued after 0.4). the example
you have is actually executing the sql.select() statement beforehand since
thats what scalar() does.
you're looking for
query = test.insert().values(text=sql.func.to_tsvector(u'some text'))
engine.execute(query)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] insert with scalar select statament
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: [...] OK you mean SQL expression during compilation. This is ticket #1534 which is not yet implemented. After some tests I got something that works http://paste.pocoo.org/show/173099/ [...] errr, you can put SQL expressions into insert.values(), but not in the params sent to execute() (that was discontinued after 0.4). I have never used the values method. Is this a recent feature? the example you have is actually executing the sql.select() statement beforehand since thats what scalar() does. you're looking for query = test.insert().values(text=sql.func.to_tsvector(u'some text')) engine.execute(query) Thanks. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktolCUACgkQscQJ24LbaURINgCglj+N2JKqkWit1/DFCEBff2Kd 7fsAn0F8Y5mUCYyqWSxydosxk36RzsGZ =VHYH -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] insert with scalar select statament
Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: Manlio Perillo wrote: [...] OK you mean SQL expression during compilation. This is ticket #1534 which is not yet implemented. After some tests I got something that works http://paste.pocoo.org/show/173099/ [...] errr, you can put SQL expressions into insert.values(), but not in the params sent to execute() (that was discontinued after 0.4). I have never used the values method. Is this a recent feature? couple of years old at this point...goes back to 0.4 at least the example you have is actually executing the sql.select() statement beforehand since thats what scalar() does. you're looking for query = test.insert().values(text=sql.func.to_tsvector(u'some text')) engine.execute(query) Thanks. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktolCUACgkQscQJ24LbaURINgCglj+N2JKqkWit1/DFCEBff2Kd 7fsAn0F8Y5mUCYyqWSxydosxk36RzsGZ =VHYH -END PGP SIGNATURE- -- 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] passive updates and concurrent mod error
avdd wrote:
Something a bit more normal now.
Combining optimistic concurrency control with cascading PK updates is
problematic, if you load the child relation, the session issues
unecessary updates for the children, resulting in
ConcurrentModificationError
this issue is fixed in trunk as of r6719. mapper() now has a
passive_updates flag set to True by default. The caveat for now is that
to run this test script on SQLite or MySQL/MyISAM requires setting the
flag to False, since the mapper has to make a definitive yes/no decision
on if it can assume a PK-FK change must occur via CASCADE or not, which
determines if it has to use the new or old PK value of the row in order to
locate it. will look into automating this flag in a future release based
on dialect/table options.
import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
__metaclass__ = type
engine = sql.create_engine(postgresql:///avdd)
metadata = sql.MetaData(bind=engine)
db = orm.create_session(bind=engine, autocommit=False)
T = declarative_base(metadata=metadata)
class P(T):
__tablename__ = 'p'
id = sql.Column(sql.String, primary_key=True)
version = sql.Column(sql.Integer, nullable=False, default=1)
cc = orm.relation('C', backref='parent', passive_updates=True)
__mapper_args__ = {'version_id_col': version}
class C(T):
__tablename__ = 'c'
i = sql.Column(sql.String, primary_key=True)
p = sql.Column(sql.String,
sql.ForeignKey('p.id', onupdate='cascade',
ondelete='cascade'),
primary_key=True)
version = sql.Column(sql.Integer, nullable=False, default=1)
__mapper_args__ = {'version_id_col': version}
metadata.create_all()
P.__table__.delete().execute()
with db.transaction:
p = P(id='P1', cc=[C(i='C.1'), C(i='C.2')])
db.add(p)
db.expunge_all()
p = db.query(P).first()
with db.transaction:
p.id = 'P2'
# ok, no ConcModError
db.expunge_all()
p = db.query(P).first()
with db.transaction:
p.id = 'P3'
p.cc
# issues spurious updates, throws ConcModError
--
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.
--
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.
