Re: [sqlalchemy] Automatically retrieving the row index
Take a look at using ordering_list for the collection class on your relation. You add a position in season and SQLAlchemy will maintain the value. http://www.sqlalchemy.org/docs/orm/extensions/orderinglist.html -- Mike Conley -- 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] [None vs. NaN] Is there a Database independent way with SQLAlchemy to query filtered by “None”/“NaN”?
The following code is DB specific: import sqlalchemy # ... ergebnis = session.query( my_object.attr1).filter(sa.and_( my_object.attr2 != 'NaN')).all() # PostgreSQL # my_object.attr2 != None)).all() # sQLite With PostgreSQL it is 'NaN', with SQLite None (without single quotes). Is there a SQLAlchemy-way to do this backend independant? -- 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] Sqlalchemy+sqlite3 autoload problem
Hi everyone, I'm writing a script using sqlalchemy 0.66 and sqlite3. I'm encountering a problem trying to create and load a table from two different engines. Here is an example script showing the problem: from sqlalchemy import * engine1 = create_engine(sqlite:///test.db, echo=False) metadata1 = MetaData(engine1) try : table = Table(user, metadata1, autoload=True) table.drop() except : print Not found engine2 = create_engine(sqlite:///test.db, echo=False) metadata2 = MetaData(engine2) table = Table(user, metadata2, Column('id', Integer, primary_key=True), Column('name', String), Column('password', String), sqlite_autoincrement=True) table.create() metadata1 = MetaData(engine1) print Table(user, metadata2, autoload=True) print Table(user, metadata1, autoload=True) As you can see, I create the table 'user' from engine2 and then I try to load it both from engine1 and engine2. The try-except part do some clean up and it is aimed only to make the script repeatable. If you run the code you'll see that the first print statement is executed correctly, while the second one raises a NoSuchTableError exception. It seems to be connected with some flushing issue, but I don't know what I am doing wrong. Any suggestion? Thanks in advance. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] pyodbc + FreeTDS segfault?? on linux
On Apr 7, 2011, at 12:46 AM, Randy Syring wrote: I am running Ubuntu 10.04, python 2.6.5, SA 0.6.6, latest pyodbc release. I have tried FreeTDS that ships with the distro (0.82) as well as current CVS. I can make a connection and issue a basic SQL statement. However, when I try to run my unit tests, I get the following error: *** glibc detected *** /path/to/venv/bin/python: free(): invalid next size (fast): 0x02527bf0 *** nothing ive seen before with freetds (and I use freetds a lot) - so your steps would be to isolate the problem into something reproducible, then ask on the FreeTDS or possibly pyodbc lists, possibly first converting it into a straight pyodbc script so there are at least fewer layers of abstraction at play. The SQL issued just before that error is: 2011-04-07 00:26:39,257 INFO sqlalchemy.engine.base.Engine.0x...0350 INSERT INTO corporate_sessions (createdts, updatedts, id, hits, relatedip, user_id) VALUES (?, ?, ?, ?, ?, ?) 2011-04-07 00:26:39,257 INFO sqlalchemy.engine.base.Engine.0x...0350 (datetime.datetime(2011, 4, 7, 0, 26, 39, 257073), None, u'e1457896AdZgRjqg8CD8', 0, '10.66.3.230', 1) and then the process just hangs and I have to kill the process. My unit tests run successfully against sqlite, postgresql, and MSSQL on Windows. I have successfully ran the following test script on with the same libraries and virtualenv: import datetime import sqlalchemy as sa eng = sa.create_engine(mssql://user:pass@server/temp? Port=1435TDS_Version=8.0,echo=True) res = eng.execute('select 1+1 as foo') for row in res: print 'answer=',row['foo'] #eng.execute('DROP TABLE satest') #eng.execute('CREATE TABLE satest(id INT, name VARCHAR(300), ts datetime)') res = eng.execute('INSERT INTO satest(id, name, ts) VALUES (?, ?, ?)', (10, u'foobar', datetime.datetime(2011, 4, 7, 0, 26, 39, 257073))) One last thing, when I first tested this, I got an error related to using an ORM instance when it had already been deleted (or something like that, I can't remember exactly). But I haven't seen that error for a long time and don't remember doing anything in particular to change it. -- 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] Sqlalchemy+sqlite3 autoload problem
On Apr 7, 2011, at 9:35 AM, Massi wrote: Hi everyone, I'm writing a script using sqlalchemy 0.66 and sqlite3. I'm encountering a problem trying to create and load a table from two different engines. Here is an example script showing the problem: from sqlalchemy import * engine1 = create_engine(sqlite:///test.db, echo=False) metadata1 = MetaData(engine1) try : table = Table(user, metadata1, autoload=True) table.drop() except : print Not found engine2 = create_engine(sqlite:///test.db, echo=False) metadata2 = MetaData(engine2) table = Table(user, metadata2, Column('id', Integer, primary_key=True), Column('name', String), Column('password', String), sqlite_autoincrement=True) table.create() metadata1 = MetaData(engine1) print Table(user, metadata2, autoload=True) print Table(user, metadata1, autoload=True) As you can see, I create the table 'user' from engine2 and then I try to load it both from engine1 and engine2. The try-except part do some clean up and it is aimed only to make the script repeatable. If you run the code you'll see that the first print statement is executed correctly, while the second one raises a NoSuchTableError exception. It seems to be connected with some flushing issue, but I don't know what I am doing wrong. Any suggestion? Thanks in advance. its been observed that SQLite doesn't refresh the pragma information regarding tables once a connection is made. So switch to NullPool or create the engine after tables are created. -- 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] [None vs. NaN] Is there a Database independent way with SQLAlchemy to query filtered by “None”/“NaN”?
On Apr 7, 2011, at 1:25 AM, Philipp Rautenberg wrote: The following code is DB specific: import sqlalchemy # ... ergebnis = session.query( my_object.attr1).filter(sa.and_( my_object.attr2 != 'NaN')).all() # PostgreSQL # my_object.attr2 != None)).all() # sQLite With PostgreSQL it is 'NaN', with SQLite None (without single quotes). Is there a SQLAlchemy-way to do this backend independant? from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import ColumnElement from sqlalchemy.types import NULLTYPE class Nan(ColumnElement): type = NULLTYPE @compiles(Nan, postgresql) def pg_nan(elem, compiler, **kw): return NaN @compiles(Nan, sqlite) def sl_nan(elem, compiler, **kw): return None from sqlalchemy import select from sqlalchemy.dialects import postgresql, sqlite print select([one, two, three]).where(one != Nan()).compile(dialect=postgresql.dialect()) print select([one, two, three]).where(one != Nan()).compile(dialect=sqlite.dialect()) -- 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] ObjectDeletedError when query.delete() hits an expired item
Hi. With SQLA 0.6.6, the program below fails on the last line with ObjectDeletedError. Is this expected or a bug? from sqlalchemy import create_engine, MetaData, Column, Unicode from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite:///:memory:') metadata = MetaData(bind = engine) session = sessionmaker(bind = engine)() Base = declarative_base(metadata = metadata) class Entity(Base): __tablename__ = 'entity' name = Column(Unicode(128), primary_key = True) metadata.create_all() e = Entity(name = u'hello') session.add(e) session.flush() session.expire(e) session.query(Entity).filter_by(name = u'hello').delete() -- 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] Problem with SAWarning: Multiple rows returned with uselist=False
Hi, in part of my model I have a TaskIntro item and RedirectRule item connected with relation one-to-one one-sided, mapper for TaskIntro looks like: mapper( TaskIntro, table, version_id_col = table.c.version , properties={ ... 'redirect_rule': relation( RedirectRule, cascade=all, delete, primaryjoin=table.c.redirect_rule_id == redirect_rule_t.c.id ), ... } and when I call method that get TaskIntro defined: def _get_all_query(self, **kwargs): query = self.query.options( contains_eager('redirect_rule'), eagerload_all('redirect_rule.periods'), eagerload('redirect_rule.channels'), ... ) return query I receive following warning: /usr/lib/python2.6/dist-packages/sqlalchemy/orm/mapper.py:2113: SAWarning: Multiple rows returned with uselist=False for eagerly- loaded attribute 'TaskIntro.redirect_rule' populator(state, dict_, row) I'm googling about this warning, but have no idea what this can mean. How can multiple rows be returned here? What is interesting, this following warning appears in SQLAlchemy 0.6.3-2, but on earlier version doesn't. -- 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] ObjectDeletedError when query.delete() hits an expired item
it is a bug and ticket 2122 has the fix for this. However I'd like to target this at 0.7 since it rearranges things in update()/delete() significantly and I'd like to add test coverage for all the changes that have been made. The workaround for 0.6 is to pass False or fetch to the delete() method so that the in-session evaluation isn't called on expired objects. On Apr 7, 2011, at 10:33 AM, Bobby Impollonia wrote: Hi. With SQLA 0.6.6, the program below fails on the last line with ObjectDeletedError. Is this expected or a bug? from sqlalchemy import create_engine, MetaData, Column, Unicode from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite:///:memory:') metadata = MetaData(bind = engine) session = sessionmaker(bind = engine)() Base = declarative_base(metadata = metadata) class Entity(Base): __tablename__ = 'entity' name = Column(Unicode(128), primary_key = True) metadata.create_all() e = Entity(name = u'hello') session.add(e) session.flush() session.expire(e) session.query(Entity).filter_by(name = u'hello').delete() -- 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] Problem with SAWarning: Multiple rows returned with uselist=False
On Apr 7, 2011, at 11:07 AM, Aleksander Siewierski wrote: Hi, in part of my model I have a TaskIntro item and RedirectRule item connected with relation one-to-one one-sided, mapper for TaskIntro looks like: mapper( TaskIntro, table, version_id_col = table.c.version , properties={ ... 'redirect_rule': relation( RedirectRule, cascade=all, delete, primaryjoin=table.c.redirect_rule_id == redirect_rule_t.c.id ), ... } and when I call method that get TaskIntro defined: def _get_all_query(self, **kwargs): query = self.query.options( contains_eager('redirect_rule'), eagerload_all('redirect_rule.periods'), eagerload('redirect_rule.channels'), ... ) return query I receive following warning: /usr/lib/python2.6/dist-packages/sqlalchemy/orm/mapper.py:2113: SAWarning: Multiple rows returned with uselist=False for eagerly- loaded attribute 'TaskIntro.redirect_rule' populator(state, dict_, row) I'm googling about this warning, but have no idea what this can mean. How can multiple rows be returned here? You didn't put a full example or the query you're using so its impossible to give a specific answer. But it means the way you're joining from TaskIntro to RedirectRule in your query is incorrect such that multiple RedirectRule rows are being returned corresponding to a single TaskIntro identity. -- 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: when is object.id initialized
OK, thanks, this was part of the ActiveRecord kind of approach I was playing with, which after reading your article at zzzeek and the alternative described there I will probably shelve. On Apr 6, 9:59 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 6, 2011, at 6:38 AM, farcat wrote: Thank you, I now experiment with putting session.add and session.flush in object.__init__ .. Are there any general disadvantages of that approach? Using add() inside of __init__ is somewhat common. Using flush() has the significant downside that flushes occur too often which is wasteful and performs poorly for larger scale operations (like bulk loads and such). The ORM is designed such that primary key identifiers are not needed to be explicitly accessed outside of a flush except for query situations that wish to avoid the usage of relationships. When you build your application to be strongly dependent on primary key identifiers being available within otherwise fully pending object graphs, you begin to work against the usage goals of the ORM. On Apr 3, 7:44 pm, Michael Bayer mike...@zzzcomputing.com wrote: Integer primary key identifiers are generated by the database itself using a variety of techniques which are all database-dependent. This process occurs when the session flushes. If you read the object relational tutorial starting athttp://www.sqlalchemy.org/docs/orm/tutorial.html#setting-up-the-mappi...working down through the end ofhttp://www.sqlalchemy.org/docs/orm/tutorial.html#adding-new-objectsyouwill see that this interaction is described. You can of course set .id to any value you'd like and that will be the value used when the flush happens. On Apr 3, 2011, at 1:09 PM, farcat wrote: Hi all, I use a kind of dynamic reference from parent_table to other tables. For that parent_table uses columns table_name and a record_id. This makes it possible to have a reference from parent_table to any record in any table in the database. However, say that i want to reference a record of table_name, i need the record.id to initialize parent_table.record_id. However, when i create a record and session.add it to the database, record.id == None. I was wondering when and how record.id is initialized and how it can be forced. Cheers, Lars -- 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 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: how to delete record (special case)
Hi Michael, I am trying to run the alternative you described in the article, but the following code is most likely from an old version of SA a don't know how to update (I am working with 6.6): mapper = class_mapper(cls) table = mapper.local_table mapper.add_property(attr_name, relationship(GenericAssoc, backref=backref('_backref_%s' % table.name, uselist=False))) class_mapper is unknown or moved. What does it do/how can I fix this? Cheers, Lars On Apr 6, 10:18 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 6, 2011, at 5:43 AM, farcat wrote: Hello, I am experimenting with a pattern where records hold the table name and record id of the next record in any other table, chaining records in different tables. This works, but I can't figure out how to clean op references to the next record in another table when I delete a record (the pattern does not use foreign keys in the normal sense). .. in that it doesn't use foreign keys. Since you're working against the relational database's supported patterns, you'd need to roll the deletion of related rows yourself. The pattern is also called a polymorphic association and I blogged about it years ago here: http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-s The code is: = from sqlalchemy import * from sqlalchemy.orm.session import sessionmaker from sqlalchemy.ext.declarative import declarative_base, declared_attr, DeclarativeMeta #-- - Base = declarative_base() reg = dict() engine = create_engine('sqlite:///:memory:', echo=False) Session = sessionmaker(bind = engine) #-- - class chainmeta(DeclarativeMeta): #-- - class Base(object): session = Session() @declared_attr def __tablename__(cls): return cls.__name__ id = Column(Integer, primary_key = True) next_table = Column(String(64)) next_id = Column(Integer) #in table with name stored in next_table! def __init__(self, data, next = None): self.data = data self.prev = None self.next = next self.session.add(self) self.session.flush() def _getnext(self): if self.next_table and self.next_id: return self.session.query(reg[self.next_table]).filter(self.next_id == reg[self.next_table].id).one() else: return None def _setnext(self, next): if next: if self.next: self.next.prev = None self.next_table = next.__tablename__ self.next_id = next.id next.prev = self elif self.next: self.next.prev = None self.next_table = None self.next_id = None def _delnext(self): self.next.prev = None self.next_table = None self.next_id = None next = property(_getnext, _setnext, _delnext) def __repr__(self): out = type: + type(self).__name__ + [ for name in self.__dict__: out += name + , out += ] return out #-- - def __new__(mcls, name, coltype): return DeclarativeMeta.__new__(mcls, name, (chainmeta.Base, Base),{data: Column(coltype, nullable = False)}) def __init__(cls, name, coltype): reg[name] = cls return DeclarativeMeta.__init__(cls, name, (chainmeta.Base, Base),{}) #-- - if __name__ == '__main__': Base.metadata.drop_all(engine) session = chainmeta.Base.session = Session() Ni = chainmeta(Ni, Integer) Nb = chainmeta(Nb, Boolean) Nt = chainmeta(Nt, String(200)) Base.metadata.create_all(engine) ni1 = Ni(5) ni2 = Ni(12) nb1 = Nb(True) nb2 = Nb(False) nt1 = Nt(text in nt1) nt2 = Nt(text in nt2) ni1.next = ni2 ni2.next = nb1 nb1.next = nb2 nb2.next = nt1 nt1.next = nt2 nt2.next = ni1 #circular print OBJECTS n = ni1 count = 0 print nexts: . while n and count 10: print n.data count += 1 n = n.next n = ni1 count = 0 print prevs: . while n and count 10: print n.data count += 1 n = n.prev print -- - nts = session.query(Nt).all() print QUERIES
[sqlalchemy] multiple inheritance experiment
Hello, I am pretty determined to find a way to get (a simplified version of) multiple inheritance working with SA. The simplification lies in that no overriding of attributes will be possible (but I don't know whether that is significant). I was thinking of a schema as follows: -- metadata = MetaData() base1_table = Table(base1_table, metadata, Column('id', Integer, primary_key=True), Column('str', String) ) base2_table = Table(base2_table, metadata, Column('id', Integer, primary_key=True), Column('int', Integer) ) claz1_table = Table(claz1_table, metadata, Column('id', Integer, primary_key=True), Column('base1_id', None, ForeignKey('base1_table.id')), Column('base2_id', None, ForeignKey('base2_table.id')), Column('assoc_id', None, ForeignKey('assoc_table.id')) ) assoc_table = Table(assoc_table, metadata, Column('id', Integer, primary_key=True), Column('name', String(50), nullable=False), Column('type', String(50), nullable=False) ) base3_table = Table(base3_table, metadata, Column('id', Integer, primary_key=True), Column('assoc_id', None, ForeignKey('assoc_table.id')), Column('bool', Boolean) ) claz2_table = Table(claz2_table, metadata, Column('id', Integer, primary_key=True), Column('base3_id', None, ForeignKey('base3_table.id')), Column('date', Date) ) class base1(object): pass class base2(object): pass class base3(object): pass class claz1(base1, base2): pass class claz2(base3): pass # do mappings, relationships and e.g. be able to c1 = claz1(str = hello, int = 17) setattr(c1, name, claz2(bool = True, date = Date(2010,9,10))) - I am still pretty new to SA. Can anyone give me any hints, tips, issues with this scheme (e.g. about how to do the mappings, descriptors, etc)? The step after will be to write factory functions/metaclasses to generate these dynamically. Multiple inheritance is very important for my use case. Cheers, Lars -- 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] multiple inheritance experiment
On Apr 7, 2011, at 2:30 PM, Lars wrote: Hello, I am pretty determined to find a way to get (a simplified version of) multiple inheritance working with SA. The simplification lies in that no overriding of attributes will be possible (but I don't know whether that is significant). I was thinking of a schema as follows: -- metadata = MetaData() base1_table = Table(base1_table, metadata, Column('id', Integer, primary_key=True), Column('str', String) ) base2_table = Table(base2_table, metadata, Column('id', Integer, primary_key=True), Column('int', Integer) ) claz1_table = Table(claz1_table, metadata, Column('id', Integer, primary_key=True), Column('base1_id', None, ForeignKey('base1_table.id')), Column('base2_id', None, ForeignKey('base2_table.id')), Column('assoc_id', None, ForeignKey('assoc_table.id')) ) assoc_table = Table(assoc_table, metadata, Column('id', Integer, primary_key=True), Column('name', String(50), nullable=False), Column('type', String(50), nullable=False) ) base3_table = Table(base3_table, metadata, Column('id', Integer, primary_key=True), Column('assoc_id', None, ForeignKey('assoc_table.id')), Column('bool', Boolean) ) claz2_table = Table(claz2_table, metadata, Column('id', Integer, primary_key=True), Column('base3_id', None, ForeignKey('base3_table.id')), Column('date', Date) ) class base1(object): pass class base2(object): pass class base3(object): pass class claz1(base1, base2): pass class claz2(base3): pass # do mappings, relationships and e.g. be able to c1 = claz1(str = hello, int = 17) setattr(c1, name, claz2(bool = True, date = Date(2010,9,10))) You can just forego the inherits flag and map each class to the appropriate join or base table. claz1 would be mapped to a join of the three tables involved.The difficult part of course is the query side, if you're looking to query base1 or base2 and get back claz1 objects. Alternatively, each class can be mapped to one table only, and relationship() used to link to other tables. Again if you don't use the inherits flag, you can maintain the class hierarchy on the Python side and use association proxies to provide local access to attributes that are normally on the related class. This would still not give you polymorphic loading but would grant a little more flexibility in which tables are queried to start. - I am still pretty new to SA. Can anyone give me any hints, tips, issues with this scheme (e.g. about how to do the mappings, descriptors, etc)? The step after will be to write factory functions/metaclasses to generate these dynamically. Multiple inheritance is very important for my use case. Cheers, Lars -- 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: pyodbc + FreeTDS segfault?? on linux
Seems to be a unicode conversion problem, if you are interested in following, the pyodbc issue with very small test case is here: http://code.google.com/p/pyodbc/issues/detail?id=170 On Apr 7, 9:37 am, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 7, 2011, at 12:46 AM, Randy Syring wrote: I am running Ubuntu 10.04, python 2.6.5, SA 0.6.6, latest pyodbc release. I have tried FreeTDS that ships with the distro (0.82) as well as current CVS. I can make a connection and issue a basic SQL statement. However, when I try to run my unit tests, I get the following error: *** glibc detected *** /path/to/venv/bin/python: free(): invalid next size (fast): 0x02527bf0 *** nothing ive seen before with freetds (and I use freetds a lot) - so your steps would be to isolate the problem into something reproducible, then ask on the FreeTDS or possibly pyodbc lists, possibly first converting it into a straight pyodbc script so there are at least fewer layers of abstraction at play. The SQL issued just before that error is: 2011-04-07 00:26:39,257 INFO sqlalchemy.engine.base.Engine.0x...0350 INSERT INTO corporate_sessions (createdts, updatedts, id, hits, relatedip, user_id) VALUES (?, ?, ?, ?, ?, ?) 2011-04-07 00:26:39,257 INFO sqlalchemy.engine.base.Engine.0x...0350 (datetime.datetime(2011, 4, 7, 0, 26, 39, 257073), None, u'e1457896AdZgRjqg8CD8', 0, '10.66.3.230', 1) and then the process just hangs and I have to kill the process. My unit tests run successfully against sqlite, postgresql, and MSSQL on Windows. I have successfully ran the following test script on with the same libraries and virtualenv: import datetime import sqlalchemy as sa eng = sa.create_engine(mssql://user:pass@server/temp? Port=1435TDS_Version=8.0,echo=True) res = eng.execute('select 1+1 as foo') for row in res: print 'answer=',row['foo'] #eng.execute('DROP TABLE satest') #eng.execute('CREATE TABLE satest(id INT, name VARCHAR(300), ts datetime)') res = eng.execute('INSERT INTO satest(id, name, ts) VALUES (?, ?, ?)', (10, u'foobar', datetime.datetime(2011, 4, 7, 0, 26, 39, 257073))) One last thing, when I first tested this, I got an error related to using an ORM instance when it had already been deleted (or something like that, I can't remember exactly). But I haven't seen that error for a long time and don't remember doing anything in particular to change it. -- 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] Avoiding spaghetti inheritance
Hello there. I'm using SA-0.7 to develop an application that should help me manage my company's services. A central concept here is the order, it could be a service (like one year of web hosting) or a physical item (like a pc we sell). So far I generalized them in two classes: the Order and the ServiceOrder - the latter simply inherits the former and adds start and end dates. Now I need to add all various kinds of metadata to orders, for example: - a ServiceOrder for a domain hosting should contain the domain name - a ServiceOrder for a maintenance service should contain the service level for that service (say, basic or advanced) - an Order for a PC we delivered should contain its serial number - and so on... I could easily add child classes, but that would mean to keep and maintain that code forever even after we stop using it (ie, next year we stop doing hosting) or when it's not really useful (many things will just have some 'metadata' in them like a serial number or similar things). I'd also like to avoid having to add code every time we just hit something slightly different to manage, when we just have some additional data to keep track of. I wonder what could be an intelligent approach to such a situation. One idea I got could be to add an 'OrderTags' table / class that would associate (tag, value) tuples to my orders, and somehow access them like a dictionary (eg. Order.tags['serialnumber'] = 'foo' or ServiceOrder.tags['domainname'] = 'example.com'). But that will probably keep them out of standard SA queries? For example, if I want to retrieve the full history of a domain we're hosting, how could I query for all orders with (tags['domainname'] == something)? I'm looking for advice on how to structure this data, and how to best implement it with python and sqlalchemy-0.7. Thank you, Luca -- 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] DetachedInstanceError when calling __repr__
Hi, I have an objects that when I convert to a string using __repr__ throws a DetachedInstanceError. If I access any of their members or test the session (using 'user in Session') everything is fine but if I check 'self in Session' in __repr__ the result is False. I can reattach it to the Session using merge, inside the __repr__ call, but every time I call repr I get the same problem. I'm using PostgreSQL 8.4 and SqlAlchemy 0.6.4 and I'm using Pylons 1.0. I have a nested transaction (using Session.begin_nested) that is updating objects that are attached to a User object but that don't change the user object itself but the problem is seen in other, unrelated objects. Other than the nested transaction there are no other flushes or commits. Does anybody have any idea what is going on? Thanks, Ben -- 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] DetachedInstanceError when calling __repr__
On Apr 7, 2011, at 9:54 PM, BenH wrote: Hi, I have an objects that when I convert to a string using __repr__ throws a DetachedInstanceError. If I access any of their members or test the session (using 'user in Session') everything is fine but if I check 'self in Session' in __repr__ the result is False. I can reattach it to the Session using merge, inside the __repr__ call, but every time I call repr I get the same problem. I'm using PostgreSQL 8.4 and SqlAlchemy 0.6.4 and I'm using Pylons 1.0. I have a nested transaction (using Session.begin_nested) that is updating objects that are attached to a User object but that don't change the user object itself but the problem is seen in other, unrelated objects. Other than the nested transaction there are no other flushes or commits. Does anybody have any idea what is going on? if you're doing any session.close() or session.expunge(), whatever references you have left after that aren't in the session. that's the only way you can end up with detachment errors. also, pulling objects in and out of caches, across processes with pickle, stuff like that, can introduce detached objects. merge() doesn't reattach objects either it returns a new object that is associated with the session. the one you send stays detached (if it was already). Thanks, Ben -- 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.