Re: [sqlalchemy] Entity name - multiple schema - relationship
yes. but i'll always have to use a bind anyway. this routing style has a problem to identify what entity you're querying (and then the mapper) when using functions, either at column level session.query(func.count(MyObj.attribute)) or at the query level itself session.query(MyObj.attribute).count(), among other functions (exists, etc). best regards, richard. On 05/18/2014 05:04 PM, Michael Bayer wrote: well if you're working with the RoutingSession example you can manufacture get_bind() and using_bind() to work in any way you want. If you have the engine, as the example shows, session.using_bind(some_bind).query(...) http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ On May 18, 2014, at 3:28 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: yeah, well, i was using implicit for little things and explicit for the bigger ones, but it seems that even small things are error prone :) i was just wondering if there's a faster way to do it, even explicit, so i can get a class (whatever it is) to query against an engine i know (so there's the key to make things work). if I have a metadata bind to some engine, is there a quick (and performatic) way to know it? Em 2014-05-18 16:21, Michael Bayer escreveu: On May 18, 2014, at 12:10 PM, Richard Gerd Kuesters rich...@humantech.com.br mailto:rich...@humantech.com.br wrote: well, this part is still working, as long as i remember. my biggest problem now - and has been for the last couple of years - is to manage this mayhem of classes and engines AND sessions, because everyone wants to go online with their data. i'm writting and rewriting a session manager that can simplify my life for a long time, i got close to get things done with your RoutingSession vertical example, but it doesn't work very well with functions, session.query(...).count() or .exists() and so on. i'm writing code as hell and still far from an acceptable, performatic session router (?) for a class that can come from anywhere, for one or more specific engines, without grind string ids everywhere. well, i think my problem have a lot of weaknesses to discuss ... but, one at a time. for now, any tips on enterprise multi-everything session routing? :) you're trying to route to different sessions based on the intricacies of what's inside a SELECT statement? See I just would never do that, it's very complicated and error prone. I'd have an explicit node name sent in right at the top. Explicit is better than implicit. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] SQLAlchemy and Informix advice
I'm exploring SQLAlchemy features for a new project which must use an IBM Informix database. Found the ibm_db and ibm_db_sa modules to integrate with SQLAlchemy and successfully connected to the database after some tries. I found the ibm_db_sa module only supports DB2 and NOT Informix. At least this is what was answered me on the project page. Now I have a couple of newbie questions for the SQLAlchemy experts: 1. Does anyone know if the Informix support is something will be added? (planned, considering). I don't know if this module is developed from IBM or SQLAlchemy so, I'm not even sure to whom address this question. 2. I see, I can connect to my Informix database, but can't use the ibm_db_sa module. Does this mean I can only use SQLAlchemy Core features and not the ORM? That would be sad since the ORM is exactly what I planned to use. Thanks for help, Massimo Valle -- 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] SQLAlchemy and Informix advice
Hi Massimo! In the past, I have used SQLAlchemy to connect to Informix (using the db2 driver), but it was for pure lazyness -- I had to write everything by hand, and my application already was using SQLAlchemy, so ... But that was back in 2007. Those codes are long dead and not in my possession. Let's see if I can address those problems better for you: *1. */Does anyone know if the Informix support is something will be added?/: you can see all official dialects supported by SQLAlchemy in here: http://docs.sqlalchemy.org/en/rel_0_9/dialects/index.html All supported dialects in SQLAlchemy have something in common (IMHO): SQL, obviously. Informix have support to SQL, of couse, but it also have a lot of other tools, spatial support, JSON and so on; that I may find hard see completely supported by SQLAlchemy. Of course, there are some engines that looks quite familiar to these, PostgreSQL, -but- I think it's support in SQLAlchemy is a completely different subject. Are you planning to use SQLAlchemy ORM on top of Informix for common SQL tasks? If so, you can adapt - or even contribute - to the ibm_db_sa adapter :) Here's a good lecture: http://techspot.zzzeek.org/2012/10/25/supporting-a-very-interesting-new-database/ -- but, of course, the SQLAlchemy source code is the best way to know how dialects works under the hood. If you're planning to use with the new JSON integration, boy I think you should stick to the roots somewhere, away from SQLAlchemy and close to Mongo's adapters (somewhere else I had bumped with MongoAlchemy http://www.mongoalchemy.org/, that provides a quite similar API to Mongo as SQLAlchemy offers to others RDBMs, but IMHO it looks like a huge waste of code time since SQL and NoSQL databases have just one thing in common: they store data somewhere). *2. */Does this mean I can only use SQLAlchemy Core features and not the ORM?/: I don't know about that anymore, since everything I write to Informix (if and when applied) are not SQLAlchemy (or even Python) related, at all. If you provide more information about what you're trying to accomplish, it may still be possible with SQLAlcheny, given some circumstances. Best regards, Richard. On 05/19/2014 08:50 AM, Massimo Valle wrote: I'm exploring SQLAlchemy features for a new project which must use an IBM Informix database. Found the ibm_db and ibm_db_sa modules to integrate with SQLAlchemy and successfully connected to the database after some tries. I found the ibm_db_sa module only supports DB2 and NOT Informix. At least this is what was answered me on the project page. Now I have a couple of newbie questions for the SQLAlchemy experts: 1. Does anyone know if the Informix support is something will be added? (planned, considering). I don't know if this module is developed from IBM or SQLAlchemy so, I'm not even sure to whom address this question. 2. I see, I can connect to my Informix database, but can't use the ibm_db_sa module. Does this mean I can only use SQLAlchemy Core features and not the ORM? That would be sad since the ORM is exactly what I planned to use. Thanks for help, Massimo Valle -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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] SQLAlchemy and Informix advice
we've had a dedicated Informix dialect in the past, however I'm unable to maintain it because I don't have any way to test it, it was contributed by someone many years ago. The dialect was moved out to https://bitbucket.org/zzzeek/sqlalchemy-informixdb, and most of what's needed for it to work should be there. It might need some tweaks to get it going, e.g. with getting the setuptools entry point to be correct and all that, but overall it should have what's needed for rudimental access. Also I'm not sure why ibm_db_sa advertises informix support when this is not the case, can you elaborate on this? do they support only connectivity but not actually the right SQL format? On May 19, 2014, at 7:50 AM, Massimo Valle valle...@gmail.com wrote: I'm exploring SQLAlchemy features for a new project which must use an IBM Informix database. Found the ibm_db and ibm_db_sa modules to integrate with SQLAlchemy and successfully connected to the database after some tries. I found the ibm_db_sa module only supports DB2 and NOT Informix. At least this is what was answered me on the project page. Now I have a couple of newbie questions for the SQLAlchemy experts: 1. Does anyone know if the Informix support is something will be added? (planned, considering). I don't know if this module is developed from IBM or SQLAlchemy so, I'm not even sure to whom address this question. 2. I see, I can connect to my Informix database, but can't use the ibm_db_sa module. Does this mean I can only use SQLAlchemy Core features and not the ORM? That would be sad since the ORM is exactly what I planned to use. Thanks for help, Massimo Valle -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: SQLAlchemy and Informix advice
Thank you Richard, about the 2nd question, sorry but I'm new to SQLAlchemy and trying to find a way, so using the ORM for SQL access to Informix is my goal. Following the tutorial, I found for the ORM I have to create a Session and my guess is for doing this, I need the SQLAlchemy module support through the ibm_db_sa module. At this time it spits an error because it expects a DB2 database (no Informix support). Or, is there another way to do it? Unfortunately I have no time to contribute to the module for Infornix support, therefore I'm looking if there is a way to achieve my goal (that's using the ORM) as it's now. thanks again for your help, Massimo Valle On Monday, May 19, 2014 1:50:04 PM UTC+2, Massimo Valle wrote: I'm exploring SQLAlchemy features for a new project which must use an IBM Informix database. Found the ibm_db and ibm_db_sa modules to integrate with SQLAlchemy and successfully connected to the database after some tries. I found the ibm_db_sa module only supports DB2 and NOT Informix. At least this is what was answered me on the project page. Now I have a couple of newbie questions for the SQLAlchemy experts: 1. Does anyone know if the Informix support is something will be added? (planned, considering). I don't know if this module is developed from IBM or SQLAlchemy so, I'm not even sure to whom address this question. 2. I see, I can connect to my Informix database, but can't use the ibm_db_sa module. Does this mean I can only use SQLAlchemy Core features and not the ORM? That would be sad since the ORM is exactly what I planned to use. Thanks for help, Massimo Valle -- 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] Re: SQLAlchemy and Informix advice
Massimo, git it a try to the dialect that Mike passed you. I think it should give you a better start then using the ibm_db_sa module. On 05/19/2014 10:40 AM, Massimo Valle wrote: Thank you Richard, about the 2nd question, sorry but I'm new to SQLAlchemy and trying to find a way, so using the ORM for SQL access to Informix is my goal. Following the tutorial, I found for the ORM I have to create a Session and my guess is for doing this, I need the SQLAlchemy module support through the ibm_db_sa module. At this time it spits an error because it expects a DB2 database (no Informix support). Or, is there another way to do it? Unfortunately I have no time to contribute to the module for Infornix support, therefore I'm looking if there is a way to achieve my goal (that's using the ORM) as it's now. thanks again for your help, Massimo Valle On Monday, May 19, 2014 1:50:04 PM UTC+2, Massimo Valle wrote: I'm exploring SQLAlchemy features for a new project which must use an IBM Informix database. Found the ibm_db and ibm_db_sa modules to integrate with SQLAlchemy and successfully connected to the database after some tries. I found the ibm_db_sa module only supports DB2 and NOT Informix. At least this is what was answered me on the project page. Now I have a couple of newbie questions for the SQLAlchemy experts: 1. Does anyone know if the Informix support is something will be added? (planned, considering). I don't know if this module is developed from IBM or SQLAlchemy so, I'm not even sure to whom address this question. 2. I see, I can connect to my Informix database, but can't use the ibm_db_sa module. Does this mean I can only use SQLAlchemy Core features and not the ORM? That would be sad since the ORM is exactly what I planned to use. Thanks for help, Massimo Valle -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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] SQLAlchemy and Informix advice
My problem with InformixDB (I tried it) is it seems too weakly supported/used, while I need something more reliable, sorry. ibm_db seems more stable but the problem is with the ibm_db_sa adapter (SQLAlchemy module). My problem is when I try the IBM_DB SA adapter sanity test as from the project wiki (https://code.google.com/p/ibm-db/wiki/README). I always get this error: ibm_db_dbi.DatabaseError?https://code.google.com/p/ibm-db/w/edit/DatabaseError: ibm_db_dbi::DatabaseError?https://code.google.com/p/ibm-db/w/edit/DatabaseError: SQLNumResultCols failed: IBM? https://code.google.com/p/ibm-db/w/edit/IBM Driver? https://code.google.com/p/ibm-db/w/edit/CLI[IDS/UNIX32] The specified table (sysibm.sysdummy1) is not in the database. SQLCODE=-206 The same happens when creating a Session() in SQLAlchemy. It seems the ibm_db_sa adapter thinks to DB2 only and try to query the sysibm.sysdummy1 system table, which is not present in Informix. On the same wiki page I commented about this fact, and a project member (Rahul) (with an @ibm.com email address) answered: Informix is currently not supported for SQLALchemy's ibm_db_sa backend. Btw, since I'm new to SQLAlchemy, I don't understand the full implications of this, and that was my question about the ability to use the ORM or just the Core features. Thanks for help, Massimo On Monday, May 19, 2014 3:37:12 PM UTC+2, Michael Bayer wrote: we’ve had a dedicated Informix dialect in the past, however I’m unable to maintain it because I don’t have any way to test it, it was contributed by someone many years ago. The dialect was moved out to https://bitbucket.org/zzzeek/sqlalchemy-informixdb, and most of what’s needed for it to work should be there. It might need some tweaks to get it going, e.g. with getting the setuptools entry point to be correct and all that, but overall it should have what’s needed for rudimental access. Also I’m not sure why ibm_db_sa advertises informix support when this is not the case, can you elaborate on this? do they support only connectivity but not actually the right SQL format? On May 19, 2014, at 7:50 AM, Massimo Valle vall...@gmail.comjavascript: wrote: I'm exploring SQLAlchemy features for a new project which must use an IBM Informix database. Found the ibm_db and ibm_db_sa modules to integrate with SQLAlchemy and successfully connected to the database after some tries. I found the ibm_db_sa module only supports DB2 and NOT Informix. At least this is what was answered me on the project page. Now I have a couple of newbie questions for the SQLAlchemy experts: 1. Does anyone know if the Informix support is something will be added? (planned, considering). I don't know if this module is developed from IBM or SQLAlchemy so, I'm not even sure to whom address this question. 2. I see, I can connect to my Informix database, but can't use the ibm_db_sa module. Does this mean I can only use SQLAlchemy Core features and not the ORM? That would be sad since the ORM is exactly what I planned to use. Thanks for help, Massimo Valle -- 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. 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] SQLAlchemy and Informix advice
Informix is a commercial product that is not cheap to keep running so I'd assume you're working for a commercial entity with IBM support? Perhaps you can get support from Rahul at IBM to either improve sqlalchemy-informixdb (or they can just take it) or the ibm_db_sa product ? The improvement and modernization of the ibm_db_sa adapter in the first place was due to engagements with clients that IBM supports. I have a relationship with Rahul to the degree that he can throw me questions as he needs and I've submitted patches to the project as well. On May 19, 2014, at 10:51 AM, Massimo Valle valle...@gmail.com wrote: My problem with InformixDB (I tried it) is it seems too weakly supported/used, while I need something more reliable, sorry. ibm_db seems more stable but the problem is with the ibm_db_sa adapter (SQLAlchemy module). My problem is when I try the IBM_DB SA adapter sanity test as from the project wiki (https://code.google.com/p/ibm-db/wiki/README). I always get this error: ibm_db_dbi.DatabaseError?: ibm_db_dbi::DatabaseError?: SQLNumResultCols failed: IBM?Driver?[IDS/UNIX32] The specified table (sysibm.sysdummy1) is not in the database. SQLCODE=-206 The same happens when creating a Session() in SQLAlchemy. It seems the ibm_db_sa adapter thinks to DB2 only and try to query the sysibm.sysdummy1 system table, which is not present in Informix. On the same wiki page I commented about this fact, and a project member (Rahul) (with an @ibm.com email address) answered: Informix is currently not supported for SQLALchemy's ibm_db_sa backend. Btw, since I'm new to SQLAlchemy, I don't understand the full implications of this, and that was my question about the ability to use the ORM or just the Core features. Thanks for help, Massimo On Monday, May 19, 2014 3:37:12 PM UTC+2, Michael Bayer wrote: we've had a dedicated Informix dialect in the past, however I'm unable to maintain it because I don't have any way to test it, it was contributed by someone many years ago. The dialect was moved out to https://bitbucket.org/zzzeek/sqlalchemy-informixdb, and most of what's needed for it to work should be there. It might need some tweaks to get it going, e.g. with getting the setuptools entry point to be correct and all that, but overall it should have what's needed for rudimental access. Also I'm not sure why ibm_db_sa advertises informix support when this is not the case, can you elaborate on this? do they support only connectivity but not actually the right SQL format? On May 19, 2014, at 7:50 AM, Massimo Valle vall...@gmail.com wrote: I'm exploring SQLAlchemy features for a new project which must use an IBM Informix database. Found the ibm_db and ibm_db_sa modules to integrate with SQLAlchemy and successfully connected to the database after some tries. I found the ibm_db_sa module only supports DB2 and NOT Informix. At least this is what was answered me on the project page. Now I have a couple of newbie questions for the SQLAlchemy experts: 1. Does anyone know if the Informix support is something will be added? (planned, considering). I don't know if this module is developed from IBM or SQLAlchemy so, I'm not even sure to whom address this question. 2. I see, I can connect to my Informix database, but can't use the ibm_db_sa module. Does this mean I can only use SQLAlchemy Core features and not the ORM? That would be sad since the ORM is exactly what I planned to use. Thanks for help, Massimo Valle -- 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. To post to this group, send email to sqlal...@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. -- 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] deleting from a many-to-many association table
(Apologies if this is a dupe. I posted it twice via the GG webpage and it
never showed up.)
I've spent time unsuccessfully trying to fix some problems
with a many-to-many relationship and lazy joins.
Here's a simplified repro:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import random
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker, joinedload
engine = create_engine('sqlite:///appts.db', echo=True)
Base = declarative_base()
#LAZYJOIN = 'dynamic'
LAZYJOIN = 'joined'
class Appointment(Base):
__tablename__ = 'appointments'
id = Column(Integer, primary_key=True, autoincrement=True,
nullable=False)
subject = Column(String)
persons = relationship(
'AppointmentPerson',
cascade='delete, save-update, merge, expunge',
lazy=LAZYJOIN)
# rel1 = relationship('Foo')
# rel2 = relationship('Bar')
class Person(Base):
__tablename__ = 'persons'
id = Column(Integer, primary_key=True, autoincrement=True,
nullable=False)
name = Column(String)
class AppointmentPerson(Base):
augmented association table between Appointment and Person
__tablename__ = 'appointment_persons'
appointment_id = Column(Integer, ForeignKey(Appointment.id,
ondelete='CASCADE'), nullable=False, primary_key=True)
person_id = Column(Integer, ForeignKey(Person.id), nullable=False,
primary_key=True)
person = relationship(Person)
# Other columns omitted that are necessary for the real use case
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
def create_data(num_appts=20, num_people=20):
random.seed(12345)
session = Session()
persons = []
for i in range(num_people):
person = Person(name=Person_%03d % (i+1))
session.add(person)
persons.append(person)
session.flush()
for i in range(num_appts):
appt = Appointment(subject=Appt_%03d % (i+1))
session.add(appt)
session.flush()
random.shuffle(persons)
attendee_count = random.randrange(4) + 1
for k in range(1, attendee_count):
p = persons[k]
print i, k, p.name
ap = AppointmentPerson(appointment_id=appt.id, person_id=
p.id)
appt.persons.append(ap)
session.add(ap)
session.commit()
def query_all_appointments(session):
query = session.query(Appointment)
if LAZYJOIN == 'joined':
query = query.options(joinedload(Appointment.persons)
.joinedload(AppointmentPerson.person))
return query.all()
def show_data():
print show_data
session = Session()
appts = query_all_appointments(session)
print len(appts)
for a in appts:
people = , .join([ap.person.name for ap in a.persons])
x = {0}: {1}.format(a.subject, people)
def update_appointment_people(appt_id):
session = Session()
appt = session.query(Appointment).filter(Appointment.id ==
appt_id).one()
appt.persons.delete()
session.commit()
if __name__ == '__main__':
create_data()
show_data()
update_appointment_people(7)
The code originally used
Appointment.persons = relationship(
'AppointmentPerson',
cascade='delete, save-update, merge, expunge',
lazy='dynamic')
where everything worked, but accessing the persons on every appointment
triggered a separate query for AppointmentPersons, followed by
queries for each Person.
Changing the relationship to `lazy='joined'` and using joinedload
query = (session.query(Appointment).
options(joinedload(Appointment.persons).
joinedload(AppointmentPerson.person)))
return query.all()
reduces this to one SQL query:
SELECT appointments.id AS appointments_id,
appointments.subject AS appointments_subject,
persons_1.id AS persons_1_id,
persons_1.name AS persons_1_name,
appointment_persons_1.appointment_id AS
appointment_persons_1_appointment_id,
appointment_persons_1.person_id AS appointment_persons_1_person_id
FROM appointments
LEFT OUTER JOIN
appointment_persons AS appointment_persons_1
ON appointments.id = appointment_persons_1.appointment_id
LEFT OUTER JOIN persons AS persons_1
ON persons_1.id = appointment_persons_1.person_id
which is great.
Aside: In the original code, we're using MySQL, not SQLite.
On my MacBook, I have been unable to reproduce the pathological select
behavior
with MySQL 5.6, whereas it repros consistently on
Re: [sqlalchemy] deleting from a many-to-many association table
On May 19, 2014, at 12:07 PM, George Reilly george.v.rei...@gmail.com wrote: The code originally used Appointment.persons = relationship( 'AppointmentPerson', cascade='delete, save-update, merge, expunge', lazy='dynamic') where everything worked, but accessing the persons on every appointment triggered a separate query for AppointmentPersons, followed by queries for each Person. Changing the relationship to `lazy='joined'` and using joinedload query = (session.query(Appointment). options(joinedload(Appointment.persons). joinedload(AppointmentPerson.person))) return query.all() However, with `lazy='joined'` and using joinedload, I no longer know how to delete the AppointmentPersons associated with an Appointment. `appt.persons.delete()` used to work; now I get `AttributeError: 'InstrumentedList' object has no attribute 'delete'` the dynamic relationship strategy replaces the usual simple Python collection with a special Query object, so when you call delete() on this Query object you get a DELETE statement immediately. When you go to any strategy other than dynamic however the relationship now acts like a simple Python collection, in this case a list, which has no delete() method nor does it have any linkage to a live SQL query. You can empty out the collection using a standard python idiom like myobject.stuff[:] = [] - if you then configure delete-orphan cascade those objects will be deleted on the next flush. Alternatively, you can get that direct delete() statement if you say sess.query(AppointmentPerson).with_parent(some_appointment).delete(). -- 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] error reporting wishlist -- not sure if this is possible
occasionally, i run into this problem with Postgres; though it would likely happen on other systems too. my database might miss a migration, and we end up with this situation SqlAlchemy Model - Column = varchar(1000) PostgreSQL - Field = varchar(255) Data = 500 characters in length When I try to insert data into the field, the error I get just shows me an error on the table. It would be great if SqlAlchemy could identify which column caused the error too. Similar issues happen when putting the wrong type of data into a field. I don't know if the DBAPI makes any of this data available, or even if the database provides the driver with this sort of data. but if so, it would be really useful. -- 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] error reporting wishlist -- not sure if this is possible
a DBAPI error? we have no control over those. if it's an error that SQLA detects directly then it should be showing you all it knows. On May 19, 2014, at 4:42 PM, Jonathan Vanasco jonat...@findmeon.com wrote: occasionally, i run into this problem with Postgres; though it would likely happen on other systems too. my database might miss a migration, and we end up with this situation SqlAlchemy Model - Column = varchar(1000) PostgreSQL - Field = varchar(255) Data = 500 characters in length When I try to insert data into the field, the error I get just shows me an error on the table. It would be great if SqlAlchemy could identify which column caused the error too. Similar issues happen when putting the wrong type of data into a field. I don't know if the DBAPI makes any of this data available, or even if the database provides the driver with this sort of data. but if so, it would be really useful. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] error reporting wishlist -- not sure if this is possible
i think it is from the DBAPI. i was (sadly) expecting you to say that. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Adding where clause to existing SELECT without wrapping SELECT
I'm trying to modify a select statement which is fairly complex and which
is created in a function that I don't want to modify. It is returned from
this function and I'd like to add more WHERE clauses by just referencing
the returned select statement. How can I do this without causing SQLA
wrapping the returned select statement in another select?
See code below for demonstration. `sel` is the complex select statement (in
my application returned from the function) and by `sel2 = sel.where()` I
try to add another clause.
from sqlalchemy import *
metadata = MetaData()
product_table = Table('product', metadata, Column('id', Integer),
Column('name', String(32)))
sel = select(columns=[product_table.c.id.label('product_id'),
product_table.c.name.label('product_name'),
order_table.c.id.label('order_id'),
order_table.c.name.label('order_name')],
from_obj=product_table.join(order_table,
order_table.c.product_id==product_table.c.id))
# Fine
print sel
SELECT product.id AS product_id, product.name AS product_name, order.id
AS order_id, order.name AS order_name
FROM product JOIN order ON order.product_id = product.id
# Trying to add a where condition to sel
sel2 = sel.where(sel.c.product_name=='water')
# Which unfortunately wraps the select in another select. Any way of
adding the WHERE to `sel` post construction of `select()`?
print sel2
SELECT product.id AS product_id, product.name AS product_name, order.id
AS order_id, order.name AS order_name
FROM (SELECT product.id AS product_id, product.name AS product_name,
order.id AS order_id, order.name AS order_name
FROM product JOIN order ON order.product_id = product.id), product JOIN
order ON order.product_id = product.id
WHERE product_name = :product_name_1
# I would have expected:
SELECT product.id AS product_id, product.name AS product_name,
order.id AS order_id, order.name AS order_name FROM product JOIN
order ON order.product_id = product.id WHERE product_name =
:product_name_1
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Adding where clause to existing SELECT without wrapping SELECT
Is there some issue with doing: sel2 = sel.where(product_table.c.name
== water) ?
If you really have no reference to the product_table, I think you can
hack it with sel._raw_columns to try and find the column you want to
filter to.
-Ryan Kelly
On Mon, May 19, 2014 at 9:12 PM, gbr doubl...@directbox.com wrote:
I'm trying to modify a select statement which is fairly complex and which is
created in a function that I don't want to modify. It is returned from this
function and I'd like to add more WHERE clauses by just referencing the
returned select statement. How can I do this without causing SQLA wrapping
the returned select statement in another select?
See code below for demonstration. `sel` is the complex select statement (in
my application returned from the function) and by `sel2 = sel.where()` I try
to add another clause.
from sqlalchemy import *
metadata = MetaData()
product_table = Table('product', metadata, Column('id', Integer),
Column('name', String(32)))
sel = select(columns=[product_table.c.id.label('product_id'),
product_table.c.name.label('product_name'),
order_table.c.id.label('order_id'),
order_table.c.name.label('order_name')],
from_obj=product_table.join(order_table,
order_table.c.product_id==product_table.c.id))
# Fine
print sel
SELECT product.id AS product_id, product.name AS product_name, order.id AS
order_id, order.name AS order_name
FROM product JOIN order ON order.product_id = product.id
# Trying to add a where condition to sel
sel2 = sel.where(sel.c.product_name=='water')
# Which unfortunately wraps the select in another select. Any way of
adding the WHERE to `sel` post construction of `select()`?
print sel2
SELECT product.id AS product_id, product.name AS product_name, order.id AS
order_id, order.name AS order_name
FROM (SELECT product.id AS product_id, product.name AS product_name,
order.id AS order_id, order.name AS order_name
FROM product JOIN order ON order.product_id = product.id), product JOIN
order ON order.product_id = product.id
WHERE product_name = :product_name_1
# I would have expected:
SELECT product.id AS product_id, product.name AS product_name,
order.id AS order_id, order.name AS order_name FROM product JOIN
order
ON order.product_id = product.id WHERE product_name = :product_name_1
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
