[sqlalchemy] mssql and pyodbc weirdness with data not saving
I have been having a weird thing happen when using pyodbc with mssql. The create table statements work, but none of the INSERT statements do. For example, when I run code and echo, I get this: (TCSData) F:\Inetpub\TCSData\src\tcsdata-dist\tcsdata>pysmvt broadcast initapp calling: action_pysapp_initapp 2009-05-29 02:22:17,194 INFO sqlalchemy.engine.base.Engine.0x...a350 BEGIN 2009-05-29 02:22:17,210 INFO sqlalchemy.engine.base.Engine.0x...a350 INSERT INTO users_permission (name) VALUES (?); select scope_identity() 2009-05-29 02:22:17,210 INFO sqlalchemy.engine.base.Engine.0x...a350 [u'webapp-c ontrolpanel'] 2009-05-29 02:22:17,210 INFO sqlalchemy.engine.base.Engine.0x...a350 COMMIT But there is nothing in that table when I am done committing. I profiled the server, and here is the code sequence that it shows: set implicit_transactions on exec sp_datatype_info 93, @ODBCVer = 3 SET IMPLICIT_TRANSACTIONS OFF BEGIN TRANSACTION declare @P1 int set @P1=1 exec sp_prepexec @P1 output, N'@P1 nvarchar(19)', N'INSERT INTO users_permission (name) VALUES (@P1); select scope_identity()', N'webapp-controlpanel' select @P1 exec sp_unprepare 1 IF @@TRANCOUNT > 0 COMMIT TRAN If is switch to adodbapi, then the insert works just fine. --~--~-~--~~~---~--~~ 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: making join with using clause
Thanks. I have one more question do we have the option of making different type of joins ? i have seen in docs it says it have join and outer join only What abbt left outer right outer natural cross and many ... On May 28, 9:00 pm, "Michael Bayer" wrote: > SQLAlchemy SQL expressions have no need for JOIN USING which is just a > typing saver in straight SQL. you can approximate this in Python as > follows: > > def using(t1, t2, *names): > return join(t1, t2, onclause=and_(*[t1.c[x]==t2.c[x] for x in names])) > > Ash wrote: > > > Hello > > > I want to make a join with using . > > Eg. Table1 join table2 using (id) > > > How can i do this? > > > I have tried for join like table1 join table on t1.xx = t2.xx > > > Making the object like > > t1 = Table(t1 ,metadata) > > t2 = Table(t2 ,metadata) > > > t1xx = Column(xx) > > t1.append_column(t1xx) > > t2xx = Column(xx) > > t2.append_column(t2xx) > > i did lik join(t1,t2, onclause=t1xx == t2xx) > > > How cn i add using in the above way ? > > > 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@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: 0.5.4p2 Tests failed
Hi Michael, I just installed 0.5.4p2 on my Mac 10.5.7 Intel with the Python bundled pysqlite. Test suite ran without a single error. >From your post to psqlite, there is a way for memory and gc to be managed. Hope they implement the change on the upcoming updates. Meanwhile, it's probably safe to use pysqlite 2.5.5 with the failed memory test. Thanks for your help. Cheers, Mel On Wed, May 27, 2009 at 10:08 AM, Michael Bayer wrote: > I did a static build of pysqlite 2.5.5 against sqlite 3.6.14. All tests > pass except the single memory leak test. I'll see if i can isolate this for > them. > > On May 27, 2009, at 11:21 AM, Melton Low wrote: > > The first time I installed pysqlite 2.5.5, my Mac 10.4 build fine but not > on the 10.5. The solution from the pysqlite people was to build_static on > 10.5. It seem to work the first time around. Unfortunately I was not able > to get a clean install last night on either systems. So for the time begin > I will stick with the Python included version. > > Regards, Mel > > On Wed, May 27, 2009 at 9:05 AM, Michael Bayer > wrote: > >> with the latest pysqlite (2.5.5) I also get a huge (192) amount of >> failures with many versions of SQLalchemy, including p1 and p2. I had >> noticed that pysqlite's development seemed to be going further with changes >> that make the library harder to develop against, but particularly disturbing >> is that one of our memory leak tests seems to reveal a memory leak within >> pysqlite.So overall this is a very disturbing result and I would >> recommend against using the non-python included pysqlite. >> >> >> On May 27, 2009, at 10:51 AM, Melton Low wrote: >> >> Hi Michael, >> >> I re-build my systems last night - zapped Python, Mercurial, Sqlite, >> pysqlite, psycopg, and SQLAlchemy. You 0.5.4p1 test suite ran perfectly as >> long as I only have the version of pysqlite included with Python. As I am >> going out of town, I decided to leave 0.5.4p2 alone for the time being. As >> soon as I can I will try installing it and will let you know. >> >> Mel >> >> On Wed, May 27, 2009 at 8:21 AM, Michael Bayer >> wrote: >> >>> >>> the changes between 0.5.4p1 and 0.5.4p2 are extremely minimal and I've >>> pasted them at http://paste.pocoo.org/show/119367/ .Additionally >>> here they are passing for py2.4 and py2.5 at http://groovie.org:8012/ >>> which is an intel linux platform. I run the tests with 2.6 here on >>> an intel mac. >>> >>> In particular the PPC tests seem to be running with pre-existing >>> tables from a previous run - run the tests with --dropfirst to clear >>> out all pre-existing rables. For the intel, my guess would be the >>> wrong version of SQLAlchemy is being tested (like an early 0.5 version >>> perhaps). >>> >>> >>> On May 26, 2009, at 11:34 PM, Melton Low wrote: >>> >>> > Hi, >>> > >>> > I just installed the just released version 0.5.4p2. 3 of the tests >>> > failed on my Intel Mac and 97 failures on my PPC Mac. With the >>> > previous version 0.5.4p1, all tests ran successfully on both >>> > systems. The successfull tests were ran with the stock version of >>> > psqlite bundled with Python. Pysqlite 2.5.5 was installed over the >>> > standard version bundled with Python. I did not re-run the >>> > SqlAlchemy test suites after installing pysqlite 2.5.5. Earlier >>> > today I installed the latest release of Sqlite 3.6.14.2. >>> > >>> > I have attached my installation log and the test result logs for >>> > both systems. Since I am just learning SQLAlchemy, the failed tests >>> > probably will not affect me. In any case, I will be reverting back >>> > to 0.5.4p1. Hopefull, the log entries can help you isolate the >>> > problems. >>> > >>> > My environment: >>> > Mac OS X 10.5.7 Intel MacBook >>> > Python 2.6.2 >>> > pysqlite 2.5.5 >>> > psycopg2 2.0.11 >>> > >>> > Mac OS X 10.4.11 PowerPC >>> > Python 2.6.2 >>> > pysqlite 2.5.5 >>> > psycopg2 2.0.11 >>> > >>> > Regards, Mel >>> > >>> > > >>> > >> > installlog.txt>>> > SQLAlchemy-0.5.4p2 installlog.txt> >>> >>> >>> >>> >> >> >> >> >> >> >> > > > > > > > > --~--~-~--~~~---~--~~ 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: making join with using clause
SQLAlchemy SQL expressions have no need for JOIN USING which is just a typing saver in straight SQL. you can approximate this in Python as follows: def using(t1, t2, *names): return join(t1, t2, onclause=and_(*[t1.c[x]==t2.c[x] for x in names])) Ash wrote: > > Hello > > I want to make a join with using . > Eg. Table1 join table2 using (id) > > How can i do this? > > I have tried for join like table1 join table on t1.xx = t2.xx > > Making the object like > t1 = Table(t1 ,metadata) > t2 = Table(t2 ,metadata) > > t1xx = Column(xx) > t1.append_column(t1xx) > t2xx = Column(xx) > t2.append_column(t2xx) > i did lik join(t1,t2, onclause=t1xx == t2xx) > > How cn i add using in the above way ? > > 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@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: multiple tables for only one schema ...?
sbard wrote: > > hello, > i've got a database with one table per country. > for example : >part_es (for spain) >part_uk () >part_it > > each tables have got the same schema > (id, libel, description, part_numer) > same __init__ method > same __repr__ method > > is there an easy way to avoid code multiplication and simplify a new > country integration > inheritance way throw me away ... > > if i've got somewhere the list of possible country, is there any way > too loop and add > to declarative_base ? > > dont' hit me for such dummy/newbie question map anonymous classes. if you're already on declarative the metaclass should do it for you: from sqlalchemy.ext.declarative import DeclarativeMeta def add_a_table(tablename): return DeclarativeMeta("%sMyBase" % tablename, (MyBase,), {'__tablename__':tablename} --~--~-~--~~~---~--~~ 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: Populate a mapped class from RowProxy
Andi Albrecht wrote: > > class Foo(object): > pass > > database.mapper(Foo, database.metadata.tables["footable"]) > > Now I have a rather complex SQL that I would like to execute directly > instead of using object queries: > > sql = "select * from footable where something_really_complex_happens" > for rp in database.session.execute(sql): > f = Foo() > for key, value in rp.iteritems(): > setattr(f, key, value) > > The problem is that when I try to save the created object, it tries to > do an INSERT instead of an UPDATE and that AppenderQueries defined in > the mapping don't work as they try to build a JOIN condition with None > instead of the primary key. > > There must be something wrong with this approach :) Any tipps how to > solve this? the most typical way to use raw SQL is to make sure it has the right column names and use query.from_statement(): rows = query.from_statement("select the_correct column_names from ").all() a variant of the above approach is to pass the live cursor into query.instances(), but this doesn't really change much: result = engine.execute("select the_correct column_names from ") rows = list(query.instances(result)) --~--~-~--~~~---~--~~ 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] multiple tables for only one schema ...?
hello, i've got a database with one table per country. for example : part_es (for spain) part_uk () part_it each tables have got the same schema (id, libel, description, part_numer) same __init__ method same __repr__ method is there an easy way to avoid code multiplication and simplify a new country integration inheritance way throw me away ... if i've got somewhere the list of possible country, is there any way too loop and add to declarative_base ? dont' hit me for such dummy/newbie question --~--~-~--~~~---~--~~ 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] Populate a mapped class from RowProxy
Hi, maybe I've overlooked something very trivial, but how can I populate a mapped class from a RowProxy? Here's a simple example of what I'd like to do (using TurboGears): class Foo(object): pass database.mapper(Foo, database.metadata.tables["footable"]) Now I have a rather complex SQL that I would like to execute directly instead of using object queries: sql = "select * from footable where something_really_complex_happens" for rp in database.session.execute(sql): f = Foo() for key, value in rp.iteritems(): setattr(f, key, value) The problem is that when I try to save the created object, it tries to do an INSERT instead of an UPDATE and that AppenderQueries defined in the mapping don't work as they try to build a JOIN condition with None instead of the primary key. There must be something wrong with this approach :) Any tipps how to solve this? Andi --~--~-~--~~~---~--~~ 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: This join does not fill in the collection
> -Original Message- > From: sqlalchemy@googlegroups.com > [mailto:sqlalch...@googlegroups.com] On Behalf Of Marcin Krol > Sent: 28 May 2009 13:57 > To: sqlalchemy@googlegroups.com > Subject: [sqlalchemy] Re: This join does not fill in the collection > > > Hello Simon, > > This answered my question, thanks! (I don't know how I missed that in > docs..) > > This is theoretically theoretical: > > But suppose I *did* some selection on Hosts and still used > .options(eagerload('hosts')) on query - would that screw smth up? > > In particular, if I called session.commit() later, would that save > Reservations with changed collections? > > Regards, > mk > I don't know the answer for certain, but I suspect that if you only had a half-loaded collection, you could add and remove items from that collection, and those changes would be reflected in the database, without affecting items that hadn't been loaded. I don't think SA would do the bulk UPDATES or DELETES that would be necessary to affect items that hadn't been loaded. (I also don't know what would happen if you deleted a Reservation with a half-loaded hosts collection. It probably depends on the cascade settings on the relation) Again, these are only guesses. It should be easy enough for you to knock up a test case to find out the answer for sure. 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] Adding alias to columns
Hello, I want to add the alias for the columns is it possible in sqlalchemy. Say i have select id as uid from xyz so i do like this t1 = Table("xyz", metadata) id = Column("id") Now i want to add alias to it id = Column("id",alias_= "uid") :( didnt work Can i add it later like i make default say id.default=1 ? 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@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] making join with using clause
Hello I want to make a join with using . Eg. Table1 join table2 using (id) How can i do this? I have tried for join like table1 join table on t1.xx = t2.xx Making the object like t1 = Table(t1 ,metadata) t2 = Table(t2 ,metadata) t1xx = Column(xx) t1.append_column(t1xx) t2xx = Column(xx) t2.append_column(t2xx) i did lik join(t1,t2, onclause=t1xx == t2xx) How cn i add using in the above way ? 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@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: This join does not fill in the collection
Hello Simon, This answered my question, thanks! (I don't know how I missed that in docs..) This is theoretically theoretical: But suppose I *did* some selection on Hosts and still used .options(eagerload('hosts')) on query - would that screw smth up? In particular, if I called session.commit() later, would that save Reservations with changed collections? Regards, mk King Simon-NFHD78 wrote: >> -Original Message- >> From: sqlalchemy@googlegroups.com >> [mailto:sqlalch...@googlegroups.com] On Behalf Of Marcin Krol >> Sent: 28 May 2009 10:09 >> To: sqlalchemy@googlegroups.com >> Subject: [sqlalchemy] This join does not fill in the collection >> >> >> Hello everyone, >> >> session.query(Reservation, >> Host).join(Reservation.hosts).filter(Reservation ...).all() >> >> Reservation.hosts is not filled in, when I access .hosts >> collection in >> individual Reservations, SQLA issues queries to fill in the >> collection >> one Reservation by one. >> >> Again, Reservation and Hosts are many to many relation. >> >> I certainly can group the Host objects to particular >> Reservations myself >> later. >> >> But... Is there a way to make SQLA do it itself at query >> time? I haven't >> found anything in the docs that would suggest that.. >> >> Regards, >> mk >> > > If your query had also filtered by some of the host columns, the result > set wouldn't contain all the hosts for each Reservation returned. For > this reason, SQLAlchemy doesn't assume that just because the host > columns are available they represent the entire Reservation.hosts > collection. > > If you want Reservation.hosts to be filled in by the query, you want > "eager loading". This can be configured for all queries when you define > the relation (by setting lazy=False), or on a query-by-query basis by > adding an 'eagerload' option to the query. Both of these methods will > add an extra join to the hosts table in your query (separate from any > join that you explicitly ask for) > > If you've added an explicit join and you know that the result set > already contains all the information you need, you can use the > contains_eager option to indicate that the the relation should be filled > in from the information in the result set. > > Documentation for most of this is at > http://www.sqlalchemy.org/docs/05/mappers.html#configuring-loader-strate > gies-lazy-loading-eager-loading > > 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: This join does not fill in the collection
> -Original Message- > From: sqlalchemy@googlegroups.com > [mailto:sqlalch...@googlegroups.com] On Behalf Of Marcin Krol > Sent: 28 May 2009 10:09 > To: sqlalchemy@googlegroups.com > Subject: [sqlalchemy] This join does not fill in the collection > > > Hello everyone, > > session.query(Reservation, > Host).join(Reservation.hosts).filter(Reservation ...).all() > > Reservation.hosts is not filled in, when I access .hosts > collection in > individual Reservations, SQLA issues queries to fill in the > collection > one Reservation by one. > > Again, Reservation and Hosts are many to many relation. > > I certainly can group the Host objects to particular > Reservations myself > later. > > But... Is there a way to make SQLA do it itself at query > time? I haven't > found anything in the docs that would suggest that.. > > Regards, > mk > If your query had also filtered by some of the host columns, the result set wouldn't contain all the hosts for each Reservation returned. For this reason, SQLAlchemy doesn't assume that just because the host columns are available they represent the entire Reservation.hosts collection. If you want Reservation.hosts to be filled in by the query, you want "eager loading". This can be configured for all queries when you define the relation (by setting lazy=False), or on a query-by-query basis by adding an 'eagerload' option to the query. Both of these methods will add an extra join to the hosts table in your query (separate from any join that you explicitly ask for) If you've added an explicit join and you know that the result set already contains all the information you need, you can use the contains_eager option to indicate that the the relation should be filled in from the information in the result set. Documentation for most of this is at http://www.sqlalchemy.org/docs/05/mappers.html#configuring-loader-strate gies-lazy-loading-eager-loading 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: Questions on SQLA Queries
You right, i don't like chain, because i'm to lazy to import itertools %) so i use 'map'. On May 27, 5:13 pm, Gregg Lind wrote: > I believe by "map function", Timothy may be implying that you should > use any of the python idioms for converting iterables of tuples to a > straight tuple. The one I like best > > from itertools import chain > q = session.query(User.name) #(User is a class) > names = itertools.chain(*q.all() ) > > But you could use generator comprehensions ( names = (x[0] for x in > q.all()), operator.itemgetter, or map instead. > > Correct me, Timothy, if necessary. > > Gregg > > On Wed, May 27, 2009 at 6:25 AM, Harish Vishwanath > > > > wrote: > > Thanks! > > > Could you elaborate on how you use the map function? I couldn't find it > > myself in the docs. > > > Regards, > > Harish > > > On Wed, May 27, 2009 at 3:07 PM, Timothy N. Tsvetkov > > wrote: > > >> Q1. Good question %) I didn't find anything about it in docs (but i > >> didn't search a lot), so i use map function to convert it to a list > >> you want. And I think it is the right solution. Because if you query > >> for more then one column (session.query(User.is, User.name).all()) a > >> list of tuples is what you want to get as a result. So i think it is > >> good, that it works the same way for one or more then one query > >> params. > > >> On May 26, 9:10 pm, Harish Vishwanath > >> wrote: > >> > Hello, > > >> > Question 1: > > >> > When there is a query like below : > > >> > q = session.query(User.name) #(User is a class) > > >> > and when I do q.all(), a list of tuples (User.name,) is returned though > >> > a > >> > single column is asked for. Is there a way to get a list directly from > >> > q.all() when a single column is required? > > >> > Question 2: > > >> > I need to delete a bulky table and I want to print diagnostics after n > >> > number of deletes. Is there a way to use Query object so that a SQL > >> > statement like below can be generated? > > >> > " delete from movie where year in (select top 30 year from movie where > >> > year > > >> > > 50); ", so that a message can be logged after every 30 deletes. > > >> > I am using Sqlite DB. > > >> > Regards, > >> > Harish --~--~-~--~~~---~--~~ 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] This join does not fill in the collection
Hello everyone, session.query(Reservation, Host).join(Reservation.hosts).filter(Reservation ...).all() Reservation.hosts is not filled in, when I access .hosts collection in individual Reservations, SQLA issues queries to fill in the collection one Reservation by one. Again, Reservation and Hosts are many to many relation. I certainly can group the Host objects to particular Reservations myself later. But... Is there a way to make SQLA do it itself at query time? I haven't found anything in the docs that would suggest that.. Regards, mk --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---