Re: [sqlalchemy] Re: internationalization of content
Hi Nil, On 14/09/2010 21:58, NiL wrote: Hi Werner, many thanks for your rich reply. I'm going to try an elixir implementation for now. If you want follow the thread of the same title in the elixir mailing list. Thanks for letting me know. One of the things which disturb me about SA or Elixir approach is that other means to access the database will now have access to the translations, e.g. Report Writer accessing the db directly, tools like Excel using the db via ODBC etc etc. I am surprised that db's at this point in time don't have some support for something like this. Werner -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Declarative classproperty member problem in 0.6.4, not 0.6.3
The following test fails in 0.6.4 but not 0.6.3 with AttributeError: type object 'Person' has no attribute 'foo'. Is this a deliberate change? It seems a bit weird that every @classproperty on a declarative subclass is accessed/run on import. from sqlalchemy import create_engine, Column, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.util import classproperty Base = declarative_base() class Person(Base): __tablename__ = 'people' name = Column(String, primary_key=True) @classproperty def bar(cls): return cls.foo -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Declarative classproperty member problem in 0.6.4, not 0.6.3
On Sep 15, 2010, at 10:04 AM, Nikolaj wrote: The following test fails in 0.6.4 but not 0.6.3 with AttributeError: type object 'Person' has no attribute 'foo'. Is this a deliberate change? It seems a bit weird that every @classproperty on a declarative subclass is accessed/run on import. from sqlalchemy import create_engine, Column, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.util import classproperty Base = declarative_base() class Person(Base): __tablename__ = 'people' name = Column(String, primary_key=True) @classproperty def bar(cls): return cls.foo The backrground for this is deliberate, but the effect you are seeing was not originally intended. Person.foo is being evaluated when Person is first created by the declarative base, to see if it returns a mapper property. If .foo isn't available there's your error. The declarative evaluation is limited to the @classproperty decorator that's inside of sqlalchemy.util. If you use your own external @classproperty it won't be called upon. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Declarative classproperty member problem in 0.6.4, not 0.6.3
On 15/09/2010 15:04, Nikolaj wrote: Base = declarative_base() class Person(Base): __tablename__ = 'people' name = Column(String, primary_key=True) @classproperty def bar(cls): return cls.foo Can you explain why you'd want to do something like this? Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Declarative classproperty member problem in 0.6.4, not 0.6.3
On Sep 15, 2010, at 12:10 PM, Chris Withers wrote: On 15/09/2010 15:04, Nikolaj wrote: Base = declarative_base() class Person(Base): __tablename__ = 'people' name = Column(String, primary_key=True) @classproperty def bar(cls): return cls.foo Can you explain why you'd want to do something like this? What I should have done, is had declarative look for a decorator @mapperproperty. Which is identical to @classproperty, except its the specific decorator that declarative will actually look at. I have already hit this glitch in my own code where I am using @classproperty for other reasons. Class-level decorators are going to be more prominent in 0.7 so I will be getting this story straight. Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Fetching last insert id from MySQL.
I've got database with auto increment column called `id` and INSERT query, whom I need to execute without model declaration in project. meta.Session.execute() returns ResultProxy, but last_inserted_ids() doesn't work with execute() and SELECT LAST_INSERT_ID() statement sometimes return 0. Is there any other way to fetch last inserted id ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Fetching last insert id from MySQL.
SELECT LAST_INSERT_ID() is ultimately where the value comes from - the raw .lastrowid is present on the ResultProxy for those DBAPIs which support it, so try using that. Perhaps you're getting 0 because the transaction is going away, in which case .lastrowid should solve that issue. On Sep 15, 2010, at 12:45 PM, phasma wrote: I've got database with auto increment column called `id` and INSERT query, whom I need to execute without model declaration in project. meta.Session.execute() returns ResultProxy, but last_inserted_ids() doesn't work with execute() and SELECT LAST_INSERT_ID() statement sometimes return 0. Is there any other way to fetch last inserted id ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: on padded character fields again
You mean something like this: import sqlalchemy.types as types class CHAR(types.TypeDecorator): '''Strips padding from CHAR types. ''' impl = types.CHAR def process_bind_param(self, value, dialect): return value def process_result_value(self, value, dialect): return value.rstrip() On Sep 14, 11:49 am, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 14, 2010, at 11:42 AM, Victor Olex wrote: We have discussed one aspect of this before and it was hugely helpful (http://groups.google.com/group/sqlalchemy/browse_thread/thread/ 965287c91b790b68/361e0a53d4100b5d?lnk=gstq=padding#361e0a53d4100b5d) This time I wanted to ask not about the WHERE clause but mapped object contents, where field is of padded type such as CHAR. Currently SQLAlchemy populates such fields consistently with what a raw SQL query would return for the database engine. In Oracle it would be with padding. I would like to suggest however that this behavior be parametrized. The reason being that the same code operating on objects retrieved from a mapped database may behave differently depending on the underlying engine. For example a field defined as follows: description = Column(u'desciption', CHAR(length=100), nullable=False) would return padded values when run on Oracle but on SQLite it would be trimmed to the string length. This behavior led to having to duplicate a lot of unit tests (SQLite) into functional test (Oracle) to avoid unpleasant surprises such as: myobj.description == some vaule behaving differently in each environment. One of the most important features of the ORM's is abstracting away the physical database store. Unless I missed something obvious this could be a room for improvement. By the way the mapping was reverse engineered from existing database. In forward engineering scenario one would probably use a generic type String instead, which would map to VARCHAR where the issue is non- existent. Well the first thing I'd note is that the CHAR type is not part of the ORM, its the part of schema definition language. The schema definition and SQL expression languages attempt to strike a balance between backend-agnosticism and literal DBAPI/database behavior. The other thing is I'd ask is have you looked at TypeDecorator (http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorato...), is that adequate here or otherwise why not ? A real world ORM application generally has a whole module dedicated to custom types that are tailored to the application's needs. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Trying to walk through tutorial, getting (OperationalError) no such table
This is driving me crazy since I can't even get through the tutorial :
( Thanks for any help! code, then error below.
#! /usr/local/bin/python
dbtestpy
Walking through sqlalchemy tutorial
Author: Michelle Brenner
from sqlalchemy import Table, Column, Integer, Float, Sequence,
String, MetaData, ForeignKey, create_engine, or_, func
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy.orm.exc import NoResultFound
engine = create_engine('sqlite:///dbtest.db', echo=False)
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()
#creating db
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(bind=engine)
metadata = Base.metadata
metadata.create_all(engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
def __repr__(self):
return User('%s','%s', '%s') % (self.name, self.fullname,
self.password)
users_table = User.__table__
#adding data
ed_user = User('ed', 'Ed Jones', 'edspassword')
#querying data
session.add(ed_user)
session.commit()
#our_user = session.query(User).filter_by(name='ed').first()
#our_user
Traceback (most recent call last):
File dbtest.py, line 48, in ?
session.commit()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 673, in commit
self.transaction.commit()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 378, in commit
self._prepare_impl()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 362, in _prepare_impl
self.session.flush()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 1354, in flush
self._flush(objects)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 1432, in _flush
flush_context.execute()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
unitofwork.py, line 257, in execute
UOWExecutor().execute(self, tasks)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
unitofwork.py, line 720, in execute
self.execute_save_steps(trans, task)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
unitofwork.py, line 735, in execute_save_steps
self.save_objects(trans, task)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
unitofwork.py, line 726, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
mapper.py, line 1387, in _save_obj
c = connection.execute(statement.values(value_params), params)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 874, in _execute_clauseelement
return self.__execute_context(context)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) no such table:
users u'INSERT INTO users (name, fullname, password) VALUES
(?, ?, ?)' ['ed', 'Ed Jones', 'edspassword']
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Combining aliases with labels
On Sep 9, 9:53 am, Conor conor.edward.da...@gmail.com wrote:
On 09/08/2010 01:05 PM, Jack Kordas wrote:
When I try to use both aliases and labels, the results are not named
as expected.
Instead of being able to access the columns as label-name_column-
name it appears as original-table-name_numeric-sequence_column-
name
Thanks,
Jack
Sample code follows:
parent = Table('parent', metadata,
Column('id', INTEGER(), primary_key=True),
Column('name', VARCHAR(length=128)),
Column('first_id', INTEGER(),
ForeignKey(u'child.id')),
)
child = Table('child', metadata,
Column('id', INTEGER(), primary_key=True),
Column('name', VARCHAR(length=128))
)
def test_labels1(conn):
s = select([parent,child], use_labels=True)
s = s.where(parent.c.first_id==child.c.id)
return conn.execute(s).fetchone()
def test_alias1(conn):
firstchild = child.alias()
s = select([parent,firstchild], use_labels=True)
s = s.where(parent.c.first_id==firstchild.c.id)
return conn.execute(s).fetchone()
conn = engine.connect()
results = test_labels1(conn)
print results.parent_name
print results.child_name
results = test_alias1(conn)
print 'alias1 results: '
print results.parent_name
#print results.firstchild_name # expected this to work
print results.child_1_name # this worked instead
You need to set an explicit name for the alias to prevent SQLAlchemy
from generating an anonymous name[1]:
firstchild = child.alias(firstchild)
-Conor
[1]http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sq...
Thanks, that did the trick. I didn't appreciate the difference
between using the aliased variable in the from clause and generating
names for the selected columns.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Trying to walk through tutorial, getting (OperationalError) no such table
Try moving the metadata.create_all(engine) below the User class declaration.
Like:
snipped code
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
def __repr__(self):
return User('%s','%s', '%s') % (self.name, self.fullname,
self.password)
metadata.create_all(engine)
snipped code
After User class is evaluated, the metadata object will have knowledge
about the users table.
Cheers
On Wed, Sep 15, 2010 at 16:52, MichelleB mbrenne...@gmail.com wrote:
This is driving me crazy since I can't even get through the tutorial :
( Thanks for any help! code, then error below.
#! /usr/local/bin/python
dbtestpy
Walking through sqlalchemy tutorial
Author: Michelle Brenner
from sqlalchemy import Table, Column, Integer, Float, Sequence,
String, MetaData, ForeignKey, create_engine, or_, func
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy.orm.exc import NoResultFound
engine = create_engine('sqlite:///dbtest.db', echo=False)
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()
#creating db
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(bind=engine)
metadata = Base.metadata
metadata.create_all(engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
def __repr__(self):
return User('%s','%s', '%s') % (self.name, self.fullname,
self.password)
users_table = User.__table__
#adding data
ed_user = User('ed', 'Ed Jones', 'edspassword')
#querying data
session.add(ed_user)
session.commit()
#our_user = session.query(User).filter_by(name='ed').first()
#our_user
Traceback (most recent call last):
File dbtest.py, line 48, in ?
session.commit()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 673, in commit
self.transaction.commit()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 378, in commit
self._prepare_impl()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 362, in _prepare_impl
self.session.flush()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 1354, in flush
self._flush(objects)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
session.py, line 1432, in _flush
flush_context.execute()
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
unitofwork.py, line 257, in execute
UOWExecutor().execute(self, tasks)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
unitofwork.py, line 720, in execute
self.execute_save_steps(trans, task)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
unitofwork.py, line 735, in execute_save_steps
self.save_objects(trans, task)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
unitofwork.py, line 726, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/orm/
mapper.py, line 1387, in _save_obj
c = connection.execute(statement.values(value_params), params)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 874, in _execute_clauseelement
return self.__execute_context(context)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File /net/vol240/shots/spi/home/python/common/sqlalchemy/engine/
base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) no such table:
users u'INSERT INTO users (name, fullname, password) VALUES
(?, ?, ?)' ['ed', 'Ed Jones', 'edspassword']
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
[sqlalchemy] Re: Fetching last insert id from MySQL.
Lastrowid return: Could not locate column in row for column 'lastrowid'. I try to use transaction: trans = meta.Session.begin() try: meta.Session.execute(INSERT statement) result = meta.Session.execute(SELECT LAST_INSERT_ID()) trans.commit() except: trans.rollback() raise Now, I'm testing this, think it helps to stop loosing session between INSERT and SELECT. On 15 сен, 21:45, Michael Bayer mike...@zzzcomputing.com wrote: SELECT LAST_INSERT_ID() is ultimately where the value comes from - the raw .lastrowid is present on the ResultProxy for those DBAPIs which support it, so try using that. Perhaps you're getting 0 because the transaction is going away, in which case .lastrowid should solve that issue. On Sep 15, 2010, at 12:45 PM, phasma wrote: I've got database with auto increment column called `id` and INSERT query, whom I need to execute without model declaration in project. meta.Session.execute() returns ResultProxy, but last_inserted_ids() doesn't work with execute() and SELECT LAST_INSERT_ID() statement sometimes return 0. Is there any other way to fetch last inserted id ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] PostgreSQL, cascading and non-nullable ForeignKeys
Hi,
I'm using SqlAlchemy 0.6.3 and PostgreSQL 8.4 and I'm trying to setup
a cascading delete between several levels of tables. The problem seems
to be that I can't have a relationship with cascade=all and a column
with ForeignKey that has nullable=False.
Here is my example:
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship
engine = create_engine('postgresql://tsq:passt%qw...@localhost:5432/
ce_cascade_test', echo=True)
Session = scoped_session(sessionmaker())
Base = declarative_base()
Session.configure(bind=engine)
s = Session()
class User(Base):
__tablename__ = users
id = Column(Integer, primary_key=True)
name = Column(String)
device = relationship(Device, uselist=False)
@classmethod
def create(cls, user_name, device_name, manufacturer):
new_user = User()
new_user.name = user_name
new_user.device = Device.create(device_name)
return new_user
class Device(Base):
__tablename__ = devices
id = Column(Integer, primary_key=True)
name = Column(String)
user_id = Column(Integer,
ForeignKey('users.id'),
nullable=False)
manufacturer_id = Column(Integer,
ForeignKey('manufacturers.id'),
nullable=False)
user = relationship(User,
uselist=False,
cascade=all)
@classmethod
def create(cls, name):
new_device = Device()
new_device.name = name
return new_device
Base.metadata.create_all(engine)
user = User.create(bob, iphone)
s.add(user)
s.commit()
s.delete(user)
s.commit()
If I run this then I get an Integrity error during the delete (because
it is updating the user_id to null before deleting it). I would like
to keep the nullable=False on the ForeignKey.
I've tried adding ondelete=CASCADE to the ForeignKey and adding
passive_deletes=True to the relationship but it always throw the same
Integrity error.
I know I'm missing something but I can't find it in the docs, what am
I missing?
Thanks for your help,
BEN
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] PostgreSQL, cascading and non-nullable ForeignKeys
On 09/15/2010 05:04 PM, BenH wrote:
Hi,
I'm using SqlAlchemy 0.6.3 and PostgreSQL 8.4 and I'm trying to setup
a cascading delete between several levels of tables. The problem seems
to be that I can't have a relationship with cascade=all and a column
with ForeignKey that has nullable=False.
Your cascade clause is on the wrong side of the relationship. It means
you want to cascade TO the remote object, not FROM the remote object.
Here is my example:
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship
engine = create_engine('postgresql://tsq:passt%qw...@localhost:5432/
ce_cascade_test', echo=True)
Session = scoped_session(sessionmaker())
Base = declarative_base()
Session.configure(bind=engine)
s = Session()
class User(Base):
__tablename__ = users
id = Column(Integer, primary_key=True)
name = Column(String)
device = relationship(Device, uselist=False)
This should be:
device = relationship(Device, cascade=all, passive_deletes=True,
uselist=False)
Also, including uselist=False indicates that the User-Device
relationship is one-to-one. The lack of a unique constraint on
devices.user_id suggests a one-to-many relationship.
@classmethod
def create(cls, user_name, device_name, manufacturer):
new_user = User()
new_user.name = user_name
new_user.device = Device.create(device_name)
return new_user
class Device(Base):
__tablename__ = devices
id = Column(Integer, primary_key=True)
name = Column(String)
user_id = Column(Integer,
ForeignKey('users.id'),
nullable=False)
manufacturer_id = Column(Integer,
ForeignKey('manufacturers.id'),
nullable=False)
user = relationship(User,
uselist=False,
cascade=all)
This should be:
user = relationship(User)
Adding uselist=False here is redundant, since that is the default for
the side which contains the foreign key. Also, by including
'cascade=all', you are telling SQLAlchemy to implicitly delete the
user when the device is deleted. This is probably not what you want.
@classmethod
def create(cls, name):
new_device = Device()
new_device.name = name
return new_device
Base.metadata.create_all(engine)
user = User.create(bob, iphone)
s.add(user)
s.commit()
s.delete(user)
s.commit()
If I run this then I get an Integrity error during the delete (because
it is updating the user_id to null before deleting it). I would like
to keep the nullable=False on the ForeignKey.
I've tried adding ondelete=CASCADE to the ForeignKey and adding
passive_deletes=True to the relationship but it always throw the same
Integrity error.
I know I'm missing something but I can't find it in the docs, what am
I missing?
Thanks for your help,
BEN
-Conor
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Fetching last insert id from MySQL.
no its not a column on a row, its on the ResultProxy:
result = session.execute('...')
id = result.lastrowid
http://www.sqlalchemy.org/docs/core/connections.html?highlight=resultproxy#sqlalchemy.engine.base.ResultProxy.lastrowid
On Sep 15, 2010, at 5:51 PM, phasma wrote:
Lastrowid return: Could not locate column in row for column
'lastrowid'. I try to use transaction:
trans = meta.Session.begin()
try:
meta.Session.execute(INSERT statement)
result = meta.Session.execute(SELECT LAST_INSERT_ID())
trans.commit()
except:
trans.rollback()
raise
Now, I'm testing this, think it helps to stop loosing session between
INSERT and SELECT.
On 15 сен, 21:45, Michael Bayer mike...@zzzcomputing.com wrote:
SELECT LAST_INSERT_ID() is ultimately where the value comes from - the raw
.lastrowid is present on the ResultProxy for those DBAPIs which support it,
so try using that. Perhaps you're getting 0 because the transaction is
going away, in which case .lastrowid should solve that issue.
On Sep 15, 2010, at 12:45 PM, phasma wrote:
I've got database with auto increment column called `id` and INSERT
query, whom I need to execute without model declaration in project.
meta.Session.execute() returns ResultProxy, but last_inserted_ids()
doesn't work with execute() and SELECT LAST_INSERT_ID() statement
sometimes return 0. Is there any other way to fetch last inserted id ?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
