Re: [sqlalchemy] Locked file when trying to copy
See
http://groups.google.com/group/sqlalchemy/browse_thread/thread/aa9c753384532e6c/8d070ff7208494b1
The solution though I believe is just:
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
to_engine = create_engine('sqlite:///%s' % temp_file_name, poolclass=NullPool)
Lance
On Mon, 2010-08-16 at 21:08 +0200, Jeroen Dierckx wrote:
Hello all,
I am trying to export data from a MySQL database to a sqlite database
using SqlAlchemy.
I am using 2 engines for each database ( from and to ).
This is the part that creates the sqlite engine:
to_engine = create_engine(u'sqlite:///%s'%temp_file_name)
to_meta_data = MetaData()
to_meta_data.bind = to_engine
Then i move the tables from one engine to the other.
I close the connections;
to_connection.close()
Finally i want to copy the sqlite file to its permanent location using
shutil.move. But here it goes wrong; the file seems to be locked
still:
shutil.move(temp_file_name, self._filename)
I get this error (might not be verbatim):
WindowsError: [Error 32] The process does not have access to the file
because the file is being used by another process.
Can anyone give direction as to why the file might still be locked and
how to avoid it?
Thanks in advance,
Best regards,
Jeroen
--
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] How to filter by date with SA 0.6?
On Mon, 2010-08-16 at 13:01 -0700, Italo Maia wrote: How's the best way to filter a date field by year? Maybe something like: from sqlalchemy import and_ import datetime relevant_year = 1978 query = session.query(MyClass).filter(and_( MyClass.my_date = datetime.date(relevant_year, 1, 1), MyClass.my_date datetime.date(relevant_year + 1, 1, 1), )) I wouldn't expect the database back-end to matter on this one but I could be wrong. Lance -- 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: open session blocks metadata create_all method
On Wed, 2010-07-28 at 11:33 +, Faheem Mitha wrote: On Wed, 28 Jul 2010 15:17:09 +0530 (IST), Faheem Mitha fah...@email.unc.edu wrote: Hi, When calling create_all on a metadata instance after a session has alrady been opened causes the create_all to hang, I assume because the session is blocking the create_all. Is there some way to get create_all to use the existing session, or any other graceful way around this? Thanks. I guess another option is to close and then reopen the session after the create_all has been called, but I'd prefer not to do that if possible. Puting a session.close() before the create_all fixes the problem. I assume this means that create_all doesn't work in the middle of a transaction, or something like that? I can't speak to the underlying mechanics of create_all(), but calling session.close() prior to create_all() would work, as you say. Another option would be to simply not use a session, but instead just a *connection*. Sessions are specific to the ORM which, according to the code you posted, you are not using. So if you really just need to make a SELECT call to a table, then instead of creating a session and calling .execute() on it, you could instead do this: db = create_engine(dbstring) meta.bind = db db.echo = 'debug' make_foo(meta) meta.create_all() db.connect().execute(select * from foo;) make_bar(meta) meta.create_all() Lance -- 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: open session blocks metadata create_all method
On Wed, 2010-07-28 at 17:17 +, Faheem Mitha wrote: Hi Lance, On Wed, 28 Jul 2010 06:45:30 -0500, Lance Edgar lance.ed...@gmail.com wrote: --=-dKyzuPx4woj1H0B5IT48 Content-Type: text/plain; charset=ISO-8859-1 On Wed, 2010-07-28 at 11:33 +, Faheem Mitha wrote: On Wed, 28 Jul 2010 15:17:09 +0530 (IST), Faheem Mitha fah...@email.unc.edu wrote: Hi, When calling create_all on a metadata instance after a session has alrady been opened causes the create_all to hang, I assume because the session is blocking the create_all. Is there some way to get create_all to use the existing session, or any other graceful way around this? Thanks. I guess another option is to close and then reopen the session after the create_all has been called, but I'd prefer not to do that if possible. Puting a session.close() before the create_all fixes the problem. I assume this means that create_all doesn't work in the middle of a transaction, or something like that? I can't speak to the underlying mechanics of create_all(), but calling session.close() prior to create_all() would work, as you say. Another option would be to simply not use a session, but instead just a *connection*. Sessions are specific to the ORM which, according to the code you posted, you are not using. So if you really just need to make a SELECT call to a table, then instead of creating a session and calling .execute() on it, you could instead do this: db = create_engine(dbstring) meta.bind = db db.echo = 'debug' make_foo(meta) meta.create_all() db.connect().execute(select * from foo;) make_bar(meta) meta.create_all() The example was just an example. After going back and forth a bit, I've finally standardized on session as the thing to more around in my application. The db.connect thing works, I think, because autocommit is the default for connect. I'd like to hear an explanation of why create_all is blocked here. I periodically have my scripts hang for no apparent reason, almost always because the db is blocking something, so would like to become more educated on this issue. Are your sessions contextual (created with scoped_session()) ? Not sure what kind of project you're working on (i.e. if you need a contextual session or not), but I use sessions and also have to create tables on the fly occasionally...but my sessions aren't contextual and I always create and close them immediately when finished. See When do I make a Session ? at http://www.sqlalchemy.org/docs/session.html#frequently-asked-questions But I also apologize if I'm telling you nothing new, certainly don't mean to insult. Just trying to help. I assume Michael will have to explain the blocking thing, but FWIW I couldn't reproduce that issue while using SQLite or MySQL, so it might have just as much to do with PostgreSQL as anything else...whether that means its SA dialect or the server configuration itself I couldn't say. Lance -- 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: SQLAlchemy NotImplementedError when doing a metadata create_all
On 7/26/2010 4:55 PM, Faheem Mitha wrote:
Hi,
It turns out my example was too hasty. I should have had something like
foobar = Table(
'foo', meta,
Column('id', Integer, nullable=False, primary_key=True),
)
bar = Table(
'bar', meta,
Column('id', None, ForeignKey('foo.id', ,
), nullable=False, primary_key=True),
)
baz = Table(
'baz', meta,
Column('id', None, ForeignKey('bar.id', ,
), nullable=False, primary_key=True),
)
which also gives the same error. Using None type for ForeignKeys here,
per the docs. My previous example was using None for a col that was not
a ForeignKey.
I had a similar question a little while back and here was the answer:
http://groups.google.com/group/sqlalchemy/msg/89efcb84f25dcd28
Basically you shouldn't use None for a ForeignKey's type anymore.
Also, replacing the type of bar.id with Integer gives the
error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) there is no unique
constraint matching given keys for referenced table "bar"
'\nCREATE TABLE baz (\n\tid INTEGER NOT NULL, \n\tPRIMARY KEY (id),
\n\t FOREIGN KEY(id) REFERENCES bar (id) ON DELETE CASCADE ON UPDATE
CASCADE\n)\n\n' {}
which is not very encouraging either. Currently trying to get sqla to
emit the SQL for create_all so I can see what it is trying to do.
I added
db.echo = True
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.getLogger('sqlalchemy.orm.unitofwork').setLevel(logging.DEBUG)
but only the
db.echo = True
does something, and that doesn't emit the SQL for creating the tables.
I'm not sure what the problem here is, unless perhaps fiddling with the
logging setup after enabling db.echo might be changing the output to
not include the SQL commands? All I really know about this is that if
I set db.echo = True (actually I usually pass echo=True to the
create_engine() call) then that's *all* I have to do to get SQL
commands output to the console. Perhaps someone else could clarify or
maybe you could provide a complete example?
Lance
On Tue, 27 Jul 2010, Faheem Mitha wrote:
Dear SQLAlchemists,
With the following script, I get the traceback below. This is not the
actual example I ran into, but a small test case. To my surprise, I was
able to reproduce the identical error almost immediately. In my actual
code, I was able to work around this error by doing a table.create() on
the first table followed by a create_all, but that doesn't seem to work
with my small example.
This is SQLAlchemy 0.5.8-1 running on Debian lenny with python 2.5.2-3,
and with PostgreSQL 8.4.2-1~bpo50+1. I'm considering moving to 0.6 but
am concerned about breakage.
This seems pretty innocuous. Clarifications appreciated.
Regards, Faheem.
***
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy import MetaData
meta = MetaData()
foo = Table(
'foo', meta,
Column('id', None, nullable=False, primary_key=True),
)
bar = Table(
'bar', meta,
Column('id', None, ForeignKey('foo.id', ,
), nullable=False, primary_key=True),
)
dbuser =
password =
dbname =
dbstring = "postgres://%s:%...@localhost:5432/%s"%(dbuser, password,
dbname)
from sqlalchemy import create_engine
db = create_engine(dbstring)
meta.bind = db
meta.create_all()
Session = sessionmaker()
session = Session(bind=db)
**
Traceback (most recent call last):
File "stdin", line 23, in module
File "/usr/lib/pymodules/python2.5/sqlalchemy/schema.py", line 1811,
in create_all
bind.create(self, checkfirst=checkfirst, tables=tables)
File "/usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py", line
1129, in create
self._run_visitor(self.dialect.schemagenerator, entity,
connection=connection, **kwargs)
File "/usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py", line
1158, in _run_visitor
visitorcallable(self.dialect, conn, **kwargs).traverse(element)
File "/usr/lib/pymodules/python2.5/sqlalchemy/sql/visitors.py", line
89, in traverse
return traverse(obj, self.__traverse_options__, self._visitor_dict)
File "/usr/lib/pymodules/python2.5/sqlalchemy/sql/visitors.py", line
200, in traverse
return traverse_using(iterate(obj, opts), obj, visitors)
File "/usr/lib/pymodules/python2.5/sqlalchemy/sql/visitors.py", line
194, in traverse_using
meth(target)
File "/usr/lib/pymodules/python2.5/sqlalchemy/sql/compiler.py", line
831, in visit_metadata
self.traverse_single(table)
File "/usr/lib/pymodules/python2.5/sqlalchemy/sql/visitors.py", line
79, in traverse_single
return meth(obj)
Re: [sqlalchemy] session get last row id?
On 7/24/2010 8:21 AM, manman wrote: table A,B B.a_id=A.id my code like this: new_a=A() session.begin() session.add(new_a) new_b=B() new_b.a_id=new_a.id session.add(new_b) try: session.commit() except: session.rollback() raise why new_b.a_id is None? how to do? I presume a.id is an autoincrement field, in which case it isn't given a value until a session.flush() occurs. So this should work: new_a = A() session.begin() session.add(new_a) session.flush() new_b = B() new_b.a_id = new_a.id session.add(new_b) session.commit() session.flush() happens automatically as part of session.commit(), but an explicit one is needed for the autoincrement ID to be generated ahead of time in this case. Also if you set up a relationship between the classes you could avoid the problem as well: mapper(A, a_table) mapper(B, b_table, properties=dict(a=relation(A))) new_a = A() session.begin() session.add(new_a) new_b = B() new_b.a = new_a session.add(new_b) session.commit() Note the object assignment instead of assigning an ID this time. Lance -- 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 get last row id?
On Sat, 2010-07-24 at 07:06 -0700, manman wrote: thanks. if not use relation then how to do? i hate use ForeignKey or ManyToMany. So did the explicit call to session.flush() not work? Here it is again for reference: new_a = A() session.begin() session.add(new_a) session.flush() # note this line new_b = B() new_b.a_id = new_a.id session.add(new_b) session.commit() This doesn't require changing anything about the structure you already had in place. Lance -- 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 get last row id?
On Sat, 2010-07-24 at 08:06 -0700, manman wrote: new_a=A() session.begin() session.add(new_a) session.flush() new_b=B() new_b.a_id=new_a.id session.add(new_b) try: session.commit() except: session.rollback() raise this code is right? when error all be rollback? Should be, yes. You may verify by throwing an exception yourself: new_a = A() session.begin() session.add(new_a) session.flush() new_b = B() new_b.a_id = new_a.id session.add(new_b) try: raise Exception(commit hasn't happened, so neither new_a nor new_b should be persisted after the rollback) session.commit() except: session.rollback() raise Lance -- 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] object dirtiness from the session's perspective?
On 7/8/2010 3:23 AM, Chris Withers wrote: Hi All, I'm working on a variation of this recipe: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedRows ...and I've got a couple of questions about changed objects: - do objects end up in session.dirty as a result of attributes being set or changed? Setting an attribute is enough for the instance to wind up in Session.dirty, according to http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.session.Session.dirty For example: class Example(Versioned, Base): __tablename__ = 'example' id = Column(Integer, primary_key=True) data = Column(String) obj = session.query(Example).get(1) print obj.data 'something' obj.data = 'something' Is obj now considered dirty? Hopefully not, hopefully it'll only be considered dirty if the following was done: So, per the docs, yes it would be dirty. I've tested this a little in the past and I believe my experience corroborated this. obj.data = 'something else' Would both of the above result in obj being dirty or just the latter? If both, are there any hooks for affecting this behaviour? - in a SessionExtension's before_flush method, is there any way I can tell which attributes have changed? Or, almost the same, can I check some specific attributes to see if they've changed? For instances that wind up in Session.dirty, you can check each for truly dirty attributes with Session.is_modified (http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.session.Session.is_modified). That won't tell you which attributes have been modified though, only if the instance can be ignored even though it's in Session.dirty. I'm assuming if you need to know which attributes have changed then you'll have to examine the instance's state a little closer yourself, looking at the instrumented history for each attribute, etc. I've not done this though so I'm afraid that's a guess. Lance -- 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] Using the declarative base across projects
On Wed, 2010-07-07 at 11:13 -0400, thatsanicehatyouh...@mac.com wrote:
Hi,
I have a question that I can't find a satisfactory answer to. Apologies in
advance if it's more of a Python question, but it's possible that there is a
SA solution.
I have a project that defines a database connection and classes based on
database tables. A script that uses these classes would start with:
import project.DatabaseConnection as db # create_engine() and metadata
defined here
import project.ModelClasses
In ModelClasses, I define all of the table classes using:
Base = declarative_base(bind=db.engine)
class Table1(Base):
__tablename__ = 'table1'
__table_args__ = {'autoload' : True} # requires metadata to work
This is fine. I have a second project that also defines a different set of
tables that I want to use, so the main script would then:
import project2.ModelClasses # a different set of tables
If it's not clear how the db parameter (the database connection) was
defined in ModelClasses, well, that's my problem. I can't pass a parameter to
an import statement of course. The DatabaseConnection class defines the
engine and metadata, and now I need to use these objects to generate my base
class. How can I pass this object around? Should I be using a different model?
Why not just do this in project2 ?
import project.DatabaseConnection as db
Base = declarative_base(bind=db.engine)
# ... etc.
The python way seems to be to create a config class, but
project2.ModelClasses won't know anything about it if it's defined in the
first project. As to the reason why there are two separate projects, consider
the case where one set of tables is one logical group, and the second is a
replicated copy from another server. I can't merge all of these projects
since they really are independent units, but sometimes I will link them (as
above).
I don't understand why project2 wouldn't know anything about it if
defined in (first) project. All it needs to do is import the connection
info from the project (as in above example). If the database
configuration really transcends both project and project2 though, then
yes it probably could be wrapped in a config module of some sort in
another project; depending on the scope that may be a bit overkill. If
you can consider either project or project2 to be slightly more
default than the other then the db config could stay there I'd think.
Lance
--
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: remapping column type
On Jul 7, 8:56 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 7, 2010, at 1:30 AM, Lance Edgar wrote:
Hi, I was wondering what method might be used (assuming it's possible)
to redefine a column's attribute type after the mapping has already
been made? Specifically I have the following scenario:
from sqlalchemy import *
from sqlalchemy.orm import mapper
metadata = MetaData()
orders = Table(
orders, metadata,
Column(id, Integer, primary_key=True),
Column(quantity, Numeric(8,3)),
)
class Order(object):
pass
mapper(Order, orders)
# ... then later ...
from sqlalchemy.orm import class_mapper
class_mapper(Order).get_property('quantity').update_to_integer_type()
Obviously that last method doesn't exist, but I would like something
along those lines. Basically I want to type-cast the column at the
ORM layer instead of everywhere it appears in the UI. I can't just
pass Integer to the Column definition because that's happening
elsewhere in another package. Thanks in advance for any suggestions.
map it as column_property(cast(table.c.column, Integer)).
Michael, thanks for the tip. I still found this somewhat confusing
though:
When my code runs, the mapper has already been created (and
compiled, I assume). So what I ended up doing, that seemed to work,
is:
class_mapper(Order).add_property('quantity',
column_property(cast(orders.c.quantity, Integer)))
The thing is, the mapper *already* had a quantity property so
without knowing the internals of that business I can only assume that
my .add_property() call doesn't have weird side effects (although it
does accomplish what I'm after). The mapper exposes .get_property()
and .iterate_properties(), and of course .add_property(), but I guess
I would have expected there to be a .set_property()
or .update_property(). Is this just a quirk in the wording or my
understanding, or am I still missing some important step?
Lance
--
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: remapping column type
On Jul 7, 2:01 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 7, 2010, at 1:53 PM, Lance Edgar wrote:
OMichael, thanks for the tip. I still found this somewhat confusing
though:
When my code runs, the mapper has already been created (and
compiled, I assume). So what I ended up doing, that seemed to work,
is:
class_mapper(Order).add_property('quantity',
column_property(cast(orders.c.quantity, Integer)))
The thing is, the mapper *already* had a quantity property so
without knowing the internals of that business I can only assume that
my .add_property() call doesn't have weird side effects (although it
does accomplish what I'm after). The mapper exposes .get_property()
and .iterate_properties(), and of course .add_property(), but I guess
I would have expected there to be a .set_property()
or .update_property(). Is this just a quirk in the wording or my
understanding, or am I still missing some important step?
It's add_property() since you are adding a new property to the existing
collection of properties. It is not really intended for the replacement
of existing properties as that has direct impact on the instrumentation of
the mapped class. It probably works in the simple case here, but would be
better if you were to define it inline with the original Order mapper.
Well, I'm already seeing some issue(s) with it even in my simple
case. Where this used to return an Integer-type Column...
class_mapper(Order).get_property('quantity').columns[0]
...now it returns a sqlalchemy.sql.expression._Label object. This
makes sense because of the SELECT CAST(...) AS anon_1 that's
happening at the SQL level, but it's throwing FormAlchemy off in this
case and I guess generally speaking I hoped for a more transparent
override. So my follow-up question is: Is there a way to tear down
and replace the mapper for just a single class, or is the only
relevant option the clear_mappers() function which will remove all
class mappings? If I can't replace a single property within the
mapper then I'm ok with replacing the mapper, but replacing all of
them seems a bit heavy-handed.
Lance
--
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: remapping column type
On Wed, 2010-07-07 at 18:45 -0400, Michael Bayer wrote:
On Jul 7, 2010, at 6:30 PM, Lance Edgar wrote:
On Jul 7, 2:01 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 7, 2010, at 1:53 PM, Lance Edgar wrote:
OMichael, thanks for the tip. I still found this somewhat confusing
though:
When my code runs, the mapper has already been created (and
compiled, I assume). So what I ended up doing, that seemed to work,
is:
class_mapper(Order).add_property('quantity',
column_property(cast(orders.c.quantity, Integer)))
The thing is, the mapper *already* had a quantity property so
without knowing the internals of that business I can only assume that
my .add_property() call doesn't have weird side effects (although it
does accomplish what I'm after). The mapper exposes .get_property()
and .iterate_properties(), and of course .add_property(), but I guess
I would have expected there to be a .set_property()
or .update_property(). Is this just a quirk in the wording or my
understanding, or am I still missing some important step?
It's add_property() since you are adding a new property to the existing
collection of properties. It is not really intended for the
replacement of existing properties as that has direct impact on the
instrumentation of the mapped class. It probably works in the simple
case here, but would be better if you were to define it inline with the
original Order mapper.
Well, I'm already seeing some issue(s) with it even in my simple
case. Where this used to return an Integer-type Column...
class_mapper(Order).get_property('quantity').columns[0]
...now it returns a sqlalchemy.sql.expression._Label object. This
makes sense because of the SELECT CAST(...) AS anon_1 that's
happening at the SQL level, but it's throwing FormAlchemy off in this
case and I guess generally speaking I hoped for a more transparent
override. So my follow-up question is: Is there a way to tear down
and replace the mapper for just a single class, or is the only
relevant option the clear_mappers() function which will remove all
class mappings? If I can't replace a single property within the
mapper then I'm ok with replacing the mapper, but replacing all of
them seems a bit heavy-handed.
No, you have to set up the mappers the way you want them to be the first time
around. If FormAlchemy is setting up mappers for you without you being able
to change how it does that, then they should fix that.
Hm, okay then. FWIW, FormAlchemy isn't creating any mappers, but it
must be examining the properties found in them. If I don't override the
mapper's quantity property then FA will render the field as being a
writable decimal. If I do override it then FA renders it as a read-only
integer (I'm assuming this is because of the _Label being found there in
place of the Column). I need it to be a writable integer. So it sounds
like telling the ORM to cast it to an integer as part of the original
mapper definition is the ideal solution, but the project structure is
such that the mappers are created elsewhere (different project) and the
classes are imported from there.
I think I'll just fiddle with the FA rendering so as to emulate an
integer type in the UI; that was the next thing to try anyway. Thanks
again.
Lance
--
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] remapping column type
Hi, I was wondering what method might be used (assuming it's possible)
to redefine a column's attribute type after the mapping has already
been made? Specifically I have the following scenario:
from sqlalchemy import *
from sqlalchemy.orm import mapper
metadata = MetaData()
orders = Table(
orders, metadata,
Column(id, Integer, primary_key=True),
Column(quantity, Numeric(8,3)),
)
class Order(object):
pass
mapper(Order, orders)
# ... then later ...
from sqlalchemy.orm import class_mapper
class_mapper(Order).get_property('quantity').update_to_integer_type()
Obviously that last method doesn't exist, but I would like something
along those lines. Basically I want to type-cast the column at the
ORM layer instead of everywhere it appears in the UI. I can't just
pass Integer to the Column definition because that's happening
elsewhere in another package. Thanks in advance for any suggestions.
Lance
--
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] Simple example of a GROUP BY
On 7/5/2010 10:55 PM, Andrew Bialecki wrote: I've looked for a while, but I can't find the *best* way to use the ORM to generate the following query: SELECT user_id, count(*) cnt FROM orders GROUP BY user_id I have classes that represent both User and Order. So what should go after: Order.query.[fill this in] Sorry this is such an easy question, feel free to forward me to an example, I just couldn't find one. I think the following might work? from sqlalchemy import func session.query(Order.user_id, func.count(Order.user_id)).group_by(Order.user_id) Anyway a little more is to be found here: http://www.sqlalchemy.org/docs/ormtutorial.html#counting http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.query.Query.group_by Lance -- 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] Querying with case insensitive
On 6/23/2010 5:28 AM, Alexander Zhabotinskiy wrote: Hello. I'v got an problem I have a string like 'john' and I need to get results for 'john, JOHN' etc. How to do that? I think perhaps the ilike() filter operator might be the only way? It might even depend on your back-end. http://www.sqlalchemy.org/docs/ormtutorial.html#common-filter-operators http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.ColumnOperators Lance -- 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: help please
On 6/10/2010 10:29 AM, Aref wrote:
Thank you for the response. However, that is not the problem. If I do
update = table.update(project.c.ProjectID=='project-name', values =
{project.c.ProjectID:'program'})
print update
update.execute()
everything works fine.
if I do this:
test = 'table.c.'+columns[0] #columns is a list which contains the
columns names
update = table.update(test == 'project-name', values={test:'program'})
update.execute()
it does not work. I get an error that there is no such column.
I need to be able to update columns dynamically where I do not have a
prior knowledge of what tables and what are the table columns that may
exist. How can I do that if at all?
Instead try:
update = table.update(eval(test)=='project-name',
values={test:'program'})
I can't say for sure that's the best way to do it still, but it
would solve your immediate problem. The "test" variable is referencing
a string, not a column. You have to eval() it to get the column
reference.
However, you say your error is that "there is no such column" ... I'd
expect a much different error if my suggestion were to actually fix
your problem. Anyway good luck. :) Might include your traceback next
time if you still have problems.
Lance
--
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: help please
On 6/10/2010 11:22 AM, Aref wrote: Thank you very much. I'll try it. Is there a better way of doing this-- I mean there must be since this is necessary for any application needing to modify a database where generally tables are accessed dynamically. Well, I suspect the "better way" would be to follow King Simon's advice (http://groups.google.com/group/sqlalchemy/msg/b8c0f6bce263ff3d?hl=en) and avoid the eval() stuff as well. Instead you'd be using getattr() or accessing the table.c collection dictionary-style. Lance -- 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: help w/ creating complex query
On 6/1/2010 7:18 AM, nospam wrote:
I see significant slow down if I have 1000 items, and a couple of
annotations for each one. That's a 1001 queries I need to make, so I
want to avoid the for loop there - and just have 1 query that returns
me the list of correct annotation objs.
I think this is basically the give me the last order each user
placed problem, but w/ more complexity around the last order
definition.
http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html
I'm not as hip on the whole selectable stuff and where it should be
used in place of the ORM, since I usually want to interact with objects
instead of rows, but the following will hopefully get you closer to your
goal (using the ORM):
orm.mapper(
Item, items,
properties = dict(
annotations = orm.relation(Annotation,
order_by = [annotations.c.creation_datetime.desc()],
primaryjoin = sa.and_(annotations.c.item_id == items.c.id,
annotations.c.type.in_(('x','y'))),
lazy = False,
),
),
)
So this does a couple of things...first of all the /opposite/ of
lazy='select' since apparently you want the annotations loaded via the
same query that pulls in the items; also it filters the annotation.type
to only include ('x', 'y'). BTW also I used lazy=False instead of
lazy='joined' - I hadn't previously realized the SA 0.5 vs 0.6
difference with regard to that (lazy=False will work the same for both).
Obviously one drawback is that the annotations property is now
restricted with the type filter, so if you ever /did/ want to see all
the possible annotations then you'll need to create a second property
for that. Also, I personally am not familiar enough to say whether or
not this mapping should specify viewonly=True ... I'd think so but the
docs suggest complicated joins may not compile properly? Not sure what
that means specifically.
Lance
--
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: help w/ creating complex query
On 5/30/2010 7:36 AM, nospam wrote:
Thanks Lance. Would this lazily load the annotations for each item?
I'm trying to avoid # of item trips to the db, and also avoid
loading all the annotations.for each item. I'm thinking I can do w/
some joins, and subquery()'s...
This would load the annotations lazily in that they would not be fetched
along with each item initially, but they would be as soon as you
reference item.annotations in code. This behavior is default, but you
can explicitly state it in your mapper like so:
orm.mapper(
Item, items,
properties = dict(
annotations = orm.relation(Annotation,
order_by=[annotations.c.creation_datetime.desc()], lazy='select'),
),
)
More on the relationship mapping is at
http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm.relation
.
However, it sounds like you need to know about the annotations within
your item loop anyway, so I'm not clear what you look to save yourself
in terms of # of item trips to the db? Just a suggestion, but I think
improving the speed of things might be easier later, and possibly even
unnecessary. Or it could be that I'm just not understanding what you're
after here. ;)
Also remember you can pass echo=True when creating your engine, e.g.:
my_engine = sa.create_engine('sqlite:///', echo=True)
Then you'll see exactly what SQL is being issued to your database and in
what order, etc.
(http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html#sqlalchemy.create_engine)
Lance
--
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: engine bound to Session
On 5/30/2010 9:43 AM, Eric Lemoine wrote: On Sun, May 30, 2010 at 4:39 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hello I use Pylons. Pylons does: Session = scoped_session(sessionmaker()) and then: Session.configure(bind=engine) My question: with a reference to Session how can I get the engine that's bound to it? I tried Session.get_bind() but I get this error: TypeError: get_bind() takes at least 2 arguments (1 given). Session.get_bind(mapper=None) seems to do the trick, but I'm not sure this is the good way. Could someone confirm? Thanks again. Not sure if this is proper, but I've always just used Session.bind. Then again I've really only used it when testing things out, because at runtime my engines are defined in such a way that I never need to discover them through the Session. Lance -- 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] Initializing ORM objects manually
On 5/30/2010 5:49 PM, Chris C wrote: I'm hoping to write a Python package which integrates Sphinx Search (open-source SQL full-text search) and SQLAlchemy. Unfortunately, I don't have much insight into the internals of SQLAlchemy (though I've been reviewing the documentation/source trying to understand more..) Once I return a resultset, how should I convert a tuple representing a row of the resultset to an ORM object? Does anyone know of any open-source implementations of generating SQLAlchemy ORM objects from tuples which I can use as a reference? Unclear why you're retrieving a resultset of tuples (and wanting to convert them to objects later) instead of just retrieving the objects directly? See http://www.sqlalchemy.org/docs/ormtutorial.html for more on the ORM. Lance -- 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] Mapper can't map primary key
On 4/28/2010 11:31 PM, Mark wrote:
Hi guys,
I have the following Table construction:
ADMIN_TABLE = Table('admin',
bound_meta_data,
Column('username', types.VARCHAR(100),
primary_key=True),
autoload=True, schema=schema)
and a mapper as such:
mapper(Admin, TABLES.ADMIN_TABLE,
properties={'employee':
relation(Employee, primaryjoin=
TABLES.ALL_EMPLOYEES_TABLE.c.employee_id==\
TABLES.ADMIN_TABLE.c.employee_id,
foreign_keys=[TABLES.ADMIN_TABLE.c.employee_id],
backref=backref('user',
foreign_keys=
[TABLES.ADMIN_TABLE.c.employee_id],
lazy=dynamic)
)
},
extension = VerificationMapper()
)
When I run paster serve --reload development.ini in my Pylons app, I
get an irritating error complaining the following:
sqlalchemy.exc.ArgumentError: Mapper Mapper|Admin|admin could not
assemble any primary key columns for mapped table 'admin'
As you can see above, I have already mapped the primary_key=True
property, why is it still complaining that it can't find the primary
key? With this error, I tried out something else, adding the code
below to my mapper configuration:
primary_key=[TABLES.ADMIN_TABLE.columns.username]
Adding this, allowed me to run the server properly, however, when I
query the database, it claims that it is unable to locate the username
column. I am very sure my database is correct and this is definitely
an issue with my SQLAlchemy code.
Can someone please explain what's going on? Why do I get the
exception? Thanks.
This is just a guess I'm afraid, but could it be the autoload=True you're
passing to the admin Table constructor? From the docs:
Usually there will be no Column objects in the constructor if this property is
set.
Do you really mean to reflect everything _except_ the username column?
Lance
--
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] select .group_by
On 4/27/2010 5:22 AM, jo wrote:
Hi all,
In version 0.6 seems the group_by property does nothing...
(Pdb) sql =
select([Verifica.c.codice,func.sum(Prestazione.c.importo).label('importo')])
(Pdb) print sql
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione
(Pdb) sql.group_by(Verifica.c.codice)
sqlalchemy.sql.expression.Select at 0x706b6d0; Select object
(Pdb) print sql
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione
(Pdb)
I expected a query like this:
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione
GROUP BY verifica.codice
How it works in 0.6?
I believe the group_by() method will return a new selectable instead of
changing your current one in-place. So perhaps try:
sql_grouped = sql.group_by(Verifica.c.codice)
print sql_grouped
Lance
--
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] Cascading orphan deletes in self-referential table
On 4/25/2010 6:39 AM, Adrian wrote:
class MenuNode(Base):
__tablename__ = 'menu'
id = Column(Integer, primary_key=True, nullable=False,
unique=True)
parent_id = Column(Integer, ForeignKey('menu.id',
onupdate='CASCADE', ondelete='CASCADE'), nullable=True, index=True)
name = Column(String(64), nullable=False)
parent = relationship('MenuNode', remote_side='MenuNode.id',
cascade='all, delete', passive_deletes=True,
backref=backref('children', order_by=position))
Top-level menu nodes have parent_id=NULL.
When deleting a node via session.delete(node) sqlalchemy issues UPDATE
statements to set all children's parent_id fields to NULL instead of
deleting the children.
How can i tell SA that I want to delete any children?
Does specifying cascade='all, delete-orphan' on the parent relationship
accomplish what you're after?
Lance
--
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] about commit()
On 4/23/2010 9:19 AM, jose soares wrote:
jo wrote:
Hi all,
I need to insert a new row and get back the last inserted id,
I have some difficulty using the flush(), then I'm trying with
commit() but
I can't understand how commit() works in 0.6.
In the following script I try to update a row and it works properly
but when I try to insert a new one, it doesn't work,
there's no messages but the row is not inserted into the table.
Is this the right way ?
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(autoflush=True)
session = Session()
#update an existing row... it works
old = Specie.get('D')
old.specie_descrizione='dog'
#insert a new row... it doesn't work
new=Specie(
specie_codice='C',
specie_descrizione='cat'
)
session.commit()
thanks for any help
j
in my disperation, I tried also the following, but without success: :-(
from sqlalchemy.orm.session import Session
session=Session(autoflush=True,autocommit=True)
class Gruppo:
pass
mapper(Gruppo,
tbl['gruppo'],
column_prefix = 'gruppo_',
)
session.begin()
sqlalchemy.orm.session.SessionTransaction object at 0x28a9710
new=Gruppo( gruppo_id = 1, gruppo_descrizione='cat')
session.commit()
print Gruppo.get(1)
SELECT gruppo.id AS gruppo_id, gruppo.descrizione AS gruppo_descrizione
FROM gruppo
WHERE gruppo.id = %(param_1)s
Col ('gruppo_id', 'gruppo_descrizione')
None
I don't understand what's wrong. I can't INSERT a new record into a table.
Could someone, give me some help?
Would this (not) work?
from sqlalchemy import *
from sqlalchemy.orm import mapper
metadata = MetaData()
groups = Table('groups', metadata, Column('id', Integer, primary_key=True),
Column('name', String(25)))
class Group(object):
pass
mapper(Group, groups)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
session = Session()
group = Group()
group.name = 'cat'
session.add(group)
session.commit()
session.expunge_all()
group = session.query(Group).first()
print group.id
session.close()
--
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] about commit()
Sorry, I'd meant for that code to be self-contained but of course I forgot to
set up the engine. As an example you can try the following (see inserted code
below).
Lance
On 4/23/2010 9:50 AM, jose soares wrote:
session.commit() raises an UnBoundExecutionError:
Would this (not) work?
from sqlalchemy import *
from sqlalchemy.orm import mapper
engine = create_engine('sqlite:///:memory:')
metadata = MetaData()
groups = Table('groups', metadata, Column('id', Integer,
primary_key=True), Column('name', String(25)))
metadata.create(bind=engine)
class Group(object):
pass
mapper(Group, groups)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
session = Session()
session.bind = engine
group = Group()
group.name = 'cat'
session.add(group)
session.commit()
UnboundExecutionError: Could not locate a bind configured on mapper
Mapper|Group|groups or this Session
session.expunge_all()
group = session.query(Group).first()
print group.id
session.close()
--
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] list filter
On 4/23/2010 9:33 AM, Alexander Zhabotinskiy wrote: Hello. How to filter by list may be like: .filter(Some.value==[1,2,3]) I believe you want the IN filter; see http://www.sqlalchemy.org/docs/ormtutorial.html#common-filter-operators. Lance -- 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] custom dialect with no join syntax support possible?
Hi, I'm writing a new custom dialect for a legacy database (Centura SQLBase 7.5.1) for use in migrating to a new system over time. Everything's gone pretty well, until I needed a join... Whereas most dialects would create a statement such as: SELECT T1.ID, T1.COL1, T2.COL2 FROM T1 JOIN T2 ON T1.ID = T2.ID WHERE T1.ID = 100 , (at least this flavor of) SQLBase expects it to be like so: SELECT T1.ID, T1.COL1, T2.COL2 FROM T1, T2 WHERE T1.ID = T2.ID AND T1.ID = 100 And in fact JOIN isn't even one of their reserved words, so sending it a statement like the first example will of course cause an error. I've subclassed sqlalchemy.sql.compiler.SQLCompiler in the hopes of overriding the visit_join method for my dialect, but I'm not sure it's possible to achieve what I'm after this way? I can of course replace the JOIN text with , but if I replace ON with WHERE then all of a sudden the final statement has two WHERE clauses and is thus invalid for a whole new reason. Is there a way to override the visit_join method to accomplish my goal or should I be looking somewhere else? (I assume I can add custom @properties to my data class, for instance. I'd like to solve the bigger problem here but if I can't then I really just need a way past this particular problem.) TIA, I really appreciate any help. Lance -- 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.
