[sqlalchemy] using scoped_session in serial mode as well as parallel (multi-threaded) mode
Hi, I'm using scoped_session with PostgreSQL to run multiple threads, each thread creating a table. However, I'm getting some pretty weird and inconsistent errors (segfaults from Python with some very peculiar errors), and I was wondering if my usage was at fault. The program has a serial bit, and a parallel bit, and currently I'm doing something like this. For the serial bit I do db = create_engine(dbstring) Session = sessionmaker() session = Session(bind=db) and then later I do session.close() db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) Looking at this is seems likely that is would be better to just use scoped_session everywhere, that is, just start with db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) [proceed with using Session in serial mode and eventually use it in parallel mode too] I'm basically writing to confirm that it is Ok to use scoped_session in this way. The way I'm doing it looks a little dodgy. I don't know if this is really the cause of my problem - just clutching at straws here. Thanks in advance. Please CC me on any reply. Regards, Faheem -- 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] mixed up relationships
I'm using SQLAlchemy 0.6.3 on Python 2.6 (win32) I have a class, Efforts, which has many splits (time and distance and some other fluff), so for each training effort, there can be one or more splits. It has a few other relationships as well, but this is the important one, or at least, the one I'm stuck on at the moment! The definition in my script : class Efforts(Base): __tablename__ = 'efforts' id = Column(Integer, primary_key = True) effortTypeId = Column(Integer, ForeignKey(effortTypes.id), nullable=False) riderId = Column(Integer, ForeignKey(riders.id), nullable=False) sessionId = Column(Integer, ForeignKey(trainingSessions.id), nullable=False) . # stuff trimmed to save space . splits = relationship(Splits, backref='efforts', order_by=Splits.sequenceNumber) rider = relationship(Riders, backref='efforts', order_by=Riders.id) session = relationship(TrainingSessions, backref='efforts', order_by= TrainingSessions.id) def __init__(self, effortTypeId, riderId, sessionId, distance, time, maxpower, fiveSecPower, \ maxTorque, startTime, temperature, pressure, humidity, windSpeed, comments, timingAccuracy, \ gearInches, seated, standingStartType, startingSpeed, startingLeg, riderWeight, bikeWeight, \ extraWeight, borgRPE): self.effortTypeId = effortTypeId self.riderId = riderId self.sessionId = sessionId # stuff trimmed ... and the 'splits' definition : class Splits(Base): __tablename__ = 'splits' id = Column(Integer, primary_key = True) effort = Column(Integer, ForeignKey(efforts.id), nullable=False) sequenceNumber = Column(Integer, nullable=False, default = 1) distance = Column(Float, nullable=False) time = Column(Float, nullable=False) timingAccuracy = Column(Float, default = 0.1) def __init__(self, effort, sequenceNumber, distance, time, timingAccuracy): self.effort = effort self.sequenceNumber = sequenceNumber self.distance = distance self.time = time self.timingAccuracy = timingAccuracy I've stuffed something up, because when I create an 'effort' : trainingEffort = stDataClasses.Efforts( effortTypeId = ChosenEffortId,\ riderId = self.rider.id,\ sessionId = self.thisSession.id,\ . . . ) print trainingEffort print splits in trainingEffort :, trainingEffort.Splits I should, I think, see a null array when I ask for trainingEffort.Splits, but instead I see this : AttributeError: 'Efforts' object has no attribue 'Splits' Can anyone here untangle my mess? My python skill level is still very much a novice, I've read the SQLAlchemy doco for the ORM but I think something's just not sinking in. Thank you for any pointers in the right direction! Carl -- 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: using scoped_session in serial mode as well as parallel (multi-threaded) mode
Addendum: the types of error I'm seeing includes SQLA trying to execute notices from the PG server eg. one of the tracebacks I'm seeing is: #0 PyObject_Malloc (nbytes=86) at ../Objects/obmalloc.c:756 #1 0x00455eb5 in PyString_FromString (str=0x2de0ece0 WARNING: there is no transaction in progress\n) at ../Objects/stringobject.c:139 This smells like memory being overwritten. Any idea what might be causing this? Another possibility is that my usage of scoped_session is wrong. I can't find any explicit examples of usage in the official documentation, so this was partly guesswork on my part. Here is a sketch of my usage. The model I'm using is a thread pool, which lines up n jobs in a queue, and has a pool of k threads executing them. The problems seem to occur when n is too large. Session = scoped_session(sessionmaker()) Session.configure(bind=db) pool = ThreadPool(Session, self.jobs) ids = self.get_idlink_ids(Session) for i in ids: pool.add_task(self.load_geno_table_from_file, Session, i) pool.start() where load_geno_table_from_file is def load_geno_table_from_file(self, session, i): session.execute(ALTER TABLE %s.geno%s SET ( autovacuum_enabled = false );%(self.schema, i)) self.drop_geno_table_constraints(session, 'geno%s'%i) self.copy_data_to_geno_table(session, 'geno%s'%i, 'tmp/geno%s'%i) self.restore_geno_table_constraints(session, 'geno%s'%i) session.execute(ALTER TABLE %s.geno%s SET ( autovacuum_enabled = true );%(self.schema, i)) and add_task is def add_task(self, func, *args, **kargs): Add a task to the queue self.queue.put((func, args, kargs)) So, Session is passed to load_geno_table_from_file, which executes inside each thread. Is that Ok? I'm adding the rest of the thread code below for reference. Regards, Faheem *** import Queue, threading, urllib2, time class Worker(threading.Thread): Thread executing tasks from a given tasks queue def __init__(self, session, queue, num): threading.Thread.__init__(self) self.num = num self.queue = queue self.setDaemon(True) self.session = session def run(self): import traceback while True: func, args, kargs = self.queue.get() try: func(*args, **kargs) except: traceback.print_exc() self.queue.task_done() class ThreadPool: Pool of threads consuming tasks from a queue def __init__(self, session, num_threads): from geno import Geno_Shard self.queue = Queue.Queue() self.workerlist = [] self.num = num_threads self.session = session for i in range(num_threads): self.workerlist.append(Worker(session, self.queue, i)) def add_task(self, func, *args, **kargs): Add a task to the queue self.queue.put((func, args, kargs)) def start(self): for w in self.workerlist: w.start() def wait_completion(self): Wait for completion of all the tasks in the queue self.queue.join() On Thu, 12 Aug 2010, Faheem Mitha wrote: Hi, I'm using scoped_session with PostgreSQL to run multiple threads, each thread creating a table. However, I'm getting some pretty weird and inconsistent errors (segfaults from Python with some very peculiar errors), and I was wondering if my usage was at fault. The program has a serial bit, and a parallel bit, and currently I'm doing something like this. For the serial bit I do db = create_engine(dbstring) Session = sessionmaker() session = Session(bind=db) and then later I do session.close() db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) Looking at this is seems likely that is would be better to just use scoped_session everywhere, that is, just start with db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) [proceed with using Session in serial mode and eventually use it in parallel mode too] I'm basically writing to confirm that it is Ok to use scoped_session in this way. The way I'm doing it looks a little dodgy. I don't know if this is really the cause of my problem - just clutching at straws here. Thanks in advance. Please CC me on any reply. Regards, Faheem -- 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] Multiple Sessions
I've found a strange behaviour when using multiple sessions: A
committed change to an object in one session does not get reflected in
the other session, even after a session.expire_all() (or expire() or
refresh() on the object. In fact, even an explicit query does not
retrieve the changed data. I'm using MySQL. Further investigation
showed that it happens only with InnoDB. Using MyISAM produces the
expected results. Looks like a caching issue with InnoDB.
Here is some test code:
from sqlalchemy import create_engine, Table, Column, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base=declarative_base()
class A(Base):
__tablename__=a
__table_args__ = {'mysql_engine':'InnoDB'}
id = Column(Integer, primary_key=True)
data = Column(Integer)
engine = create_engine('mysql://localhost/test')
Base.metadata.bind = engine
Base.metadata.drop_all()
Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session1 = Session()
session2 = Session()
a1 = A()
a1.id = 1
a1.data = 123
session1.add(a1)
session1.commit()
print a1.data # trigger query of a1.data
a2 = session2.query(A).one()
print a1,a2 # to see that a1 and a2 are different objects
print a2.data
a2.data = 999
session2.commit()
print a1.data: , a1.data
print session1.query(A.data): , session1.query(A.data).one()[0]
print session2.query(A.data): , session2.query(A.data).one()[0]
print session1.expire_all()
session1.expire_all()
print a1.data: , a1.data
print session1.query(A.data): , session1.query(A.data).one()[0]
print session2.query(A.data): , session2.query(A.data).one()[0]
The output is:
123
a1.data: 123
session1.query(A.data): 123
session2.query(A.data): 999
session1.expire_all()
a1.data: 123
session1.query(A.data): 123
session2.query(A.data): 999
When commenting out the __table_args__ line in order to use MyISAM,
the output is what one would expect:
123
a1.data: 123
session1.query(A.data): 999
session2.query(A.data): 999
session1.expire_all()
a1.data: 999
session1.query(A.data): 999
session2.query(A.data): 999
Any idea?
Thanks,
Erich
--
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: mixed up relationships
Carl, the formatting got a bit messed up but if I read your definition correctly you defined the relation as 'splits'. So you'd want to write trainingEffor.splits (lower case 's' in splits). -sas On Aug 12, 9:20 am, Bleve carl.i.bre...@gmail.com wrote: I'm using SQLAlchemy 0.6.3 on Python 2.6 (win32) I have a class, Efforts, which has many splits (time and distance and some other fluff), so for each training effort, there can be one or more splits. It has a few other relationships as well, but this is the important one, or at least, the one I'm stuck on at the moment! The definition in my script : class Efforts(Base): __tablename__ = 'efforts' id = Column(Integer, primary_key = True) effortTypeId = Column(Integer, ForeignKey(effortTypes.id), nullable=False) riderId = Column(Integer, ForeignKey(riders.id), nullable=False) sessionId = Column(Integer, ForeignKey(trainingSessions.id), nullable=False) . # stuff trimmed to save space . splits = relationship(Splits, backref='efforts', order_by=Splits.sequenceNumber) rider = relationship(Riders, backref='efforts', order_by=Riders.id) session = relationship(TrainingSessions, backref='efforts', order_by= TrainingSessions.id) def __init__(self, effortTypeId, riderId, sessionId, distance, time, maxpower, fiveSecPower, \ maxTorque, startTime, temperature, pressure, humidity, windSpeed, comments, timingAccuracy, \ gearInches, seated, standingStartType, startingSpeed, startingLeg, riderWeight, bikeWeight, \ extraWeight, borgRPE): self.effortTypeId = effortTypeId self.riderId = riderId self.sessionId = sessionId # stuff trimmed ... and the 'splits' definition : class Splits(Base): __tablename__ = 'splits' id = Column(Integer, primary_key = True) effort = Column(Integer, ForeignKey(efforts.id), nullable=False) sequenceNumber = Column(Integer, nullable=False, default = 1) distance = Column(Float, nullable=False) time = Column(Float, nullable=False) timingAccuracy = Column(Float, default = 0.1) def __init__(self, effort, sequenceNumber, distance, time, timingAccuracy): self.effort = effort self.sequenceNumber = sequenceNumber self.distance = distance self.time = time self.timingAccuracy = timingAccuracy I've stuffed something up, because when I create an 'effort' : trainingEffort = stDataClasses.Efforts( effortTypeId = ChosenEffortId,\ riderId = self.rider.id,\ sessionId = self.thisSession.id,\ . . . ) print trainingEffort print splits in trainingEffort :, trainingEffort.Splits I should, I think, see a null array when I ask for trainingEffort.Splits, but instead I see this : AttributeError: 'Efforts' object has no attribue 'Splits' Can anyone here untangle my mess? My python skill level is still very much a novice, I've read the SQLAlchemy doco for the ORM but I think something's just not sinking in. Thank you for any pointers in the right direction! Carl -- 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: using scoped_session in serial mode as well as parallel (multi-threaded) mode
Another reply to my own message, sorry. Another thing I might be doing wrong is my usage of MetaData. The code I have looks like db = create_engine(self.dbstring) meta = self.table_dict['metadata'] meta.bind = db meta.create_all() Session = scoped_session(sessionmaker()) Session.configure(bind=db) pool = ThreadPool(Session, self.jobs) ids = self.get_idlink_ids(Session) for i in ids: pool.add_task(self.load_geno_table_from_file, Session, i) pool.start() Perhaps creation of this metadata instance is not thread-safe? I found reference to a ThreadLocalMetaData. Would it better to use that instead? Regards, Faheem. On Thu, 12 Aug 2010, Faheem Mitha wrote: Addendum: the types of error I'm seeing includes SQLA trying to execute notices from the PG server eg. one of the tracebacks I'm seeing is: #0 PyObject_Malloc (nbytes=86) at ../Objects/obmalloc.c:756 #1 0x00455eb5 in PyString_FromString (str=0x2de0ece0 WARNING: there is no transaction in progress\n) at ../Objects/stringobject.c:139 This smells like memory being overwritten. Any idea what might be causing this? Another possibility is that my usage of scoped_session is wrong. I can't find any explicit examples of usage in the official documentation, so this was partly guesswork on my part. Here is a sketch of my usage. The model I'm using is a thread pool, which lines up n jobs in a queue, and has a pool of k threads executing them. The problems seem to occur when n is too large. Session = scoped_session(sessionmaker()) Session.configure(bind=db) pool = ThreadPool(Session, self.jobs) ids = self.get_idlink_ids(Session) for i in ids: pool.add_task(self.load_geno_table_from_file, Session, i) pool.start() where load_geno_table_from_file is def load_geno_table_from_file(self, session, i): session.execute(ALTER TABLE %s.geno%s SET ( autovacuum_enabled = false );%(self.schema, i)) self.drop_geno_table_constraints(session, 'geno%s'%i) self.copy_data_to_geno_table(session, 'geno%s'%i, 'tmp/geno%s'%i) self.restore_geno_table_constraints(session, 'geno%s'%i) session.execute(ALTER TABLE %s.geno%s SET ( autovacuum_enabled = true );%(self.schema, i)) and add_task is def add_task(self, func, *args, **kargs): Add a task to the queue self.queue.put((func, args, kargs)) So, Session is passed to load_geno_table_from_file, which executes inside each thread. Is that Ok? I'm adding the rest of the thread code below for reference. Regards, Faheem *** import Queue, threading, urllib2, time class Worker(threading.Thread): Thread executing tasks from a given tasks queue def __init__(self, session, queue, num): threading.Thread.__init__(self) self.num = num self.queue = queue self.setDaemon(True) self.session = session def run(self): import traceback while True: func, args, kargs = self.queue.get() try: func(*args, **kargs) except: traceback.print_exc() self.queue.task_done() class ThreadPool: Pool of threads consuming tasks from a queue def __init__(self, session, num_threads): from geno import Geno_Shard self.queue = Queue.Queue() self.workerlist = [] self.num = num_threads self.session = session for i in range(num_threads): self.workerlist.append(Worker(session, self.queue, i)) def add_task(self, func, *args, **kargs): Add a task to the queue self.queue.put((func, args, kargs)) def start(self): for w in self.workerlist: w.start() def wait_completion(self): Wait for completion of all the tasks in the queue self.queue.join() On Thu, 12 Aug 2010, Faheem Mitha wrote: Hi, I'm using scoped_session with PostgreSQL to run multiple threads, each thread creating a table. However, I'm getting some pretty weird and inconsistent errors (segfaults from Python with some very peculiar errors), and I was wondering if my usage was at fault. The program has a serial bit, and a parallel bit, and currently I'm doing something like this. For the serial bit I do db = create_engine(dbstring) Session = sessionmaker() session = Session(bind=db) and then later I do session.close() db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) Looking at this is seems likely that is would be better to just use scoped_session everywhere, that is, just start with db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) [proceed with using Session in serial mode and eventually use it in parallel mode too] I'm basically writing to confirm that it is Ok to use
Re: [sqlalchemy] Multiple Sessions
On Aug 12, 2010, at 7:36 AM, Erich Eder wrote:
I've found a strange behaviour when using multiple sessions: A
committed change to an object in one session does not get reflected in
the other session, even after a session.expire_all() (or expire() or
refresh() on the object. In fact, even an explicit query does not
retrieve the changed data. I'm using MySQL. Further investigation
showed that it happens only with InnoDB. Using MyISAM produces the
expected results. Looks like a caching issue with InnoDB.
its not caching, its transaction isolation, which is why expire_all() is not
the issue here (and why expire_all() is not really needed by itself with
autocommit=False - rollback() and commit() handle it). at the end, session1
is still open in its second transaction which has loaded a1.data as 123.
http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html
Here is some test code:
from sqlalchemy import create_engine, Table, Column, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base=declarative_base()
class A(Base):
__tablename__=a
__table_args__ = {'mysql_engine':'InnoDB'}
id = Column(Integer, primary_key=True)
data = Column(Integer)
engine = create_engine('mysql://localhost/test')
Base.metadata.bind = engine
Base.metadata.drop_all()
Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session1 = Session()
session2 = Session()
a1 = A()
a1.id = 1
a1.data = 123
session1.add(a1)
session1.commit()
print a1.data # trigger query of a1.data
a2 = session2.query(A).one()
print a1,a2 # to see that a1 and a2 are different objects
print a2.data
a2.data = 999
session2.commit()
print a1.data: , a1.data
print session1.query(A.data): , session1.query(A.data).one()[0]
print session2.query(A.data): , session2.query(A.data).one()[0]
print session1.expire_all()
session1.expire_all()
print a1.data: , a1.data
print session1.query(A.data): , session1.query(A.data).one()[0]
print session2.query(A.data): , session2.query(A.data).one()[0]
The output is:
123
a1.data: 123
session1.query(A.data): 123
session2.query(A.data): 999
session1.expire_all()
a1.data: 123
session1.query(A.data): 123
session2.query(A.data): 999
When commenting out the __table_args__ line in order to use MyISAM,
the output is what one would expect:
123
a1.data: 123
session1.query(A.data): 999
session2.query(A.data): 999
session1.expire_all()
a1.data: 999
session1.query(A.data): 999
session2.query(A.data): 999
Any idea?
Thanks,
Erich
--
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.
Re: [sqlalchemy] Re: mixed up relationships
On 12/08/2010 10:03 PM, Sven A. Schmidt wrote: Carl, the formatting got a bit messed up but if I read your definition correctly you defined the relation as 'splits'. So you'd want to write trainingEffor.splits (lower case 's' in splits). heh! Case sensitivity bites! Thank you Carl -- 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] using scoped_session in serial mode as well as parallel (multi-threaded) mode
On Aug 12, 2010, at 5:10 AM, Faheem Mitha wrote: Hi, I'm using scoped_session with PostgreSQL to run multiple threads, each thread creating a table. Its generally a poor application practice for an application to need new permanent tables on the fly. I think reddit's application might do this, but its considered poor design. Database schemas are supposed to be fixed. However, I'm getting some pretty weird and inconsistent errors (segfaults from Python with some very peculiar errors), and I was wondering if my usage was at fault. The program has a serial bit, and a parallel bit, and currently I'm doing something like this. For the serial bit I do db = create_engine(dbstring) Session = sessionmaker() session = Session(bind=db) and then later I do session.close() db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) Looking at this is seems likely that is would be better to just use scoped_session everywhere, that is, just start with db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) [proceed with using Session in serial mode and eventually use it in parallel mode too] I'm basically writing to confirm that it is Ok to use scoped_session in this way. The way I'm doing it looks a little dodgy. I don't know if this is really the cause of my problem - just clutching at straws here. Thanks in advance. Please CC me on any reply. you can make as many scoped_sessions, metadatas, etc. as you want, none of that would cause a segfault.They are just Python objects. Its only if you share a psycopg2 connection between threads and have different threads hammer on it simultaneously that there would be issues like that.If you have a single session, and share that among threads who access it concurrently, this will produce that result. There should be no need to guess about it. If multiple threads are hitting the session you have above, then that's a likely cause of your issue. -- 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: using scoped_session in serial mode as well as parallel (multi-threaded) mode
On Thu, 12 Aug 2010 08:47:33 -0400, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 5:10 AM, Faheem Mitha wrote: Hi, I'm using scoped_session with PostgreSQL to run multiple threads, each thread creating a table. Its generally a poor application practice for an application to need new permanent tables on the fly. I think reddit's application might do this, but its considered poor design. Database schemas are supposed to be fixed. I'm not sure what you mean by on-the-fly. The app is creating the tables for later use. It is parallelizing the table creation for performance reasons - there are a lot of tables. However, I'm getting some pretty weird and inconsistent errors (segfaults from Python with some very peculiar errors), and I was wondering if my usage was at fault. The program has a serial bit, and a parallel bit, and currently I'm doing something like this. For the serial bit I do db = create_engine(dbstring) Session = sessionmaker() session = Session(bind=db) and then later I do session.close() db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) Looking at this is seems likely that is would be better to just use scoped_session everywhere, that is, just start with db = create_engine(self.dbstring) Session = scoped_session(sessionmaker()) Session.configure(bind=db) [proceed with using Session in serial mode and eventually use it in parallel mode too] I'm basically writing to confirm that it is Ok to use scoped_session in this way. The way I'm doing it looks a little dodgy. I don't know if this is really the cause of my problem - just clutching at straws here. Thanks in advance. Please CC me on any reply you can make as many scoped_sessions, metadatas, etc. as you want, none of that would cause a segfault. They are just Python objects. Its only if you share a psycopg2 connection between threads and have different threads hammer on it simultaneously that there would be issues like that. If you have a single session, and share that among threads who access it concurrently, this will produce that result. There should be no need to guess about it. If multiple threads are hitting the session you have above, then that's a likely cause of your issue. Hi Mike, Thanks for the response, but I don't follow. When you say multiple threads are hitting the session you have above, which session are you referring to? There is more than one object above that could be called a session. Ie. Session1 in Session1 = sessionmaker() session1 in session1 = Session1(bind=db) Session2 in Session2 = scoped_session(sessionmaker()) Let me try to ask a precise question. If I do Session = scoped_session(sessionmaker()) then is it ok for this Session object to be be passed around between multiple threads and used directly as in Session.commit() Does this correspond to a single psycopyg2 connection? If it does, and this usage is wrong, should I be creating separate sessions within each thread like session = Session() and then doing session.commit() within each thread? Or something else? My usage is based on examples online. My later posts have more details, along with healthy amounts of confusion. I apologise for my cluelessness, sorry. Regards, Faheem -- 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: using scoped_session in serial mode as well as parallel (multi-threaded) mode
On Aug 12, 2010, at 9:25 AM, Faheem Mitha wrote: Hi Mike, Thanks for the response, but I don't follow. When you say multiple threads are hitting the session you have above, which session are you referring to? There is more than one object above that could be called a session. Ie. I don't actually know, it would require that I have a full install of your application for me to run and step through with a debugger to fully understand what it's doing. All I can say from here is that the errors you have suggest concurrent access to a single psycopg2 connection resource, and that a single Session references a single connection when in use. A MetaData object does not, nor does an Engine - only a Session.If you remove all threading from your application and the errors go away, then you know you're accessing some resource illegally. Session1 in Session1 = sessionmaker() session1 in session1 = Session1(bind=db) Session2 in Session2 = scoped_session(sessionmaker()) Let me try to ask a precise question. If I do Session = scoped_session(sessionmaker()) then is it ok for this Session object to be be passed around between multiple threads and used directly as in Session.commit() Does this correspond to a single psycopyg2 connection? If it does, and this usage is wrong, should I be creating separate sessions within each thread like That's a scoped_session, which is threadsafe. Everything you call upon it will acquire a Session object from a thread local context, and commit() is called on that (for information on thread locals, see http://docs.python.org/library/threading.html#threading.local. If you pass a scoped_session from one thread to another, and the second thread calls commit(), the second thread is not affecting the transaction begun by the first. They are two separate transactions. -- 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: using scoped_session in serial mode as well as parallel (multi-threaded) mode
On Thu, 12 Aug 2010 09:44:04 -0400, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 9:25 AM, Faheem Mitha wrote: Hi Mike, Thanks for the response, but I don't follow. When you say multiple threads are hitting the session you have above, which session are you referring to? There is more than one object above that could be called a session. Ie. I don't actually know, it would require that I have a full install of your application for me to run and step through with a debugger to fully understand what it's doing. All I can say from here is that the errors you have suggest concurrent access to a single psycopg2 connection resource, and that a single Session references a single connection when in use. A MetaData object does not, nor does an Engine - only a Session. If you remove all threading from your application and the errors go away, then you know you're accessing some resource illegally. Yes, I see. Yes, the error does not show up unless I run multiple threads, and I agree with your interpretation. If MetaData is threadsafe, then using ThreadLocalMetaData is not necessary? Session1 in Session1 = sessionmaker() session1 in session1 = Session1(bind=db) Session2 in Session2 = scoped_session(sessionmaker()) Let me try to ask a precise question. If I do Session = scoped_session(sessionmaker()) then is it ok for this Session object to be be passed around between multiple threads and used directly as in Session.commit() Does this correspond to a single psycopyg2 connection? If it does, and this usage is wrong, should I be creating separate sessions within each thread like That's a scoped_session, which is threadsafe. Everything you call upon it will acquire a Session object from a thread local context, and commit() is called on that (for information on thread locals, see http://docs.python.org/library/threading.html#threading.local. If you pass a scoped_session from one thread to another, and the second thread calls commit(), the second thread is not affecting the transaction begun by the first. They are two separate transactions. Ok. Thanks for the confirmation. So, if I was to use scoped sessions systematically everywhere, this problem would likely disappear. Can you confirm that there is no reason not to use scoped sessions everywhere, even in serial execution? Of course, if that is the case, then I wonder why non-scoped sessions are used at all. Regards, Faheem. -- 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: using scoped_session in serial mode as well as parallel (multi-threaded) mode
On Thu, 12 Aug 2010 09:44:04 -0400, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 9:25 AM, Faheem Mitha wrote: Hi Mike, Thanks for the response, but I don't follow. When you say multiple threads are hitting the session you have above, which session are you referring to? There is more than one object above that could be called a session. Ie. I don't actually know, it would require that I have a full install of your application for me to run and step through with a debugger to fully understand what it's doing. All I can say from here is that the errors you have suggest concurrent access to a single psycopg2 connection resource, and that a single Session references a single connection when in use. A MetaData object does not, nor does an Engine - only a Session.If you remove all threading from your application and the errors go away, then you know you're accessing some resource illegally. Session1 in Session1 = sessionmaker() session1 in session1 = Session1(bind=db) Session2 in Session2 = scoped_session(sessionmaker()) Let me try to ask a precise question. If I do Session = scoped_session(sessionmaker()) then is it ok for this Session object to be be passed around between multiple threads and used directly as in Session.commit() Does this correspond to a single psycopyg2 connection? If it does, and this usage is wrong, should I be creating separate sessions within each thread like That's a scoped_session, which is threadsafe. Everything you call upon it will acquire a Session object from a thread local context, and commit() is called on that (for information on thread locals, see http://docs.python.org/library/threading.html#threading.local. If you pass a scoped_session from one thread to another, and the second thread calls commit(), the second thread is not affecting the transaction begun by the first. They are two separate transactions. -- 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: using scoped_session in serial mode as well as parallel (multi-threaded) mode
On Aug 12, 2010, at 11:47 AM, Faheem Mitha wrote: On Thu, 12 Aug 2010 09:44:04 -0400, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 9:25 AM, Faheem Mitha wrote: Hi Mike, Thanks for the response, but I don't follow. When you say multiple threads are hitting the session you have above, which session are you referring to? There is more than one object above that could be called a session. Ie. I don't actually know, it would require that I have a full install of your application for me to run and step through with a debugger to fully understand what it's doing. All I can say from here is that the errors you have suggest concurrent access to a single psycopg2 connection resource, and that a single Session references a single connection when in use. A MetaData object does not, nor does an Engine - only a Session. If you remove all threading from your application and the errors go away, then you know you're accessing some resource illegally. Yes, I see. Yes, the error does not show up unless I run multiple threads, and I agree with your interpretation. If MetaData is threadsafe, then using ThreadLocalMetaData is not necessary? ThreadLocalMetaData is not necessary and is not used for this purpose. Ok. Thanks for the confirmation. So, if I was to use scoped sessions systematically everywhere, this problem would likely disappear. that's not necessarily true - if you share individual persistent objects among threads, they reference their owning session in order to load additional state. If you pass objects between threads you should merge() them into the current thread's session first, then use that result. Can you confirm that there is no reason not to use scoped sessions everywhere, even in serial execution? Of course, if that is the case, then I wonder why non-scoped sessions are used at all. scoped_sessions are usually the default choice for web applications since they desire distinct transactions and object state for individual threads.They are overkill and potentially confusing or inappropriate in other situations, however. -- 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: User DataType for casting
On Jul 30, 7:25 am, Kent k...@retailarchitects.com wrote: Also, I'm afraid the CAST(NULL AS VARCHAR(255)) doesn't work with Oracle 8, but I don't have access to Oracle 8 at the moment. I'm afraid you need TO_NUMBER(NULL) or TO_CHAR(NULL), etc... I'm wrong, CAST seems to work fine on Oracle 8i. -- 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] How to link one table to itself?
Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when I add any element to it. Any idea??? Thanks in advance! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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: using scoped_session in serial mode as well as parallel (multi-threaded) mode
[This message has also been posted.] On Thu, 12 Aug 2010 12:47:37 -0400, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 11:47 AM, Faheem Mitha wrote: On Thu, 12 Aug 2010 09:44:04 -0400, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 9:25 AM, Faheem Mitha wrote: Hi Mike, Thanks for the response, but I don't follow. When you say multiple threads are hitting the session you have above, which session are you referring to? There is more than one object above that could be called a session. Ie. I don't actually know, it would require that I have a full install of your application for me to run and step through with a debugger to fully understand what it's doing. All I can say from here is that the errors you have suggest concurrent access to a single psycopg2 connection resource, and that a single Session references a single connection when in use. A MetaData object does not, nor does an Engine - only a Session. If you remove all threading from your application and the errors go away, then you know you're accessing some resource illegally. Yes, I see. Yes, the error does not show up unless I run multiple threads, and I agree with your interpretation. If MetaData is threadsafe, then using ThreadLocalMetaData is not necessary? ThreadLocalMetaData is not necessary and is not used for this purpose. Ok. Thanks for the confirmation. So, if I was to use scoped sessions systematically everywhere, this problem would likely disappear. that's not necessarily true - if you share individual persistent objects among threads, they reference their owning session in order to load additional state. If you pass objects between threads you should merge() them into the current thread's session first, then use that result. I see. That's very enlightening. Can one query such objects to determine their owning session? Some attribute, perhaps? Can you confirm that there is no reason not to use scoped sessions everywhere, even in serial execution? Of course, if that is the case, then I wonder why non-scoped sessions are used at all. scoped_sessions are usually the default choice for web applications since they desire distinct transactions and object state for individual threads. They are overkill and potentially confusing or inappropriate in other situations, however. I'm not sure why they would be potentially confusing. What are some of the downsides? I'd have thought that not having shared state was less confusing. Regards, Faheem. -- 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: using scoped_session in serial mode as well as parallel (multi-threaded) mode
On Aug 12, 2010, at 2:41 PM, Faheem Mitha wrote: objects among threads, they reference their owning session in order to load additional state. If you pass objects between threads you should merge() them into the current thread's session first, then use that result. I see. That's very enlightening. Can one query such objects to determine their owning session? Some attribute, perhaps? see http://www.sqlalchemy.org/docs/session.html#frequently-asked-questions Can you confirm that there is no reason not to use scoped sessions everywhere, even in serial execution? Of course, if that is the case, then I wonder why non-scoped sessions are used at all. scoped_sessions are usually the default choice for web applications since they desire distinct transactions and object state for individual threads. They are overkill and potentially confusing or inappropriate in other situations, however. I'm not sure why they would be potentially confusing. What are some of the downsides? I'd have thought that not having shared state was less confusing. the scoped_session is a proxy object to the real object. It is less confusing for those unfamiliar with thread locals and proxy objects to deal with a Session directly. -- 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: How to link one table to itself?
I'm still working on the solution. I've found out some stuff in internet. I guess I'm close to, but I haven't got it yet. I'm using this for the relation: mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref='media_groups', foreign_keys = [media_group_groups.groupA_id, media_group_groups.groupB_id], primaryjoin = MediaGroup.id == media_group_groups.groupA_id, secondaryjoin = MediaGroup.id == media_group_groups.groupB_id) I'm playing with the parameters, but I usually get this error: ArgumentError: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. Thank you! On Aug 12, 1:08 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when I add any element to it. Any idea??? Thanks in advance! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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: How to link one table to itself?
On Aug 12, 2010, at 5:09 PM, Michael Bayer wrote: On Aug 12, 2010, at 4:58 PM, Alvaro Reinoso wrote: I'm still working on the solution. I've found out some stuff in internet. I guess I'm close to, but I haven't got it yet. I'm using this for the relation: mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref='media_groups', foreign_keys = [media_group_groups.groupA_id, media_group_groups.groupB_id], primaryjoin = MediaGroup.id == media_group_groups.groupA_id, secondaryjoin = MediaGroup.id == media_group_groups.groupB_id) I'm playing with the parameters, but I usually get this error: ArgumentError: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. media_group_groups is not available when primaryjoin is evaluated as a string, nor within foreign_keys which is not necessary here since your meta_group_groups already has ForeignKey objects on it, so use a non-string format for primaryjoin. i will add additional examples to the declarative docs. scratch part of that, tablenames are available in the string eval as long as they're from the same MetaData: mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups, primaryjoin=MediaGroup.id==media_group_groups.c.groupA_id, secondaryjoin=MediaGroup.id==media_group_groups.c.groupB_id also the error for the exampe you have above should be 'Table' object has no attribute 'groupA_id'. Thank you! On Aug 12, 1:08 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when I add any element to it. Any idea??? Thanks in advance! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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. -- 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] Extending and automating the vertical dictionary paradigm
Hi all --
I am relatively new to sqlalchemy and am in the midst of building a
generic vertical dictionary collection base class that can be
subclassed and extended in different ways. I began by examining the
dictlike.py and dictlike-polymorphic SQLalchemy examples and
extended them into a class factory framework using metaclasses. This
is my first attempt at using metaclasses and class-level programming
in python. Although my implementation works, I feel that
contributions and suggestions from all of you would substantially
improve the design. Any comments and/or feedback would be appreciated
-- and I apologize for the somewhat complicated code.
A summary of my approach is as follows:
1) Create a method to automatically generated the database tables for
a new vertical parent class
2) Create a method to create the class types to use in mapping to the
tables
3) Instrument and connect a new class to be vertically enabled using
a metaclass method
4) Provide a simple interface that the new class can inherit via the
VerticalAttrMixin class to provide methods for accessing the key/
values in the vertical dictionary collection.
The code is provided below:
--
from datetime import datetime
from sqlalchemy import Table, Column, DateTime, String, Integer, Enum,
Boolean, Float, Sequence, ForeignKey, UniqueConstraint,
ForeignKeyConstraint
from sqlalchemy.orm import mapper, relationship
from sqlalchemy.orm.collections import attribute_mapped_collection,
MappedCollection
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import DeclarativeMeta
from sqlalchemy.orm.exc import MultipleResultsFound,NoResultFound
from sequel2.db import metadata, Base, Session, SequelDBError
ENUM_TYPE = enum
_value_types = Enum(ENUM_TYPE, bool, int, float,
name=value_type_enum)
def create_vertical_attr_tables(parent_tablename):
keys_tablename = parent_tablename + '_attr_keys'
keys_pk_seq = parent_tablename + '_key_id_seq'
keys_table = Table(keys_tablename, metadata,
Column('id', Integer, Sequence(keys_pk_seq),
primary_key=True),
Column('name', String(50), unique=True),
Column('type', _value_types,
default=ENUM_TYPE),
Column('description', String(1000)))
values_tablename = parent_tablename + '_attr_enum_values'
values_pk_seq = parent_tablename + '_enum_value_id_seq'
values_table = Table(values_tablename, metadata,
Column('key_id', ForeignKey(keys_tablename +
'.id'), primary_key=True),
Column('value_id', Integer,
Sequence(values_pk_seq), primary_key=True),
Column('value', String(50), nullable=False),
Column('description', String(1000)),
UniqueConstraint('key_id', 'value',
name=value_unique_constraint))
attrs_tablename = parent_tablename + '_attrs'
attrs_fk = parent_tablename + '_id'
attrs_table = Table(attrs_tablename, metadata,
#Column('parent_id',
ForeignKey(parent_tablename + '.id'), primary_key=True),
Column(attrs_fk, ForeignKey(parent_tablename +
'.id'), primary_key=True),
Column('key_id', ForeignKey(keys_tablename +
'.id'), primary_key=True),
Column('enum_value_id',
ForeignKey(values_tablename + '.value_id')),
Column('bool_value', Boolean, default=False),
Column('int_value', Integer, default=0),
Column('float_value', Float, default=0),
# add some auditing columns
Column('updated_at', DateTime,
default=datetime.now),
Column('updated_by', String(50),
default=anonymous))
return keys_table, values_table, attrs_table
class VerticalAttrKey(object):
def __init__(self, name, type, description=None):
self.name = name
self.type = type
self.description = description
def __repr__(self):
return (%s(id='%s',name='%s',type='%s',description='%s')
% (self.__class__.__name__, self.id, self.name,
self.type, self.description))
def __getitem__(self, value):
assert self.type == ENUM_TYPE
return self.enum_values[value]
def __contains__(self, value):
assert self.type == ENUM_TYPE
return value in self.enum_values
# del an enum value from this key
def __delitem__(self, value):
assert self.type == ENUM_TYPE
del self.enum_values[value]
def get(self, value, default):
return self.enum_values.get(value, None)
def get_enums(self):
assert self.type == ENUM_TYPE
return self.enum_values.keys()
def set_enum(self, value, description=None, newvalue=None):
assert self.type == ENUM_TYPE
if value in
[sqlalchemy] Re: How to link one table to itself?
I'm trying that, but I got different error: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. As I can see, the error is related to the users table which I don't use in this class. However, user table is related to media_groups. This is the user's table: class User(rdb.Model): Represents the user rdb.metadata(metadata) rdb.tablename(users) id = Column(id, Integer, primary_key=True) name = Column(name, String(50)) email = Column(email, String(50)) channels = relationship(Channel, secondary=user_channels, order_by=Channel.titleView, backref=users) mediaGroups = relationship(MediaGroup, secondary=user_media_groups, order_by=MediaGroup.title, backref=users) Do I need to add something else to that table? Thanks!!! On Aug 12, 5:15 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 5:09 PM, Michael Bayer wrote: On Aug 12, 2010, at 4:58 PM, Alvaro Reinoso wrote: I'm still working on the solution. I've found out some stuff in internet. I guess I'm close to, but I haven't got it yet. I'm using this for the relation: mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref='media_groups', foreign_keys = [media_group_groups.groupA_id, media_group_groups.groupB_id], primaryjoin = MediaGroup.id == media_group_groups.groupA_id, secondaryjoin = MediaGroup.id == media_group_groups.groupB_id) I'm playing with the parameters, but I usually get this error: ArgumentError: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. media_group_groups is not available when primaryjoin is evaluated as a string, nor within foreign_keys which is not necessary here since your meta_group_groups already has ForeignKey objects on it, so use a non-string format for primaryjoin. i will add additional examples to the declarative docs. scratch part of that, tablenames are available in the string eval as long as they're from the same MetaData: mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups, primaryjoin=MediaGroup.id==media_group_groups.c.groupA_id, secondaryjoin=MediaGroup.id==media_group_groups.c.groupB_id also the error for the exampe you have above should be 'Table' object has no attribute 'groupA_id'. Thank you! On Aug 12, 1:08 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when I add any element to it. Any idea??? Thanks in advance! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received
[sqlalchemy] Specifying additional primaryjoin conditions for bidirectional adjacency list relationship
Hi there,
I'm exploring the bidirectional adjacency list pattern described in
the documentation (the scalar relationship is intentional):
Person.child = relationship(
Person,
uselist=False,
backref=backref('parent', remote_side=Person.id)
)
However, I'd like to add an additional primaryjoin condition,
something to the effect of:
child = aliased(Person)
Person.child_bob = relationship(
child,
uselist=False,
primaryjoin=and_(
child.parent_id == Person.id,
child.name == 'Bob'
),
backref=backref('parent', remote_side=Person.id)
)
But of course I can't use an AliasedClass in the first argument to
relationship(). Similarly I can't add the relationship property onto
an aliased parent class. What's the proper way of defining such a
relationship?
--
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: Multiple Sessions
Thanks for pointing me there, Michael. Wasn't aware of that InnoDB feature. Make all sense, now. On Aug 13, 12:37 am, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 7:36 AM, Erich Eder wrote: I've found a strange behaviour when using multiple sessions: A committed change to an object in one session does not get reflected in the other session, even after a session.expire_all() (or expire() or refresh() on the object. In fact, even an explicit query does not retrieve the changed data. I'm using MySQL. Further investigation showed that it happens only with InnoDB. Using MyISAM produces the expected results. Looks like a caching issue with InnoDB. its not caching, its transaction isolation, which is why expire_all() is not the issue here (and why expire_all() is not really needed by itself with autocommit=False - rollback() and commit() handle it). at the end, session1 is still open in its second transaction which has loaded a1.data as 123. http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html -- 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.
