[sqlalchemy] Re: correlated update across logical databases
Hi Michael
2008/7/17 Michael Bayer [EMAIL PROTECTED]:
On Jul 17, 2008, at 7:12 AM, Ryan Tracey wrote:
Hi
I would like to do a correlated update involving tables located in two
logical databases on the same MySQL server.
The commented out code below would work except that the mysql ends up
looking for the one table in the wrong database. customer is defined
as Table('customer', ps_final_meta, autoload=True) and sdf_customer is
defined as Table('sdf_customer, ps_staging_meta, autoload=True).
How can I tell sqlalchemy to include the database names in the sql it
generates? For the moment I am just using SQL directly in an
execute().
file_ids_str = makeSQLList(tuple(file_ids))
# sqlalchemy correlated update
# TODO: figure out to do correlated updates across databases
#
#s = select([customer.c.MP_Code],
#
and_(customer.c.CustomerAccNo1==sdf_customer.c.CustomerAccNo1,
#
customer.c.WholesalerID==sdf_customer.c.WholesalerID
# )).limit(1)
#rc = sdf_customer.update(
# and_(sdf_customer.c.StatusID.in_([8, 12]),
#sdf_customer.c.FileID.in_(file_ids)
# ),
# values={sdf_customer.c.MP_Code:s}).execute().rowcount
sql =
update
sdf_customer
set
sdf_customer.MP_Code = (
select
fc.MP_Code
from
ps_final.customer fc
where
sdf_customer.CustomerAccNo1=fc.CustomerAccNo1
and
sdf_customer.WholesalerID=fc.WholesalerID)
where
sdf_customer.StatusID in (8, 12)
and
sdf_customer.FileID in %s % (file_ids_str,)
rc = dbengine.execute(sql).rowcount
OK, you have two MetaData objects which makes me think each one has
its own engine pointing to an environment with a particular default
schema. If you'd like one SQL statement to be generated, referencing
tables in both schemas and executeable within a single environment,
all the Table objects need to be defined in terms of one default
schema. Those which are in a different schema should include the
Table keyword argument schema='somename'. You should get the whole
thing working using just one Engine and one MetaData object which
contains all tables.
Ahhh! Works like a charm. It seems I missed the obvious. Thank you
kindly for speedy assistance!
Just a note on something I picked up concerning stored procedures and
MySQL. There's a thread (which I seem unable to locate now) which
ended with the suggestion that to get SPs working with SA and MySQL
one should edit databases/mysql.py and add the CALL keyword to the
regex which handles SELECTs, etc.
SQLAlchemy-0.5.0beta1-py2.5.egg/sqlalchemy/databases/mysql.py
Diff:
224c224
r'\s*(?:SELECT|SHOW|DESCRIBE|XA RECOVER)',
---
r'\s*(?:SELECT|SHOW|DESCRIBE|XA RECOVER|CALL)',
The suggested patch works (for the way that I am using SPs) but has
not been applied to the main SA branch -- I have had to apply the
patch to a number of SA versions which have come out since the patch
was originally suggested. Is this an oversight or is there a reason
why the patch has not been applied. Just don't want to be shooting
myself in the foot with a patch that solves one problem and introduces
another.
Thanks again for the help and much gratitude for the totally awesome
SQLAlchemy. Even without using the orm component it makes working with
databases almost a pleasure ;-)
Cheers,
Ryan
--
Ryan Tracey
Citizen: The World
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Uncharted waters? attributes with 30 chars in FreeTDS, unixODBC, PyODBC and SA 0.5beta2
Hello again everyone, I've been trying to move my app from Win64 to Ubuntu, while keeping a MSSQL database. Most of the deps are OS-managed: SA was stuck in 0.4.2p3 and didn't yet support mssql:///?driver=FreeTDS, and PyODBC had to be installed by hand. Anyone experienced with this? I've bumped into a 30-char limit somewhere with exchange_rates.exchange_rate_id, a column name I can surely make shorter; but I'd like to know how to solve the problem, not workaround it: File '/home/lbruno/Company/scraping/suppliers/XXX/wsgi.py', line 65 in parts_json for part in self.cache.parts(part): File '/home/lbruno/Company/scraping/suppliers/XXX/cache.py', line 36 in parts exchange_rate = self.rates.get_USD_EUR_rate().exchange_rate File '/home/lbruno/Company/scraping/suppliers/xe/cache.py', line 17 in get_USD_EUR_rate model.Rate.exchange_rate_id.desc() File '/usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py', line 1006 in first ret = list(self[0:1]) File '/usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py', line 928 in __getitem__ return list(res) File '/usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py', line 1099 in instances process[0](context, row, rows) File '/usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py', line 1565 in main _instance(row, result) File '/usr/lib/python2.5/site-packages/sqlalchemy/orm/mapper.py', line 1424 in _instance identitykey = identity_key(row) File '/usr/lib/python2.5/site-packages/sqlalchemy/orm/mapper.py', line 1371 in identity_key return (identity_class, tuple([row[column] for column in pk_cols]), entity_name) File '/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py', line 1364 in __getitem__ return self.__parent._get_col(self.__row, key) File '/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py', line 1619 in _get_col type_, processor, index = self._key_cache[key] File '/usr/lib/python2.5/site-packages/sqlalchemy/util.py', line 128 in __missing__ self[key] = val = self.creator(key) File '/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py', line 1516 in lookup_key raise exc.NoSuchColumnError(Could not locate column in row for column '%s' % (str(key))) NoSuchColumnError: Could not locate column in row for column 'exchange_rates.exchange_rate_id' There is a exchange_rates_exchange_rate_i key in the ResultProxy three frames from the bottom. Suggestions? Thanks in advance, -- Luis Bruno --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: correlated update across logical databases
Hi Michael
2008/7/17 Michael Bayer [EMAIL PROTECTED]:
On Jul 17, 2008, at 7:12 AM, Ryan Tracey wrote:
Hi
I would like to do a correlated update involving tables located in two
logical databases on the same MySQL server.
The commented out code below would work except that the mysql ends up
looking for the one table in the wrong database. customer is defined
as Table('customer', ps_final_meta, autoload=True) and sdf_customer is
defined as Table('sdf_customer, ps_staging_meta, autoload=True).
How can I tell sqlalchemy to include the database names in the sql it
generates? For the moment I am just using SQL directly in an
execute().
file_ids_str = makeSQLList(tuple(file_ids))
# sqlalchemy correlated update
# TODO: figure out to do correlated updates across databases
#
#s = select([customer.c.MP_Code],
#
and_(customer.c.CustomerAccNo1==sdf_customer.c.CustomerAccNo1,
#
customer.c.WholesalerID==sdf_customer.c.WholesalerID
# )).limit(1)
#rc = sdf_customer.update(
# and_(sdf_customer.c.StatusID.in_([8, 12]),
#sdf_customer.c.FileID.in_(file_ids)
# ),
# values={sdf_customer.c.MP_Code:s}).execute().rowcount
sql =
update
sdf_customer
set
sdf_customer.MP_Code = (
select
fc.MP_Code
from
ps_final.customer fc
where
sdf_customer.CustomerAccNo1=fc.CustomerAccNo1
and
sdf_customer.WholesalerID=fc.WholesalerID)
where
sdf_customer.StatusID in (8, 12)
and
sdf_customer.FileID in %s % (file_ids_str,)
rc = dbengine.execute(sql).rowcount
OK, you have two MetaData objects which makes me think each one has
its own engine pointing to an environment with a particular default
schema. If you'd like one SQL statement to be generated, referencing
tables in both schemas and executeable within a single environment,
all lthe Table objects need to be defined in terms of one default
schema. Those which are in a different schema should include the
Table keyword argument schema='somename'. You should get the whole
thing working using just one Engine and one MetaData object which
contains all tables.
Urgh. Looks like I spoke too soon. In my previous email I said your
suggestion worked. That was only partially correct. Tables in the
second schema (the one not specified in the engine definition)
autoload fine and the correlated update across the two schemas works.
However, if a table in the second schema has a constraint on another
table in that same schema then autoloading fails. Looking at the (pdb)
traceback in ipython, somewhere schema='schema2' becomes schema=None
where FK constraints are being handled.
I am using SA 0.5.0beta2. But the same problem occurs with 0.4.6.
Here's the standard python traceback:
Traceback (most recent call last):
File /usr/lib/python2.5/runpy.py, line 95, in run_module
filename, loader, alter_sys)
File /usr/lib/python2.5/runpy.py, line 52, in _run_module_code
mod_name, mod_fname, mod_loader)
File /usr/lib/python2.5/runpy.py, line 32, in _run_code
exec code in run_globals
File /var/tmp/dbconnect.py, line 61, in module
autoload=True, schema='ps_final')
File
/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/schema.py,
line 113, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File
/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/schema.py,
line 242, in __init__
_bind_or_error(metadata).reflecttable(self, include_columns=include_columns)
File
/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/engine/base.py,
line 1282, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File
/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/databases/mysql.py,
line 1655, in reflecttable
only=include_columns)
File
/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/databases/mysql.py,
line 2135, in reflect
self._set_constraints(table, constraints, connection, only)
File
/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/databases/mysql.py,
line 2295, in _set_constraints
autoload=True, autoload_with=connection)
File
/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/schema.py,
line 113, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File
/home/ryant/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/schema.py,
line 240, in __init__
autoload_with.reflecttable(self, include_columns=include_columns)
File
[sqlalchemy] Re: Uncharted waters? attributes with 30 chars in FreeTDS, unixODBC, PyODBC and SA 0.5beta2
pymssql has a 30 char identifier limit, but pyodbc should work with identifiers up to 128 chars. I can't tell from your message if you're running pymssql or pyodbc, but that may be the issue. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Declarative 0.5 tutorial fails to create tables before insert
Thanks, this wasn't clear from the tutorial.
Kris
On Jul 18, 6:04 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Jul 17, 2008, at 4:44 PM, Kris Kennaway wrote:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
ed_user = User('ed', 'Ed Jones', 'edspassword')
session.add(ed_user)
session.add_all([
User('wendy', 'Wendy Williams', 'foobar'),
User('mary', 'Mary Contrary', 'xxg527'),
User('fred', 'Fred Flinstone', 'blah')])
session.commit()
you're missing a metadata.create_all() in there. From your
declarative base call Base.metadata.create_all(engine).
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] question
hi, i have a method that returns list of (material, thickness) groups which looks like: C = [order_element_items.c.material_uuid, materials.c.name, order_element_items.c.thickness] S = select( C, order_element_items.c.material_uuid==materials.c.uuid, from_obj=[order_element_items, materials], order_by=[materials.c.name, order_element_items.c.thickness], group_by=C, ) res = [(session.query(Material).filter_by(uuid=a).one(), c) for a,b,c in session.bind.execute(S)] but it does too many selects and i wonder is it possible somehow to achieve the same result with one select? thanks in advance --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Uncharted waters? attributes with 30 chars in FreeTDS, unixODBC, PyODBC and SA 0.5beta2
On Jul 18, 2008, at 9:35 AM, Luis Bruno wrote: Hello again everyone, I've been trying to move my app from Win64 to Ubuntu, while keeping a MSSQL database. Most of the deps are OS-managed: SA was stuck in 0.4.2p3 and didn't yet support mssql:///?driver=FreeTDS, and PyODBC had to be installed by hand. Anyone experienced with this? I've bumped into a 30-char limit somewhere with exchange_rates.exchange_rate_id, a column name I can surely make shorter; but I'd like to know how to solve the problem, not workaround it: see this thread http://groups.google.com/group/sqlalchemy/browse_thread/thread/36fd2e935b165d70/fc1735ee363f9402?hl=enlnk=gstq=unixODBC#fc1735ee363f9402 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: correlated update across logical databases
On Jul 18, 2008, at 5:43 AM, Ryan Tracey wrote: Just a note on something I picked up concerning stored procedures and MySQL. There's a thread (which I seem unable to locate now) which ended with the suggestion that to get SPs working with SA and MySQL one should edit databases/mysql.py and add the CALL keyword to the regex which handles SELECTs, etc. SQLAlchemy-0.5.0beta1-py2.5.egg/sqlalchemy/databases/mysql.py Diff: 224c224 r'\s*(?:SELECT|SHOW|DESCRIBE|XA RECOVER)', --- r'\s*(?:SELECT|SHOW|DESCRIBE|XA RECOVER|CALL)', The suggested patch works (for the way that I am using SPs) but has not been applied to the main SA branch -- I have had to apply the patch to a number of SA versions which have come out since the patch was originally suggested. Is this an oversight or is there a reason why the patch has not been applied. Just don't want to be shooting myself in the foot with a patch that solves one problem and introduces another. this is already in 0.5beta2, trunk, and the 0.4 branch, back when it was suggested. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: question
On Jul 18, 2008, at 10:07 AM, Vladimir Iliev wrote: hi, i have a method that returns list of (material, thickness) groups which looks like: C = [order_element_items.c.material_uuid, materials.c.name, order_element_items.c.thickness] S = select( C, order_element_items.c.material_uuid==materials.c.uuid, from_obj=[order_element_items, materials], order_by=[materials.c.name, order_element_items.c.thickness], group_by=C, ) res = [(session.query(Material).filter_by(uuid=a).one(), c) for a,b,c in session.bind.execute(S)] but it does too many selects and i wonder is it possible somehow to achieve the same result with one select? \ something along the lines of (using 0.5): session.query(Material, S.c.thickness).select_from(materials.join(S, materials.c.uuid==S.c.material_uuid)).all() 0.4 would be along the lines of sess .query (Material).add_column(OrderElementItem.thickness).select_from() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: correlated update across logical databases
On Jul 18, 2008, at 9:37 AM, Ryan Tracey wrote:
sqlalchemy.exc.NoSuchTableError: manufacturer
Just to sum up:
dbengine = create_engine('mysql://u:[EMAIL PROTECTED]/schema1')
meta = MetaData()
meta.bind = dbengine
Table('tableX', meta, autoload=True)
...
Table('tableA', meta, autoload=True, schema='schema2')
Table('tableB', meta, autoload=True, schema='schema2')
tableX is okay, and tableA loads okay too. However tableB has a FK
referencing tableA. Bang! I get a NoSuchTableError for tableA.
I'll try get some ipython output. Or figure out how pdb works.
its likely mysql reflection doesn't yet know how to reflect cross-
schema foreign keys. put up a trac ticket with a short test case.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Sessions + Twisted Perspective Broker
Hello. First off--excellent work. I've been using SQLAlchemy for a year or so now, and I must say it's pretty awesome, but that I'm still learning it. So, on to my question. I'm using Twisted's Perspective Broker to sit in between the database on the server, and wxPython on the client. For those that don't know, PB is used to translucently call remote functions and copy objects back and forth across the network. I already have a method for making SA objects safe to copy across the network--that's not an issue. I don't think you need an understanding of Twisted or PB to help me out with this, but if you need more information, or think I should send this to Twisted's lists, please let me know. When a client connects using PB, the server maintains a connection with it (along with other data) in what's called an Avatar. The Avatar is used to deal with permissions and such, and in the case of my app, it's used to manage preferences. Because I need frequent access to the underlying Person object (from the person table), I store it in the Avatar, as it's always available in my PB functions. Then, when I need to access relations of it, I use object_session(), so that I can query it. This works perfectly--most of the time. Sometimes (as my app grows more complicated), I'll have issues with object_session() returning None. I *think* what the problem is is that I'm loading that Person row elsewhere (by searching for people, for example), and that's pulling it out of the session that it was originally in. I think that I'm using sessions wrong. I know I should be creating a new session for every request from PB-- but then how do I store a persistent copy of the Person object in the Avatar? Should I create a new session each time and use session.merge()? I should probably *not* use contextual sessions, as, if I understand that correctly, it wouldn't only help for a singly-threaded program. I don't need to access the database asynchronously, so I think that makes things simpler. So, any advice? Thanks, Jeff --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Error after upgrade from 0.4.3 to 0.4.6
I upgraded from 0.4.3 to 0.4.6 and now I get the error below when
starting my TurboGears app. I'm using reflection and that fails on the
first table. I had a look at the changelog and tried adding
oracle_resolve_synonyms=True to that table's definition but I got
another error (not shown here).
Any help would be appreciated.
Thanks,
Steve
C:\Documents and Settings\daystev\Desktop\asrt_trunk\srt-projectstart-
srt.py de
v.cfg
SELECT USER FROM DUAL
{}
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
DATA_SCALE, NULLABLE
, DATA_DEFAULT from ALL_TAB_COLUMNS where TABLE_NAME = :table_name and
OWNER = :
owner
{'owner': 'ASRT_PROXY', 'table_name': 'ACC_PROTOCOL'}
Traceback (most recent call last):
File C:\Documents and Settings\daystev\Desktop\asrt_trunk\srt-
project\start-s
rt.py, line 24, in module
from srt.controllers import Root
File C:\Documents and Settings\daystev\Desktop\asrt_trunk\srt-
project\srt\con
trollers.py, line 6, in module
import model as dbmodel
File C:\Documents and Settings\daystev\Desktop\asrt_trunk\srt-
project\srt\mod
el.py, line 27, in module
autoload=True)
File c:\python25\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg
\sqlalchemy\sche
ma.py, line 110, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File c:\python25\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg
\sqlalchemy\sche
ma.py, line 226, in __init__
_bind_or_error(metadata).reflecttable(self,
include_columns=include_columns)
File c:\python25\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg
\sqlalchemy\engi
ne\base.py, line 1275, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File c:\python25\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg
\sqlalchemy\data
bases\oracle.py, line 501, in reflecttable
raise exceptions.AssertionError(Couldn't find any column
information for ta
ble %s % actual_name)
sqlalchemy.exceptions.AssertionError: Couldn't find any column
information for t
able ACC_PROTOCOL
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Error after upgrade from 0.4.3 to 0.4.6
On Jul 18, 2008, at 1:32 PM, shday wrote: I upgraded from 0.4.3 to 0.4.6 and now I get the error below when starting my TurboGears app. I'm using reflection and that fails on the first table. I had a look at the changelog and tried adding oracle_resolve_synonyms=True to that table's definition but I got another error (not shown here). Any help would be appreciated. what's the schema look like ? are synonyms in use ? are there cross- owner names in use ? what's the error you got with oracle_resolve_synonyms ? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Sessions + Twisted Perspective Broker
On Jul 18, 2008, at 11:47 AM, Jeff wrote: I think that I'm using sessions wrong. I know I should be creating a new session for every request from PB-- but then how do I store a persistent copy of the Person object in the Avatar? Should I create a new session each time and use session.merge()? I should probably *not* use contextual sessions, as, if I understand that correctly, it wouldn't only help for a singly-threaded program. I don't need to access the database asynchronously, so I think that makes things simpler. if you have some kind of long running ORM-mapped instances that need to participate in multiple sessions on an ad hoc basis, you use merge() to get them in. It produces a copy of the object local to that session, which wont be messed with by anything else.You can only use contextual sessions if you've designed a contextual function that interacts brilliantly with twisted's concurrency model. In this case, such as, per-Avatar; assuming the Avatar corresponds to exactly one single process of work (i.e. no concurrent access). You definitely do *not* want to use the default thread local behavior since Twisted implements concurrency without threads necessarily being involved. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Overriding table columns with Python-property
I have a table 'test' that defines a column 'col'. I map this table on:
class Mapper(object):
@property
def col(self):
return uSome read-only value.
passing exclude_properties=('col',).
However, when I save and commit an instance of Mapper, I get:
[snip]
UnmappedColumnError: No column test.col is configured on mapper Mapper...
This is on SQLAlchemy 0.4.6.
\malthe
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Overriding table columns with Python-property
works for me:
from sqlalchemy import *
from sqlalchemy.orm import *
e = create_engine('sqlite://')
m = MetaData(e)
t= Table('t1', m,
Column('id', Integer, primary_key=True),
Column('col', String(50)),
Column('data', String(50)),
)
t.create()
class Mapper(object):
@property
def col(self):
return uSome read-only value.
mapper(Mapper, t, exclude_properties=('col',))
sess = sessionmaker()()
x = Mapper()
x.data = some data
sess.save(x)
sess.commit()
sess.clear()
assert sess.query(Mapper).one().data == some data
assert sess.query(Mapper).one().col == uSome read-only value.
x = sess.query(Mapper).one()
x.data = some new data
sess.commit()
assert sess.query(Mapper).one().data == some new data
On Jul 18, 2008, at 2:24 PM, Malthe Borch wrote:
I have a table 'test' that defines a column 'col'. I map this table
on:
class Mapper(object):
@property
def col(self):
return uSome read-only value.
passing exclude_properties=('col',).
However, when I save and commit an instance of Mapper, I get:
[snip]
UnmappedColumnError: No column test.col is configured on mapper
Mapper...
This is on SQLAlchemy 0.4.6.
\malthe
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Sessions + Twisted Perspective Broker
Alright, merge() it is. That's kind of what I was leaning towards, as it seems that it would be the easiest (at this point) to implement. Thanks for weighing in, and thanks again for a great library. -Jeff On Jul 18, 2:12 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 18, 2008, at 11:47 AM, Jeff wrote: I think that I'm using sessions wrong. I know I should be creating a new session for every request from PB-- but then how do I store a persistent copy of the Person object in the Avatar? Should I create a new session each time and use session.merge()? I should probably *not* use contextual sessions, as, if I understand that correctly, it wouldn't only help for a singly-threaded program. I don't need to access the database asynchronously, so I think that makes things simpler. if you have some kind of long running ORM-mapped instances that need to participate in multiple sessions on an ad hoc basis, you use merge() to get them in. It produces a copy of the object local to that session, which wont be messed with by anything else. You can only use contextual sessions if you've designed a contextual function that interacts brilliantly with twisted's concurrency model. In this case, such as, per-Avatar; assuming the Avatar corresponds to exactly one single process of work (i.e. no concurrent access). You definitely do *not* want to use the default thread local behavior since Twisted implements concurrency without threads necessarily being involved. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] [ANN] Fixture Goes 1.0 (tool for testing with data)
I've just pushed 1.0 -- the *I Heart Data* release -- of fixture, a Python module for loading and referencing test data. It is used heavily at my work in two test suites: one for the functional tests of an ETL framework and another for a Pylons + Elixir (SQLAlchemy) + Ext JS web application. http://farmdev.com/projects/fixture/ easy_install -U fixture Highlights of This Release * SQLAlchemy 0.4 now finally works as a nice fixture backend (0.5beta support is shaky, but mostly there). * Even more documentation! The new docs (http://farmdev.com/projects/fixture/) have been completely reorganized thanks to the venerable Sphinx. Of special note is a new tutorial that shows how to use fixture to test a Pylons + SQLAlchemy application: http://farmdev.com/projects/fixture/using-fixture-with-pylons.html Many thanks to those who submitted issues and patches, especially to Manuel Aristarán, sokann, Brian Lee Hawthorne, and Jeffrey Cousens. What's next? The fixture command that generates DataSet classes from a real database needs some attention. It doesn't work with SQLAlchemy 0.5beta yet and could work better with SQLAlchemy based data models in general. Aside from that, fixture seems to have stabilized for my apps at work so I'll be waiting to hear from the community about what other areas to improve on. -Kumar --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
