http://blog.gmane.org/gmane.comp.python.sqlalchemy.user hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/15055 Hi all. I'm currently working on a map (like in geography :) ) When a new tile in inserted in the DB, I'm using an extension mapper to update some neighbor's properties (like the neighbors count). The after_insert method helps a lot... but: When I modify another object than the one being inserted in the after_insert method, the modification happens in the python object, but doesn't occur is the DB. The commit at the end does not seem to have an effect. What should I do? Thanks Guillaume --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- GustaV 2008-08-28T22:57:09 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/15035 Hi, is there a way to query on running averages in sqlalchemy? Eg for a baseball database how could I query on "a team scored 2 more runs then their year-to-date average"? Thanks for any clues. joe --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- dirjoe 2008-08-28T04:51:15 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/15034 I am using the ORM query strategy using session.query. I vaguely remember seeing in the past that doing a slice on the result set, i.e. result[:10] used to perform a Limit/Offset query, it may not have been so. Nevertheless, with SA 0.5beta3 this is definitely not the case. It seems that limit offset queries are only issued when I index the result set, e.g. result[0] which does a limit 1 offset 0. The question is, how would I be able to extend the slicing to use Limit Offset. e.g: result[:10] => Limit 10 offset 0 I have a large database, and I quickly run out of memory with queries that do not utilize the Limit Offset. Anything better than [result[i] for i in range(0,10)] would be greatly appreciated (since that would run 10 different queries). Thanks, -- Hatem Nassrat --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe fr PyDevler 2008-08-28T04:18:48 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/15027 I was just wondering if the folowing possible. A record has severall groups connected through a N:M relation. But it also has a parent. What I would like is that all the groups from the parent (and its parent, etc.) also are seen as group for the record. When the parent has a group added or removed that should then be 'visible' at the child. Not a very good description I am afraid, so I'll try to give an example. Say I have the groups G1, G2, G3, G4 and G5. I have the records R1 and R2. R1 is the parent of R2 and does not have a parent itself. If R1 has the groups G1 and G2 and R2 has the groups G2, G4 and G5, then when asking the list of groups of R2 should give, G1, G2, G4 and G5. When quering for records that have group G1, both R1 and R2 should be returned. When adding G3 to R1 the list for R2 should be: G1, G2, G3, G4 and G5. When removing G1 from R1 the list for R2 should be: G2, G3, G4 and G5. When removing G2 from R1 the list for R2 should be: G2, G3, G4 and G5. (R2 has itself also group G2.) I Cecil Westerhof 2008-08-28T12:11:42 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/15022 Hi, I am working on a wxPython application that saves data to various tables in our MS SQL Server 2000. I connect to one table and get data using a session. This works great. I then do a session.close() and then a conn.close() where conn = engine.connect(). This seems to work as expected as well. The problem happens when I connect to the second table. I get the data from the second table just fine, but when I try to update it using my 2nd session object, I get the following error: Traceback (most recent call last): File "\\debianis\loginscript$\PythonPackages\Development\Timesheet_sa \ts_worksheet_MT.py", line 689, in onClose session.flush() File "c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg \sqlalchemy\orm\session.py", line 789, in flush self.uow.flush(self, objects) File "c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg \sqlalchemy\orm\unitofwork.py", line 233, in flush flush_context.execute() File "c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg \sqlalc Mike 2008-08-27T21:34:40 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/15019 I am looking into what are the best practices, common methods of doing things like RPC with SqlAlchemy declarative_base sublcasses. Is there a concept of DTO? Or is this something I would have to build into it myself? Thank you. Arthur Pemberton 2008-08-27T20:57:46 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/15016 Hi, I've been trying to profile why loading a single table from remote ORACLE DB takes more then 5 seconds. Here is some numbers: create engine 0.0740728378296 engine.connect 2.05604815483 SELECT table_name FROM all_tables WHERE owner='ZZZ' get tables 0.18466091156 Loading 'triggerpathdescription' table 5.85890698433 Load tables manually ['triggerpathdescription'] load table 5.85950708389 It looks like SQLAlchemy takes 25 fetchone calls in order to autoload table, but I tweak a code for ORACLE engine to use fetchmany and just slighly reduced the total time. To me it's WAY to long and as far as I can tell it internals of building foreign-key relationships. So I wonder if there is a way to speed up such auto load. I'm attaching a simple test code below. Thanks, Valentin. #!/usr/bin/env python import sqlalchemy,types,sys,os,time def loadTables(tableNames): for tName in tableNames: t = tName[0].lower().split(".")[-1] print "Loading '%s' table"%t t1=time.time() tables[t]=sqlal vkuznet 2008-08-27T19:57:25 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/15001 Hi, Using the example in the tutorial: ... Column('id', Integer, primary_key=True), ... Column('name', String), ... Column('fullname', String), ... ) if I commit the following is there a way to get the value of "id" without calling select? I'm using MySQL if that matters. Thanks! --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- manuhack 2008-08-27T06:25:43 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14998 I have a program in which I display severall tables. This is partly generic, so I would like to have one general function and put the specific functionality in the object definition itself. For example I have: ##### kmStandTable = sa.Table( 'kmStand', metadata, sa.Column('datum', sa.Date, primary_key = True), sa.Column('beginStand', sa.Integer, nullable = False), sa.Column('eindStand', sa.Integer, nullable = False), ) class KmStand(object): def __repr__(self): return '<KmStand: %10.10s, %6d, %6d>' % (self.datum, self.beginStand, self.eindStand) columns = ( ('Datum', 80), ("BeginStand", 80), ('EindStand', 80), ('Verschil', 60), ) size = (320, 600) title = 'Km Stand' sa_orm.mapper(KmStand, kmStandTable) ##### And in my main code I have: ##### i = 0 for row in session.query(KmStand).all(): if (row.beginStand == -1) or (row.eindStand == -1): afstand = -1 else: afstand = row.eindStand - row.beginStand addRow(self Cecil Westerhof 2008-08-27T09:03:53 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14985 Hi, I am trying get a count for a query but it errors out with following message: Page handler: <bound method Root.index of <harvest.controllers.Root object at 0x018444B0>> Traceback (most recent call last): File "c:\python25\lib\site-packages\cherrypy-2.3.0-py2.5.egg\cherrypy \_cphttptools.py", line 121, in _run self.main() File "c:\python25\lib\site-packages\cherrypy-2.3.0-py2.5.egg\cherrypy \_cphttptools.py", line 264, in main body = page_handler(*virtual_path, **self.params) File "<string>", line 3, in index File "c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg \turbogears\controllers.py", line 359, in expose *args, **kw) File "<string>", line 5, in run_with_transaction File "c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg \turbogears\database.py", line 407, in sa_rwt retval = func(*args, **kw) File "<string>", line 5, in _expose File "c:\python25\lib\site-packages\TurboGears-1.0.5-py2.5.egg \turbogears\controllers.py", line 372, in <lambda> mapping mkhan 2008-08-26T23:17:26 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14984 Hi all, I'm using Elixir & SQLAlchemy to manage some entities, it's going quite well. However, I just realize that when I was deleting my parents entities, my sons entites were not deleted. The're might be something I didn't get quite well, so that's why I'm requiring some help... Let's say I have two classes: class PublicationElement(Entity): using_options(tablename='publication') sections = OneToMany('SectionElement') covers = OneToMany('PublicationCoverElement') publication_id = Field(Integer, primary_key=True) name = Field(Unicode(255)) short_name = Field(Unicode(30)) app_download_link = Field(UnicodeText) class SectionElement(Entity): using_options(tablename='section') publication = ManyToOne('PublicationElement', colname="publication_id", cascade="all,delete-orphan") section_id = Field(Integer, primary_key=True) name = Field(Unicode(255)) articles_on_cover = Field(Integer) articles_on_sub_cover = Field(Integer) ad_on_top = Field(Boolean) Alex Mathieu 2008-08-26T22:08:58 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14983 I just started playing with sqlalchemy. At the moment I am working with one database, but properly I need to work with severall database at the same time, but I want to work in such a way, that I do not need to know in which database a certain table resides. Is this possible? For example it looks like it is possible for the session object. I use in a model file (through an import): ##### import sqlalchemy as sa import sqlalchemy.orm as sa_orm metadata = sa.MetaData('sqlite:////home/cecil/databases/metingen.db') engine = metadata.bind Session = sa_orm.sessionmaker() session = Session() gewichtTable = sa.Table( 'gewicht', metadata, sa.Column('id', sa.Integer, primary_key = True), sa.Column('datum', sa.Date, nullable = False, unique = True), sa.Column('gewicht', sa.Float, nullable = False), sa.Column('vet', sa.Float, nullable = False), sa.Column('water', sa.Float, nullable = False), sa.Column('spieren', sa.Float, nullable = False), ) class Gewicht(object): def __repr__( Cecil Westerhof 2008-08-26T13:56:40 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14980 Dear list... I'm on a documentation frenzy now in my current Pylons project. And I'm using Sphinx for that purpose. All my controllers and helper functions are documented automatically. Great. Unfortunately Sphinx fails to create module documentation for my models. I'm staying close to the "Using SQLAlchemy with Pylons" way as documented on the wiki. This is the output on my console: ================================= $> make html mkdir -p _build/html _build/doctrees sphinx-build -b html -d _build/doctrees . _build/html Sphinx v0.4.2, building html trying to load pickled env... not found building [html]: targets for 4 source files that are out of date updating environment: 4 added, 0 changed, 0 removed reading... antrag-allgemein antrag-erstellen api Exception occurred: File "/var/lib/python-support/python2.5/sqlalchemy/schema.py", line 101, in __call__ "columns on an existing Table object." % key) InvalidRequestError: Table 'applications' is already defined for this MetaData instance. Speci Christoph Haas 2008-08-26T11:01:12 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14977 Hello, I have created a manual and give example on how to use sqlalchemy and openoffice together to create documents. The code shows you how to use openoffice as a document template and fill in the data from the database. I basically use "Find and Replace" function of openoffice to do the "template" work. SqlAlchemy and OpenOffice http://lucasmanual.com/mywiki/OpenOffice#head-a6f86c6beea8170c083cd3f45438066e6f20760f The program works great. I'm able to create around 100 letters per minute which is way better then typing them. Because it is so easy to find and replace in openoffice I can let marketing people design the forms, and I just fill it in. Great Combination!!! Enjoy, Lucas Lukasz Szybalski 2008-08-26T16:04:32 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14976 Hi all, I have 2 tables: - info (id: integer, cfg: integer) - info_with_error (id:integer, cfg: varchar(32)) I'd like to check the query before inserting into table. For instance: if I build this query: INSERT INTO info VALUES("STRING") I d like to catch the warning generated during the insertion: Warning: Incorrect integer value: 'STRING' for column 'cfg' at row 1 cursor.execute(statement, parameters) If my query has an error, i put the query in the table info_with_error... I hope it's clear enougth How can I do that? Thanks in advance. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- menuge 2008-08-26T09:14:55 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14961 Hi, I'm wondering if the following is possible with mappers: I have two tables A and B, whereas B is referenced by A. What I need is a special mapper for A that returns all referenced rows in B except those, that have a specific value in one column of the row, e.g.: Table A: id, sometext, reftoB 1, xyz, 1 2, abc, 2 Table B: id, sometext 1, foo 2, NOTTHISONE <- Should not be returned by the mapper Is there a simple way to achieve this? Best Regards, Hermann Hermann Himmelbauer 2008-08-25T16:22:08 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14957 Hi, I have a table B which is connected to table A through a integer foreign key. This is a 1:1 relation so for every item in table B there is exactly one item in table A. But not every item in A must have a connection to B. I stored the foreign key in table B and using it as a primary key (that was the easiest way to ensure that there is only one item in B for every item in A). No I want to get all items from table A where no matching item in table B exists. I'm aware that this would be very easy if table A stores the foreign key. I think there is no direct sql query which can do that for me, right? So I have to query for every item in table A that there is no matching item in B? (I just want to make sure that I'm not missing a really obvious optimization.) Thanks so far, fs Felix Schwarz 2008-08-25T10:13:43 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14954 Hi! How can I disable a polymorphic load for a single query? I found a hint in http://markmail.org/message/2kwbm377j3pdvvqb but I can't find more. Thanks for any advice. David --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- ml 2008-08-25T14:35:36 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14952 Hi all, Really, you do an amazing job on that stuff, it's a pleasure to work with! A short question though. It's possible to get a random order on selects in mysql using the RAND() in parameter of ORDER BY. I know similar (but different) was possible on others. What about sqlalchemy? The goal is to have only one result on the query, randomly. Thanks again Gus --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- GustaV 2008-08-23T16:10:48 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14947 I have this in my model: sa.Column(u'created', sa.Date(), default=sa.func.now()), sa.Column(u'updated', sa.Date(), onupdate=datetime.datetime.now), But the dates don't get updated when I do: langtable = [ """INSERT INTO LANGUAGE (LANGID, NAME, LOCALES) VALUES (1, 'English', 'en')""", .... etc ] conn = engine.connect() for row in langtable: conn.execute(row) for lang in session.query(db.Language).all(): print lang Gives me: Language(created=None, langid=1, locales=u'en', name=u'English', updated=None) I am doing this with sqlite database. What am I doing wrong here? Werner --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~--- Werner F. Bruhin 2008-08-24T10:42:40 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/14944 Hi, I'm using sqlalchemy 0.5 beta 3 and I am trying to have a Call object that contains two relations to a Contact object. One is the callee and the other is the caller. The code is as follows: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey from sqlalchemy.orm import relation, backref, mapper Base = declarative_base() metadata = Base.metadata contact_table = Table('contact', metadata, Column('id', Integer, primary_key=True), Column('first_name', String(20)), Column('last_name', String(30))) call_table = Table('call', metadata, Column('id', Integer, primary_key=True), Column('subject', String(255)), Column('callee_id', Integer, ForeignKey('contact.id')), Column('caller_id', Integer, ForeignKey('contact.id'))) class Contact(object): def __init__(self, first_name, last_name): self.first_name = first_name self.last_name = last_name def __repr__(self): return self.first_name + ' ' + self.last_name mappe Rob 2008-08-23T17:56:48 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.python.sqlalchemy.user