[sqlalchemy] Cannot delete persisted object which is not in current session
I'm using SQLAlchemy 0.4.6 with Python 2.5.2, and having a problem
with deleting object. Below is the testing code that shows my problem:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///C:/test_db.sqlite')
Session = sessionmaker(bind=engine, autoflush=True,
transactional=True)
metadata = MetaData()
class Foo(object):
def __init__(self, name):
self.name = name
foo_table = Table('foo', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40))
)
if __name__ == '__main__':
# Create database, map database table to its model
metadata.create_all(engine)
mapper(Foo, foo_table)
# Create a new Foo object, then save it to the database
db_session = Session()
f = Foo('001')
db_session.save(f)
db_session.commit()
# Try deleting Foo object created above with a new, different
session
db_session2 = Session()
f1 = db_session2.get(Foo, f.id)
print 'f =', f
print 'f1 =', f1
print 'f1 is f =', f1 is f
db_session2.delete(f)
I got a traceback below:
f = __main__.Foo object at 0x00E64FD0
f1 = __main__.Foo object at 0x00E6D8D0
f1 is f = False
Traceback (most recent call last):
File test_sqlalchemy.py, line 39, in module
db_session2.delete(f)
File C:\Python25\lib\site-packages\SQLAlchemy-0.4.6-py2.5.egg
\sqlalchemy\orm\
session.py, line 954, in delete
self._delete_impl(instance)
File C:\Python25\lib\site-packages\SQLAlchemy-0.4.6-py2.5.egg
\sqlalchemy\orm\
session.py, line 1123, in _delete_impl
raise exceptions.InvalidRequestError(Instance '%s' is with key %s
already p
ersisted with a different identity %
(mapperutil.instance_str(instance), instan
ce._instance_key))
sqlalchemy.exceptions.InvalidRequestError: Instance '[EMAIL PROTECTED]' is
with key (
class '__main__.Foo', (1,), None) already persisted with a different
identity
It seems that when I get the object back from db with a different
session, new instance of the same db record is created in this
session. There are 2 different objects presenting the same record in
2 different sessions, and SQLAlchemy is not aware of this.
Any help or idea is appreciated!!!
--~--~-~--~~~---~--~~
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: Implementation patterns for initializing the model and obtaining the mappers
On Jun 30, 2008, at 1:27 AM, Andreas Jung wrote:
--On 29. Juni 2008 16:57:16 -0400 Michael Bayer [EMAIL PROTECTED]
wrote:
On Jun 29, 2008, at 12:46 PM, Andreas Jung wrote:
This approach is ugly (because of putting the mapper within the
local
scope into the global scope (in order to make them importable) and
because
of this code within main.py:
import model
model.setup(dsn)
from model import MyMapper, Session
This there any better pattern for implementing this?
you don't need to pass DSN as a parameter. The engine argument to
declarative_base() is optional, and the docs have been revised in
recent months to reflect this.
However the engine is required in order to perform autoloading.
not attached to the metadata, you can say Table('foo', metadata,
autoload=True, autoload_with=engine)
but yes, if you want to load your tables from the DB you need an
engine. Alternatives include pickling the loaded table objects to a
file and loading them in via pickle.loads().
--~--~-~--~~~---~--~~
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: Cannot delete persisted object which is not in current session
On Jun 30, 2008, at 4:37 AM, Tai Tran wrote:
# Create a new Foo object, then save it to the database
db_session = Session()
f = Foo('001')
db_session.save(f)
db_session.commit()
# Try deleting Foo object created above with a new, different
session
db_session2 = Session()
f1 = db_session2.get(Foo, f.id)
print 'f =', f
print 'f1 =', f1
print 'f1 is f =', f1 is f
db_session2.delete(f)
f is still attached to db_session. if you want to delete f1, say
db_session2.delete(f1). Or if f, say db_session.delete(f). if you
want to move objects between sessions, use session.expunge()/
session.save_or_update().
--~--~-~--~~~---~--~~
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] custom type for PostGIS geometry
Hi, Based on some posts I was able to find scouring Google, I was trying to implement a custom type (GeometryType) for the PostGIS GEOMETRY column. Now, I should point out that I am not using sqlalchemy to build the DDL, since the geometry columns are indirectly created by invoking stored procedures (and I couldn't really figure out a clean way to handle that). I am, however, using these Geometry types in my Table instances [mapping to already-created tables]. The problem is that I'd like to have sqlalchemy return KML for all geometry types by default, but I don't know how to setup my type to specify a SQL function that needs to be applied to the column in the select clause. Is this possible? Alternatively, it would be *perfect* to have additional *_kml properties added to the result objects (one for each GEOMETRY column), but this seems even more complex (?). Here is my simple type class: class GeometryType(sa.types.TypeEngine): def __init__(self, SRID, typeName, dimension): super(GeometryType, self).__init__() self.mSrid = SRID self.mType = typeName.upper() self.mDim = dimension def __repr__(self): return %s:%s-%s(%s) % (self.__class__.__name__, self.mType, self.mDim, self.mSrid) def get_col_spec(self): return GEOMETRY def convert_bind_param(self, value, engine): if not value: return None else: return 'GeomFromText(\'%s\',%s)' % (value, self.mSrid) def convert_result_value(self, value, engine): return 'AsKml(%s)' % value I was expecting my convert_result_value method to operate on the SQL, but I am assuming from looking at the generated SQL that this method is actually going to operate on the raw data that was returned from SQL. So, is there a way to accomplish what I want? Thanks in advance! Hans --~--~-~--~~~---~--~~ 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] Following docs but can't get logging to work as expected
I must be missing something simple, but with SQLA 0.4.6 I can't get
the logging to work without using the echo flag. When I do set echo =
True and leave my logging configuration code I get the double logging
problem mentioned in the docs.
My apologies if this is just a logging module issue. The primary bit
of documentation I'm using is this:
http://www.sqlalchemy.org/docs/04/dbengine.html#dbengine_logging
but I've also been digging around the logging.py docs and code without
any luck.
Below is a standalone script I've reduced to that demonstrates the
problem. What do I need to do to make this output the SQL statements
to stdout?
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
import logging
_ORM_BASE = declarative_base()
class ORM_User(_ORM_BASE):
__tablename__ = user
id = sa.Column(id, sa.Integer, primary_key = True)
login = sa.Column(login, sa.String(80), index = True, unique =
True)
password = sa.Column(password, sa.String(80), nullable = False)
DbEngine = sa.create_engine('sqlite:///:memory:', echo = False)
logging.basicConfig(format = '%(message)s')
engineLogger = logging.getLogger('sqlalchemy.engine')
assert isinstance(engineLogger, logging.Logger)
assert engineLogger is not logging.root
engineLogger.setLevel(logging.DEBUG) # to get statements and results
_ORM_BASE.metadata.create_all(DbEngine)
--~--~-~--~~~---~--~~
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: custom type for PostGIS geometry
On Jun 30, 2008, at 1:04 PM, Hans wrote: The problem is that I'd like to have sqlalchemy return KML for all geometry types by default, but I don't know how to setup my type to specify a SQL function that needs to be applied to the column in the select clause. Is this possible? Alternatively, it would be *perfect* to have additional *_kml properties added to the result objects (one for each GEOMETRY column), but this seems even more complex (?). I was expecting my convert_result_value method to operate on the SQL, but I am assuming from looking at the generated SQL that this method is actually going to operate on the raw data that was returned from SQL. So, is there a way to accomplish what I want? TypeEngine currently has no hook that's consulted during SQL rendering, which is where you're looking for this to happen.Its not impossible to add such a feature but its not yet clear to me that TypeEngine is the appropriate place for this decision to be made. Its also not apparent yet how specifically within the SQL it would be appropriate to render the function, and how labeling would work - since SQLA, when it generates nested select statements, uses labels to track each column named against a table outwards...such as select foo, bar from (select a as foo, b as bar from table). In the case of a SQL function, the column name becomes somewhat undefined until a label is applied. SQLA nests SELECT statements very often. You might want to try experimenting with a simple monkeypatch approach just to see what kind of results you get - this is just a guess based on a particular compiler hook we have that specifically processes columns being rendered within a select: from sqlalchemy.databases.postgres import PGCompiler def label_select_column(self, select, column, asfrom): if isinstance(col.type, MyGISType): return func.some_kml_function(col).label(column.name) else: return super(self, PGCompiler).label_select_column(select, column, asfrom) PGCompiler.label_select_column = label_select_column if the above leads to decent results we can attempt to add a hook onto TypeEngine perhaps. --~--~-~--~~~---~--~~ 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: custom type for PostGIS geometry
On Jun 30, 1:24 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 30, 2008, at 1:04 PM, Hans wrote: The problem is that I'd like to have sqlalchemy return KML for all geometry types by default, but I don't know how to setup my type to specify a SQL function that needs to be applied to the column in the select clause. Is this possible? Alternatively, it would be *perfect* to have additional *_kml properties added to the result objects (one for each GEOMETRY column), but this seems even more complex (?). I was expecting my convert_result_value method to operate on the SQL, but I am assuming from looking at the generated SQL that this method is actually going to operate on the raw data that was returned from SQL. So, is there a way to accomplish what I want? TypeEngine currently has no hook that's consulted during SQL rendering, which is where you're looking for this to happen.Its not impossible to add such a feature but its not yet clear to me that TypeEngine is the appropriate place for this decision to be made. Its also not apparent yet how specifically within the SQL it would be appropriate to render the function, and how labeling would work - since SQLA, when it generates nested select statements, uses labels to track each column named against a table outwards...such as select foo, bar from (select a as foo, b as bar from table). In the case of a SQL function, the column name becomes somewhat undefined until a label is applied. SQLA nests SELECT statements very often. You might want to try experimenting with a simple monkeypatch approach just to see what kind of results you get - this is just a guess based on a particular compiler hook we have that specifically processes columns being rendered within a select: from sqlalchemy.databases.postgres import PGCompiler def label_select_column(self, select, column, asfrom): if isinstance(col.type, MyGISType): return func.some_kml_function(col).label(column.name) else: return super(self, PGCompiler).label_select_column(select, column, asfrom) PGCompiler.label_select_column = label_select_column if the above leads to decent results we can attempt to add a hook onto TypeEngine perhaps. Thank you -- I will give that a shot! Hans --~--~-~--~~~---~--~~ 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] Order_by
Hi guys, I'm new to this list but I I'm using SA from a while ago. I'm wondering how to sort a query based on a child table attribute. Table A- Many to One - Table B. I need to make a query in A, then order those records by a property of B. query = session.query(A).order_by( B.something ) B has an B.a relation attribute to A. Any pointers on how to achieve this ? Thanks in advice ! --~--~-~--~~~---~--~~ 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] subtle AttributeError in 0.4.5
Folks- I'm (seemingly) randomly running across this issue in my pylons/SA app. I'm generating a SQL where clause like so: where = sa.and_(cal_table.c.adweekid == table.c.adweekid, ) and every so often I'll get an attribute error in sqlalchemy.util:494 in __getattr__ type 'exceptions.AttributeError': adweekid I'll refresh the page and it will work. I'll open the pylons debug page and type: cal_table.c.adweekid and table.c.adweekid and neither will throw an AttributeError, but will just work. Any ideas?!? thanks, matt --~--~-~--~~~---~--~~ 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: subtle AttributeError in 0.4.5
On Jun 30, 2008, at 4:41 PM, matt harrison wrote: Folks- I'm (seemingly) randomly running across this issue in my pylons/SA app. I'm generating a SQL where clause like so: where = sa.and_(cal_table.c.adweekid == table.c.adweekid, ) and every so often I'll get an attribute error in sqlalchemy.util:494 in __getattr__ type 'exceptions.AttributeError': adweekid I'll refresh the page and it will work. I'll open the pylons debug page and type: cal_table.c.adweekid and table.c.adweekid and neither will throw an AttributeError, but will just work. its possible theres more than one Table def at play - depending on the order of module initialization, you get one Table, or you get the other. --~--~-~--~~~---~--~~ 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: subtle AttributeError in 0.4.5
On Jun 30, 2:46 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 30, 2008, at 4:41 PM, matt harrison wrote: Folks- I'm (seemingly) randomly running across this issue in my pylons/SA app. I'm generating a SQL where clause like so: where = sa.and_(cal_table.c.adweekid == table.c.adweekid, ) and every so often I'll get an attribute error in sqlalchemy.util:494 in __getattr__ type 'exceptions.AttributeError': adweekid I'll refresh the page and it will work. I'll open the pylons debug page and type: cal_table.c.adweekid and table.c.adweekid and neither will throw an AttributeError, but will just work. its possible theres more than one Table def at play - depending on the order of module initialization, you get one Table, or you get the other. Hmmm, any table I reference there should have the adweekid column. --~--~-~--~~~---~--~~ 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: MySQL 5.0 DateTime initialization during Table creation is failing.
I thought this was a scope problem, but it seems to be even bigger.
This is the error I get:
OperationalError: (OperationalError) (1067, Invalid default value for
'date_created') u'\nCREATE TABLE fu_op_requests (\n
\tfu_op_requests_id INTEGER NOT NULL AUTO_INCREMENT, \n\tname
VARCHAR(50), \n\trequested_items VARCHAR(300), \n\tamount_donated
FLOAT(4), \n\tcomments VARCHAR(300), \n\tdate_created DATETIME DEFAULT
now() NOT NULL, \n\tPRIMARY KEY (fu_op_requests_id)\n)\n\n' {}
Wow, how could it be generating a DATETIME DEFAULT now() raw command
from this syntax?
from sqlalchemy.databases import mysql
purchases_table = sqlalchemy.Table('fu_op_purchases',
metadata,
sqlalchemy.Column('fu_op_purchases_id',
sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('name', sqlalchemy.String(50)),
sqlalchemy.Column('purchased_item',
sqlalchemy.VARCHAR(300)),
sqlalchemy.Column('price', sqlalchemy.Float(4)),
sqlalchemy.Column('comments', sqlalchemy.VARCHAR(300)),
sqlalchemy.Column('date_created', mysql.MSTimeStamp,
sqlalchemy.PassiveDefault(sqlalchemy.text(CURRENT_TIMESTAMP)),
nullable=False))
--~--~-~--~~~---~--~~
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: MySQL 5.0 DateTime initialization during Table creation is failing.
running this script:
import sqlalchemy
from sqlalchemy.databases import mysql
engine = sqlalchemy.create_engine('mysql://scott:[EMAIL PROTECTED]/
test', echo=True)
metadata = sqlalchemy.MetaData(engine)
purchases_table = sqlalchemy.Table('fu_op_purchases',metadata,
sqlalchemy.Column('fu_op_purchases_id',sqlalchemy.Integer,
primary_key=True),
sqlalchemy.Column('name', sqlalchemy.String(50)),
sqlalchemy.Column('purchased_item',sqlalchemy.VARCHAR(300)),
sqlalchemy.Column('price', sqlalchemy.Float(4)),
sqlalchemy.Column('comments', sqlalchemy.VARCHAR(300)),
sqlalchemy.Column('date_created',
mysql
.MSTimeStamp
,sqlalchemy
.PassiveDefault(sqlalchemy.text(CURRENT_TIMESTAMP)),nullable=False))
purchases_table.create()
produces this output:
CREATE TABLE fu_op_purchases (
fu_op_purchases_id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
purchased_item VARCHAR(300),
price FLOAT(4),
comments VARCHAR(300),
date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (fu_op_purchases_id)
)
On Jun 30, 2008, at 5:51 PM, Gloria W wrote:
I thought this was a scope problem, but it seems to be even bigger.
This is the error I get:
OperationalError: (OperationalError) (1067, Invalid default value for
'date_created') u'\nCREATE TABLE fu_op_requests (\n
\tfu_op_requests_id INTEGER NOT NULL AUTO_INCREMENT, \n\tname
VARCHAR(50), \n\trequested_items VARCHAR(300), \n\tamount_donated
FLOAT(4), \n\tcomments VARCHAR(300), \n\tdate_created DATETIME DEFAULT
now() NOT NULL, \n\tPRIMARY KEY (fu_op_requests_id)\n)\n\n' {}
Wow, how could it be generating a DATETIME DEFAULT now() raw command
from this syntax?
from sqlalchemy.databases import mysql
purchases_table = sqlalchemy.Table('fu_op_purchases',
metadata,
sqlalchemy.Column('fu_op_purchases_id',
sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('name', sqlalchemy.String(50)),
sqlalchemy.Column('purchased_item',
sqlalchemy.VARCHAR(300)),
sqlalchemy.Column('price', sqlalchemy.Float(4)),
sqlalchemy.Column('comments', sqlalchemy.VARCHAR(300)),
sqlalchemy.Column('date_created', mysql.MSTimeStamp,
sqlalchemy.PassiveDefault(sqlalchemy.text(CURRENT_TIMESTAMP)),
nullable=False))
--~--~-~--~~~---~--~~
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: Order_by
I found the solution using Joins. Thanks anyways. This is it: query = session.query(A).select_from(join(A, B)).order_by(B.some_attr) cheers --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
