[sqlalchemy] Re: Problems with Oracle Express/sqlalchemy
) description = Field(String(255), nullable = True) url = Field(String(255), nullable = True) email_address = Field(String(255), nullable = True) expiration_date = Field(DateTime, nullable = True) password = Field(String(40), nullable = False) disabled = Field(Boolean, nullable = True) #TODO: UNIQUE Index IdUniqueKey setup_all() create_all(checkfirst=False) On May 24, 9:57 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 23, 2008, at 10:02 PM, Brandon Goldfedder wrote: Michael, Yes - that is what I am doing now : ForeignKey(schemaname.tablename.colname) and I have things working. (see example 3). My problem is these steps seem really ugly and a lot more work than it should be to get things working portably and reliably - thus my question on if this is best practice or not. no, what you had there is crazy. send an example case (just the plain tables and their relationships, none of the extra stuff you did) so we can fully see what you'd like to achieve. --~--~-~--~~~---~--~~ 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: Problems with Oracle Express/sqlalchemy
Michael,
Great - that handles the create_all(checkfirst issue)
Any fix for the ix_ table name taking into account Oracle name limits
in schema.py?
Thx,
Brandon
On May 24, 7:37 pm, Michael Bayer [EMAIL PROTECTED] wrote:
thank you , this was an old ticket #709 and is fixed in r4814 of the
0.4 branch and r4813 of trunk. remove the usage of owner, it is
synonymous with schema (as it is in oracle itself
-http://www.dba-oracle.com/t_schema_components_owner_user.htm
) , as well as all the other hacks.
On May 24, 2008, at 6:34 PM, Brandon Goldfedder wrote:
MIchael,
Thanks for your help on this - I agree I want to get beyond a mess
here regardless if it is 'working'
Here are 3 tables that demostrate the issues (with the changes I
needed to do use_alter, modify schema.py, hardcode in schema name to
ForeignKey and tables). I want the model file below to work regardless
of the database target (so the schema cram and Oracle target need to
be separated)
The problem is that there is already a table in another schema called
'app_user' and 'facility' so check tables is finding them...
Also - you will note the ForeignKey generates an invalid ix_* index
for Oracle so I needed to modify schema.py until I get around to
setting index=False and manually creating Index'es
#!/usr/bin/env python
#
# Copyright (c) 2008 Digital Innovation, Inc. All rights reserved.
#
# The information and source code contained herein is the exclusive
property of
# Digital Innovation Inc (dicorp) and may not be disclosed,
examined, or
# reproduced in whole or in part without the explicit written
authorization from dicorp.
#
#
from datetime import datetime
from elixir import Entity, Field, OneToMany, ManyToOne, ManyToMany,
has_field, belongs_to
from elixir import options_defaults, using_options, setup_all,
using_table_options
from elixir import String, Unicode, Integer, DateTime, SmallInteger,
Boolean, Text, Binary, Float
options_defaults['table_options'] = {'schema':'cram','owner':'cram'}
from sqlalchemy import Sequence, CheckConstraint, Index,
ForeignKeyConstraint, ForeignKey, schema
class Facility(Entity):
using_options(tablename='facility')
#using_table_options(schema='cram',owner='cram')
control_number = Field(Integer,
Sequence('facility_control_number_seq'), primary_key = True,
autoincrement = True, nullable = False)
id = Field(String(20), nullable = False)
name = Field(String(50), nullable = False)
description = Field(String(255), nullable = True)
url = Field(String(255), nullable = True)
type_link = Field(Integer, CheckConstraint('type_link = 0'),
nullable = False)
#TODO: UNIQUE Index IdUniqueKey
class PropertyItem(Entity):
using_options(tablename='property_item')
#using_table_options(schema='cram',owner='cram')
control_number = Field(Integer, Sequence('property_item_cn_seq'),
primary_key = True, autoincrement = True, nullable = False)
created_by = Field(Integer,
ForeignKey('cram.app_user.control_number', name =
'property_item_created_by_fk', use_alter = False), nullable = True,
index = True) #Link field for link CreatedBy
created_when = Field(DateTime, nullable = True)
modified_by = Field(Integer,
ForeignKey('cram.app_user.control_number', name =
'property_item_modified_by_fk', use_alter = False), nullable = True,
index = True) #Link field for link ModifiedBy
modified_when = Field(DateTime, nullable = True)
property_profile_link = Field(Integer,
ForeignKey('cram.property_profile.control_number', name =
'property_item_3_fk', use_alter = False), nullable = False, index =
True) #Link field for link PropertyProfileLink
code = Field(String(50), nullable = False)
description = Field(String(50), nullable = True)
sort_order = Field(SmallInteger, CheckConstraint('sort_order =
0'), nullable = True)
disabled = Field(Boolean, nullable = True)
class PropertyProfile(Entity):
using_options(tablename='property_profile')
#using_table_options(schema='cram',owner='cram')
control_number = Field(Integer,
Sequence('property_profile_cn_seq'), primary_key = True, autoincrement
= True, nullable = False)
primary_facility_link = Field(Integer,
ForeignKey('cram.facility.control_number', name =
'property_profile_1_fk', use_alter = False), nullable = True, index =
True) #Link field for link PrimaryFacilityLink
created_by = Field(Integer,
ForeignKey('cram.app_user.control_number', name =
'property_profile_created_by_fk', use_alter = False), nullable = True,
index = True) #Link field for link CreatedBy
created_when = Field(DateTime, nullable = True)
modified_by = Field(Integer,
ForeignKey('cram.app_user.control_number', name =
'property_profile_3_fk', use_alter = False), nullable = True, index =
True) #Link field for link ModifiedBy
modified_when = Field
[sqlalchemy] Problems with Oracle Express/sqlalchemy
All, Perhaps someone can help me here since I am in that 'bad place' where I am retrying things again and getting deeper than I want into it. I am trying to create a database (using elixir model although the problem appears to be in sqlalchemy so asking here) in Oracle Express. The problem I think is that the table names I am using exist in other schemas. Before I set schema and owner I was getting the ORA-00942 error which I think was that it was finding the table names in other schemas and attempting to use them in the foreign key reference. Now, I am passing both schema and owner explictly for the table (using_table_options(schema='cram',owner='cram')) and I now get: File C:\PYTHON25\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg \sqlalchemy\sche ma.py, line 788, in column foreign key % tname) sqlalchemy.exceptions.NoReferencedTableError: Could not find table 'property_ite m' with which to generate a foreign key Debating if I abandon using sqlalchemy to generate the creation DDL and just use it for mapping or if there is a better solution anyone has. Any help/Best Practices/Incantations in getting Oracle to work right appreciated, Thanks, Brandon --~--~-~--~~~---~--~~ 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: Problems with Oracle Express/sqlalchemy
All,
So have a 'working' solution but think it is pretty bad... Someone
MUST have a better solution. Here is what I had to do:
1. Have create_all explictly not test for tables existing (or else it
finds it in wrong schema)
create_all(checkfirst = False)
2. Explictly set the schema and owner
options_defaults['table_options'] = {'schema':'cram','owner':'cram'}
3. Modify every Foreign_Key reference and prefix the schema name
created_by = Field(Integer, ForeignKey('cram.app_user.control_number',
name = 'property_profile_created_by_fk', use_alter = False), nullable
= True, index = True) #Link field for link CreatedBy
4. Turn off use_alter in each Foreign Key ref (see above)
5. *** Modify schema.py default Index creation to truncate at 30
chars uniquely
global _i_count
_i_count+=1
Index('ix_%s%d' % (self._label[:22],_i_count), self,
unique=self.unique)
5b. The above will go away in next pass when I set index=False and
manually add the Indexes
Anyone else have a cleaner incantation?
Thanks,
Brandon
On May 23, 3:10 pm, Brandon Goldfedder [EMAIL PROTECTED] wrote:
All,
Perhaps someone can help me here since I am in that 'bad place' where
I am retrying things again and getting deeper than I want into it.
I am trying to create a database (using elixir model although the
problem appears to be in sqlalchemy so asking here) in Oracle
Express.
The problem I think is that the table names I am using exist in other
schemas.
Before I set schema and owner I was getting the ORA-00942 error which
I think was that it was finding the table names in other schemas and
attempting to use them in the foreign key reference.
Now, I am passing both schema and owner explictly for the table
(using_table_options(schema='cram',owner='cram')) and I now get:
File C:\PYTHON25\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg
\sqlalchemy\sche
ma.py, line 788, in column
foreign key % tname)
sqlalchemy.exceptions.NoReferencedTableError: Could not find table
'property_ite
m' with which to generate a foreign key
Debating if I abandon using sqlalchemy to generate the creation DDL
and just use it for mapping or if there is a better solution anyone
has.
Any help/Best Practices/Incantations in getting Oracle to work right
appreciated,
Thanks,
Brandon
--~--~-~--~~~---~--~~
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: Problems with Oracle Express/sqlalchemy
Michael, Yes - that is what I am doing now : ForeignKey(schemaname.tablename.colname) and I have things working. (see example 3). My problem is these steps seem really ugly and a lot more work than it should be to get things working portably and reliably - thus my question on if this is best practice or not. For example the schemaname I have to change depending on whether I am doing Oracle or other DB (thinking I will build the string up from a variable to minimize the changes) On May 23, 7:55 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 23, 2008, at 3:10 PM, Brandon Goldfedder wrote: All, Perhaps someone can help me here since I am in that 'bad place' where I am retrying things again and getting deeper than I want into it. I am trying to create a database (using elixir model although the problem appears to be in sqlalchemy so asking here) in Oracle Express. The problem I think is that the table names I am using exist in other schemas. Before I set schema and owner I was getting the ORA-00942 error which I think was that it was finding the table names in other schemas and attempting to use them in the foreign key reference. Now, I am passing both schema and owner explictly for the table (using_table_options(schema='cram',owner='cram')) and I now get: dont use owner, its deprecated. schema is all you need. To continue here, you'd have to show us what your Table definitions look like. It seems like you just arent sending the correct identifier to your ForeignKey constructs (for a table with an explicit schema attribute, they should be: ForeignKey(schemaname.tablename.colname) ). --~--~-~--~~~---~--~~ 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: MemoryError in unitofwork.py
Okay - next dump quesion: How do I clear out the objects in a session that I no longer need to reference (in this case all of them)... Will python GC take care of this? --Brandon On Apr 25, 7:15 am, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 24, 2007, at 10:59 AM,BrandonGoldfedder wrote: I'm doing a fair number of session flushes as I update my data in the database. After about 53 of them (or so) I am getting the following exception: File build\bdist.win32\egg\sqlalchemy\orm\session.py, line 294, in flush File build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py, line 181, in flush MemoryError Anyone have any ideas: My code is simply adding new entries and every 1000 entries flushing the objectstore. the session doesnt clear itself out after a flush, so with each successive save() of objects memory will grow. --~--~-~--~~~---~--~~ 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] MemoryError in unitofwork.py
I'm doing a fair number of session flushes as I update my data in the database. After about 53 of them (or so) I am getting the following exception: File build\bdist.win32\egg\sqlalchemy\orm\session.py, line 294, in flush File build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py, line 181, in flush MemoryError Anyone have any ideas: My code is simply adding new entries and every 1000 entries flushing the objectstore. Thanks, Brandon Goldfedder --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
