RE: [sqlalchemy] Possible bug with subqueryload
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 27 September 2011 19:37 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Possible bug with subqueryload On Sep 27, 2011, at 1:16 PM, King Simon-NFHD78 wrote: Great, thanks a lot :-) I only discovered it in a toy application, and the workaround (including order_by on the query) is not a problem. In this toy application, I was also wondering if there existed a mechanism for doing some sort of lazy subqueryload. ie. I'm loading a collection of objects and I don't know ahead of time if I'm going to access a particular relationship (so I don't want to eagerload it). However, if I *do* access it, I'm going to access it on each object in the collection, so I'd like to load all the related objects in a single hit. It's just like a subqueryload, except it is only executed when the relationship is accessed for the first time. Is that a silly idea? Or perhaps it already exists and I've missed it. It actually exists in Hibernate, but not for us.We do sort of have the infrastructure in place to make it possible, i.e.the subqueryload right now prepares a Query object at query time that fires off during load time, with a mapper option it would need to stick it as some kind of memo in each InstanceState, it would be very tricky to implement.Keeping that state and keeping it plugged into the InstanceStates, then what if the loader was fired after many of the other states have been garbage collected, just a lot of corner cases to deal with. it can be handrolled of course, the general technique when you want to construct objects such that they appear loaded is to use attributes.set_commited_value() to set an attribute such that the ORM sees it as what was loaded from the database. an example of that is where we first introduced the subquery concept here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading Ah yes, set_committed_value is exactly the sort of thing I was looking for. Thanks a lot, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Possible bug with subqueryload
Hi,
I think there may be a bug in the interaction between 'subqueryload' and
having a default 'order_by' defined on a mapped class. When the subquery
is run, it looks like the ORDER BY is being placed on the outer query,
whereas it should be on the inner query. The full test case is below,
but here are the 2 queries (produced using hg revision 62e97372a028):
Main query
--
SELECT master.id AS master_id, master.dummy AS master_dummy
FROM master
ORDER BY master.id DESC
LIMIT 2 OFFSET 0
Subquery
SELECT detail.id AS detail_id, detail.master_id AS detail_master_id,
anon_1.master_id AS anon_1_master_id
FROM (SELECT master.id AS master_id
FROM master
LIMIT 2 OFFSET 0) AS anon_1
JOIN detail ON anon_1.master_id = detail.master_id
ORDER BY anon_1.master_id
Since the ORDER BY is not on the inner query, a different set of
'master' rows is referenced than in the main query.
Cheers,
Simon
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Master(Base):
__tablename__ = 'master'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
dummy = sa.Column(sa.Integer)
__mapper_args__ = {'order_by': sa.desc(id)}
class Detail(Base):
__tablename__ = 'detail'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
master_id = sa.Column(sa.Integer, sa.ForeignKey(Master.id))
master = saorm.relationship(Master, backref='details')
def test():
dburi = 'sqlite://'
engine = sa.create_engine(dburi, echo=True)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
session = saorm.create_session(bind=engine)
session.begin()
# Insert 5 masters, each with 1 detail
for i in range(5):
master = Master(dummy=i)
master.details.append(Detail())
session.add(master)
session.commit()
session.close()
# Load back 2 masters, using subqueryload to load the detail
# rows. If you uncomment the '.order_by' line here, the test
# passes.
master_query = (session.query(Master)
#.order_by(sa.desc(Master.id))
.limit(2)
.options(saorm.subqueryload('details')))
# Display the details for each master
for item in master_query:
print 'Master %s: %s' % (item.id, item.details),
if len(item.details) == 0:
print 'FAIL'
else:
print 'PASS'
if __name__ == '__main__':
test()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Possible bug with subqueryload
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 27 September 2011 16:24 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Possible bug with subqueryload Hi Simon - yeah that looks pretty buglike to me, mapper.order_by is not a frequently used feature so this one may need some adjustment. I've created http://www.sqlalchemy.org/trac/ticket/2287 to take a look at this and so far I'm targeting it at 0.6.9/0.7.3. Great, thanks a lot :-) I only discovered it in a toy application, and the workaround (including order_by on the query) is not a problem. In this toy application, I was also wondering if there existed a mechanism for doing some sort of lazy subqueryload. ie. I'm loading a collection of objects and I don't know ahead of time if I'm going to access a particular relationship (so I don't want to eagerload it). However, if I *do* access it, I'm going to access it on each object in the collection, so I'd like to load all the related objects in a single hit. It's just like a subqueryload, except it is only executed when the relationship is accessed for the first time. Is that a silly idea? Or perhaps it already exists and I've missed it. Thanks, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Bulk creation of columns
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of pravin battula
Sent: 21 September 2011 12:54
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Bulk creation of columns
Hi,
How can i create columns in bulk using create_column method?
I tried as below,.
migrate_engine = create_engine('mysql://root:root@localhost/
payroll', echo=False)
metadata = MetaData(bind = migrate_engine)
metadata.reflect(bind = migrate_engine, schema = 'payroll')
tableObj = metadata.tables.get('test.salary')
colList =
[Column('description',String(100)),Column('information',String(50))]
tableObj.append_column(*colList)
tableObj.create_column(*colList)
getting an error as TypeError:create() got multiple values for
keyword argument 'table'
Please do the needful.
create_column isn't an SQLAlchemy method as far as I know. Are you using
something like sqlalchemy-migrate
(http://code.google.com/p/sqlalchemy-migrate/)?
If so, you'll probably get more help on their mailing list.
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: Error while using CAST
Does this work instead:
table.update().values(empno = cast(table.c.empno,Integer)).execute()
ie. a bare 'empno' inside your cast expression is just referring to a python
variable 'empno', which you've probably set to the value 'testing' at some
other point in your code. You need the column object table.c.empno instead
Hope that helps,
Simon
pravin battula wrote
Mike,
when i execute the below sql statement directly in the database using
sqlyog,it works fine but when tried with sqlalchemy it didn't.
update mytable set EmpMaster = cast(empno as UNSIGNED INTEGER)
On Sep 14, 8:23 pm, pravin battula pravin.batt...@gmail.com wrote:
Hi Mike,
I'm using Mysql 5.0 backend
On Sep 14, 8:20 pm, Mike Conley mconl...@gmail.com wrote:
Don't know what database you are using, but this looks like you
are trying
to cast the string 'testing' to an integer and the database
engine says you
can't do that.
--
Mike Conley
On Wed, Sep 14, 2011 at 9:51 AM, pravin battula
pravin.batt...@gmail.comwrote:
Sorry for the spelling mistake.It shows an error as below.
OperationalError: (OperationalError) (1292, Truncated
incorrect
INTEGER value: 'testing') 'UPDATE test.mytable SET
`newColumn`=CAST(test.mytable.`empno` AS SIGNED INTEGER)' ()
On Sep 14, 6:48 pm, pravin battula pravin.batt...@gmail.com
wrote:
Hi,
I'm using cast to update values in a table by issuing
following
command.
table.update().values(empno = cast(empno,Integer)).execute().
Where as empno is an string field,i'm trying to convert the
data from
empno column from string to integer and then issuing the
below command
to alter the data type of the column by issuing following
command.
alter_column(table.c.empno,type=Integer).
It shows an error as
OperationalError: (OperationalError) (1292, Truncated
incorrect
INTEGER value: '1d') 'UPDATE test.mytable SET
`newColumn`=CAST(CAST(%s AS CHAR) AS SIGNED INTEGER)' ('1d',)
Please do the needful
--
You received this message because you are subscribed to the
Google Groups
sqlalchemy group.
To post to this group, send email to
sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] data driven schema in sqlalchemy
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of espresso maker Sent: 05 August 2011 06:19 To: sqlalchemy Subject: [sqlalchemy] data driven schema in sqlalchemy Hi there, I have a data driven database schema that I am trying to implement in sqlalchemy. Here's how the tables look like: user user_id | | user_properties property_id | property_name | property_description user_properties_data user_id | property_id | property_value What I would like to do eventually is if I have u = User() , u. [some_propery_name] return the property_value if it exist for that user. Any suggestions on how to implement this? There's an example of something like this in the SQLAlchemy repository: http://www.sqlalchemy.org/docs/orm/examples.html#vertical-attribute-mapp ing http://hg.sqlalchemy.org/sqlalchemy/file/3e75f284f253/examples/vertical I don't think it's exactly what you've described, but hopefully it's a starting point. Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] update existing row
vitsin wrote:
hi,
can't figure out why raw SQL works fine, but update() is not working:
1.working raw SQL:
self.session.execute(update public.my_table set
status='L',updated_at=now() where my_name='%s' % (self.my_name))
2.non working update() from Alchemy:
s = aliased(MyTable)
query = self.session.query(s).filter(s.my_name==self.my_name)
sts = self.session.execute(query).fetchone()
sts.update(values={'status':'L'})
sts.update(values={s.status:'L'})
File /usr/lib/python2.6/dist-packages/sqlalchemy/engine/base.py,
line 2097, in _key_fallback
Could not locate column in row for column '%s' % key)
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for
column 'update'
But Column s.status exists ...
appreciate any help,
--vs
In your example, 'sts' represents a single row from the database. These
objects don't have an 'update' method, which is why you are getting that
error. It thinks you are trying to access a column called 'update'
instead.
You appear to be using the SQL Expression language (ie. MyTable is
created using sqlalchemy.Table). You can create an 'update' statement
using MyTable.update(). Examples are at:
http://www.sqlalchemy.org/docs/core/tutorial.html#inserts-and-updates
(You should be able to substitute conn.execute() with session.execute())
However, you might be interested in using the ORM part of SQLAlchemy:
http://www.sqlalchemy.org/docs/orm/tutorial.html
Your usage would then look something like this (assuming MyMappedClass
is the class mapped to MyTable):
s = MyMappedClass
query = self.session.query(s).filter(s.my_name == self.my_name)
sts = query.first()
sts.status = 'L'
self.session.flush()
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Updating records in table not working
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of jos.carpente...@yahoo.com Sent: 26 July 2011 18:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Updating records in table not working I'm using Postgres as a database. I try to create new records or update existing records with data. The data is parsed from a csv file. Creating new records works fine. But when a record already exists, the update fails with: IntegrityError: (IntegrityError) duplicate key value violates unique constraint stock_item_pkey I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' I've also tried updata, but that fails too and als mentions a depreciated statement. The new data is going to a single table. The PrimaryKey is the item number (item with value itemno in snippet below). Since the item is unique, I don't let Postgres create an id. new = Item(item=itemno, ...) db.session.add(new) db.session.commit() I'm pretty new with SA and I might overlook something. How can I solve this? I *think* you should be able to use session.merge instead: http://www.sqlalchemy.org/docs/orm/session.html#merging temp = Item(item=itemno, ...) new = db.session.merge(temp) db.session.commit() (note that 'merge' returns a new object attached to the session) Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: RE: [sqlalchemy] Updating records in table not working
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Gunnlaugur Briem Sent: 27 July 2011 10:36 To: sqlalchemy@googlegroups.com Subject: Re: RE: [sqlalchemy] Updating records in table not working On Wednesday, 27 July 2011 08:23:14 UTC, Simon King wrote: I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' Only for brand new instances, not associated with a session. For *detached* instances the identity is known and the instances will be in session but not in session.new, so an UPDATE will be issued. Regards, - Gulli Ah, I see. Thanks for the clarification. Cheers, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] engine.echo not working as expected
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Mike Conley
Sent: 27 July 2011 17:43
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] engine.echo not working as expected
Under 0.5 I was able to turn echo on and off as desired to support
debugging; it doesn't seem to work now.
Python version: 2.7.1
SQLAlchemy version: 0.7.1
Here's the code:
from sqlalchemy import *
eng1 = create_engine('sqlite:///')
meta1 = MetaData(bind=eng1)
tab_a = Table('x', meta1,
Column('id',Integer, primary_key=True))
meta1.create_all()
conn = eng1.connect()
conn.execute(tab_a.insert())
x=conn.execute(select([tab_a])).fetchone()
eng1.echo=True
conn.execute(tab_a.delete().where(tab_a.c.id==x.id))
Under 0.5.8 The SQL for the delete is echoed, under 0.7 (and I think
0.6) it is not.
If I move the echo=True before the select, both the select and delete
are echoed.
It looks like there might be a subtle difference since 0.5 that keeps
the logging from taking effect immediately when echo is changed.
P.S. Now as I try to reverify it, I have to move the echo=True all
the way before the connect() to get it to echo.
This is explained in the note at the bottom of
http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging.
(not that that necessarily helps you, but it does at least say that it
is expected behaviour)
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] how to get last record from a resultset
If you don't have something consistent to sort by, then I'm not sure that the last record is meaningful, is it? If you have 10 rows with the same voucher code and account code (and there is nothing else to uniquely identify them, such as a more precise timestamp, or an auto-incrementing ID), then as far as the result set is concerned, there is nothing special about the last row. The database could be giving them to you in any order. I'm sure I'm misunderstanding your situation - perhaps you could describe your schema and why you need this information, and then we might be more help. Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Krishnakant Mane Sent: 20 July 2011 15:16 To: sqlalchemy@googlegroups.com Cc: Timuçin Kızılay Subject: Re: [sqlalchemy] how to get last record from a resultset Well, there won't be a consistent result using sort because there might be 10 rows with same voucher code and same account code. That's exactly the challenge so I don't know how sort will help. If we can invert the entire resultset having the last record become first, then its worth while. But again, I don't want the entire set of rows in the first place. I just want that particular row. Happy hacking. Krishnakant. On 20/07/11 19:20, Timuçin Kızılay wrote: I think, reversing the sort and getting the first record will do. 20-07-2011 16:32, Krishnakant Mane yazmış: Hello all, Subject line says it all. Basically what I want to do is to get last record from a result set. I am dealing with a situation where given a date I need to know the last record pertaining to transaction on a given account. yes, it is an accounting/ book keeping software. So I thought there was some thing like .last() method for a resultset? Or even better do we have some thing like session.query(table).last() The problem is that my logic is in place but I know that performance wise it is very dirty to get the list of all records, just to loop till the end and throw away all the rest of the rows. So plese suggest how can I only get just that one (last) record? Happy hacking. Krishnakant. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: information about filed create_engine
Eduardo wrote:
/.../.../python2.6/site-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/
dialects/postgresql/psycopg2.py, line 234, in dbapi
psycopg = __import__('psycopg2')
ImportError: No module named psycopg2
The module psycopg2 is already installed in the site-packages
directory. I even included the path in the system variable by :
sys.path.append('/.../.../python2.6/site-packages/') in the wsgi
script.Still it won't work.
Why?
OK, this is definitely no longer an SQLAlchemy issue and more of a
mod_wsgi issue - you might get more help over on their mailing list
(http://code.google.com/p/modwsgi/wiki/WhereToGetHelp).
I believe psycopg2 is not a pure python module - it has a binary
component. Was it compiled with the same version of python that mod_wsgi
was?
Try this wsgi script (based on one from
http://code.google.com/p/modwsgi/wiki/InstallationIssues)
import sys
from pprint import pformat
def application(environ, start_response):
status = '200 OK'
output = (sys.prefix: %r\nsys.path: %s\n
% (sys.prefix, pformat(sys.path))
response_headers = [('Content-type', 'text/plain'),
('Content-Length', str(len(output)))]
start_response(status, response_headers)
return [output]
It would be worth comparing the output from that with the values of
sys.prefix and sys.path when run from bottle.
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Question about sqlalchemy inserts and deletes order in a transaction
ammar azif wrote: Hi, The code that I am working on deletes rows from table A that are based on a certain query and then recreates these rows based on entries supplied by a csv file. Table A is referenced by table B. My question is, how does sql alchemy manage inserts and deletes in a transaction and it what order are they done? It seems that deletes are done after inserts because I am getting unique constraint errors, although the rows are deleted before inserts are done. If my assumption is correct, how do I change this behaviour in SQLAlchemy. I do not want to add unique deferrable constraint into table A because its unique constraint key is being referred by table B, this is a limitation of postgres. Appreciate your feedback I assume you are using the ORM. (If you are using the low-level API, SQL statements are executed explicitly via something like connection.execute()) The ORM executes statements when you call session.flush(). If you call that after deleting your rows, you should be safe to insert new ones with the same IDs afterwards. SQLAlchemy does detect dependencies between rows, so for example it would know to insert rows into Table A before any rows in Table B that reference them. However, I don't think it necessarily performs deletions before insertions. Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: information about filed create_engine
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Eduardo
Sent: 18 July 2011 14:12
To: sqlalchemy
Subject: [sqlalchemy] Re: information about filed create_engine
I dont get any log. The access strings from the local and wsgi
applications are identical so the script should connect to the same
database. I encountered problems with create_engine. What type of
exception can this method throw?
The application catches: TypeError, ValueError and OperationalError.
Is there any other Error or some universal sqlalchemy error that can
indicate me where the problem is?
Thanks
I'm sorry - I still don't understand your setup. How do you know that
you've encountered problems with create_engine if you're not getting
any kind of exception from it?
If you really think that create_engine is failing but the exception is
being caught silently, why not change your code so that you've got an
exception handler around create_engine:
try:
engine = create_engine(your_connection_string)
except Exception, e:
import traceback
log_file = open('/tmp/sqlalchemy_errors', 'w+')
log_file.write('Exception from create_engine\n')
log_file.write('%s\n' % e)
log_file.write(traceback.format_exc())
raise
But your life would be much easier if you learnt how to configure
SQLAlchemy's built-in logging features:
http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging
What WSGI server and web framework are you using (if any)? It sounds
like they are hampering your efforts to debug this. You might find it
easier to run a very simple wsgi server such as the one in the wsgiref
module:
http://docs.python.org/library/wsgiref.html#module-wsgiref.simple_server
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: information about filed create_engine
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Eduardo Sent: 18 July 2011 15:54 To: sqlalchemy Subject: [sqlalchemy] Re: information about filed create_engine Yes, I use wsgi server of the python library bottle and I don't have any problem but when I want to use the same script via the apache web server I get only a server error no exception could be caught not even by using the code snippet from you (Thanks by the way). I simply included many print lines that appear in the error log file. The create_engine fails (I know it from try and except) but I cannot catch any exception that sheds some light on the reason of the failure. If you are getting a generic server error from Apache, you'll normally find the reason in the Apache error log (the location depends on your installation, but typically it is something like /var/log/httpd/error_log. Does that shed any light on the problem? Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: information about filed create_engine
Eduardo wrote On Jul 13, 7:11 pm, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: Eduardo wrote Hi, I am trying to prompt an answer from a database after failed create_engine command. I searched through the source code and I found TypeError, and ValueError returns but they relate (if I understood well only to the access parameters). My problem is that I am sure that my access parameters are correct but for some reason the creation of the engine fails. Is there any way to get information why the engin could not be created. The access to db log files is not granted! Thanks What kind of database are you trying to connect to? Are you getting a Python exception, and if so, can you show us the traceback? Simon !) PostgresSQL 2) I don't get any Python exception. So how do you know it's failing then? Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: information about filed create_engine
Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described on http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: information about filed create_engine
Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described on http://www.sqlalchemy.org/docs/core/engines.html#configuring-logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: information about filed create_engine
Eduardo wrote: On Jul 14, 10:49 am, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: Eduardo wrote When I use the same script with a standalone application it works but when I try to run it as a wsgi application it fails (wsgi logs does not contain any information regarding the failure!) Try turning on SQL logging (either by passing echo='debug') to create_engine, or by configuring the python logging package as described onhttp://www.sqlalchemy.org/docs/core/engines.html#configuring- logging. Then you should see the SQL being issued and the results coming back from the database. How are you configuring transactions? Is it possible that the transaction isn't being committed at the end of the web request, so any changes you've made are being discarded? Simon My application only queries the database there are no inputs and therefore no transactions involved. What was the result of turning on SQL logging? Are you sure you're even pointing at the same database that you were when you ran the standalone script? Try printing the value of session.bind.url (or including it in HTTP response, if you don't have easy access to the stdout from your wsgi script) Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] information about filed create_engine
Eduardo wrote Hi, I am trying to prompt an answer from a database after failed create_engine command. I searched through the source code and I found TypeError, and ValueError returns but they relate (if I understood well only to the access parameters). My problem is that I am sure that my access parameters are correct but for some reason the creation of the engine fails. Is there any way to get information why the engin could not be created. The access to db log files is not granted! Thanks What kind of database are you trying to connect to? Are you getting a Python exception, and if so, can you show us the traceback? Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] relationship problem
mik wrote:
Hello,
I am trying to use sqlalchemy with oracle, here is my code:
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, mapper, relationship
class Activite(object):
pass
class Famprod(object):
pass
engine = create_engine('oracle://login/paswd@db', echo=True)
metadata = MetaData(engine)
tActivite = Table('ACTIVITE', metadata,
autoload=True)
mapper(Activite, tActivite)
tFamprod = Table('FAMPROD', metadata,
autoload=True)
mapper(Famprod, tFamprod)
Famprod.activite = relationship(Activite)
Session = sessionmaker(bind=engine)
session = Session()
famprod = session.query(Famprod).get((ED, 15))
print famprod.activite
and i get this error:
AttributeError: 'RelationshipProperty' object has no attribute
'parent'
The table famprod has a composite key, one of the key columns is the
key of activite.
Is there something wrong with my code ?
I have tried to manually define the tFamprod's keys and foreign key
without succes.
Thank you.
I think your problem is here:
mapper(Famprod, tFamprod)
Famprod.activite = relationship(Activite)
You can't add relationship properties to mapped classes, unless they
were set up with the declarative extension
(http://www.sqlalchemy.org/docs/orm/extensions/declarative.html).
Without declarative, the code should look something like this:
mapper(Famprod, tFamprod, properties={
'activite': relationship(Activite),
})
(http://www.sqlalchemy.org/docs/orm/relationships.html#one-to-many)
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] In case of joinedload_all how do I order by on a columns of those relations
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Marc Van Olmen
Sent: 29 June 2011 04:19
To: sqlalchemy
Subject: [sqlalchemy] In case of joinedload_all how do I order by on
a columns of those relations
Hi
I'm trying to order by a column from a relationship.
Taken example from:
http://www.sqlalchemy.org/docs/orm/loading.html#routing-explicit-
joins-statements-into-eagerly-loaded-collections
In case of
query.options(joinedload_all('orders.items.keywords'))...
or
query.options(joinedload_all(User.orders, Order.items, Item.keywords))
I would like to do something like:
query.options(joinedload_all('orders.items.keywords')).order_by('user
.orders.items.keywords.name')
Tried this above but didn't work. Searched for some sample/tutorials
but with no luck.
thanks for any direction.
marc
I think this is in the FAQ:
http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOU
TERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYL
IMITetc.whichreliesupontheOUTERJOIN
(That link has probably wrapped - search for ORDER BY on
http://www.sqlalchemy.org/trac/wiki/FAQ)
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] (OperationalError) (1066, Not unique table/alias: '...') when selecting only from a joined table
Michael Bayer wrote: - I am loathe to reference the 0.5 docs as people keep finding them and thinking they are current, but an example of this is at http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with- joins (Note to people reading this: these are the *OLD DOCS* regarding 0.5; for current join usage please see http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins) In the Quick Select links at the top of the 0.5 docs, there's no link to the 0.7 docs. Is this deliberate or has it just been overlooked? Would it be worth putting some sort of big banner at the top of the older docs pointing out that they are old? FWIW, I *really* appreciate that you keep the old versions of the docs around - I have an application that I maintain using SA 0.3, and just last week I needed to refer back to the docs. I hope they never go away! (I know they still exist in the repository, but the website is so convenient...) Cheers, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] dynamically set table_name at runtime
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Cody Django
Sent: 20 June 2011 19:37
To: sqlalchemy
Subject: [sqlalchemy] dynamically set table_name at runtime
Hello!
I would like to dynamically set/change the table that is mapped in my
python object, as instantiated through the declarative style.
class Feature(Base, GeometryTableMixIn):
this is dynamically created to use a table and pk_column
determined at runtime
__table_args__ = {
schema: 'a_schema',
autoload: True,
autoload_with: Session.bind,
useexisting: True
}
wkb_geometry = GeometryColumn('wkb_geometry', Geometry(srid=4269))
def __init__(self, *args, **kwargs):
self.__tablename__ = kwargs['tablename']
self.pk_id = Column('%s' % kwargs['pk_id'], types.Integer,
primary_key=True, autoincrement=False)
super(Feature, self).__init__(*args, **kwargs)
This doesn't work:
InvalidRequestError: Class class 'javelin.model.feature.Feature'
does not have a __table__ or __tablename__ specified and does not
inherit from an existing table-mapped class.
Could this possibly be done through another approach? Suggestions
are
greatly appreciated.
Can you describe your use case? The solution presented in the
StackOverflow article seems like a hack at best. A cleaner way to do the
same thing might be:
def make_feature_class(tablename):
class Feature(Base, GeometryTableMixIn):
__table__ = tablename
# etc.
return Feature
...but the whole thing feels strange. What are you actually trying to
do?
Cheers,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Accessing several databases
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Julian J. M.
Sent: 16 June 2011 11:43
To: sqlalchemy
Subject: [sqlalchemy] Accessing several databases
Hello,
I'm intending to use sqalchemy with orm for loading and storing my
application's project files. Each sqlite database would be a project
file, that will have several tables.
I'd like to work with projects like this:
project1=AppProject(/tmp/pr1.sqlite);
project2=AppProject(/tmp/pr2.sqlite);
item1 = project1.getItem(5) # item1 should be and object of a mapped
class.
item1.value=test
anotheritem = project1.getNewItem()
anotheritem.value=this is new
# this should flush and commit the underlying session for project1,
#modifying item with id 5, and adding a new one
project1.commitEverything()
item2 = project2.getItem(8)
item2.value = another test
project2.commitEverything()
The problem i'm facing is how to create the engine, metadata, mapper,
session, and the orm classes for each AppProject instance.
I'm not sure if this is supported or even a good idea.
Thanks,
Julian J. M.
I think this should be pretty easy with a separate SQLAlchemy Session
per project. You would define all your mappers and so on without any
reference to a specific database:
##
# your_db_module.py
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class ProjectItem(Base):
__tablename__ = 'project_item'
id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
# other columns etc.
Then your AppProject class would look something like this:
##
# appproject.py
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from your_db_module import ProjectItem
class AppProject(object):
def __init__(self, filename):
self.engine = sa.create_engine('sqlite://' + filename)
self.session = saorm.Session(bind=self.engine)
def get_item(self, id):
return self.session.query(ProjectItem).get(id)
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] mapping a class linked with two other classes (AttributeError: 'str' object has no attribute '_sa_instance_state')
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Jules Stevenson
Sent: 16 June 2011 08:44
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] mapping a class linked with two other classes
(AttributeError: 'str' object has no attribute '_sa_instance_state')
Hi List,
I have a user class, a contact class, and a googleID class.
the contact class has can have a googleID per user in the system. I'm
trying to map it out as follows:
# ArkContact - clientprojectshot module
orm.mapper(ArkContact, contacts_table, properties={
'notes': orm.relation(ArkNote,
secondary=contact_notes_table,
backref='contacts',
single_parent=True,
cascade=all, delete, delete-orphan),
'users': orm.relation(ArkUser,
secondary=user_contact_table,
backref='contacts'),
'google_UID': orm.relation(ArkUserContactGUID,
cascade=all, delete,
backref='user')
})
#user contact google_GUID
user_contact_UID = sa.Table('user_contact_UID_table', meta.metadata,
sa.Column('user_id', sa.types.Integer, sa.ForeignKey('users.id'),
primary_key=True),
sa.Column('contact_id', sa.types.Integer,
sa.ForeignKey('contacts.id'), primary_key=True),
sa.Column('google_UID', sa.types.String(length = 1024))
)
class ArkUserContactGUID(object):
def __init__(self):
pass
orm.mapper(ArkUserContactGUID, user_contact_UID)
This raises two issues, the first is that an instrumented list is
returned for the google_UID paramter on the contact object, whereas
there should only ever be one (since as an operator there is only
ever
one user signed in - you).
For one-to-one relationships, you should supply uselist=False to your
relationship:
http://www.sqlalchemy.org/docs/orm/relationships.html#one-to-one
The second is it outright errors :), presumably because my mapping is
off:
File 'C:\\ark\\ark\\controllers\\contacts.py', line 368 in
initial_sync
contact_sync.initial_sync()
File 'C:\\ark\\ark\\arkTools\\arkGoogle.py', line 121 in initial_sync
self.add_contact_to_google(contact)
File 'C:\\ark\\ark\\arkTools\\arkGoogle.py', line 259 in
add_contact_to_google
data.google_UID.append(entry.get_id())
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\collections.py',
line 952 in append
item = __set(self, item, _sa_initiator)
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\collections.py',
line 927 in __set
item = getattr(executor, 'fire_append_event')(item, _sa_initiator)
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\collections.py',
line 618 in fire_append_event
item, initiator)
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\attributes.py',
line 741 in fire_append_event
value = fn(state, value, initiator or self)
File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-
py2.6.egg\\sqlalchemy\\orm\\unitofwork.py',
line 35 in append
item_state = attributes.instance_state(item)
AttributeError: 'str' object has no attribute '_sa_instance_state'
Many thanks for any help!
Jules
You're passing a string (presumably the result of entry.get_id()) where
SA is expecting an instance of a mapped class. I haven't looked at your
mapping in detail, but rather than this:
data.google_UID.append(entry.get_id())
you probably want something like this:
obj = ArkUserContactGUID(google_UID=entry.get_id())
data.google_UID.append(obj)
(If I've misunderstood your mapping, these class names are probably
wrong)
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] General questions of a newbee
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Knack
Sent: 14 June 2011 18:43
To: sqlalchemy
Subject: [sqlalchemy] General questions of a newbee
Hi guys,
I've done some programming, but I'm new to RDBMS and ORMs. I've read
some documentation, but before diving in deeper and doing some
tutorials, I'm trying to understand what can be done with SQLAlchemy
and get a coarse understanding of how it works.
Imagine some tables which are all related (like 'created by') to a
user by a foreign key. If I query all tables by a certain user, I
assume SQLAlchemy loads and creates all objects which have references
in the column 'created by' to the certain user. Like a 'manual' eager
loading. If I use the objects properties to follow the relations,
does
SQLA need to perform any more DB accesses? Or are the referenced
objects directly referenced (maybe with properties that stores the
direct reference after resolving after the first call)?
How about backrefs? Would every call to those require a new SQL query
under the hood? Or are those 'stored' in the ORM after the first call?
I guess this would impact how to model parent-children relations. On
the one hand it seems like an easy life to me if the parents don't
need references to the children in the database, as children could be
added without modifing the parents. One the other hand, how's the
performance impact if you need to get the children by backref calls?
SQLAlchemy gives you a lot of control over when related objects are
accessed - the full details are at
http://www.sqlalchemy.org/docs/orm/loading.html.
When you configure a relationship between 2 classes, the default load
behaviour is known as lazy loading. This means that the related object
will only be loaded when you first access the property on the parent.
Once an object is loaded, it is stored in the SQLAlchemy session object.
Subsequent requests for that same object (ie. same type and primary key)
will get the object from the session rather than going to the database.
That's not a very clear explanation - perhaps an example would help.
Imagine you were modelling a blog, and you had Post items and User
items. Posts have a 'created_by_id' foreign key to the User table, and a
'created_by' relationship which gives you the actual User object. Now
imagine that you have 3 posts in the database, created by 2 different
users.
Here's what happens when you load all the posts and then access their
'created_by' property, in the default configuration.
posts = session.query(Post).all()
...runs something like 'SELECT * from post'
print posts[0].created_by
...SA looks at the created_by_id on posts[0], then checks to see if it
already has a User with that id in the session. It doesn't, so it
retrieves it from the database (SELECT * from user where id = :id),
stores it in the session, and returns it to you.
print posts[1].created_by
...SA checks posts[1].created_by_id again. It is the same as
posts[0].created_by_id. SA already has that user in the session, so it
returns the same user without going to the database.
print posts[2].created_by
...this post was created by a different user, which isn't already in the
session, so SA goes to the database again.
The posts themselves have now been stored in the session, so if you
wrote the following:
post = session.query(Post).get(1)
...SA would see that post 1 already exists in the session and not go
back to the database. Note that this only works for the 'get' method -
if you try to do any other kind of query, SA will still run the query.
However, when it's reading the rows back, it will try to match those
rows up with objects already in the session. If it finds a match, the
instance from the session will be returned. This ensures that (for a
given session) you will only ever have one instance representing a row
in the database.
If you were working with a large number of posts and users, it would be
very inefficient to (potentially) run a new query for each post just to
get the user that created it. SQLAlchemy allows you to request a
different loading strategy:
posts = (session.query(Post)
.options(joinedload('created_by'))
.all())
...issues something like:
SELECT *
FROM post
LEFT JOIN user ON post.created_by_id = user.id
ie. the users will be loaded in the same query as the posts. After this,
SA will not need to go back to the database when you access the
'created_by' property, even the first time.
Backrefs are not really any different from forward references, and the
same conditions apply. I think there may be a slight caveat though. If
you wrote:
posts = session.query(Post).all()
user = posts[0].created_by
print user.posts
...I don't think SA has any way of knowing that all the posts from the
DB have been already been loaded into the session. It will run something
like 'SELECT * from post where
RE: [sqlalchemy] db name from session?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Chris Withers Sent: 15 June 2011 10:48 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] db name from session? Hi All, If I have a session object, what's the correct way to get the name of the db that session is attached to? cheers, Chris A session can be bound to multiple databases, so I'm not sure it's as simple as you'd like. Session has a get_bind method that will return the engine (or perhaps connection, depending on how the session was configured): http://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.session.S ession.get_bind ...but even then, I'm not sure you can necessarily go from an engine to a db name. What do you even mean by db name? Schema name? Host? Dialect? Filename? Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] question re using the session object
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of robert rottermann Sent: 14 June 2011 10:53 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] question re using the session object hi there, for a zope website I am using sqlalchemy. Now I am unsure how to use the session object. What I do now is: from sqlalchemy.orm import scoped_session ... Session = scoped_session(session_factory, scopefunc) session = Session() this session object I import into all classes where ever I need it. Now my question: is it ok to use this single instance troughout the life of the Zope severer, or should I call Session() whenever I need a session? thanks robert You definitely shouldn't use your 'session' instance throughout the application - it won't be thread-safe. Scoped sessions are described at http://www.sqlalchemy.org/docs/orm/session.html#unitofwork-contextual, but basically, you have two choices. You can: a) Call Session() whenever you need a session. SA will ensure that if you call it twice within the same scope (which is typically the current thread), the same instance will be returned. b) Use your Session directly - it implements the same interface as the real session, and forwards all requests on to the underlying thread-local session. Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: Trying to query a relationship of a relationship
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Jules Stevenson
Sent: 09 June 2011 08:53
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: Trying to query a relationship of a
relationship
Sorry, for the spamming, code typo (was trying to simplify it),
should read:
invoices = query(ArkInvoice).\
join(ArkInvoice.project).\
join(ArkProject.client).\
options(sa.orm.contains_eager(ArkInvoice.project.client)).\
filter(ArkInvoice.project.client.id == id)
I think you probably want something like this (all untested):
invoices = (session.query(ArkInvoice)
.join(ArkInvoice.project)
.join(ArkProject.client)
.filter(ArkClient.id == id)).all()
If you need contains_eager (which is purely an optimisation allowing you
to access invoice.project without a subsequent query), I think it would
look like this:
invoices = (session.query(ArkInvoice)
.join(ArkInvoice.project)
.join(ArkProject.client)
.options(contains_eager(ArkInvoice.project),
contains_eager(ArkProject.client))
.filter(ArkClient.id == id)
.all())
However, if you are actually going to be working with the client,
project and invoice objects after this query, you may find it easier to
start from the client:
client = (session.query(ArkClient)
.options(joinedload_all('projects.invoices'))
.filter(ArkClient.id == id)
.one())
After this query, you could access client.projects and
client.projects[n].invoices without further database queries.
See http://www.sqlalchemy.org/docs/orm/loading.html for a description of
joinedload_all.
I hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Filtered backref
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Joril Sent: 08 June 2011 22:41 To: sqlalchemy Subject: [sqlalchemy] Filtered backref Hi everyone! Is it possible to have a many-to-one declarative relation between two classes and a _filtered_ backref? I'm trying to build a tagging system for my bloglike application, and to allow a user to apply private tags to posts of other people. My classes are: Owner Post TagAssociation Tag A Post has an Owner, while TagAssociation has a Tag, a Post and an Onwer Between TagAssociation and Post there's a many-to-one, and I'd like to configure a tags backref so that it would handle only the TagAssociations having the same Owner as the Post... Is this possible? Many thanks! The 'relationship' function takes optional primaryjoin and secondaryjoin parameters that control the join conditions for the relationship. So I think you should be able to do something like this: import sqlalchemy as sa class TagAssociation(Base): # columns including owner_id and post_id class Post(Base): # columns including id and owner_id tags = relationship( TagAssociation, primary_join=(sa.and_(id == TagAssociation.post_id, owner_id == TagAssociation.owner_id))) I think you would have to treat this relationship as readonly, so you might need/want to add viewonly=True. Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Appending a where clause to a query
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Luka Novsak
Sent: 27 April 2011 05:32
To: sqlalchemy
Subject: [sqlalchemy] Appending a where clause to a query
The docs on Select's where() method say:
return a new select() construct with the given expression added to
its WHERE clause, joined to the existing clause via AND, if any.
Note: return a new select() construct
But this doesn't seem to happen.
This is my code:
def posts_per_dow(self, start_date=None, end_date=None):
q = select([func.date_part('isodow', t_posts.c.created_at),
func.count(t_posts.c.id)],
t_posts.c.user_id==self.id).group_by('1').order_by('1')
if start_date: q.where(t_posts.c.created_at=start_date)
if end_date: q.where(t_posts.c.created_atend_date)
Only the first where clause is actually used when I execute the query.
If I'm just going about it wrong, then how do I append a where clause
like this?
You need to store the return value of the 'where' method. eg:
if start_date:
q = q.where(t_posts.c.created_at=start_date)
if end_date:
q = q.where(t_posts.c.created_atend_date)
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Best design for commits?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Aviv Giladi Sent: 20 April 2011 15:53 To: sqlalchemy Subject: [sqlalchemy] Best design for commits? Hey guys, I have a Pylons back-end running on SQLAlchemy. I have a script that reads a tree of XML files from an HTTP server (it downloads an xml X, and then downloads that X's children, and then iterates the children, and so forth in recursion). Each xml file represents an SQLAlchemy model. The problem is that I have thousands of these xml's (sometimes 5000, sometimes 26000). I was able to optimize the download process with HTTP pooling, but I cannot seem to think of the best approach as to committing the models to the DB. Every time an xml file is downloaded, I create an orm object for it and add it to my session. Problem 1: some xml's will exists multiple times in the tree so I am checking that there is no duplicate insertion. Is the check in my code optimal or should I keep an indexed collection on the side and use it to check for duplicates? Problem 2: my autocommit is set to False because I don't want to commit on every add (not because its bad design, but because of performance). But I also don't want to iterate the entire tree of thousands of categories without committing at all. Therefor, I created a constant number upon which my code commits the data. Is this a good approach? What would be a good number for that? It might be important to mention that I do not know in advance how many xml's I am looking at. Here is what my pseudo-code looks like now (ignore syntax errors): count = 0 COMMIT_EVERY = 50 def recursion(parent): global count, COMMIT_EVERY pool = get_http_connection_pool(...) sub_xmls = get_sub_xmls(pool, parent) if sub_xmls == None: return for sub_xml in sub_xmls: orm_obj = MyObj(sub_xml) duplicate = Session.query(MyObj).filter(MyObj.id == orm_obj.id).first() if not duplicate: Session.add(orm_obj) count = count + 1 if count % COMMIT_EVERY == 0: Session.commit() recursion(orm_obj.id) recursion(0) I'm not sure I can comment on the overall approach, but there are a couple of things that might help you. 1. If you use Query.get rather than Query.filter, you won't actually query the database when the object already exists in the session. You'll probably need to clear the session every now and then (I don't think flush() or commit() clear it, but I could be wrong) 2. You may want to distinguish Session.flush() from Session.commit() - you could flush every N new objects, and only commit once at the very end. 3. If you know you are the only person writing to the database, consider setting expire_on_commit=False on your session. Otherwise I think accessing orm_obj.id after Session.commit() will trigger another (possibly unnecessary) query to the database. Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] API that allows me to do additional database operations just before insert execution for SQL Expression
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of bool Sent: 19 April 2011 14:16 To: sqlalchemy Subject: [sqlalchemy] API that allows me to do additional database operations just before insert execution for SQL Expression Is there any API that allows me do some processing (I want to do additional updates based on the insert statement) just before executing an insert statement using SQL Expression? I dont want to do this during compile time (@Compiles(Insert)) as I will be doing some database updates and this is not desirable for every compilation e.g., just a simple print should not do this additional processing. SA 0.7 generates events both at the ORM level and at the SQL level. See: http://www.sqlalchemy.org/docs/07/core/event.html http://www.sqlalchemy.org/docs/07/core/events.html For example, there is a 'before_execute' event which you could listen for, and look for INSERT clauses. If you can't upgrade to 0.7, you might be able to use a ConnectionProxy: http://www.sqlalchemy.org/docs/07/core/interfaces.html Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: Context based execution
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of bool Sent: 15 April 2011 14:41 To: sqlalchemy Subject: [sqlalchemy] Re: Context based execution Hi, Thanks a lot. Can someone answer this question also = @compiles(Select) def contextual_select_thing(select, compiler, **kw): This method gets registered with Select. But How/When does this registration automatically happen? The implementation of the compiler extension is very short - you can see it at http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/ext/compiler.py. It looks like it modifies the target class to add _compiler_dispatcher and _compiler_dispatch attributes to it (or update them if it already has them). The SA statement compiler must look at these attributes to determine how to compile the statement. The registration happens as soon as the @compiles(Select) decorator is evaluated. If it is at module-global scope (rather than being buried inside another function), it'll happen when the module is imported. Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Create a one-to-many relationship using association object with two foreign key primary keys
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of frankentux
Sent: 14 April 2011 14:42
To: sqlalchemy
Subject: [sqlalchemy] Create a one-to-many relationship using
association object with two foreign key primary keys
I have packages and repos. A package can be in many different repos
and a repo has many packages. I want to have an additional
relationship to capture the 'status' of a particular package in a
particular repo. This would be a many-to-many relationship with an
additional field, so I guess I have to use an Association object, as
described by the docs.
When I create a 'normal' association object, it works fine. However,
as a next step I would like to add any number of comments to the
association object - i.e. in my case (below), I would like a PackRepo
object to have any number of comments - as a classic one-to-many.
However, given that PackRepo itself has no 'id' but rather uses the
foreign key relationships to package.id and repo.id as primary keys,
I
don't know how to create the relationship to the package_repo table
when I'm building the comments_table - I can't simply say
packagerepo.id because packagerepo doesn't _have_ an id - it has two
foreign key primary keys as described above.
Any ideas of what to do?
package_table = Table('package',metadata,
Column('id',Integer,primary_key=True),
Column('name',String))
repo_table = Table('repo',metadata,
Column('id',Integer,primary_key=True),
Column('name',String))
comment_table = Table('comment',metadata,
Column('id',Integer,primary_key=True),
### PROBLEM - HOW TO CREATE RELATIONSHIP TO package_repo ###
# Column('packagerepo_id', Integer, ForeignKey(### how to declare
this ###)),
Column('msg',String))
You just need to add a column to your comment_table for each key column
in the target table. Something like this:
comment_table = Table('comment',metadata,
Column('id',Integer,primary_key=True),
Column('package_id', Integer, ForeignKey('package_repo.package_id'),
Column('repo_id', Integer, ForeignKey('package_repo.repo_id'),
Column('msg',String))
I *think* SA will automatically work out the relationship condition
based on those two foreign keys.
Hope that helps,
Simon
package_repo_table = Table('package_repo', metadata,
Column('package_id',Integer,ForeignKey('package.id'),primary_key=True
),
Column('repo_id',Integer,ForeignKey('repo.id'), primary_key=True),
Column('status',String,default='builds'))
mapper(Package, package_table, properties={
'repos':relationship(PackRepo)
})
mapper(PackRepo, pack_repo_table, properties={
'repo':relationship(Repo),
'comments': relationship(Comment)
})
mapper(Comment,comment_table)
mapper(Repo, repo_table)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Two Objects, One Table and the inverse
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 05 April 2011 18:38 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Two Objects, One Table and the inverse On Apr 5, 2011, at 12:30 PM, Israel Ben Guilherme Fonseca wrote: Thks for the insight Michael. With the @property solution, its not possible to make queries like session.query(Person).filter(Person.address.street=Something) right? that's not possible with standard SQLAlchemy expression constructs anyway. Normally you'd use Address.street to get clause elements against Address.You can use hybrids to create this effect fully (see http://www.sqlalchemy.org/docs/07/orm/extensions/hybrid.html ) Out of interest, if Person and Address were standard mapped classes with a one-to-one relationship between them, could that query be made to work: session.query(Person).filter(Person.address.street==Something) I guess it would have to be equal to: session.query(Person).join(Person.address).filter(Address.street==Somet hing) In order for that to work, Person.address would have to be a smart wrapper for the Address class that adds the join condition into any attribute comparison operations. Good idea, or silly idea? (Or perhaps it already works...) Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key
This is just the way Python works - code inside a module is only executed when that module is imported. If you don't import myapp.models.notes, then the class definitions never get executed. One solution is to import all the sub-modules in your bootstrap.py before calling create_all. Another is importing the submodules inside the myapp/models/__init__.py Hope that helps, Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of eric cire Sent: 30 March 2011 14:57 To: sqlalchemy Subject: [sqlalchemy] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key After investigating further, i have the impression that the problem occurs when models are in different modules (notes.py users.py in this case) but if the models are in the same module eg. myapp.models.__init__.py, the tables are created. I'd still like to know why this is happening because i don't intend to put al my models in the same module.. Thanks, On Wed, Mar 30, 2011 at 3:36 PM, 371c 371c@gmail.com wrote: Hi, I have the following setup: myapp.models.notes.py Note model defined here using declarative base myapp.models.users.py User model defined here using declarative base myapp.models.meta.py Base and DBSession defined here to avoid circular imports... myapp.lib.bootstrap.py Called to initialize the database with some initial data. The following is done: create an engine (sqlite:///notes.db) call Base.create_all(bind=engine) The Base class is the same for the models and the bootstrap.py module, but i still get a noreferencedtableerror... it basically doesn't create the database tables when bootstrap.py is called.. Any ideas ? Regards, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key
Something else must be importing those modules when run from pylons. If you really want to know how they are getting imported, stick something in the module which will raise an exception when it is imported (eg type blah blah blah at the top of the module) and look at the traceback. Cheers, Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of 371c Sent: 30 March 2011 16:00 To: sqlalchemy Subject: [sqlalchemy] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key Actually that helps alot and i had infact resolved to and was in the process of doing so (i know that about python modules.. ;) but i might be missing something) But, why does this work in the context of an application (eg. pylons app). Basically, calling Base.create_all() in some init_db method of an application works without having to import all the modules in, say, myapp.models.__init__.py Suggestions are welcome, though i'm considering the question answered Thanks alot and Regards, On Mar 30, 4:39 pm, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: This is just the way Python works - code inside a module is only executed when that module is imported. If you don't import myapp.models.notes, then the class definitions never get executed. One solution is to import all the sub-modules in your bootstrap.py before calling create_all. Another is importing the submodules inside the myapp/models/__init__.py Hope that helps, Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of eric cire Sent: 30 March 2011 14:57 To: sqlalchemy Subject: [sqlalchemy] Re: sqlalchemy.exc.NoReferencedTableError: Could not find table with which to generate a foreign key After investigating further, i have the impression that the problem occurs when models are in different modules (notes.py users.py in this case) but if the models are in the same module eg. myapp.models.__init__.py, the tables are created. I'd still like to know why this is happening because i don't intend to put al my models in the same module.. Thanks, On Wed, Mar 30, 2011 at 3:36 PM, 371c 371c@gmail.com wrote: Hi, I have the following setup: myapp.models.notes.py Note model defined here using declarative base myapp.models.users.py User model defined here using declarative base myapp.models.meta.py Base and DBSession defined here to avoid circular imports... myapp.lib.bootstrap.py Called to initialize the database with some initial data. The following is done: create an engine (sqlite:///notes.db) call Base.create_all(bind=engine) The Base class is the same for the models and the bootstrap.py module, but i still get a noreferencedtableerror... it basically doesn't create the database tables when bootstrap.py is called.. Any ideas ? Regards, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] trouble with metaclass
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of farcat
Sent: 16 March 2011 21:01
To: sqlalchemy
Subject: [sqlalchemy] trouble with metaclass
I have an error i cant figure out (likely a beginners error):
#
Base = declarative_base()
class tablemeta(DeclarativeMeta):
def __new__(mcls, name):
return DeclarativeMeta.__new__(mcls, name, (Base,), {})
def _init__(cls, name):
temp = dict()
temp[__tablename__] = _ + name
temp[id] = Column(Integer, primary_key = True)
temp[text] = Column(String(120))
DeclarativeMeta.__init__(cls, name, (Base,), temp)
if __name__ == __main__:
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
table1 = tablemeta(table1) #= ERROR
row1 = table1(text = detextenzo)
row2 = table1(text = detextenzoennogeenbeetje)
session.commit()
list = session.query(table1).all()
for l in list:
print str(l)
print done
#
the error is:
#
Traceback (most recent call last):
File D:\Documents\Code\NetBeans\test\temp\src\temp.py, line 33,
in
module
table1 = tablemeta(table1)
TypeError: __init__() takes exactly 4 arguments (2 given)
#
I do not understand what __init__ i am miscalling: I call
tablemeta.__init__ with 2 (1 implicit) as defined and
DeclarativeMeta.__init__ with 4 as defined?
please help ...
I'm not sure if it's the cause of your problem, but you have a typo in
tablemeta - your __init__ only has 1 underscore at the beginning...
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] In-memory object duplication
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Jacques Naude Sent: 17 March 2011 12:32 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] In-memory object duplication Hi, Simon Thanks for the quick response. Elixir doesn't use __init__ - there's something automatic going on there. My create(), in essence, does the job of __init__, which means you might still be hitting the nail on the head. I haven't had the time to test it out yet, but I will. (Why, though, would the double entry not be persisted to the database too?) The entry only appears once in the database because SQAlchemy works hard to ensure that a single object instance corresponds to a single row in the database. It doesn't really make sense (in the standard one-to-many model) for a particular child to appear more than once in a parent-child relationship. By default, SA uses a list as the collection implementation for relationships, and doesn't care if you add the same instance more than once. If it bothers you, you could use a set instead: http://www.sqlalchemy.org/docs/orm/collections.html#customizing-collecti on-access Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] In-memory object duplication
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of jln
Sent: 15 March 2011 16:37
To: sqlalchemy
Subject: [sqlalchemy] In-memory object duplication
[SNIP]
statuses = OneToMany('DocumentStatus', inverse='doc', cascade='all,
delete-orphan', order_by=['timestamp'])
So, when I create a new DocumentStatus object, Document.statuses
lists
two of them, but not actually persisted to the database. In other
words, leaving my Python shell, and starting the model from scratch,
there actually is only one child object (corroborated by squizzing
the
database directly). Here's my DocumentStatus.create() class method:
@classmethod
@logged_in
@log_input
def create(cls, doc, status, person=None, date=None):
person=validate_person(person)
if person:
status = DocumentStatus(doc=doc, status=status,
person=person, date=resolve_datetime(date))
if status:
doc.statuses.append(status)
doc.flush()
out = 'Document status created'
success = True
else:
out = 'Document status not created'
success = False
else:
out = 'Person does not exist'
success = False
log_output(out)
return success
I simply don't know why this is happening or, as I said, how to
search, intelligently, for an answer.
I don't know Elixir, but I assume that the inverse='doc' line in the
relationship sets up an SQLAlchemy backref. If so, then setting
status.doc (presumably done in DocumentStatus.__init__) will
automatically populate doc.statuses at the same time.
So when you do doc.statuses.append(status) a bit later on, you're adding
it to the list a second time.
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Simple Join failing
Warwick Prince wrote:
Hi All
I have what I hope is a very simple question;
Just started experimenting with joins, so I tried a very basic test
and got a fail that I don't understand. It appears that SA is
creating bad SQL, but I'm sure it's something I'm missing.. Here's
what I did;
I have two tables. products and product_prices. There is a one to
many relationship based on Foreign Keys of Group and Code Both
tables have columns Group and Code and they are also the primary of
each.
I do this;
e = an Engine (MySQL connector)
m = MetaData(e)
prod = Table('products', m, autoload=True)
price = Table('product_prices, m, autoload=True
# These tables are both fine and load correctly
# I want to build up my query generatively, so..
# Note that I'm selecting specific columns, and both sets of Foreign
Keys are in the selected columns (not that I believe I should need to
do that)
q = prod.select().with_only_columns(['products.Group',
'products.Code', 'product_prices.Group', 'product_prices.Code',
'product_prices.ListPriceEx', 'product_prices.ListPriceInc'])
q = q.join(price)
# I get this error;
ArgumentError: Can't find any foreign key relationships between
'Select object' and 'product_prices'.(They do exists BTW)
So, I remove my .with_only_columns and try again
q = prod.select()
Here you are creating a Select object (ie SELECT all columns FROM
products)
q = q.join(price)
Now you are joining that Select object with another table
ie. (SELECT all columns FROM products) JOIN price ON join condition
The extra parentheses are there because you are joining a SELECT with a
table.
Instead, you want to join the tables together:
prod.join(price)
To select from that, you can use the standalone select function:
http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.e
xpression.select
eg.
select([products.c.Group, products.c.Code, price.c.ListPriceEx],
from_obj=[prod.join(price)])
Hope that helps,
Simon
--
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 Join failing
Warwick Prince wrote: Hi Simon Thanks for that - I knew it was something wrong with the approach but simply could not pick it!Back to the test bench for another go :-) Cheers Warwick P.S. OK - I have to ask - when and how (why?) do I do the .join on the query? ;-) In SQL, you can treat a query just like a table, so you can join 2 queries together, or join a query to another table. For example: SELECT * FROM (SELECT a, b FROM table_1) as q1 INNER JOIN (SELECT c, d FROM table_2) as q2 ON q1.b = q2.c That example is not very helpful - it could easily be rewritten as a single SELECT, but I hope you see that the subqueries can be as complicated as you like. The object that you were originally producing with your 'q.join(price)' wasn't a Select object, but a Join - something that you can select from. You could write something like this: # JOIN the price table with a query on the products table: j = prod.select().join(price) # SELECT from that JOIN: q = select(some_columns, from_obj=[j]) This almost certainly isn't what you wanted in your situation, but there are plenty of cases where subqueries are very useful. Hope that helps, Simon -- 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: Python's reserved keywords as column names
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com]
On Behalf Of Andrey Semyonov
Sent: 10 September 2010 14:35
To: sqlalchemy
Subject: [sqlalchemy] Re: Python's reserved keywords as column names
On 10 сен, 17:15, King Simon-NFHD78 simon.k...@motorola.com wrote:
Hi Andrey,
See the section in the docs 'Attribute Names for Mapped Columns':
http://www.sqlalchemy.org/docs/orm/mapper_config.html#attribute-
names-fo
r-mapped-columns
Hope that helps,
Simon
Well, this leads to the only way to map in my case named
'Declarative'. Because it would fail on
mapper(Class, table, properties = { '_from': table.c.from })
Could non-declarative way for mapping python's reserved keywords as
column names be scheduled as a bug or enhancement request ?
--
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.
The 'c' collection on a Table object allows dictionary-style access, so you
should be able to use:
mapper(Class, table, properties = { '_from': table.c['from'] })
Even if that didn't work, you could always use Python's getattr function:
mapper(Class, table, properties = { '_from': getattr(table.c, 'from') })
Hope that helps,
Simon
--
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] update a relation from its id
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of chaouche yacine Sent: 02 September 2010 11:02 To: sqlalchemy googlegroups Subject: [sqlalchemy] update a relation from its id Hello group, Suppose A has a ManyToOne relation to B (A is a child of B). I want to perform something like : a.b_id = b.id assert a.b == b How do I do this in sqlalchemy ? Hi, This is answered in the FAQ: http://www.sqlalchemy.org/trac/wiki/FAQ#Isetthefoo_idattributeonmyinsta nceto7butthefooattributeisstillNone-shouldntithaveloadedFoowithid7 Hope that helps, Simon -- 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.merge and multiple databases
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Raf Geens Sent: 25 August 2010 16:48 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Session.merge and multiple databases On 25/08/2010 17:15, Michael Bayer wrote: On Aug 25, 2010, at 10:50 AM, Raf Geens wrote: Hi, I have a sqlite database A and a MySQL database B which share the same schema, where I want to synchronize the contents of certain tables in A with those in B at certain times. Except for the synchronizing step the contents on A's side don't change, while those on B's side might. I'm trying to do this using Session.merge, which works if the row doesn't exist yet in A, or does but hasn't changed in B. If it has changed in B, I get a ConcurrentModificationError when the merge is flushed. can't reproduce in 0.5.6 nor in 0.6.3, so a full test script that reproduces will be needed. Alternatively, you might want to look at your SQL output and see what primary key is attempting to be updated: Thanks for the quick reply. I've looked at the SQL output of the last commit and it appears to match on the correct primary key. However, the Individual has a version_id_col defined in the mapper, and it's trying to match on the wrong value there, which seems to cause the update to fail. I'll try to reproduce it in a full script. Raf The version_id_col is likely not to work - the whole point of the column is that SA adds the current version to the WHERE clause, and then checks to see if any rows were updated. If they were, the object was still at the same version that SA loaded from the database. If no rows were updated, it assumes it was because someone else modified the object and incremented the version number (hence the ConcurrentModificationError). SA increments the version number every time a change to the object is flushed to the database. So when your object is modified in B, the version number no longer matches the version in A and no rows match the criteria. I don't know how you fix this if you want to continue using the version_id_col feature - is there any chance that you could do without it (perhaps by implementing similar functionality in a SessionExtension which only gets attached to the primary session)? Simon -- 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] Which columns changing during orm commit?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Hipp Sent: 19 August 2010 23:39 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Which columns changing during orm commit? On 8/19/2010 5:24 AM, Chris Withers wrote: Michael Hipp wrote: SQLAlchemy seems pretty smart about updating only the changed columns in an orm object... If I have an orm object. Something changes one of the columns. Just before I commit() the session, is there a way to tell which columns will be updated vs those that are unchanged? Any way to ascertain the before/after values on those changed columns? Here's the basics: http://www.sqlalchemy.org/docs/session.html#session-attributes These examples should fill in the rest: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedMap http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedRows http://www.sqlalchemy.org/docs/examples.html?#module-versioning Thanks. But I believe all those items deal with which orm objects (rows) are changed. I'm asking about columns within an orm object that might be changed. Did I miss something? Thanks, Michael You could use mapper.iterate_properties [1] to loop over all the properties of your object, and for each one call attributes.get_history [2] to find out if it has changed. I'm not sure if it's the best way, but it should work. The return value from get_history isn't documented, but the source is pretty simple. If you only want to know if the attribute has changed, you can call the 'has_changes' method. You can look at the 'added' and 'deleted' properties to get the before and after values. Hope that helps, Simon [1] http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm .mapper.Mapper.iterate_properties [2] http://www.sqlalchemy.org/docs/reference/orm/mapping.html#attribute-util ities -- 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: To select only some columns from some tables using session object, relation many-to-many
Alvaro Reinoso wrote:
It works out, thank you! How could I just retrieve some columns from
both tables? For example, if I try to select some columns from Item
and Channel, I get class 'sqlalchemy.util.NamedTuple' when I'd
like to get a channel type with its items:
result = session.query(Channel.title,
Item.title).join('items').filter(Item.typeItem == zeppelin/
channel).order_by(Channel.titleView).all()
I just need some values many times, I don't need to retrieve the whole
object.
Thanks in advance!
It sounds like you are looking for deferred column loading:
http://www.sqlalchemy.org/docs/mappers.html#deferred-column-loading
You can mark certain columns as not to be loaded until they are
accessed. This can be done at mapper definition time as well as at query
time.
Hope that helps,
Simon
--
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, 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? You can tell meta.create_all() to use the same underlying DB connection as the session by using the session.connection() method with the 'bind' parameter to create_all(). Ie. connection = session.connection() meta.create_all(bind=connection) See the docs at http://www.sqlalchemy.org/docs/session.html#using-sql-expressions-with-s essions and http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalche my.schema.MetaData.create_all Hope that helps, Simon -- 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] Problem with Joined Table inheritance
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Jules Stevenson
Sent: 13 July 2010 15:01
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Problem with Joined Table inheritance
Apologies for any stupidity, but I'm struggling with some joined table
inheritance, I have the following code:
widgets_table = sa.Table('web_widgets', meta.metadata,
sa.Column('widget_id', sa.types.Integer, primary_key=True),
sa.Column('title',sa.types.String(length=255)),
sa.Column('widget_type', sa.types.String(30), nullable=False),
sa.Column('position', sa.types.Integer),
sa.Column('page_id', sa.types.Integer,
sa.ForeignKey('web_pages.id'))
)
video_widget_table = sa.Table('web_video_widget', meta.metadata,
sa.Column('widget_id', sa.types.Integer,
sa.ForeignKey('web_widgets.widget_id'), primary_key=True),
sa.Column('teaser', sa.types.String(length=1)),
sa.Column('body',sa.types.String(length=21845)),
sa.Column('image', sa.types.String(length=256))
)
class ArkWebWidget(object):
def __init__(self):
pass
class ArkWebVideoWidget(object):
def __init__(self):
pass
orm.mapper(ArkWebWidget, widgets_table,
polymorphic_on=widgets_table.c.widget_type,
polymorphic_identity='widget'
)
orm.mapper(ArkWebVideoWidget, video_widget_table,
inherits=ArkWebWidget,
polymorphic_identity='video_widget'
)
---
However, when I run this I get an error:
...
File
C:\ark\env_x64\lib\site-packages\sqlalchemy-0.6.2-py2.6.egg\s
qlalchemy\o
rm\__init__.py, line 818, in mapper
return Mapper(class_, local_table, *args, **params)
File
C:\ark\env_x64\lib\site-packages\sqlalchemy-0.6.2-py2.6.egg\s
qlalchemy\o
rm\mapper.py, line 207, in __init__
self._configure_inheritance()
File
C:\ark\env_x64\lib\site-packages\sqlalchemy-0.6.2-py2.6.egg\s
qlalchemy\o
rm\mapper.py, line 231, in _configure_inheritance
(self.class_.__name__, self.inherits.class_.__name__))
sqlalchemy.exc.ArgumentError: Class 'ArkWebVideoWidget' does
not inherit from 'A
rkWebWidget'
And I'm really not sure what I've done wrong, it seems ok based on
what is written in the docs?
Any pointers much appreciated.
Jules
I think the error message is quite explicit - you need to make your
ArkWebVideoWidget class inherit from ArkWebWidget. At the moment, it
inherits from 'object'.
http://www.sqlalchemy.org/docs/mappers.html#mapping-class-inheritance-hi
erarchies
Notice that the Manager and Engineer classes both inherit from Employee.
Hope that helps,
Simon
--
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
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of
thatsanicehatyouh...@mac.com
Sent: 07 July 2010 20:33
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Using the declarative base across projects
Hi Lance,
Thanks for your comments.
On Jul 7, 2010, at 12:28 PM, Lance Edgar wrote:
Why not just do this in project2 ?
import project.DatabaseConnection as db
Base = declarative_base(bind=db.engine)
# ... etc.
The DatabaseConnection class contains the particulars of the
connection (i.e. host, username, password) which can be
different. I don't want to create dependencies between the
projects, I just want to reuse the class definitions. I want
to create the DatabaseConnection once and pass it into the
definition of the classes.
Another approach I tried was to make ModelClasses an object
and define the classes in a method there (so I could just
pass the Base class to it), but the class definitions were in
the wrong namespace.
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.
This is a bit tricky to explain. Imagine I have one database,
and I create a project (1) to work with that database
(connections, table class definitions, etc.). That is
standalone (to me). I have another completely separate
database (2) on another host where I do the same thing. Using
replication I then create a read-only copy of database 1 in
database 2, and join some of the tables. Project 2 needs to
generate the classes, but use SA's Base class that is
dynamically generated. Since it's dynamic, I have to create
it at run time... but now I can't pass that to the definition
of project 1's classes. It's that communication that I'm
struggling with.
Cheers,
Demitri
In general, you don't need a database connection just to define your
tables and mappers. The 'bind' parameter to DeclarativeBase is optional,
and only necessary if you are using autoloading. So one solution to your
problem would be not to use autoloading, and bind to a database at the
Session level rather than the Mapper level. That would be the usual way
to use the same set of classes against multiple databases.
If you really need to use autoloading, you could move all your class
definitions into a function that accepts a database engine as a
parameter. For example:
#
# ModelClasses.py
class Namespace(object):
def __init__(self, **kwargs):
self.__dict__.update(kwargs)
def initdb(connection_string):
engine = create_engine(connection_string)
Base = declarative_base(bind=engine)
class Table1(Base):
__tablename__ = 'table1'
__table_args__ = {'autoload': True}
return Namespace(Base=Base,
Table1=Table1)
# or, you could be lazy:
# return Namespace(**locals())
# MainScript1.py
import ModelClasses
db = ModelClasses.initdb(my_connection_string)
# access db.Table1, db.Base etc.
Hope that helps,
Simon
--
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] Comparable properties
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers Sent: 08 July 2010 09:28 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Comparable properties Oliver Beattie wrote: @property def is_visible(self): return (self.enabled and not self.is_deleted) This can clearly be mapped quite easily to SQL expression `Klass.enabled == True Klass.is_deleted == False` You could always add a class-level attribute that stored this... @property def is_visible(self): return (self.enabled and not self.is_deleted) visible = enabled==True is_deleted==False You may need to wrap that into a method with the classproperty decorator... But, it'd be nice to have one attribute of the object fulfil both roles, and I don't know how to do that :-S Chris I think the 'Derived Attributes' example does what you want: http://www.sqlalchemy.org/docs/examples.html#module-derived_attributes http://www.sqlalchemy.org/trac/browser/examples/derived_attributes/attri butes.py As far as I can tell, it uses some Python descriptor magic to allow your property to work both at the instance and at the class level (so 'self' will either be the instance or the class). Accessing Klass.is_visible returns the SQL expression construct, but instance.is_visible works as normal. You'd be more restricted in what you can write inside your property definition though. For example, you can't use plain Python 'and', or assume that 'self.enabled' evaluates to True or False. I think something like this would work though: @hybrid def is_visible(self): return (self.enabled == True) (self.is_deleted == False) Hope that helps, Simon -- 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] models in different packages, often declaratively defined
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers Sent: 01 July 2010 19:17 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] models in different packages, often declaratively defined Hi All, Suppose I have packageA that defines: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class User(Base) __tablename__ = 'user' ... Now, I have a packageB that defines: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Something(Base) ... I want Something to have a foreign key that points at User. How do I do that? The tables for packageA and packageB should exist in the same database (the extraction of User into packageA is just so that all our projects that need users get the same schema for the 'user' and related tables and functionality for users). I guess things could be engineered such that one MetaData instance is shared between all the bases (how would that be done though? I'd need to get the MetaData instance into each of the packages before declarative_base is called...) Moreover, how do I get all the Base's to share a _decl_class_registry? (I'm still hazy on why the information in _decl_class_registry can't go into MetaData, rather than having two registries...) Any ideas gratefully received... Chris If packageB depends on packageA, I would have packageB import the metadata or declarative Base class from packageA. Otherwise, I would create a new package, (called something like 'common'), which creates the metadata and declarative Base class. packageA and packageB would import those items from the common package. I imagine that by having a single declarative Base class, the _decl_class_registry problem will disappear. I also assume that the reason that isn't stored in the MetaData is that MetaData is an object provided by the underlying sql library, whereas 'declarative' is an extension to the ORM, and the MetaData class shouldn't know anything about it. Simon -- 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] cross-database joins with MySQL
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers
Sent: 29 June 2010 10:28
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] cross-database joins with MySQL
Michael Bayer wrote:
We have engines set up like:
engine1 = create_engine('mysql://username:passw...@server/db1')
engine2 = create_engine('mysql://username:passw...@server/db2')
..and then have them bound to separate sessions, with
separate model classes mapped to them.
Now, mysql supports cross database joins, eg:
select t1.colwhatever
from db1.table1 as t1,db2.table2 as t2
where t1.something=t2.something
Is it possible to express that in SQLAlchemy, particularly
at the ORM layer with the multiple session/engine/model setup
described above?
(I suppose the case to test would be, if ModelA is bound
to engine1 and ModelB is bound to engine2, how would we do:
session.query(ModelA,ModelB,ModelA.something==ModelB.something)
...or something similar, if the above isn't possible?
its not possible across two distinct database connections,
no. Only the database can do joins, and that requires a
single connection session to do so.
Right, but how can I create an engine such that it can be
used to access
two databases? Is it as simple as setting the __tablename__ as
'db.tablename' rather than just 'tablename'?
Chris
You want the 'schema' parameter to the Table:
http://www.sqlalchemy.org/docs/metadata.html#specifying-the-schema-name
(Probably need to use __table_args__ if you are using declarative)
Simon
--
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] Referential integrity actions are not doing what I want
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of exhuma.twn
Sent: 22 June 2010 14:27
To: sqlalchemy
Subject: [sqlalchemy] Referential integrity actions are not
doing what I want
Hi,
I have a table of items, where each item can be owned by one
person, and held by someone else. I want the owner to be
compulsory (not nullable), and the holder to be optional (nullable).
To model this I have two tables, one for contacts and one for items.
The item table has two fields owner_id and holder_id. Bot are
references to the contact table and have the on delete rule set to
restrict and set null respectively.
The problem is that when I want to delete the contact attached to the
holder_id column, it seems that SA tries to set *both* references to
null. It should not do this! For example: If you have an item which
has an owner_id 1 and a holder_id 2, then deleting the contact
with ID 2 will cause the following query:
'UPDATE item SET owner_id=%(owner_id)s, holder_id=%(holder_id)s WHERE
item.item_id = %(item_item_id)s' {'holder_id': None, 'item_item_id':
10, 'owner_id': None}
First of all, *why* is SA issuing this query at all? A delete query
would suffice. The ref. integrity should be handled by the DB,
shouldn't it? More importantly, it updates both owner_id and
holder_id. But as previously said, owner_id=1 and holder_id=2. So
deleting contact #2 should only trigger - if at all - an update query
to set holder_id to null.
Any ideas as to what I am doing wrong here?
There are various ways of configuring SA's behaviour when you delete
objects with relationships. You may want to refer to these pages in the
docs:
http://www.sqlalchemy.org/docs/mappers.html#using-passive-deletes
http://www.sqlalchemy.org/docs/session.html#cascades
http://www.sqlalchemy.org/docs/ormtutorial.html#deleting
Hope that helps,
Simon
--
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] help please
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Aref
Sent: 10 June 2010 02:40
To: sqlalchemy
Subject: [sqlalchemy] help please
Hello All,
I just began learning sqlalchemy and am not quite used to it yet so
please excuse my ignorance and which might be a trivial question to
some of you.
I am writing a database module and need to load a table and possibly
modify a record in the table. I can get the connection established and
everything works fine. The problem I am running into is that I do not
necessarily know the column name before hand to code it in the update
method. I want to be able to find out to send a generic column name
which will be updated (gets the column name dynamically).
I tried the following:
columns=['ProjectID', 'Program', 'progmanger']
test = str('table.c.'+columns[1])
update = table.update(test=='project-name', values = {test:'program'})
print update
update.execute()
I get a error when I try to run it. It does not recognize the column
for some reason even though if I print test everything seems to be OK.
I get 'project.c.Program'
Is there something I am missing here? How can I send the project and
column name to the update method dynamically?
Thank you so much in advance for any help or insight you
could provide.
The table.c object supports dictionary-style access, so you should be
able to use something like this:
colname = 'Program'
column = table.c[colname]
update = table.update(column=='project-name', values =
{test:'program'})
However, in general, if you want to get a named attribute of an object,
and the name is stored in a variable, you can use Python's getattr
function. This code should also work:
colname = 'Program'
column = getattr(table.c, colname)
update = table.update(column=='project-name', values =
{test:'program'})
Hope that helps,
Simon
--
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] SA on MySQL 3.23
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer
Sent: 03 June 2010 19:38
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] SA on MySQL 3.23
On Jun 3, 2010, at 1:15 PM, King Simon-NFHD78 wrote:
Hi,
According to sqlalchemy/dialects/mysql/base.py, MySQL v3.23
should be
supported in some form. However, with SA 0.6.1 and MySQL
3.23.58, I get
the following error:
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, You have an
error in your
SQL syntax near '('test unicode returns' AS CHAR(60)) AS
anon_1' at line
1)
According to
http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html,
the CAST function was added in 4.0.2.
Is there any way that I can avoid this error? Perhaps with
some engine
or dialect option that configures the
returns_unicode_strings attribute
without running the test?
heh wow, that little test we've added is proving to be quite
a PITA.OK so in this case its the CAST thats barfing ?
the options we could do here are:
1. have cast() do nothing with the MySQL dialect if the MySQL
version 4.0.2 (is there some MySQL-specific syntax that
works maybe ?)
2. have the MySQL dialect not run _check_unicode_returns if
the version 4.0.2
3. put the unicode checks in a try/except and default the
returns to False if something didn't work
since i dont have an old MySQL installed here, do you need me
to give you patches for these so you can test ?
I'll happily try any suggestions you've got :-)
I couldn't see anything in the MySQL docs that suggested an alternative
to the CAST function, so it seems reasonable to just omit it for older
MySQL servers. I applied the attached patch, and it at least allowed me
to connect to the server and issue basic queries, but I haven't done any
more testing than that.
Option 1 sounded best to me just because I didn't know if there would be
any other places that SA might implicitly run a query that included a
CAST. I suppose it changes the semantics of the query though...
I've tried to run the unit tests, but I get lots of errors and failures
that I assume are expected on such an old version of MySQL.
Cheers,
Simon
--
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.
mysql_no_cast.patch
Description: mysql_no_cast.patch
RE: [sqlalchemy] SA on MySQL 3.23
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: 04 June 2010 14:42 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] SA on MySQL 3.23 On Jun 4, 2010, at 6:54 AM, King Simon-NFHD78 wrote: I'll happily try any suggestions you've got :-) I couldn't see anything in the MySQL docs that suggested an alternative to the CAST function, so it seems reasonable to just omit it for older MySQL servers. I applied the attached patch, and it at least allowed me to connect to the server and issue basic queries, but I haven't done any more testing than that. Option 1 sounded best to me just because I didn't know if there would be any other places that SA might implicitly run a query that included a CAST. I suppose it changes the semantics of the query though... I've tried to run the unit tests, but I get lots of errors and failures that I assume are expected on such an old version of MySQL. its not entirely my usual style to have an operator emit nothing on a given platform instead of failing, but because this is such an old MySQL version and cast is a little bit of a crossover operator it isn't bothering me much here. I can commit your patch with an extra artificial compiler test in dialect/test_mysql.py to ensure it does what's expected; if you want to tool around with it a bit this week, let me know that we're good with it. If you're more comfortable with a version that just doesn't call _check_unicode_returns, or that catches the exception, either would be fine with me. I just sent the first thing I tried that seemed to work. I agree that silently converting CAST to nothing might mask other bugs, and so probably isn't ideal. Which would be your preference then? Catching the exception, or not calling the method in the first place? I'll make a patch for whichever you prefer and test it next week. Thanks again, Simon -- 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] reflecting existing databases with no a priori knowledge of their structure
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Harry Percival
Sent: 03 June 2010 16:24
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] reflecting existing databases with no a
priori knowledge of their structure
Hi All,
I'm building a tool to extract info from databases. The
user/programmer doesn't have any advance knowledge of the structure of
the database before they load it, and i want to dynamically generate
mapped classes for the database.
i just want to check there isn't some helpful sqlalchemy stuff that
can make my life easier, cos it feels harder than it should be.
sqlsoup seems to expect you to know table names ahead of time. i can't
find a way of extracting a list of table names from db =
SqlSoup(engine) and i'm finding myself generating classes on the fly
using the type() function.
stuff like:
meta.reflect(bind=engine)
tables = meta.raw_tables
class MyTable(object):
pass
for t in tables:
tempclass =
type('Table%d'%counter,(MyTable,),{'engine':self.engine})
mapper(tempclass,t)
then i use a bunch of classfunctions hanging off MyTable to do things
like return select alls ... anyways, this feels harder than it should
be. am i missing something? or is sqlalchemy simply not really used
much to work with existing / arbitrary databases?
I'm not quite sure what you're asking for. Once you've used meta.reflect to
reflect all your tables, you can pass that metadata instance to the SqlSoup
constructor. So you now have all the table names available in MetaData.tables
(or MetaData.sorted_tables), and you can access the mapped classes via
SqlSoup.entity(table_name).
For example:
import sqlalchemy as sa
from sqlalchemy.ext.sqlsoup import SqlSoup
meta = sa.MetaData('db://user:passw...@host/database')
meta.reflect()
db = SqlSoup(meta)
for table in meta.sorted_tables:
cls = db.entity(table.name)
print cls
print cls.get(1)
Hope that helps,
Simon
Hope that helps,
Simon
--
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] SA on MySQL 3.23
Hi,
According to sqlalchemy/dialects/mysql/base.py, MySQL v3.23 should be
supported in some form. However, with SA 0.6.1 and MySQL 3.23.58, I get
the following error:
import sqlalchemy as sa
e = sa.create_engine('mysql://user:passw...@host')
e.execute('select Hello World')
Traceback (most recent call last):
File stdin, line 1, in module
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/base.py,
line 1714, in execute
connection = self.contextual_connect(close_with_result=True)
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/base.py,
line 1742, in contextual_connect
self.pool.connect(),
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
157, in connect
return _ConnectionFairy(self).checkout()
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
321, in __init__
rec = self._connection_record = pool.get()
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
176, in get
return self.do_get()
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
670, in do_get
con = self.create_connection()
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
137, in create_connection
return _ConnectionRecord(self)
File /ve/sa6/lib/python2.5/site-packages/sqlalchemy/pool.py, line
217, in __init__
l.first_connect(self.connection, self)
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/strategies.py,
line 145, in first_connect
dialect.initialize(c)
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/dialects/mysql/base.py,
line 1755, in initialize
default.DefaultDialect.initialize(self, connection)
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/default.py, line
138, in initialize
self.returns_unicode_strings =
self._check_unicode_returns(connection)
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/default.py, line
183, in _check_unicode_returns
unicode_for_varchar = check_unicode(sqltypes.VARCHAR(60))
File
/ve/sa6/lib/python2.5/site-packages/sqlalchemy/engine/default.py, line
173, in check_unicode
]).compile(dialect=self)
File
/ve/sa6/lib/python2.5/site-packages/MySQL_python-1.2.2-py2.5-linux-i686
.egg/MySQLdb/cursors.py, line 166, in execute
self.errorhandler(self, exc, value)
File
/ve/sa6/lib/python2.5/site-packages/MySQL_python-1.2.2-py2.5-linux-i686
.egg/MySQLdb/connections.py, line 35, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, You have an error in your
SQL syntax near '('test unicode returns' AS CHAR(60)) AS anon_1' at line
1)
According to http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html,
the CAST function was added in 4.0.2.
Is there any way that I can avoid this error? Perhaps with some engine
or dialect option that configures the returns_unicode_strings attribute
without running the test?
Thanks a lot,
Simon
--
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: For each begin_nested() call, a corresponding rollback() or commit() must be issued.
Kent wrote: [SNIP] I'm fine with how SQLA is designed, it isn't really a SQLA issue, I was just appealing to you to see if you could think of a workaround I believe the problem is in the framework tools we are using, whether it is Zope or TG. (I've posted to zope group now to see if they intended to support savepoints and how, etc.). Since that framework won't allow me to issue the command session.commit(), I cannot release the savepoints until the zope transaction commits, and by then I'm getting a python max recursion problem because there are so many outstanding savepoints for it to release. From a quick reading of the 'transaction' package source, it looks like you should be able to create savepoints and roll them back something like this: savepoint = transaction.savepoint() try: # ... except: savepoint.rollback() raise I've no idea if that will confuse SA's accounting mechanisms though - I imagine you'd probably need to clean up some objects in the session. If you have a look at the tg.configuration module, the transaction middleware is added based on the config.use_transaction_manager value, so you could set that to False and implement whatever transaction management features you want in your own middleware. Hope that helps, Simon -- 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] Use a foreign key mapping to get data from the other table using Python and SQLAlchemy.
Az wrote:
[SNIP]
The following code maps these classes to respective database tables.
# SQLAlchemy database transmutation
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
customers_table = Table('customers', metadata,
Column('uid', Integer, primary_key=True),
Column('name', String),
Column('email', String)
)
orders_table = Table('orders', metadata,
Column('item_id', Integer, primary_key=True),
Column('item_name', String),
Column('customer', Integer, ForeignKey('customers.uid'))
)
metadata.create_all(engine)
mapper(Customer, customers_table)
mapper(Orders, orders_table)
Now if I do something like:
for order in session.query(Order):
print order
I can get a list of orders in this form:
Item ID 1001: MX4000 Laser Mouse, has been ordered by customer no.
12
=
What I want to do is find out customer 12's name and email address
(which is why I used the ForeignKey into the Customer table). How
would I go about it?
=
You need to add a relationship between the two classes. This is
documented at
http://www.sqlalchemy.org/docs/ormtutorial.html#building-a-relationship.
The documentation is using the declarative form. If you want to continue
to use the distinct table definitions followed by mapper definitions, it
would look something like this:
mapper(Orders, orders_table, properties={
'customer_object': relationship(Customer, backref='orders')
})
This will add a 'customer_object' property to the Orders class which
returns the corresponding Customer object. The backref='orders'
parameter means that the Customer object will also get an 'orders'
property which will be a list of all orders owned by the Customer.
You might find it more convenient if your existing 'customer' column was
actually called something like 'customer_id', then you could call your
relationship property 'customer' instead. If you didn't want to rename
the actual column in the database, you can still ask SQLAlchemy to use a
different name for the column, as demonstrated in
http://www.sqlalchemy.org/docs/mappers.html#customizing-column-propertie
s
Hope that helps,
Simon
--
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 lifecycle and wsgi
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers Sent: 28 April 2010 14:37 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] session lifecycle and wsgi Hi All, I'm still trying to get an answer on this... Am I right in understanding that the basic session lifecycle should be: try: use session session.commit() except: log() session.rollback() finally: session.remove() The structure I've traditionally used with transactions has been: try: use session except: log() session.rollback() else: session.commit() Is this okay? Why would the first setup be preferable? (ie: what's wrong with my location of the commit() call?) What happens when the remove() call is omitted()? Have you read http://www.sqlalchemy.org/docs/session.html#lifespan-of-a-contextual-ses sion - it describes typical usage of a scoped session in a web application. In your traditional structure, you could get an exception during session.commit() which would not be handled in your exception handler. I believe (but I'm not certain) that after any kind of database exception, it is recommended that you roll back the existing transaction, as it is likely to be invalid anyway. Session.remove() ensures that the current session is removed from the scoped session registry. If you don't do this, I think that the next time this thread calls Session(), it'll get the old session back again, rather than creating a new one. Simon -- 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] declarative commit hook - onCommit()?
Daniel Robbins wrote: On Wed, Apr 28, 2010 at 10:04 AM, Chris Withers ch...@simplistix.co.uk wrote: Daniel Robbins wrote: Let's say that when a database record is added or updated, I need to perform some arbitrary action (in my case, ensuring that data in other tables is consistent with what is being committed.) What mechanisms are suggested for this? Mapper extesions: http://www.sqlalchemy.org/docs/05/reference/orm/interfaces.htm l#sqlalchemy.orm.interfaces.MapperExtension Thanks, Chris. Right now I am not defining a mapper, just a bunch of declarative classes. Can I still use MapperExtensions? The declarative docs include an example of using a MapperExtension: http://www.sqlalchemy.org/docs/reference/ext/declarative.html#mapper-con figuration Simon -- 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] Storing Nested Lists
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of greg Sent: 25 April 2010 22:59 To: sqlalchemy Subject: [sqlalchemy] Storing Nested Lists Hi All, I'm new to sqlalchemy. I've been reading the documentation and group archives, but can't really find an answer to my question. I suspect it's a question of terminology, and that I don't really know the term for what I'm looking for. Can I map a nested list to one column, and have my nested list returned to me intact? A simple example is a list like: ['a','b','c', ['x','y','z',['m','n','o']]] If anyone can point me in the right direction, I'd much appreciate it. Thanks. If you make the column a PickleType: http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#sqlalchem y.types.PickleType ...it should behave as you want. Simon -- 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] Is the mapper must have a primary_key?
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Gaicitadie
Sent: 16 April 2010 20:06
To: sqlalchemy
Subject: [sqlalchemy] Is the mapper must have a primary_key?
#!/usr/bin/python
# -*- coding: UTF-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Test(Base):
__tablename__ = 'test'
tid = Column(Integer)
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = Base.metadata
metadata.create_all(engine)
[SNIP]
It seems must make a primary_key for table，but my table need't
primary_key，what can i do？
The ORM part of SQLALchemy requires that you have some combination of columns
that uniquely identify a row in the database. This is so that when you load an
instance from the database, then modify it and flush your changes back to the
database, the updates actually get applied to the correct row. It also means
that if you load rows from the same table more than once in the same Session,
you always get the same instance back for a given row. Note that the columns
don't have to actually be a primary key in the database.
If you are treating the database as read-only and you have rows in your table
which really are identical, you may be better off just using the lower-level
SQL expression language part of SA, which doesn't have these constraints.
Hope that helps,
Simon
--
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] cls._state / cls._state.get('original') class 'sqlalchemy.orm.attributes.CommittedState'
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of jose soares
Sent: 16 April 2010 11:03
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] cls._state /
cls._state.get('original') class
'sqlalchemy.orm.attributes.CommittedState'
jo wrote:
Hi all,
I cannot find anymore the attribute _state :
if (not cls._state or not cls._state.get('original') or
(cls._state['original'].data.get(k) != data.get(k:
Could someone please help me?
thank you
j
To explain better my problem, in version 0.3 my models have the
attribute _state where I find the
class 'sqlalchemy.orm.attributes.CommittedState'
(Pdb) self._state
{'original': CommittedState: {'anagrafica_dato_fiscale':
u'02241850367/02241850367', 'anagrafica_id_operatore': 1,
'anagrafica_cap': None, 'anagrafica_telefono': None,
'anagrafica_email':
None, 'anagrafica_nome': u'AZ.USL MODENA distr. PAVULLO',
'anagrafica_cod_nazione_nascita': None, 'anagrafica_localita': None,
'anagrafica_tipo': u'S', 'anagrafica_cod_cittadinanza': None,
'anagrafica_tipo_persona': u'S', 'anagrafica_ts_ultima_modifica':
datetime.datetime(2010, 3, 3, 16, 4, 22, 50891,
tzinfo=psycopg2.tz.FixedOffsetTimezone object at 0xc13488c),
'anagrafica_id': 8, 'anagrafica_data_nascita': None,
'anagrafica_id_comune_nascita': None, 'anagrafica_id_comune': 15090,
'anagrafica_cod_professione': None, 'anagrafica_indirizzo': u'?',
'anagrafica_cod_titolo_studio': None}, 'modified': False}
I can't find this attribute anymore on version 0.6
j
I haven't used this, so I don't know if this helps, but you may be
interested in a couple of the functions described at
http://www.sqlalchemy.org/docs/reference/orm/mapping.html#attribute-util
ities
In particular, either the instance_state() function or the get_history()
function might be useful. I'm not sure there's much documentation for
either the History class or the InstanceState class so you'll have to
read the code in sqlalchemy.orm.state and sqlalchemy.orm.attributes
Hope that helps,
Simon
--
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] flush(), save(), delete()
Jo wrote: [SNIP] and- In [13]: aa=Anagrafica.get(111) In [14]: aa.delete() In [15]: aa.flush() - but in version 0.6 I can't find flush(), save(), delete(). Where are them? thank you j These methods were added to your objects by the old assign_mapper extension. This extension no longer exists, and the methods on the Session should be used instead. For example, instead of aa.delete(), you would say session.delete(aa). If you want to preserve your old API, you could create a base class for your mapped objects that implements each of the old methods. A delete method might look like this (untested): class Base(object): def _get_session(self): return sqlalchemy.orm.object_session(self) def delete(self): session = self._get_session() session.delete(self) The flush method would correspond to session.flush([self]), but you should read the deprecation warning about passing a list of objects at http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.or m.session.Session.flush. Assuming that the save() method adds the object to the current contextual (scoped) session, it would be as simple as: def save(self): session = Session() session.add(self) However, I personally wouldn't add that one, as it ties your class to the scoped session mechanism which may not always be what you want. Hope that helps, Simon -- 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] further restricting a query provided as raw sql
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Mariano Mara Sent: 15 April 2010 16:20 To: sqlalchemy Subject: Re: [sqlalchemy] further restricting a query provided as raw sql Excerpts from Chris Withers's message of Thu Apr 15 11:46:05 -0300 2010: Michael Bayer wrote: you have to rewrite your SQL to support the number of values in the IN clause for each parameter set. Hmm :'( While my code knows the number of values, they don't, and it may vary from when they write the SQL to when that SQL gets executed by my code... Chris My answer will be generic since I don't know id SA provide a better way to deal with it (I haven't face this situation yet). You will have to provide the values not as binded parameters but hardcoded instead: SELECT somestuff FROM somewhere WHERE some_date = :from_date AND some_date = :to_date AND somefield in (%s) % (,.join([str(x) for x in a])) if you don't like this kind of hack, depending on your database, you can create a temp table, insert all the values in it and join with your real table. ...and make sure that you properly escape all your values to prevent SQL injection. I guess another option would be to detect when lists are passed in, then replace ':values' with ':value0, :value1, :value2' etc. in the SQL. As long as you can be sure that the string ':values' doesn't appear anywhere else in the SQL, this wouldn't be too bad. Simon -- 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, Twisted, and sAsync
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Matthew Williams Sent: 26 March 2010 12:10 To: sqlalchemy@googlegroups.com; twisted-pyt...@twistedmatrix.com Subject: [sqlalchemy] Re: SQLAlchemy, Twisted, and sAsync On Mar 26, 2010, at 3:20 AM, Chris Withers wrote: Matthew Williams wrote: From previous posts to this and other lists, it seems that ORMs and threads don't get along too well... What makes you think that? First of all, most of my impressions about ORMs come from SQLAlchemy. This quote from this list (http://twistedmatrix.com/pipermail/twisted-python/2009-March/ 019359.html ) sums up what I have found as well: It's much trickier if you want to use the ORM, unless you are very careful to fully eager load every thing in any possible database operation if you have need of the information subsequently in your twisted code. Otherwise you may block unexpectedly simply when accessing your objects, and end up with database operations from the main twisted thread. So perhaps I should have said SQL Alchemy's ORM and threads don't get along too well... that's not to say it's impossible, you just have to be exceedingly careful how you use it. I think that point should be clarified, so that people don't later come across this post and just accept it without understanding. I imagine that SQLALchemy is used in a lot of threaded applications. For example, it is the recommended ORM in web frameworks such as Pylons and TurboGears, which work fine in threaded environments. However, typically in these libraries a web request is handled by a single thread, and all the SQLAlchemy operations occur within the scope of that request. As long as you don't share a Session instance between the threads, you won't have any problems. SQLAlchemy provides a ScopedSession class which helps in these situations, as you can call the constructor many times on a single thread and always get the session instance back for that thread. Sessions themselves aren't thread-safe. When an instance is loaded from the database, it is linked to the session that loaded it. This means that when you have lazy-loading properties on that instance (such as related classes, or deferred column properties), they will be automatically loaded when they are accessed, in the same session. This will cause a problem if you load an instance in thread A, hand the object off to thread B, and then thread B accesses one of these lazy-loading properties. The load will occur in thread A's session, which might be in the middle of doing something else. The solution to this is either to eager-load all the attributes you think you are going to need before handing the instance off to another thread (difficult), or (probably better) to detach (expunge) the instance from thread A's session. Thread B should then merge the object into its own session (using the load=False flag so that it doesn't needlessly requery the database). The Session docs at http://www.sqlalchemy.org/docs/session.html explain the lifecycle of loaded instances. I haven't actually done any of this - I've only ever used SA from TG and command-line scripts, but I think the principles are about right. I hope that helps, Simon -- 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] recommended declarative method design pattern for sessions
Daniel Robbins wrote: Hi All, One of the things that doesn't seem to be covered in the docs, and that I'm currently trying to figure out, is the recommended design pattern to use for managing sessions from declarative methods calls. Consider a declarative class User, where I want to implement a FindFriends() method: class User(Base): # declarative fields defined here def FindFriends(self): session = Session() # it's handy to use the self reference in query methods: friends = session.query(Friends).filter_by(friend=self).all() session.close() return friends Certainly, these types of methods would seem to be useful, but here's a dilemma - the above code doesn't work. Because the method uses a new session, which is guaranteed to not be the same session that was used to retrieve the original User object, the following code will fail: session = session() me = session.query(User).filter_by(name=Daniel).first() me.FindFriends() See the 'How can I get the Session for a certain object' question at http://www.sqlalchemy.org/docs/session.html#frequently-asked-questions Basically, in your FindFriends method, replace: session = Session() with: session = Session.object_session(self) Hope that helps, Simon -- 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] extended use of VALUES command
Manlio Perillo wrote: Hi. Is it possible, in SQLAlchemy, to express this query? CREATE TEMP TABLE foo ( x INTEGER, y INTEGER ); INSERT INTO foo VALUES (10, 11); INSERT INTO foo VALUES (1, 2); manlio= SELECT * FROM foo WHERE (x, y) in (VALUES (1, 2), (3, 4)); x | y - ---+--- 1 | 2 (1 riga) This should be standard SQL. It is supported by PostgreSQL [1], but SQLite fails with a syntax error. [1] http://www.postgresql.org/docs/8.4/static/sql-values.html Thanks Manlio Does the following thread help at all? http://groups.google.com/group/sqlalchemy/browse_thread/thread/7f950b628 d7ebee5/4421b272d4c7f91f Hope that helps, Simon -- 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] reflecting schema just on a single table
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Paul Rigor (uci) Sent: 24 February 2010 00:23 To: sqlalchemy Subject: [sqlalchemy] reflecting schema just on a single table Hi, Is there anyway to use a metadata object just to obtain the schema of a single table? I have a database of hundreds of tables, and calling MetaData.reflect() retrieves the schema for all of the tables. This unnecessarily uses up memory and incurs additional time for i/o to complete. Thanks, Paul Yes - see http://www.sqlalchemy.org/docs/metadata.html#reflecting-tables Hope that helps, Simon -- 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] Oracle Views/DatabaseLink and declarative
Christian Klinger wrote: [SNIP] Ok now a second table comes into the game. The name of this table is BTeilnehmer. As you can see this table has a ForeignKey to Unternehmen.mnr. I use a seperate metadata BaseC for it because i want to create this table. Hi, I don't know if the seperate base class is the cause of your problem, but you don't have to do that just to be able to create the table. All metadata.create_all() does is call create() on each of the Table objects. So you should be able to do something like this to create the BTeilnehmer table: BTeilnehmer.__table__.create() Hope that helps, Simon -- 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 a arbitrary select mapper/class in a relation - is this allowed?
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of werner
Sent: 04 February 2010 09:41
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Using a arbitrary select
mapper/class in a relation - is this allowed?
On 03/02/2010 20:25, Michael Bayer wrote:
werner wrote:
In my model I have:
class Country(BaseExt):
pass
sao.mapper(Country, createSelect(Country_D, Country_T,
'countries_d_id',
['name', 'url']))
Which I can use like this:
for cs in session.query(db.Country).all():
print cs.name, cs.id
But I run into problems when I try to use Country in a
relation like
this:
class Region_D(Base, CreateUpdateMixin):
__tablename__ = u'regions_d'
id = sa.Column(sa.Integer(), sa.Sequence('regions_d_id'),
primary_key=True, nullable=False)
name = sa.Column(sa.String(length=50, convert_unicode=False))
countries_d_id = sa.Column(sa.Integer())
country = sao.relation('Country', backref='region_d',
primaryjoin='Region_D.countries_d_id == Country.id')
I am getting this exception also Country is defined
before Region_D:
if you use 'Country' as a string in relation(), the
declarative base
looks for it inside of Base._decl_class_registry. Its not
here since
Country isn't part of Base. You should be saying
Country, i.e. send
the actual class, to the relation().
Thanks, just tried this but I get the same exception.
Just to confirm, were you actually defining your Region_D class exactly
like this:
class Region_D(Base, CreateUpdateMixin):
__tablename__ = u'regions_d'
id = sa.Column(sa.Integer(), sa.Sequence('regions_d_id'),
primary_key=True, nullable=False)
name = sa.Column(sa.String(length=50, convert_unicode=False))
countries_d_id = sa.Column(sa.Integer())
country = sao.relation(Country, backref='region_d',
primaryjoin=countries_d_id == Country.id)
Ie. Neither the class nor the primaryjoin parameters in the relation
were strings? I would be surprised if you got the exception you
described (expression 'Country' failed to locate a name ) if you had
done that, because SA wouldn't be trying to look up that name.
Simon
--
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] Another tutorial!
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Mike Driscoll Sent: 04 February 2010 03:34 To: sqlalchemy Subject: [sqlalchemy] Another tutorial! Hi, I just finished up a tutorial series on SqlAlchemy that I thought I'd share: http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-s tep-sqlalchemy-tutorial-part-1-of-2/ http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-s tep-sqlalchemy-tutorial-part-2-of-2/ Hopefully it's made well enough that people can follow the tutorial easily. Let me know if I made any serious blunders. Thanks, Mike Hi Mike, Not a serious blunder, but I think there may be a small mistake in part 2, where you describe updating an email address: # change the first address prof.addresses[0] = Address(pr...@marvel.com) I don't think this is going to update the 'pr...@dc.com' row in the database to say 'pr...@marvel.com'. Instead, it is going to disconnect that row from the user by setting the user_id to NULL, and add a new row with the new address. (This may be what you intended, but I don't think it's clear from the description). I would have thought that you'd actually want to write this: # change the first address prof.addresses[0].email_address = pr...@marvel.com Hope that helps, Simon -- 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: Another tutorial!
Mike Driscoll wrote:
On Feb 4, 8:30 am, Mike Driscoll kyoso...@gmail.com wrote:
On Feb 4, 4:24 am, King Simon-NFHD78 simon.k...@motorola.com
wrote:
[SNIP]
Not a serious blunder, but I think there may be a small
mistake in part
2, where you describe updating an email address:
# change the first address
prof.addresses[0] = Address(pr...@marvel.com)
I don't think this is going to update the 'pr...@dc.com'
row in the
database to say 'pr...@marvel.com'. Instead, it is going
to disconnect
that row from the user by setting the user_id to NULL,
and add a new row
with the new address. (This may be what you intended, but
I don't think
it's clear from the description).
I would have thought that you'd actually want to write this:
# change the first address
prof.addresses[0].email_address = pr...@marvel.com
Hope that helps,
Simon
I was testing this in IDLE and it seemed to work when I did
prof.addresses to check it. If the user gets set to NULL, wouldn't
prof.addresses only show one entry? I'll check it out and make sure.
If I messed it up, I'll get it fixed. Thanks for the bug report!
- Mike
I just ran through that section again using the Python interpreter and
after changing the address like this:
prof.addresses[0] = Address(pr...@marvel.com)
I then used the following (per the official tutorial):
prof.addresses[0].user
User('Prof','Prof. Xavier', 'fudge')
So my method appears to work. I tried your method too:
prof.addresses[0].email_address = prof...@image.com
prof.addresses[0].user
User('Prof','Prof. Xavier', 'fudge')
That appears to give the same result. Let me know if I am
misunderstanding something basic here.
The difference is that in your case, there is now a row in the Address table
without an associated User. Try running the following:
for address in session.query(Address):
print Address %r belongs to User %r % (address, address.user)
I think you will see addresses that don't belong to any users.
Simon
--
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: Another tutorial!
Mike Driscoll wrote: Thanks Simon! That made sense. I've fixed my example to match what you said. Sorry about that. - Mike No problem. I'm afraid you still have a typo though. You have: addresses[0].email_address = Address(pr...@marvel.com) Whereas you want: addresses[0].email_address = pr...@marvel.com :-) Simon -- 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] Getting useful error messages
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Adam Tauno Williams Sent: 03 February 2010 12:17 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Getting useful error messages Attempting, again, to get a declarative representation of a two table entity http://groups.google.com/group/sqlalchemy/browse_thread/threa d/b0ce69e368b444dd/bcb9c287f3e9d939?lnk=raot, but I keep getting errors like: Traceback (most recent call last): File stdin, line 1, in module File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg /sqlalchemy/orm/session.py, line 895, in query return self._query_cls(entities, self, **kwargs) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg /sqlalchemy/orm/query.py, line 91, in __init__ self._set_entities(entities) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg /sqlalchemy/orm/query.py, line 98, in _set_entities entity_wrapper(self, ent) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg /sqlalchemy/orm/query.py, line 2017, in __init__ raise sa_exc.InvalidRequestError(Invalid column expression '%r' % column) sqlalchemy.exc.InvalidRequestError: Invalid column expression 'class '__main__.TaskAction'' - - which is useless [I'm having Java flashbacks]. Is there a way to get more useful debugging information from sqlalchemy? Like maybe what expression '%r' was or the column in question? The line below the one you're complaining about is telling you what the column in question is: Invalid column expression 'class '__main__.TaskAction'' So somehow, you've passed your TaskAction class in a place where SA is expecting a column expression. I think we'd need to see the command that you actually typed in to work out what the problem is. Simon -- 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] problem when executing multiple insert statements and boolean type
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Manlio Perillo
Sent: 29 January 2010 13:15
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] problem when executing multiple insert
statements and boolean type
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I'm here again with a problem I don't know if it is a bug in
SA or in my
code.
Here is the offending code:
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('x', types.Integer, primary_key=True),
schema.Column('y', types.Boolean, default=True, nullable=False)
)
engine = create_engine('sqlite://')
engine.create(metadata)
try:
params = [ {'x': 1}, {'x': 2, 'y': False} ]
engine.execute(test.insert(), params)
print engine.execute(test.select()).fetchall()
finally:
engine.drop(metadata)
This should print:
[(1, True), (2, False)]
and instead it prints
[(1, True), (2, True)]
Thanks Manlio
This is explained in the last paragraph of
http://www.sqlalchemy.org/docs/sqlexpression.html#executing-multiple-sta
tements:
When executing multiple sets of parameters, each dictionary must have
the same set of keys; i.e. you cant have fewer keys in some dictionaries
than others. This is because the Insert statement is compiled against
the first dictionary in the list, and it's assumed that all subsequent
argument dictionaries are compatible with that statement.
I think a check has been added in 0.6 so that an exception is raised if
you don't follow this advice.
Hope that helps,
Simon
--
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] Map to Arbitrary Select Using Raw SQL
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: 27 January 2010 16:31 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Map to Arbitrary Select Using Raw SQL Michael Chambliss wrote: Hey Michael - thanks for the patient and helpful response. I played around with the from_statement() approach earlier today, but what I was able to derive seemed to follow the standard model of define table, define class, map table to class, execute query. That approach would be great assuming I can map to some composite result (IE, multi-table/function). Perhaps I need to dive further into this to determine how joins are handled and how the mapping should be defined for them. The original example I linked seemed to imply some mapping magic in that the Customer class wasn't defined but was mapped to the complex Selectable. However, my research and attempts to do this mapping with from_statement() proved fruitless. In a theoretical example, say I have a CAR table that refers to both a CAR_TYPE table and CAR_ATTRIBUTES table. CAR_TYPE is simply an enumeration for a static list of types, and CAR_ATTRIBUTES is an arbitrarily long list of key,value attributes (color, weight, top speed, etc). So, ultimately, a Car is made up of these three. I'd want to bake all of these together, passing in a CAR.ID (primary key) to map to a Car instance. I prefer to live in SQL because I'm pretty good at it, and I need to reference, specifically, Oracle Spatial and Workspace functions. I do not, however, need to chain additional filters off of this, handle updates/inserts (at least at this point), etc. I'm literally just looking for a cheap way to map a row to an object and scoop up connection pooling, type handling, and other great things I'll probably learn about as I go. Assuming you've configured Car, CarType and CarAttributes with mappers, My reading of the original email is that configuring the mapping is the problem that the OP is having. To the OP: As far as I'm aware, you can't configure a mapper directly against a textual SQL statement. However, you might be able to get away with using the declarative syntax to define your Car class: http://www.sqlalchemy.org/docs/reference/ext/declarative.html#synopsis You'll have to give it a table name, which will have the effect of defining a Table object even though no such table exists in the database, but I don't think this matters. Then you could use the query that Mike suggested to actually retrieve rows. Hope that helps, Simon -- 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: Is it possible to narrow down the generated query in SQLAlchemy if it was created via query_property?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Boda Cydo Sent: 26 January 2010 01:35 To: sqlalchemy Subject: [sqlalchemy] Re: Is it possible to narrow down the generated query in SQLAlchemy if it was created via query_property? On Jan 25, 2:46 am, Boda Cydo bodac...@gmail.com wrote: Let me know if the question is not clearly stated. I'll update it with more details. Any ideas? When you access Comment.query, you are getting back an already instantiated Query object which, as the error message indicates, isn't callable. I guess I don't really understand why you want to use Comment.query(Comment.comment) rather than Session.query(Comment.comment). If you really want this, you could subclass Query to add a __call__ method that creates a new query instance: class CallableQuery(Query): def __call__(self, *args, **kwargs): return Session.query(*args, **kwargs) class Comments(Base): query = Session.query_property(query_cls=CallableQuery) ...but I'm still not sure what the point is. Simon -- 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?
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers Sent: 26 January 2010 09:13 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] any way to pre cook a monster query? Hi All, I have a few monster queries like this: [SNIP] Now, is there any way I can pre-cook this (eg: at module-level) such that I can later just plug in self.id and on_date, bind to a session and call .all() on it? It seems a bit wasteful to do all the SQL generation on every query when it's almost all identical all the time... cheers, Chris I think you can use bind parameter objects for this: http://www.sqlalchemy.org/docs/sqlexpression.html#bind-parameter-objects ...and use the query's params method to supply the values. Hope that helps, Simon -- 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] dynamic_loader
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of werner
Sent: 25 January 2010 13:37
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] dynamic_loader
On 24/01/2010 16:57, werner wrote:
...
Next thing is to make _get_translation reusable for
different tables.
I got it down to this:
def _do_translation(bInstance, tTable, fCrit, cLang, dLang):
try:
x =
sao.object_session(bInstance).query(tTable).with_parent(bInsta
nce).filter(fCrit==cLang)
return x.one()
except sao.exc.NoResultFound:
try:
x =
sao.object_session(bInstance).query(tTable).with_parent(bInsta
nce).filter(fCrit==dLang)
return x.one()
except sao.exc.NoResultFound:
return 'no translation found'
class Country_B(Base):
__table__ = sa.Table(u'countries_b', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('countries_b_id'),
primary_key=True, nullable=False),
..
)
def _get_translation(self):
return _do_translation(self, Country_T,
Country_T.lang_code5,
getCurrentUserLang, getDefaultUserLang)
country_t = sao.relation('Country_T', backref='country_b')
country = property(_get_translation)
But would like to remove the def _get_translation and call directly
_do_translation, something like this:
country = property(_do_translation('Country_B', 'Country_T',
'lang_code5', getCurrentUserLang, getDefaultUserLang))
But can't figure out how I would then get at the instance of
Country_B
within _do_translation.
As always tips or pointers to documentation are very appreciated.
Werner
Hi Werner,
You need to implement your own 'property'-like class that implements the
descriptor protocol. This page might give you some clues:
http://users.rcn.com/python/download/Descriptor.htm#descriptor-example
If you had a TranslationProperty class that looked like this:
class TranslationProperty(object):
def __get__(self, obj, objtype):
# Call _do_translation(obj, ...) here
Then your Country class can look like:
class Country_B(Base):
country_t = TranslationProperty()
When you access the country_t attribute, the __get__ method will be
called with your Country_B instance as the obj parameter, and the
Country_B class as the objtype parameter, which you can hopefully pass
on to the _do_translation function.
I hope that helps,
Simon
--
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: column label and order by
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of rajasekhar911
Sent: 17 November 2009 11:32
To: sqlalchemy
Subject: [sqlalchemy] Re: column label and order by
anyone??
On Nov 14, 6:48 pm, rajasekhar911 rajasekhar...@gmail.com wrote:
Hi guys,
how do i apply order by on a column with a label.
My requirement is like this
class x
id,
amount,
date
i have to group based on id and take sum of amount within a date
range.
i am applying a label to sum of amount
now how do i order based on that so that i can get top 5 ..
session.query( func.sum(x.amount).label('tot_amount'), x.id ).
filter(x.datefromdate).filter(x.datetodate).
.group_by(x.id)
.order_by(?)
.limit(5)
thanks.
How about (untested):
tot_amount = func.sum(x.amount).label('tot_amount')
session.query(tot_amount, x.id).
filter(x.datefromdate).filter(x.datetodate).
.group_by(x.id)
.order_by(tot_amount)
.limit(5)
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: bad result when querying for null values (in pk at least)
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Mike Conley Sent: 15 October 2009 14:43 To: sqlalchemy@googlegroups.com Cc: SQLElixir Subject: [sqlalchemy] Re: bad result when querying for null values (in pk at least) On Thu, Oct 15, 2009 at 9:21 AM, alexbodn.gro...@gmail.com wrote: thanks a lot mike, it's working great now. but this flag should be implied if one of the primary key fields is nullable (especially since it's not nullable by default). what would you think? You can argue just as easily that the null primary key means the record is incomplete and should not be available. It's an application code issue. According to http://www.sqlalchemy.org/trac/wiki/06Migration the flag will be turned on by default in 0.6 Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Declerative Relation trouble
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling
Sent: 15 October 2009 14:42
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Declerative Relation trouble
Hi All,
I am having a very bad day (or two to be honest), spending
time reading error messages.
I am sorry to say but the SQLALCHEMY documentation is not very helpful
when using declarative_base when it is about relations..
Without a runnable example which actually shows your problem, it's very
difficult to debug. Here's something I cobbled together based on your
description. It may not be exactly right, but it seems to work:
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class XProp(Base):
__tablename__ = CalendarXProps
Id = sa.Column(sa.Integer, primary_key=True)
EventId = sa.Column(sa.ForeignKey('CalendarEvents.Id'))
AlarmId = sa.Column(sa.ForeignKey('CalendarAlarms.Id'))
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
Name = sa.Column(sa.String(20))
Value = sa.Column(sa.String(20))
class Event(Base):
__tablename__ = CalendarEvents
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Events')
class Alarm(Base):
__tablename__ = CalendarAlarms
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Alarms')
class Calendar(Base):
__tablename__ = Calendars
Id = sa.Column(sa.Integer, primary_key=True)
Events = orm.relation(Event, backref='Calendar')
Alarms = orm.relation(Alarm, backref='Calendar')
XProps = orm.relation(XProp, backref='Calendar')
if __name__ == '__main__':
engine = sa.create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
cal.Events.append(Event(XProps=[XProp(Name='Hello', Value='World'),
XProp(Name='foo', Value='bar')]))
Session.add(cal)
Session.flush()
print cal
for event in cal.Events:
print event
for prop in event.XProps:
print prop
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Declerative Relation trouble
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling
Sent: 15 October 2009 15:38
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: Declerative Relation trouble
Hi Simon,
(I do things a little different on the import side)
Working example (very minimised):
from sqlalchemy import
Integer,Column,DateTime,Unicode,UnicodeText,Boolean,ForeignKey
,Interval
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy.orm as orm
Base = declarative_base()
class Event(Base):
__tablename__ = CalendarEvents
Id = Column(Integer, primary_key=True)
CalendarId = Column(ForeignKey('Calendars.Id'))
class Calendar(Base):
__tablename__ = Calendars
Id = Column(Integer, primary_key=True)
UserId = Column(Integer, index=True)
ProdId = Column(Unicode(255))
Version = Column(Unicode(5))
CalScale= Column(Unicode(20))
Method = Column(Unicode(10))
Events =
orm.relation(Event,backref='Calendar')#, cascade=all)
if __name__ == '__main__':
engine = create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
when I run this I get:
Traceback (most recent call last):
File /var/www/PyWebOs/caltst.py, line 28, in module
cal = Calendar()
File string, line 4, in __init__
File
/usr/local/lib/python2.6/site-packages/sqlalchemy/orm/state.p
y, line 71, in initialize_instance
fn(self, instance, args, kwargs)
File
/usr/local/lib/python2.6/site-packages/sqlalchemy/orm/mapper.
py, line 1810, in _event_on_init
instrumenting_mapper.compile()
File
/usr/local/lib/python2.6/site-packages/sqlalchemy/orm/mapper.
py, line 666, in compile
Message was: %s % mapper._compile_failed)
InvalidRequestError: One or more mappers failed to compile.
Exception was probably suppressed within a hasattr() call.
Message was: One or more mappers failed to compile.
Exception was probably suppressed within a hasattr() call.
Message was: One or more mappers failed to compile.
Exception was probably suppressed within a hasattr() call.
Message was: Could not find table 'Calendar' with which to
generate a foreign key
which is excacly the same as I got.
I have done so mutch in python/sqlalchemy that I feel
extremely stupid not to get this working, it might be just a
case of overreading the problem
Martijn
That script works for me (I needed to add 'create_engine' to the import
line) on SQLAlchemy 0.5.5. How are you running it?
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Declerative Relation trouble
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 15:55 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Declerative Relation trouble I downgraded SQLA to version 0.5.5, no luck, (Simon) what version of python are you using? 2.5.1 on Linux Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:21 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re:[runs one one installation not on the other] Declerative Relation trouble Hi All, bad problem I have trouble making relations in SQLA. My code runs on Simons computer but his (and mine) not. I downgraded SLQA from 0.5.6. to 0.5.5 to have the same version, still no luck. I run Python 2.6.2 and Simon 2.5.1 Downgrading Python is a hassle at the moment (Mod_python) so is there anything I can try? (Database changes give me the same results on both mysql and sqllite so that seems no problem) Martijn Are you running the test script from mod_python, or from the command line? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble Mod_python has nothing to do with this project, so I run it from idle within X I have a feeling that Idle doesn't necessarily spawn a separate process to run your code, so you may have old definitions of your objects in memory. Try running it directly from the command line. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Many to Many Across Two Databases
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Luke Arno Sent: 14 October 2009 16:41 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: ORM Many to Many Across Two Databases It looks like if I put the relation on the obj mapped to the DB where the association table is _not_, it works in one direction. (So, in the example Right.lefts can work but Left.rights cannot.) When trying to use Left.rights, it looks for the table in the wrong database. It appears that it would be fine if I could just get the table names all qualified with database name in the issued SQL. Is there a way to make that happen, by any chance? You can do this by using a single engine and metadata, and passing a 'schema' parameter when defining your tables: http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na me Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Duck-typing style of relations
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Yannick Gingras Sent: 09 October 2009 14:43 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Duck-typing style of relations Greetings Alchemists, this is more of a general data modeling question but maybe Alchemy has a neat trick to resolve this issue. It happens quite often that I want to use instances of a class as attributes of unrelated objects. One example is Addresses. Both Companies and Persons have addresses and it would be somewhat awkward to derive both from a common ancestor, even though mixins would do the trick. However, the concept of mixins is not straightforward to transpose to data mapping. The Address example could be implemented as follow: [snip] Mike wrote a blog post about this a while ago: http://techspot.zzzeek.org/?p=13 It even uses Addresses as the example. It's quite old, so some of the syntax will probably need updating, but the basic idea should still hold. Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Select from multiple databases
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of kkapron
Sent: 01 October 2009 21:27
To: sqlalchemy
Subject: [sqlalchemy] Select from multiple databases
Hello, I'm a beginner in sqlalchemy and I've got a problem with select
statement.
example:
tabela T1: id, username, date (in database B1)
tabela T2: id, user_id, ip (in database B2)
I've got two engines
...
self.db['B1']['engine'] = create_engine('mysql://B1')
self.db['B1']['conn'] = self.db['B1']['engine'].connect()
self.db['B1']['metadata'] = MetaData()
self.db['B1']['metadata'].bind = self.db['B1']['engine']
self.db['B1']['metadata'].create_all()
self.db['B2']['engine'] = create_engine('mysql://B2')
self.db['B2']['conn'] = self.db['B1']['engine'].connect()
self.db['B2']['metadata'] = MetaData()
self.db['B2']['metadata'].bind = self.db['B2']['engine']
self.db['B2']['metadata'].create_all()
...
and tables
...
self.tables['T1'] = Table('T1', self.db['B1']['metadata'],
autoload=True)
self.tables['T2'] = Table('T2', self.db['B2']['metadata'],
autoload=True)
...
and a test query:
...
T1 = self.tables['T1']
T2 = self.tables['T2']
s = select( [T1.c.username, T2.c.ip], (T2.c.user_id == T1.c.id) )
s.execute().fetchall()
and error:
(ProgrammingError) (1146, Table 'B2.T1' doesn't exist)...
it's true that T1 doesn't exists in B2, because it exist in T2.
Does anybody know how to help me? :)
I don't think you can do this - the 'select' function represents a
single SQL SELECT statement, which can't be sent to 2 different database
servers. You'd need to run two separate queries and join the results in
Python.
If your tables are actually in different schemas but the same MySQL
instance (ie. If you can connect to the MySQL server and write 'SELECT *
FROM B1.T1' and 'SELECT * FROM B2.T2'), then you can use a single engine
and metadata to access them both by specifying the schema in your Table
definitions. See:
http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na
me
for an example.
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Bypass checking to database structure (metadata.create_all)
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Christian Démolis Sent: 01 October 2009 10:40 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Bypass checking to database structure (metadata.create_all) Hi again, Is there any way to avoid checking database structure during the metadata.create_all declaration's phase? It can be good to check when we are in test phase but when we are in production and we are sure of our model, it can be good to bypass create_all checking to database. create_all (and drop_all) have a 'checkfirst' parameter that defaults to True. If you set it to False, SA won't check to see if your tables already exist before issuing the CREATE statements: http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html#sqlalchemy.schema.MetaData.create_all Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: unexpected chained relations and append behaviour
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Crusty Sent: 24 September 2009 16:16 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: unexpected chained relations and append behaviour Hello Simon, thanks for your answer, I will have a look into that. By the way: len(car.parts) does indeed work, try it ;) Greetings, Tom len(car.parts) works with your current configuration, because accessing car.parts loads the entire relation and returns it as a python list. But if you change it to be a 'dynamic' relation, it will no longer be a list but a Query instance, which no longer has a __len__ method. Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: unexpected chained relations and append behaviour
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Crusty Sent: 23 September 2009 15:48 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] unexpected chained relations and append behaviour Hello everyone, I have a realy simple model for you to consider: 1 car has n wheels car.wheels is a relation from cars to wheels wheel.car is a backref to cars 1 car has n parts car.parts is a relation from car to parts I just wondered why my app was really getting slow, turned on SA debug mode, and saw that my_new_doorknob = model.Part(doorknob) wheel.car.parts.append(my_new_door_knob) is downloading the entire parts table WHERE parts.car == car.id (that is around 20.000 entries) just so that it can append my new doorknob to that relation. Furthermore I noticed a similar behaviour when doing something like this: amount_of_parts = len(car.parts) Instead of sending a COUNT to the database, it populates the entire car.parts relation (around 20.000 entries) just to get the count. Of course I could avoid using relations, and just use my __init__ functions, or setting: my_new_doorknob = model.Part(doorknob) my_new_doorknob.car_id = car.id DBSession.append(my_new_doorknob) But then I could as well just write literal SQL if I cant use the R part of ORM... Has anyone observed similar behaviour or is this a feature and intended to work like this? Greetings, Tom Yes, this is exactly how it is intended to work. You may like to read http://www.sqlalchemy.org/docs/05/mappers.html#working-with-large-collec tions for hints on how to improve performance. In particular, making your car.parts property a 'dynamic' relation rather than the default will prevent SA from loading the entire collection unless you specifically ask it to. However, the len(car.parts) line won't work. SA deliberately doesn't implement the __len__ method for Query objects because it is called implicitly by python in a number of situations, and running a potentially slow query when you aren't expecting it is a bad idea. Instead you would use car.parts.count(). Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pre-commit validation spanning multiple tables/ORM classes
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Randall Nortman Sent: 22 September 2009 16:31 To: sqlalchemy Subject: [sqlalchemy] Pre-commit validation spanning multiple tables/ORM classes [SNIP] So if I do this with a SessionExtension.before_commit(), I would have to iterate through the new, dirty, and deleted instances lists, inspect the type of each instance, and do whatever is required. I am not sure, though, how to handle the case of a change in membership in the parent/child relationship -- the child instance that is present in the dirty list will have only the new parent on it -- how do I find out what the old parent was, so I can validate it? If a flush has already occurred, the old value is already lost in the context of the current transaction, and I think that if I open a new transaction inside a before_commit() validator I'm just asking for trouble. Do I need to instrument the Child class with a descriptor that tracks changes to the parent and remembers the old parent? Or can I set the cascade option in such a way that the old parent will end up in the dirty list, even though there are no changes to its underlying table, and in fact it may never have been explicitly loaded into the session? (I must admit to be somewhat unsure of what the different cascade options do -- but they don't seem to be useful for tracking something like this.) I can't answer most of your question, but as far as finding out what the old parent was, could you use the get_history function? http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html#sqlalchemy .orm.attributes.get_history Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: getting data from primary keys
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of C.T. Matsumoto Sent: 15 September 2009 07:21 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: getting data from primary keys That did the trick. Thanks a lot. Your solution uses the orm sessionmaker. Till now my script was relying on sqlalchemy's expression language. Is there some way of doing the same with the expression language? Or would it get too complicated? (Just curious) Cheers, T How about: import sqlalchemy as sa key_cols = [c for c in table.primary_key.columns] query = sa.select(key_cols) print query.execute().fetchall() Or print connection.execute(query).fetchall() Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
