[sqlalchemy] selecting from a relationship
Hi,
I have a model similar to the following:
class Employee(Base):
__tablename__ = t_employee
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
first_name = sa.Column(sa.String(30))
last_name = sa.Column(sa.String(30))
phone_number = sa.Column(sa.String(30))
_jobs = sa.orm.relationship(EmployeeJob, lazy=joined, cascade=all,
delete, delete-orphan)
@property
def name(self):
return self.first_name + ( + self.last_name if
len(self.last_name or ) 0 else )
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
@jobs.setter
def jobs(self, value):
self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)]
class EmployeeJob(Base):
id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id',
ondelete=CASCADE), nullable=False)
job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id',
ondelete=CASCADE), nullable=False)
Now, I'm trying to write a simple query that will fetch all employees with
their jobs.
As I understand, I need to use joinedload so that the list of jobs will be
eagerly loaded but I can't understand how to do it.
I tried the following:
session.query(Employee.id).options(sa.orm.joinedload(Employee.jobs))
but it doesn't work.
Just to clarify, I want to load some of the columns, not all of them, and
I'm expecting to get the list of jobs for each employee (hopefully like the
getter produces them)
session.query(Employee) does fetch the required information but it selects
some unneeded columns
Also, how do I select the name property?
Thanks,
Ofir
--
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] selecting from a relationship
On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas herz...@gmail.com wrote:
Hi,
I have a model similar to the following:
class Employee(Base):
__tablename__ = t_employee
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
first_name = sa.Column(sa.String(30))
last_name = sa.Column(sa.String(30))
phone_number = sa.Column(sa.String(30))
_jobs = sa.orm.relationship(EmployeeJob, lazy=joined, cascade=all,
delete, delete-orphan)
@property
def name(self):
return self.first_name + ( + self.last_name if len(self.last_name
or ) 0 else )
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
@jobs.setter
def jobs(self, value):
self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)]
class EmployeeJob(Base):
id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id',
ondelete=CASCADE), nullable=False)
job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id',
ondelete=CASCADE), nullable=False)
Now, I'm trying to write a simple query that will fetch all employees with
their jobs.
As I understand, I need to use joinedload so that the list of jobs will be
eagerly loaded but I can't understand how to do it.
I tried the following:
session.query(Employee.id).options(sa.orm.joinedload(Employee.jobs))
but it doesn't work.
Just to clarify, I want to load some of the columns, not all of them, and
I'm expecting to get the list of jobs for each employee (hopefully like the
getter produces them)
session.query(Employee) does fetch the required information but it selects
some unneeded columns
Also, how do I select the name property?
Thanks,
Ofir
Eager loading means that when you have an instance of Employee, and
you access its 'jobs' property, no SQL is emitted because the data is
already available. This implies that you have to query for the
Employee class, not just one of its columns (otherwise you wouldn't
have an instance from which to access the jobs property)
If you don't want to load all the Employee columns, you can defer them:
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-column-loading
Hope that helps,
Simon
--
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: Selecting columns from multiple table join in SQLAlchemy Core
the first thing I noticed, is that you're referencing the idea of the tables in two separate ways : `self.execs` and `execs` are they the same python object? -- 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] Selecting columns from multiple table join in SQLAlchemy Core
I think the join.select(columns, whereclasue) part here is wrong, the select() method unfortunately does not have that calling signature (I wish it did, but there's a lot of legacy there). You can get the columns you want more explicitly, select([c1, c2, c3, ..]).select_from(my_join). The exception raised here is probably sqlalchemy being surprised by the list of columns being sent (the error message would do better to report on what it sees). On Sep 4, 2014, at 1:11 AM, apoorv.x.kulshres...@barclays.com wrote: Hi All, I am joining 3 tables in SQLAlchemy Core and selecting all columns as follows: rows = self.db.execute(self.execs.join( self.orders.join(self.instruments) ).select(whereClause)).reduce_columns()) It works well but if I want to select a subset of columns: reqdCols = [order.c.id, exec.c.last_modified, instruments.type] rows = self.db.execute(self.execs.join( self.orders.join(self.instruments) ).select(reqdCols, whereClause)).reduce_columns()) It doesn't works and gives following error: Traceback (most recent call last): File stdin, line 1, in module File /apps/qtdist/pkgs/pub/cpython/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py, line 807, in select return Select(collist, whereclause, from_obj=[self], **kwargs) File /apps/qtdist/pkgs/pub/cpython/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py, line 2219, in __init__ whereclause).self_group(against=operators._asbool) File /apps/qtdist/pkgs/pub/cpython/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/elements.py, line 3438, in _literal_as_text SQL expression object or string expected. sqlalchemy.exc.ArgumentError: SQL expression object or string expected. Alternative would be to use select instead of Join.select and make it implicit join with where clauses: joinConditions = (orders.c.colx == execs.colx) (execs.c.coly == instruments.c.coly) select(reqdCols).where(and_(whereClause, joinConditions) But I would prefer explicit join over implicit for performance reasons. Is there any way to select subset of columns using explicit joins? Regards, Apoorv PS: I have post the same in Stack Overflow Question: http://stackoverflow.com/questions/25656935/selecting-columns-from-mulitple-table-join-in-sqlalchemy-core# ___ This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer. For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please seehttp://publicresearch.barclays.com. ___ -- 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 tosqlalchemy+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] Wrapping polymorphic model for Pyramid traversal
this is probably more of a Pyramid question.
I'm pretty allergic to traversal myself :)
On Sep 3, 2014, at 2:58 PM, Milo Toor milo.t...@gmail.com wrote:
Hi.
I am trying to wrap a polymorphic model so that it can act as a traversal
node in a Pyramid application:
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
# This is the class we wish to wrap
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
type = Column(String(20))
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': 'employee'
}
class Manager(Employee):
__mapper_args__ = {
'polymorphic_identity': 'manager'
}
class Engineer(Employee):
__mapper_args__ = {
'polymorphic_identity': 'engineer'
}
class EmployeeInTraversal(Employee):
Wraps the Employee class. This is to keep our models and our application
logic decoupled.
def __getitem__(self, key):
Make the employee behave like a traversal node.
:param key:
The traversal key. If asked for tasks return the appropriate root
factory
if key == 'tasks':
return 'TasksRootFactory()'
raise KeyError
# Create the tables
Base.metadata.create_all(engine)
# Create both a Manager and an Engineer
manager = Manager(name='Taylor')
engineer = Engineer(name='Sam')
session = Session()
session.add_all([manager, engineer])
session.commit()
# somewhere a request is made for /employee/1/tasks...
# Query for the engineer, somewhere in the EmployeeRootFactory
wrapped_engineer = session.query(EmployeeInTraversal).get(1)
# Traversal doing its thing. Here lies the trouble.
engineer_tasks = wrapped_engineer['tasks']
The last line of this code throws a TypeError with the message 'NoneType'
object has no attribute '__getitem__'. Nothing is turned up by the query to
EmployeeInTraversal. If the query is instead made with the Employee class, we
get a similar TypeError: 'Manager' object has no attribute '__getitem__'. So
in other words, querying EmployeeInTraversal returns nothing, but querying
Employee returns the object, although unwrapped.
The reason for this, near as I can tell, is that the EmployeeInTraversal
class is being interpreted as a subtype of Employee rather than as a wrapper
for the class. I attribute this to the polymorphic nature of the Employee
class, but at this point I'm really just banging my head against the wall. We
would very much like to keep our models and application logic separate, and
not embed traversal logic in the schema classes... Is there any way to wrap a
polymorphic class without the wrapper being interpreted as a sub-type?
--
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] Query filter NameError issue
Hi Gilles - if you've imported models, you need to refer to Series as models.Series, unless you imported Series individually which seems to not be the case. The second part of things, if you call query.filter(), you get a new Query object back, just like with most Query methods, until you do one of the following: iterate it, e.g. for x in query:, or call one of .all(), .one(), or .first(). On Aug 30, 2014, at 11:12 AM, Gilles Coulais gilles.coul...@gmail.com wrote: Hi all, I'm starting to have a look at sqlalchemy and Flask and have a problem I can't figure out. I have a set of models and am trying to play with the corresponding database in a python console. Here is my models file: http://snipurl.com/297pxcw The following query will execute as expected and return a list of Series objects, as present in the database from book_catalog import models, db series = models.Series.query.all() for serie in series: print serie.id, serie.name 1 Nikopol But executing the following query will return an error: nikopol = models.Series.query.filter(Series.name == 'Nikopol') Traceback (most recent call last): File stdin, line 1, in module NameError: name 'Series' is not defined I tried the following, which is executing, but returning a Query object (not exactly what I'm trying to do ;) ) nikopol = models.Series.query.filter(models.Series.name == 'Nikopol') print nikopol SELECT series.id AS series_id, series.name AS series_name FROM series WHERE series.name = :name_1 Can anyone explain what I'm doing wrong here? I'm using SqlAlchemy 0.9.7 sqlalchemy.__version__ '0.9.7' Thanks a lot in advance. Gilles -- 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.
[sqlalchemy] adding columns dynamically to an ORM class ?
is it possible to add a column to an ORM class after the engine and mapper have initialized? I have a maintenance/migration script that needs a new column on the table for the duration of the script. For a variety of reasons, I need to avoid altering the actual model. I found `append_column`, but that works on `model.TableClass.__table__`. -- 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: adding columns dynamically to an ORM class ?
nevermind, i overthought this. apparently this is all i needed: model.TableClass.temp_column = sqlalchemy.Column(sqlalchemy.Integer) -- 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] selecting from a relationship
Thanks Simon,
I've tried the following:
session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only(
'id', 'first_name')).all()
which according to the documentation (
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loading-with-multiple-entities)
should work, but it throws an exception (ArgumentError: mapper option
expects string key or list of attributes)
Can you please provide an example?
Please notice that I'm trying to load only some properties of Employee
(including one relationship) while this behavior should not be the default
behavior (meaning that I don't want the defer the columns at model level)
On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote:
On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas her...@gmail.com
javascript: wrote:
Hi,
I have a model similar to the following:
class Employee(Base):
__tablename__ = t_employee
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
first_name = sa.Column(sa.String(30))
last_name = sa.Column(sa.String(30))
phone_number = sa.Column(sa.String(30))
_jobs = sa.orm.relationship(EmployeeJob, lazy=joined,
cascade=all,
delete, delete-orphan)
@property
def name(self):
return self.first_name + ( + self.last_name if
len(self.last_name
or ) 0 else )
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
@jobs.setter
def jobs(self, value):
self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)]
class EmployeeJob(Base):
id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' %
__tablename__),
primary_key=True, nullable=False)
employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id',
ondelete=CASCADE), nullable=False)
job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id',
ondelete=CASCADE), nullable=False)
Now, I'm trying to write a simple query that will fetch all employees
with
their jobs.
As I understand, I need to use joinedload so that the list of jobs will
be
eagerly loaded but I can't understand how to do it.
I tried the following:
session.query(Employee.id).options(sa.orm.joinedload(Employee.jobs))
but it doesn't work.
Just to clarify, I want to load some of the columns, not all of them,
and
I'm expecting to get the list of jobs for each employee (hopefully like
the
getter produces them)
session.query(Employee) does fetch the required information but it
selects
some unneeded columns
Also, how do I select the name property?
Thanks,
Ofir
Eager loading means that when you have an instance of Employee, and
you access its 'jobs' property, no SQL is emitted because the data is
already available. This implies that you have to query for the
Employee class, not just one of its columns (otherwise you wouldn't
have an instance from which to access the jobs property)
If you don't want to load all the Employee columns, you can defer them:
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-column-loading
Hope that helps,
Simon
--
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] selecting from a relationship
load_only indicates the columns in Employee.jobs that you want to load. 'first_name' is located on Employee, not EmployeeJob -- 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] selecting from a relationship
On Sep 4, 2014, at 12:28 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Simon,
I've tried the following:
session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only('id',
'first_name')).all()
which according to the documentation
(http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loading-with-multiple-entities)
should work, but it throws an exception (ArgumentError: mapper option
expects string key or list of attributes)
Can you please provide an example?
check your SQLAlchemy version, I cannot reproduce that issue:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
jobs = relationship(Job)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
first_name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()
output:
SELECT employee.id AS employee_id, job_1.id AS job_1_id, job_1.first_name AS
job_1_first_name
FROM employee LEFT OUTER JOIN job AS job_1 ON employee.id = job_1.employee_id
Please notice that I'm trying to load only some properties of Employee
(including one relationship) while this behavior should not be the default
behavior (meaning that I don't want the defer the columns at model level)
On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote:
On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas her...@gmail.com wrote:
Hi,
I have a model similar to the following:
class Employee(Base):
__tablename__ = t_employee
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
first_name = sa.Column(sa.String(30))
last_name = sa.Column(sa.String(30))
phone_number = sa.Column(sa.String(30))
_jobs = sa.orm.relationship(EmployeeJob, lazy=joined, cascade=all,
delete, delete-orphan)
@property
def name(self):
return self.first_name + ( + self.last_name if
len(self.last_name
or ) 0 else )
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
@jobs.setter
def jobs(self, value):
self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)]
class EmployeeJob(Base):
id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id',
ondelete=CASCADE), nullable=False)
job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id',
ondelete=CASCADE), nullable=False)
Now, I'm trying to write a simple query that will fetch all employees with
their jobs.
As I understand, I need to use joinedload so that the list of jobs will be
eagerly loaded but I can't understand how to do it.
I tried the following:
session.query(Employee.id).options(sa.orm.joinedload(Employee.jobs))
but it doesn't work.
Just to clarify, I want to load some of the columns, not all of them, and
I'm expecting to get the list of jobs for each employee (hopefully like the
getter produces them)
session.query(Employee) does fetch the required information but it selects
some unneeded columns
Also, how do I select the name property?
Thanks,
Ofir
Eager loading means that when you have an instance of Employee, and
you access its 'jobs' property, no SQL is emitted because the data is
already available. This implies that you have to query for the
Employee class, not just one of its columns (otherwise you wouldn't
have an instance from which to access the jobs property)
If you don't want to load all the Employee columns, you can defer them:
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-column-loading
Hope that helps,
Simon
--
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
RE: [sqlalchemy] selecting from a relationship
Thanks Michael,
I'm using 0.9.7 and while your example did work, the following did not:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
first_name = Column(String)
_jobs = relationship(EmployeeJob, lazy=joined)
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
class EmployeeJob(Base):
__tablename__ = employee_job
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
job_id = Column(Integer, ForeignKey('job.id'))
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()
Notice that the difference here is that I'm using a property decorator on
jobs, there is a link table (EmployeeJob), and I'm trying to load
information of Employee (first_name should be from there)
Thanks,
Ofir
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:00 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship
On Sep 4, 2014, at 12:28 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Simon,
I've tried the following:
session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only('
id', 'first_name')).all()
which according to the documentation
(http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loadi
ng-with-multiple-entities) should work, but it throws an exception
(ArgumentError: mapper option expects string key or list of attributes)
Can you please provide an example?
check your SQLAlchemy version, I cannot reproduce that issue:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
jobs = relationship(Job)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
first_name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()
output:
SELECT employee.id AS employee_id, job_1.id AS job_1_id, job_1.first_name AS
job_1_first_name
FROM employee LEFT OUTER JOIN job AS job_1 ON employee.id =
job_1.employee_id
Please notice that I'm trying to load only some properties of Employee
(including one relationship) while this behavior should not be the default
behavior (meaning that I don't want the defer the columns at model level)
On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote:
On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas her...@gmail.com javascript:
wrote:
Hi,
I have a model similar to the following:
class Employee(Base):
__tablename__ = t_employee
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
first_name = sa.Column(sa.String(30))
last_name = sa.Column(sa.String(30))
phone_number = sa.Column(sa.String(30))
_jobs = sa.orm.relationship(EmployeeJob, lazy=joined,
cascade=all,
delete, delete-orphan)
@property
def name(self):
return self.first_name + ( + self.last_name if
len(self.last_name
or ) 0 else )
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
@jobs.setter
def jobs(self, value):
self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)]
class EmployeeJob(Base):
id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' %
__tablename__),
primary_key=True, nullable=False)
employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id
http://t_employee.id/ ',
ondelete=CASCADE), nullable=False)
job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id
http://t_job.id/ ',
ondelete=CASCADE), nullable=False)
Now, I'm trying to write a simple query that will fetch all employees with
their jobs.
As I understand, I need to use joinedload so that the list of jobs will be
eagerly loaded but I can't understand how to do it.
I tried the following:
session.query(Employee.id).options(sa.orm.joinedload(Employee.jobs))
but it doesn't work.
Just to clarify, I want to
Re: [sqlalchemy] selecting from a relationship
On Sep 4, 2014, at 1:32 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Michael,
I'm using 0.9.7 and while your example did work, the following did not:
Ok that's not a relationship(). joinedload() only works with relationships.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
first_name = Column(String)
_jobs = relationship(EmployeeJob, lazy=joined)
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
class EmployeeJob(Base):
__tablename__ = employee_job
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
job_id = Column(Integer, ForeignKey('job.id'))
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()
Notice that the difference here is that I'm using a property decorator on
jobs, there is a link table (EmployeeJob), and I'm trying to load information
of Employee (first_name should be from there)
Thanks,
Ofir
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:00 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship
On Sep 4, 2014, at 12:28 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Simon,
I've tried the following:
session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only('id','first_name')).all()
which according to the documentation
(http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loading-with-multiple-entities)
should work, but it throws an exception (ArgumentError: mapper option
expects string key or list of attributes)
Can you please provide an example?
check your SQLAlchemy version, I cannot reproduce that issue:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
jobs = relationship(Job)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
first_name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()
output:
SELECT employee.id AS employee_id, job_1.id AS job_1_id, job_1.first_name AS
job_1_first_name
FROM employee LEFT OUTER JOIN job AS job_1 ON employee.id = job_1.employee_id
Please notice that I'm trying to load only some properties of Employee
(including one relationship) while this behavior should not be the default
behavior (meaning that I don't want the defer the columns at model level)
On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote:
On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas her...@gmail.com wrote:
Hi,
I have a model similar to the following:
class Employee(Base):
__tablename__ = t_employee
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
first_name = sa.Column(sa.String(30))
last_name = sa.Column(sa.String(30))
phone_number = sa.Column(sa.String(30))
_jobs = sa.orm.relationship(EmployeeJob, lazy=joined, cascade=all,
delete, delete-orphan)
@property
def name(self):
return self.first_name + ( + self.last_name if
len(self.last_name
or ) 0 else )
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
@jobs.setter
def jobs(self, value):
self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)]
class EmployeeJob(Base):
id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id',
ondelete=CASCADE), nullable=False)
job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id',
ondelete=CASCADE), nullable=False)
Now, I'm trying to write a simple query that will fetch all employees with
their jobs.
As I understand, I need to use joinedload so that the list of jobs will be
eagerly loaded but I can't understand
Re: [sqlalchemy] Wrapping polymorphic model for Pyramid traversal
OK, thanks for the reply Mike!
On Thursday, September 4, 2014 8:45:24 AM UTC-7, Michael Bayer wrote:
this is probably more of a Pyramid question.
I’m pretty allergic to traversal myself :)
On Sep 3, 2014, at 2:58 PM, Milo Toor milo...@gmail.com javascript:
wrote:
Hi.
I am trying to wrap a polymorphic model so that it can act as a traversal
node in a Pyramid application:
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
# This is the class we wish to wrap
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
type = Column(String(20))
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': 'employee'
}
class Manager(Employee):
__mapper_args__ = {
'polymorphic_identity': 'manager'
}
class Engineer(Employee):
__mapper_args__ = {
'polymorphic_identity': 'engineer'
}
class EmployeeInTraversal(Employee):
Wraps the Employee class. This is to keep our models and our
application
logic decoupled.
def __getitem__(self, key):
Make the employee behave like a traversal node.
:param key:
The traversal key. If asked for tasks return the appropriate
root
factory
if key == 'tasks':
return 'TasksRootFactory()'
raise KeyError
# Create the tables
Base.metadata.create_all(engine)
# Create both a Manager and an Engineer
manager = Manager(name='Taylor')
engineer = Engineer(name='Sam')
session = Session()
session.add_all([manager, engineer])
session.commit()
# somewhere a request is made for /employee/1/tasks...
# Query for the engineer, somewhere in the EmployeeRootFactory
wrapped_engineer = session.query(EmployeeInTraversal).get(1)
# Traversal doing its thing. Here lies the trouble.
engineer_tasks = wrapped_engineer['tasks']
The last line of this code throws a TypeError with the message *'NoneType'
object has no attribute '__getitem__'*. Nothing is turned up by the query
to EmployeeInTraversal. If the query is instead made with the Employee
class, we get a similar TypeError: *'Manager' object has no attribute
'__getitem__'*. So in other words, querying EmployeeInTraversal returns
nothing, but querying Employee returns the object, although unwrapped.
The reason for this, near as I can tell, is that the EmployeeInTraversal
class is being interpreted as a subtype of Employee rather than as a
wrapper for the class. I attribute this to the polymorphic nature of the
Employee class, but at this point I'm really just banging my head against
the wall. We would very much like to keep our models and application logic
separate, and not embed traversal logic in the schema classes... Is there
any way to wrap a polymorphic class without the wrapper being interpreted
as a sub-type?
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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] selecting from a relationship
Nevertheless, is there a way to achieve what I want? (which is to
selectively load several columns and this 'jobs' property from Employee)
Thanks,
Ofir
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:42 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship
On Sep 4, 2014, at 1:32 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Michael,
I'm using 0.9.7 and while your example did work, the following did not:
Ok that's not a relationship(). joinedload() only works with relationships.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
first_name = Column(String)
_jobs = relationship(EmployeeJob, lazy=joined)
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
class EmployeeJob(Base):
__tablename__ = employee_job
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
job_id = Column(Integer, ForeignKey('job.id'))
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()
Notice that the difference here is that I'm using a property decorator on
jobs, there is a link table (EmployeeJob), and I'm trying to load
information of Employee (first_name should be from there)
Thanks,
Ofir
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:00 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship
On Sep 4, 2014, at 12:28 PM, Ofir Herzas mailto:herz...@gmail.com
herz...@gmail.com wrote:
Thanks Simon,
I've tried the following:
session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only('
id','first_name')).all()
which according to the documentation (
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loadi
ng-with-multiple-entities
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loadin
g-with-multiple-entities) should work, but it throws an exception
(ArgumentError: mapper option expects string key or list of attributes)
Can you please provide an example?
check your SQLAlchemy version, I cannot reproduce that issue:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
jobs = relationship(Job)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
first_name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()
output:
SELECT employee.id AS employee_id, job_1.id AS job_1_id, job_1.first_name AS
job_1_first_name
FROM employee LEFT OUTER JOIN job AS job_1 ON employee.id =
job_1.employee_id
Please notice that I'm trying to load only some properties of Employee
(including one relationship) while this behavior should not be the default
behavior (meaning that I don't want the defer the columns at model level)
On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote:
On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas her...@gmail.com wrote:
Hi,
I have a model similar to the following:
class Employee(Base):
__tablename__ = t_employee
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
first_name = sa.Column(sa.String(30))
last_name = sa.Column(sa.String(30))
phone_number = sa.Column(sa.String(30))
_jobs = sa.orm.relationship(EmployeeJob, lazy=joined,
cascade=all,
delete, delete-orphan)
@property
def name(self):
return self.first_name + ( + self.last_name if
len(self.last_name
or ) 0 else )
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
@jobs.setter
def jobs(self, value):
self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)]
class EmployeeJob(Base):
id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' %
Re: [sqlalchemy] selecting from a relationship
You need to join along the actual relationships between your classes. You've
got this:
Employee._jobs - EmployeeJob.? - Job
(I assume EmployeeJob has a job relationship to Job.)
I think you probably want something like this:
(session.query(Employee)
.options(load_only('id', 'first_name'),
joinedload(_jobs).joinedload(job))
).all()
Note that load_only is a separate option. The way you had it:
joinedload(Employee._jobs).load_only('id', 'first_name')
...would be looking for 'id' and 'first_name' columns on the EmployeeJob object.
There are lots of examples at:
http://docs.sqlalchemy.org/en/latest/orm/loading.html
Hope that helps,
Simon
On 4 Sep 2014, at 19:17, Ofir Herzas herz...@gmail.com wrote:
Nevertheless, is there a way to achieve what I want? (which is to selectively
load several columns and this 'jobs' property from Employee)
Thanks,
Ofir
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:42 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship
On Sep 4, 2014, at 1:32 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Michael,
I'm using 0.9.7 and while your example did work, the following did not:
Ok that's not a relationship(). joinedload() only works with relationships.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
first_name = Column(String)
_jobs = relationship(EmployeeJob, lazy=joined)
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
class EmployeeJob(Base):
__tablename__ = employee_job
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
job_id = Column(Integer, ForeignKey('job.id'))
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()
Notice that the difference here is that I'm using a property decorator on
jobs, there is a link table (EmployeeJob), and I'm trying to load information
of Employee (first_name should be from there)
Thanks,
Ofir
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:00 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship
On Sep 4, 2014, at 12:28 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Simon,
I've tried the following:
session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only('id','first_name')).all()
which according to the documentation
(http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loading-with-multiple-entities)
should work, but it throws an exception (ArgumentError: mapper option
expects string key or list of attributes)
Can you please provide an example?
check your SQLAlchemy version, I cannot reproduce that issue:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
jobs = relationship(Job)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
first_name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()
output:
SELECT employee.id AS employee_id, job_1.id AS job_1_id, job_1.first_name AS
job_1_first_name
FROM employee LEFT OUTER JOIN job AS job_1 ON employee.id = job_1.employee_id
Please notice that I'm trying to load only some properties of Employee
(including one relationship) while this behavior should not be the default
behavior (meaning that I don't want the defer the columns at model level)
On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote:
On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas her...@gmail.com wrote:
Hi,
I have a model similar to the following:
class Employee(Base):
__tablename__ = t_employee
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
first_name = sa.Column(sa.String(30))
RE: [sqlalchemy] selecting from a relationship
Thanks Simon, it worked!
I did the whole thing just because I had an sql select query talking 1.5
seconds to complete (several joins and many properties, just 1000 records),
and this change reduced it to 1.1 seconds (I guess I'll have to find a
better way to improve performance)
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Simon King
Sent: Thursday, September 04, 2014 11:14 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship
You need to join along the actual relationships between your classes. You've
got this:
Employee._jobs - EmployeeJob.? - Job
(I assume EmployeeJob has a job relationship to Job.)
I think you probably want something like this:
(session.query(Employee)
.options(load_only('id', 'first_name'),
joinedload(_jobs).joinedload(job))
).all()
Note that load_only is a separate option. The way you had it:
joinedload(Employee._jobs).load_only('id', 'first_name')
...would be looking for 'id' and 'first_name' columns on the EmployeeJob
object.
There are lots of examples at:
http://docs.sqlalchemy.org/en/latest/orm/loading.html
Hope that helps,
Simon
On 4 Sep 2014, at 19:17, Ofir Herzas herz...@gmail.com wrote:
Nevertheless, is there a way to achieve what I want? (which is to
selectively load several columns and this 'jobs' property from
Employee)
Thanks,
Ofir
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:42 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship
On Sep 4, 2014, at 1:32 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Michael,
I'm using 0.9.7 and while your example did work, the following did not:
Ok that's not a relationship(). joinedload() only works with
relationships.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
first_name = Column(String)
_jobs = relationship(EmployeeJob, lazy=joined)
@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]
class EmployeeJob(Base):
__tablename__ = employee_job
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
job_id = Column(Integer, ForeignKey('job.id'))
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('i
d', 'first_name')).all()
Notice that the difference here is that I'm using a property decorator
on jobs, there is a link table (EmployeeJob), and I'm trying to load
information of Employee (first_name should be from there)
Thanks,
Ofir
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
On Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:00 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship
On Sep 4, 2014, at 12:28 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Simon,
I've tried the following:
session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_
only('id','first_name')).all()
which according to the documentation
(http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred
-loading-with-multiple-entities) should work, but it throws an
exception (ArgumentError: mapper option expects string key or list of
attributes)
Can you please provide an example?
check your SQLAlchemy version, I cannot reproduce that issue:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
jobs = relationship(Job)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey('employee.id'))
first_name = Column(String)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
session = Session(e)
session.query(Employee).options(joinedload(Employee.jobs).load_only('i
d', 'first_name')).all()
output:
SELECT employee.id AS employee_id, job_1.id AS job_1_id,
job_1.first_name AS job_1_first_name FROM employee LEFT OUTER JOIN job
AS job_1 ON employee.id = job_1.employee_id
Please notice that I'm trying to load only some properties of Employee
(including one
Re: [sqlalchemy] selecting from a relationship
On Thursday, September 4, 2014 2:19:34 PM UTC-4, Ofir Herzas wrote: Nevertheless, is there a way to achieve what I want? (which is to selectively load several columns and this 'jobs' property from Employee) The ways I usually do that are: 1. Select the other object. Query for EmployeeJob, and then load only the Employee columns. 2. Explicitly load columns. I'm not sure if this will work for relationships: session.query( TableClass.column_a, TableClass.column_b).all() instead of returning an ORM object, you get a tuple. 3. Define a column as deferred in the mapper [ http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#deferred-column-loading] Looking at your example though, unless you have very high traffic -- you're not going to have a significant performance gain with a query like that. Your tables don't have many columns, and they're not very large. People are usually concerned about load_only when you have very large fields (BLOBS, TEXT, HSTORE) or complex joins and need to save memory. You might just be better off letting the ORM load stuff you don't need -- you'll get much more code done that way. -- 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] selecting from a relationship
Thanks Jonathan, I actually have more properties and relationships which were removed for the sake of the example. You are right though, the performance gain was not as I hoped it would be. I'll have to think of other methods (caching maybe)... On Sep 4, 2014 11:50 PM, Jonathan Vanasco jonat...@findmeon.com wrote: On Thursday, September 4, 2014 2:19:34 PM UTC-4, Ofir Herzas wrote: Nevertheless, is there a way to achieve what I want? (which is to selectively load several columns and this 'jobs' property from Employee) The ways I usually do that are: 1. Select the other object. Query for EmployeeJob, and then load only the Employee columns. 2. Explicitly load columns. I'm not sure if this will work for relationships: session.query( TableClass.column_a, TableClass.column_b).all() instead of returning an ORM object, you get a tuple. 3. Define a column as deferred in the mapper [ http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#deferred-column-loading ] Looking at your example though, unless you have very high traffic -- you're not going to have a significant performance gain with a query like that. Your tables don't have many columns, and they're not very large. People are usually concerned about load_only when you have very large fields (BLOBS, TEXT, HSTORE) or complex joins and need to save memory. You might just be better off letting the ORM load stuff you don't need -- you'll get much more code done that way. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OprfrGJcoJU/unsubscribe. To unsubscribe from this group and all its topics, 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.
[sqlalchemy] Session.close() appears to infrequently not expunge all objects
I am using sqlalchemy 0.8.5 with mysql 5.5 and think an intermittent failure I am seeing may be due to an issue in sqlalchemy. Very infrequently I receive an error from Session.add() stating the instance is already attached to session 'XXX' (this is 'YYY') (see below for stack trace). I understand the typical reason this error is raised, but I do not believe the instance should still be attached to XXX, even though it was shortly before (milliseconds) since Session.close() was called on the session. A second session is created, and in the vast majority of cases the objects from the first session can be added to the second session without error, but occasionally (1% of the time) add() raises InvalidRequestError. My understanding of Session.close() is that upon return all instances should be detached from the session and their instance state session_id attribute should no longer reference the session. This does not appear to be happening since adding the instance to a different session fails since the instance is still attached to the previous session. In reading the code it appears as though it is expected in certain situations for the session_id to continue to refer to the session after close since the check for already attached checks session_id as well as whether the session remains in the sessions registry. I have created a patch to check not only the instance state reference to the session but also check whether the session still contains the instance before raising the InvalidRequestError, but I do not have a way to reproduce this issue outside our production system, and am very leery of hacking sqlalchemy for a production system with my limited understanding of session internals. I would appreciate any comments on possible causes of the symptoms I am seeing as well as comments on the wisdom of applying this patch and running it in a production system. I am in the process of upgrading to sqlalchemy 0.9.7 but that is at least a month away and hope to make traction on this issue before then. I also do not believe upgrading will help since there are no sqlalchemy bugs on this. TIA for any advice or suggestions you have. diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py index dcb68d6..b06e624 100644 --- a/lib/sqlalchemy/orm/session.py +++ b/lib/sqlalchemy/orm/session.py @@ -1749,10 +1749,13 @@ class Session(_SessionClassMethods): if state.session_id and \ state.session_id is not self.hash_key and \ state.session_id in _sessions: - raise sa_exc.InvalidRequestError( - Object '%s' is already attached to session '%s' - (this is '%s') % (orm_util.state_str(state), - state.session_id, self.hash_key)) + other_session = _sessions.get(state.session_id, None) + if other_session and (other_session.identity_map.contains_state(state) or + state in other_session._new): + raise sa_exc.InvalidRequestError( + Object '%s' is already attached to session '%s' + (this is '%s') % (orm_util.state_str(state), + state.session_id, self.hash_key)) if state.session_id != self.hash_key: if include_before and Stack trace: File snip, line 81, in quota_allocation_management yield File snip, line 235, in __init__ tx.add(self.configuration) File snip, line 35, in __call return attr(*args, **kwargs) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.8.5-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1399, in add self._save_or_update_state(state) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.8.5-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1418, in _save_or_update_state self._save_or_update_impl(st_) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.8.5-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1667, in _save_or_update_impl self._update_impl(state) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.8.5-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1661, in _update_impl self._attach(state) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.8.5-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1755, in _attach state.session_id, self.hash_key)) -- 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] Issue with return results
I had to reinstall my python dev environment from scratch due to a hd
failure, and in the process something seems to have changed.
When querying against MS SQL using the script (test_conenction.py):
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=server.ip.address;'
'DATABASE=STUDY_PARTICIPANT;UID=test;PWD=test;port=1433;CHARSET=utf8;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql+pyodbc://', creator=connect,
encoding='latin1',echo='debug',supports_unicode_binds=False)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A: ' + table_name
I used to get the following nice output:
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col (
'default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7 as [
Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT [TABLES_1
].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [TABLES_1].[
TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col (
'TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irb_desc'
, )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_status', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_status_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_subject', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'subj_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'subj_status_desc', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row (u
'subject_status', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row (u
'sysdiagrams', )
Now, in my updated environment, it looks like this:
python test_connect.py
2014-09-04 15:26:05,974 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-09-04 15:26:05,974 INFO sqlalchemy.engine.base.Engine ()
2014-09-04 15:26:05,976 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-09-04 15:26:05,976 DEBUG sqlalchemy.engine.base.Engine Row
(u'd\x00b\x00o\x00', )
2014-09-04 15:26:05,978 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-09-04 15:26:05,978 INFO sqlalchemy.engine.base.Engine ()
2014-09-04 15:26:05,981 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2014-09-04 15:26:05,981 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x10fda1510
2014-09-04 15:26:06,000 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-09-04 15:26:06,001 INFO sqlalchemy.engine.base.Engine ()
2014-09-04 15:26:06,002 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-09-04 15:26:06,002 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-09-04 15:26:06,007 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-09-04
[sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
So I have a few tables as follows (abbreviated for unnecessary columns):
class Project(Base):
__tablename__ = 'projects'
id = Column(u'project_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
lazy=False,
)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class PackageDefinition(Base):
__tablename__ = 'package_definitions'
id = Column(u'pkg_def_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class AppDefinition(Base):
__tablename__ = 'app_definitions'
id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class ProjectPackage(Base):
__tablename__ = 'project_package'
project_id = Column(
INTEGER(),
ForeignKey('projects.project_id', ondelete='cascade'),
primary_key=True
)
pkg_def_id = Column(
INTEGER(),
ForeignKey('package_definitions.pkg_def_id', ondelete='cascade'),
primary_key=True
)
app_id = Column(
SMALLINT(display_width=6),
ForeignKey('app_definitions.AppID', ondelete='cascade'),
primary_key=True
)
app_definition = relationship('AppDefinition', uselist=False)
package_definition = relationship('PackageDefinition', uselist=False)
project = relationship('Project', uselist=False)
If I select a row from the projects table and try to delete it, when I try
to commit
the session I get something like this:
[INFO] DELETE FROM project_package WHERE project_package.project_id = ? AND
project_package.app_id = ?
[INFO] (1L, 1L)
[INFO] DELETE FROM project_package WHERE project_package.project_id = ? AND
project_package.pkg_def_id = ?
[INFO] (1L, 1L)
[INFO] ROLLBACK
At the time of the delete, the project_package table simply has this:
[('project_id', 1L), ('pkg_def_id', 1L), ('app_id', 1L)]
Obviously the combination of the ondelete='cascade' settings in the
project_package table and the 'passive_deletes' settings in the
relationships
is what's triggering this, but after try many different searches on Google,
I'm
not finding the right solution to fix this; obviously I only want it to
attempt to
delete the row once, but each relationship in project is triggering its own
delete which is bad.
So... help? Am I missing something simple here, or is my model not good
enough to do the right thing?
--
- Ken Lareau
--
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] Session.close() appears to infrequently not expunge all objects
On Sep 4, 2014, at 5:00 PM, Lonnie Hutchinson lonn...@skytap.com wrote: I am using sqlalchemy 0.8.5 with mysql 5.5 and think an intermittent failure I am seeing may be due to an issue in sqlalchemy. Very infrequently I receive an error from Session.add() stating the instance is already attached to session 'XXX' (this is 'YYY') (see below for stack trace). I understand the typical reason this error is raised, but I do not believe the instance should still be attached to XXX, even though it was shortly before (milliseconds) since Session.close() was called on the session. OK I'm seeing some red flags here. I see the word intermittent, which almost always means, multiple threads. Then I see the word, milliseconds. Are we using threads? Are we trying to time things? Because looking at 0.8.5, when you call close(), the objects in that Session are absolutely de-assocaited with that Session, including that session_id is set to None. This is very simple to confirm: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) e = create_engine(sqlite://, echo=True) Base.metadata.create_all(e) sess = Session(e) a1 = A() sess.add(a1) sess.commit() assert a1._sa_instance_state.session_id == sess.hash_key sess.close() assert a1._sa_instance_state.session_id is None there's no milliseconds here. close() completes, and that id is gone. If you are playing with threads, the only way to guarantee one thread finishes something before the other starts is to either join() that thread or use mutexes. My understanding of Session.close() is that upon return all instances should be detached from the session and their instance state session_id attribute should no longer reference the session. This does not appear to be happening since adding the instance to a different session fails since the instance is still attached to the previous session. Above I illustrate that this is not the case. The code path is clear: session.py - line 942 - close() calls: session.py - line 952 - self.expunge_all() session.py - line 965 calls state._detach() on everything in identity + _new. (question. are you trying to move an object that was deleted? that might have issues, but that's not a valid use case). state.py - line 166 - _detach() calls: state.py - line 167: self.session_id = self._strong_obj = None In reading the code it appears as though it is expected in certain situations for the session_id to continue to refer to the session after close since the check for already attached checks session_id as well as whether the session remains in the sessions registry. I'm not seeing that in 0.8.5 at all.what line of code are you referring to ? -- 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] Issue with return results
SQL Server and unix, many things can change:
- UnixODBC version
- FreeTDS version (0.82 and 0.91 have *extremely* different behaviors)
- FreeTDS configuration
The first place I'd look in this case would be your freetds.conf, you probably
need to configure the character set correctly in there.
On Sep 4, 2014, at 5:06 PM, Horcle g...@umn.edu wrote:
I had to reinstall my python dev environment from scratch due to a hd
failure, and in the process something seems to have changed.
When querying against MS SQL using the script (test_conenction.py):
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=server.ip.address;'
'DATABASE=STUDY_PARTICIPANT;UID=test;PWD=test;port=1433;CHARSET=utf8;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql+pyodbc://', creator=connect,
encoding='latin1',echo='debug',supports_unicode_binds=False)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A: ' + table_name
I used to get the following nice output:
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]WHERE
[TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irb_desc',
)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_status', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_status_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_subject', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'subj_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'subj_status_desc', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
(u'subject_status', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
(u'sysdiagrams', )
Now, in my updated environment, it looks like this:
python test_connect.py
2014-09-04 15:26:05,974 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-09-04 15:26:05,974 INFO sqlalchemy.engine.base.Engine ()
2014-09-04 15:26:05,976 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-09-04 15:26:05,976 DEBUG sqlalchemy.engine.base.Engine Row
(u'd\x00b\x00o\x00', )
2014-09-04 15:26:05,978 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-09-04 15:26:05,978 INFO sqlalchemy.engine.base.Engine ()
2014-09-04 15:26:05,981 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-09-04 15:26:05,981 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x10fda1510
2014-09-04 15:26:06,000 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
Re: [sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
On Sep 4, 2014, at 5:37 PM, Ken Lareau klar...@tagged.com wrote:
So I have a few tables as follows (abbreviated for unnecessary columns):
class Project(Base):
__tablename__ = 'projects'
id = Column(u'project_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
lazy=False,
)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class PackageDefinition(Base):
__tablename__ = 'package_definitions'
id = Column(u'pkg_def_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class AppDefinition(Base):
__tablename__ = 'app_definitions'
id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class ProjectPackage(Base):
__tablename__ = 'project_package'
project_id = Column(
INTEGER(),
ForeignKey('projects.project_id', ondelete='cascade'),
primary_key=True
)
pkg_def_id = Column(
INTEGER(),
ForeignKey('package_definitions.pkg_def_id', ondelete='cascade'),
primary_key=True
)
app_id = Column(
SMALLINT(display_width=6),
ForeignKey('app_definitions.AppID', ondelete='cascade'),
primary_key=True
)
app_definition = relationship('AppDefinition', uselist=False)
package_definition = relationship('PackageDefinition', uselist=False)
project = relationship('Project', uselist=False)
this model has serious issues, though it can be made to hobble along with some
small changes. The three way association table is not a great pattern in the
first place. Additionally, using that same table as secondary in all those
places is clearly the issue here, and they don't even have back_populates
between them, which are used in many-to-many in order to try avoid double
deleting. But that wouldn't solve the issue here anyway.
In this case, you have mapped to the project_package table directly; you're
using the association object pattern. The note at the end of
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
applies here - you absolutely should have viewonly=True on all of those
relationship() directives that refer to this association table as the
secondary table. The ORM otherwise has no way to know what changes should
result in what deletions where.
--
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] Multiple delete attempts of same rows with a star schema configuration
On Thu, Sep 4, 2014 at 3:30 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Sep 4, 2014, at 5:37 PM, Ken Lareau klar...@tagged.com wrote:
So I have a few tables as follows (abbreviated for unnecessary columns):
class Project(Base):
__tablename__ = 'projects'
id = Column(u'project_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
lazy=False,
)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class PackageDefinition(Base):
__tablename__ = 'package_definitions'
id = Column(u'pkg_def_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class AppDefinition(Base):
__tablename__ = 'app_definitions'
id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class ProjectPackage(Base):
__tablename__ = 'project_package'
project_id = Column(
INTEGER(),
ForeignKey('projects.project_id', ondelete='cascade'),
primary_key=True
)
pkg_def_id = Column(
INTEGER(),
ForeignKey('package_definitions.pkg_def_id', ondelete='cascade'),
primary_key=True
)
app_id = Column(
SMALLINT(display_width=6),
ForeignKey('app_definitions.AppID', ondelete='cascade'),
primary_key=True
)
app_definition = relationship('AppDefinition', uselist=False)
package_definition = relationship('PackageDefinition', uselist=False)
project = relationship('Project', uselist=False)
this model has serious issues, though it can be made to hobble along with
some small changes. The “three way association table” is not a great
pattern in the first place. Additionally, using that same table as
“secondary” in all those places is clearly the issue here, and they don’t
even have back_populates between them, which are used in many-to-many in
order to try avoid “double deleting”. But that wouldn’t solve the issue
here anyway.
As I feared, I figured this model would be frowned upon. :) Note that this
model (or the underlying database)
is not set in stone; if you have suggestions on a better way to link the
three main tables, I would love to hear
it (and we did add back_populates (somehow missed them during the
restructuring of the models), but as
you said, didn't help in this case).
In this case, you have mapped to the project_package table directly;
you’re using the association object pattern. The note at the end of
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
applies here - you absolutely should have viewonly=True on all of those
relationship() directives that refer to this association table as the
“secondary” table. The ORM otherwise has no way to know what changes
should result in what deletions where.
Will add the 'viewonly' option as suggested and see if that helps, but as
mentioned above, if you can suggest
a better model for this case, please let me know!
--
- Ken Lareau
--
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] Multiple delete attempts of same rows with a star schema configuration
On Sep 4, 2014, at 6:58 PM, Ken Lareau klar...@tagged.com wrote:
On Thu, Sep 4, 2014 at 3:30 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Sep 4, 2014, at 5:37 PM, Ken Lareau klar...@tagged.com wrote:
So I have a few tables as follows (abbreviated for unnecessary columns):
class Project(Base):
__tablename__ = 'projects'
id = Column(u'project_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
lazy=False,
)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class PackageDefinition(Base):
__tablename__ = 'package_definitions'
id = Column(u'pkg_def_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class AppDefinition(Base):
__tablename__ = 'app_definitions'
id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class ProjectPackage(Base):
__tablename__ = 'project_package'
project_id = Column(
INTEGER(),
ForeignKey('projects.project_id', ondelete='cascade'),
primary_key=True
)
pkg_def_id = Column(
INTEGER(),
ForeignKey('package_definitions.pkg_def_id', ondelete='cascade'),
primary_key=True
)
app_id = Column(
SMALLINT(display_width=6),
ForeignKey('app_definitions.AppID', ondelete='cascade'),
primary_key=True
)
app_definition = relationship('AppDefinition', uselist=False)
package_definition = relationship('PackageDefinition', uselist=False)
project = relationship('Project', uselist=False)
this model has serious issues, though it can be made to hobble along with
some small changes. The three way association table is not a great pattern
in the first place. Additionally, using that same table as secondary in
all those places is clearly the issue here, and they don't even have
back_populates between them, which are used in many-to-many in order to try
avoid double deleting. But that wouldn't solve the issue here anyway.
As I feared, I figured this model would be frowned upon. :) Note that this
model (or the underlying database)
is not set in stone; if you have suggestions on a better way to link the
three main tables, I would love to hear
it (and we did add back_populates (somehow missed them during the
restructuring of the models), but as
you said, didn't help in this case).
A row in the 3-way table means that for this project_id, and pkg_def_id, and
app_id, that's a single allowed value, so that's star schema, OK (though they
call star schema denormalized - i don't exactly understand why, if you make
all the columns part of the primary key). But then you have functions that
give you lists of things based on two-column slices of that table, in order to
relate different dimensions to each other; projects-app_definitions, means you
can have Project (X) - AppDefnition(Y) any number of times, for every value of
pkg_def_id.I guess that's the awkward part here. Those aren't really
collection-based relationships so much as analytical queries across dimension
tables. They are valid queries but aren't really object/relational - when
we map a class to another with a many-to-many collection in between, that means
something more specific.
So maybe its just, object relational mapping assumes certain patterns are
mappable to objects and collections, and 3-way associations aren't quite in
that pattern :).
--
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] Multiple delete attempts of same rows with a star schema configuration
On Thu, Sep 4, 2014 at 4:35 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Sep 4, 2014, at 6:58 PM, Ken Lareau klar...@tagged.com wrote:
On Thu, Sep 4, 2014 at 3:30 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Sep 4, 2014, at 5:37 PM, Ken Lareau klar...@tagged.com wrote:
So I have a few tables as follows (abbreviated for unnecessary columns):
class Project(Base):
__tablename__ = 'projects'
id = Column(u'project_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
lazy=False,
)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class PackageDefinition(Base):
__tablename__ = 'package_definitions'
id = Column(u'pkg_def_id', INTEGER(), primary_key=True)
applications = relationship(
'AppDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class AppDefinition(Base):
__tablename__ = 'app_definitions'
id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
package_definitions = relationship(
'PackageDefinition',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
projects = relationship(
'Project',
secondary=lambda: Base.metadata.tables['project_package'],
passive_deletes=True,
)
class ProjectPackage(Base):
__tablename__ = 'project_package'
project_id = Column(
INTEGER(),
ForeignKey('projects.project_id', ondelete='cascade'),
primary_key=True
)
pkg_def_id = Column(
INTEGER(),
ForeignKey('package_definitions.pkg_def_id', ondelete='cascade'),
primary_key=True
)
app_id = Column(
SMALLINT(display_width=6),
ForeignKey('app_definitions.AppID', ondelete='cascade'),
primary_key=True
)
app_definition = relationship('AppDefinition', uselist=False)
package_definition = relationship('PackageDefinition', uselist=False)
project = relationship('Project', uselist=False)
this model has serious issues, though it can be made to hobble along with
some small changes. The “three way association table” is not a great
pattern in the first place. Additionally, using that same table as
“secondary” in all those places is clearly the issue here, and they don’t
even have back_populates between them, which are used in many-to-many in
order to try avoid “double deleting”. But that wouldn’t solve the issue
here anyway.
As I feared, I figured this model would be frowned upon. :) Note that
this model (or the underlying database)
is not set in stone; if you have suggestions on a better way to link the
three main tables, I would love to hear
it (and we did add back_populates (somehow missed them during the
restructuring of the models), but as
you said, didn't help in this case).
A row in the 3-way table means that for this project_id, and pkg_def_id,
and app_id, that’s a single allowed value, so that’s star schema, OK
(though they call star schema “denormalized” - i don’t exactly understand
why, if you make all the columns part of the primary key). But then you
have functions that give you lists of things based on two-column slices of
that table, in order to relate different dimensions to each other;
projects-app_definitions, means you can have Project (X) -
AppDefnition(Y) any number of times, for every value of pkg_def_id.I
guess that’s the awkward part here. Those aren’t really collection-based
“relationships” so much as analytical queries across dimension tables.
They are valid queries but aren’t really “object/relational” - when we map
a class to another with a many-to-many collection in between, that means
something more specific.
So maybe its just, object relational mapping assumes certain patterns are
mappable to objects and collections, and 3-way associations aren’t quite in
that pattern :).
Hmm, understood, though the main reason for these relationships were to
avoid having to make
DB queries within the main application code (pushing them out into the
models) and simplify the
logic; if you had seen what our application code looked like before, you
might shudder even more
than over what we're doing here. :)
Is there a way to essentially allow something like 'for app in
project.applications:' without having
to make an explicit query to the DB first, but avoiding the awkwardness of
the current setup?
--
- Ken Lareau
--
You received this message because you are
Re: [sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
On Sep 4, 2014, at 7:50 PM, Ken Lareau klar...@tagged.com wrote: Is there a way to essentially allow something like 'for app in project.applications:' without having to make an explicit query to the DB first, but avoiding the awkwardness of the current setup? just use the viewonly=True and we can all go home :) I'm not too upset about it -- 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] Session.close() appears to infrequently not expunge all objects
The session executes in one of many web-server threads, but there is no multi-threading with respect to the session or the objects. The session that was closed is within an initializer and upon return a method on the object is executed that creates a new session and tries to attach objects retrieved from the first session. This method does spawn threads but the exception happens before any threads are spawned. The thread target does not take objects, only python strings. only identifiers used in new sessions to query the objects again. The milliseconds I referred to are between the close() of the first session and the add() that fails in the second session. The code I say appears to expect the session_id to remain on detached states in certain situations is that the check in Session._attach checks not only the session_id but that the referenced session still exists in _sessions: if state.session_id and \ state.session_id is not self.hash_key and \ state.session_id in _sessions: raise sa_exc.InvalidRequestError( Object '%s' is already attached to session '%s' (this is '%s') % (orm_util.state_str(state), state.session_id, self.hash_key)) I am interested in this in the hopes that sheds light on the source of the intermittent failures. In debugging this I have noticed that after the first session has been closed() and the initializer has returned the session remains in _sessions. However, if I call gc.collect() the session is removed, suggesting it just hasn't been fully cleaned up yet. Since it takes both the state referencing the session and the session still existing in _sessions, I can't help but wonder if this is a gc issue. Unfortunately it is not feasible to add gc.collect() calls in our production application as it introduces too much overhead. On Thu, Sep 4, 2014 at 3:20 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 4, 2014, at 5:00 PM, Lonnie Hutchinson lonn...@skytap.com wrote: I am using sqlalchemy 0.8.5 with mysql 5.5 and think an intermittent failure I am seeing may be due to an issue in sqlalchemy. Very infrequently I receive an error from Session.add() stating the instance is already attached to session 'XXX' (this is 'YYY') (see below for stack trace). I understand the typical reason this error is raised, but I do not believe the instance should still be attached to XXX, even though it was shortly before (milliseconds) since Session.close() was called on the session. OK I'm seeing some red flags here. I see the word intermittent, which almost always means, multiple threads. Then I see the word, milliseconds. Are we using threads? Are we trying to time things? Because looking at 0.8.5, when you call close(), the objects in that Session are absolutely de-assocaited with that Session, including that session_id is set to None. This is very simple to confirm: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) e = create_engine(sqlite://, echo=True) Base.metadata.create_all(e) sess = Session(e) a1 = A() sess.add(a1) sess.commit() assert a1._sa_instance_state.session_id == sess.hash_key sess.close() assert a1._sa_instance_state.session_id is None there's no milliseconds here. close() completes, and that id is gone. If you are playing with threads, the only way to guarantee one thread finishes something before the other starts is to either join() that thread or use mutexes. My understanding of Session.close() is that upon return all instances should be detached from the session and their instance state session_id attribute should no longer reference the session. This does not appear to be happening since adding the instance to a different session fails since the instance is still attached to the previous session. Above I illustrate that this is not the case. The code path is clear: session.py - line 942 - close() calls: session.py - line 952 - self.expunge_all() session.py - line 965 calls state._detach() on everything in identity + _new. (question. are you trying to move an object that was deleted? that might have issues, but that's not a valid use case). state.py - line 166 - _detach() calls: state.py - line 167: self.session_id = self._strong_obj = None In reading the code it appears as though it is expected in certain situations for the session_id to continue to refer to the session after close since the check for already attached checks session_id as well as whether the session remains in the sessions registry. I'm not seeing that in 0.8.5 at all.what line of code are you referring to ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To
Re: [sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
On Thu, Sep 4, 2014 at 4:54 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 4, 2014, at 7:50 PM, Ken Lareau klar...@tagged.com wrote: Is there a way to essentially allow something like 'for app in project.applications:' without having to make an explicit query to the DB first, but avoiding the awkwardness of the current setup? just use the viewonly=True and we can all go home :) I’m not too upset about it Got it... the change worked fine, thanks for all the help. :) -- - Ken Lareau -- 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] Session.close() appears to infrequently not expunge all objects
On Sep 4, 2014, at 8:17 PM, Lonnie Hutchinson lonn...@skytap.com wrote: The session executes in one of many web-server threads, but there is no multi-threading with respect to the session or the objects. The session that was closed is within an initializer and upon return a method on the object is executed that creates a new session and tries to attach objects retrieved from the first session. This method does spawn threads but the exception happens before any threads are spawned. The thread target does not take objects, only python strings. only identifiers used in new sessions to query the objects again. The milliseconds I referred to are between the close() of the first session and the add() that fails in the second session. The code I say appears to expect the session_id to remain on detached states in certain situations is that the check in Session._attach checks not only the session_id but that the referenced session still exists in _sessions: if state.session_id and \ state.session_id is not self.hash_key and \ state.session_id in _sessions: raise sa_exc.InvalidRequestError( Object '%s' is already attached to session '%s' (this is '%s') % (orm_util.state_str(state), state.session_id, self.hash_key)) I am interested in this in the hopes that sheds light on the source of the intermittent failures. yeah that code is intended to detect when an object is added to a session that hasn't been removed from another. However, the implementation for session.close() has a direct trace that leads to the session_id set to None for all objects that are either in the identity map, or in the _new (pending) collection. There's no ambiguity there. In debugging this I have noticed that after the first session has been closed() and the initializer has returned the session remains in _sessions. However, if I call gc.collect() the session is removed, suggesting it just hasn't been fully cleaned up yet. that's not really accurate. There's a collection of all the Sessions in the weak referencing map _sessions, and Python's GC may not in fact remove those sessions in a deterministic way. But that has no bearing upon testing objects that have been removed from that session, and therefore have no session_id, if they belong to that session - their session_id has been cleared out. Since it takes both the state referencing the session and the session still existing in _sessions, I can't help but wonder if this is a gc issue. Unfortunately it is not feasible to add gc.collect() calls in our production application as it introduces too much overhead. I will tell you how this *can* happen. If you have a Session, and you put objects in it, then you *don't* close the session; the session just is dereferenced, and is garbage collected at some point by the above mentioned weak dictionary. The objects within that session will *not* have anything done to their session_id in that case. If a new session is started up, and it happens to take over the same in-memory ID as the one that was just GC'ed, it will have the same hash key, and will then exist within _sessions. Then you move your objects to a third session; they will in fact have a session_id that is represented in _sessions, albeit against a different session than they started with, and you get the error. So if that's the case, then the issue here is the result of objects belonging to ad-hoc sessions that are not closed() explicitly, then those objects are shuttled along to another session, while busy thread mechanics in the background keep creating new sessions that occasionally use the same session identifier and produce this collision. There's ways to test for this, like assigning a unique counter to each Session within an event, like the begin() event, then using the before_attach event to verify that session_id is None, and if not, take a peek at that Session and its counter, compare it to something on the state. -- 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] Issue with return results
Thanks. I forgot to mention that I had tried adding the encoding scheme to
freetds.conf. I also tried other encoding schemes, all to no avail. I may
try pymssql tomorrow to see what that does. I would have tried mxodbc, but
I am not about to pay $379 for a driver. I may also see if I can get the MS
ODBC driver for Linux to work on my Mac.
I have to say that the MS SQL stuff is a royal PITA, but unfortunately,
that is what I am stuck with at work. Uggh. (;_;)
The version of FreeTDS I have been using has always been 9.1 (although, I
noticed that the Brew formula for it changed in the last few days to 9.1_1,
for what that's worth).
Greg--
On Thursday, September 4, 2014 5:23:02 PM UTC-5, Michael Bayer wrote:
SQL Server and unix, many things can change:
- UnixODBC version
- FreeTDS version (0.82 and 0.91 have *extremely* different behaviors)
- FreeTDS configuration
The first place I’d look in this case would be your freetds.conf, you
probably need to configure the character set correctly in there.
On Sep 4, 2014, at 5:06 PM, Horcle g...@umn.edu javascript: wrote:
I had to reinstall my python dev environment from scratch due to a hd
failure, and in the process something seems to have changed.
When querying against MS SQL using the script (test_conenction.py):
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=server.ip.address;'
'DATABASE=STUDY_PARTICIPANT;UID=test;PWD=test;port=1433;CHARSET=utf8;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql+pyodbc://', creator=connect,
encoding='latin1',echo='debug',supports_unicode_binds=False)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A: ' + table_name
I used to get the following nice output:
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col (
'default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result'
,)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT [
TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]WHERE [TABLES_1].[
TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [TABLES_1].[TABLE_TYPE] = ?
ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col (
'TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'irb_desc', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd',
)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row span
style=color: #660; class=st
...
--
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] More than one level of abstract base classes doesn't work
The following code fails with AttributeError: 'NoneType' object has no
attribute 'concrete':
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import AbstractConcreteBase,
declarative_base
Base = declarative_base()
class Document(Base, AbstractConcreteBase):
type = Column(Unicode, nullable=False)
class ContactDocument(Document):
__abstract__ = True
send_method = Column('sendmethod', Unicode)
class ActualDocument(ContactDocument):
__tablename__ = 'actual_documents'
__mapper_args__ = {'concrete': True, 'polymorphic_identity': 'actual'}
id = Column(Integer, primary_key=True)
configure_mappers()
Am I not supposed to have more than one level of abstract base classes? Or
am I doing something else wrong?
This is with SQLAlchemy 0.9.7.
--
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] More than one level of abstract base classes doesn't work
well that's just a simple bug. this fixes:
diff --git a/lib/sqlalchemy/ext/declarative/api.py
b/lib/sqlalchemy/ext/declarative/api.py
index daf8bff..fe64ee7 100644
--- a/lib/sqlalchemy/ext/declarative/api.py
+++ b/lib/sqlalchemy/ext/declarative/api.py
@@ -396,7 +396,7 @@ class AbstractConcreteBase(ConcreteBase):
for scls in cls.__subclasses__():
sm = _mapper_or_none(scls)
-if sm.concrete and cls in scls.__bases__:
+if sm and sm.concrete and cls in scls.__bases__:
sm._set_concrete_base(m)
if you want to post a bug report, or send a PR + test, whatever, that would
help...
for whatever reason this mapping is generating a crapload of warnings too.
Oh, I think its because ConcreteBase hardcodes that name type in the
polymorphic join, if you change that col name to type_foo they go away.
the concrete base classes aren't very smooth (nor is concrete mapping
overall...)
On Sep 4, 2014, at 9:37 PM, Alex Grönholm alex.gronh...@nextday.fi wrote:
The following code fails with AttributeError: 'NoneType' object has no
attribute 'concrete':
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import AbstractConcreteBase, declarative_base
Base = declarative_base()
class Document(Base, AbstractConcreteBase):
type = Column(Unicode, nullable=False)
class ContactDocument(Document):
__abstract__ = True
send_method = Column('sendmethod', Unicode)
class ActualDocument(ContactDocument):
__tablename__ = 'actual_documents'
__mapper_args__ = {'concrete': True, 'polymorphic_identity': 'actual'}
id = Column(Integer, primary_key=True)
configure_mappers()
Am I not supposed to have more than one level of abstract base classes? Or am
I doing something else wrong?
This is with SQLAlchemy 0.9.7.
--
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] Issue with return results
I think I am going to dump SQL Server and just go with Postgres. Much
easier, and less of a headache. Fortunately, we are not yet in production.
Thanks!
Greg--
On Thu, Sep 4, 2014 at 8:31 PM, Horcle g...@umn.edu wrote:
Thanks. I forgot to mention that I had tried adding the encoding scheme to
freetds.conf. I also tried other encoding schemes, all to no avail. I may
try pymssql tomorrow to see what that does. I would have tried mxodbc, but
I am not about to pay $379 for a driver. I may also see if I can get the MS
ODBC driver for Linux to work on my Mac.
I have to say that the MS SQL stuff is a royal PITA, but unfortunately,
that is what I am stuck with at work. Uggh. (;_;)
The version of FreeTDS I have been using has always been 9.1 (although, I
noticed that the Brew formula for it changed in the last few days to 9.1_1,
for what that's worth).
Greg--
On Thursday, September 4, 2014 5:23:02 PM UTC-5, Michael Bayer wrote:
SQL Server and unix, many things can change:
- UnixODBC version
- FreeTDS version (0.82 and 0.91 have *extremely* different behaviors)
- FreeTDS configuration
The first place I’d look in this case would be your freetds.conf, you
probably need to configure the character set correctly in there.
On Sep 4, 2014, at 5:06 PM, Horcle g...@umn.edu wrote:
I had to reinstall my python dev environment from scratch due to a hd
failure, and in the process something seems to have changed.
When querying against MS SQL using the script (test_conenction.py):
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=server.ip.address;'
'DATABASE=STUDY_PARTICIPANT;UID=test;PWD=test;port=1433;CHARSET=utf8;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql+pyodbc://', creator=connect,
encoding='latin1',echo='debug',supports_unicode_binds=False)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A: ' + table_name
I used to get the following nice output:
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col (
'default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo',
)
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7
as [Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result'
,)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT [
TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]WHERE [TABLES_1].[
TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [TABLES_1].[TABLE_TYPE] = ?
ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col (
'TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'irb_desc', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd',
)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row span
style=color: #660; class=st
...
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/-i4-GQpXkzY/unsubscribe.
To unsubscribe from this group and all its topics, 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.
--
Greg M. Silverman
Senior Developer Analyst
Cardiovascular Informatics http://www.med.umn.edu/cardiology/
