[sqlalchemy] Postgres:TEXT and Oracle:CLOB

[jo]

Hi all,

I have this definition of a table.


   session = Table('session', database.metadata,
Column('id', Unicode(40), primary_key=True, nullable=False),
Column('data', Text),
Column('expiration_time', TIMESTAMP(timezone=False)),
)

In the PostgreSQL DB, it creates a table like this:

 name   |type | default | not_null
-+-+-+--
id  | character varying(40)   | | t
data| text| | f
expiration_time | timestamp without time zone | | f

In the Oracle DB, like this:

name| data_type| nullable | data_default | data_length
--- +  +  +  + ---
ID  | NVARCHAR2| N| NULL | 80
DATA| CLOB | Y| NULL | 4000
EXPIRATION_TIME | TIMESTAMP(6) | Y| NULL | 11

When I use it with PostgreSQL all is OK but
when I try to use it with Oracle, pickle raises this error:

...data *=* pickle*.*loads*(*pickled_data*)*|
*TypeError: ('loads() argument 1 must be string, not cx_Oracle.LOB', 
bound method Root.index of sicer.BASE.controller.Root object at 
0x8231f10)*



What can I do to avoid this error?
thank you,

j

--
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] Need Urgent Help -Many to Many Relations

[Abdul Gaffar]

Hi all,


Can anybody explain how to relate three tables with a single association
table.

When a delete a row from master table the entries from the child table
should be deleted.



The tables are.

1).


User.
-
User_id
User_name
role


Group
-
group_id
group_name



Project
---
project_id
project_name



and i want a association table

user_id
group_id
project_id




And please tell how to insert records into this table using Turbogears2
aswell

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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: Need Urgent Help -Many to Many Relations

[NiL]

http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html?highlight=association%20proxy#building-complex-views

http://stackoverflow.com/questions/2310153/inserting-data-in-many-to-many-relationship-in-sqlalchemy/2310548#2310548

-- 
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: Need Urgent Help -Many to Many Relations

[Abdul Gaffar]

Thanks NiL,


I reffered the links what u hav given,

There are two tables stocks, brokers and an association table
holdings. 

Here I need three tables User, Group, Project and a association table
for three tables user_group_project kind of thing



Thank again for your reply :)




On Thu, 2011-02-17 at 03:26 -0800, NiL wrote:
 http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html?highlight=association%20proxy#building-complex-views
 
 http://stackoverflow.com/questions/2310153/inserting-data-in-many-to-many-relationship-in-sqlalchemy/2310548#2310548
 

-- 
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: Need Urgent Help -Many to Many Relations

[NiL]

your use case is unclear, maybe you could be more specific on what you
want 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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Need Urgent Help - SQLAlchemy Relation

[Hector Blanco]

Just a wild guess, but have you tried making your association table like:

#association table
user_group_table = Table('t_user_group', metadata,
   Column('user_id', Integer, ForeignKey('t_user.c.user_id',
       onupdate=CASCADE, ondelete=CASCADE)),
   Column('group_id', Integer, ForeignKey('t_group.c.group_id',
       onupdate=CASCADE, ondelete=CASCADE)),
  Column('project_id', Integer, ForeignKey('t_project.c.project_id',
       onupdate=CASCADE, ondelete=CASCADE))
)

My understanding is that .c. means column so it might need to be
t_user.c.user_id (from table t_user, get the column user_id)

I don't have too much hope with that, but you never know...

2011/2/16 Abdul Gaffar gaffar.infoval...@gmail.com:


 --
 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.



 -- Mensaje reenviado --
 From: Abdul Gaffar gaffar.infoval...@gmail.com
 To: turboge...@googlegroups.com
 Date: Wed, 16 Feb 2011 16:28:47 +0530
 Subject: Need Urgent Help - SQLAlchemy Relation
 Hi all,

 I need urgent help on SQLAlchemy relations. I have three classes User,
 Group, Project and association user_group_table.



 class User(DeclarativeBase):
    __tablename__ = 't_user'
    user_id = Column(Integer, autoincrement=True, primary_key=True)
    user_name = Column(Unicode(32), unique=True, nullable=False)
    email_address = Column(Unicode(320), unique=True, nullable=False,
                           info={'rum': {'field':'Email'}})

    def __repr__(self):
        return ('User: user_name=%r, email=%r' % (
                self.user_name,
                self.email_address)).encode('utf-8')

    def __unicode__(self):
        return self.user_name



 class Group(DeclarativeBase):
    __tablename__ = 't_group'
    group_id = Column(Integer, autoincrement=True, primary_key=True)
    group_name = Column(Unicode(16), unique=True)

    users=relation('User', secondary=user_group_table,backref='groups')

    def __repr__(self):
        return ('Group: name=%s' % self.group_name).encode('utf-8')

    def __unicode__(self):
        return self.group_name


 class Project(DeclarativeBase):
    __tablename__ = 't_project'

    project_id = Column(Integer, autoincrement=True, primary_key=True)
    project_name = Column(Unicode(80), unique=True, nullable=False)

    project=relation('Group', secondary=auth.user_group_table,
 backref='Project')

    def __repr__(self):
        return Project('%s') % self.project_name


 #association table
 user_group_table = Table('t_user_group', metadata,
    Column('user_id', Integer, ForeignKey('t_user.user_id',
        onupdate=CASCADE, ondelete=CASCADE)),
    Column('group_id', Integer, ForeignKey('t_group.group_id',
        onupdate=CASCADE, ondelete=CASCADE)),
   Column('project_id', Integer, ForeignKey('t_project.project_id',
        onupdate=CASCADE, ondelete=CASCADE))
 )


 I am unable to insert the records into association table
 below is the code snippet for insertion

 user = DBSession.query(User).filter(User.user_name == kw['PM']).one()
 group = DBSession.query(Group).filter(Group.group_name == 'pm').one()
 project = DBSession.query(Project).\            filter(Project.project_id
 == kw['project_id']).one()


                group.users.append(user)
                project.project.append(group)
                DBSession.flush()
                transaction.commit()


 Please help me ASAP.


 Thanx 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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: Need Urgent Help -Many to Many Relations

[nil]

http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html#building-complex-views


 Can anybody explain how to relate three tables with a single association
 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.

[sqlalchemy] [Postgres] possibly a bug?

[Hayato ARAKI]

Hi

With sqlalchemy 0.7b1 and postgresql
if you define a BigInteger type for a primarykey, the SQL generated for
CREATE TABLE is a 'serial' and not a 'bigserial'.
(I am generating the sql using pylons)

I just started using sqlalchemy and not sure if this is a bug, but thought I
should notice you.

thanks

-- 
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] [Postgres] possibly a bug?

[Michael Bayer]

yes that is a bug, a regression from 0.6 where you'll get BIGSERIAL.  ticket # 
2065 is added thanks for the report !


On Feb 17, 2011, at 6:35 AM, Hayato ARAKI wrote:

 Hi
 
 With sqlalchemy 0.7b1 and postgresql
 if you define a BigInteger type for a primarykey, the SQL generated for 
 CREATE TABLE is a 'serial' and not a 'bigserial'.
 (I am generating the sql using pylons)
 
 I just started using sqlalchemy and not sure if this is a bug, but thought I 
 should notice you.
 
 thanks
 
 
 -- 
 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] Postgres:TEXT and Oracle:CLOB

[Michael Bayer]

SQLAlchemy ResultProxy is set up by the cx_oracle dialect to add the 
sqlalchemy.dialect.oracle.CLOB type into any result set with a CLOB which 
intercepts cx_oracle's LOB and converts to a stream.   If you are using a 
SQLAlchemy engine and not the cx_oracle cursor directly you should not be 
getting the LOB back.   

from sqlalchemy import *

e = create_engine('oracle://scott:tiger@localhost/xe', echo=True)

m = MetaData()
t = Table('x', m, Column('id', Integer, primary_key=True), Column('data', Text))

m.drop_all(e)
m.create_all(e)

e.execute(t.insert().values(id=1, data='adjfnadkjfdanfkjdanjkdn'))

for row in e.execute(t.select()):
print row['data']


# works with plain SQL too, SQLA uses cursor.description for this particular 
type of conversion:

for row in e.execute(SELECT data FROM x):
print row['data']



CREATE TABLE x (
id INTEGER NOT NULL, 
data CLOB, 
PRIMARY KEY (id)
)
INSERT INTO x (id, data) VALUES (:id, :data)
{'data': 'adjfnadkjfdanfkjdanjkdn', 'id': 1}

SELECT x.id, x.data 
FROM x
adjfnadkjfdanfkjdanjkdn

SELECT data FROM x
{}
adjfnadkjfdanfkjdanjkdn


On Feb 17, 2011, at 4:45 AM, jo wrote:

 Hi all,
 
 I have this definition of a table. 
 
 
 session = Table('session', database.metadata,
  Column('id', Unicode(40), primary_key=True, nullable=False),
  Column('data', Text),
  Column('expiration_time', TIMESTAMP(timezone=False)),
  )
 
 In the PostgreSQL DB, it creates a table like this:
 
   name   |type | default | not_null
 -+-+-+--
  id  | character varying(40)   | | t
  data| text| | f
  expiration_time | timestamp without time zone | | f
 
 In the Oracle DB, like this:
 
 name| data_type| nullable | data_default | data_length
 --- +  +  +  + ---
 ID  | NVARCHAR2| N| NULL | 80
 DATA| CLOB | Y| NULL | 4000
 EXPIRATION_TIME | TIMESTAMP(6) | Y| NULL | 11
 
 When I use it with PostgreSQL all is OK but
 when I try to use it with Oracle, pickle raises this error:
 
 ...data *=* pickle*.*loads*(*pickled_data*)*| 
 *TypeError: ('loads() argument 1 must be string, not cx_Oracle.LOB', bound 
 method Root.index of sicer.BASE.controller.Root object at 0x8231f10)* 
 
 
 What can I do to avoid this error? 
 thank you,
 
 j 
 
 
 -- 
 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] Premature autoflushing leads to IntegrityError with AssociationProxy and a backref

[Michael Bayer]

the stack trace tells all for autoflush situations.  Note this is an 0.7 
stacktrace, 0.6 is slightly different but the same series of steps:

  File test.py, line 107, in module
group = Group([item1, item2])
  File string, line 4, in __init__
  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py, line 105, 
in initialize_instance
return manager.original_init(*mixed[1:], **kwargs)

 File test.py, line 68, in __init__
 1.  self.items_by_owner[item.owner] = item

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/associationproxy.py, 
line 600, in __setitem__
self.col[key] = self._create(key, value)

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/associationproxy.py, 
line 585, in _create
 2. return self.creator(key, value)

  File test.py, line 62, in create_for_proxy
--- 3. return cls(None, owner, item)

 File string, line 4, in __init__
  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py, line 105, 
in initialize_instance
return manager.original_init(*mixed[1:], **kwargs)

  File test.py, line 57, in __init__
--- 4, 5.self.item = item

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
149, in __set__
instance_dict(instance), value, None)
  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
642, in set
value = self.fire_replace_event(state, dict_, value, old, initiator)
  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
662, in fire_replace_event
value = fn(state, value, previous, initiator or self)

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
932, in set_
--- 6.passive=PASSIVE_NO_FETCH)

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
436, in append
self.set(state, dict_, value, initiator, passive=passive)

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
638, in set
 7.old = self.get(state, dict_, passive=PASSIVE_ONLY_PERSISTENT)

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
414, in get
value = self.callable_(state, passive)

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py, line 
542, in _load_for_state
--- 8. result = q.all()

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py, line 1636, 
in all
return list(self)
  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py, line 1745, 
in __iter__
self.session._autoflush()

  File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py, line 901, 
in _autoflush
--- 9.self.flush()

1. items_by_owner assignment
2. association proxy receives assignment, calls creator
3. creator is GroupOwner.create_for_proxy
4. GroupOwner constructor
5. assign self.item =item
6. group_owners backref must assign
7. group_owners backref is uselist=False, old value must be placed in the 
deleted collection since new value replaces.  deleted collection is so 
session knows to assign NULL to the item's foreign key.
8. Item.group_owners is not present in __dict__.  Old value must be loaded.   
There's no old value in this case, but SQLA doesn't know that until it loads
9. autoflush


Solution 1:

initialize group_owners to None:

class Item(object):
   def __init__(self, name, owner):
   self.name = name
   self.owner = owner
   self.group_owners = None


solution 2:

disable autoflush in the GroupOwner constructor.  See 
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush .



On Feb 17, 2011, at 5:56 AM, Julien Demoor wrote:

 Hi,
 
 I'm running into a problem illustrated by the code below. The result
 is an IntegrityError:
 
 sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column
 group_id violates not-null constraint
 'INSERT INTO groups_owners (owner_id, item_id) VALUES (%(owner_id)s, %
 (item_id)s) RETURNING groups_owners.group_id' {'item_id': 1,
 'owner_id': 1}
 
 Looking at the stack trace, autoflush is triggerred by the assignments
 in GroupOwner.__init__(), but I fail to see why or what to do about
 it.
 
 The error appeared when I set a backref with uselist=False on the
 GroupOwner.item relationship. I can work around the problem by using a
 list instead, although that would make less sense since there's at
 most one group per item. Is there an error in my relationship
 configuration?
 
 I'm running SA 0.6.6.
 
  Code to reproduce the error 
 
 from sqlalchemy.ext.associationproxy import association_proxy
 from sqlalchemy.orm.collections import attribute_mapped_collection
 from sqlalchemy import Table, Column, Integer, Numeric, String,
 ForeignKey, MetaData
 from sqlalchemy.engine import create_engine
 from sqlalchemy.orm import sessionmaker, mapper, relationship, backref
 
 meta = MetaData()
 engine = create_engine('postgresql://test:test@localhost/test')
 Session = sessionmaker(bind=engine)
 session = Session()
 
 owners = Table(owners, meta,
 

[sqlalchemy] Re: Premature autoflushing leads to IntegrityError with AssociationProxy and a backref

[Julien Demoor]

Great, thanks a lot!

On Feb 17, 7:05 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 the stack trace tells all for autoflush situations.  Note this is an 0.7 
 stacktrace, 0.6 is slightly different but the same series of steps:

   File test.py, line 107, in module
     group = Group([item1, item2])
   File string, line 4, in __init__
   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py, line 105, 
 in initialize_instance
     return manager.original_init(*mixed[1:], **kwargs)

  File test.py, line 68, in __init__
  1.      self.items_by_owner[item.owner] = item

   File 
 /Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/associationproxy.py, line 
 600, in __setitem__
     self.col[key] = self._create(key, value)

   File 
 /Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/associationproxy.py, line 
 585, in _create
  2.     return self.creator(key, value)

   File test.py, line 62, in create_for_proxy
 --- 3.     return cls(None, owner, item)

  File string, line 4, in __init__
   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py, line 105, 
 in initialize_instance
     return manager.original_init(*mixed[1:], **kwargs)

   File test.py, line 57, in __init__
 --- 4, 5.    self.item = item

   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
 149, in __set__
     instance_dict(instance), value, None)
   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
 642, in set
     value = self.fire_replace_event(state, dict_, value, old, initiator)
   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
 662, in fire_replace_event
     value = fn(state, value, previous, initiator or self)

   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
 932, in set_
 --- 6.    passive=PASSIVE_NO_FETCH)

   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
 436, in append
     self.set(state, dict_, value, initiator, passive=passive)

   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
 638, in set
  7.    old = self.get(state, dict_, passive=PASSIVE_ONLY_PERSISTENT)

   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line 
 414, in get
     value = self.callable_(state, passive)

   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py, line 
 542, in _load_for_state
 --- 8.     result = q.all()

   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py, line 
 1636, in all
     return list(self)
   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py, line 
 1745, in __iter__
     self.session._autoflush()

   File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py, line 
 901, in _autoflush
 --- 9.    self.flush()

 1. items_by_owner assignment
 2. association proxy receives assignment, calls creator
 3. creator is GroupOwner.create_for_proxy
 4. GroupOwner constructor
 5. assign self.item =item
 6. group_owners backref must assign
 7. group_owners backref is uselist=False, old value must be placed in the 
 deleted collection since new value replaces.  deleted collection is so 
 session knows to assign NULL to the item's foreign key.
 8. Item.group_owners is not present in __dict__.  Old value must be loaded.   
 There's no old value in this case, but SQLA doesn't know that until it loads
 9. autoflush

 Solution 1:

 initialize group_owners to None:

 class Item(object):
    def __init__(self, name, owner):
        self.name = name
        self.owner = owner
        self.group_owners = None

 solution 2:

 disable autoflush in the GroupOwner constructor.  
 Seehttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush.

 On Feb 17, 2011, at 5:56 AM, Julien Demoor wrote:

  Hi,

  I'm running into a problem illustrated by the code below. The result
  is an IntegrityError:

  sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column
  group_id violates not-null constraint
  'INSERT INTO groups_owners (owner_id, item_id) VALUES (%(owner_id)s, %
  (item_id)s) RETURNING groups_owners.group_id' {'item_id': 1,
  'owner_id': 1}

  Looking at the stack trace, autoflush is triggerred by the assignments
  in GroupOwner.__init__(), but I fail to see why or what to do about
  it.

  The error appeared when I set a backref with uselist=False on the
  GroupOwner.item relationship. I can work around the problem by using a
  list instead, although that would make less sense since there's at
  most one group per item. Is there an error in my relationship
  configuration?

  I'm running SA 0.6.6.

   Code to reproduce the error 

  from sqlalchemy.ext.associationproxy import association_proxy
  from sqlalchemy.orm.collections import attribute_mapped_collection
  from sqlalchemy import Table, Column, Integer, Numeric, String,
  ForeignKey, MetaData
  from sqlalchemy.engine import create_engine
  from sqlalchemy.orm import sessionmaker, mapper, relationship, backref

  

[sqlalchemy] Find whether a synonym points to a foreign key or a relationship

[Hector Blanco]

Hello everyone!

Let's say I have a class defined like this:

class User(declarativeBase):
Represents a user
__tablename__ = users

_id = Column(id, Integer, primary_key=True)
_phone = Column(phone, String(16))
_userName = Column(user_name, String(50), unique=True, nullable=False)
_password = Column(password, String(64), nullable=False)

_userGroupId = Column(user_group_id, Integer, 
ForeignKey(user_groups.id))
_userGroup = relationship(UserGroup, uselist=False)

def setId(self, id):
Set id
self._id = int(id)

def getId(self):
Get id
return self._id

def setUserGroupById(self, userGroupId):
userGroupId = int(userGroupId)
if userGroupId != self.userGroupId:
self.userGroup = UserGroupManager.getById(userGroupId)

def setUserGroup(self, userGroup):
Set user group
   if isinstance(userGroup, UserGroup):
self._userGroup = userGroup
else:
raise TypeError(Trying to set a  + 
str(type(userGroup)) +  as user group)

def getUserGroup(self):
Get user
return self._userGroup

#More getters/setters

id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
phone = sqlalchemy.orm.synonym('_phone',
descriptor=property(getPhone, setPhone))
userName = sqlalchemy.orm.synonym('_userName',
descriptor=property(getUserName, setUserName))
password = sqlalchemy.orm.synonym('_password',
descriptor=property(getPassword, setPassword))
userGroupId = sqlalchemy.orm.synonym('_userGroupId',
descriptor=property(getUserGroup, setUserGroup))
userGroup = sqlalchemy.orm.synonym('_userGroup',
descriptor=property(getUserGroup, setUserGroup))

I have created an utility that, given an instance gives me the names
of the synonyms in said instance.

def getProperties(instance):
properties = list()
mapper = sqlalchemy.orm.object_mapper(instance)
for prop in mapper.iterate_properties:
if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
properties.append(prop.key)
return properties

That would give me [id, phone, userName, password,
userGroupId, userGroup], so I can more or less generically go
through all said values and execute things like

for attribute in getProperties(instanceOfUser):
 value = getattr(instanceOfUser, attribute)

Is there any way of knowing that said values are ForeignKeys or
relationships? For instance, I'd like to know that the attribute id
is a regular (well... kind of regular... it's a Primary key, but it's
not going to point to anything in another table) numeric attribute,
but userGroupId is a foreign key and userGroup is a Relationship.
I've been sneaking in the vars, __dict__, dir of the values returned
by getattr, but I haven't been able to find anything suitable.

Thank you!

-- 
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] Transactional DDL and SQLite?

[Daniel Holth]

Can someone help me understand why DDL seems to not be transactional here:

import sqlalchemy
e = sqlalchemy.create_engine('sqlite://')
c = e.connect()
t = c.begin()
c.execute(CREATE TABLE foo (bar INTEGER))
t.rollback()

assert u'foo' in e.table_names() # True

But, if I start up `sqlite3 db.db` and type: BEGIN; CREATE TABLE foo (bar 
INTEGER); ROLLBACK;

then no `foo` table is created. I am using SQLite 3.7.2.

I am trying to write migration scripts that create a table and populate it, 
or fail and rollback the entire transaction.

Thanks.

-- 
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] Transactional DDL and SQLite?

[Michael Bayer]

On Feb 17, 2011, at 9:48 PM, Daniel Holth wrote:

 Can someone help me understand why DDL seems to not be transactional here:
 
 import sqlalchemy
 e = sqlalchemy.create_engine('sqlite://')
 c = e.connect()
 t = c.begin()
 c.execute(CREATE TABLE foo (bar INTEGER))
 t.rollback()
 
 assert u'foo' in e.table_names() # True
 
 But, if I start up `sqlite3 db.db` and type: BEGIN; CREATE TABLE foo (bar 
 INTEGER); ROLLBACK;
 
 then no `foo` table is created. I am using SQLite 3.7.2.
 
 I am trying to write migration scripts that create a table and populate it, 
 or fail and rollback the entire transaction.

that's a product of Pysqlite.   DDL isn't transactional with Pysqlite's default 
transaction settings.

 import sqlite3
 c = sqlite3.connect(':memory:')
 curs = c.cursor()
 curs.execute(create table foo (id integer))
sqlite3.Cursor object at 0x2f0160
 c.rollback()
 curs = c.cursor()
 curs.execute(select * from foo)
sqlite3.Cursor object at 0x2f02a0
 print curs.fetchall()
[]



 
 Thanks.
 
 -- 
 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] Find whether a synonym points to a foreign key or a relationship

[Michael Bayer]

On Feb 17, 2011, at 6:37 PM, Hector Blanco wrote:

 Hello everyone!
 
 Let's say I have a class defined like this:
 
 class User(declarativeBase):
   Represents a user
   __tablename__ = users
 
   _id = Column(id, Integer, primary_key=True)
   _phone = Column(phone, String(16))
   _userName = Column(user_name, String(50), unique=True, nullable=False)
   _password = Column(password, String(64), nullable=False)
 
   _userGroupId = Column(user_group_id, Integer, 
 ForeignKey(user_groups.id))
   _userGroup = relationship(UserGroup, uselist=False)
 
   def setId(self, id):
   Set id
   self._id = int(id)
 
   def getId(self):
   Get id
   return self._id
 
   def setUserGroupById(self, userGroupId):
   userGroupId = int(userGroupId)
   if userGroupId != self.userGroupId:
   self.userGroup = UserGroupManager.getById(userGroupId)
 
   def setUserGroup(self, userGroup):
   Set user group
   if isinstance(userGroup, UserGroup):
   self._userGroup = userGroup
   else:
   raise TypeError(Trying to set a  + 
 str(type(userGroup)) +  as user group)
 
   def getUserGroup(self):
   Get user
   return self._userGroup
 
   #More getters/setters
 
   id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
   phone = sqlalchemy.orm.synonym('_phone',
 descriptor=property(getPhone, setPhone))
   userName = sqlalchemy.orm.synonym('_userName',
 descriptor=property(getUserName, setUserName))
   password = sqlalchemy.orm.synonym('_password',
 descriptor=property(getPassword, setPassword))
   userGroupId = sqlalchemy.orm.synonym('_userGroupId',
 descriptor=property(getUserGroup, setUserGroup))
   userGroup = sqlalchemy.orm.synonym('_userGroup',
 descriptor=property(getUserGroup, setUserGroup))
 
 I have created an utility that, given an instance gives me the names
 of the synonyms in said instance.
 
 def getProperties(instance):
   properties = list()
   mapper = sqlalchemy.orm.object_mapper(instance)
   for prop in mapper.iterate_properties:
   if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
   properties.append(prop.key)
   return properties
 
 That would give me [id, phone, userName, password,
 userGroupId, userGroup], so I can more or less generically go
 through all said values and execute things like
 
 for attribute in getProperties(instanceOfUser):
 value = getattr(instanceOfUser, attribute)
 
 Is there any way of knowing that said values are ForeignKeys or
 relationships? For instance, I'd like to know that the attribute id
 is a regular (well... kind of regular... it's a Primary key, but it's
 not going to point to anything in another table) numeric attribute,
 but userGroupId is a foreign key and userGroup is a Relationship.
 I've been sneaking in the vars, __dict__, dir of the values returned
 by getattr, but I haven't been able to find anything suitable.


you just have to poke around and use isinstance() on the MapperProperty 
objects, and/or check for known attributes.The choices are ColumnProperty, 
RelationshipProperty, SynonymProperty, etc.

synonyms are also superceded in 0.7.  they're not very useful compared to 
hybrids.




-- 
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.