Re: [sqlalchemy] TypeError: unsupported operand type(s) for -: 'float' and 'datetime.datetime' when subtracting DateTime columns directly with MySQL database
Am 28.06.2013, 22:45 Uhr, schrieb Michael Bayer mike...@zzzcomputing.com: its a missing feature right now. ideally we can add a rule to MySQL's datetime object that subtraction should return a type that will translate the float to an Interval (I'm guessing it's a number of days). FWIW the MySQL function timediff might be appropriate: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] between .one() and .first()
Am 17.06.2013, 08:58 Uhr, schrieb Chris Withers ch...@simplistix.co.uk: Hi All, I seems to commonly need to do a query which should return zero or one mapped objects. .one() isn't what I want as no returned object is ok. .first() isn't what I want as if my query would return more than one object, I have the query wrong and so want an exception. .count() would seem to be your friend here, at least in case that a lot of rows might be returned. Is there something already available that meets this need? Only if you can express that need as a query. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 13.06.2013, 00:42 Uhr, schrieb Andy aml...@gmail.com: Ah, okay. I think I understand what I was missing initially. I've had another go at this and it seems to work as required, even on MySQL. Not sure how this all works with SQLAlchemy but I would expect it to be fine with it. insert into favourites (thing_id, group_id) values (2, 2) Error Code: 1062. Duplicate entry '2' for key 'PRIMARY'0.012 sec insert into favourites (thing_id, group_id) values (1, 3) Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (favourites.favourites, CONSTRAINT checker FOREIGN KEY (thing_id, group_id) REFERENCES groups_things (thing_id, group_id))0.007 sec It's not, at least with automatically configured relations, hence this thread. What do you mean with automatically configured relations? Using SQLa to define the schema? I always manage the schema directly so I don't know so much about that. For me, the important thing is that SQLa can work with the schema with the least number of contortions and I don't see any required here. Also, SERIAL? You must be using a real database engine. Well, er, yes. When it comes to modelling that's where you should start. And Oracle is making progress with MySQL now that InnoDB with separate files pro table and 5.6 even seems to have some kind of vacuuming built-in. I dunno. This may be the end of my (nonexistent) web developer career, but I think I will never again recommend using mysql for any purpose whatsoever (except perhaps compatibility). I've learned my lesson. Next time I'll use PostgreSQL. Well, yes, I'd always recommend Postgres over MySQL but I don't see what the choice of backend has to do with this problem, except how well reflection works with Postgres. From a developer's perspective MySQL's biggest problem, apart from MyASM, is that its behaviour can be unpredictable. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 13.06.2013, 21:20 Uhr, schrieb Andy aml...@gmail.com: It's the original issue. The relation (that I want the ORM to see) isn't what's literally set in the schema by foreign keys, and the primaryjoin/foreign_keys ORM magic for this IMO sucks. Something like onetomany and manytoone in the mapper config would solve the problem nicely. Can't you make the join condition explicit? I seem to remember doing something like that recently. I don't like relying on magic ever but I do think that SQLAlchemy does a really excellent job in most situations. In others, I think you can use SQL Expressions as Mike has recently indicated on another thread. My big point is that people using databases have to be prepared to find out how to get the best use of them and that often means writing out a query in SQL first and then writing it in SQLAlchemy. Well, yes, I'd always recommend Postgres over MySQL but I don't see what the choice of backend has to do with this problem, except how well reflection works with Postgres. From a developer's perspective MySQL's biggest problem, apart from MyASM, is that its behaviour can be unpredictable. The MySQL vs PostgreSQL holy war is completely irrelevant to this issue Indeed, but you started it! ;-) Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 11.06.2013, 23:47 Uhr, schrieb Andy aml...@gmail.com: I could do that, but I'd change the foreign key in favorites to point at group_things, since your favorite group must be a group that you're in. Then I'd drop the other foreign key constraint as unnecessary, since it's implied by the new one, and then I'd be back at square one (needing to convince the ORM to understand a relation with no real foreign key). Ah, okay. I think I understand what I was missing initially. I've had another go at this and it seems to work as required, even on MySQL. Not sure how this all works with SQLAlchemy but I would expect it to be fine with it. insert into favourites (thing_id, group_id) values (2, 2) Error Code: 1062. Duplicate entry '2' for key 'PRIMARY' 0.012 sec insert into favourites (thing_id, group_id) values (1, 3) Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (favourites.favourites, CONSTRAINT checker FOREIGN KEY (thing_id, group_id) REFERENCES groups_things (thing_id, group_id)) 0.007 sec Also, SERIAL? You must be using a real database engine. Well, er, yes. When it comes to modelling that's where you should start. And Oracle is making progress with MySQL now that InnoDB with separate files pro table and 5.6 even seems to have some kind of vacuuming built-in. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. favourites.sql Description: Binary data
Re: [sqlalchemy] Non negative integer column
Am 10.06.2013, 15:46 Uhr, schrieb Michael Bayer mike...@zzzcomputing.com: Oh. Well a python side rule is very different from a server side rule, but if app side is all you need then sure you have a lot of options there. Use a TypeDecorator, check the docs there are many examples. FWIW David Mertz has just written an interesting checker for Python 3 http://code.activestate.com/recipes/578528-type-checking-using-python-3x-annotations/ Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Using SQL Expressions with existing declarative_base tables
Hi, I've got an application that I'm currently porting from MySQL to Postgres. The application itself has model classes derived from declarative_base but I have some housekeeping scripts that are currently hardcoded and which I like to move to SQL expressions to try and avoid inconsistencies between MySQL and Postgres. Is this actually possible? I guess I should add that I have different configurations for the app and housekeeping so that I cannot bind the engine to classes in the way described in the SQL Expression documentation. What is the best way to do this so that I have access to .insert(), .update() for my models? Chrlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Using SQL Expressions with existing declarative_base tables
Hi Mike, Am 10.06.2013, 18:38 Uhr, schrieb Michael Bayer mike...@zzzcomputing.com: I'm not sure here where the pain point is for you, just how to get access to something.execute()? Session has execute(), Engines and Connections can be stuck onto Sessions, there's any combination you'd want there. Anything with an execute() on it, you can send an insert()/update() etc. into. I've already managed to get connections and execute. Let me provide some context: I have some models defined for a Pyramid application: https://bitbucket.org/charlie_x/python-httparchive/src/43bd077a2d75626fe4da5251695526d7bdd7274f/httparchive/httparchive/models.py?at=default And some necessary but ugly housekeeping scripts: https://bitbucket.org/charlie_x/python-httparchive/src/43bd077a2d75626fe4da5251695526d7bdd7274f/httparchive/httparchive/scripts/update.py?at=default Currently, I get the connection and just execute the statements. But, of course, some of won't work with both MySQL and Postgres. I have currently naively tried from httparchive.models import Page pages = Page() pages.update(…) This fails because AttributeError: 'Page' object has no attribute 'update' How should I be doing this instead? Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Using SQL Expressions with existing declarative_base tables
Am 10.06.2013, 18:54 Uhr, schrieb Michael Bayer mike...@zzzcomputing.com: Page.__table__.update() Duh! Just didn't see that. or agnostic of declarative: from sqlalchemy import inspect pages = inspect(Page).local_table pages.update() That looks nicer to me, thanks. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 07.06.2013, 00:05 Uhr, schrieb Michael Bayer mike...@zzzcomputing.com: can you show me the alternate design you have in mind? I'm not sure what normalize to favorites with strict 1:1 looks like. this is all just my own curiosity. the issue at hand is something different. Well, I guess I asked for it. Pseudo-tables things ( thing_id SERIAL PRIMARY KEY ) groups ( group_id SERIAL PRIMARY KEY ) groups_things( thing_id INTEGER FOREIGN KEY REFERENCES (things.thing_id), group_id INTEGER FOREIGN KEY REFERENCES (groups.group_id) ) favourites (I'm a limey) are just another relation. favourites( thing_id INTEGER PRIMARY KEY FOREIGN KEY REFERENCES (things.thing_id), group_id INTEGER FOREIGN KEY REFERENCES (groups.group_id) ) Depending on how you look at it, favourites are just another relation and could have additional attributes like colour, or simply behave like a sub-class of groups. Am I missing something big in the original question as to why this isn't a reasonable solution? Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 07.06.2013, 01:13 Uhr, schrieb Andy aml...@gmail.com: I may be misunderstanding the question, but the reason that having a favorite is optional is because I'm using mysql and mysql doesn't supported deferred constraints. Oh, I feel your pain! But you are using an engine that at leasts pretends to support foreign key constraints. If not, book yourself in at the next asylum! So if favorite were NOT NULL, then there would be no way to create the thing. Which is why it should be a relation all of its own. I've a sneaking suspicion that I'm being incredibly dense and missing something obvious. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] problem with with_entities(), over() and label()
Am 06.06.2013, 18:56 Uhr, schrieb Ladislav Lenart lenart...@volny.cz: Hello. I have already solved the issue by using subquery: SELECT t.id AS t_id, t.rownum AS t_rownum FROM ( SELECT FROM foo.id AS id, row_number() OVER (ORDER BY foo.id) AS rownum ) AS t WHERE rownum % 50 = 1 I have just tried your suggestion about using HAVING instead of WHERE, but that fails with the same error. Thus a label cannot be used inside a query. Yeah, sorry. As it says in the docs: If the query contains any window functions (see Section 3.5, Section 9.21 and Section 4.2.8), these functions are evaluated after any grouping, aggregation, and HAVING filtering is performed So, it would be possible with standard aggregate functions, well something like it is, but not with anything like row_number() which must use a window. Compared with Python seems a weird way of striding through the results but maybe that's just SQL. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 06.06.2013, 20:21 Uhr, schrieb Andy aml...@gmail.com: IOW I have things and groups. The rel table is a many-to-many relation between things and groups. A thing also may have a favorite group; if so, there has to be a rel between that thing and its favorite group. Are favourites optional? Why not normalise to Favourites with strict 1:1 with things and groups? Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Feature suggestion: fake ForeignKeyConstraints to simplify joins
Am 06.06.2013, 23:36 Uhr, schrieb Michael Bayer mike...@zzzcomputing.com: On Jun 6, 2013, at 5:18 PM, Charlie Clark charlie.cl...@clark-consulting.eu wrote: Am 06.06.2013, 20:21 Uhr, schrieb Andy aml...@gmail.com: IOW I have things and groups. The rel table is a many-to-many relation between things and groups. A thing also may have a favorite group; if so, there has to be a rel between that thing and its favorite group. Are favourites optional? Why not normalise to Favourites with strict 1:1 with things and groups? by putting the FK constraint to the composite primary key of rel, it guarantees that the favorite item is a member of the thing-groups collection. I understand that I just wonder whether every thing has a favourite or not, in which case the structure is not fully normalised and that is how I would do it because it makes the projections easier. Well, to my mind at least. And, wouldn't it resolve the join problem? Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Including counts in query results
Am 04.06.2013, 04:57 Uhr, schrieb Rob Green robsgr...@gmail.com: Hello, I'm having some trouble getting my SQL query to work in SQLAlchemy. In this example, I want to find out the number of meeting rooms and bathrooms available to a user. The user is part of a userGroup, which is assigned to one building. The building has many meeting rooms and bathrooms. My sql query looks like: select user.id, (select count(*) from meetingRoom where userGroup.buildingCode = meetingRoom.buildingCode), (select count(*) from restroom where userGroup.buildingCode = restroom.buildingCode) from user join userGroup on user.userGroupId = userGroup.userGroupId I've tried using subqueries: meetingRoomCount = session.query(func.count(MeetingRoom.id)).join(UserGroup, MeeingRoom.buildingId == UserGroup.buildingId).subquery() bathroomCount = session.query(func.count(Bathroom.id)).join(UserGroup, Bathroom.buildingId == UserGroup.buildingId).subquery() session.query(User.id, meetingRoomCount, bathroomCount).first() But this returns the total number of meeting rooms and bathrooms in the database, not the ones that are specific to that user. I feel like I'm missing something simple here, anyone have any ideas? I think you might have more success if you explicitly alias your counts. SELECT user.id, mr.total, rr.total FROM user, (select count(*) AS total from meetingRoom where userGroup.buildingCode = meetingRoom.buildingCode) AS mr, (select count(*) AS total from restroom where userGroup.buildingCode = restroom.buildingCode) AS rr from user join userGroup on user.userGroupId = userGroup.userGroupId Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] DISTINCT with LIMIT problem
Am 30.05.2013, 23:19 Uhr, schrieb Kent jkentbo...@gmail.com: For example, a query may look like this: select distinct count(*) over () as recordcount, tablea.colx, tableb.coly from tablea, tableb where limit 100 This doesn't *quite* work because the analytical window function count(*) over() is applied *before* the distinct, so the count returns the wrong number (a Cartesian effect, returning 72 instead of 17, in this example). Why are you generating Cartesian products? DISTINCT is designed to work on denormalised result sets, ie. those which can contain duplicates. Can't you avoid this with a join between your tables? Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
Am 23.05.2013, 21:50 Uhr, schrieb Sean Lynch techni...@gmail.com: Not within one of my SQLAlchemy apps, but I have an NHibernate application where the database and application servers are in different data centers (out of my control) and thus using .future() calls saves a good bit I/O time. After seeing the ActiveRecord::Futures project show up on https://github.com/languages/Ruby, I was curious if SQLAlchemy had a similar feature / capability. I'm not sure how related this is to your problem but ActiveRecord needs something like this because it has a very poor model with lots of I/O to the database, SQLAlchemy gives you the flexibility to decide how you want your queries processed. With a persistent connection I wouldn't have thought it made much difference where the servers are. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] non foreign-key relationship between schemas
Am 24.05.2013, 17:53 Uhr, schrieb YKdvd davidobe...@gmail.com: but I can't seem to find anything that works. I can provide some sort of instance method or property with the necessary id value for foreign(), but I'm not sure if this is acceptable, or even if the remote reference is correct (I've tried the string Studio.productions.id as well as the Production.id variable. Note that you can stuff a join() method with all the conditions you need and this is sometimes unavoidable. I could probably add a production_id column to the episodes table - it would get filled with the same value for all records in a particular Production_?.episodes table. That would let me do a normal foreign_key relationship and shouldn't break the legacy PHP access. But I was curious if there is a way to torture SQLAlchemy into creating this sort of non-column relationship? You really do not want to try to trick SQLAlchemy (or yourself) into even thinking this. Non-existent foreign keys will almost certainly mean a table scan with terrible implications for performance. I recently discovered that MySQL will do this even for indexed columns. :-/ Make the relationship explicit, note dump, truncate, alter, import is often the only way to do this and enjoy the, er, show. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Working with stored procedures
Am 20.05.2013, 00:53 Uhr, schrieb Michael Bayer mike...@zzzcomputing.com: Yes, SQLA actually doesn't get into callproc() or any of that at all right now. Not that we can't someday, but it hasn't been a priority to build around that. (It is something I'm curious about, stored procedure support, but I'd need a reason to go there). Hi Mike, thanks for the help and the explanation. I can understand why support isn't there - there is so much that can be done with SPs. FWIW and for future reference as I didn't find much myself when I searched, I've put the code into practice: https://bitbucket.org/charlie_x/python-httparchive/src/a9a2c30c8ffb4e1d39720ad44368284922e0e94f/httparchive/httparchive/views/trends.py?at=default#cl-341 I do think it would be useful to support something like pivots - whether these are done as stored procedures or table functions like Postgres http://www.postgresql.org/docs/9.1/static/tablefunc.html ie. a view on a normalised table which transposes rows into columns, the dynamic nature of which causes I problems for the ORM, I think. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are consecutive query.get calls supposed to send a single SELECT query?
Am 21.05.2013, 17:31 Uhr, schrieb Etienne Rouxel
rouxel.etie...@gmail.com:
if __name__ == '__main__':
engine = create_engine('postgresql://user@localhost:5432/mydatabase')
Session = sessionmaker(bind=engine)
session = Session()
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
session.query(Descriptiontype).get(-2147483648)
Each call to session.query() is creating a new query object, thus, a new
query will run on the DB.
Try:
q = session.query(Descriptiontype)
q.get(-24…)
Charlie
--
Charlie Clark
Managing Director
Clark Consulting Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Working with stored procedures
Am 19.05.2013, 17:09 Uhr, schrieb Michael Bayer mike...@zzzcomputing.com: you want to pull a DBAPI cursor from SQLAlchemy's notion of the DBAPI connection, like this: dbapi_conn = session.connection().connection cursor = dbapi_conn.cursor() Thanks for explaining the nested connection stuff. So, you recommend completely bypassing SQLa for this sort of thing? I found I was doing that anyway because of the inability to map the results to any models. Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
