[sqlalchemy] before_flush event doesn't seem to permit additional update to dirty objects
Hi,
I have a before flush event set up that sets the current user_id and
datetime on new objects that are going to be commited to the database
(all tables have these fields).
Unfortunately, it seems to ignore changes I make to existing objects, ie
when I set the user_id (which i get from pyramid) and last_updated
columns in the event handler, the sql only issues updates for the other
columns that where modified.
Any ideas as to what am I missing?
Thanks in advance,
Damian
def attach_user_committing(Session, flush_context, instances):
This function gets called by the before_flush event,
it grabs the current threads request and extracts the
authenticated user_id out of it. You can only
commit things if you are authenticated.
Once it has that id, it goes through and adds the id
of the user who modified it to new commits- need to be careful that
_new doesn't change.
#this is used when creating databases testing only
user_id = 1
if not creating_database:
user_id =
authenticated_userid(pyramid.threadlocal.get_current_request())
#for each object being committed/flushed, set the
flushing/commiting user
for obj in Session._new.values():
#log.debug(obj)
obj.user_id = user_id
obj.last_updated = datetime.now()
log.debug('Session dirty is : %s' %Session.dirty)
for obj in Session.dirty:
log.debug('Objects in dirty: %s' % obj.__dict__)
#if obj._sa_instance_state.modified:
obj.user_id = user_id
obj.last_updated = datetime.now()
#session.user_id = request
#this event ensures that user_id lastupdate is correctly stored on
each commit.
event.listen(Session, before_flush, attach_user_committing)
--
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] 'InstanceState' object has no attribute 'manager.mapper', w/ Jython2.7.2
Hello All,
I was curious if anyone else has run into this error:
error message
Traceback (most recent call last):
File util/sa/tests/test_sa_dao.py, line 96, in setUp
self.session.add(tc1)
File
/home/adorsk/projects/gr/jenv2.7/Lib/site-packages/sqlalchemy/orm/session.py,
line 1251, in add
self._save_or_update_state(state)
File
/home/adorsk/projects/gr/jenv2.7/Lib/site-packages/sqlalchemy/orm/session.py,
line 1262, in _save_or_update_state
mapper = _state_mapper(state)
AttributeError: 'InstanceState' object has no attribute 'manager.mapper'
/error message
The context in which this error message is generated is pasted below.
I'm using Jython2.7 on a Postgres, via xzJDBC and the Postgresql JDBC
driver.
If you did encounter this error, did you find a workaround?
-Alex
P.S. SqlAlchemy is absolutely brilliant! Beautiful docs and code,
definitely my new favorite python tool.
code
import unittest
from sqlalchemy import Table, Column, ForeignKey, ForeignKeyConstraint,
Integer, String, Float, MetaData, create_engine
from sqlalchemy.orm import relationship, mapper
from geoalchemy import *
from geoalchemy.postgis import PGComparator
from sqlalchemy.orm import sessionmaker
class My_Test(unittest.TestCase):
def testFoo(self):
print foo
def setUp(self):
self.engine =
create_engine('postgresql+zxjdbc://MY_DB:MY_USER@localhost/MY_PASS)
self.Session = sessionmaker()
connection = self.engine.connect()
# begin a non-ORM transaction
self.trans = connection.begin()
# bind an individual Session to the connection
self.session = self.Session(bind=connection)
schema = {}
self.schema = schema
schema['classes'] = {}
class TestClass1(object):
id = None
children = []
schema['classes']['TestClass1'] = TestClass1
class TestClass2(object):
id = None
name =
schema['classes']['TestClass2'] = TestClass2
schema['primary_class'] = TestClass1
metadata = MetaData()
test1_table = Table('test1', metadata,
Column('id', Integer, primary_key=True)
)
test2_table = Table('test2', metadata,
Column('id', Integer, primary_key=True),
Column('name', String)
)
test1_test2_table = Table('test1_test2', metadata,
Column('test1_id', Integer, primary_key=True),
Column('test2_id', Integer, primary_key=True),
ForeignKeyConstraint(['test1_id'], [test1_table.c.id]),
ForeignKeyConstraint(['test2_id'], [test2_table.c.id])
)
mapper(
TestClass1,
test1_table,
properties = {
'children': relationship(TestClass2,
secondary=test1_test2_table)
}
)
mapper(
TestClass2,
test2_table,
properties = {
}
)
metadata.create_all(self.session.bind)
tc1s = []
tc2s = []
for i in range(5):
tc1 = TestClass1()
tc1s.append(tc1)
self.session.add(tc1)
tc2 = TestClass2()
tc2.name = tc2_%s % i
tc2s.append(tc2)
self.session.add(tc2)
self.session.commit()
for i in range(len(tc1s)):
tc1 = tc1s[i]
child_tc2s = [tc2s[i], tc2s[ (i + 1) % len(tc1s)]]
for c in child_tc2s:
tc2 = self.session.query(TestClass2).filter(TestClass2.id
== c.id).one()
tc1.children.append(tc2)
self.session.commit()
if __name__ == '__main__':
unittest.main()
/code
--
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] 'InstanceState' object has no attribute 'manager.mapper', w/ Jython2.7.2
unfortunately issues like these are often resulting from Jython bugs.For
example, SQLAlchemy was entirely unusable with the previous version of Jython
due to a bug in their __import__ mechanism. That the test works fine using
regular cPYthon with psycopg2 further points to some incompatibility/quirk in
Jython as a potential culprit.
As a test, since I don't have Jython installed, what does this produce for you ?
class Foo(object):
pass
class Bar(object):
pass
f = Foo()
f.bar = Bar()
f.bar.bat = 5
from operator import attrgetter
print attrgetter(bar.bat)(f)
if you get that same error, then this is the Jython bug - attrgetter() as of
2.6 handles dotted paths. SQLAlchemy does have a workaround version for
Python less than 2.6 which we can also enable for Jython, for the interim, but
also this should be reported to Jython as a bug.
On Jun 6, 2012, at 10:37 AM, Alexander Dorsk wrote:
import unittest
from sqlalchemy import Table, Column, ForeignKey, ForeignKeyConstraint,
Integer, String, Float, MetaData, create_engine
from sqlalchemy.orm import relationship, mapper
from geoalchemy import *
from geoalchemy.postgis import PGComparator
from sqlalchemy.orm import sessionmaker
class My_Test(unittest.TestCase):
def testFoo(self):
print foo
def setUp(self):
self.engine =
create_engine('postgresql+zxjdbc://MY_DB:MY_USER@localhost/MY_PASS)
self.Session = sessionmaker()
connection = self.engine.connect()
# begin a non-ORM transaction
self.trans = connection.begin()
# bind an individual Session to the connection
self.session = self.Session(bind=connection)
schema = {}
self.schema = schema
schema['classes'] = {}
class TestClass1(object):
id = None
children = []
schema['classes']['TestClass1'] = TestClass1
class TestClass2(object):
id = None
name =
schema['classes']['TestClass2'] = TestClass2
schema['primary_class'] = TestClass1
metadata = MetaData()
test1_table = Table('test1', metadata,
Column('id', Integer, primary_key=True)
)
test2_table = Table('test2', metadata,
Column('id', Integer, primary_key=True),
Column('name', String)
)
test1_test2_table = Table('test1_test2', metadata,
Column('test1_id', Integer, primary_key=True),
Column('test2_id', Integer, primary_key=True),
ForeignKeyConstraint(['test1_id'], [test1_table.c.id]),
ForeignKeyConstraint(['test2_id'], [test2_table.c.id])
)
mapper(
TestClass1,
test1_table,
properties = {
'children': relationship(TestClass2,
secondary=test1_test2_table)
}
)
mapper(
TestClass2,
test2_table,
properties = {
}
)
metadata.create_all(self.session.bind)
tc1s = []
tc2s = []
for i in range(5):
tc1 = TestClass1()
tc1s.append(tc1)
self.session.add(tc1)
tc2 = TestClass2()
tc2.name = tc2_%s % i
tc2s.append(tc2)
self.session.add(tc2)
self.session.commit()
for i in range(len(tc1s)):
tc1 = tc1s[i]
child_tc2s = [tc2s[i], tc2s[ (i + 1) % len(tc1s)]]
for c in child_tc2s:
tc2 = self.session.query(TestClass2).filter(TestClass2.id ==
c.id).one()
tc1.children.append(tc2)
self.session.commit()
if __name__ == '__main__':
unittest.main()
--
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] before_flush event doesn't seem to permit additional update to dirty objects
The code looks fine to me, other than the access of Session._new for which you
should be really calling upon the public new collection.
What you might want to make sure of is that the objects you expect to see in
dirty are actually there. Sometimes objects don't make it into dirty
until they are found to be impacted by a relationship() changing something on
them. If this is the case you'd want to navigate to those objects differently.
On Jun 6, 2012, at 6:36 AM, Damian Dimmich wrote:
Hi,
I have a before flush event set up that sets the current user_id and datetime
on new objects that are going to be commited to the database (all tables have
these fields).
Unfortunately, it seems to ignore changes I make to existing objects, ie when
I set the user_id (which i get from pyramid) and last_updated columns in the
event handler, the sql only issues updates for the other columns that where
modified.
Any ideas as to what am I missing?
Thanks in advance,
Damian
def attach_user_committing(Session, flush_context, instances):
This function gets called by the before_flush event,
it grabs the current threads request and extracts the
authenticated user_id out of it. You can only
commit things if you are authenticated.
Once it has that id, it goes through and adds the id
of the user who modified it to new commits- need to be careful that
_new doesn't change.
#this is used when creating databases testing only
user_id = 1
if not creating_database:
user_id =
authenticated_userid(pyramid.threadlocal.get_current_request())
#for each object being committed/flushed, set the flushing/commiting user
for obj in Session._new.values():
#log.debug(obj)
obj.user_id = user_id
obj.last_updated = datetime.now()
log.debug('Session dirty is : %s' %Session.dirty)
for obj in Session.dirty:
log.debug('Objects in dirty: %s' % obj.__dict__)
#if obj._sa_instance_state.modified:
obj.user_id = user_id
obj.last_updated = datetime.now()
#session.user_id = request
#this event ensures that user_id lastupdate is correctly stored on each
commit.
event.listen(Session, before_flush, attach_user_committing)
--
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] CircularDependencyError with relationships
you need to use the post_update option described at
http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#rows-that-point-to-themselves-mutually-dependent-rows
.
On Jun 6, 2012, at 1:15 AM, Alex Grönholm wrote:
I have trouble configuring two relationships from one class to another. The
following code should be fairly self-explanatory:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)
default_address_id = Column(Integer, ForeignKey('addresses.id',
use_alter=True, name='defaultaddress_fk'))
addresses = relationship('Address', backref='company',
primaryjoin='Address.company_id == Company.id')
default_address = relationship('Address',
primaryjoin='Company.default_address_id == Address.id')
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
company_id = Column(Integer, ForeignKey(Company.id), nullable=False)
engine = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(engine)
session = Session(engine)
company = Company()
address = Address()
session.add(company)
company.default_address = address
company.addresses.append(address)
session.flush()
What I expect is SQLAlchemy to 1) create the company, 2) create the address
with the new company's id in company_id, 3) assign the ID of the new address
to company.default_address_id
Trouble is, I get this error:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected. Cycles:
set([ProcessState(ManyToOneDP(Company.default_address), Company at
0x16a7210, delete=False), ProcessState(ManyToOneDP(Address.company),
Address at 0x16ad190, delete=False), SaveUpdateState(Company at
0x16a7210), ProcessState(OneToManyDP(Company.addresses), Company at
0x16a7210, delete=False), SaveUpdateState(Address at 0x16ad190)]) all
edges: set([(ProcessState(OneToManyDP(Company.addresses), Company at
0x16a7210, delete=False), SaveUpdateState(Address at 0x16ad190)),
(SaveUpdateState(Address at 0x16ad190),
ProcessState(ManyToOneDP(Company.default_address), Company at 0x16a7210,
delete=False)), (SaveUpdateState(Company at 0x16a7210),
SaveUpdateState(Address at 0x16ad190)), (SaveUpdateState(Company at
0x16a7210), ProcessState(ManyToOneDP(Address.company), Address at
0x16ad190, delete=False)),
(ProcessState(ManyToOneDP(Company.default_address), Company at 0x16a7210,
delete=False), SaveUpdateState(Company at 0x16a7210)),
(ProcessState(ManyToOneDP(Address.company), Address at 0x16ad190,
delete=False), SaveUpdateState(Address at 0x16ad190)),
(SaveUpdateState(Company at 0x16a7210),
ProcessState(OneToManyDP(Company.addresses), Company at 0x16a7210,
delete=False))])
What am I doing wrong? I had a similar problem in my production app when
trying to delete a Company that had a default address assigned.
I'm on SQLAlchemy 0.7.7.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/fqFKTLBdTYwJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: 'InstanceState' object has no attribute 'manager.mapper', w/ Jython2.7.2
Ah, you're right, it does look like a Jython bug. When I run the code you provided above I get the error below, which does show that it's a Jython issue. Traceback (most recent call last): File t.py, line 13, in module print attrgetter(bar.bat)(f) AttributeError: 'Foo' object has no attribute 'bar.bat' I'll let the Jython folks know. Thanks for the fast response, you saved me a lot of head-banging and code spelunking. -Alex -- 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] CircularDependencyError with relationships
06.06.2012 18:06, Michael Bayer kirjoitti:
you need to use the post_update option described at
http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#rows-that-point-to-themselves-mutually-dependent-rows
.
Thanks for the pointer. Problem solved :)
On Jun 6, 2012, at 1:15 AM, Alex Grönholm wrote:
I have trouble configuring two relationships from one class to
another. The following code should be fairly self-explanatory:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)
default_address_id = Column(Integer, ForeignKey('addresses.id',
use_alter=True, name='defaultaddress_fk'))
addresses = relationship('Address', backref='company',
primaryjoin='Address.company_id == Company.id')
default_address = relationship('Address',
primaryjoin='Company.default_address_id == Address.id')
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
company_id = Column(Integer, ForeignKey(Company.id), nullable=False)
engine = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(engine)
session = Session(engine)
company = Company()
address = Address()
session.add(company)
company.default_address = address
company.addresses.append(address)
session.flush()
What I expect is SQLAlchemy to 1) create the company, 2) create the
address with the new company's id in company_id, 3) assign the ID of
the new address to company.default_address_id
Trouble is, I get this error:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected.
Cycles: set([ProcessState(ManyToOneDP(Company.default_address),
Company at 0x16a7210, delete=False),
ProcessState(ManyToOneDP(Address.company), Address at 0x16ad190,
delete=False), SaveUpdateState(Company at 0x16a7210),
ProcessState(OneToManyDP(Company.addresses), Company at 0x16a7210,
delete=False), SaveUpdateState(Address at 0x16ad190)]) all edges:
set([(ProcessState(OneToManyDP(Company.addresses), Company at
0x16a7210, delete=False), SaveUpdateState(Address at 0x16ad190)),
(SaveUpdateState(Address at 0x16ad190),
ProcessState(ManyToOneDP(Company.default_address), Company at
0x16a7210, delete=False)), (SaveUpdateState(Company at 0x16a7210),
SaveUpdateState(Address at 0x16ad190)), (SaveUpdateState(Company
at 0x16a7210), ProcessState(ManyToOneDP(Address.company), Address
at 0x16ad190, delete=False)),
(ProcessState(ManyToOneDP(Company.default_address), Company at
0x16a7210, delete=False), SaveUpdateState(Company at 0x16a7210)),
(ProcessState(ManyToOneDP(Address.company), Address at 0x16ad190,
delete=False), SaveUpdateState(Address at 0x16ad190)),
(SaveUpdateState(Company at 0x16a7210),
ProcessState(OneToManyDP(Company.addresses), Company at 0x16a7210,
delete=False))])
What am I doing wrong? I had a similar problem in my production app
when trying to delete a Company that had a default address assigned.
I'm on SQLAlchemy 0.7.7.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/fqFKTLBdTYwJ.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
mailto: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.
--
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: before_flush event doesn't seem to permit additional update to dirty objects
Hello,
Thanks for the response - how do I access the public new collection?
In terms of the date not being updated - turns out that in my code the
columns are called last_update_date and not last_updated which
explains why it wasn't committing anything
Also, for the Session.dirty objects I check that they've actually been
modified before committing using is_modified -
for obj in Session.dirty:
log.debug('Objects in dirty: %s' % obj.__dict__)
if Session.is_modified(obj, passive=True):
obj.user_id = user_id
obj.last_updated = datetime.now()
Thank you for helping me spot this,
Damian
On Jun 6, 4:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
The code looks fine to me, other than the access of Session._new for which
you should be really calling upon the public new collection.
What you might want to make sure of is that the objects you expect to see in
dirty are actually there. Sometimes objects don't make it into dirty
until they are found to be impacted by a relationship() changing something on
them. If this is the case you'd want to navigate to those objects
differently.
On Jun 6, 2012, at 6:36 AM, Damian Dimmich wrote:
Hi,
I have a before flush event set up that sets the current user_id and
datetime on new objects that are going to be commited to the database (all
tables have these fields).
Unfortunately, it seems to ignore changes I make to existing objects, ie
when I set the user_id (which i get from pyramid) and last_updated columns
in the event handler, the sql only issues updates for the other columns
that where modified.
Any ideas as to what am I missing?
Thanks in advance,
Damian
def attach_user_committing(Session, flush_context, instances):
This function gets called by the before_flush event,
it grabs the current threads request and extracts the
authenticated user_id out of it. You can only
commit things if you are authenticated.
Once it has that id, it goes through and adds the id
of the user who modified it to new commits- need to be careful that
_new doesn't change.
#this is used when creating databases testing only
user_id = 1
if not creating_database:
user_id =
authenticated_userid(pyramid.threadlocal.get_current_request())
#for each object being committed/flushed, set the flushing/commiting user
for obj in Session._new.values():
#log.debug(obj)
obj.user_id = user_id
obj.last_updated = datetime.now()
log.debug('Session dirty is : %s' %Session.dirty)
for obj in Session.dirty:
log.debug('Objects in dirty: %s' % obj.__dict__)
#if obj._sa_instance_state.modified:
obj.user_id = user_id
obj.last_updated = datetime.now()
#session.user_id = request
#this event ensures that user_id lastupdate is correctly stored on each
commit.
event.listen(Session, before_flush, attach_user_committing)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] mapping without key
With the understanding that we would loose the ability to properly track the sate of a mapped object and ability to update or insert in ORM and likely the ability to correctly use relationships as well - how can one accomplish a mapper, which would work on tables (views) without any key, which uniquely identify records in it? The rationale for this question is to be able to be able to operate on these tables in object (ORM) context and to join them in queries with other normally mapped classes for reading purposes only. The ideal solution would be perhaps a different Declarative Base class using a modified mapper. -- 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] Declarative Models: Can they be used with two databases and two schema names?
Hi, I'm trying to use my declarative models to copy data from an Oracle database with a non-default schema name to a SQLite database (which has no schema name, or at least a default name that can't be changed). Copying from Oracle to Oracle has not been a problem for me, but Oracle to SQLite will not work. The problem for me is that the schema definition used for SQL generation is on the table. I went through a fruitless exercise of calling tometadata on every table in the metadata created by the generated declarative base class, copying into a new MetaData object. I then swapped the metadata on the declarative base and ran my query, with the intention of swapping it back after. No luck. The purpose of my project is to surgically extract related data for a small subset of accounts from our production database and bring it down to a local SQLite database. Does anybody have experience doing this? Am I going about this the wrong way? Thanks for any help, Shawn -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Y6z2q5U_B8gJ. 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] mapping without key
There's two variants to this question, and I can't tell which one you're asking for. If the views in question do in fact have candidate keys, that is, columns which uniquely identify a row, you just specify those either to the Table or mapper() as the columns that uniquely identify the row. They don't have to be considered primary by the database in any formal way. If OTOH you have views which truly have duplicate rows and no candidate key of any kind, the ORM won't do that. As you probably know, the primary key thing is more or less the spine of mapper() and Query, and there's really no way the ORM could be refactored, without a great loss of stability and performance, to make this requirement optional. If you're looking for duplicate rows to come back as individual objects, Query() can be handed Table objects to load rows from, so a custom Query subclass that wraps named tuples into objects could possibly approximate this effect. On Jun 6, 2012, at 3:01 PM, Victor Olex wrote: With the understanding that we would loose the ability to properly track the sate of a mapped object and ability to update or insert in ORM and likely the ability to correctly use relationships as well - how can one accomplish a mapper, which would work on tables (views) without any key, which uniquely identify records in it? The rationale for this question is to be able to be able to operate on these tables in object (ORM) context and to join them in queries with other normally mapped classes for reading purposes only. The ideal solution would be perhaps a different Declarative Base class using a modified mapper. -- 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] Declarative Models: Can they be used with two databases and two schema names?
By far the easiest approach is to modify the username you're coming into Oracle as so that the schema in question is the default. Or if you can, create Oracle synonyms (i.e. CREATE SYNONYM) in the default schema that link to the schema-qualified tables. Otherwise SQLA doesn't have a lot of ability to change the schema name. The tometadata() approach you've used is the best it has, however you'd need to declare all new classes against those Table objects. A recipe for doing this is here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName. Another way you might do it is to put an event handler that modifies all the SQL to replace a particular schema name with something else, or nothing, like s/someschema./someotherschema./ type of thing. You can do this with the before_execute or before_cursor_execute events: engine = create_engine(...) @event.listens_for(engine, before_cursor_execute, retval=True) def replace_schema(conn, cursor, statement, parameters, context, executemany): statement = statement.sub(someschema., ) return statement, parameters On Jun 6, 2012, at 3:51 PM, Shawn Wheatley wrote: Hi, I'm trying to use my declarative models to copy data from an Oracle database with a non-default schema name to a SQLite database (which has no schema name, or at least a default name that can't be changed). Copying from Oracle to Oracle has not been a problem for me, but Oracle to SQLite will not work. The problem for me is that the schema definition used for SQL generation is on the table. I went through a fruitless exercise of calling tometadata on every table in the metadata created by the generated declarative base class, copying into a new MetaData object. I then swapped the metadata on the declarative base and ran my query, with the intention of swapping it back after. No luck. The purpose of my project is to surgically extract related data for a small subset of accounts from our production database and bring it down to a local SQLite database. Does anybody have experience doing this? Am I going about this the wrong way? Thanks for any help, Shawn -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Y6z2q5U_B8gJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: before_flush event doesn't seem to permit additional update to dirty objects
On Jun 6, 2012, at 1:48 PM, Damian wrote:
Hello,
Thanks for the response - how do I access the public new collection?
it's called session.new
In terms of the date not being updated - turns out that in my code the
columns are called last_update_date and not last_updated which
explains why it wasn't committing anything
Also, for the Session.dirty objects I check that they've actually been
modified before committing using is_modified -
for obj in Session.dirty:
log.debug('Objects in dirty: %s' % obj.__dict__)
if Session.is_modified(obj, passive=True):
obj.user_id = user_id
obj.last_updated = datetime.now()
Thank you for helping me spot this,
Damian
On Jun 6, 4:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
The code looks fine to me, other than the access of Session._new for which
you should be really calling upon the public new collection.
What you might want to make sure of is that the objects you expect to see in
dirty are actually there. Sometimes objects don't make it into dirty
until they are found to be impacted by a relationship() changing something
on them. If this is the case you'd want to navigate to those objects
differently.
On Jun 6, 2012, at 6:36 AM, Damian Dimmich wrote:
Hi,
I have a before flush event set up that sets the current user_id and
datetime on new objects that are going to be commited to the database (all
tables have these fields).
Unfortunately, it seems to ignore changes I make to existing objects, ie
when I set the user_id (which i get from pyramid) and last_updated columns
in the event handler, the sql only issues updates for the other columns
that where modified.
Any ideas as to what am I missing?
Thanks in advance,
Damian
def attach_user_committing(Session, flush_context, instances):
This function gets called by the before_flush event,
it grabs the current threads request and extracts the
authenticated user_id out of it. You can only
commit things if you are authenticated.
Once it has that id, it goes through and adds the id
of the user who modified it to new commits- need to be careful that
_new doesn't change.
#this is used when creating databases testing only
user_id = 1
if not creating_database:
user_id =
authenticated_userid(pyramid.threadlocal.get_current_request())
#for each object being committed/flushed, set the flushing/commiting user
for obj in Session._new.values():
#log.debug(obj)
obj.user_id = user_id
obj.last_updated = datetime.now()
log.debug('Session dirty is : %s' %Session.dirty)
for obj in Session.dirty:
log.debug('Objects in dirty: %s' % obj.__dict__)
#if obj._sa_instance_state.modified:
obj.user_id = user_id
obj.last_updated = datetime.now()
#session.user_id = request
#this event ensures that user_id lastupdate is correctly stored on each
commit.
event.listen(Session, before_flush, attach_user_committing)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
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: mapping without key
Thanks. Model that we work with has tables, which have no unique constraints. Keys can be inferred from data contained specified in ORM maping but there is no guarantee that this will always work because data may change. Still one could argue a case where mapping such table to a class has merit even if far removed from all the benefits of SQLAlchemy ORM. On Jun 6, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote: There's two variants to this question, and I can't tell which one you're asking for. If the views in question do in fact have candidate keys, that is, columns which uniquely identify a row, you just specify those either to the Table or mapper() as the columns that uniquely identify the row. They don't have to be considered primary by the database in any formal way. If OTOH you have views which truly have duplicate rows and no candidate key of any kind, the ORM won't do that. As you probably know, the primary key thing is more or less the spine of mapper() and Query, and there's really no way the ORM could be refactored, without a great loss of stability and performance, to make this requirement optional. If you're looking for duplicate rows to come back as individual objects, Query() can be handed Table objects to load rows from, so a custom Query subclass that wraps named tuples into objects could possibly approximate this effect. On Jun 6, 2012, at 3:01 PM, Victor Olex wrote: With the understanding that we would loose the ability to properly track the sate of a mapped object and ability to update or insert in ORM and likely the ability to correctly use relationships as well - how can one accomplish a mapper, which would work on tables (views) without any key, which uniquely identify records in it? The rationale for this question is to be able to be able to operate on these tables in object (ORM) context and to join them in queries with other normally mapped classes for reading purposes only. The ideal solution would be perhaps a different Declarative Base class using a modified mapper. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: mapping without key
To be clear this is not a feature request. I could use a hit how to build a fake mapper like this if not compatible in certain cases. On Jun 6, 5:52 pm, Victor Olex victor.o...@vtenterprise.com wrote: Thanks. Model that we work with has tables, which have no unique constraints. Keys can be inferred from data contained specified in ORM maping but there is no guarantee that this will always work because data may change. Still one could argue a case where mapping such table to a class has merit even if far removed from all the benefits of SQLAlchemy ORM. On Jun 6, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote: There's two variants to this question, and I can't tell which one you're asking for. If the views in question do in fact have candidate keys, that is, columns which uniquely identify a row, you just specify those either to the Table or mapper() as the columns that uniquely identify the row. They don't have to be considered primary by the database in any formal way. If OTOH you have views which truly have duplicate rows and no candidate key of any kind, the ORM won't do that. As you probably know, the primary key thing is more or less the spine of mapper() and Query, and there's really no way the ORM could be refactored, without a great loss of stability and performance, to make this requirement optional. If you're looking for duplicate rows to come back as individual objects, Query() can be handed Table objects to load rows from, so a custom Query subclass that wraps named tuples into objects could possibly approximate this effect. On Jun 6, 2012, at 3:01 PM, Victor Olex wrote: With the understanding that we would loose the ability to properly track the sate of a mapped object and ability to update or insert in ORM and likely the ability to correctly use relationships as well - how can one accomplish a mapper, which would work on tables (views) without any key, which uniquely identify records in it? The rationale for this question is to be able to be able to operate on these tables in object (ORM) context and to join them in queries with other normally mapped classes for reading purposes only. The ideal solution would be perhaps a different Declarative Base class using a modified mapper. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] auto reflect to create table declarative table code
This seems (to me) like an obvious question but a brief googling and looking at
the docs didn't seem to find the answer.
I have an existing database (lets say mysql)
I can easily create object like:
class BroadPeaks(Base):
__table__ = Table('broad_peaks', Base.metadata, autoload=True)
def __repr__(self):
return 'Peak %r' % (self.peak_name)
Once I am connected, yay. And I think this has already all the relationships I
need.
But let's say I am S lazy that I just want to auto generate the lines:
class ClassName(Base)
__table__ = Table('table_name', Base.metadata, autoload=True)
For each table. Should I just introspect the metadata object? I thought of
this but not all the tables were loaded...
Ben
--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO Consortium
Stanford University ** h...@stanford.edu
--
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] auto reflect to create table declarative table code
On Jun 6, 2012, at 7:47 PM, Benjamin Hitz wrote:
This seems (to me) like an obvious question but a brief googling and looking
at the docs didn't seem to find the answer.
I have an existing database (lets say mysql)
I can easily create object like:
class BroadPeaks(Base):
__table__ = Table('broad_peaks', Base.metadata, autoload=True)
def __repr__(self):
return 'Peak %r' % (self.peak_name)
Once I am connected, yay. And I think this has already all the relationships
I need.
But let's say I am S lazy that I just want to auto generate the lines:
class ClassName(Base)
__table__ = Table('table_name', Base.metadata, autoload=True)
For each table. Should I just introspect the metadata object? I thought of
this but not all the tables were loaded...
if you're incredibly lazy to that degree you might want to check out / help out
with SQLSoup, which has been with SQLAlchemy since the beginning but is now
it's own project:
http://readthedocs.org/docs/sqlsoup/en/latest/tutorial.html
basically you give it a name, it reflects that name and maps it. All kinds of
caveats apply.
--
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] auto reflect to create table declarative table code
Nah, I don't want a simpler interface, I want something that just generates the
code so I can extend it as needed.
Ben
On Jun 6, 2012, at 5:57 PM, Michael Bayer wrote:
On Jun 6, 2012, at 7:47 PM, Benjamin Hitz wrote:
This seems (to me) like an obvious question but a brief googling and looking
at the docs didn't seem to find the answer.
I have an existing database (lets say mysql)
I can easily create object like:
class BroadPeaks(Base):
__table__ = Table('broad_peaks', Base.metadata, autoload=True)
def __repr__(self):
return 'Peak %r' % (self.peak_name)
Once I am connected, yay. And I think this has already all the
relationships I need.
But let's say I am S lazy that I just want to auto generate the lines:
class ClassName(Base)
__table__ = Table('table_name', Base.metadata, autoload=True)
For each table. Should I just introspect the metadata object? I thought of
this but not all the tables were loaded...
if you're incredibly lazy to that degree you might want to check out / help
out with SQLSoup, which has been with SQLAlchemy since the beginning but is
now it's own project:
http://readthedocs.org/docs/sqlsoup/en/latest/tutorial.html
basically you give it a name, it reflects that name and maps it. All kinds
of caveats apply.
--
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.
--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO Consortium
Stanford University ** h...@stanford.edu
--
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.
