[sqlalchemy] Re: creating tables and mappers multiple times in the same python script
On Fri, 20 May 2011 00:52:28 -0400, Michael Bayer mike...@zzzcomputing.com
wrote:
On May 19, 2011, at 5:24 PM, Faheem Mitha wrote:
Unfortunately, that is not true. (So I guess just leaving the
structure alone and switching dbs will not work.) There are 4
possible different database layouts. Also, there can be multiple
schemas in each database.
So you have a model with a set of tables, the tables are split
amongst multiple schemas, say schemas A, B, and C. You then have
four types of databases, each of which have the identical set of
table designs, except the actual names of *just the schemas*,
I.e. A, B, and C, randomly change.
Well, let me try to be clear here, for the record. I have a bunch of
schemas across databases. Let us assume that all schemas are in one
database and there are k schemas. Then each of these k schemas contain
a dataset of 9 tables. Now this collection of 9 tables vary in
structure - there are 4 different possible structures/layouts for
these 9 tables (what are confusingly also called schemas), but the
table names used in all the different layouts are the same, say A, B,
C, I. So, a total of 9k tables. Most of the tables are the same,
just a couple of the tables differ. So, my application may switch from
one set of tables, to another set of tables. I hope it is not quite as
ridiculous as it sounds. Unfortunately I haven't had the help of a
database professional in this project.
That seems ridiculous. I would absolutely name the schemas
consistently. Or name the tables distinctly in the schemas so that
search_path could be used. Very unfortunate that PG doesn't support
synonyms.
Well, the code for handling these different layouts is essentially the
same, so I've mostly used the same routines across all, and
encapsulated the differences using object orientation. This would be
more difficult if I started using different names fot the different
tables.
Not sure what you mean by name the schemas consistently.
Since I know you aren't going for that, there's some other Python
tricks all of which are more complex, or interfere with how mapper()
works in such a way that I can't guarantee ongoing compatibility,
than just wiping out everything with clear mappers and re-mapping.
I would keep the MetaData for each set of tables in a dictionary and
pull the appropriate set of tables out for each use, send them into
mapper(). I'd create the three copies of each Table from the
original one using table.tometadata(schema='newschema').
# runs only once
metadatas = {
'one':MetaData(),
'two':MetaData(),
'three':MetaData(),
'four':MetaData(),
}
# runs only once, per table
def table(name, *args, **kw):
t = Table(name, metadatas['one'], *args, **kw)
t.tometadata(metadatas['two'], schema='two')
t.tometadata(metadatas['three'], schema='three')
t.tometadata(metadatas['four'], schema='four')
Wow, never heard of tometadata before. Google thought I might be
searching for metadata. :-)
At http://readthedocs.org/docs/sqlalchemy/rel_0_6_6/core/schema.html I
found this defn of tometadata
*
tometadata(metadata, schema=symbol 'retain_schema)
Return a copy of this Table associated with a different MetaData.
E.g.:
# create two metadata
meta1 = MetaData('sqlite:///querytest.db')
meta2 = MetaData()
# load 'users' from the sqlite engine
users_table = Table('users', meta1, autoload=True)
# create the same Table object for the plain metadata
users_table_2 = users_table.tometadata(meta2)
***
Does tometadata just retrieve the new table by name? Not completely
clear from the context.
Not sure what is happening here. The idea is to get the equivalent of
t from a different metadata, yes? So wouldn't this be like
def table(name, number, *args, **kw):
t = Table(name, metadatas['one'], *args, **kw)
# Return the table corresponding to t from metadata[number]
return t.tometadata(metadatas[number], schema=number)
then to map a class:
mapper(cls, metadatas['two'].tables['some_table'])
This would need to loop over all tables, yes? Here tables is some
other directory? I think I'm missing some context. Off the top of my
head, something like
for cls, name in zip(classnames, tablenames):
mapper(cls, metadatas['two'].table(name, two))
perhaps?
Regards, Faheem
--
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: creating tables and mappers multiple times in the same python script
On May 20, 2011, at 4:48 AM, Faheem Mitha wrote: On Fri, 20 May 2011 00:52:28 -0400, Michael Bayer mike...@zzzcomputing.com wrote: On May 19, 2011, at 5:24 PM, Faheem Mitha wrote: Unfortunately, that is not true. (So I guess just leaving the structure alone and switching dbs will not work.) There are 4 possible different database layouts. Also, there can be multiple schemas in each database. So you have a model with a set of tables, the tables are split amongst multiple schemas, say schemas A, B, and C. You then have four types of databases, each of which have the identical set of table designs, except the actual names of *just the schemas*, I.e. A, B, and C, randomly change. Well, let me try to be clear here, for the record. I have a bunch of schemas across databases. Let us assume that all schemas are in one database and there are k schemas. Then each of these k schemas contain a dataset of 9 tables. Now this collection of 9 tables vary in structure - there are 4 different possible structures/layouts for these 9 tables (what are confusingly also called schemas), but the table names used in all the different layouts are the same, say A, B, C, I. So, a total of 9k tables. Most of the tables are the same, just a couple of the tables differ. So, my application may switch from one set of tables, to another set of tables. I hope it is not quite as ridiculous as it sounds. Unfortunately I haven't had the help of a database professional in this project. OK so not just schema names, table structure as well. So yeah so you're using the same classes among entirely different databases essentially, so yeah just like our tests do, just tear down before remapping for a new DB and you're done. -- 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] Conflicting state in identity map?
I have a Turbogears server that uses sqlalchemy to interface with a postgres database. Today, I noticed the server was down, so I tried restarting it. Now my turbogears log is full of errors like: AssertionError: A conflicting state is already present in the identity map for key (class 'dr8db.ModelClasses.FITSHeaderKeyword', (1045,)) and Exception KeyError: KeyError((class 'dr8db.ModelClasses.MaskbitsType', (61,)),) in bound method InstanceState._cleanup of sqlalchemy.orm.state.InstanceState object at 0x4445c90 ignored I tried googling this stuff, but found nothing... Basically it lets me start the paster (Turbogears) server, but after ~5-10 minutes the server dies and there are hundreds of these errors in the log -- help!! I need to get this server back up ASAP! Thanks, Adrian -- 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] Conflicting state in identity map?
On May 20, 2011, at 9:45 AM, Adrian wrote: I have a Turbogears server that uses sqlalchemy to interface with a postgres database. Today, I noticed the server was down, so I tried restarting it. Now my turbogears log is full of errors like: AssertionError: A conflicting state is already present in the identity map for key (class 'dr8db.ModelClasses.FITSHeaderKeyword', (1045,)) and Exception KeyError: KeyError((class 'dr8db.ModelClasses.MaskbitsType', (61,)),) in bound method InstanceState._cleanup of sqlalchemy.orm.state.InstanceState object at 0x4445c90 ignored I tried googling this stuff, but found nothing... Basically it lets me start the paster (Turbogears) server, but after ~5-10 minutes the server dies and there are hundreds of these errors in the log -- help!! I need to get this server back up ASAP! That's an assertion that is generally unreachable from within the Session. The only ways I think you could get there would be via direct manipulation of session.identity_map, or if the Session is being shared among concurrent threads, which is not supported. The main thing you'd be looking for here is, at what point did this server begin to fail and what event precluded that happening ?Either a code update, or perhaps the app was never tested against its current load, are the two possibilities. -- 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] ORA-01461 on insert/update to String column
I have a table in Oracle (10gXE) that has a VARCHAR2 (4000) column. Whenever I try to specify a value for the column that has more 2000 characters using a python Unicode variable, I get ORA-01461: can bind a LONG value only for insert into a LONG column. When I explicitly encode this into a utf-8 str, the error does not occur. The string contains only ASCII characters and fewer than 4000 of them. I have convert_unicode=True in my create_engine() call, but removing it doesn't change anything that I can see. I'm using cx_Oracle 5.1 and SQLAlchemy 0.6.7. The Oracle database characterset is AL32UTF8. Is this expected behavior? Rodney -- 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: Conflicting state in identity map?
So if it is the latter, that the Session is being shared amongst threads, what is the correct way to handle the sessions from within Turbogears? What I do now is create a scoped_session in the model, and import it into each controller. For a while, I made use of special functions _before() and _after() to create the session in _before the page loads, and close it in _after, but then I was getting DetatchedInstanceErrors when I tried to access columns from objects returned to the template. I'm not sure how familiar you are with Turbogears, so I apologize if this is too much of a TG question...but I asked this on their mailing list and their answer was that what I was doing was correct -- obviously it's not if I'm getting these AssertionErrors... Thanks for the quick reply, as usual! On May 20, 9:59 am, Michael Bayer mike...@zzzcomputing.com wrote: On May 20, 2011, at 9:45 AM, Adrian wrote: I have a Turbogears server that uses sqlalchemy to interface with a postgres database. Today, I noticed the server was down, so I tried restarting it. Now my turbogears log is full of errors like: AssertionError: A conflicting state is already present in the identity map for key (class 'dr8db.ModelClasses.FITSHeaderKeyword', (1045,)) and Exception KeyError: KeyError((class 'dr8db.ModelClasses.MaskbitsType', (61,)),) in bound method InstanceState._cleanup of sqlalchemy.orm.state.InstanceState object at 0x4445c90 ignored I tried googling this stuff, but found nothing... Basically it lets me start the paster (Turbogears) server, but after ~5-10 minutes the server dies and there are hundreds of these errors in the log -- help!! I need to get this server back up ASAP! That's an assertion that is generally unreachable from within the Session. The only ways I think you could get there would be via direct manipulation of session.identity_map, or if the Session is being shared among concurrent threads, which is not supported. The main thing you'd be looking for here is, at what point did this server begin to fail and what event precluded that happening ? Either a code update, or perhaps the app was never tested against its current load, are the two possibilities. -- 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] ORA-01461 on insert/update to String column
On the most recent versions of cx Oracle you need to have the NLS_LANG environment variable set for proper unicode handling. They temporarily made this step optional in the 5.0 series but they backtracked on that in 5.1. If that doesn't fix the issue then check on the cx_oracle list, as you should be able to reproduce the behavior with a cx_oracle only script. On May 20, 2011, at 3:51 PM, Rodney Barnett wrote: I have a table in Oracle (10gXE) that has a VARCHAR2 (4000) column. Whenever I try to specify a value for the column that has more 2000 characters using a python Unicode variable, I get ORA-01461: can bind a LONG value only for insert into a LONG column. When I explicitly encode this into a utf-8 str, the error does not occur. The string contains only ASCII characters and fewer than 4000 of them. I have convert_unicode=True in my create_engine() call, but removing it doesn't change anything that I can see. I'm using cx_Oracle 5.1 and SQLAlchemy 0.6.7. The Oracle database characterset is AL32UTF8. Is this expected behavior? Rodney -- 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: Conflicting state in identity map?
On May 20, 2011, at 4:12 PM, Adrian wrote: So if it is the latter, that the Session is being shared amongst threads, what is the correct way to handle the sessions from within Turbogears? What I do now is create a scoped_session in the model, and import it into each controller. For a while, I made use of special functions _before() and _after() to create the session in _before the page loads, and close it in _after, but then I was getting DetatchedInstanceErrors when I tried to access columns from objects returned to the template. I'm not sure how familiar you are with Turbogears, so I apologize if this is too much of a TG question...but I asked this on their mailing list and their answer was that what I was doing was correct -- obviously it's not if I'm getting these AssertionErrors... Thanks for the quick reply, as usual! So scoped_session() will ensure that sessions are associated with threads. Its not a total guarantee of thread safety if for example you're placing objects in some kind of in-memory cache, then using them in other threads without detaching them first from their original session. You really need to figure out what the catalyst for the issue was - short of locating the actual cause of the bug, that would produce the most clues towards what it is. Or, this is a really harsh approach that I had to do once when a deeply nested call to a defunct Google service started crashing the site, I had to disable all pages on the site, then slowly turn one page on after the next to isolate which one was the cause of the issue. Clearly that isn't an option in lots of cases but it depends on if you can reproduce the issue locally, perhaps when load testing with Apache ab and such. If its something I had seen before that would help but I've never seen anyone hitting that assertion before. On May 20, 9:59 am, Michael Bayer mike...@zzzcomputing.com wrote: On May 20, 2011, at 9:45 AM, Adrian wrote: I have a Turbogears server that uses sqlalchemy to interface with a postgres database. Today, I noticed the server was down, so I tried restarting it. Now my turbogears log is full of errors like: AssertionError: A conflicting state is already present in the identity map for key (class 'dr8db.ModelClasses.FITSHeaderKeyword', (1045,)) and Exception KeyError: KeyError((class 'dr8db.ModelClasses.MaskbitsType', (61,)),) in bound method InstanceState._cleanup of sqlalchemy.orm.state.InstanceState object at 0x4445c90 ignored I tried googling this stuff, but found nothing... Basically it lets me start the paster (Turbogears) server, but after ~5-10 minutes the server dies and there are hundreds of these errors in the log -- help!! I need to get this server back up ASAP! That's an assertion that is generally unreachable from within the Session. The only ways I think you could get there would be via direct manipulation of session.identity_map, or if the Session is being shared among concurrent threads, which is not supported. The main thing you'd be looking for here is, at what point did this server begin to fail and what event precluded that happening ?Either a code update, or perhaps the app was never tested against its current load, are the two possibilities. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] SQLAlchemy 0.7.0 Released
The SQLAlchemy project is pleased to announce version 0.7.0 of SQLAlchemy, the first production release within the 0.7.0 series. 0.7 represents the past year's worth of development, streamlining APIs, adding new features, solidifying the core and improving performance. Key highlights of version 0.7 include: - New event system applies a consistent and flexible approach to the task of extending SQLAlchemy, both within the core and the ORM. The previous system of ad-hoc extension and listener classes is replaced by a single function event.listen() which can apply listeners to a wide variety of hooks. - A new extension allows easy creation of hybrid attributes, attributes that provide Python expression behavior at the instance level and SQL expression behavior at the declarative (class) level. - A new system of building so-called mutable attributes, column-mapped values which can change their value in-place. Detection of changes now uses change events and solves the previous issue of full unit-of-work scans for such attributes. - Major speed improvements, including batching of INSERT statements when possible, greatly reduced codepaths for many key operations. - C extensions, battle tested after a year of use in 0.6, now build by default on install for CPython platforms. - Pypy is fully supported. - Dialect support for Psycopg2 on Python 3, Drizzle, pymysql added. - Documentation and example updates, including a modernized, declarative version of polymorphic associations. - Many, many core and ORM behavioral improvements. See the almost-full list at 07Migration. Thanks to everyone who's contributed via code, bug reports, infrastructure support, production testing. SQLAlchemy 0.7.0 links: Download: http://www.sqlalchemy.org/download.html Whats New + Migration: http://www.sqlalchemy.org/trac/wiki/07Migration Changelog: http://www.sqlalchemy.org/changelog/CHANGES_0_7_0 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy 0.7.0 Released
May the force be with SQLAlchemy. :) 2011/5/20 Michael Bayer mike...@zzzcomputing.com The SQLAlchemy project is pleased to announce version 0.7.0 of SQLAlchemy, the first production release within the 0.7.0 series. 0.7 represents the past year's worth of development, streamlining APIs, adding new features, solidifying the core and improving performance. Key highlights of version 0.7 include: - New event system applies a consistent and flexible approach to the task of extending SQLAlchemy, both within the core and the ORM. The previous system of ad-hoc extension and listener classes is replaced by a single function event.listen() which can apply listeners to a wide variety of hooks. - A new extension allows easy creation of hybrid attributes, attributes that provide Python expression behavior at the instance level and SQL expression behavior at the declarative (class) level. - A new system of building so-called mutable attributes, column-mapped values which can change their value in-place. Detection of changes now uses change events and solves the previous issue of full unit-of-work scans for such attributes. - Major speed improvements, including batching of INSERT statements when possible, greatly reduced codepaths for many key operations. - C extensions, battle tested after a year of use in 0.6, now build by default on install for CPython platforms. - Pypy is fully supported. - Dialect support for Psycopg2 on Python 3, Drizzle, pymysql added. - Documentation and example updates, including a modernized, declarative version of polymorphic associations. - Many, many core and ORM behavioral improvements. See the almost-full list at 07Migration. Thanks to everyone who's contributed via code, bug reports, infrastructure support, production testing. SQLAlchemy 0.7.0 links: Download: http://www.sqlalchemy.org/download.html Whats New + Migration: http://www.sqlalchemy.org/trac/wiki/07Migration Changelog: http://www.sqlalchemy.org/changelog/CHANGES_0_7_0 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Conflicting state in identity map?
Ok, I'll definitely do some quality debugging... Just to be clear -- I **don't** have to worry about closing my sessions in each controller? On May 20, 6:08 pm, Michael Bayer mike...@zzzcomputing.com wrote: On May 20, 2011, at 4:12 PM, Adrian wrote: So if it is the latter, that the Session is being shared amongst threads, what is the correct way to handle the sessions from within Turbogears? What I do now is create a scoped_session in the model, and import it into each controller. For a while, I made use of special functions _before() and _after() to create the session in _before the page loads, and close it in _after, but then I was getting DetatchedInstanceErrors when I tried to access columns from objects returned to the template. I'm not sure how familiar you are with Turbogears, so I apologize if this is too much of a TG question...but I asked this on their mailing list and their answer was that what I was doing was correct -- obviously it's not if I'm getting these AssertionErrors... Thanks for the quick reply, as usual! So scoped_session() will ensure that sessions are associated with threads. Its not a total guarantee of thread safety if for example you're placing objects in some kind of in-memory cache, then using them in other threads without detaching them first from their original session. You really need to figure out what the catalyst for the issue was - short of locating the actual cause of the bug, that would produce the most clues towards what it is. Or, this is a really harsh approach that I had to do once when a deeply nested call to a defunct Google service started crashing the site, I had to disable all pages on the site, then slowly turn one page on after the next to isolate which one was the cause of the issue. Clearly that isn't an option in lots of cases but it depends on if you can reproduce the issue locally, perhaps when load testing with Apache ab and such. If its something I had seen before that would help but I've never seen anyone hitting that assertion before. On May 20, 9:59 am, Michael Bayer mike...@zzzcomputing.com wrote: On May 20, 2011, at 9:45 AM, Adrian wrote: I have a Turbogears server that uses sqlalchemy to interface with a postgres database. Today, I noticed the server was down, so I tried restarting it. Now my turbogears log is full of errors like: AssertionError: A conflicting state is already present in the identity map for key (class 'dr8db.ModelClasses.FITSHeaderKeyword', (1045,)) and Exception KeyError: KeyError((class 'dr8db.ModelClasses.MaskbitsType', (61,)),) in bound method InstanceState._cleanup of sqlalchemy.orm.state.InstanceState object at 0x4445c90 ignored I tried googling this stuff, but found nothing... Basically it lets me start the paster (Turbogears) server, but after ~5-10 minutes the server dies and there are hundreds of these errors in the log -- help!! I need to get this server back up ASAP! That's an assertion that is generally unreachable from within the Session. The only ways I think you could get there would be via direct manipulation of session.identity_map, or if the Session is being shared among concurrent threads, which is not supported. The main thing you'd be looking for here is, at what point did this server begin to fail and what event precluded that happening ? Either a code update, or perhaps the app was never tested against its current load, are the two possibilities. -- 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.
