[sqlalchemy] after_update event is fired when I save a many-to-many relationship
I'm trying to update an audit log table called 'loggings', here is the
definition of the corresponding model:
class *Logging*(Base):
__tablename__ = 'loggings'
id = Column(GUID(), primary_key=True, nullable=False, default=uuid4)
target_id = Column(GUID(), doc="The ID of the altered object")
version = Column('version', Integer, default=0)
created_at = Column(DateTime, default=datetime.now)
modified_at = Column(DateTime, default=datetime.now,
onupdate=datetime.now)
and two other models and an association table:
membership_table = Table('membership', Base.metadata,
Column('user_id', GUID(), ForeignKey('user.id')),
Column('team_id', GUID(), ForeignKey('team.id')),
PrimaryKeyConstraint('user_id', 'team_id'))
class *User*(ActivityMixin, Base): # Product
__tablename__ = "user"
id = Column(GUID(), primary_key=True, nullable=False, default=uuid4)
name = Column(String)
password = Column(String)
def __repr__(self): # optional
return f"User {self.name}"
class *Team*(Base):
__tablename__ = 'team'
id = Column(GUID(), primary_key=True, nullable=False, default=uuid4)
name = Column(String(64))
users = relationship(lambda:User, secondary=membership_table,
backref='teams')
I have an event listener/handler attached to the User class and another to
the Team class:
@event.listens_for(User, 'after_update')
def create_logs_for_user(mapper, connection, target):
# logic to add a creation related record to the loggings table
@event.listens_for(User, 'after_update')
def update_logs_for_user(mapper, connection, target):
# logic to add user-updates records to the loggings table
@event.listens_for(Team, 'after_update')
def update_logs_for_team(mapper, connection, target):
# logic to add team-updates records to the loggings table
These are the operations that I perform:
[1]: from app import Logging, User, Team, session
[2]: user1 = User(name='qwerh')
[3]: session.add(user1)
[4]: session.commit() # after_insert gets fired here
[5]: team1 = Team(name="team1")
[6]: session.add(team1)
[7]: session.commit() # After this point, the users, team, and loggings
tables have one record each, as expected.
[8]: user1.teams.append(team1)
[10]: session.commit() # Two update listeners are getting fired here! After
the append.
*Question:*
When I perform the operation on step [8] above, the loggings table gets
filled in twice, due to the "after_update" event getting fired by both the
User and the Team models.
How do I prevent this from happening, I only want to capture the one event
from the Team model, sorry I'm very new to SQLAlchemy, thank you!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/8d9517f3-e4f3-4f84-bb12-b5a8d1a9d7b8n%40googlegroups.com.
Re: [sqlalchemy] How to format class property as another accessible property
Oh right, I've used the @property decorator before, just been a while.
Thanks for the explanation of @hybrid_property. Makes sense.
On Wed, Jun 7, 2017 at 5:32 PM, mike bayer <mike...@zzzcomputing.com> wrote:
>
>
> On 06/07/2017 06:19 PM, Greg Silverman wrote:
>
>> Thanks, it worked!
>>
>> I tried something similar using the @hybrid_property decorator, but could
>> not get it to work. What exactly is the difference between that and the
>> @property decorator?
>>
>
>
> @property is super normal Python stuff that's in all the Python tutorials
> and is something everyone should use all the time.
>
> @hybrid_property is more of a magic trick that's specific to the kind of
> query interface that SQLAlchemy has - in that in an ORM like SQLAlchemy
> (and lots of others), you access attributes off of classes, as well as
> instances of those classes, as part of regular use of the API. This is not
> a standard object-oriented thing, it's kind of odd. The @hybrid_property
> allows you to build functions that act a lot like @property, but they
> accommodate for accessing an attribute both at the "instance" (e.g. 'self')
> level and at the "class" level. Which translated to SQLAlchemy means, at
> the "row I've loaded into memory" level and the "SQL expression I want to
> execute on the database" level. The kinds of functions we can use in these
> two very different contexts are quite different.
>
>
>
>
>
>
>
>> Greg--
>>
>>
>> On Wed, Jun 7, 2017 at 5:08 PM, mike bayer <mike...@zzzcomputing.com
>> <mailto:mike...@zzzcomputing.com>> wrote:
>>
>>
>>
>> On 06/07/2017 02:31 PM, GMS wrote:
>>
>> I am sure this is easier than I am making it, but I just want to
>> add a property to a class so that decimal representations get
>> truncated at 3 decimal digits.
>>
>> My class is this:
>>
>> class Measures(Model):
>> __tablename__= 'xcelera_measures'
>> id= Column(Numeric,primary_key=True)
>> studyidk= Column(Numeric, ForeignKey('xcel.studyidk'))
>> xceleragroup= relationship("Xcelera")
>> explainstring= Column(String(255))
>> mrn= Column(String(255))
>> value= Column(Numeric)
>>
>> __mapper_args__= {
>> "order_by": [mrn]
>> }
>>
>>
>> and 'value' is the property/attribute that I want to also have a
>> truncated version available in the class.
>>
>> I tried adding this to the class value_new =
>> column_property("%.3f" % value())
>>
>> but got an error that
>>
>> value_new = column_property("%.3f" % value)
>>
>>
>> "%" is a Python function, that doesn't execute on the database.
>> This works as a normal Python descriptor:
>>
>> class MyClass(Base):
>> # ...
>>
>> @property
>> def value_new(self):
>> return "%.3f" % self.value
>>
>>
>>
>>
>>
>>
>>
>>
>> TypeError: float argument required, not Column
>>
>> I also tried this as a hybrid_property, but I don't think I was
>> using it correctly.
>>
>> All I want to do is have the truncated version of my attribute
>> available in my class.
>>
>> Thanks!
>>
>> Greg--
>>
>>
>> -- SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete,
>> and Verifiable Example. See http://stackoverflow.com/help/mcve
>> <http://stackoverflow.com/help/mcve> for a full description.
>> ---
>> 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
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>>
Re: [sqlalchemy] How to format class property as another accessible property
Thanks, it worked!
I tried something similar using the @hybrid_property decorator, but could
not get it to work. What exactly is the difference between that and the
@property decorator?
Greg--
On Wed, Jun 7, 2017 at 5:08 PM, mike bayer <mike...@zzzcomputing.com> wrote:
>
>
> On 06/07/2017 02:31 PM, GMS wrote:
>
>> I am sure this is easier than I am making it, but I just want to add a
>> property to a class so that decimal representations get truncated at 3
>> decimal digits.
>>
>> My class is this:
>>
>> class Measures(Model):
>> __tablename__= 'xcelera_measures'
>> id= Column(Numeric,primary_key=True)
>> studyidk= Column(Numeric, ForeignKey('xcel.studyidk'))
>> xceleragroup= relationship("Xcelera")
>> explainstring= Column(String(255))
>> mrn= Column(String(255))
>> value= Column(Numeric)
>>
>> __mapper_args__= {
>> "order_by": [mrn]
>> }
>>
>>
>> and 'value' is the property/attribute that I want to also have a
>> truncated version available in the class.
>>
>> I tried adding this to the class value_new = column_property("%.3f" %
>> value())
>>
>> but got an error that
>>
>> value_new = column_property("%.3f" % value)
>>
>
> "%" is a Python function, that doesn't execute on the database. This
> works as a normal Python descriptor:
>
> class MyClass(Base):
> # ...
>
> @property
> def value_new(self):
> return "%.3f" % self.value
>
>
>
>
>
>
>
>
>> TypeError: float argument required, not Column
>>
>> I also tried this as a hybrid_property, but I don't think I was using it
>> correctly.
>>
>> All I want to do is have the truncated version of my attribute available
>> in my class.
>>
>> Thanks!
>>
>> Greg--
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> 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 > sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com > sqlalchemy@googlegroups.com>.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> --- 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/to
> pic/sqlalchemy/3FFm1stwEoU/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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
Greg M. Silverman
› flora-script <http://flora-script.grenzi.org/> ‹
› grenzi.org ›
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: How to format class property as another accessible property
Hi,
Thanks for pointing that out. It's still throwing the same error, even when
changed. Does not seem to like passing the column name to the
column_property method.
Greg--
On Wed, Jun 7, 2017 at 3:30 PM, Jonathan Vanasco <jonat...@findmeon.com>
wrote:
> These are two different lines of code. The second one looks correct.
>
>> value_new = column_property("%.3f" % value())
>> value_new = column_property("%.3f" % value)
>>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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/3FFm1stwEoU/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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
Greg M. Silverman
› flora-script <http://flora-script.grenzi.org/> ‹
› grenzi.org ›
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class
Yes, I can certainly do that. Sounds pretty simple, actually. I may have more questions as I dive into this. Thanks! On Mon, Apr 24, 2017 at 3:06 PM, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 04/24/2017 03:42 PM, Greg Silverman wrote: > >> My naive first response would be that I want the class to look like >> >> class DiagnosisTest(Model): >> __tablename__= 'vw_svc_diagnosis' >> #id = Column(Integer, primary_key=True, autoincrement=True) >> dx_id= Column(String(32),primary_key=True) >> first_name= Column(String(255)) >> last_name= Column(String(255)) >> mrn= Column(String(255)) >> dx_code= Column(String(255)) >> #dx_code_orig = Column(String(255)) >> dx_code_type= Column(String(255)) >> dx_name= Column(String(255)) >> #chronic_diagnosis_yn = Column(String(255)) >> diagnosis_datetime= Column(DateTime) >> >> >> >> @property >> def get_diagnosis_groups(self): >> >> import itertoolsas itertools >> >> for (dx_code, patient_id), detailsin itertools.groupby(self, >> keyfunc): >> diagnosis_group = DiagnosisGroup( >> dx_code, patient_id >> ) >> diagnosis_group.details= details >> for detailin details: >> detail.group= diagnosis_group >> yield diagnosis_group >> >> >> So that I could then instantiate this and access grouped elements like >> DiagnosisTest.get_diagnosis_groups.patient_id and >> DiagnosisTest.get_diagnosis_groups.dx_code for example. >> > > > OK can you add a relationship() to DiagnosisTest that returns a list of > DiagnosisDetail objects? that would be all you need here. > > > > >> I am sure this does not answer your question, but what I really want to >> do is have the data grouped within the class and available whenever I >> instantiate the class. >> >> I know what I need, but I am having problems in articulating the details. >> >> Thanks for being patient with me! ^_^ >> >> >> On Mon, Apr 24, 2017 at 12:21 PM, mike bayer <mike...@zzzcomputing.com >> <mailto:mike...@zzzcomputing.com>> wrote: >> >> >> >> On 04/24/2017 12:51 PM, Greg Silverman wrote: >> >> Hi Mike, >> I'm finally getting to this. Instead of having both a detail >> and grouped methods, I would like to have only the grouped >> method. I'm not sure I follow your suggestion for the two >> methods above and how that would be modified to fit into the >> @property in my class. >> >> Could you please elaborate a bit how these would work within >> context of the class? >> >> >> cutting and pasting: >> >> >>def get_diagnosis_groups(sorted_list_of_diagnosis_detail): >> >> for (dx_code, patient_id), details in >> itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc): >> diagnosis_group = DiagnosisGroup( >> dx_code, patient_id >> ) >> diagnosis_group.details = details >> for detail in details: >> detail.group = diagnosis_group >> yield diagnosis_group >> >> >> you want to take out the "sorted_list_of_diagnosis_detail" argument. >> OKbut I don't know where you'd like to get this from. Where is >> this "method" going and are you calling that as a classmethod or an >> instance method? what class? Just show a usage example. >> >> >> >> >> >> >> >> Thanks! >> >> Greg-- >> >> >> >> >> On Wed, Mar 1, 2017 at 8:53 PM, mike bayer >> <mike...@zzzcomputing.com <mailto:mike...@zzzcomputing.com> >> <mailto:mike...@zzzcomputing.com >> >> <mailto:mike...@zzzcomputing.com>>> wrote: >> >> >> >> On 03/01/2017 08:27 PM, GMS wrote: >> >> I have the following class models: >> >> >> | class DiagnosisDetail(Model): >> __tablename__ = 'vw_svc_diagnosis' >> diagnosis_id = Column(String(32), >> primary_key=True) >> first_name = Column(String(255)) >> last_name = Column(String(
Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class
My naive first response would be that I want the class to look like
class DiagnosisTest(Model):
__tablename__ = 'vw_svc_diagnosis'
#id = Column(Integer, primary_key=True, autoincrement=True)
dx_id = Column(String(32), primary_key=True)
first_name = Column(String(255))
last_name = Column(String(255))
mrn = Column(String(255))
dx_code = Column(String(255))
#dx_code_orig = Column(String(255))
dx_code_type = Column(String(255))
dx_name = Column(String(255))
#chronic_diagnosis_yn = Column(String(255))
diagnosis_datetime = Column(DateTime)
@property
def get_diagnosis_groups(self):
import itertools as itertools
for (dx_code, patient_id), details in itertools.groupby(self, keyfunc):
diagnosis_group = DiagnosisGroup(
dx_code, patient_id
)
diagnosis_group.details = details
for detail in details:
detail.group = diagnosis_group
yield diagnosis_group
So that I could then instantiate this and access grouped elements like
DiagnosisTest.get_diagnosis_groups.patient_id and
DiagnosisTest.get_diagnosis_groups.dx_code for example.
I am sure this does not answer your question, but what I really want to do
is have the data grouped within the class and available whenever I
instantiate the class.
I know what I need, but I am having problems in articulating the details.
Thanks for being patient with me! ^_^
On Mon, Apr 24, 2017 at 12:21 PM, mike bayer <mike...@zzzcomputing.com>
wrote:
>
>
> On 04/24/2017 12:51 PM, Greg Silverman wrote:
>
>> Hi Mike,
>> I'm finally getting to this. Instead of having both a detail and grouped
>> methods, I would like to have only the grouped method. I'm not sure I
>> follow your suggestion for the two methods above and how that would be
>> modified to fit into the @property in my class.
>>
>> Could you please elaborate a bit how these would work within context of
>> the class?
>>
>
> cutting and pasting:
>
>
> def get_diagnosis_groups(sorted_list_of_diagnosis_detail):
>
> for (dx_code, patient_id), details in
> itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc):
> diagnosis_group = DiagnosisGroup(
>dx_code, patient_id
> )
> diagnosis_group.details = details
> for detail in details:
> detail.group = diagnosis_group
> yield diagnosis_group
>
>
> you want to take out the "sorted_list_of_diagnosis_detail" argument.
> OKbut I don't know where you'd like to get this from. Where is this
> "method" going and are you calling that as a classmethod or an instance
> method? what class? Just show a usage example.
>
>
>
>
>
>
>
>> Thanks!
>>
>> Greg--
>>
>>
>>
>>
>> On Wed, Mar 1, 2017 at 8:53 PM, mike bayer <mike...@zzzcomputing.com
>> <mailto:mike...@zzzcomputing.com>> wrote:
>>
>>
>>
>> On 03/01/2017 08:27 PM, GMS wrote:
>>
>> I have the following class models:
>>
>>
>> | class DiagnosisDetail(Model):
>> __tablename__ = 'vw_svc_diagnosis'
>> diagnosis_id = Column(String(32), primary_key=True)
>> first_name = Column(String(255))
>> last_name = Column(String(255))
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_id = Column(String(255), ForeignKey('dx_group.dx_id'))
>> diagnosisgroup = relationship("DiagnosisGroup")
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>>
>> __mapper_args__ = {
>> "order_by":[mrn, dx_name]
>> }
>>
>> class DiagnosisGroup(Model):
>> __tablename__ = 'diagnosis_group'
>> dx_id = Column(String(32), primary_key=True)
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>> diagnosis_datetime = Column(DateTime)
>>
>> __mapper_args__ = {
>> "order_by":[mrn, dx_name]
>> }|
>>
>>
>>
>> where the underlying tables for DiagnosisGroup and
>> DiagnosisDetail are
>> SQL views. DiagnosisGroup is so that I can have a more succi
Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class
Hi Mike,
I'm finally getting to this. Instead of having both a detail and grouped
methods, I would like to have only the grouped method. I'm not sure I
follow your suggestion for the two methods above and how that would be
modified to fit into the @property in my class.
Could you please elaborate a bit how these would work within context of the
class?
Thanks!
Greg--
On Wed, Mar 1, 2017 at 8:53 PM, mike bayer <mike...@zzzcomputing.com> wrote:
>
>
> On 03/01/2017 08:27 PM, GMS wrote:
>
>> I have the following class models:
>>
>>
>> | class DiagnosisDetail(Model):
>> __tablename__ = 'vw_svc_diagnosis'
>> diagnosis_id = Column(String(32), primary_key=True)
>> first_name = Column(String(255))
>> last_name = Column(String(255))
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_id = Column(String(255), ForeignKey('dx_group.dx_id'))
>> diagnosisgroup = relationship("DiagnosisGroup")
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>>
>> __mapper_args__ = {
>> "order_by":[mrn, dx_name]
>>}
>>
>> class DiagnosisGroup(Model):
>> __tablename__ = 'diagnosis_group'
>> dx_id = Column(String(32), primary_key=True)
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>> diagnosis_datetime = Column(DateTime)
>>
>> __mapper_args__ = {
>> "order_by":[mrn, dx_name]
>>}|
>>
>>
>>
>> where the underlying tables for DiagnosisGroup and DiagnosisDetail are
>> SQL views. DiagnosisGroup is so that I can have a more succinct view of
>> the data, since a patient can have the same diagnosis many times. I am
>> wondering if there is a way to do this within the class structure
>> instead of at the db server?
>>
>
> do you mean, derive a DiagnosisGroup object from a DiagnosisDetail without
> running SQL? (or a list of them?) (the answer is..sure? just build Python
> code to generate objects from a list of DiagnosisDetail objects).
>
> I do not wish to do this through any ORM
>
>> session queries, since these two classes have distinct use cases where
>> they bind to wtform views. Thus, I would like to inherit the properties
>> of these two classes from another distinct class.
>>
>> I have not been able to find anything like this, short
>> of [create-column-properties-that-use-a-groupby][1]
>> <http://stackoverflow.com/questions/25822393/how-can-i-creat
>> e-column-properties-that-use-a-groupby/25879453>,
>> but this uses session queries to achieve the result. I would like to
>> keep everything within the class itself through inheritance of the
>> DiagnosisDetail class.
>>
>
> You don't need a relational database to do grouping, if you have a list of
> data in memory it can be grouped using sets, or most succinctly Python's
> own groupby function: https://docs.python.org/2/libr
> ary/itertools.html#itertools.groupby
>
>
>
>
>> Note: the primary key for DiagnosisGroup, is a concatenation of dx_code
>> and another field patient_id. Groupings thus are unique.
>>
>
> OK, so
>
> def keyfunc(detail):
> return (detail.dx_code, detail.patient_id)
>
> def get_diagnosis_groups(sorted_list_of_diagnosis_detail):
>
> for (dx_code, patient_id), details in
> itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc):
> diagnosis_group = DiagnosisGroup(
>dx_code, patient_id
> )
> diagnosis_group.details = details
> for detail in details:
> detail.group = diagnosis_group
> yield diagnosis_group
>
>
>
>
> I do also need
>
>> the FK relation back to the DiagnosisDetail class, which leads me to
>> believe there should be three classes, where the two above classes
>> inherit their properties from a parent class.
>>
>
>
>
>
>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google
>> Groups "sqlalchemy" group.
>> To
Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class
Mike,
I'll try it and let you know the outcome (it may be a bit before I get to
it, but I like your suggestions much better than having to use SQL views,
which I am only doing due to a time crunch).
Much appreciated!
On Wed, Mar 1, 2017 at 9:52 PM, mike bayer <mike...@zzzcomputing.com> wrote:
>
>
> On 03/01/2017 10:22 PM, Greg Silverman wrote:
>
>>
>>
>> On Wed, Mar 1, 2017 at 8:53 PM, mike bayer <mike...@zzzcomputing.com
>> <mailto:mike...@zzzcomputing.com>> wrote:
>>
>>
>>
>> On 03/01/2017 08:27 PM, GMS wrote:
>>
>> I have the following class models:
>>
>>
>> | class DiagnosisDetail(Model):
>> __tablename__ = 'vw_svc_diagnosis'
>> diagnosis_id = Column(String(32), primary_key=True)
>> first_name = Column(String(255))
>> last_name = Column(String(255))
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_id = Column(String(255), ForeignKey('dx_group.dx_id'))
>> diagnosisgroup = relationship("DiagnosisGroup")
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>>
>> __mapper_args__ = {
>> "order_by":[mrn, dx_name]
>>}
>>
>> class DiagnosisGroup(Model):
>> __tablename__ = 'diagnosis_group'
>> dx_id = Column(String(32), primary_key=True)
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>> diagnosis_datetime = Column(DateTime)
>>
>> __mapper_args__ = {
>> "order_by":[mrn, dx_name]
>>}|
>>
>>
>>
>> where the underlying tables for DiagnosisGroup and
>> DiagnosisDetail are
>> SQL views. DiagnosisGroup is so that I can have a more succinct
>> view of
>> the data, since a patient can have the same diagnosis many
>> times. I am
>> wondering if there is a way to do this within the class structure
>> instead of at the db server?
>>
>>
>> do you mean, derive a DiagnosisGroup object from a DiagnosisDetail
>> without running SQL?
>>
>>
>>
>> In as much as having the ORM do the work versus the backend, I guess.
>>
>>
>>
>> (or a list of them?) (the answer is..sure? just build Python code
>> to generate objects from a list of DiagnosisDetail objects).
>>
>>
>>
>>
>> Hmmm... but I don't get all the benefits of related data/data
>> associations via key constraints that way with a non SQLA object. For
>> example, I have a form that binds the Grouped records to their Detailed
>> records in another form utilizing the one-to-many relationship between
>> the two classes.
>>
>
>
> my example illustrates joining the two types of objects together in the
> same way as a relationship-bound collection would.
>
>
>
>>
>>
>>
>> I do not wish to do this through any ORM
>>
>> session queries, since these two classes have distinct use cases
>> where
>> they bind to wtform views. Thus, I would like to inherit the
>> properties
>> of these two classes from another distinct class.
>>
>> I have not been able to find anything like this, short
>> of [create-column-properties-that-use-a-groupby][1]
>> <http://stackoverflow.com/questions/25822393/how-can-i-creat
>> e-column-properties-that-use-a-groupby/25879453
>> <http://stackoverflow.com/questions/25822393/how-can-i-creat
>> e-column-properties-that-use-a-groupby/25879453>>,
>> but this uses session queries to achieve the result. I would like
>> to
>> keep everything within the class itself through inheritance of the
>> DiagnosisDetail class.
>>
>>
>> You don't need a relational database to do grouping, if you have a
>> list of data in memory it can be grouped using sets, or most
>> succinctly Python's own groupby function:
>> https://docs.python.org/2/library/itertools.html#itertools.groupby
>> <https://docs.python.org/2/library/itertools.html#ite
Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class
On Wed, Mar 1, 2017 at 8:53 PM, mike bayer <mike...@zzzcomputing.com> wrote:
>
>
> On 03/01/2017 08:27 PM, GMS wrote:
>
>> I have the following class models:
>>
>>
>> | class DiagnosisDetail(Model):
>> __tablename__ = 'vw_svc_diagnosis'
>> diagnosis_id = Column(String(32), primary_key=True)
>> first_name = Column(String(255))
>> last_name = Column(String(255))
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_id = Column(String(255), ForeignKey('dx_group.dx_id'))
>> diagnosisgroup = relationship("DiagnosisGroup")
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>>
>> __mapper_args__ = {
>> "order_by":[mrn, dx_name]
>>}
>>
>> class DiagnosisGroup(Model):
>> __tablename__ = 'diagnosis_group'
>> dx_id = Column(String(32), primary_key=True)
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>> diagnosis_datetime = Column(DateTime)
>>
>> __mapper_args__ = {
>> "order_by":[mrn, dx_name]
>>}|
>>
>>
>>
>> where the underlying tables for DiagnosisGroup and DiagnosisDetail are
>> SQL views. DiagnosisGroup is so that I can have a more succinct view of
>> the data, since a patient can have the same diagnosis many times. I am
>> wondering if there is a way to do this within the class structure
>> instead of at the db server?
>>
>
> do you mean, derive a DiagnosisGroup object from a DiagnosisDetail without
> running SQL?
In as much as having the ORM do the work versus the backend, I guess.
> (or a list of them?) (the answer is..sure? just build Python code to
> generate objects from a list of DiagnosisDetail objects).
>
Hmmm... but I don't get all the benefits of related data/data associations
via key constraints that way with a non SQLA object. For example, I have a
form that binds the Grouped records to their Detailed records in another
form utilizing the one-to-many relationship between the two classes.
>
> I do not wish to do this through any ORM
>
>> session queries, since these two classes have distinct use cases where
>> they bind to wtform views. Thus, I would like to inherit the properties
>> of these two classes from another distinct class.
>>
>> I have not been able to find anything like this, short
>> of [create-column-properties-that-use-a-groupby][1]
>> <http://stackoverflow.com/questions/25822393/how-can-i-creat
>> e-column-properties-that-use-a-groupby/25879453>,
>> but this uses session queries to achieve the result. I would like to
>> keep everything within the class itself through inheritance of the
>> DiagnosisDetail class.
>>
>
> You don't need a relational database to do grouping, if you have a list of
> data in memory it can be grouped using sets, or most succinctly Python's
> own groupby function: https://docs.python.org/2/libr
> ary/itertools.html#itertools.groupby
Indeed. I have used this for other things, but never thought of it for this
case.
>
>
>
>
>
>> Note: the primary key for DiagnosisGroup, is a concatenation of dx_code
>> and another field patient_id. Groupings thus are unique.
>>
>
> OK, so
>
> def keyfunc(detail):
> return (detail.dx_code, detail.patient_id)
>
> def get_diagnosis_groups(sorted_list_of_diagnosis_detail):
>
> for (dx_code, patient_id), details in
> itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc):
> diagnosis_group = DiagnosisGroup(
>dx_code, patient_id
> )
> diagnosis_group.details = details
> for detail in details:
> detail.group = diagnosis_group
> yield diagnosis_group
>
Is there a way to use these as methods within a class model using the
mapper, like in the stackoverflow link I gave?
Thanks for the out-of-the-box approach to thinking about this.
Greg--
>
>
>
> I do also need
>
>> the FK relation back to the DiagnosisDetail class, which leads me to
>> believe there should be three classes, where the two above classes
>> inherit their properties from a parent class.
>>
>
>
>
>
>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example cod
Re: [sqlalchemy] Python datatime object issue with SQLite/SQLAlchemy
I should add: The specific queries I was running that were throwing the
error all worked using your test case setup.
Greg--
On Fri, Nov 11, 2016 at 10:50 AM, Greg M. Silverman <g...@grenzi.org> wrote:
> Hi Mike,
> Modifications of your test made to point to the actual database file I am
> using worked. The only difference in your test versus my code is that my
> ClinicalData class definition uses a Flask db.Model versus a straight
> declarative_base. I'll need to play around with this a bit more.
> Interesting.
>
> Thanks!
>
> Greg--
>
> On Thu, Nov 10, 2016 at 8:33 PM, mike bayer <mike...@zzzcomputing.com>
> wrote:
>
>>
>>
>> On 11/10/2016 08:10 PM, horcle_buzz wrote:
>>
>>> I am using SQLAlchemy and am having an issue wrt to an SQLite error:
>>>
>>>
>>> |
>>> SQLiteDatetype only accepts Pythondate objects asinput.
>>> [SQL:'SELECT anon_1.patient_sid AS sid FROM
>>> (SELECT clinical_data.patient_sid AS patient_sid FROM clinical_data
>>> WHERE clinical_data.event_date >= ?) AS anon_1']
>>> |
>>>
>>>
>>> I understand perfectly the meaning of the error, but I do not understand
>>> why it is happening in my case.
>>>
>>
>>
>> nor do I. You'd need to provide a complete test. Here is one, modify it
>> to show how you get your error.
>>
>>
>> from sqlalchemy import *
>> from sqlalchemy.orm import *
>> from sqlalchemy.ext.declarative import declarative_base
>> from datetime import datetime
>>
>> Base = declarative_base()
>>
>>
>> class ClinicalData(Base):
>> __tablename__ = 'clinical_data'
>> id = Column(Integer, primary_key=True, autoincrement=True)
>> patient_id = Column(Integer)
>> event_date = Column(Date)
>>
>> e = create_engine("sqlite://", echo=True)
>> Base.metadata.create_all(e)
>>
>>
>> valdate = datetime.strptime('1776-01-01 00:00:00', "%Y-%m-%d
>> %H:%M:%S").date()
>>
>> s = Session(e)
>> s.add(ClinicalData(event_date=valdate))
>> s.commit()
>>
>>
>> print s.query(ClinicalData).filter(ClinicalData.event_date >=
>> valdate).all()
>>
>>
>>
>>
>>> The parameter that I am passing to do the date comparison in the query
>>> above
>>> |
>>> clinical_data.event_date >=?
>>> |
>>> is set as:
>>>
>>>
>>> |
>>> valdate =datetime.strptime('1776-01-01 00:00:00',"%Y-%m-%d
>>> %H:%M:%S").date()
>>> |
>>>
>>>
>>> and, I have validated that the data type of `valdate` is `>> 'datetime.date'>`
>>>
>>> The class used to construct the query is:
>>>
>>> |
>>> classClinicalData(db.Model):
>>> __tablename__ ='clinical_data'
>>> id =Column(Integer,primary_key=True,autoincrement=True)
>>> patient_id =Column(Integer)
>>> patient_sid =Column(Integer)
>>> string_value =Column(String(255))
>>> double_value =Column(Float)
>>> data_type_id =Column(Integer)
>>> event_date =Column(Date)
>>> ontology_id =Column(Integer)
>>> attribute_id =Column(Integer)
>>> project_id =Column(Integer)
>>> replaced_by_id =Column(Integer)
>>> date_record_added =Column(DateTime)
>>> parent =Column(Integer)
>>> num_children =Column(Integer)
>>> lft =Column(Integer)
>>> rgt =Column(Integer)
>>> |
>>>
>>>
>>> The SQLAlchemy documentation for SQLite states (see [SQLAlchemy SQLite
>>> documentation][1] <
>>> http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html>) that
>>> "SQLAlchemy’s own DateTime and related types provide date formatting and
>>> parsing functionality when SQlite is used..."
>>>
>>> Note that when I use `DateTime` as a data type in my class model on the
>>> `event_date` attribute I get the following error
>>> |
>>> SQLiteDateTimetype only accepts Pythondatetime anddate objects asinput.
>>> |
>>>
>>> For this I define
>>> |
>>> valdate =datetime.strptime('1776-01-01 00:00:00',"%Y-%m-%d %H:%M:%S")
>>> |
>>>
>>> without the `date()` method. As expected, `type(valdate)` in this case
>>> yields
>>> |
>>> <type'dat
Re: [sqlalchemy] Python datatime object issue with SQLite/SQLAlchemy
Hi Mike,
Modifications of your test made to point to the actual database file I am
using worked. The only difference in your test versus my code is that my
ClinicalData class definition uses a Flask db.Model versus a straight
declarative_base. I'll need to play around with this a bit more.
Interesting.
Thanks!
Greg--
On Thu, Nov 10, 2016 at 8:33 PM, mike bayer <mike...@zzzcomputing.com>
wrote:
>
>
> On 11/10/2016 08:10 PM, horcle_buzz wrote:
>
>> I am using SQLAlchemy and am having an issue wrt to an SQLite error:
>>
>>
>> |
>> SQLiteDatetype only accepts Pythondate objects asinput.
>> [SQL:'SELECT anon_1.patient_sid AS sid FROM
>> (SELECT clinical_data.patient_sid AS patient_sid FROM clinical_data
>> WHERE clinical_data.event_date >= ?) AS anon_1']
>> |
>>
>>
>> I understand perfectly the meaning of the error, but I do not understand
>> why it is happening in my case.
>>
>
>
> nor do I. You'd need to provide a complete test. Here is one, modify it
> to show how you get your error.
>
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> from datetime import datetime
>
> Base = declarative_base()
>
>
> class ClinicalData(Base):
> __tablename__ = 'clinical_data'
> id = Column(Integer, primary_key=True, autoincrement=True)
> patient_id = Column(Integer)
> event_date = Column(Date)
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
>
> valdate = datetime.strptime('1776-01-01 00:00:00', "%Y-%m-%d
> %H:%M:%S").date()
>
> s = Session(e)
> s.add(ClinicalData(event_date=valdate))
> s.commit()
>
>
> print s.query(ClinicalData).filter(ClinicalData.event_date >=
> valdate).all()
>
>
>
>
>> The parameter that I am passing to do the date comparison in the query
>> above
>> |
>> clinical_data.event_date >=?
>> |
>> is set as:
>>
>>
>> |
>> valdate =datetime.strptime('1776-01-01 00:00:00',"%Y-%m-%d
>> %H:%M:%S").date()
>> |
>>
>>
>> and, I have validated that the data type of `valdate` is `> 'datetime.date'>`
>>
>> The class used to construct the query is:
>>
>> |
>> classClinicalData(db.Model):
>> __tablename__ ='clinical_data'
>> id =Column(Integer,primary_key=True,autoincrement=True)
>> patient_id =Column(Integer)
>> patient_sid =Column(Integer)
>> string_value =Column(String(255))
>> double_value =Column(Float)
>> data_type_id =Column(Integer)
>> event_date =Column(Date)
>> ontology_id =Column(Integer)
>> attribute_id =Column(Integer)
>> project_id =Column(Integer)
>> replaced_by_id =Column(Integer)
>> date_record_added =Column(DateTime)
>> parent =Column(Integer)
>> num_children =Column(Integer)
>> lft =Column(Integer)
>> rgt =Column(Integer)
>> |
>>
>>
>> The SQLAlchemy documentation for SQLite states (see [SQLAlchemy SQLite
>> documentation][1] <
>> http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html>) that
>> "SQLAlchemy’s own DateTime and related types provide date formatting and
>> parsing functionality when SQlite is used..."
>>
>> Note that when I use `DateTime` as a data type in my class model on the
>> `event_date` attribute I get the following error
>> |
>> SQLiteDateTimetype only accepts Pythondatetime anddate objects asinput.
>> |
>>
>> For this I define
>> |
>> valdate =datetime.strptime('1776-01-01 00:00:00',"%Y-%m-%d %H:%M:%S")
>> |
>>
>> without the `date()` method. As expected, `type(valdate)` in this case
>> yields
>> |
>> <type'datetime.datetime'>
>> |
>>
>> I have tried every combination of creating the variable `valdate` with
>> the `event_date` attribute of my class.
>>
>> I am not asking how to convert my string object to a python datetime
>> object, nor am I asking what the error means. I am not sure exactly why
>> I keep getting this error when everything appears to be sufficiently
>> defined.
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve f
Re: [sqlalchemy] dealing with NULLS in 1-many relationships
Unfortunately, the data are out of our control. However, this solution
looks like it will do the job.
Thanks!
Greg--
On Mon, Jun 6, 2016 at 5:54 PM, Mike Bayer <mike...@zzzcomputing.com> wrote:
>
>
> On 06/06/2016 11:21 AM, Horcle wrote:
>
>> I have the following models:
>>
>> class LabResult(Model):
>> __tablename__ = 'cp_svc_lab_result'
>> id = Column(Integer, primary_key=True, autoincrement=True)
>> test_code = Column(String(255))
>> test_code_system = Column(String(255))
>> test_name = Column(String(255))
>> test_name_orig = Column(String(255))
>> proc_name = Column(String(255))
>> proc_code = Column(String(255))
>> proc_code_modifier = Column(String(255))
>> proc_code_system = Column(String(255))
>> result_value = Column(String(255))
>> result_value_num = Column(String(255))
>> result_value_num_orig = Column(String(255))
>> result_unit = Column(String(255))
>> result_unit_orig = Column(String(255))
>> ref_normal_min = Column(String(255))
>> ref_normal_max = Column(String(255))
>> result_characterization = Column(String(255))
>> collection_datetime = Column(DateTime)
>> result_datetime = Column(DateTime)
>> abnormal_flag = Column(String(255))
>> lab_status = Column(String(255))
>> result_comment = Column(UnicodeText)
>> component_comment = Column(UnicodeText)
>> order_id = Column(String(255))
>> order_num = Column(String(255))
>> order_priority = Column(String(255))
>> order_result_id = Column(String(255))
>> order_reviewed = Column(String(255))
>> order_type_orig = Column(String(255))
>> order_type_orig_id = Column(String(255))
>> result_code_orig = Column(String(255))
>> result_code_orig_system = Column(String(255))
>> result_status = Column(String(255))
>> patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
>> service_id = Column(Integer, ForeignKey('cp_service.service_id'))
>> provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))
>>
>> and,
>>
>> class Provider(Model):
>> __tablename__ = 'cp_provider'
>> provider_id = Column(Integer, primary_key=True)
>> authorize_meds_yn = Column(String(80))
>> active_status = Column(String(80))
>> authorize_orders_yn = Column(String(80))
>> birth_date = Column(DateTime)
>> clinician_degree = Column(String(80))
>> clinician_title = Column(String(80))
>> country = Column(String(80))
>> dea_number = Column(String(80))
>> email = Column(String(80))
>> external_name = Column(String(80))
>> provider_e_prescribe_yn = Column(String(80))
>> inpatient_ordering_yn = Column(String(80))
>> name = Column(String(80))
>> npi = Column(String(80))
>> office_fax = Column(String(80))
>> office_phone = Column(String(80))
>> outpatient_ordering_yn = Column(String(80))
>> provider_type = Column(String(80))
>> referral_source_type = Column(String(80))
>> resident_yn = Column(String(80))
>> sex = Column(String(80))
>> surgical_pool_yn = Column(String(80))
>> transcription_user_yn = Column(String(80))
>> upin = Column(String(80))
>> encounter = relationship("EncounterList", backref=backref("Provider"),
>> lazy='dynamic')
>>
>> Where one provider can have multiple LabResults... How do I handle the
>> case when there may be a provider_id in the LabResult table, but not in
>> the Provider table (we are only keeping a subset of the provider list)?
>> I need to access the object Provider so that I can have access to all of
>> its attributes, such as Provider.name, etc. When I try this now, I get
>> an error that "Nonetype has attribute name." Ia there a way to set a
>> default value for when the result is NULL?
>>
>
> In relational database design, provider_id always must refer to a row in
> Provider. If that's not the case, then your database is failing
> referential integrity and is mis-designed; the definition of a ForeignKey
> is that it's a constraint that indicates a remote primary key that must
> exist.
>
> If you're in some situation where this isn't actually happening and you
> need to work around it, it looks like you're just looking for a string
> "missing" instead of None? This is just a Python access issue. Use a
> method like "def get_provider_id()", or a synonym:
>
> class LabResult(Base):
>
> provider_id = Column(Integer)
>
> @synonym_for("_provider_id", map_column=True)
> @property
> def provider_id(self):
>return self._provider_id or "Missing"
>
>
>
>
>
>
>
>
Re: [sqlalchemy] Re: Dynamically constructing joins
Ha! Ha! On my previous attempts, I had something similar to this, but
instead, I had
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid)))
if (n 1):
for table in join_tables[1:]:
for criterion in join_criteria[1:]:
query = query.join(eval(table), eval(criterion))
Where the variables table and criterion were built lists, so that I ended
up doing a Cartesian product of all my tables, which was giving me many
problems, with aliasing being the least of it!
Thanks!
Greg--
On Tue, Mar 24, 2015 at 11:22 PM, Jonathan Vanasco jonat...@findmeon.com
wrote:
any reason why you're not building a query like this?
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid)))
if n = 2
query = query.\
join(a[2],a[2].c.patient_sid==a[1].c.patient_sid)
if n = 3
query = query.\
join(a[3],a[3].c.patient_sid==a[1].c.patient_sid)
or
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid)))
for i in range(2, n):
query = query.\
join(a[i],a[i].c.patient_sid==a[1].c.patient_sid)
--
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/SySyi4CCCUY/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/
University of Minnesota
612-626-0919
g...@umn.edu
› flora-script http://flora-script.grenzi.org/ ‹
› grenzi.org ‹
› evaluate-it.org ‹
--
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
Hi Jonathan, For the record, in my current setup, I installed python via Homebrew. Prior to that, I was using the Apple build of python, which would have been the 32-bit version that came with Lion. Greg-- On Mon, Sep 8, 2014 at 6:31 PM, Jonathan Vanasco jonat...@findmeon.com wrote: Looking at that issue, and suggested fix... I think you're best going with that route. the stock apple Python is usually pretty bad, and it seems to be the compile settings apple selected, not python. apple's version is often VERY out of date and has some weird settings. It's screwed me and colleagues up a lot. I'd strongly suggest you do the following: 1. Install a second, custom python [ you can get one from Python.org in a click-installer https://www.python.org/download/mac ]. You can then update your bash so that your console user will use that python, and not break anything on your mac. 2. reinstall all pyodbc and everything else into that custom python's site-packages. you actually need to do all this stuff whenever you have a version bump (2.7.7 to 2.7.8), but some packages magically work even if you don't. i've found that shit just works when you use a python.org interpreter. On Monday, September 8, 2014 7:04:17 PM UTC-4, Horcle wrote: Unfortunately, dumping SQL Server (in favor of Oracle) may not be an option, due to management concerns and other factors. Still working on it. However, I did manage to get this working with pymssql. Apparently, there is a bug with pyodbc and 64-bit python (see https://community. vertica.com/vertica/topics/mac_pyodbc_string_encoding_issue). So, short of applying the fix to the cpp file recompiling pyodbc (see http://www.vertica-forums.com/viewtopic.php?f=35t=1863p=6174#p6174), the easy solution is to use pymssql. I believe the upgrade I did from Lion to Mavericks allows use of 64-bit python now, so this makes sense. Thanks! -- 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. -- 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
Re: [sqlalchemy] cannot access tables
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
On Mon, Aug 18, 2014 at 9:47 AM, Horcle g...@umn.edu wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
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'
Again, I am connecting fine with the database create.engine method (that
is '42' is printing as expected), but when I run the
inspector.get_table_names method with the given conditional it is printing
the 'A' (I have tried other table names in the same database to which I
added 'irbd_balance,' all with the same result.
what is the SQL output if you set echo=‘debug’; then, take the SQL you
see and take a look at what it’s SELECTing so you can see what might be
wrong. Probably some schema name setting or something like that.
Thanks, I did not realize this was an option (actually, it is echo=True,
but at least I can see the SQL being sent). Hopefully this will lead me to
an answer.
Greg--
--
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/YSjU_Ohsyvw/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/
University of Minnesota
612-626-0919
g...@umn.edu
› flora-script http://flora-script.grenzi.org/ ‹
› grenzi.org ‹
--
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] sqlite string concats and datetime arithmetics
I'm trying to get a series of datetimes using func.datetime. The format of
input is func.datetime(basetime, '+ NNN seconds'), which works nicely if
the shift applied is constant. However I need to add 10, 20, 30 seconds,
etc to this base time. So I want something like func.datetime(basetime,
concat('+', seconds.c.n, ' seconds')), but concat doesn't work for sqlite,
which concatenates using the '||' operator. Is there working method to
concat in sqlite?
Failing that, is there another way to get at what I want with datetime
arithmetics?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] unregister mapper event
Is it possible to remove mapper events? Specifically I want to call event.remove(mapper, 'mapper_configured', fn) but I get an error back saying Mapper is not iterable. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] kivy and sqlalchemy
I'm writing an application that uses kivy for GUI and sqlalchemy for ORM. Each has its own instrumentation system, and I initially planned to connect them by 1) mirroring relevant SA attributes in kivy at init, 2) work with only the kivy versions of the attributes for the duration of the app, 3) on write events in kivy, propagate the writes to SA. This works well except when inside SA, changes are propagated through relationships and backrefs. Then I need to reload kivy attributes from their SA mirror parts when such things happen. But I'm having a hard time trying to figure out a way to do this without getting an infinite feedback loop. Particularly, I looked at the AttributeEvents of SA, and the initiator argument seems hopeful, but I'm not familiar with the internals of SA. Does the attribute implementation object have attributes/methods that allows me to prevent the loop? Otherwise, is there an easy way to handle this? Or do I need to get dirty and do something like constructing a child class of both kivy and SA properties? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Strange behavior with a class inheriting from AssociationProxy
I see now, thanks! It's fairly difficult to plug in the events as you
suggested in my code, but I subclassed MappedCollection to discard setitem
calls with a None key, and used this as the collection class. This seems to
work, and if there's no other events I need to worry about during this
append process, should be a complete solution.
On Monday, July 8, 2013 9:14:02 PM UTC-5, Michael Bayer wrote:
here's how you debug that:
@event.listens_for(A.ab, append)
def append(target, value, initiator):
import pdb
pdb.set_trace()
@event.listens_for(B.ab, append)
def append(target, value, initiator):
import pdb
pdb.set_trace()
and if you're concerned about attribute-access side effects, in your pdb
you look at an object like:
ab.__dict__
no side effects that way (though there aren't any getter side effects in
this test).
the sequence is:
1. create AB(a=a, b=b)
2. the AB has a set first, which then fires off the backref A.ab
3. AB is assigned to the A.ab dictionary, with key of None because AB.b is
None
4. AB.b is then assigned to b
5. AB.b fires off backref B.ab
6. the association proxy now gets involved, and appends your AB to the
A.ab collection again, this time with the correct key of b
So if you just did the assignment without the association proxy (which is
a good idea when understanding this), you just get the key of None in aa.ab
and nothing else. If you were to assign .b or .a to the AB first, you get
the same problem here in one direction or the other, because both AB.a and
AB.b will both try to assign it to a dictionary that requires the other
reference be present, it's a mutual referencing issue.
It's an awkward mapping, one way to make it work is to just not use
backrefs and make your own event, though to make it work in both directions
without going into an endless loop would require a more intricate approach
(using internal appenders that pass along the initiator so you can stop
an endless setter loop). Below is just the one direction:
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
ab = relationship('AB',
collection_class=attribute_mapped_collection('b'))
abnum = correlated_proxy('ab', 'num', correlator=corr)
class AB(Base):
__tablename__ = 'table_ab'
num = Column(Integer)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('table_b.id'), primary_key=True)
a = relationship(A)
b = relationship(B)
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
ab = relationship('AB',
collection_class=attribute_mapped_collection('a')
)
@event.listens_for(A.ab, append)
def append(target, value, initiator):
value.b.ab[value.a] = value
On Jul 8, 2013, at 9:07 PM, Greg Yang sorcer...@gmail.com javascript:
wrote:
I created a class CorrelatedProxy inheriting from AssociationProxy that
allows the creator function to depend on the owner instance of the
association proxy. Essentially it gets a attribute 'correlator' of the
something like lambda x: lambda y, z: Constructor(x, y, z), and then
intercepts the __get__ of AssociationProxy to create self.creator on the
fly by applying the owner instance to the correlator. Now consider the code
below.
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer
from sqlalchemy.ext.associationproxy import AssociationProxy
class CorrelatedProxy(AssociationProxy):
def __init__(self, *args, **kw):
self.correlator = kw.pop('correlator', None)
AssociationProxy.__init__(self, *args, **kw)
def __get__(self, obj, class_):
if obj:
self.creator = self.correlator(obj)
return AssociationProxy.__get__(self, obj, class_)
def correlated_proxy(*args, **kw):
return CorrelatedProxy(*args, **kw)
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
ab = relationship('AB', backref = 'a',
collection_class=attribute_mapped_collection('b'))
abnum = correlated_proxy('ab', 'num', correlator=\
lambda a: lambda b, n: AB(a=a, b=b, num=n))
class AB(Base):
__tablename__ = 'table_ab'
num = Column(Integer)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('table_b.id'), primary_key=True)
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True
[sqlalchemy] Strange behavior with a class inheriting from AssociationProxy
I created a class CorrelatedProxy inheriting from AssociationProxy that
allows the creator function to depend on the owner instance of the
association proxy. Essentially it gets a attribute 'correlator' of the
something like lambda x: lambda y, z: Constructor(x, y, z), and then
intercepts the __get__ of AssociationProxy to create self.creator on the
fly by applying the owner instance to the correlator. Now consider the code
below.
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer
from sqlalchemy.ext.associationproxy import AssociationProxy
class CorrelatedProxy(AssociationProxy):
def __init__(self, *args, **kw):
self.correlator = kw.pop('correlator', None)
AssociationProxy.__init__(self, *args, **kw)
def __get__(self, obj, class_):
if obj:
self.creator = self.correlator(obj)
return AssociationProxy.__get__(self, obj, class_)
def correlated_proxy(*args, **kw):
return CorrelatedProxy(*args, **kw)
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
ab = relationship('AB', backref = 'a',
collection_class=attribute_mapped_collection('b'))
abnum = correlated_proxy('ab', 'num', correlator=\
lambda a: lambda b, n: AB(a=a, b=b, num=n))
class AB(Base):
__tablename__ = 'table_ab'
num = Column(Integer)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('table_b.id'), primary_key=True)
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
ab = relationship('AB', backref = 'b',
collection_class=attribute_mapped_collection('a'))
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
aa = A()
bb = B()
aa.abnum[bb] = 1
assert aa.abnum[bb] == aa.abnum[None] == 1
Basically, no matter, what I do, any time I assign something to the
CorrelatedProxy, everything goes normally except that 'None' always becomes
a key, assigned to the last value I assigned to the proxy. I tried
debugging and tracing, but there's some quantum effect going on where if I
inspect some value, some other value changes. I for the life of me can't
figure out why it's doing this. I'm guessing it's some Instrumentation
effect of SA, but I don't understand the in and outs of that very much. I
currently can work around this by filtering out the None, but it'd be nice
to know why this occurs and whether it will affect any other elements of my
program with whatever is going on underneath.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] self-referential one to many relationship with a kind of composite foreign key
Oh wow, haha, this is pretty awesome. Never thought I'd use mapper by
itself at all! Thanks a lot! I guess the ancient technique is still
useful for something, eh
On Wednesday, June 12, 2013 11:36:21 AM UTC-5, Michael Bayer wrote:
Well for proxies you'd need to roll part of it manually, like a custom
collection that filters, that kind of thing.
But no matter. I'll apologize up front you don't get the hear the raucous
cackle I made when I got this to work. Because it required heading down
the dusty stairs to unlock the ancient texts, calling upon a technique that
in the early days I thought would be commonplace, but now is mostly unheard
of. The non-primary mapper means you're going to make a second map of a
class - mostly obsolete for querying because you can pass any kind of
selectable into a Query anyway using aliased(). But it is still useful
when you need to get a very unusual thing into relationship(). in 0.9,
you can join to this mapper without the nested SELECT as long as you're not
on sqlite, but this all works great in 0.8 too.
from sqlalchemy import *
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import *
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('table_a.id'))
children = relationship('A', backref=backref('parent',
remote_side=[id]))
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
a_re = relationship('A', backref='b_re')
b_table = B.__table__
a_table = A.__table__
# this is easier to map to an alias like this:
#child_bs = select([b_table, a_table.c.child_id],
#
use_labels=True).select_from(b_table.join(a_table)).alias()
# but mapping straight to the JOIN we get simpler queries
# (for lazyloads in 0.8, joins/joinedloads too in 0.9)
child_bs = b_table.join(a_table)
cbm = mapper(B, child_bs, properties=dict(
# make sure attribute names line up
# with our original names...
id=child_bs.c.table_b_id,
a_id=child_bs.c.table_a_id,
_b_a_id=child_bs.c.table_b_a_id,
),
non_primary=True)
B.children = relationship(cbm,
primaryjoin=and_(
B.id == foreign(remote(child_bs.c.table_b_id)),
B.a_id ==
foreign(remote(child_bs.c.table_a_child_id))
# or can get to the cols using mapped names
# B.id == foreign(remote(cbm.c.id)),
# B.a_id == foreign(remote(cbm.c.child_id))
),
viewonly=True,
collection_class=set)
e = create_engine(sqlite://, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
a1 = A()
a2 = A(parent=a1)
a3 = A(parent=a1)
a4 = A()
b1 = B(id=1, a_re=a1)
b2 = B(id=1, a_re=a2)
b3 = B(id=1, a_re=a3)
b4 = B(id=1, a_re=a4)
b5 = B(id=2, a_re=a1)
b6 = B(id=2, a_re=a2)
b7 = B(id=2, a_re=a3)
b8 = B(id=2, a_re=a4)
s.add_all([
a1, a2, a3, a4
])
s.commit()
assert b1.children == set([b2, b3])
assert b5.children == set([b6, b7])
s.expire_all()
# oh yes
for beta in s.query(B).options(joinedload(children)):
for b in beta.children:
assert b.id == beta.id
assert b.a_re in beta.a_re.children
On Jun 11, 2013, at 6:34 PM, Greg Yang sorcer...@gmail.com javascript:
wrote:
How would you use proxies? I can get B.a_re.children.b_re, but this
includes all Bs that have different B.id than I want along with the ones I
do want. I could just use a @property that issues SQL on every call, but
I'm trying to see if there are more efficient ways of doing this.
On Tuesday, June 11, 2013 4:18:20 PM UTC-5, Michael Bayer wrote:
getting it to work with secondary or only primaryjoin as it sometimes
works out is fairly complex and might not be possible. If secondary,
you might need to make secondary an aliased SELECT statement, or in 0.9
maybe it can be a a JOIN, that represents all the intermediary rows. Might
work, might not, would have to spend a few hours with it.
Is there a reason you can't just route to the related
B.a.children.bshttp://b.a.children.bs/using proxies? Or a @property
based loader?
On Jun 11, 2013, at 4:45 PM, Greg Yang sorcer...@gmail.com wrote:
Consider these 2 mapped classes
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from
[sqlalchemy] self-referential one to many relationship with a kind of composite foreign key
Consider these 2 mapped classes
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm.util import aliased
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.sql.expression import and_
from sqlalchemy.types import Integer, String
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('table_a.id'))
children = relationship('A', backref = 'parent', remote_side=[id])
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
a_re = relationship('A', backref='b_re')
What I want to do is have a self-referential relationship in B that routes
through A's children relationship while keeping B.id the same. More
explicitly I want some relationship B.children such that for any instance
beta of B
for b in beta.children:
assert b.id == beta.id
assert b.a_re in beta.a_re.children
Now, if the condition b.id == beta.id is ignored, then it's just a plain
association table many-to-many relationship, something like
B.children = relationship('B', secondary=A.__table__,
primaryjoin=B.a_id==A.id, secondaryjoin=B.a_id==A.child_id, viewonly=True)
But with the b.id == beta.id condition I need to refer to table_b twice in
the join table_b JOIN table_a JOIN table_b, and I'm not sure how to do that
in relationship.
I've tried this
BB = aliased(B)
B.children = relationship('BB', secondary=A.__table__,
primaryjoin=B.a_id==A.id,
secondaryjoin='''and_(A.id==BB.a_id,
B.id==BB.id)''',
viewonly=True)
but it seems like BB is not recognized by the ORM in mapping.
How do I do this?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] self-referential one to many relationship with a kind of composite foreign key
How would you use proxies? I can get B.a_re.children.b_re, but this
includes all Bs that have different B.id than I want along with the ones I
do want. I could just use a @property that issues SQL on every call, but
I'm trying to see if there are more efficient ways of doing this.
On Tuesday, June 11, 2013 4:18:20 PM UTC-5, Michael Bayer wrote:
getting it to work with secondary or only primaryjoin as it sometimes
works out is fairly complex and might not be possible. If secondary,
you might need to make secondary an aliased SELECT statement, or in 0.9
maybe it can be a a JOIN, that represents all the intermediary rows. Might
work, might not, would have to spend a few hours with it.
Is there a reason you can't just route to the related B.a.children.bsusing
proxies? Or a @property based loader?
On Jun 11, 2013, at 4:45 PM, Greg Yang sorcer...@gmail.com javascript:
wrote:
Consider these 2 mapped classes
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm.util import aliased
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.sql.expression import and_
from sqlalchemy.types import Integer, String
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('table_a.id'))
children = relationship('A', backref = 'parent', remote_side=[id])
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'), primary_key=True)
a_re = relationship('A', backref='b_re')
What I want to do is have a self-referential relationship in B that routes
through A's children relationship while keeping B.id the same. More
explicitly I want some relationship B.children such that for any instance
beta of B
for b in beta.children:
assert b.id == beta.id
assert b.a_re in beta.a_re.children
Now, if the condition b.id == beta.id is ignored, then it's just a plain
association table many-to-many relationship, something like
B.children = relationship('B', secondary=A.__table__,
primaryjoin=B.a_id==A.id, secondaryjoin=B.a_id==A.child_id, viewonly=True)
But with the b.id == beta.id condition I need to refer to table_b twice
in the join table_b JOIN table_a JOIN table_b, and I'm not sure how to do
that in relationship.
I've tried this
BB = aliased(B)
B.children = relationship('BB', secondary=A.__table__,
primaryjoin=B.a_id==A.id,
secondaryjoin='''and_(A.id==BB.a_id,
B.id==BB.id)''',
viewonly=True)
but it seems like BB is not recognized by the ORM in mapping.
How do I do this?
--
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.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Ordering by composite column gives sqlite3 OperationalError
Right now query.order_by(composite) gives a sqlite3 operational error,
because the rendered SQL is ORDER BY (composite_val1, composite_val2,
composite_val3) instead of ORDER BY composite_val1, composite_val2,
composite_val3. (The parenthesis is causing an error)
For example, consider the code below modified from the documentation.
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship, composite
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
import itertools
Base = declarative_base()
class Point(object):
def __init__(self, x, y):
self.x = x
self.y = y
def __composite_values__(self):
return self.x, self.y
def __repr__(self):
return Point(x=%r, y=%r) % (self.x, self.y)
def __eq__(self, other):
return isinstance(other, Point) and \
other.x == self.x and \
other.y == self.y
def __ne__(self, other):
return not self.__eq__(other)
class Vertex(Base):
__tablename__ = 'vertice'
id = Column(Integer, primary_key=True)
x1 = Column(Integer)
y1 = Column(Integer)
x2 = Column(Integer)
y2 = Column(Integer)
start = composite(Point, x1, y1)
end = composite(Point, x2, y2)
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
pts = [((1, 2), (3, 4)),
((2, 3), (1, 5)),
((0, 5), (6, 3))]
session.add_all(itertools.starmap(
lambda a, b: Vertex(start=Point(*a),
end=Point(*b)),
pts))
We run the following in the console:
q = session.query(Vertex).order_by(Vertex.start)
q
Out[1]: sqlalchemy.orm.query.Query at 0x3bc1f30
str(q)
Out[1]: 'SELECT vertice.id AS vertice_id, vertice.x1 AS vertice_x1,
vertice.y1 AS vertice_y1, vertice.x2 AS vertice_x2, vertice.y2 AS
vertice_y2 \nFROM vertice ORDER BY (vertice.x1, vertice.y1)'
q.all()
Traceback (most recent call last):
File C:\Anaconda\Lib\site-packages\IPython\core\interactiveshell.py,
line 2731, in run_code
exec code_obj in self.user_global_ns, self.user_ns
File ipython-input-1-511354a8265d, line 1, in module
q.all()
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py,
line 2140, in all
return list(self)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py,
line 2252, in __iter__
return self._execute_and_instances(context)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py,
line 2267, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py,
line 664, in execute
params)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py,
line 764, in _execute_clauseelement
compiled_sql, distilled_params
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py,
line 878, in _execute_context
context)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py,
line 871, in _execute_context
context)
File
C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\default.py,
line 320, in do_execute
cursor.execute(statement, parameters)
OperationalError: (OperationalError) near ,: syntax error u'SELECT
vertice.id AS vertice_id, vertice.x1 AS vertice_x1, vertice.y1 AS
vertice_y1, vertice.x2 AS vertice_x2, vertice.y2 AS vertice_y2 \nFROM
vertice ORDER BY (vertice.x1, vertice.y1)' ()
Whereas, if we directly execute the correct SQL, without the parenthesis,
session.execute(u'SELECT vertice.id AS vertice_id, vertice.x1 AS
vertice_x1, vertice.y1 AS vertice_y1, vertice.x2 AS vertice_x2, vertice.y2
AS vertice_y2 \nFROM vertice ORDER BY vertice.x1, vertice.y1' )
Out[1]: sqlalchemy.engine.result.ResultProxy at 0x3bc1d70
_.fetchall()
Out[1]: [(3, 0, 5, 6, 3), (1, 1, 2, 3, 4), (2, 2, 3, 1, 5)]
We get the right result back, albeit not wrapped in Vertex objects
So it seems like a fairly simple bug.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] AssociationProxy's behavior with == None seems unintuitive
Awesome! The changes should be able to cover the issue.
On Saturday, June 8, 2013 12:40:51 PM UTC-5, Michael Bayer wrote:
On Jun 8, 2013, at 1:33 AM, Greg Yang sorcer...@gmail.com javascript:
wrote:
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
b1 = B()
b2 = B()
b3 = B()
b1.a_color = 'blue'
session.add_all([b1, b2, b3])
q = session.query(B).filter(B.a_color == None).all()
p = session.query(B).filter(B.a_color != None).all()
assert q == []
assert set(p) == set([b1, b2, b3])
I find it surprising that, when only b1 really has a color string
through B.a_color, filtering for B.a_color == None doesn't return b2 and
b3, and filtering for B.a_color != None returns b2 and b3 at all. The
latter is especially unintuitive.
The scalar comparison case was only rudimentally implemented. Along
these lines, the case of query(B).filter(B.a_color != 'blue') also needed
to be covered. Keeping in mind that in SQL, x != 'somevalue' does *not*
return rows where x IS NULL, this will now return those B's for which an
A with non-NULL a_color is present but not equal to 'blue'.
While the results are pretty wrong especially in the != None case, this
seems strongly like an 0.9 only change, as it is very possible that
applications are relying upon the current behavior. Particularly the x
== None case not returning records for which an association row is not
present could break an application that isn't prepared for those rows. The
x != 'somevalue' case behaves very differently as well, no longer
returning rows for which the association is missing. An illustration of
all these cases contrasted can be seen in
http://www.sqlalchemy.org/trac/ticket/2751. The changes are committed
in r20d1e9c3fa8ccc992079.
On a similar note, is there a way to get a_re.has() or equivalent
through only a_color? This can be important when a_re is supposed to be a
private variable, and only the string a_color is exposed. I originally
thought that != None would do the trick but that doesn't work, as I've
shown here.
this is like a special operation so I also added support for an empty
call to B.a_color.has(). If you put criterion in there, it raises, because
while you can do that, it doesn't really make any sense. The difference
between B.a_color.has() and B.a_color != None is that the former will
return you rows for which an A exists, regardless of whether or not A.color
is NULL.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] AssociationProxy's behavior with == None seems unintuitive
Right now a filter clause AssociationProxy == None
Consider the following code:
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
color = Column(String)
def __init__(self, color):
self.color = color
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'))
a_re = relationship('A', backref='b_re')
a_color = association_proxy('a_re', 'color')
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
b1 = B()
b2 = B()
b3 = B()
b1.a_color = 'blue'
session.add_all([b1, b2, b3])
q = session.query(B).filter(B.a_color == None).all()
p = session.query(B).filter(B.a_color != None).all()
assert q == []
assert set(p) == set([b1, b2, b3])
I find it surprising that, when only b1 really has a color string through
B.a_color, filtering for B.a_color == None doesn't return b2 and b3, and
filtering for B.a_color != None returns b2 and b3 at all. The latter is
especially unintuitive.
Now I know what's going on:
print B.a_color == None
EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL)
print B.a_color != None
NOT (EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL))
The == clause requires that the relationship a_re has a valid target in the
first place before checking for a_re.color IS NULL, and the != is just the
negation of that. I understand that this is the desired action when the
right side is some non-null value like blue, but in this case it should
be implemented along the lines of
print B.a_color == None
table_b.a_id IS NULL
OR
EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL)
print B.a_color != None
table_b.a_id IS NOT NULL
AND
NOT (EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL))
On a similar note, is there a way to get a_re.has() or equivalent through
only a_color? This can be important when a_re is supposed to be a private
variable, and only the string a_color is exposed. I originally thought that
!= None would do the trick but that doesn't work, as I've shown here.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] How to disable Foreign Keys to clear database
Hi, I was naively trying to clear a db earlier in the day and I ran into this problem: 'Cannot delete or update a parent row: a foreign key constraint fails' So as anyone would do I've been searching online and through the documentation on how to turn the foreign keys off, on delete = Cascade, delete orphan-cascade what have you. So many hours and many stack traces later I'd like to ask your help in this matter def cleanMapping(self, dbName): connection = self.dbEngines[dbName].connect() trans = connection.begin() for my_table in reversed(self.dbMetaData[dbName].tables.values()): keys= my_table.foreign_keys for column in my_table.columns: for key in keys: my_table = Table(my_table, self.dbMetaData[dbName], Column(column, Integer, ForeignKey(key, onupdate=CASCADE, ondelete = CASCADE) ,primary_key=True)) connection.execute(my_table.delete()) trans.commit() So I'm fresh out of ideas; Everytime I try using this code I get sqlalchemy.exc.ArgumentError: Column object already assigned to Table col -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/QM0GVC402DAJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Storing Nested Lists
Excellent. Thanks very much. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Storing Nested Lists
Hi All, I'm new to sqlalchemy. I've been reading the documentation and group archives, but can't really find an answer to my question. I suspect it's a question of terminology, and that I don't really know the term for what I'm looking for. Can I map a nested list to one column, and have my nested list returned to me intact? A simple example is a list like: ['a','b','c', ['x','y','z',['m','n','o']]] If anyone can point me in the right direction, I'd much appreciate it. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Linking Tables using Reflection
Hello,
How do I create a link between two tables (1:1) when using reflection
to define my objects? Suppose my database has tables:
Table_A
id (Integer, primary_key)
name (String)
Table_B
id (Integer, primary_key)
shoe_size (VarChar(5))
In my program I do:
A = Table('Table_A', metadate, engine, autoload_with=engine)
B = Table('Table_B', metadate, engine, autoload_with=engine)
Now, how do I go about creating a link, say A_id, from table 'B' to
table 'A.id'
Thanks for your help!
--greg
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Reflection, Foreign Keys, Auto Defaults, Oh My!
Hello, Everyone,
I'm getting to know SQLAlchemy after watching the PyCon tutorials and
have a couple of questions I hope you can either can/will answer for
me or point me in the correct direction.
I'm using SQLAlchemy v 0.5.3 with Python 2.6 on Windows hitting a SQL
Server database. I am using reflection to define my Python objects
and can see the column names and values as expected. Very nice.
Question 1: In my database I have a timestamp column defined which
will default to the current system timestamp on the db server. When I
create an object then commit it, the database complains about the ts
column. Can I exclude it from the Insert query? I found an earlier
thread on this but it appeared (to me) that the op was not using
reflection. If I have a very simplified class:
class FileInfo(Base):
__table__ = Table('file_info', metadata, autoload=True)
def __init__(self, path, client):
self.path = path
self.client = client
How would I specify not to add a ts column (which exists in the db) to
the Insert query?
Question 2: Along the same lines, using reflection, can I define
foreign keys and backrefs in my objects? I'd like to link the above
file_info id to a file_id column in another table (also defined
using reflection). I see how to do this when I explicitly define my
tables, but I have an existing database I would like to work with.
Thanks for your help. I'm looking forward to learning more about all
of this!
--greg
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Reflection, Foreign Keys, Auto Defaults, Oh My!
I just noticed that part of this has been answered yesterday. I apologize for the redundancy; I did not think I had posted the earlier request. --greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
Yeah, I can get it to you, but in the reply box I'm not seeing anything here about attachments. There's just Send, Discard, Add Cc, or Edit Subject. I am set up to use this group only through the browser. On Jan 23, 12:35 pm, Don Dwiggins d...@dondwiggins.net wrote: Greg wrote: I think I found one that might help. Where can I upload this HTML file it generated? In case you don't get a better answer: open the file in your browser, then cut paste the text the browser displays (or take a screenshot of the browser window and upload that as a gif or jpg). -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
Thanks, I'll try this out and let you know how it goes. On Jan 23, 12:43 pm, Rick Morrison rickmorri...@gmail.com wrote: Uh, did you guys not see my last message in this thread? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
The commit mentioned earlier fixed the issue. Thanks for all the help. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
I think I found one that might help. Where can I upload this HTML file it generated? On Jan 22, 12:57 pm, Don Dwiggins d...@dondwiggins.net wrote: Greg wrote: Unfortunately, I'm connecting remotely via iODBC and do not have said tools. I'm running Ubuntu Linux, if you know of any way to get the same thing prettied up, let me know and I will. Ahh, sorry; I've never had occasion to work with MSSQL through Linux. Maybe the best approach would be to use a Python shell to run the sp_columns proc and format the results yourself. (Or, you might check whether there are already some decent Linux GUI tools like Query Analyzer.) FWIW, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
I guess I'm kind of confused as to how it's supposed to create a table
that it doesn't even really know about yet. It hasn't successfully
reflected the table yet, so shouldn't know anything about its
structure. It seems like it'd just create an empty table at this
point.
engine = create_engine('CONNECTION STRING HERE')
meta = MetaData()
meta.bind = engine
# SQLAlchemy doesn't know about the remote table structure yet, how
will this do us any good?
meta.create_all()
# This is where I get that exception listed earlier. SQLAlche
artreqs = Table('tb_FSAR_Data_SampArtReq', meta, autoload=True,
autoload_with=engine)
On Jan 21, 1:14 am, Michael Bayer mike...@zzzcomputing.com wrote:
yeah i meant send along the CREATE TABLE to the mailing list here.
or a describe, whatever shows us what column type might be failing.
On Jan 20, 2009, at 9:45 PM, Greg wrote:
How do I go about doing this? Showing the create table?
On Jan 20, 4:54 pm, Rick Morrison rickmorri...@gmail.com wrote:
I'm just trying to introspect an existing production database, not
create any new tables.
The structure of the table is read when reflecting the table: it's
likely
that an unusual column definition would trigger an error like this,
and it
would be helpful to someone diagnosing the problem to get a better
clue as
to what might be happening.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
As a disclaimer, this is a legacy system in which I have no control over, so what you are about to see is pretty disturbing :) http://pastebin.com/m10d49ac1 The formatting is pretty crazy, I'm hoping you can make use of this. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
Unfortunately, I'm connecting remotely via iODBC and do not have said tools. I'm running Ubuntu Linux, if you know of any way to get the same thing prettied up, let me know and I will. On Jan 21, 12:22 pm, Don Dwiggins d...@dondwiggins.net wrote: Greg wrote: As a disclaimer, this is a legacy system in which I have no control over, so what you are about to see is pretty disturbing :) http://pastebin.com/m10d49ac1 The formatting is pretty crazy, I'm hoping you can make use of this. If you have the MSSQL tools handy, try this: - Open Query Analyzer, connect to the server - Press F8 to get the Object Browser (unless it's already open - Go to the database and table in question. - Right click on the table name - Select Script object to new window as; in the submenu choose Create You should have a reasonably formatted table creation script, including indices and FKs. HTH, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] MSSQL Reflection Error
I've got the following code to reflect an MS SQL 8 database using the
latest stable pyodbc, python 2.5, and SQLAlchemy 5.1:
engine = create_engine('mssql://userXXX:pass...@mydsn', echo=False)
meta = MetaData()
meta.bind = engine
artreqs = Table('tb_FSAR_Data_SampArtReq', meta, autoload=True,
autoload_with=engine)
And am getting this error when I run it:
Traceback (most recent call last):
File alchemy.py, line 9, in module
artreqs = Table('tb_FSAR_Data_SampArtReq', meta, autoload=True,
autoload_with=engine)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/
sqlalchemy/schema.py, line 113, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/
sqlalchemy/schema.py, line 239, in __init__
autoload_with.reflecttable(self, include_columns=include_columns)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/
sqlalchemy/engine/base.py, line 1265, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/
sqlalchemy/databases/mssql.py, line 1157, in reflecttable
coltype = coltype(*args, **kwargs)
TypeError: __init__() takes at most 2 arguments (3 given)
Any ideas? I'm not too sure what's going on here.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
If I understand, i only need to do that when I want to create a table.
I'm just trying to introspect an existing production database, not
create any new tables.
On Jan 20, 4:37 pm, Michael Bayer mike...@zzzcomputing.com wrote:
send along what the CREATE TABLE for the table in question looks like.
On Jan 20, 2009, at 4:20 PM, Greg wrote:
I've got the following code to reflect an MS SQL 8 database using the
latest stable pyodbc, python 2.5, and SQLAlchemy 5.1:
engine = create_engine('mssql://userXXX:pass...@mydsn', echo=False)
meta = MetaData()
meta.bind = engine
artreqs = Table('tb_FSAR_Data_SampArtReq', meta, autoload=True,
autoload_with=engine)
And am getting this error when I run it:
Traceback (most recent call last):
File alchemy.py, line 9, in module
artreqs = Table('tb_FSAR_Data_SampArtReq', meta, autoload=True,
autoload_with=engine)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/
sqlalchemy/schema.py, line 113, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/
sqlalchemy/schema.py, line 239, in __init__
autoload_with.reflecttable(self, include_columns=include_columns)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/
sqlalchemy/engine/base.py, line 1265, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/
sqlalchemy/databases/mssql.py, line 1157, in reflecttable
coltype = coltype(*args, **kwargs)
TypeError: __init__() takes at most 2 arguments (3 given)
Any ideas? I'm not too sure what's going on here.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
How do I go about doing this? Showing the create table? On Jan 20, 4:54 pm, Rick Morrison rickmorri...@gmail.com wrote: I'm just trying to introspect an existing production database, not create any new tables. The structure of the table is read when reflecting the table: it's likely that an unusual column definition would trigger an error like this, and it would be helpful to someone diagnosing the problem to get a better clue as to what might be happening. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] problem with join, count on 0.5.0rc3
This following request works fine and produce the result I was
expecting session.query(UserRss, Rss, Item).join([Rss, Item]). But
count doesn't work. Is it a bug, or did I miss something ?
str(session.query(UserRss, Rss, Item).join([Rss, Item]).count())
Traceback (most recent call last):
File console, line 1, in module
File /opt/local/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc3-
py2.5.egg/sqlalchemy/orm/query.py, line 1251, in count
return self._col_aggregate(sql.literal_column('1'),
sql.func.count,
nested_cols=list(self._only_mapper_zero().primary_key))
File /opt/local/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc3-
py2.5.egg/sqlalchemy/orm/query.py, line 241, in _only_mapper_zero
raise sa_exc.InvalidRequestError(This operation requires a Query
against a single mapper.)
InvalidRequestError: This operation requires a Query against a single
mapper.
Thanks.
Greg
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Release posted on homepage!
I noticed current release information is now available on the SQLAlchemy homepage! Good job! Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Database Disconnection Detection
I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Update Latest News on website?
Perhaps I missed it, but it would be great if you guys would update the Latest News section on the SA homepage when a new release is made available. Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
On Apr 2, 12:27 pm, Michael Bayer [EMAIL PROTECTED] wrote: this feature is not supported right now for most DB's except mysql but is being addressed in a current ongoing ticket and will be available in the next release. I'm assuming you are talking about autoreconnect here? If so, I would be curious to hear the intended direction. I cam easily imagine a variety of needs assocaited with such a feature. For example, blocking during reconnect/try is probably fine for some class of applications but problematic for others (would be for me). however, its possible that some DBAPIs do not raise the error in a manner which can ever be consistently caught; for example we've had problems getting this feature to work with Psycopg2 since the exception is thrown at unpredictable times (i.e. not within execute() or cursor() call; instead, in the middle of parsing a string or something which has nothing to do with a psycopg2 operation...we're not sure if this is some artifact of native python extensions or what). Interesting. So there is no generic method for catching this category of error with SQLAlchemy right now, assuming the DBAPI actually returns an exception? Greg On Apr 2, 2007, at 1:07 PM, Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Update Latest News on website?
Well, according to the website, Version 3.0 of SQLAlchemy is the latest exciting release. ;) And that was was October 2006. As is, I have to go to the download page to see if a new release is available. Then I have to download the release to see if any of the features/ fixes interest me. One could reasonbly argue the website should be the goto place for latest news and the other sites are simply extras. I must say, it's very strange to go to a product's website to find out I have to go elsewhere to find out the latest skinny... Or that I have to download it to find out if I'm interested in it. The last thing I want is to subscribe to yet more stuff. If something is not broken, I don't want to know about new releases. If on the other hand, something is broken, I'd love to be able to go to the package's website and rapidly determine if the problem has already been addressed. Just some food for thought. Greg On Apr 2, 12:33 pm, Michael Bayer [EMAIL PROTECTED] wrote: releases are announced on this mailing list as well as the Python cheeseshop (and all feeds which aggregate it, such as planet python). you can also subscribe to be a watcher on sourceforge where release emails also get sent out. im hesitant to add to the regular news sidebar on the site since we do a lot more releases than there are actual interesting news pieces (so far), and the news section would be pretty boring (also the news on the site is not RSS aggregated or anything...its not very functional). On Apr 2, 2007, at 1:08 PM, Greg Copeland wrote: Perhaps I missed it, but it would be great if you guys would update the Latest News section on the SA homepage when a new release is made available. Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
Frankly I'm very surprised this class of error has not been previously addressed. I'll have to test and see what happens. I was planning on doing the defensive coding, followed by the testing to make sure it is caught and recovers correctly. Greg On Apr 2, 12:36 pm, Paul Johnston [EMAIL PROTECTED] wrote: Greg, If you can send the traceback you get when such an error occurs, that would be helpful for providing Oracle support for this condition. Paul Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
Please don't take my comment the wrong way. It's actually an off handed compliment. SA seems fairly robust and developmentally strong. As such, it's just surprising that something like error handling doesn't have comprehensive coverage. Now I can certainly understand why, since the DBAPI doesn't cover the bases. Any idea who we should mention this to revise the DBAPI? Anyone know if a DBAPI PEP already exists to ensure parity for SQL independant error handling? In my opinion, any failure to provide robust error handling by the DBAPI specification is contrary to the fundamental requirements of DBAPI. This defeats the porpose of DBAPI if every robust app must have DB specific error handling. Then again, I'm sure I'm preaching to the choir here. ;) Okay, so what's SA's take on this problem domain? Applcation executes a query but the database has died between the time the connection was established and the time the query was executed. Does SA attempt to return an SA specific exception to the calling application? Does it simply allow the native DB exception to be returned to the application? Meaning, despire the fact that I'm using SA, my application must be prepared to catch a cx_Oracle specific exception? Worse, you're saying cx_Oracle may not return an exception (thusly, the previous request)? Or, how about this. Application has a pool of connections. The database goes down. No valid connections exist within the cool; having been previously reaped. Application attempts to query database. What does SA return? In this case, it should know it does not have any connections within its pool. Is this documented? Is there any documention which describes the known failure modes which SA detects? If so, link please? Greg On Apr 2, 1:11 pm, Michael Bayer [EMAIL PROTECTED] wrote: if you care to view trac, its ticket #516. also, reasons like these are why i keep SA at a relatively low version number (0.3) despite some saying this is underestimating. in any case, as the experience with psycopg2 is illustrating, i am not totally confident the state of DBAPI is going to really let this feature work very well on all platforms, since it does not define where such an error condition should be thrown, what the specific error should be, nor is there any documented way to ping a database reliably (therefore, be less surprised about SA, we arent the only ones insufficiently addressing this). we are not blocking on reconnect/try. the error gets thrown in all cases - the connection is then removed from the pool. so while its auto-reconnecting, we are not going for seamless midstream refreshing. trying to reconnect midstream would be extremely problematic particularly that we cant make any assumptions about ongoing transactional state and such. to minimize errors like these if you think your database is going to crash a lot, put a very low pool_recycle setting on your engine...that way connections can be kept very fresh. also there has just been discusson on this list about this issue not more than a week ago so feel free to browse the archives a bit. On Apr 2, 2007, at 1:59 PM, Greg Copeland wrote: Frankly I'm very surprised this class of error has not been previously addressed. I'll have to test and see what happens. I was planning on doing the defensive coding, followed by the testing to make sure it is caught and recovers correctly. Greg On Apr 2, 12:36 pm, Paul Johnston [EMAIL PROTECTED] wrote: Greg, If you can send the traceback you get when such an error occurs, that would be helpful for providing Oracle support for this condition. Paul Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
On Apr 2, 12:36 pm, Paul Johnston [EMAIL PROTECTED] wrote: Greg, If you can send the traceback you get when such an error occurs, that would be helpful for providing Oracle support for this condition. Paul I assume this means that the exception is not being caught and wrapped by an SQLAlchemy.exceptions exception? As you can tell, the envionment for this test is cygwin. I also run/test on RHEL Linux. Traceback (most recent call last): File stdin, line 1, in ? File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 266, in execute File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 276, in execute_text File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 350, in _execute_raw File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 365, in _execute File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 254, in _autorollback File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 239, in _rollback_impl File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/ default.py, line 55, in do_rollback cx_Oracle.DatabaseError: ORA-03114: not connected to ORACLE To get this, I started python and interactively created and engine. I then grabbed a connection by using engine.connect(). Once I had my connection, I then pulled my network cable and issued the execute on my connection. Let me know if you want me to go about this from another direction. Greg Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
connection, I then pulled my network cable and issued the execute on my connection. Nice. I've been restarting the database server (for MSSQL, MySQL, Postgres) but I wonder if that will yield slightly different results. If you need me to shutdown the database and retry, I can try that later this week. My DB server is a shared server so shutting it down for the whole group is not well receieved. ;) Paul Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] PyInstaller Support
If anyone cares, the next release of PyInstaller has have support for SQLAlchemy. I tested with version 3.5 of SQLAlchemy and 1.3 of PyInstaller. Support has already been checked into the RC system. So look for any release newer than 1.3 of PyInstaller. If you don't already know, PyInstaller is yet another python freeze application which can be used to create stand-alone binaries, including the python runtime and dependant shared libraries. I worked with the PyInstaller author to create the corrosponding hook contents to make sure the dynamically loaded database modules are properly imported. If you don't want to wait for the next release, search the google group and you'll find the proper hook file contents. Hope this isn't too much of a commercial. Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: PyInstaller Support
I hate to say it, but I honestly don't know. I've seen mention of pkg_resources in various blogs and messages but I've never bothered to learn exactly what it is. As such, I can't answer your question. I did some quick googling and found mention that pkg_resource datafiles are known to work. In the same thread, they make mention of the illogical use of direct zip/egg access because of the way pyinstaller works. Which is to say, pyinstaller is simply going to remove the modules from from the zip/egg and place it into the binary/ directory (depending on how PyInstaller is used). This means, to support egg/zip, you make it install the egg in uncompressed format and PyInstaller works fine. That's the way I use SQLAlchemy and can make a single binary which contains cx_Oracle, SQLAlchemy, my application (big), the python runtime (2.4; which 2.3 is installed on the system), and all the dependents, including shared libraries for Oracle, sockets, etc. I've even tried UPX binaries, which turns my 13.5M binary into a 9M binary. Hopefully that answers your question, but I doubt it. I highly recommend you pop over to the PyInstaller Google groups and ask your question. Hopefully the author will be able to better address your questions/concerns. Greg On Apr 2, 6:02 pm, Michael Bayer [EMAIL PROTECTED] wrote: thats great. we are going to be checking in some code soon that will look for alternate database modules via pkg_resources...is that supported by PyInstaller also ? On Apr 2, 2007, at 5:32 PM, Greg Copeland wrote: If anyone cares, the next release of PyInstaller has have support for SQLAlchemy. I tested with version 3.5 of SQLAlchemy and 1.3 of PyInstaller. Support has already been checked into the RC system. So look for any release newer than 1.3 of PyInstaller. If you don't already know, PyInstaller is yet another python freeze application which can be used to create stand-alone binaries, including the python runtime and dependant shared libraries. I worked with the PyInstaller author to create the corrosponding hook contents to make sure the dynamically loaded database modules are properly imported. If you don't want to wait for the next release, search the google group and you'll find the proper hook file contents. Hope this isn't too much of a commercial. Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: memory leak with psyco
One of the optimizations that psyco performs is function specialization. It is possible this is what is occuring. Regardless, you should see memory consumption taper after sustained use, under the assumption that psyco will eventually stop creating new variants of the specialized function. You could test this by driving your application with a set of fixed inputs. One of the gotchas of using psyco is memory use goes through the roof in exchange for much improved performance. Greg On Mar 21, 10:49 am, Andrew Stromnov [EMAIL PROTECTED] wrote: Using psyco (with full() optimization) and SA (only SQL level) together leads to memory leak. :\ I'm not sure, but it is probable that SA makes many functions (function generators?) which are optimized by psyco, but are not released after use. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Functions with out parameters?
Great that's working. And Yuck! Having to do that directly on the cursor really makes me enjoy SA's capabilities. Can't wait until that's a supported feature. Keep up the good work guys! Greg On Mar 9, 10:42 am, Michael Bayer [EMAIL PROTECTED] wrote: you can pull raw_connection() off of the engine. On Mar 9, 11:33 am, Greg Copeland [EMAIL PROTECTED] wrote: On Mar 8, 4:46 pm, Michael Bayer [EMAIL PROTECTED] wrote: we have a notion of how this feature can be implemented with oracle but the actual work has not been performed. i just put this notion into ticket #507 since I hadnt written it down anywhere. for now theres not really a good way to make it happen within SA's querying facilities; youd have to drop into cx_oracle to do it. Dang it. I was afraid of that. How do I grab the cx_Oracle cursor from SA? Thanks, Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Functions with out parameters?
This was cross posted to comp.lang.python because it would not let me join here. At any rate, I'm using engine.func.blah(1, 2, 3, error ).execute() and get: sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-06572: Function blah has out arguments. Anthing special I need to do to call an Oracle function via the func method, which also has output parameters? Are they even supported? Function blah looks something like: create or replace function blah( arg1 IN VARCHAR2(15), arg2 IN INTEGER, arg3 IN INTEGER, arg4 OUT NOCOPY VARCHAR2 ) RETURN INTEGER ... Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
