[sqlalchemy] relation does not honor order_by attribute
hi there,
I am using a one to many relation, and would like the result to be ordered by a
field of the child table. however no order by statement is executed when I
access the related property of the parent object.
this is my declaration:
mitarbeiter_table = Table('mitarbeiter', Base2.metadata, autoload=True)
class mitarbeiterCL(Base2):
__table__ = mitarbeiter_table
abwesenheit_table = Table('abwesenheit', Base2.metadata, autoload=True)
class abwesenheitCL(Base2):
__table__ = abwesenheit_table
mitarbeiter = relation(
mitarbeiterCL,
uselist=False,
backref='abwesenheiten',
order_by = abwesenheit_table.c.datumvon,
)
when I then access the abwesenheiten property of a mitarbeiterCL object I get
the following sql statement executed.
2009-10-14 07:53:24,206 INFO sqlalchemy.engine.base.Engine.0x...194c SELECT
abwesenheit.awid AS abwesenheit_awid, abwesenheit.pid AS abwesenheit_pid,
abwesenheit.datumvon AS abwesenheit_datumvon, abwesenheit.datumbis AS
abwesenheit_datumbis, abwesenheit.grund AS abwesenheit_grund
FROM abwesenheit
WHERE %(param_1)s = abwesenheit.pid
what I would like is to get all abwesenheiten related to mitarbeiter ordered by
abwesenheiten.datumvon
what can I do to achieve this?
thanks for a great package
robert
--~--~-~--~~~---~--~~
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: relation does not honor order_by attribute
On Wed, Oct 14, 2009 at 08:34, robert rottermann rob...@redcor.ch wrote:
I am using a one to many relation, and would like the result to be ordered by
a
field of the child table. however no order by statement is executed when I
access the related property of the parent object.
this is my declaration:
mitarbeiter_table = Table('mitarbeiter', Base2.metadata, autoload=True)
class mitarbeiterCL(Base2):
__table__ = mitarbeiter_table
abwesenheit_table = Table('abwesenheit', Base2.metadata, autoload=True)
class abwesenheitCL(Base2):
__table__ = abwesenheit_table
mitarbeiter = relation(
mitarbeiterCL,
uselist=False,
backref='abwesenheiten',
order_by = abwesenheit_table.c.datumvon,
)
The problem is that you are specifying the order_by on the wrong side
of the relationship (ie on the ManyToOne side). You need to place the
order_by on the OneToMany side, and in your precise example, that
means, on the backref.
mitarbeiter = relation(
mitarbeiterCL,
uselist=False,
backref=backref('abwesenheiten', order_by=abwesenheit_table.c.datumvon)
)
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
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: relation does not honor order_by attribute
thanks
robert
Gaetan de Menten schrieb:
On Wed, Oct 14, 2009 at 08:34, robert rottermann rob...@redcor.ch wrote:
I am using a one to many relation, and would like the result to be ordered
by a
field of the child table. however no order by statement is executed when I
access the related property of the parent object.
this is my declaration:
mitarbeiter_table = Table('mitarbeiter', Base2.metadata, autoload=True)
class mitarbeiterCL(Base2):
__table__ = mitarbeiter_table
abwesenheit_table = Table('abwesenheit', Base2.metadata, autoload=True)
class abwesenheitCL(Base2):
__table__ = abwesenheit_table
mitarbeiter = relation(
mitarbeiterCL,
uselist=False,
backref='abwesenheiten',
order_by = abwesenheit_table.c.datumvon,
)
The problem is that you are specifying the order_by on the wrong side
of the relationship (ie on the ManyToOne side). You need to place the
order_by on the OneToMany side, and in your precise example, that
means, on the backref.
mitarbeiter = relation(
mitarbeiterCL,
uselist=False,
backref=backref('abwesenheiten',
order_by=abwesenheit_table.c.datumvon)
)
--~--~-~--~~~---~--~~
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: problem with like
Thx Kyle and Conor, i finally dit that s = session.query(Contact.IdContact, Contact.Civilite, Contact.Nom, Contact.Prenom, ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier) # s = s.filter(ContactTel.Tel.like(NumeroApparu)) s = s.filter(ContactTel.Tel.op(regexp)((.)*.join(list(NumeroApparu))) ) i construct a regular expression from the telephone number and do the regexp search sql 2009/10/14 Kyle Schaffrick k...@raidi.us On Mon, 12 Oct 2009 13:47:19 -0500 Conor conor.edward.da...@gmail.com wrote: Christian Démolis wrote: Hi, The idea of creating another column is good but it will multiplicate the size of my table by 2 for nothing. Is it possible to use MYSQL regular expression search with sql alcmehy? If yes, what is the command? MySQL supports RLIKE/REGEXP operators; you can see how to use them in SQLAlchemy in this thread: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7c6abe2ab9d5061 Be aware that regexp matching cannot utilize indexes, so you may have performance problems. If you want to get fancy, you can try creating an index on the expression REPLACE(REPLACE(tel_column, ' ', ''), '.', '') which should create an index on the normalized phone numbers. In theory, as long as your queries use the exact same function sequence as the index, the index will be scanned instead of the full table. I don't know what limitations MySQL has in this regard. Even if it does work, the only real advantage it provides over using another column is the normalized form is hidden in the index instead of the table. Hope it helps, -Conor Indeed this problem is easily solved with expression indexes (a.k.a. functional or calculated indexes). Unfortunately MySQL does not appear to support them. Their suggested workaround is to add a column to store the precomputed expression, with a trigger to keep it up to date, and index that column. This also means the optimization is not transparent: you have to explicitly use the precomputed column in your query. It seems expression indexes have been MySQL's todo list since at least 2007 :( You can of course still use RLIKE/REGEXP instead of adding this redundant column, but you'll get a full table scan every time. A classic space/time performance tradeoff :) -Kyle 2009/10/10 Andre Stechert stech...@gmail.com mailto:stech...@gmail.com This is not really a sqlalchemy question, but the quick answer is that you need to convert both your indexed data and your queries to the same normal form. In your example, you appear to be correctly stripping spaces and periods in your query. If you haven't done that in the database, then you should do it there, too. If you need to preserve the original formatting of the telephone number column, then create another column that contains the stripped phone numbers. You probably also want to put an index on that column. Lastly, a minor note on the sample code: you appear to be missing a % operator in your LIKE query. Cheers, Andre On Fri, Oct 9, 2009 at 5:46 AM, Christian Démolis christiandemo...@gmail.com mailto:christiandemo...@gmail.com wrote: Hi everybody, I m stuck with a query about telephone number : I want to find in my database all the contact who have a telephone number. The difficulty is that some number in the database can have space or . between numbers example : 06.06.50.44.11 or 45 87 12 45 65 This my query with like but it s not what i want because i ignore telephone number who have special chars NumeroApparu = 064544 s = session.query(Contact.IdContact, Contact.Civilite, Contact.Nom, Contact.Prenom, ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier).filter(ContactTel.Tel.like(NumeroApparu)) i saw class sqlalchemy.sql.expression.ColumnOperators¶ in the doc but lack of explication... --~--~-~--~~~---~--~~ 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] How to implement View
Hi All, I want to implemet View in my project. I am using sqlalchemy 0.4.4, turbogears1.0.4b3, python 2.5 Thanks Reetesh Nigam --~--~-~--~~~---~--~~ 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] Create Table errors on mysql...
Hi, I have a python module where I am implementing several classes. When I do a metadata.create_all(engine) every time Mysql trows an exception (1064, PROGRAMMING ERROR), but on a different table, I think SQLAlchemy is behaving different every time I run the program. ie (one of the definitions failing): class Journal(Base): __tablename__ = CalendarJournals Id = Column(Integer(), primary_key=True,quote=True) Attendees = relation(Attendee, cascade=all) Attachments = relation(Attachment, cascade=all) Catagories = relation(Catagorie, cascade=all) Comments= relation(Comment, cascade=all) Contacts= relation(Contact, cascade=all) ExDates = relation(ExDate, cascade=all) ExRules = relation(ExRule, cascade=all) RDates = relation(RDate, cascade=all) Related = relation(Relate, cascade=all) RRules = relation(RRule, cascade=all) RStatusses = relation(RStatus, cascade=all) XProps = relation(XProp, cascade=all) Class = Column(Unicode(20),quote=True) Created = Column(DateTime(),quote=True) Description = Column(UnicodeText(),quote=True) DTStamp = Column(DateTime(),quote=True) DtStart = Column(DateTime(),quote=True) LastModified= Column(DateTime(),quote=True) RecurId = Column(Unicode(),quote=True) Sequence= Column(Integer(),quote=True) Status = Column(Unicode(),quote=True) Summary = Column(Unicode(),quote=True) uid = Column(Unicode(),quote=True) url = Column(Unicode(),quote=True) ProgrammingError: (ProgrammingError) (1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' \n\t`Sequence` INTEGER, \n \t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR,' at line 9) '\nCREATE TABLE `CalendarJournals` (\n\t`Id` INTEGER NOT NULL AUTO_INCREMENT, \n\t`Class` VARCHAR(20), \n\t`Created` DATETIME, \n \t`Description` TEXT, \n\t`DTStamp` DATETIME, \n\t`DtStart` DATETIME, \n\t`LastModified` DATETIME, \n\t`RecurId` VARCHAR, \n\t`Sequence` INTEGER, \n\t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR, \n\t`url` VARCHAR, \n\tPRIMARY KEY (`Id`)\n)\n\n' () CREATE TABLE `CalendarJournals` ( `Id` INTEGER NOT NULL AUTO_INCREMENT, `Class` VARCHAR(20), `Created` DATETIME, `Description` TEXT, `DTStamp` DATETIME, `DtStart` DATETIME, `LastModified` DATETIME, `RecurId` VARCHAR, `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` VARCHAR, PRIMARY KEY (`Id`) ) The Error: ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` V' Since the syntax seems to be correct and Sequence is Quoted with BackQuotes (`). I am puzzled what to do to fix this. Please help, Martijn --~--~-~--~~~---~--~~ 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: Very odd utf8 problem
Hey, thanks. I tried to add the encoding parameter with the latin1 value, but it messed up everything and all of content was shown wrong. I decided to try to convert my whole DB into UTF-8, but I found out that I'm not sure how SA converts the gibberish in my DB into Hebrew. After a lot of trying different encodings, I built a program that will tell me what conversion is done to my Hebrew strings, so I can revert them back to Hebrew and then insert them as UTF-8. Apparently I need to use iconv to convert my sql dump file from utf8 to cp1252, and then I could just insert the sql file as a UTF-8 file. I'll try to convert everything in the next few days and will let you know. Anyhow, the program called Memir is released here - http://github.com/bjesus/memir . It's a PyGTK application that helps you test different encodings quickly, and trace conversions. Thank you, Yo'av. 2009/10/13 Michael Bayer mike...@zzzcomputing.com On Oct 12, 2009, at 7:22 PM, Yo'av Moshe wrote: Hey, Yes, I'm using a MySQL 5. I understand that the problem is probably happening because of some data I have in my DB, but it's seems odd to me since everything I have in this DB was created using SA. Can't it read the data it written? My mysql connection is specified with charset=latin1unicode=0. My website is shown right, and if I set it to charsrt=utf8 like the wiki says everything is garbled. The charset is because that is my mysql's tables' encoding. Maybe if I used utf8 when I created the tables it was working now, but it's too late and I just don't understand how come everything works except for this search query, and how come SA created data it cannot read, and why the hell it works the second time ... :( so if your MySQL DB is all in latin1, then you'd have to use that character set across the board, including the encoding parameter sent to create_engine() - it defaults to utf-8, which is why you see that in your error message. to dig deeper you'd have to really understand exactly what is present in your tables. This would involve pulling out the row as a raw string and just trying to decode it with different encodings to see what you have. I'm not sure that latin1 encoding can handle hebrew characters either (maybe it can, I've never used latin1 extensively), that's something you might want to research as well. Yo'av 2009/10/11 Michael Bayer mike...@zzzcomputing.com On Oct 11, 2009, at 2:29 PM, Yo'av Moshe wrote: No, the error is an UnicodeDecodeError (http://paste2.org/p/457059). I can't just try a different DB, switch to SQLite, etc. As I've said, my website is on production and I have a lot of users using it. the purpose of trying a different database is to narrow down the cause of the issue, not that you would switch the platform in use for production. One thing you should be aware of is that your program is failing due to the data coming back in your result set, not the data being bound to your SQL query. You likely have mis-encoded data present in your table which is matched by the criterion you're sending it. When the data is fetched, it cannot be decoded via utf-8. Also you havent as yet told us what database you're using , but I'm guessing MySQL, in which case you should ensure that you are using the correct client encoding as well as the correct encoding in your schema. These are MySQL settings, not SQLAlchemy. client encoding can be specified with create_engine() ( http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#MySQL) or within my.cnf. Also, the problem is something that started lately, probably because of some content that a user has uploaded, so a new DB will work for sure, even if it's the same kind. But, I need it to work with my DB, or a least understand what caused it so I can make sure it never happens again. I'll check my DBAPI, although I'm pretty sure it's that latest one that is shipped with CentOS5. Thank you, Yo'av 2009/10/10 Michael Bayer mike...@zzzcomputing.com On Oct 10, 2009, at 3:43 AM, Yo'av Moshe wrote: Any ideas? I still don't understand why the query is failing even when I'm using a unicode object. whats the error ? EOF in multi-line statement ? thats not a SQLAlchemy error message. what happens when you try SQLA 0.5.6 (perhaps there was some quirk regarding encoding that was fixed) ? a different / latest version of your DBAPI (perhaps your DBAPI is misunderstanding a character as a newline ) ? try SQLite with the same statement ? (what database are you using ?) Yo'av 2009/10/8 Yo'av Moshe bje...@gmail.com Thanks, I didn't know about that awful IPython bug... I checked, and apparently my website is already doing the SA query with a unicode object and not with a string one, so I think that it's not the u'' thing (it's true that I forgot it in my console testing, though). What you showed about IPython explains why it didn't give me any result when running in
[sqlalchemy] Re: Create Table errors on mysql...
Martijn Moeling wrote: Hi, I have a python module where I am implementing several classes. When I do a metadata.create_all(engine) every time Mysql trows an exception (1064, PROGRAMMING ERROR), but on a different table, I think SQLAlchemy is behaving different every time I run the program. ie (one of the definitions failing): class Journal(Base): __tablename__ = CalendarJournals Id = Column(Integer(), primary_key=True,quote=True) Attendees = relation(Attendee, cascade=all) Attachments = relation(Attachment, cascade=all) Catagories = relation(Catagorie, cascade=all) Comments= relation(Comment, cascade=all) Contacts= relation(Contact, cascade=all) ExDates = relation(ExDate, cascade=all) ExRules = relation(ExRule, cascade=all) RDates = relation(RDate, cascade=all) Related = relation(Relate, cascade=all) RRules = relation(RRule, cascade=all) RStatusses = relation(RStatus, cascade=all) XProps = relation(XProp, cascade=all) Class = Column(Unicode(20),quote=True) Created = Column(DateTime(),quote=True) Description = Column(UnicodeText(),quote=True) DTStamp = Column(DateTime(),quote=True) DtStart = Column(DateTime(),quote=True) LastModified= Column(DateTime(),quote=True) RecurId = Column(Unicode(),quote=True) Sequence= Column(Integer(),quote=True) Status = Column(Unicode(),quote=True) Summary = Column(Unicode(),quote=True) uid = Column(Unicode(),quote=True) url = Column(Unicode(),quote=True) ProgrammingError: (ProgrammingError) (1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' \n\t`Sequence` INTEGER, \n\t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR,' at line 9) '\nCREATE TABLE `CalendarJournals` (\n\t`Id` INTEGER NOT NULL AUTO_INCREMENT, \n\t`Class` VARCHAR(20), \n\t`Created` DATETIME, \n\t`Description` TEXT, \n\t`DTStamp` DATETIME, \n\t`DtStart` DATETIME, \n\t`LastModified` DATETIME, \n\t`RecurId` VARCHAR, \n\t`Sequence` INTEGER, \n\t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR, \n\t`url` VARCHAR, \n\tPRIMARY KEY (`Id`)\n)\n\n' () CREATE TABLE `CalendarJournals` ( `Id` INTEGER NOT NULL AUTO_INCREMENT, `Class` VARCHAR(20), `Created` DATETIME, `Description` TEXT, `DTStamp` DATETIME, `DtStart` DATETIME, `LastModified` DATETIME, `RecurId` VARCHAR, `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` VARCHAR, PRIMARY KEY (`Id`) ) The Error: ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` V' Since the syntax seems to be correct and Sequence is Quoted with BackQuotes (`). I am puzzled what to do to fix this. It is choking on your `RecurId` defintion, because MySQL requires all VARCHAR columns to have a length specifier. You need to replace your `Unicode()` column definitions with `Unicode(some_length)` or `UnicodeText()`. -Conor --~--~-~--~~~---~--~~ 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] What happens if a session is closed inside a subtransaction?
Hi All, I'm just wondering what happens if I were to call session.close() whilst inside a subtransaction? Would it indeed close the session and abort all of the parent transactions or would it do nothing? Looking at the code (and I haven't looked at it in any great detail, sorry) I imagine that it does indeed abort all parent transactions. If so, is there any way to tell whether the session is in a subtransaction state or not, so I could only call close() if it is the root? Sorry if this is a bit confusing or if I'm being horrendously ignorant :) —Oliver --~--~-~--~~~---~--~~ 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: sqlalchemy.sql.expression.func and composite types
David Gardner wrote: I have a PostgreSQL function that returns a composite type (a text field and 6 bigint columns). Currently I am calling it with: session.query(Job,func.farm.call_job_status(Job.path)).filter(Job.path=='testshow').first() Which returns a tuple, but the second element is a string. I could probably parse the string, but that wouldn't be very elegant. I was wondering is there an object that I can subclass to support this? I tried passing in type_=(String,Integer,...) as well as type_=composite(SomeObj) neither worked. unsure what this means. the text field + 6 int columns are returned as one big string ? if so, that would be a postgresql/psycopg2 behavior, so you'd have to parse the string (most cleanly using TypeDecorator). --~--~-~--~~~---~--~~ 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: Persistence of Python Objects with respect to the Session
mviamari wrote: Hello, I'm writing tests for my database, and I've run into a little bit of confusion. Say I have a class (we'll call it person) with two declared attributes that correspond to db columns: person.id person.name If I randomly assign a person object another attribute dynamically: person.undeclared_value = 'New' That value doesn't get stored in the database. (That part I expected and understand). What I don't understand is if I commit the object, set it to None (to presumably garbage collect it) and then reacquire it from the DB, sometimes that dynamically declared attribute is still pesent: person = Person() person.name = 'John Smith' person.new_attr = 'New' commit() id = person.id #id isn't assigned till after a commit person = None person = Person.get_by(id=id) #This is expected to be true assert person.name = 'John Smith' #This is expected to yield an AttributeError, but sometimes it doesn't assert person.new_attr = 'New' Based on my tests, it appears that the sometimes it doesn't is whenever there are relationships created in the session. If I only create one object and follow the above pattern, I get the expected results. If I create two objects and the relationship between them, then I get the unexpected results. Does anyone have any idea what might be going on? I think the object is getting stored in the session, and when I reobtain the object it just uses the reference in the session. this is the identity map at work. Read Is the session a cache? at http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-questions . --~--~-~--~~~---~--~~ 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: ORM Many to Many Across Two Databases
Luke Arno wrote:
I have a MSSQL server with two logical databases. (I inherited this
situation,
of course.) There is a table in each database and an association table in
one of them. What is the right way to configure this? Here is what I have
and
it complains about the values in foreign_keys. I've tried a lot of
permutations
and can't seem to hit on the right one. Thanks much!
left_engine = create_engine(SERVER_A_DB_ONE)
left_meta = MetaData()
left_meta.bind = left_engine
right_engine = create_engine(SERVER_A_DB_TWO)
right_meta = MetaData()
right_meta.bind = right_engine
left_table = Table('LeftTable', left_meta,
Column('id', Integer, primary_key=True),
Column('description', String(128)))
right_table = Table('RightTable', right_meta,
Column('id', Integer, primary_key=True),
Column('description', String(128)))
assoc_table = Table('LeftAssoc', left_meta,
Column('left_id', Integer),
Column('right_id', Integer))
MySession = sessionmaker(binds={
left_table: left_engine,
right_table: right_engine,
assoc_table: left_engine
})
class Left(object): pass
class Right(object): pass
mapper(Left, left_table)
mapper(Right, right_table, properties={
'lefts': relation(Left, secondary=assoc_table,
primaryjoin=right_table.c.id==assoc_table.c.right_id,
secondaryjoin=assoc_table.c.left_id==left_table.c.id,
foreign_keys=[right_table.c.id, left_table.c.id],
backref=rights),
})
the foreign keys here would be assoc_table.c.left_id and
assoc_table.c.right_id.
However I don't think relation() + secondary is going to work here. The
load of child objects will be against the target database only - there's
no behavior such that it will separately select rows from the association
table first, then the target table.
Your two options here are to map assoc_table explicitly, optionally using
association_proxy to have its usage be implicit, or alternately to build a
read-only accessor on your class which manually queries the association
table and then queries the target table.
Another possibility, if this were Oracle I'd suggest using dblink tables
- tables which are present in the local database but are remote to
another server. Since MS-SQL competes fiercely with Oracle I wouldn't be
surprised if MS-SQL supports such a concept as well.
- Luke
--~--~-~--~~~---~--~~
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: Create Table errors on mysql...
Thanks Guy's! Kinda stupid, but that happens with the use of examples. Martijn On Oct 14, 2009, at 3:34 PM, limodou wrote: On Wed, Oct 14, 2009 at 9:03 PM, Martijn Moeling mart...@xs4us.nu wrote: Hi, I have a python module where I am implementing several classes. When I do a metadata.create_all(engine) every time Mysql trows an exception (1064, PROGRAMMING ERROR), but on a different table, I think SQLAlchemy is behaving different every time I run the program. ie (one of the definitions failing): class Journal(Base): __tablename__ = CalendarJournals Id = Column(Integer(), primary_key=True,quote=True) Attendees = relation(Attendee, cascade=all) Attachments = relation(Attachment, cascade=all) Catagories = relation(Catagorie, cascade=all) Comments= relation(Comment, cascade=all) Contacts= relation(Contact, cascade=all) ExDates = relation(ExDate, cascade=all) ExRules = relation(ExRule, cascade=all) RDates = relation(RDate, cascade=all) Related = relation(Relate, cascade=all) RRules = relation(RRule, cascade=all) RStatusses = relation(RStatus, cascade=all) XProps = relation(XProp, cascade=all) Class = Column(Unicode(20),quote=True) Created = Column(DateTime(),quote=True) Description = Column(UnicodeText(),quote=True) DTStamp = Column(DateTime(),quote=True) DtStart = Column(DateTime(),quote=True) LastModified= Column(DateTime(),quote=True) RecurId = Column(Unicode(),quote=True) Sequence= Column(Integer(),quote=True) Status = Column(Unicode(),quote=True) Summary = Column(Unicode(),quote=True) uid = Column(Unicode(),quote=True) url = Column(Unicode(),quote=True) ProgrammingError: (ProgrammingError) (1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' \n\t`Sequence` INTEGER, \n\t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR,' at line 9) '\nCREATE TABLE `CalendarJournals` (\n\t`Id` INTEGER NOT NULL AUTO_INCREMENT, \n \t`Class` VARCHAR(20), \n\t`Created` DATETIME, \n\t`Description` TEXT, \n \t`DTStamp` DATETIME, \n\t`DtStart` DATETIME, \n\t`LastModified` DATETIME, \n \t`RecurId` VARCHAR, \n\t`Sequence` INTEGER, \n\t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR, \n\t`url` VARCHAR, \n\tPRIMARY KEY (`Id`)\n)\n\n' () CREATE TABLE `CalendarJournals` ( `Id` INTEGER NOT NULL AUTO_INCREMENT, `Class` VARCHAR(20), `Created` DATETIME, `Description` TEXT, `DTStamp` DATETIME, `DtStart` DATETIME, `LastModified` DATETIME, `RecurId` VARCHAR, `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` VARCHAR, PRIMARY KEY (`Id`) ) The Error: ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` V' Since the syntax seems to be correct and Sequence is Quoted with BackQuotes (`). I am puzzled what to do to fix this. Please help, Martijn I think VARCHAR need a length, but most of your table field has no length. -- I like python! UliPad The Python Editor: http://code.google.com/p/ulipad/ UliWeb simple web framework: http://uliwebproject.appspot.com My Blog: http://hi.baidu.com/limodou --~--~-~--~~~---~--~~ 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: What happens if a session is closed inside a subtransaction?
Oliver Beattie wrote: Hi All, I'm just wondering what happens if I were to call session.close() whilst inside a subtransaction? Would it indeed close the session and abort all of the parent transactions or would it do nothing? Looking at the code (and I haven't looked at it in any great detail, sorry) I imagine that it does indeed abort all parent transactions. If so, is there any way to tell whether the session is in a subtransaction state or not, so I could only call close() if it is the root? Sorry if this is a bit confusing or if I'm being horrendously ignorant :) close() removes all transactional markers present. The connection is returned to the pool and a rollback() occurs on it which will release any state left on the connection. If you have an application that is making explicit usage of subtransactions, that is session.begin(allow_subtransactions=True), that would imply a nesting of functionality within methods which each issue an explicit begin/commit pair (note that this is different from a nested transaction, which uses SAVEPOINT. Since you said subtransactions I'm going with that concept). In the first place, such a usage pattern is extremely rare, even though SQLA makes use of it internally - its a lot easier to construct an application where there is a single point of begin/commit for a particular session, instead of having that kind of boilerplate in multiple places. Secondly, if you are in fact using that sort of pattern, I wouldn't try to call close() within arbitrary points of the callstack. Ensuring that rollback() or commit() is called at the end of each block will ensure that transactional/connection state is released when the full nest of operations complete. Session has an is_active accessor which will indicate if a transaction is present. to tell if its a subtransation you'd need to say sess.transaction and sess.transaction.is_active and sess.transaction._parent. —Oliver --~--~-~--~~~---~--~~ 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: Modifying the scopefunc of an existing ScopedSession
On Tue, Oct 13, 2009 at 16:49, Michael Bayer mike...@zzzcomputing.com wrote: We have a situation where we have an existing ScopedSession, but want to change its scopefunc. This sounds like a strange requirement, it is because we use elixir - the issue is discusses here: http://groups.google.com/group/sqlelixir/browse_thread/thread/623f190c1784e5e9 How could we do this? To test, we currently we do: elixir.session.registry.scopefunc = lambda: 1 (The default is thread-local) But we seem to still end up with different sessions in different threads. you have to set that up ahead of time. by default, the registry evaluates as a _TLocalRegistry which is hardcoded to threadlocal. Session = scoped_session(sessionmaker(), scopefunc=lambda: 1) Since I was curious about the reason this didn't work, I looked more closely at that part of the code and I don't like that __new__ trick: it doesn't really help simplify the code and can be surprising. Attached patch suppress it. Btw: Iwan, did you try: factory = elixir.session.session_factory elixir.session.registry = sqlalchemy.util.ScopedRegistry(maker, scope_func=your_scope_func) Kinda ugly, but should work... (hopefully) -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- Index: orm/scoping.py === --- orm/scoping.py (revision 6393) +++ orm/scoping.py (working copy) @@ -5,7 +5,8 @@ # the MIT License: http://www.opensource.org/licenses/mit-license.php import sqlalchemy.exceptions as sa_exc -from sqlalchemy.util import ScopedRegistry, to_list, get_cls_kwargs, deprecated +from sqlalchemy.util import ScopedRegistry, ThreadLocalRegistry, \ +to_list, get_cls_kwargs, deprecated from sqlalchemy.orm import ( EXT_CONTINUE, MapperExtension, class_mapper, object_session ) @@ -29,7 +30,10 @@ def __init__(self, session_factory, scopefunc=None): self.session_factory = session_factory -self.registry = ScopedRegistry(session_factory, scopefunc) +if scopefunc: +self.registry = ScopedRegistry(session_factory, scopefunc) +else: +self.registry = ThreadLocalRegistry(session_factory) self.extension = _ScopedExt(self) def __call__(self, **kwargs): Index: util.py === --- util.py (revision 6393) +++ util.py (working copy) @@ -1163,14 +1163,7 @@ scopefunc a callable that will return a key to store/retrieve an object. - If None, ScopedRegistry uses a threading.local object instead. - -def __new__(cls, createfunc, scopefunc=None): -if not scopefunc: -return object.__new__(_TLocalRegistry) -else: -return object.__new__(cls) def __init__(self, createfunc, scopefunc): self.createfunc = createfunc @@ -1196,8 +1189,8 @@ except KeyError: pass -class _TLocalRegistry(ScopedRegistry): -def __init__(self, createfunc, scopefunc=None): +class ThreadLocalRegistry(ScopedRegistry): +def __init__(self, createfunc): self.createfunc = createfunc self.registry = threading.local()
[sqlalchemy] Re: Modifying the scopefunc of an existing ScopedSession
On Wed, Oct 14, 2009 at 17:09, Gaetan de Menten gdemen...@gmail.com wrote: Btw: Iwan, did you try: factory = elixir.session.session_factory elixir.session.registry = sqlalchemy.util.ScopedRegistry(maker, scope_func=your_scope_func) Of course, that should read : factory = elixir.session.session_factory elixir.session.registry = sqlalchemy.util.ScopedRegistry(factory, scope_func=your_scope_func) -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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] Can I use MapperExtension.before_insert to prevent an object from being INSERTed on commit()?
I was under the impression that returning EXT_STOP in my MapperExtension.before_insert() can prevent an object from being inserted into the database altogether, but that doesn't seem to be working, so I'm not sure if I'm misunderstanding the operation of MapperExtensions or it's a bug. I'd like to have objects with (non-mapped) attribute temporary set to True not be written to the database, because they haven't been fully initialized yet (e.g. some of their fields would violate integrity constraints). And before you say simply don't add them to the session until they have been initialized - they get added automatically because of backrefs. I can attach a test case if further clarification is needed. --~--~-~--~~~---~--~~ 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: ORM Many to Many Across Two Databases
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Luke Arno Sent: 14 October 2009 16:41 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: ORM Many to Many Across Two Databases It looks like if I put the relation on the obj mapped to the DB where the association table is _not_, it works in one direction. (So, in the example Right.lefts can work but Left.rights cannot.) When trying to use Left.rights, it looks for the table in the wrong database. It appears that it would be fine if I could just get the table names all qualified with database name in the issued SQL. Is there a way to make that happen, by any chance? You can do this by using a single engine and metadata, and passing a 'schema' parameter when defining your tables: http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na me Hope that helps, Simon --~--~-~--~~~---~--~~ 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: sqlalchemy.sql.expression.func and composite types
I have a composite type that I defined as:
CREATE TYPE farm.job_status_ret AS
(total bigint,
valid bigint,
invalid bigint,
processing bigint,
pending bigint,
canceled bigint);
I dropped the text field. When I run the query in postgres I get the six
distinct fields:
hdpsdb=# SELECT * FROM farm.call_job_status('testshow');
total | valid | invalid | processing | pending | canceled
-+---+-++-+--
9892718 | 116 | 20 | 0 | 9886233 | 6349
but from SQLAlchemy I just get a string:
session.query(func.farm.call_job_status('testshow')).first()
('(9892718,116,20,0,9886233,6349)',)
Looks like the TypeDecorator will do what I need.
Michael Bayer wrote:
David Gardner wrote:
I have a PostgreSQL function that returns a composite type (a text field
and 6 bigint columns).
Currently I am calling it with:
session.query(Job,func.farm.call_job_status(Job.path)).filter(Job.path=='testshow').first()
Which returns a tuple, but the second element is a string. I could
probably parse the string, but that wouldn't be very elegant.
I was wondering is there an object that I can subclass to support this?
I tried passing in type_=(String,Integer,...) as well as
type_=composite(SomeObj) neither worked.
unsure what this means. the text field + 6 int columns are returned as
one big string ? if so, that would be a postgresql/psycopg2 behavior, so
you'd have to parse the string (most cleanly using TypeDecorator).
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
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: Can I use MapperExtension.before_insert to prevent an object from being INSERTed on commit()?
bojanb wrote:
I was under the impression that returning EXT_STOP in my
MapperExtension.before_insert() can prevent an object from being
inserted into the database altogether, but that doesn't seem to be
working, so I'm not sure if I'm misunderstanding the operation of
MapperExtensions or it's a bug.
it does not.
http://www.sqlalchemy.org/docs/05/reference/orm/interfaces.html?highlight=mapperextension#sqlalchemy.orm.interfaces.MapperExtension
Returning EXT_STOP will halt processing of further extensions handling
that method.
that only refers to additional extensions.
I'd like to have objects with (non-mapped) attribute temporary set
to True not be written to the database, because they haven't been
fully initialized yet (e.g. some of their fields would violate
integrity constraints).
And before you say simply don't add them to the session until they
have been initialized - they get added automatically because of
backrefs.
turn off cascade on those backrefs:
'foo':relation(Bar, backref=backref('foos', cascade=None))
--~--~-~--~~~---~--~~
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: sqlalchemy.sql.expression.func and composite types
David Gardner wrote:
I have a composite type that I defined as:
CREATE TYPE farm.job_status_ret AS
(total bigint,
valid bigint,
invalid bigint,
processing bigint,
pending bigint,
canceled bigint);
I dropped the text field. When I run the query in postgres I get the six
distinct fields:
hdpsdb=# SELECT * FROM farm.call_job_status('testshow');
total | valid | invalid | processing | pending | canceled
-+---+-++-+--
9892718 | 116 | 20 | 0 | 9886233 | 6349
but from SQLAlchemy I just get a string:
session.query(func.farm.call_job_status('testshow')).first()
('(9892718,116,20,0,9886233,6349)',)
Looks like the TypeDecorator will do what I need.
it would be interesting to nail down exactly what psycopg2's contract is
here. strange that it does that.
Michael Bayer wrote:
David Gardner wrote:
I have a PostgreSQL function that returns a composite type (a text
field
and 6 bigint columns).
Currently I am calling it with:
session.query(Job,func.farm.call_job_status(Job.path)).filter(Job.path=='testshow').first()
Which returns a tuple, but the second element is a string. I could
probably parse the string, but that wouldn't be very elegant.
I was wondering is there an object that I can subclass to support this?
I tried passing in type_=(String,Integer,...) as well as
type_=composite(SomeObj) neither worked.
unsure what this means. the text field + 6 int columns are returned as
one big string ? if so, that would be a postgresql/psycopg2 behavior,
so
you'd have to parse the string (most cleanly using TypeDecorator).
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
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: ORM Many to Many Across Two Databases
Thanks, Simon. Good suggestion, but these are databases and not schema within a database. If I use the schema=MyDatabase. ( notice the .) and quote_schema=False, the table names come out like I want, but look at what happens to the label here: SELECT [LeftTable_1].description AS [MyDatabase._LeftTab_1] FROM MyDatabase..[LeftTable] AS [LeftTable_1] WHERE [LeftTable_1].id = %(param_1)s That is really abusing the schema feature, so this is not a bug... I just wish there were a databasename=Foo option for Table() so I could use these ugly databases the way they are. :( On Wed, Oct 14, 2009 at 11:51 AM, King Simon-NFHD78 simon.k...@motorola.com wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Luke Arno Sent: 14 October 2009 16:41 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: ORM Many to Many Across Two Databases It looks like if I put the relation on the obj mapped to the DB where the association table is _not_, it works in one direction. (So, in the example Right.lefts can work but Left.rights cannot.) When trying to use Left.rights, it looks for the table in the wrong database. It appears that it would be fine if I could just get the table names all qualified with database name in the issued SQL. Is there a way to make that happen, by any chance? You can do this by using a single engine and metadata, and passing a 'schema' parameter when defining your tables: http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na me Hope that helps, Simon --~--~-~--~~~---~--~~ 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: sqlalchemy.sql.expression.func and composite types
Did a quick test using psycopg2 and it returns a tuple of six longs:
(9892718L, 1046L, 189L, 235L, 9890143L, 1105L)
---
import psycopg2
import psycopg2.extensions
DB_HOST = 'localhost'
DB_NAME = 'hdpsdb'
DB_USER = 'testuser'
DB_PASS = 'testuser'
db_uri = dbname='%s' user='%s' host='%s' password='%s' %
(DB_NAME,DB_USER,DB_HOST,DB_PASS)
pg2con = psycopg2.connect(db_uri)
cursor=pg2con.cursor()
cursor.execute(SELECT * FROM farm.call_job_status('testshow');)
row = cursor.fetchone()
print row
cursor.close()
pg2con.close()
Michael Bayer wrote:
David Gardner wrote:
I have a composite type that I defined as:
CREATE TYPE farm.job_status_ret AS
(total bigint,
valid bigint,
invalid bigint,
processing bigint,
pending bigint,
canceled bigint);
I dropped the text field. When I run the query in postgres I get the six
distinct fields:
hdpsdb=# SELECT * FROM farm.call_job_status('testshow');
total | valid | invalid | processing | pending | canceled
-+---+-++-+--
9892718 | 116 | 20 | 0 | 9886233 | 6349
but from SQLAlchemy I just get a string:
session.query(func.farm.call_job_status('testshow')).first()
('(9892718,116,20,0,9886233,6349)',)
Looks like the TypeDecorator will do what I need.
it would be interesting to nail down exactly what psycopg2's contract is
here. strange that it does that.
Michael Bayer wrote:
David Gardner wrote:
I have a PostgreSQL function that returns a composite type (a text
field
and 6 bigint columns).
Currently I am calling it with:
session.query(Job,func.farm.call_job_status(Job.path)).filter(Job.path=='testshow').first()
Which returns a tuple, but the second element is a string. I could
probably parse the string, but that wouldn't be very elegant.
I was wondering is there an object that I can subclass to support this?
I tried passing in type_=(String,Integer,...) as well as
type_=composite(SomeObj) neither worked.
unsure what this means. the text field + 6 int columns are returned as
one big string ? if so, that would be a postgresql/psycopg2 behavior,
so
you'd have to parse the string (most cleanly using TypeDecorator).
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
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: ORM Many to Many Across Two Databases
So, is there any chance that relations across multiple _logical_ databases on a _single_ physical server will be supported by SQLAlchemy in the future? As I mentioned before, this could work (at least for MSSQL) if we only had a databasename=foo on Table(). I am not really sure how much work it would be, but it _sounds_ easy. :) Thanks. - Luke On Wed, Oct 14, 2009 at 3:01 PM, Luke Arno luke.a...@gmail.com wrote: Thanks, Simon. Good suggestion, but these are databases and not schema within a database. If I use the schema=MyDatabase. ( notice the .) and quote_schema=False, the table names come out like I want, but look at what happens to the label here: SELECT [LeftTable_1].description AS [MyDatabase._LeftTab_1] FROM MyDatabase..[LeftTable] AS [LeftTable_1] WHERE [LeftTable_1].id = %(param_1)s That is really abusing the schema feature, so this is not a bug... I just wish there were a databasename=Foo option for Table() so I could use these ugly databases the way they are. :( On Wed, Oct 14, 2009 at 11:51 AM, King Simon-NFHD78 simon.k...@motorola.com wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Luke Arno Sent: 14 October 2009 16:41 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: ORM Many to Many Across Two Databases It looks like if I put the relation on the obj mapped to the DB where the association table is _not_, it works in one direction. (So, in the example Right.lefts can work but Left.rights cannot.) When trying to use Left.rights, it looks for the table in the wrong database. It appears that it would be fine if I could just get the table names all qualified with database name in the issued SQL. Is there a way to make that happen, by any chance? You can do this by using a single engine and metadata, and passing a 'schema' parameter when defining your tables: http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na me Hope that helps, Simon --~--~-~--~~~---~--~~ 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: Can I use MapperExtension.before_insert to prevent an object from being INSERTed on commit()?
it does not.
http://www.sqlalchemy.org/docs/05/reference/orm/interfaces.html?highl...
Returning EXT_STOP will halt processing of further extensions handling
that method.
that only refers to additional extensions.
Right. The doc was a little ambigous - the or use the default
functionality if there are no other extensions for EXT_CONTINUE kinda
led me to believe that the default functionality (which is to insert
the object in the database I guess) is just treated as the last
extension.
turn off cascade on those backrefs:
'foo':relation(Bar, backref=backref('foos', cascade=None))
I figured out that session.expunge(new_object) before doing a commit()
does what I need - keep the incomplete object from being commited
while saving other new objects. I kept trying to do session.new.remove
(new_object) but of course that didn't work, which led me to play with
the more exotic solutions...
--~--~-~--~~~---~--~~
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: ORM Many to Many Across Two Databases
Luke Arno wrote: So, is there any chance that relations across multiple _logical_ databases on a _single_ physical server will be supported by SQLAlchemy in the future? that is supported now, via the schema argument to Table, assuming you are connecting with an account that has access to all logical databases. relation() supports spanning physical databases as well, if you either ensure the secondary table is on the same server as the target, or alternatively map the association table explicitly as I said in my initial response. Thanks. - Luke On Wed, Oct 14, 2009 at 3:01 PM, Luke Arno luke.a...@gmail.com wrote: Thanks, Simon. Good suggestion, but these are databases and not schema within a database. If I use the schema=MyDatabase. ( notice the .) and quote_schema=False, the table names come out like I want, but look at what happens to the label here: SELECT [LeftTable_1].description AS [MyDatabase._LeftTab_1] FROM MyDatabase..[LeftTable] AS [LeftTable_1] WHERE [LeftTable_1].id = %(param_1)s That is really abusing the schema feature, so this is not a bug... I just wish there were a databasename=Foo option for Table() so I could use these ugly databases the way they are. :( On Wed, Oct 14, 2009 at 11:51 AM, King Simon-NFHD78 simon.k...@motorola.com wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Luke Arno Sent: 14 October 2009 16:41 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: ORM Many to Many Across Two Databases It looks like if I put the relation on the obj mapped to the DB where the association table is _not_, it works in one direction. (So, in the example Right.lefts can work but Left.rights cannot.) When trying to use Left.rights, it looks for the table in the wrong database. It appears that it would be fine if I could just get the table names all qualified with database name in the issued SQL. Is there a way to make that happen, by any chance? You can do this by using a single engine and metadata, and passing a 'schema' parameter when defining your tables: http://www.sqlalchemy.org/docs/05/metadata.html#specifying-the-schema-na me Hope that helps, Simon --~--~-~--~~~---~--~~ 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: Persistence of Python Objects with respect to the Session
So if I read the documentation right, it appears that because I'm accessing the object via the primary key, it looks the object up locally (i.e. in the session) and returns that object. Other than doing a query/lookup on another attribute, how can I force it to create the object from the data in the database? The key here is that I'd like to be able to verify if an attribute (that corresponds to a DB column) has been deleted. To complicate matters, I'd also like to be able to do this in a nested transaction, without destroying the session (so I can rollback everything when I'm done). Thanks, Mike On Oct 14, 7:00 am, Michael Bayer mike...@zzzcomputing.com wrote: mviamari wrote: Hello, I'm writing tests for my database, and I've run into a little bit of confusion. Say I have a class (we'll call it person) with two declared attributes that correspond to db columns: person.id person.name If I randomly assign a person object another attribute dynamically: person.undeclared_value = 'New' That value doesn't get stored in the database. (That part I expected and understand). What I don't understand is if I commit the object, set it to None (to presumably garbage collect it) and then reacquire it from the DB, sometimes that dynamically declared attribute is still pesent: person = Person() person.name = 'John Smith' person.new_attr = 'New' commit() id = person.id #id isn't assigned till after a commit person = None person = Person.get_by(id=id) #This is expected to be true assert person.name = 'John Smith' #This is expected to yield an AttributeError, but sometimes it doesn't assert person.new_attr = 'New' Based on my tests, it appears that the sometimes it doesn't is whenever there are relationships created in the session. If I only create one object and follow the above pattern, I get the expected results. If I create two objects and the relationship between them, then I get the unexpected results. Does anyone have any idea what might be going on? I think the object is getting stored in the session, and when I reobtain the object it just uses the reference in the session. this is the identity map at work. Read Is the session a cache? athttp://www.sqlalchemy.org/docs/05/session.html#frequently-asked-quest... . --~--~-~--~~~---~--~~ 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] MySQL Stored Procedures and result sets
Hi I have a stored procedure on a mysql server i'm calling. However, all i can seem to get back are the arguments passed to the procedure if i do it using MySQLdb i just use the cursor .nextset() to get the actual results of the procedure call Is there a way I can advance the record set to the actual returned rows using SQLAlchemy? I hope I'm not missing something obvious, I have searched the docs and googled everything I could think of. Thanks --~--~-~--~~~---~--~~ 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: MySQL Stored Procedures and result sets
On Oct 14, 2009, at 8:40 PM, BEES INC wrote: Hi I have a stored procedure on a mysql server i'm calling. However, all i can seem to get back are the arguments passed to the procedure if i do it using MySQLdb i just use the cursor .nextset() to get the actual results of the procedure call Is there a way I can advance the record set to the actual returned rows using SQLAlchemy? I hope I'm not missing something obvious, I have searched the docs and googled everything I could think of. you'd have to stick to raw cursor access for that as cursor.nextset() is not part of SQLA's public API.If it were me though I'd modify the procedure to not require multiple result sets. --~--~-~--~~~---~--~~ 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: Persistence of Python Objects with respect to the Session
Michael, First, Thanks for your help. Second, something still isn't right. Here's the code in question: obj = Obj() org = Org() self.assertNotEqual(obj, None) self.assertNotEqual(org, None) #Nonsense_var is not representative of a table column, #and is dynamically declared (i.e. not defined in the class definition). obj.nonsense_var = 12 org.nonsense_var = 11 self.assertEqual(obj.nonsense_var, 12) self.assertEqual(org.nonsense_var, 11) org.obj = obj util.database.commit_session() objid = obj.id orgid = org.id util.database.get_session().expire(obj) util.database.get_session().expire(org) obj = None org = None self.assertEqual(obj, None) self.assertEqual(org, None) obj = Obj.get_by(id=objid) org = Org.get_by(id=orgid) self.assertNotEqual(obj, None) self.assertNotEqual(org, None) self.assertRaises(AttributeError, getattr, obj, 'nonsense_var') self.assertRaises(AttributeError, getattr, org, 'nonsense_var') I don't get the expected AttributeError at the end. Interestingly, if I delete the org.obj = obj declaration (which is a one-to-one relationship) it runs as expected. The whole point of this is to be able to determine if a given attribute is part of the object (and corresponding table) definition. This would be useful, for example, to detect renamed attributes, deleted attributes, or data transformations when committed to the database (i.e. 10/11/2009 becomes datatime(2009, 10, 11)). Thanks Mike On Oct 14, 6:44 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 14, 2009, at 9:15 PM, mviamari wrote: So if I read the documentation right, it appears that because I'm accessing the object via the primary key, it looks the object up locally (i.e. in the session) and returns that object. Other than doing a query/lookup on another attribute, how can I force it to create the object from the data in the database? The key here is that I'd like to be able to verify if an attribute (that corresponds to a DB column) has been deleted. To complicate matters, I'd also like to be able to do this in a nested transaction, without destroying the session (so I can rollback everything when I'm done). the Session/Query is quite flexible and individual objects can be refreshed, expired, etc. as needed. The most direct route is to expire() the object in question. Alternatively use the populate_existing() method on query which forces a reload. However, if all of this is occuring within a transaction that is isolated from others, none of that should be needed - the Session in its default settings always synchronizes its state against the current transaction automatically, and the object in your identity map does represent what's in that transaction - unless you've issued an UPDATE or DELETE statement using a plain SQL expression (in which case I'd recommend checking out query.update() and query.delete()). After a rollback() or commit() when the transaction goes away, all the contents of the session are expired so that the new state is loaded into the next transaction. Thanks, Mike On Oct 14, 7:00 am, Michael Bayer mike...@zzzcomputing.com wrote: mviamari wrote: Hello, I'm writing tests for my database, and I've run into a little bit of confusion. Say I have a class (we'll call it person) with two declared attributes that correspond to db columns: person.id person.name If I randomly assign a person object another attribute dynamically: person.undeclared_value = 'New' That value doesn't get stored in the database. (That part I expected and understand). What I don't understand is if I commit the object, set it to None (to presumably garbage collect it) and then reacquire it from the DB, sometimes that dynamically declared attribute is still pesent: person = Person() person.name = 'John Smith' person.new_attr = 'New' commit() id = person.id #id isn't assigned till after a commit person = None person = Person.get_by(id=id) #This is expected to be true assert person.name = 'John Smith' #This is expected to yield an AttributeError, but sometimes it doesn't assert person.new_attr = 'New' Based on my tests, it appears that the sometimes it doesn't is whenever there are relationships created in the session. If I only create one object and follow the above pattern, I get the expected results. If I create two objects and the relationship between them, then I get the unexpected results. Does anyone have any idea what might be going on? I think the object is getting stored in the session, and when I reobtain the object it just uses the reference in the session. this is the identity map at work. Read Is the session a cache?
[sqlalchemy] Re: Persistence of Python Objects with respect to the Session
UPDATE: If I inject a session.expunge_all() before I query the DB for the objects again, the undeclared attribute is no longer present (i.e. I get the AttributeError I expect). On Oct 14, 7:08 pm, mviamari mviam...@gmail.com wrote: Michael, First, Thanks for your help. Second, something still isn't right. Here's the code in question: obj = Obj() org = Org() self.assertNotEqual(obj, None) self.assertNotEqual(org, None) #Nonsense_var is not representative of a table column, #and is dynamically declared (i.e. not defined in the class definition). obj.nonsense_var = 12 org.nonsense_var = 11 self.assertEqual(obj.nonsense_var, 12) self.assertEqual(org.nonsense_var, 11) org.obj = obj util.database.commit_session() objid = obj.id orgid = org.id util.database.get_session().expire(obj) util.database.get_session().expire(org) obj = None org = None self.assertEqual(obj, None) self.assertEqual(org, None) obj = Obj.get_by(id=objid) org = Org.get_by(id=orgid) self.assertNotEqual(obj, None) self.assertNotEqual(org, None) self.assertRaises(AttributeError, getattr, obj, 'nonsense_var') self.assertRaises(AttributeError, getattr, org, 'nonsense_var') I don't get the expected AttributeError at the end. Interestingly, if I delete the org.obj = obj declaration (which is a one-to-one relationship) it runs as expected. The whole point of this is to be able to determine if a given attribute is part of the object (and corresponding table) definition. This would be useful, for example, to detect renamed attributes, deleted attributes, or data transformations when committed to the database (i.e. 10/11/2009 becomes datatime(2009, 10, 11)). Thanks Mike On Oct 14, 6:44 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 14, 2009, at 9:15 PM, mviamari wrote: So if I read the documentation right, it appears that because I'm accessing the object via the primary key, it looks the object up locally (i.e. in the session) and returns that object. Other than doing a query/lookup on another attribute, how can I force it to create the object from the data in the database? The key here is that I'd like to be able to verify if an attribute (that corresponds to a DB column) has been deleted. To complicate matters, I'd also like to be able to do this in a nested transaction, without destroying the session (so I can rollback everything when I'm done). the Session/Query is quite flexible and individual objects can be refreshed, expired, etc. as needed. The most direct route is to expire() the object in question. Alternatively use the populate_existing() method on query which forces a reload. However, if all of this is occuring within a transaction that is isolated from others, none of that should be needed - the Session in its default settings always synchronizes its state against the current transaction automatically, and the object in your identity map does represent what's in that transaction - unless you've issued an UPDATE or DELETE statement using a plain SQL expression (in which case I'd recommend checking out query.update() and query.delete()). After a rollback() or commit() when the transaction goes away, all the contents of the session are expired so that the new state is loaded into the next transaction. Thanks, Mike On Oct 14, 7:00 am, Michael Bayer mike...@zzzcomputing.com wrote: mviamari wrote: Hello, I'm writing tests for my database, and I've run into a little bit of confusion. Say I have a class (we'll call it person) with two declared attributes that correspond to db columns: person.id person.name If I randomly assign a person object another attribute dynamically: person.undeclared_value = 'New' That value doesn't get stored in the database. (That part I expected and understand). What I don't understand is if I commit the object, set it to None (to presumably garbage collect it) and then reacquire it from the DB, sometimes that dynamically declared attribute is still pesent: person = Person() person.name = 'John Smith' person.new_attr = 'New' commit() id = person.id #id isn't assigned till after a commit person = None person = Person.get_by(id=id) #This is expected to be true assert person.name = 'John Smith' #This is expected to yield an AttributeError, but sometimes it doesn't assert person.new_attr = 'New' Based on my tests, it appears that the sometimes it doesn't is whenever there are relationships created in the session. If I only create one object and follow the above pattern, I get the expected results. If I create two objects
[sqlalchemy] a special relation
I have a special kind of relation.
Relation = Table(relations, metadata,
Column('src_id',Unicode(50)),
Column('dest_id',Unicode(50)),
Column('relation',Unicode(50))
)
class Node(DeclarativeBase):
__tablename__ = 'nodes'
name = Column(Unicode(50), nullable=False)
node_id = Column(Unicode(50), primary_key=True)
children=relation('Node',secondary=Relation,\
primaryjoin=and_
(node_id==Relation.c.src_id,Relation.c.relation==u'Children'),\
foreign_keys=[Relation.c.src_id,Relation.c.dest_id],\
secondaryjoin=and_(Relation.c.dest_id==node_id),\
backref=backref('parent'))
this relation can be anything...children,friend,sibling
the query executed when i say node.children seems to be correct.
but my problem is when i add an object to this collection
only src_id and dest_id is populated.
i.e if i say node.children.append(xx) , i expect the relations table
to get
populated as
src_id=node.id ,
dest_id=xx.id
relation=u'Children'
but the relation column has value None.
how can i make SA to populate the relation column also?
PS:I checked Association Object, but couldn't figure out a way.
thnx for any 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
-~--~~~~--~~--~--~---
