Re: [sqlalchemy] overriding base constructor to allow non-column named keys in the constructor dict
2014-12-11 14:29 GMT-03:00 dewey de...@pathoz.com: Oh, I guess I was completely mis-reading the docs You are saying I just teach the base class to use my own function (exclusively) for __init__ and I totally don't need to worry about calling the sqlalchemy internal (superclass) __init__ method AFAIK that is right. Bear in mind though that this will only apply to newly created instances. the ones already stored in the db and retrieved via .query will ignore your __init__ and use SQLA internal mechanisms to populate their values. On Thursday, December 11, 2014 10:45:00 AM UTC-6, Mariano Mara wrote: 2014-12-11 13:35 GMT-03:00 dewey de...@pathoz.com: I'm importing lots of text files, using csv.dictReader I then create model instances and store the recs in the DB One row from the csv file might represent a child-row, plus 4-5 parents (using get_or_create for these) It would be highly convenient for me if I could disable the safety mechanism that prevents a constructor dict from containing keys belonging to other tables. I'd rather not have to split my imported row-dictionary into 5-6 other dictionaries before calling each constructor In other words, I'd like to override __init__ on the base (or 1st mixin) constructor to allow (and ignore) non-column named keys in the dict passed as 1st constructor param Can someone show me how to do this?? I'm using declarative and I'm not sure if this helps, but all of my model objects have a __prefix__ class var that tells me the first 4 chars of my column names. So, all of my column (except the pkey and modification_dttm which are declared in the base class I've not figured out how to name them dynamicaly at class construction), names begin with a consistent prefix. I'd love to learn how to do dynamic naming of columns from an inherited parent class also but that's a separate post For example: Class: Customer Table: cus_customers Cols:cus_first, cus_last, cus_dob -- Check the constructor parameter for declarative_base, that should do the trick: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html?highlight=declarative_base#sqlalchemy.ext.declarative.declarative_base -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] is there a way to customize the names of declarative_base or mixin columns at class creation time?
2014-12-11 17:28 GMT-03:00 dewey de...@pathoz.com: Oh doh...that is perfect!!! Except for now, I either need to merge my __mapper_args__ from each table to my base __mapper_args__ Or is there a way to add to __mapper_args__ in the base DYNAMICALLY based on some property in the class? That would mean I could automate the whole process.. You decorate your __mapper_args__ with declared_attr (see http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#sqlalchemy.ext.declarative.declared_attr) and build your required __mapper_args__ according to the class you are in (see the second example in the docs I just linked). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] NVARCHAR doubt
Hi there. I have some code that works ok in PG and now I need to make it
work with Oracle, however I am hitting this error with NVARCHAR and I am
not sure how to handle it.
First a small code in order you can reproduce it (as you can see name has
been defined as UnicodeText):
from sqlalchemy import create_engine, Integer, UnicodeText, Column, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('oracle://scott:tiger@localhost:1521/xe', echo=True)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(UnicodeText)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
qry = session.query(func.count('1'), User.name).group_by(User.name)
print(qry.all())
but each time I get to the actually execution of the query I hit the same
error (see below, it is related to the use of an UnicodeText field) and I
wonder if there is a way to avoid the issue transparently.
TIA,
Mariano
2014-09-30 16:48:12,028 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-09-30 16:48:12,029 INFO sqlalchemy.engine.base.Engine SELECT
count(:param_1) AS count_1, users.name AS users_name
FROM users GROUP BY users.name
2014-09-30 16:48:12,030 INFO sqlalchemy.engine.base.Engine {'param_1': '1'}
Traceback (most recent call last):
File stdin, line 1, in module
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
line 2300, in all
return list(self)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
line 2412, in __iter__
return self._execute_and_instances(context)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
line 2427, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 729, in execute
return meth(self, multiparams, params)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/sql/elements.py,
line 321, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 826, in _execute_clauseelement
compiled_sql, distilled_params
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 958, in _execute_context
context)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 1160, in _handle_dbapi_exception
exc_info
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/util/compat.py,
line 199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 951, in _execute_context
context)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py,
line 436, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00932: inconsistent
datatypes: expected - got NCLOB
'SELECT count(:param_1) AS count_1, users.name AS users_name \nFROM users
GROUP BY users.name' {'param_1': '1'}
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: NVARCHAR doubt
Sorry, I meant NCLOB, not NVARCHAR.
2014-09-30 16:57 GMT-03:00 Mariano Mara mariano.m...@gmail.com:
Hi there. I have some code that works ok in PG and now I need to make it
work with Oracle, however I am hitting this error with NVARCHAR and I am
not sure how to handle it.
First a small code in order you can reproduce it (as you can see name
has been defined as UnicodeText):
from sqlalchemy import create_engine, Integer, UnicodeText, Column, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('oracle://scott:tiger@localhost:1521/xe',
echo=True)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(UnicodeText)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
qry = session.query(func.count('1'), User.name).group_by(User.name)
print(qry.all())
but each time I get to the actually execution of the query I hit the same
error (see below, it is related to the use of an UnicodeText field) and I
wonder if there is a way to avoid the issue transparently.
TIA,
Mariano
2014-09-30 16:48:12,028 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-09-30 16:48:12,029 INFO sqlalchemy.engine.base.Engine SELECT
count(:param_1) AS count_1, users.name AS users_name
FROM users GROUP BY users.name
2014-09-30 16:48:12,030 INFO sqlalchemy.engine.base.Engine {'param_1': '1'}
Traceback (most recent call last):
File stdin, line 1, in module
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
line 2300, in all
return list(self)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
line 2412, in __iter__
return self._execute_and_instances(context)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
line 2427, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 729, in execute
return meth(self, multiparams, params)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/sql/elements.py,
line 321, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 826, in _execute_clauseelement
compiled_sql, distilled_params
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 958, in _execute_context
context)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 1160, in _handle_dbapi_exception
exc_info
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/util/compat.py,
line 199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 951, in _execute_context
context)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py,
line 436, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00932: inconsistent
datatypes: expected - got NCLOB
'SELECT count(:param_1) AS count_1, users.name AS users_name \nFROM
users GROUP BY users.name' {'param_1': '1'}
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: NVARCHAR doubt
I just noticed that the error code comes from oracle directly and it is not
sqlalchemy related. Looks like the best way is not to use an unicodetext
field (if I plan to use to to group results). Probably I failed to read
such limitation in oracle's doc.
Sorry for the inconvenience.
2014-09-30 17:06 GMT-03:00 Mariano Mara mariano.m...@gmail.com:
Sorry, I meant NCLOB, not NVARCHAR.
2014-09-30 16:57 GMT-03:00 Mariano Mara mariano.m...@gmail.com:
Hi there. I have some code that works ok in PG and now I need to make it
work with Oracle, however I am hitting this error with NVARCHAR and I am
not sure how to handle it.
First a small code in order you can reproduce it (as you can see name
has been defined as UnicodeText):
from sqlalchemy import create_engine, Integer, UnicodeText, Column, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('oracle://scott:tiger@localhost:1521/xe',
echo=True)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(UnicodeText)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
qry = session.query(func.count('1'), User.name).group_by(User.name)
print(qry.all())
but each time I get to the actually execution of the query I hit the same
error (see below, it is related to the use of an UnicodeText field) and I
wonder if there is a way to avoid the issue transparently.
TIA,
Mariano
2014-09-30 16:48:12,028 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2014-09-30 16:48:12,029 INFO sqlalchemy.engine.base.Engine SELECT
count(:param_1) AS count_1, users.name AS users_name
FROM users GROUP BY users.name
2014-09-30 16:48:12,030 INFO sqlalchemy.engine.base.Engine {'param_1':
'1'}
Traceback (most recent call last):
File stdin, line 1, in module
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
line 2300, in all
return list(self)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
line 2412, in __iter__
return self._execute_and_instances(context)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py,
line 2427, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 729, in execute
return meth(self, multiparams, params)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/sql/elements.py,
line 321, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 826, in _execute_clauseelement
compiled_sql, distilled_params
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 958, in _execute_context
context)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 1160, in _handle_dbapi_exception
exc_info
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/util/compat.py,
line 199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 951, in _execute_context
context)
File
/home/mariano/Code/env/local/lib/python2.7/site-packages/SQLAlchemy-0.9.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py,
line 436, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00932: inconsistent
datatypes: expected - got NCLOB
'SELECT count(:param_1) AS count_1, users.name AS users_name \nFROM
users GROUP BY users.name' {'param_1': '1'}
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Update instead of delete on collection
Hi all Each time my Parent class is edited and one of the elements from its Child relationship is removed I want to invalidate this element (e.g. element.valid=False) instead of actually performing the delete DML instruction. How can I achieve that? I have created a remove event listener and I see the value to be removed from the target but so far I have been unable to find a way to successfully stop the delete event (I tried with EXP_STOP to no avail). Any ideas? TIA, Mariano -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Update instead of delete on collection
2014-07-15 14:47 GMT-03:00 Michael Bayer mike...@zzzcomputing.com: On Jul 15, 2014, at 1:27 PM, Mariano Mara mariano.m...@gmail.com wrote: Hi all Each time my Parent class is edited and one of the elements from its Child relationship is removed I want to invalidate this element (e.g. element.valid=False) instead of actually performing the delete DML instruction. How can I achieve that? the foreign key has to be nullable, then you make sure delete-orphan cascade is not turned on, then you use a collection remove event to mark this flag as False when it is removed from the collection: http://docs.sqlalchemy.org/en/rel_0_9/orm/events.html#sqlalchemy.orm.events.AttributeEvents.remove I have created a remove event listener and I see the value to be removed from the target but so far I have been unable to find a way to successfully stop the delete event (I tried with EXP_STOP to no avail). just make sure delete, delete-orphan cascade are not set. It will set the foreign key column(s) to NULL. But actually I don't want to lose track of the relation between parent and child. Of course, the easiest solution would be to perform the update in the ORM step but I have to use a common class to handle all ORM actions and I have to easy easy way to hook my own process in there. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Relationship issue with composite foreign key
Hi everyone, I am hitting an error while defining a relationship with a
parent table with a composite primary key (my child class has two
attributes that point to the same parent class -with a composite pk- but
both relationships are properly defined with the 'foreign_keys'
parameter): I am not sure the reason I get an AmbiguousForeignKeysError,
I have followed the docs and tried several alternatives to no avail.
Probably some typo or a silly mistake like that but I am unable to see
it. I would appreciate a pointer.
first, here is the error message I am getting:
File
/home/mariano/Code/n-p/env/lib/python3.3/site-packages/SQLAlchemy-0.9.2-py3.3-linux-i686.egg/sqlalchemy/orm/relationships.py,
line 1868, in _determine_joins
% self.prop)
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
condition between parent/child tables on relationship Bet.runner - there
are multiple foreign key paths linking the tables. Specify the
'foreign_keys' argument, providing a list of those columns which should
be counted as containing a foreign key reference to the parent table.
and here is a completed self contained example to reproduce the problem:
from sqlalchemy import (
Table, Column, ForeignKey, Index, CheckConstraint,
ForeignKeyConstraint,
Integer, Text, String, Boolean, Date, Enum, Numeric,
inspect, create_engine,
and_
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
scoped_session,
sessionmaker,
relationship
)
DBSession = scoped_session(sessionmaker())
Base = declarative_base()
class User(Base):
__tablename__ = user
uid = Column(String(600), primary_key=True)
oid = Column(String(100), primary_key=True)
name = Column(String(600), nullable=False)
lastname = Column(String(600), nullable=False)
class Bet(Base):
__tablename__ = bet
__table_args__ = (
ForeignKeyConstraint((uid, oid), (user.uid, user.oid)),
ForeignKeyConstraint((loader_uid, oid), (user.uid,
user.oid)),
)
id = Column(Integer, autoincrement=True, primary_key=True)
uid = Column(String(600), nullable=False)
oid = Column(String(100), nullable=False)
number = Column(Integer, CheckConstraint(number1))
loader_uid = Column(String(600), nullable=False)
# relationships
runner = relationship(User, foreign_keys=[uid, oid],
#primaryjoin=and_(Bet.uid==User.uid,
Bet.oid==User.oid),
)
loader = relationship(User, foreign_keys=[loader_uid, oid])
Index(runner_idx, Bet.__table__.c.uid, Bet.__table__.c.oid)
if __name__ == __main__:
engine = create_engine('sqlite://')
DBSession.configure(bind=engine)
Base.metadata.create_all(engine)
u1 = User(uid='example1', oid='MDQ', name='Mariano', lastname='Mara')
u2 = User(uid='example2', oid='MDQ', name='Mariano', lastname='Mara')
DBSession.add_all([u1, u2])
DBSession.flush()
b = Bet(uid='example1', oid='MDQ', number=20, loader_uid='example2')
DBSession.add(b)
DBSession.commit()
Thanks in advance
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Relationship issue with composite foreign key
On 19/02/14 15:28, Michael Bayer wrote: On Feb 19, 2014, at 1:14 PM, Mariano Mara mariano.m...@gmail.com wrote: # relationships runner = relationship(User, foreign_keys=[uid, oid], #primaryjoin=and_(Bet.uid==User.uid, Bet.oid==User.oid), ) loader = relationship(User, foreign_keys=[loader_uid, oid]) you've got the quoting off on that commented-out primaryjoin, here's the most direct way to set it up: runner = relationship(User, primaryjoin=and_(foreign(Bet.uid)==User.uid, foreign(Bet.oid)==User.oid), ) loader = relationship(User, primaryjoin=and_(foreign(Bet.loader_uid)==User.uid, foreign(Bet.oid)==User.oid), ) I have tried with the primaryjoin too (although not including the foreign()) and I hit a different error. Your suggestion -of course- works. I am confuse now: when do I use the primaryjoin and/or foreign_keys and/or foreign? Thanks in advance! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Relationship issue with composite foreign key
On 19/02/14 16:01, Michael Bayer wrote: On Feb 19, 2014, at 1:34 PM, Mariano Mara mariano.m...@gmail.com wrote: On 19/02/14 15:28, Michael Bayer wrote: On Feb 19, 2014, at 1:14 PM, Mariano Mara mariano.m...@gmail.com wrote: # relationships runner = relationship(User, foreign_keys=[uid, oid], #primaryjoin=and_(Bet.uid==User.uid, Bet.oid==User.oid), ) loader = relationship(User, foreign_keys=[loader_uid, oid]) you've got the quoting off on that commented-out primaryjoin, here's the most direct way to set it up: runner = relationship(User, primaryjoin=and_(foreign(Bet.uid)==User.uid, foreign(Bet.oid)==User.oid), ) loader = relationship(User, primaryjoin=and_(foreign(Bet.loader_uid)==User.uid, foreign(Bet.oid)==User.oid), ) I have tried with the primaryjoin too (although not including the foreign()) and I hit a different error. Your suggestion -of course- works. I am confuse now: when do I use the primaryjoin and/or foreign_keys and/or foreign? well these approaches are largely interchangeable in the vast majority of cases. your schema here is actually throwing two curveballs at once, one is the usual more than one foreign key constraint problem, but the other is that two of these constraints actually share the same column. For example I can also set foreign_keys=[loader_uid] / foreign_keys=[uid], and it configures, because the 'oid' column isn't there to confuse things, but then oid isn't taken into account. Really, SQLA's here's how i figure things out based on foreign key logic is not quite ready for that specific set of inputs. I'll add a bug report for it. the approach of primaryjoin with foreign() is the most specific method which leaves the least amount of questions for SQLAlchemy to figure out. Crystal clear. Thanks for your answer and time. I really appreciate it. Mariano -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Expected behaviour for Oracle's lost contact
Hi there, in one application I am using sqlalchemy 0.7.8 against Oracle 11 release 1 and I am hitting Oracle's lost contact more times than I would like to. Since the app seems unable to recover from the error, I have to restart it completely (it is a pyramid app). According to this changeset[1], the error ORA-03135 (connection lost contact) is included in the expected disconnection errors from oracle since 0.7.5 and my expectation would be that sqlalchemy would be able to recover from such error gracefully. Did I misunderstand how it works? Should I take actions im my app when I somehow detects this situation? TIA, Mariano [1] http://www.sqlalchemy.org/trac/changeset/dfb0b6aa622006e1e71a5296c3fa17667e24aac1/ -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Expected behaviour for Oracle's lost contact
On jue 05 dic 2013 16:19:14 ART, Michael Bayer wrote: also note if you really want to prevent disconnects at the top of a transaction, you can use a pessimistic approach, see http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html?highlight=pessimistic#disconnect-handling-pessimistic . thanks so much for your quick answer. I will review this option and evaluate if I can use it. On Dec 5, 2013, at 11:05 AM, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 5, 2013, at 10:54 AM, Mariano Mara mariano.m...@gmail.com wrote: Hi there, in one application I am using sqlalchemy 0.7.8 against Oracle 11 release 1 and I am hitting Oracle's lost contact more times than I would like to. Since the app seems unable to recover from the error, I have to restart it completely (it is a pyramid app). According to this changeset[1], the error ORA-03135 (connection lost contact) is included in the expected disconnection errors from oracle since 0.7.5 and my expectation would be that sqlalchemy would be able to recover from such error gracefully. Did I misunderstand how it works? Should I take actions im my app when I somehow detects this situation? TIA, Mariano [1] http://www.sqlalchemy.org/trac/changeset/dfb0b6aa622006e1e71a5296c3fa17667e24aac1/ it will recover in that when you catch this exception, then continue to use that Connection, it will know that it needs to reconnect with a fresh DBAPI connection. the underlying connection pool is also dumped when this exception is encountered. it’s not possible for the Connection to transparently retry the operation with a new DBAPI connection without raising an error because connection session state is lost. e.g. if it occurs in the middle of a result set, the results are gone, occurs in the middle of a transaction, everything in the transaction is gone, etc. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] postgres with queries
On 16/09/13 11:49, Chris Withers wrote: Hi All, I know I asked this before, but I can't find the answer... How would I get SQLALchemy to generate these: http://www.postgresql.org/docs/9.2/static/queries-with.html cheers, Chris look here: http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=cte#sqlalchemy.orm.query.Query.cte In the list archives there are some examples too -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Reliable way to read comments from database schema
On 06/04/2013 11:22 PM, Warwick Prince wrote: On 06/04/2013 10:46 PM, Michael Bayer wrote: There's a long standing ticket to add support for comments, at least at the DDL level. I don't think anyone has looked into what level of support we get from the various backends as far as reflection. So its something the library has room for, but it's an open item for now. The four-year-old ticket is http://www.sqlalchemy.org/trac/ticket/1546. On Jun 4, 2013, at 8:33 PM, Warwick Prince warwi...@mushroomsys.com wrote: Hi Michael I'm toying with the idea of embedding some metadata into the comments on columns and/or the table comment. Is there a way to reliably read the comment from the column definition via reflection across all dialects that would support a comment at a column level? Also, can I read the comment normally attached to the table definition? Thanks Warwick If this metadata will be used just in sqlalchemy (or python libraries up in the stack), you could use the info parameter when creating columns. OK - sounds promising. What actually is the 'info' that it reads/writes? i.e. where is it getting it from/putting it in the DB? (Or does it only reside in the Python code?) It is a dictionary and you can store whatever you want in there (please see http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#sqlalchemy.schema.SchemaItem.info for more info about it). This information is only available on Python code. Also, I'd like to +1 the task to reflect the comments with support for as many dialects as possible :-) We have developed a large platform using SQLA as the ORM, and as part of the maintenance area we have a generic database manager that is intended to allow maintenance, viewing, manual row insertion/deletion etc (Like MySQL Workbench et al) that is 100% database agnostic. Comments are missing at the moment which is a shame. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Reliable way to read comments from database schema
On 06/04/2013 10:46 PM, Michael Bayer wrote: There's a long standing ticket to add support for comments, at least at the DDL level. I don't think anyone has looked into what level of support we get from the various backends as far as reflection. So its something the library has room for, but it's an open item for now. The four-year-old ticket is http://www.sqlalchemy.org/trac/ticket/1546. On Jun 4, 2013, at 8:33 PM, Warwick Prince warwi...@mushroomsys.com wrote: Hi Michael I'm toying with the idea of embedding some metadata into the comments on columns and/or the table comment. Is there a way to reliably read the comment from the column definition via reflection across all dialects that would support a comment at a column level? Also, can I read the comment normally attached to the table definition? Thanks Warwick If this metadata will be used just in sqlalchemy (or python libraries up in the stack), you could use the info parameter when creating columns. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] distinct on
On 05/23/2013 04:42 AM, jo wrote: |Hi all, I wondered if it is possible to execute a partial distinct in sqlalchemy. The following query works in oracle and postgresql: select distinct col1, first_value(col2) over (partition by col1 order by col2 asc) from tmp; How can I do such query in sqlalchemy? Thanks for any help. j Yes, it is entirely possible. Something like this should do the trick (not tested): from sqlalchemy import select, func from sqlalchemy.sql.expression import over q = select([tmp.c.id.distinct(), over(func.first_value(tmp.c.cid), partition_by=tmp.c.id, order_by=tmp.c.name.asc())]) print(q) SELECT DISTINCT user.id, first_value(user.cid) OVER (PARTITION BY user.id ORDER BY user.name ASC) AS anon_1 FROM user This chapter of the documentation will help with these features and much more: http://docs.sqlalchemy.org/en/latest/core/expression_api.html -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Creating an index if it doesn't exist
On 05/23/2013 09:06 PM, Mike Bissell wrote:
How might I convince SQLAlchemy 0.7.9 to create a newly added index on a table
in the event that the index doesn't already exist? This new index is created as
a member of __table_args__; it is not instantiated with index=True. I have many
such index objects to create.
Calling create_all() doesn't appear to issue CREATEs for a table if it already
exists. I think that's because the optional checkfirst parameter defaults to
True.
Setting checkfirst=False breaks because trying to create an already existing DB
object is an error, and most of my DB already exists:
ModelBase.metadata.create_all(checkfirst=False)
ProgrammingError: (ProgrammingError) relation organism already exists
'\nCREATE TABLE organism (\n\tid SERIAL NOT NULL, \n\tname VARCHAR NOT
NULL, \n\tPRIMARY KEY (id)\n)\n\n' {}
I did check stackoverflow, and their unsatisfactory advice was simply to cut
and paste the generated DDL:
http://stackoverflow.com/questions/14419299/adding-indexes-to-sqlalchemy-models-after-table-creation
In a perfect world, I'd like a way to drop a new index onto a column with the
assurance that SQLAlchemy will create it as needed. In my case, this DB is just
a big testing data set, so there is no need for formal migrations.
If this feature doesn't exist, I would settle for a function that would take a
table and conditionally create any missing pieces (specifically indexes).
As a last resort, is the correct way to do this simply to call Index.create for
each index I make?
MB
I would suggest event.listen with a DDL event:
1- first a routine to check if the objects exists (vendors providing the
create if not exists idiom could help here, too):
def should_create(ddl, target, connection, state, **kw):
http://www.sqlalchemy.org/docs/core/schema.html;
row = connection.execute(select relname from pg_class where
relname='{0}'.format(state)).scalar()
return not bool(row)
2- then an event that will be execute when the time is right:
event.listen(Base.metadata, after_create, DDL(create index
lower_value_ix on
dummy_table(lower(value)).execute_if(callable_=should_create,
state=lower_value_ix))
These chapters should be of help:
http://docs.sqlalchemy.org/en/rel_0_7/core/events.html?highlight=after_create#sqlalchemy.events.DDLEvents.after_create
http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#customizing-ddl
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Tricky situation
On 04/25/2013 11:17 AM, Richard Gerd Kuesters wrote: Well. I'm pretty interested :) I did find your solution very flexible, thou. Thanks a lot, Richard. For completeness, here is a pure sqlalchemy version of the same recursive ideas: def _hierarchy(): # TODO: check if object has self referential key sq1 = S.query(self.model) sq1 = sq1.add_columns(*(label(level, literal(1, type_=Integer)), label(cycle, literal(False, type_=Boolean)), label(connect_path, literal_column(ARRAY[id], ARRAY(Integer) sq1 = sq1.filter(self.model.parent_id==None) sq1 = sq1.cte(name=sq1, recursive=True) sq1_alias = aliased(sq1, name=pr) sq2_alias = aliased(self.model, name=p) sq1 = sq1.union_all( S.query(sq2_alias, label(level, sq1_alias.c.level+1), label(cycle, Any(sq2_alias.id, sq1_alias.c.connect_path)), label(connect_path, func.array_append(sq1_alias.c.connect_path, sq2_alias.id)) ).filter(and_(sq2_alias.parent_id==sq1_alias.c.id, ~sq1_alias.c.cycle))) q = S.query(sq1) q = q.add_columns(*(label(is_leaf, case([( sq1.c.connect_path.contained_by( over(func.lead( sq1.c.connect_path, 1),order_by=sq1.c.connect_path))==True, False)], else_=True)),)) return q.all() It could use some testing (something I am about to do) and you still need to cast to your object but all the ingredients are here. As usual the model needs a self referential key (in this case id and parent_id). I try not to use the textual ARRAY[id] to generate the first connect_path but I couldn't make it without errors (and the time was pressing): If you have a suggestion on how to do so, I will surely appreciate it. Mariano On 04/25/2013 11:08 AM, Mariano Mara wrote: On 04/25/2013 10:22 AM, Richard Gerd Kuesters wrote: Hi all, I've been playing with sqla_hierarchy from https://github.com/marplatense/sqla_hierarchy . That code of that sqla_hierarchy was written to provide a limited support for cte, from the time when sqalchemy didn't have cte. Since sqlalchemy has cte now I would recommend to migrate to pure sqlalchemy code which will open more possibilities for you to interact with your objects and provide support in the long term. I am currently trying to do so (and once I solve how to get *level*, *is_leaf* and *connect_path* attributes as part of the results, I can surely share it through the list for anyone who's interested). Mariano -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Tricky situation
On 04/25/2013 10:22 AM, Richard Gerd Kuesters wrote: Hi all, I've been playing with sqla_hierarchy from https://github.com/marplatense/sqla_hierarchy . That code of that sqla_hierarchy was written to provide a limited support for cte, from the time when sqalchemy didn't have cte. Since sqlalchemy has cte now I would recommend to migrate to pure sqlalchemy code which will open more possibilities for you to interact with your objects and provide support in the long term. I am currently trying to do so (and once I solve how to get *level*, *is_leaf* and *connect_path* attributes as part of the results, I can surely share it through the list for anyone who's interested). Mariano -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Modifying a relationship before it gets inserted
On 05.01.12 16:13, Michael Bayer wrote: On Jan 5, 2012, at 3:03 PM, Mariano Mara wrote: Hi there! I have a master-detail entity. Since I do some post-processing work on the details before inserting the entity in the db, I added an 'after_insert' event where I do the extra stuff. One of the things I need is to make sure certain details have been selected by the user and in case he didn't I add some default values on his behalf, just like master.detail.append(DetailCls(score=100, timestamp=datetime.datetime.now())) don't do any kind of manipulation of the flush plan, which includes collection alterations, inside of after_insert(). The append() operation here is most cleanly implemented in the __init__ method of your Master object. That way the model itself handles the shape it should be in. Another way to set up state is using the @validates hook to respond to an attribute set event: http://www.sqlalchemy.org/docs/orm/mapper_config.html?highlight=validates#simple-validators The other way is to use the before_flush event: http://www.sqlalchemy.org/docs/orm/events.html?highlight=before_flush#sqlalchemy.orm.events.SessionEvents.before_flush Thanks a lot for the answer. As expected adding my logic in the __init__ was the way to go. I have a question though: Let's say users can edit the detail instances of this master and in case I detect that certain attribute is modified, I want invalidate the detail and create a new one. e.g.: if get_history(inst_detail, attr).has_changes(): inst_detail.active=False new_detail = Detail(attr=new_attr, score=100) master.append(new_detail) My question is how I can skip the attr modification made in the old_instance? I want to keep the active=False but I want to ignore the attr change. I tried with several methods in Session (e.g. expunge) but the update occurs nevertheless. TIA, Mariano -- 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] Modifying a relationship before it gets inserted
Hi there! I have a master-detail entity. Since I do some post-processing work on the details before inserting the entity in the db, I added an 'after_insert' event where I do the extra stuff. One of the things I need is to make sure certain details have been selected by the user and in case he didn't I add some default values on his behalf, just like master.detail.append(DetailCls(score=100, timestamp=datetime.datetime.now())) My problem is that the foreign key column in Detail does not get populated for my inserted details and an error is raised when flushing the entity to the db. My question is where exactly should I hook an action so that I can intercept the primary key and update the FK attribute for the details I added in the event. TIA, Mariano -- 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 is ignoring the CheckConstraint name parameter while creating a check constraint
Hi all,
the subject pretty much explains it all. Here's a complete example of the
issue. Any tips will be appreciate.
Regards,
Mariano
Python 2.7.2 (default, Oct 14 2011, 23:34:02)
[GCC 4.5.2] on linux2
Type help, copyright, credits or license for more information.
import sqlalchemy
sqlalchemy.__version__
'0.7.3'
from sqlalchemy import Table, Column, Integer, create_engine, MetaData,
CheckConstraint
from sqlalchemy.orm import sessionmaker
session = sessionmaker()
Session = session()
engine = create_engine('postgresql://usr:passws@localhost/base', echo=True)
Session.configure(bind=engine)
md = MetaData()
md.bind = engine
t = Table(test_ck, md, Column(id, Integer, primary_key=True),
... Column(id2, Integer, CheckConstraint(id2 between 1 and 10,
name=my_constraint)))
md.create_all(engine)
2011-10-21 19:53:45,523 INFO sqlalchemy.engine.base.Engine select version()
2011-10-21 19:53:45,523 INFO sqlalchemy.engine.base.Engine {}
2011-10-21 19:53:45,708 INFO sqlalchemy.engine.base.Engine select
current_schema()
2011-10-21 19:53:45,709 INFO sqlalchemy.engine.base.Engine {}
2011-10-21 19:53:45,804 INFO sqlalchemy.engine.base.Engine select relname from
pg_class c join pg_namespace n on n.oid=c.relnamespace where
n.nspname=current_schema() and relname=%(name)s
2011-10-21 19:53:45,804 INFO sqlalchemy.engine.base.Engine {'name': u'test_ck'}
2011-10-21 19:53:46,168 INFO sqlalchemy.engine.base.Engine
CREATE TABLE test_ck (
id SERIAL NOT NULL,
id2 INTEGER CHECK (id2 between 1 and 10),
PRIMARY KEY (id)
)
2011-10-21 19:53:46,168 INFO sqlalchemy.engine.base.Engine {}
2011-10-21 19:53:47,116 INFO sqlalchemy.engine.base.Engine COMMIT
--
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 is ignoring the CheckConstraint name parameter while creating a check constraint
On 21.10.11 20:09, Michael Bayer wrote:
Because not every DB supports this (such as MySQL), we have not yet
implemented the feature of named column level constraints across the board
yet. We'd have to either implement it only for those DBs which support it,
or add exceptions to those which don't. The main job is a. figure out which
backends support it, and b. write some tests.
Here's a patch:
http://www.sqlalchemy.org/trac/ticket/2305
Table-level CHECK constraints do support generation of the name in all
cases, however.
Thanks a lot, the patch works like charm
On Oct 21, 2011, at 7:10 PM, Mariano Mara wrote:
Hi all,
the subject pretty much explains it all. Here's a complete example of the
issue. Any tips will be appreciate.
Regards,
Mariano
Python 2.7.2 (default, Oct 14 2011, 23:34:02)
[GCC 4.5.2] on linux2
Type help, copyright, credits or license for more information.
import sqlalchemy
sqlalchemy.__version__
'0.7.3'
from sqlalchemy import Table, Column, Integer, create_engine, MetaData,
CheckConstraint
from sqlalchemy.orm import sessionmaker
session = sessionmaker()
Session = session()
engine = create_engine('postgresql://usr:passws@localhost/base',
echo=True)
Session.configure(bind=engine)
md = MetaData()
md.bind = engine
t = Table(test_ck, md, Column(id, Integer, primary_key=True),
... Column(id2, Integer, CheckConstraint(id2 between 1 and 10,
name=my_constraint)))
md.create_all(engine)
2011-10-21 19:53:45,523 INFO sqlalchemy.engine.base.Engine select version()
2011-10-21 19:53:45,523 INFO sqlalchemy.engine.base.Engine {}
2011-10-21 19:53:45,708 INFO sqlalchemy.engine.base.Engine select
current_schema()
2011-10-21 19:53:45,709 INFO sqlalchemy.engine.base.Engine {}
2011-10-21 19:53:45,804 INFO sqlalchemy.engine.base.Engine select relname
from pg_class c join pg_namespace n on n.oid=c.relnamespace where
n.nspname=current_schema() and relname=%(name)s
2011-10-21 19:53:45,804 INFO sqlalchemy.engine.base.Engine {'name':
u'test_ck'}
2011-10-21 19:53:46,168 INFO sqlalchemy.engine.base.Engine
CREATE TABLE test_ck (
id SERIAL NOT NULL,
id2 INTEGER CHECK (id2 between 1 and 10),
PRIMARY KEY (id)
)
2011-10-21 19:53:46,168 INFO sqlalchemy.engine.base.Engine {}
2011-10-21 19:53:47,116 INFO sqlalchemy.engine.base.Engine COMMIT
--
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.
--
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] bulk selection
On 12.08.11 09:41, Eduardo wrote: Dear All, I have a list of elements for which I need to establish if they are in a tadabase. I can make for each element a separate query but I am afraid that that is not the best approach what is the best practice in this case? Thanks Depending on the size of the list I would use the in_ operator[1]. If you're talking about a really long list [2] you might drop everything to a temporary list and join from there. Mariano [1] http://www.sqlalchemy.org/docs/orm/tutorial.html#common-filter-operators [2] in old days there were limits to the amount of items in an in operator, I'm not sure if it still holds nowadays. -- 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 one to many relationship and composite primary key
Hi.
I'm trying to relate two tables with a one to many relationship (the
parent table has a composite primary key) but I'm getting a mapper
error. I found a recent message about this same problem but with
declarative base (which I don't use) and not sure why the suggestion
there didn't apply to my problem.
Find below the error and the table creation code.
TIA,
Mariano
Error:
ArgumentError: Could not locate any equated, locally mapped column pairs
for primaryjoin condition 'regevent.id =
regevent_who.regevent_id AND regevent.author =
regevent_who.regevent_author' on relationship RegEvent.who. For more
relaxed rules on join conditions, the relationship may be marked as
viewonly=True.
Code:
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime),
useexisting=True)
Index('regevent_cal_ix', *(regevent.c.calname,))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id, regevent_author),
(regevent.id, regevent.author),
regevent_fk),
useexisting=True)
Index(regevent_who_fk_ix, *(regevent_who.c.regevent_id,
regevent_who.c.regevent_author))
class RegEventWho(object):
pass
mapper(RegEvent, regevent_who, properties={
'who': relationship(RegEventWho,
primaryjoin=and_(
regevent.c.id==regevent_who.c.regevent_id,
regevent.c.author==regevent_who.c.regevent_author))
})
mapper(RegEventWho, regevent_who)
--
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] Problem with one to many relationship and composite primary key
Excerpts from Michael Bayer's message of Tue Nov 30 13:50:26 -0300 2010:
Nothing wrong with the mapping, except the primaryjoin is not needed. The
cause is certainly the usage of useexisting, which implies that these
tables have already been created, and everything you are specifying in the
Table() is ignored. I wouldn't use that flag.
Thanks Michael as always.
Removing the useexisting=True, if I also remove the primaryjoin I get:
ArgumentError: Could not determine join condition between parent/child
tables on relationship RegEvent.who. Specify a 'primaryjoin' expression. If
'secondary' is
present, 'secondaryjoin' is needed as well.
If I leave the primaryjoin I still get the same error as reported.
TIA for any extra ideas you can suggest to fix this.
Mariano
On Nov 30, 2010, at 10:22 AM, Mariano Mara wrote:
Hi.
I'm trying to relate two tables with a one to many relationship (the
parent table has a composite primary key) but I'm getting a mapper
error. I found a recent message about this same problem but with
declarative base (which I don't use) and not sure why the suggestion
there didn't apply to my problem.
Find below the error and the table creation code.
TIA,
Mariano
Error:
ArgumentError: Could not locate any equated, locally mapped column pairs
for primaryjoin condition 'regevent.id =
regevent_who.regevent_id AND regevent.author =
regevent_who.regevent_author' on relationship RegEvent.who. For more
relaxed rules on join conditions, the relationship may be marked as
viewonly=True.
Code:
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime),
useexisting=True)
Index('regevent_cal_ix', *(regevent.c.calname,))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id, regevent_author),
(regevent.id, regevent.author),
regevent_fk),
useexisting=True)
Index(regevent_who_fk_ix, *(regevent_who.c.regevent_id,
regevent_who.c.regevent_author))
class RegEventWho(object):
pass
mapper(RegEvent, regevent_who, properties={
'who': relationship(RegEventWho,
primaryjoin=and_(
regevent.c.id==regevent_who.c.regevent_id,
regevent.c.author==regevent_who.c.regevent_author))
})
mapper(RegEventWho, regevent_who)
--
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] Problem with one to many relationship and composite primary key
ohhh, I'm out of words other than thank you for spotting it. I can't
believe how stupid I feel right now.
Mariano
Excerpts from Michael Bayer's message of Tue Nov 30 14:27:42 -0300 2010:
your RegEvent mapper is against the wrong table, here is the correct code:
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id, regevent_author),
(regevent.id, regevent.author),
regevent_fk))
class RegEventWho(object):
pass
mapper(RegEvent, regevent, properties={
'who': relationship(RegEventWho)
})
mapper(RegEventWho, regevent_who)
print Session().query(RegEvent).join(RegEvent.who)
On Nov 30, 2010, at 12:22 PM, Mariano Mara wrote:
Excerpts from Michael Bayer's message of Tue Nov 30 13:50:26 -0300 2010:
Nothing wrong with the mapping, except the primaryjoin is not needed.
The cause is certainly the usage of useexisting, which implies that
these tables have already been created, and everything you are specifying
in the Table() is ignored. I wouldn't use that flag.
Thanks Michael as always.
Removing the useexisting=True, if I also remove the primaryjoin I get:
ArgumentError: Could not determine join condition between parent/child
tables on relationship RegEvent.who. Specify a 'primaryjoin' expression.
If 'secondary' is
present, 'secondaryjoin' is needed as well.
If I leave the primaryjoin I still get the same error as reported.
TIA for any extra ideas you can suggest to fix this.
Mariano
On Nov 30, 2010, at 10:22 AM, Mariano Mara wrote:
Hi.
I'm trying to relate two tables with a one to many relationship (the
parent table has a composite primary key) but I'm getting a mapper
error. I found a recent message about this same problem but with
declarative base (which I don't use) and not sure why the suggestion
there didn't apply to my problem.
Find below the error and the table creation code.
TIA,
Mariano
Error:
ArgumentError: Could not locate any equated, locally mapped column pairs
for primaryjoin condition 'regevent.id =
regevent_who.regevent_id AND regevent.author =
regevent_who.regevent_author' on relationship RegEvent.who. For more
relaxed rules on join conditions, the relationship may be marked as
viewonly=True.
Code:
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime),
useexisting=True)
Index('regevent_cal_ix', *(regevent.c.calname,))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id,
regevent_author),
(regevent.id,
regevent.author),
regevent_fk),
useexisting=True)
Index(regevent_who_fk_ix, *(regevent_who.c.regevent_id,
regevent_who.c.regevent_author))
class RegEventWho(object):
pass
mapper(RegEvent, regevent_who, properties={
'who': relationship(RegEventWho,
primaryjoin=and_(
regevent.c.id
[sqlalchemy] Error with ForeignKeyConstraint while reloading model
Hi.
I have some (very simple) tables defined in my model, such as
extensions = Table('ast_user_extension', metadata,
Column(id, Integer, autoincrement=True,
primary_key=True),
Column(extension, Unicode(20), nullable=False),
Column(name, Unicode(50), nullable=False),
Column(start_date, DateTime, nullable=False),
Column(end_date, DateTime),
Column(user_id, Unicode(3), ForeignKey('user.id')),
Column(group_area, Unicode(100)),
Column(group_country_id, Unicode(3)),
CheckConstraint(end_date is null or end_date=start_date,
name=ast_usr_ext_ck1),
ForeignKeyConstraint((group_area, group_country_id),
(group.area, group.country_id),
'group_fk'),
useexisting=True)
The thing is that when I'm working with the model in the interactive console
(ipython) and I try to
reload the module I'm getting KeyError: ForeignKeyConstraint() and so I need to
exit ipython
and log in again. It's not a critical problem but it's a bit cumbersome. What
can I do to fix it?
Find below the full error stack.
TIA,
Mariano
/home/mariano/Code/ReportingCom/asterisk/repdotcom/repdotcom/model/asterisk.py
in module(
)
28 (group.area,
group.country_id),
29 'group_fk'),
--- 30 useexisting=True)
31 Index(ast_usr_ext_ix, *(extensions.c.extension, extensions.c.name))
32 Index(ast_usr_ext_gro_ix, *(extensions.c.group_area,
/home/mariano/Sandbox/reporting/lib/python2.6/site-packages/SQLAlchemy-0.6.2-py2.6.egg/sqla
lchemy/schema.pyc in __new__(cls, *args, **kw)
197 columns on an existing Table object. % key)
198 table = metadata.tables[key]
-- 199 table._init_existing(*args, **kw)
200 return table
201 else:
/home/mariano/Sandbox/reporting/lib/python2.6/site-packages/SQLAlchemy-0.6.2-py2.6.egg/sqla
lchemy/schema.pyc in _init_existing(self, *args, **kwargs)
288
289 self._extra_kwargs(**kwargs)
-- 290 self._init_items(*args)
291
292 def _extra_kwargs(self, **kwargs):
/home/mariano/Sandbox/reporting/lib/python2.6/site-packages/SQLAlchemy-0.6.2-py2.6.egg/sqla
lchemy/schema.pyc in _init_items(self, *args)
57 for item in args:
58 if item is not None:
--- 59 item._set_parent(self)
60
61 def _set_parent(self, parent):
/home/mariano/Sandbox/reporting/lib/python2.6/site-packages/SQLAlchemy-0.6.2-py2.6.egg/sqla
lchemy/schema.pyc in _set_parent(self, table)
792 col.foreign_keys.remove(fk)
793 table.foreign_keys.remove(fk)
-- 794 table.constraints.remove(fk.constraint)
795
796 table._columns.replace(self)
KeyError: ForeignKeyConstraint()
--
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] Problem with alias in SQL
Hi there,
I have these two tables (with very long column names and most of them
with the same names in both tables) I want to join. Since I will be
processing the results afterwards, I would like to have shorten names
to type less and clearly differentiated names to avoid conflicts.
So I pass the use_labels param to the select statement and apply
labels to both tables.
E.g.:
select([service.alias('a'), history.alias('b')], use_labels=True)
which generates the following sql:
select a.id as a_id, a.valid_flag as a_valid_flag, a.code as
a_code, ...more columns with 'a' alias, b.id as b_id, ... more column
with 'b' alias from service as a, history as b...
The problem is when I add a where clause (or I think it's the
problem):
select([service.alias('a'), history.alias('b')],
and_(service.c.id==history.c.id, service.c.valid_flag==True),
use_labels=True)
turns out it generates the following sql:
select a.id as a_id, a.valid_flag as a_valid_flag, a.code as
a_code, ...more columns with 'a' alias, b.id as b_id, ... more column
with 'b' alias from service as a, history as b, service, history where
service.id=history.id and service.valid_flag='f'
As you can see it ignores the existence of the aliased tables and use
a new copy of them to make the join, generating a cartesian join.
How can I fix this situation?
TIA,
Mariano
--
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: blocking on Base.metadata.drop_all() in test
Excerpts from zende's message of Thu Jul 08 13:27:27 -0300 2010: I reproduced the issue the script below: http://gist.github.com/468199 Sorry for the weak explanation before. This has little to do with being in tests except that's the only code that drops and creates the db for any reason. Ctrl-C does nothing when it blocks. Chris, try running the script in the link, and let me know if you are able to reproduce the issue I'm on a similar situation. For lack of time I couldn't investigate it yet but I have a drop_all when running nosetests in my pylons project and it get stuck while dropping the tables. I have to kill the process, because ^C doesn't help. I will check with pudb this afternoon to see if I can bring more data. I'm using sqlalchemy 0.6.1 Mariano -- 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: blocking on Base.metadata.drop_all() in test
Excerpts from zende's message of Thu Jul 08 14:01:32 -0300 2010: Mariano, What db? postgres? On Jul 8, 9:41 am, Mariano Mara mariano.m...@gmail.com wrote: I'm on a similar situation. For lack of time I couldn't investigate it yet but I have a drop_all when running nosetests in my pylons project and it get stuck while dropping the tables. I have to kill the process, because ^C doesn't help. I will check with pudb this afternoon to see if I can bring more data. I'm using sqlalchemy 0.6.1 Mariano Zende, that's right: psql 8.4.4 Mariano -- 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] WITH RECURSIVE and ORM
Excerpts from Ergo's message of Sun Jun 27 14:11:58 -0300 2010: Hello, I'm looking for a way to execute a recursive query and get ORM instances with it. http://www.postgresql.org/docs/8.4/interactive/queries-with.html I know we don't have any direct support for recursive syntax in SA, but is there a way to execure arbitrary query that would return all the data to build ORM objects and use those objects later ? I built a little extension to support WITH RECURSIVE with SA's SQL Expression Language. You can find all about it here [1]. It might not address your problem directly but you might use it as inspiration. http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PgsqlRecursive -- 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] Hierarchical queries with pgsql = 8.4.0
Hi everyone. I have been working in a little class that brings support
for with recursive idiom in my project. Actually the part it supports
for the moment are the necessary bits to generate hierarchical data (I
thought somebody might find it useful too so I added it as a recipe in
the wiki[1]).
The basic idea is that you submit a select expression like (see the wiki
for the whole example):
select([category.c.id, category.c.name])
and you will get a query like
with recursive rec as (SELECT category.id, category.name, 1 AS level,
ARRAY[id] AS connect_path
FROM category
WHERE coalesce(parent_id, 0) = 0 UNION ALL SELECT category.id,
category.name, rec.level + 1 AS level, array_append(rec.connect_path,
category.id) AS connect_path
FROM category, rec
WHERE category.parent_id = rec.id) SELECT rec.id, rec.name, rec.level,
rec.connect_path, case connect_path @ lead(connect_path, 1) over (order
by connect_path) when true then false else true end AS is_leaf
FROM rec order by connect_path
that will give you the same information you requested plus some extra
columns with hierarchy related info.
The final piece I'm missing is how to pass a where clause: actually I
cannot make the final sql instruction to accept the parameters I'm
passing and after hours of trying it seems my sqlalchemy-fu is exhausted
and I can't fix it by myself.
To illustrate the problem, this is the select with a where clause:
select([category.c.id, category.c.name], category.c.active==True)
this is the query it generates:
with recursive rec as (SELECT dummy_hierarchy.id, 1 AS level, ARRAY[id]
AS connect_path
FROM dummy_hierarchy
WHERE dummy_hierarchy.active = %(active_1)s AND coalesce(parent_id, 0) =
0 UNION ALL SELECT dummy_hierarchy.id, rec.level + 1 AS level,
array_append(rec.connect_path, dummy_hierarchy.id) AS connect_path
FROM dummy_hierarchy, rec
WHERE dummy_hierarchy.active = %(active_1)s AND
dummy_hierarchy.parent_id = rec.id) SELECT rec.id, rec.level,
rec.connect_path, case connect_path @ lead(connect_path, 1) over (order
by connect_path) when true then false else true end AS is_leaf
FROM rec order by connect_path
and this is the error I'm getting (if I run with nosetest):
Traceback (most recent call last):
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/nose-0.11.3-py2.6.egg/nose/case.py,
line 186, in runTest
self.test(*self.arg)
File /home/mariano/Code/insite/dev/insite/insite/tests/test1_hierarchy.py,
line 261, in test8_where_clause
rs = Session.execute(qry).fetchall()
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/scoping.py,
line 129, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/session.py,
line 737, in execute
clause, params or {})
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py,
line 1109, in execute
return Connection.executors[c](self, object, multiparams, params)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py,
line 1186, in _execute_clauseelement
return self.__execute_context(context)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py,
line 1215, in __execute_context
context.parameters[0], context=context)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py,
line 1282, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/default.py,
line 277, in do_execute
cursor.execute(statement, parameters)
KeyError: 'active_1'
Running from the python shell I get a totally different error but it's still
about the bind parameter (it does not fail but
it doesn't take the bind value either):
Session.execute(x.compile()).fetchall()
with recursive rec as (SELECT category.id, category.name, 1 AS level, ARRAY[id]
AS connect_path
FROM category
WHERE category.active = %(active_1)s AND coalesce(parent_id, 0) = 0 UNION ALL
SELECT category.id, category.name, rec.level + 1 AS level,
array_append(rec.connect_path, category.id) AS connect_path
FROM category, rec
WHERE category.active = %(active_1)s AND category.parent_id = rec.id) SELECT
rec.id, rec.name, rec.level, rec.connect_path, case connect_path @
lead(connect_path, 1) over (order by connect_path) when true then false else
true end AS is_leaf
FROM rec order by connect_path
{'active_1': None}
[]
The tar file in the wiki includes the whole class (+/- 200 lines of code but at
least half of
them are comments and doc strings) plus a test suite with 7 tests demonstrating
the usage.
If somebody has the time
Re: [sqlalchemy] Hierarchical queries with pgsql = 8.4.0
Excerpts from Michael Bayer's message of Wed May 26 13:23:01 -0300 2010:
On May 26, 2010, at 11:47 AM, Mariano Mara wrote:
Hi everyone. I have been working in a little class that brings support
for with recursive idiom in my project. Actually the part it supports
for the moment are the necessary bits to generate hierarchical data (I
thought somebody might find it useful too so I added it as a recipe in
the wiki[1]).
The basic idea is that you submit a select expression like (see the wiki
for the whole example):
select([category.c.id, category.c.name])
and you will get a query like
with recursive rec as (SELECT category.id, category.name, 1 AS level,
ARRAY[id] AS connect_path
FROM category
WHERE coalesce(parent_id, 0) = 0 UNION ALL SELECT category.id,
category.name, rec.level + 1 AS level, array_append(rec.connect_path,
category.id) AS connect_path
FROM category, rec
WHERE category.parent_id = rec.id) SELECT rec.id, rec.name, rec.level,
rec.connect_path, case connect_path @ lead(connect_path, 1) over (order
by connect_path) when true then false else true end AS is_leaf
FROM rec order by connect_path
that will give you the same information you requested plus some extra
columns with hierarchy related info.
The final piece I'm missing is how to pass a where clause: actually I
cannot make the final sql instruction to accept the parameters I'm
passing and after hours of trying it seems my sqlalchemy-fu is exhausted
and I can't fix it by myself.
To illustrate the problem, this is the select with a where clause:
select([category.c.id, category.c.name], category.c.active==True)
I dont have time to read all your source but when you say
category.c.active==True, you get a structure like:
_BinaryExpression
Column('active'), operator.eq, _BindParamClause('active_1', value=True)
the value of that bind maybe could get lost if you aren't compiling the
statement fully with the same compiler object. for example, if you had
something like this:
@compiles(FooBar)
def compile_foo_bar(element, compiler, **kw):
return FOO BAR + str(element.value)
the str(element.value), if that is also a ClauseElement, is going to invoke a
whole new compiler with its own set of bind param values. You don't want to
do that. You want to say:
@compiles(FooBar)
def compile_foo_bar(element, compiler, **kw):
return FOO BAR + compiler.process(element.value)
thus keeping everything within the same context. this not only maintains
all the binds but also maintains the behavior of the backend being compiled
against.
hope this helps...
For sure it helps, thanks for the pointer. Will try to fix my problem
with it.
Mariano
--
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] oracle database error.
Excerpts from dhanil anupurath's message of Tue Apr 27 07:22:45 -0300 2010: I am using Sqlalchemy with Oracle backend in my project. I am running into listener issues with the Oracle database. Here are the steps that I am following: 1. My connection string. sqlalchemy.url=oracle://root::password@localhost:1521/orcdb 2. Environment variables . export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/ server export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib export ORACLE_SID=orc10g The following is the error that I get when my project tries to connect to the Oracle db. Traceback (most recent call last): File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6- py2.4.egg/sqlalchemy/engine/strategies.py, line 80, in connect raise exc.DBAPIError.instance(None, None, e) sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor None None Please give suggestions. AFAIK, Oracle XE SID by default is XE. Are you sure yours is orc10g? -- 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] about commit()
Excerpts from jo's message of Fri Apr 23 03:16:21 -0300 2010:
Hi all,
I need to insert a new row and get back the last inserted id,
I have some difficulty using the flush(), then I'm trying with commit() but
I can't understand how commit() works in 0.6.
In the following script I try to update a row and it works properly
but when I try to insert a new one, it doesn't work,
there's no messages but the row is not inserted into the table.
Is this the right way ?
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(autoflush=True)
session = Session()
#update an existing row... it works
old = Specie.get('D')
old.specie_descrizione='dog'
#insert a new row... it doesn't work
new=Specie(
specie_codice='C',
specie_descrizione='cat'
)
session.commit()
thanks for any help
j
You forgot to add the instance to the session before the commit. See
http://www.sqlalchemy.org/docs/ormtutorial.html#adding-new-objects
--
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] further restricting a query provided as raw sql
Excerpts from Chris Withers's message of Thu Apr 15 11:46:05 -0300 2010: Michael Bayer wrote: you have to rewrite your SQL to support the number of values in the IN clause for each parameter set. Hmm :'( While my code knows the number of values, they don't, and it may vary from when they write the SQL to when that SQL gets executed by my code... Chris My answer will be generic since I don't know id SA provide a better way to deal with it (I haven't face this situation yet). You will have to provide the values not as binded parameters but hardcoded instead: SELECT somestuff FROM somewhere WHERE some_date = :from_date AND some_date = :to_date AND somefield in (%s) % (,.join([str(x) for x in a])) if you don't like this kind of hack, depending on your database, you can create a temp table, insert all the values in it and join with your real table. -- 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] identifier is too long
Excerpts from jo's message of Tue Mar 30 03:25:18 -0300 2010:
Hi all,
I have some troubles creating my db schema with Oracle. The problem is
on this column:
Column('cod_caratteristica_rischio', Unicode(10), index=True,
nullable=False)
It works fine in PostgreSQL but when I try it on Oracle sa tries to
create an index with a name too long ( 30 char).
tbl['rischio_attivita'] = Table('rischio_attivita', database.metadata,
Column('id', Integer, Sequence('rischio_attivita_seq'), primary_key=True),
Column('cod_attivita', Unicode(10),nullable=False),
Column('cod_caratteristica_rischio', Unicode(10), index=True,
nullable=False),
Column('tipo_calcolo', Integer),
Column('algoritmo', Unicode(200)),
Column('fattore_x', Float, PassiveDefault('1.0')),
ForeignKeyConstraint(['cod_attivita'], ['attivita.codice']),
ForeignKeyConstraint(['cod_caratteristica_rischio'],
['caratteristica_rischio.codice'])
)
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line
599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00972: identifier is
too long
'CREATE INDEX ix_rischio_attivita_cod_caratteristica_rischio ON
rischio_attivita (cod_caratteristica_rischio)' {}
Hi there, instead of creating the index as part of the Column
expression, use the Index[1] statement and assign a name yourself.
[1]
http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalchemy.schema.Index
--
Jose Soares
Sferacarta Net
Via Bazzanese 69
40033 Casalecchio di Reno
Bologna - Italy
Ph +39051591054
fax +390516131537
web:www.sferacarta.com
Le informazioni contenute nella presente mail ed in ogni eventuale file
allegato sono riservate e, comunque, destinate esclusivamente alla persona o
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196.
La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza,
l’integrità e la sicurezza della presente mail non possono essere garantite.
Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci
immediatamente e di eliminarla. Grazie.
This communication is intended only for use by the addressee, pursuant to
legislative decree 30 June 2003, n. 196. It may contain confidential or
privileged information. You should not copy or use it to disclose its
contents to any other person. Transmission cannot be guaranteed to be
error-free, complete and secure. If you are not the intended recipient and
receive this communication unintentionally, please inform us immediately and
then delete this message from your system. Thank you.
--
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] Column type in select w/ if condition
Excerpts from Bryan's message of Tue Mar 30 11:27:57 -0300 2010: The underlying column returns a Decimal object when queried regularly, and when summed as follows: select([ mytable.c.hours ]) Decimal(1.0) select([ func.sum(mytable.c.hours) ]) Decimal(1.0) ...but when I sum it w/ an if statement, it returns a float: select([ func.sum(func.if_(True, mytable.c.hours, 0)) ]) 1.0 How can I control the return type of that summed if column? You could use cast [1] (example: casting to Float, untested): from sqlalchemy.sql.expression import cast from sqlalchemy.sa import Float ... select([ cast(func.sum(func.if_(True, mytable.c.hours, 0)), Float)]) [1] http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.cast -- 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: (Workaround found and SA bug with localhost?) error connecting to remote mysql database
Excerpts from Richard de Koning's message of Thu Mar 25 15:50:45 -0300 2010:
I've done some troubleshooting and these are my preliminary
conclusions.
A ssh-tunnel is used to reach the remote MySQL database server that
only runs on 127.0.0.1 (localhost).
Commands sent to 127.0.0.1 port 3307 are forwarded to remote server on
port 3306.
When I connect with MySQLdb.connect and create an normal SQL-
statement from a Python-script it works as it should.
sql = INSERT INTO data (fname, lname) VALUES ('%s', '%s') % (fname,
lname)
So the problem must be somewhere in Sqlalchemy. Maybe it has anything
to do with 'localhost' somewhere in the code . When I initially ran
the query on port 3307 the data was inserted in the local development-
database that was running on the standard MySQL 3306 port. I'll use
the normal SQL as a workaround, but I would prefer to do it in SA.
Is this a known issue or should I report this as a bug?
I'm developing a little pylons application and I'm using a mysql
database. Like you, I'm using a ssh tunnel with port forwarding. My
connection is working ok with SqlAlchemy.
This is my connection string (I would start looking from there):
sqlalchemy.url =
mysql://user:passw...@127.0.0.1:6/database
As you can see I'm using port 6 instead of 3307 as you mentioned.
Mariano
On Mar 25, 5:24 pm, Richard de Koning richard1...@gmail.com wrote:
I'm pretty new using sqlalchemy and I will probably ask some smart and
a lot of silly questions.
At the moment I'm having problems with connecting to remote MySQL
databases. If I run the updates on a local database everything works
fine.
When I try to connect to a remote database I keep getting the
following errors:
File /usr/lib/pymodules/python2.6/sqlalchemy/pool.py, line 142, in
connect
return _ConnectionFairy(self).checkout()
File /usr/lib/pymodules/python2.6/sqlalchemy/pool.py, line 304, in
__init__
rec = self._connection_record = pool.get()
File /usr/lib/pymodules/python2.6/sqlalchemy/pool.py, line 161, in
get
return self.do_get()
File /usr/lib/pymodules/python2.6/sqlalchemy/pool.py, line 639, in
do_get
con = self.create_connection()
File /usr/lib/pymodules/python2.6/sqlalchemy/pool.py, line 122, in
create_connection
return _ConnectionRecord(self)
File /usr/lib/pymodules/python2.6/sqlalchemy/pool.py, line 198, in
__init__
self.connection = self.__connect()
File /usr/lib/pymodules/python2.6/sqlalchemy/pool.py, line 261, in
__connect
connection = self.__pool._creator()
File /usr/lib/pymodules/python2.6/sqlalchemy/engine/strategies.py,
line 80, in connect
raise exc.DBAPIError.instance(None, None, e)
OperationalError: (OperationalError) (2003, Can't connect to MySQL
server on 'xxx.xxx.xxx.xxx' (111)) None None
I checked the userrights. Tried with root and installed sqlalchemy and
python-bindings for mysql on the server. I also tried on several
servers with different OS-versions, but I can't see anything wrong.
Manual logins with mysql-client to the servers work.
Can anybody give any pointers how to solve this?
--
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: Multiple table query ?
Excerpts from gsandorx's message of Tue Dec 22 16:03:44 -0300 2009: Hi Mariano, I tried your code and it worked great. Now I have to look a way of linking the union query with a mapper to obtain a rather elaborate object than a raw tuple, I mean, when you execute: q = session.query(Computer).all() you get Computer objects, but when you execute the union query you get tuples. Although they look like tuples, they're actually RowProxies and they provide some useful methods for solving this kind of situations. For more on RowProxy, please see [1] When looking at the resulting tuples, I noticed that those tuples that represent Computer objects contains a field equal to None due to the extra column added in the query. I guess that field could be used somehow to distinguish between Computer-like tuples and CompSetComputer-like ones. I used a null value just a convenience to show the possible solution. Actually you could use whatever you want in there: the bottom problem was that you cannot union two queries with different quantities of columns. I'm telling you this b/c I would like to know if there is a mechanism to link a method to a mapper. This way, the method would process the union query, and would create a list of Computer and CompSetComputer objects. Do I make myself clear? Thanks for your help, Sandor Off the top of my head, I could suggest a factory to build the objects. This example, of course, has nothing to do with the ORM. Maybe more tech savy guys can suggest better options. For example: class UnionComputers(object): You can extend this class with whatever methods you would like def __init__(self, **kwargs): for every in kwargs.items(): setattr(self, every[0], every[1]) class UnionFactory(object): Factory is maybe too big of a name since we're building just one type of object @staticmethod def return_inst(l_inst): for every in l_inst: yield UnionComputers(**dict([(str(x[0]), x[1]) for x in every.items()])) factory = UnionFactory() gen = factory.return_inst(session.execute(q3).fetchall()) while 1: v = gen.next() print(type(v), v.ip) [1] http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/connections.html#sqlalchemy.engine.base.RowProxy -- 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] easy_install SQLAlchemy
Excerpts from Tefnet Developers - Tomasz Jezierski's message of Tue Dec 01 19:06:31 -0300 2009: Dnia 2009-12-01, Wt o godzinie 18:45 +0100, Alexandre Conrad pisze: Hey there, just wondering: easy_install SQLAlchemy downloads and installs version 0.5.2. Shouldn't it be 0.5.6? # easy_install SQLAlchemy Searching for SQLAlchemy Reading http://pypi.python.org/simple/SQLAlchemy/ Reading http://www.sqlalchemy.org Best match: SQLAlchemy 0.5.6 Downloading http://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-0.5.6.tar.gz#md5= 65c6d2007969bf5d70307afc65687391 Processing SQLAlchemy-0.5.6.tar.gz Running SQLAlchemy-0.5.6/setup.py -q bdist_egg --dist-dir /tmp/easy_install-biunCq/SQLAlchemy-0.5.6/egg-dist-tmp-S--F9w no previously-included directories found matching 'doc/build/output' zip_safe flag not set; analyzing archive contents... sqlalchemy.databases.mysql: module MAY be using inspect.stack sqlalchemy.test.orm: module MAY be using inspect.getframeinfo sqlalchemy.test.orm: module MAY be using inspect.stack Adding SQLAlchemy 0.5.6 to easy-install.pth file Installed /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg Processing dependencies for SQLAlchemy Finished processing dependencies for SQLAlchemy It seems it really depends on something related with easy_install (I'm not sure what): I run the process for SqlAlchemy from a virtual environ using python 2.6 and it tried to install 0.5.6 I then deactivate the virtualenv and tried from the easy_install from distribution (Ubuntu 9.04, python2.6) and told me the best match was 0.5.3 BTW, you can always do easy_install SqlAlchemy==0.5.6 Mariano -- 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] Weird error with update
Excerpts from Mike Conley's message of Sat Nov 21 03:36:07 -0300 2009:
On Fri, Nov 20, 2009 at 5:14 PM, Mariano Mara mariano.m...@gmail.comwrote:
... or, at least, is weird for me :)
Hi everyone. I'm running a pylons controller
with the following instruction:
meta.Session.query(ESMagicNumber).filter(
ESMagicNumber.uuid==request.params['uuid_']).\
update({'last_access':datetime.datetime.now()})
but I'm getting the following error:
(ProgrammingError) syntax error at or near WHERE
LINE 1: UPDATE es_magicnumber SET WHERE es_magicnumber.uuid = E'705...
^ 'UPDATE es_magicnumber SET WHERE es_magicnumber.uuid = %(uuid_1)s'
{'uuid_1': u'705fa1e6-977f-416a-9847-fe8715cc920a'}
I am able to create the same bad SQL, but only if the key in the update
dictionary is not a column in the table being updated. Are you sure
'last_access' is a valid column in ESMagicNumber?
This is because the SET clause is generated from the update dictionary, and
if there are no valid columns, there is nothing to generate and the SET
clause becomes nothing.
I checked that first but turns out I was checking the wrong file :(
Somebody should not work past midnight.
Thank you very much for your answer and sorry for the waste of time.
Mariano
--
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=.
[sqlalchemy] Weird error with update
... or, at least, is weird for me :)
Hi everyone. I'm running a pylons controller
with the following instruction:
meta.Session.query(ESMagicNumber).filter(
ESMagicNumber.uuid==request.params['uuid_']).\
update({'last_access':datetime.datetime.now()})
but I'm getting the following error:
(ProgrammingError) syntax error at or near WHERE
LINE 1: UPDATE es_magicnumber SET WHERE es_magicnumber.uuid = E'705...
^ 'UPDATE es_magicnumber SET WHERE es_magicnumber.uuid = %(uuid_1)s'
{'uuid_1': u'705fa1e6-977f-416a-9847-fe8715cc920a'}
can anyone tell me why the SET instruction is being totally ignored?
Did I misunderstand how update works?
TIA,
Mariano
--
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=.
[sqlalchemy] outer join with two clauses
Hi everyone, how do I use two clauses in an outerjoin? e.g.: select ... from t1 outer join t2 on (t1.c1=t2.c1 and t1.c2t2.c2) According to docs, outerjoin is outerjoin(left, right, onclause=None) so I don't really know where to place the second clause. In a query object, outerjoin is different: outerjoin(*props, **kwargs) although I couldn't find much documentation about it. TIA, Mariano. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
