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.
