Re: [sqlalchemy] Re: Generating models from data
Excellent job :-) Actually I already know it because I follow you @Github. 2014-07-29 11:53 GMT-03:00 Jonathan Vanasco jonat...@findmeon.com: If I read the docs and examples correctly... you give it sample data as Python/Json/YAML, and this package turns it into the appropriate models? That's pretty brilliant for rapid prototyping use! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- *Mauricio de Abreu Antunes* Github: https://github.com/mauricioabreu Twitter: https://twitter.com/maugzoide -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Field in my query is a SELECT MAX FROM using a GROUP BY. How to deal with it?
Not recognizing == column x not in field list. It is there but for some reason MySQL DBAPI sees a difference between table.column_name and just column_name. Not sure why. I got what you said. I will try it here. Thanks! 2014-04-11 15:59 GMT-03:00 Michael Bayer mike...@zzzcomputing.com: On Apr 11, 2014, at 2:02 PM, Mauricio de Abreu Antunes mauricio.abr...@gmail.com wrote: My consideration here are: Why is my code translation to FROM client_global_counter, (SELECT client_glogal_counter2.date instead of just FROM (SELECT client_glogal_counter2.date? OK when you see this pattern, FROM x, (SELECT ... FROM .. x), that means you are calling upon columns directly from x in some cases and from the subquery in others. You need to make sure that your outermost SELECT always selects things in context of the subquery, and not the things that are contained within it. This can be illustrated with some rudimentary table objects: from sqlalchemy.sql import select, table, column t1 = table('t1', column('x'), column('y')) s1 = select([t1]).alias() s2 = select([s1.c.x, s1.c.y]) # correct s3 = select([t1.c.x, s1.c.y]) # (typically) incorrect print s2 # prints: # SELECT anon_1.x, anon_1.y # FROM (SELECT t1.x AS x, t1.y AS y # FROM t1) AS anon_1 print s3 # prints: # SELECT t1.x, anon_1.y # FROM t1, (SELECT t1.x AS x, t1.y AS y # FROM t1) AS anon_1 I didn't dig into your code but hopefully you can see where this is happening. MySQL is not recognizing some field like client_global_counter.date, hour and minute in the first line. Looks like my alias is not doing the right thing or I am doing something wrong. define not recognizing. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- *Mauricio de Abreu Antunes* Github: https://github.com/mauricioabreu Twitter: https://twitter.com/maugzoide -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Field in my query is a SELECT MAX FROM using a GROUP BY. How to deal with it?
Hello list, I am refactoring some RAW SQL to use SQL Alchemy ORM Query object (session.query). What I have so far is a SQL to reproduce using SQL Alchemy: My SQL is this: 1. SELECT DATE_FORMAT(DATE, '%Y%m%d') AS `date`, 2. `Hour` AS `hour`, 3. `Minute` AS `minute`, (MAX(TotalMBs)*8/5/60) AS total_mbs, 4. (MAX(TotalMissMBs)*8/5/60) AS total_mbs_miss 5. FROM (SELECT `Date`, `Hour`, `Minute`, SUM(TotalMBs) AS TotalMBs, 6. SUM(TotalMissMBs) AS TotalMissMBs, `ClientID`, `Type` 7. FROM reports.ClientsGlobalCounters 8. WHERE DATE = :date_from AND DATE = :date_to 9. IN() expression here 10. AND `Type` LIKE :service 11. GROUP BY `Date`, `Hour`, `Minute`, `ClientID`, `Type`) 12. AS t1 GROUP BY GROUP_BY here ORDER BY `Date` And my Python code is this: 1. def select_total_mbps(self, date_from, date_to, service, clients= None): 2. sub_qs = reports_session.query( 3. (func.max(ClientGlobalCounter) * 4. 8 / 5 / 60).label('total_miss_mbs'), 5. ClientGlobalCounter.client_id, 6. ClientGlobalCounter.type, 7. ClientGlobalCounter.date, 8. ClientGlobalCounter.hour, 9. ClientGlobalCounter.minute, 10. func.sum(ClientGlobalCounter.total_mbs).label('total_mbs' ), 11. func.sum(ClientGlobalCounter.total_miss_mbs).label( 12. 'total_miss_mbs')) \ 13. .filter(ClientGlobalCounter.date.between(date_from, date_to)) 14. if clients: 15. sub_qs = sub_qs.filter(ClientGlobalCounter.client_id.in_( clients)) 16. sub_qs = sub_qs.filter(ClientGlobalCounter.type.like(service) ) \ 17. .group_by(ClientGlobalCounter.date, 18. ClientGlobalCounter.hour, 19. ClientGlobalCounter.minute, 20. ClientGlobalCounter.client_id, 21. ClientGlobalCounter.type) 22. 23. qs = reports_session.query( 24. ClientGlobalCounter.client_id, 25. ClientGlobalCounter.type, 26. func.date_format(ClientGlobalCounter.date, 'Ymd'), 27. ClientGlobalCounter.hour, 28. ClientGlobalCounter.minute, 29. (func.max(ClientGlobalCounter.total_mbs) * 8 / 5 / 60). label('total_mbs'), 30. sub_qs) 31. qs = qs.group_by( 32. *ClientGlobalCounterAPI.date_group(ClientGlobalCounter, 33.date_from, 34.date_to)) 35. 36. return [d.__dict__ for d in qs.all()] I am not understanding the docs because they only talk about a subselect inside the WHERE. What I am doing here is retrieving a MAX(field) from a SELECT. There is a related problem here: https://groups.google.com/forum/#!topic/sqlalchemy/LBEyRe3w-8Q Can someone guide me to the right direction? Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: django user (using django ORM) + sqlalchemy for other db tables
The major problem is: everything in Django is mapped to the ORM. Even the sessions. Sorry for being negative but that is my way to understand this. 2013/9/9 Jonathan Vanasco jonat...@findmeon.com Honestly, I wouldn't do this. Django has a lot of magic under the hood, and it's ORM does some very specific things to make this magic happen. It's not just the auth, it's how everything is structured in the database and how the app integrates with the database. You're likely to break things and be miserable. Django , Rails, etc are all-in frameworks -- they provide a stack with several decisions made for you; it's all or nothing. I'd personally suggest you either: - Continue using Django with their ORM. Create a secondary model that uses SqlAlchemy to reflect the Django mapping. That will let you use SqlAlchemy to do advanced read queries. - Use a different framework ( Pyramid, Flask, etc ; many have auth plugins that work with SqlAlchemy models ) -- 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. -- *Mauricio de Abreu Antunes* * * Github: https://github.com/mauricioabreu Twitter: https://twitter.com/maugzoide -- 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] Updating a one-to-many relationship
: self.creator.append(Creator(creator)) def __repr__(self): return '%s, created by %s' % (self.company, self.creator[0]) 1) Weird __repr__ error: class Creator(Base): def __repr__(self): return '%s' % self.creator class Company(Base): def __repr__(self): return '%s, created by %s' % (self.company, self.creator[0]) c=Company(Company1, mike) session.add(c) c=Company(Company2, mike) True session.add(c) c=Company(Company3, john) session.add(c) c=Company(Company4, mike) True session.add(c) session.query(Company).all() [Traceback (most recent call last): File stdin, line 1, in module File stdin, line 17, in __repr__ However, if I divide the query lines among every add() statement, there is no __repr__ error. c=Company(Company1, mike) session.add(c) session.query(Company).all() [Company1, created by mike] c=Company(Company2, mike) True session.add(c) session.query(Company).all() [Company1, created by mike, Company2, created by mike] c=Company(Company3, john) session.add(c) session.query(Company).all() [Company1, created by mike, Company2, created by mike, Company3, created by john] c=Company(Company4, mike) True session.add(c) session.query(Company).all() [Company1, created by mike, Company2, created by mike, Company3, created by john, Company4, created by mike] 2) Creator.companies only shows the most recently added company: session.query(Company).all() [Company1, created by mike, Company2, created by mike, Company3, created by john, Company4, created by mike] session.query(Creator).all() [mike, john] a=session.query(Creator).first() a[0].companies a.companies Company4, created by mike 3) Weird Company.creator error: session.query(Company).all() [Company1, created by mike, Company2, created by mike, Company3, created by john, Company4, created by mike] session.query(Company.creator).all() [(False,), (False,), (False,), (False,), (True,), (False,), (False,), (True,)] a=session.query(Company).first() a.creator [mike] Anyone have any ideas? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- 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. SQLAlchemy bugs 08-14-13.py -- 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. -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Getting started with SQLAlchemy and NuoDB
Thanks, IMHO it is a useful link. I like both technologies, gonna read it soon. 2013/7/20 Michael Waclawiczek mwaclawic...@nuodb.com: In this article, Luke Bayas from NuoDB talks about SQLAlchemy, a popular object relational mapper(ORM) for Python and how one can utilize it to interact with NuoDB. More at http://www.nuodb.com/techblog/2013/06/13/getting-started-sqlalchemy-nuodb/ -- 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. -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Is definting a commit method good design?
Well, session should be a global variable, but keep in mind that everytime you call commit, you are recreating session. 2013/7/18 Victor Reichert vfr...@gmail.com: HI All, I'm working on my first SQL Alchemy project. I'm thinking I'm going to define a commit method for all the objects I want persist, I'm thinking something like: def commit(self): session = Session() #Session is a global sessionmaker session.add(self) session.commit() session.close() Is that a good practice? Thank you for the advice :) ~Victor -- 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. -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Is definting a commit method good design?
meaning that a single commit() should address all the objects that are related to a particular operation. i commited it to my mind. :) 2013/7/18 Michael Bayer mike...@zzzcomputing.com: On Jul 18, 2013, at 6:52 PM, Victor Reichert vfr...@gmail.com wrote: HI All, I'm working on my first SQL Alchemy project. I'm thinking I'm going to define a commit method for all the objects I want persist, I'm thinking something like: def commit(self): session = Session() #Session is a global sessionmaker session.add(self) session.commit() session.close() Is that a good practice? This is an antipattern. You should keep the means of persistence separate from the objects that you are persisting, and you should be thinking in terms of use cases as far as how to structure transactions, meaning that a single commit() should address all the objects that are related to a particular operation. A bad metaphor might be, suppose we wish to write a collection of sentences to a file. The antipattern approach to me looks like this: class Sentence(object): def __init__(self, text): self.text = text def write(self): handle = open(self.file, a) handle.write(self.text) handle.close() file = myfile.txt for sentence in sentences: sentence.write() While thinking in terms of operations instead of objects looks like this: class Sentence(object): def __init__(self, text): self.text = text handle = open(self.file, w) for sentence in sentences: handle.write(sentence.text) handle.close() besides the obvious efficiency, we don't force each sentence to deal with the target file in isolation of all the other sentences. Dealing with the file's lifespan is outside of the scope of the thing we're putting in the file. By use case, this depends a lot on what kind of application this is. If it's a web application, you want a transaction per request. If it's a short console script, you want a transaction for the life of the script itself. There's a lot more written about this here: http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions -- 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. -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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.
[sqlalchemy]
My question looks simple but I need help to improve it. I have two tables User and Meetings. There is a relation on two keys (User.id == Meetings.author_id). The following code does the job: db.session.query(Meetings, User).filter(Meetings.author_id == User.id).all() Is there any other way to improve it? Thanks. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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]
Thanks, I'll try it out soon and post the result here. 2013/6/21 Michael Bayer mike...@zzzcomputing.com Sure, use a relationship() between Meetings and User and join on that: s.query(Meetings, User).join(Meetings.authors).all() On Jun 21, 2013, at 11:05 AM, Mauricio de Abreu Antunes mauricio.abr...@gmail.com wrote: My question looks simple but I need help to improve it. I have two tables User and Meetings. There is a relation on two keys (User.id == Meetings.author_id). The following code does the job: db.session.query(Meetings, User).filter(Meetings.author_id == User.id).all() Is there any other way to improve it? Thanks. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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. -- 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. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] alembic questions/comments
I think I did not get it. 2013/5/30 Colleen Ross cr...@yapta.com: What would be great would be to have .sql files and .sqli (mako templates with some context provided by the env.py) in addition to .py files. How hard could that be? ;-) UHHH Alembic *doesn't* support this?! Are you kidding me? Fuckit, I'm sticking to sqlalchemy-migrate. -- 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. -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Getting ORM insert/update/delete SQL
http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html I think you can reproduce some stuff like this: http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html#insert-expressions At least this is the way that I generate my SQL string output. I suspect that you can also use delete, select and update expressions Any other recommendation? 2013/5/15 Jarrod Chesney jarrod.ches...@gmail.com: Hi I need to generate update/delete/insert SQL for a proprietary system that has an oracle database. (The system will pass the SQL onto the oracle database but for lengthy reasons, it needs to go through the proprietary system) I'm using SQLAlchemy to query the oracle database directly, i then figure out what changes i need to make to the data. Next i need to either : A) Write my own code to generate the SQL to perform the updates/deletes/inserts (Which i could do fairly easily) or, preferably B) Update the orm object and get the SQL that SQLAlchemy would execute - but the session would not execute the SQL. I'd then expunge all the objects and let them fall out of scope. What i'd like to do is make changes or merge objects into me session and then have it give me the SQL to run. Does anyone know what i can do to get the insert/update/delete SQL that SQLAlchemy would generate but not have SQLAlchemy execute it? -- 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. -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] SA and Firebird RDB$SET/GET_CONTEXT
I know my question is very weird, but Have you ever tried the same SQL string in the firebird console? 2013/4/29 Werner werner.bru...@sfr.fr On 29/04/2013 13:29, Werner wrote: Hi, I am using the above to set the user language and I am sure this worked for me, but it doesn't any more, in the mean time I upgraded to 0.8.1 (was on 0.7.8) and I wonder where things changed. In a simple test like this it still works: session.execute(select rdb$set_context('USER_SESSION'**, 'LANGUAGE_ID', 3)from rdb$database).fetchone() session.commit() result = session.query(db.Drinktype_LV) for item in result: print item.name, item.language.name I get the correct item and language name values back. But when I try this in my application the context goes away and I am not sure if this is SA doing this or Firebird. During the app init (wxPython) I do this: setcont = select rdb$set_context('USER_SESSION'**, 'LANGUAGE_ID', %s) \ from rdb$database % self.loggedInUser.language.id print wx.GetApp().ds x = wx.GetApp().ds.execute(**setcont).fetchone() y = wx.GetApp().ds.execute(**getcont).fetchone() assert y[0] == str(self.loggedInUser.**language.idhttp://self.loggedInUser.language.id ) wx.GetApp().ds.commit() Fine so far, now I try to use it e.g. like this: app = app_base.BaseApp(redirect=**False) print app.ds getcont = select rdb$get_context('USER_SESSION'**, 'LANGUAGE_ID') \ from rdb$database y = app.ds.execute(getcont).**fetchone() assert y[0] == str(app.loggedInUser.language.**idhttp://app.loggedInUser.language.id ) Here the assert fails as the fetch returns None as if the context was not set. the print app.ds both show: sqlalchemy.orm.session.**SessionMaker object at 0x06D41C90 app.ds is created with: maker = sao.sessionmaker(autoflush=**True, autocommit=False, expire_on_commit=True) DBSession = sao.scoped_session(maker) app.ds = DBSession() # just checked again that I do this only in one place of my code Anyone has a hint on where I am going wrong with all this? Werner I added: print wx.GetApp().ds.execute(select current_connection from rdb$database).fetchone() just after the two other print statements and they both show the same connection number on each run. I guess this points the finger at Firebird SQL, no? Werner -- 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+unsubscribe@**googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/**group/sqlalchemy?hl=enhttp://groups.google.com/group/sqlalchemy?hl=en . For more options, visit https://groups.google.com/**groups/opt_outhttps://groups.google.com/groups/opt_out . -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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 sqlalchemy queries a generator?
Read the source: def all(self): Return the results represented by this ``Query`` as a list. This results in an execution of the underlying query. return list(self) it means that this method collects everything it needs and it is yielded by the generator. If you returns the query for a variable, you can perform a next(variable). 2013/4/26 alonn alonis...@gmail.com so not to load too much into memory I should do something like: for i in session.query(someobject).filter(idsomething) print i I'm guessing the answer is no, because of the nature of sql, but I'm not an expert so I'm asking. Thanks for the help! -- 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. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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 sqlalchemy queries a generator?
Query object has a __iter__ descriptor. 2013/4/26 Mauricio de Abreu Antunes mauricio.abr...@gmail.com Read the source: def all(self): Return the results represented by this ``Query`` as a list. This results in an execution of the underlying query. return list(self) it means that this method collects everything it needs and it is yielded by the generator. If you returns the query for a variable, you can perform a next(variable). 2013/4/26 alonn alonis...@gmail.com so not to load too much into memory I should do something like: for i in session.query(someobject).filter(idsomething) print i I'm guessing the answer is no, because of the nature of sql, but I'm not an expert so I'm asking. Thanks for the help! -- 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. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Relationship - session.commit() is not needed?
Everytime I code SQLAlchemy relationships I note that insert operation like this (tutorial) does not need commit() http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#working-with-related-objects When using this bidirectional relationship() is the data automatically commited to the dabase? Probably this is an easy question but I can not get how to perform a rollback in jack.addresses. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Re: Relationship - session.commit() is not needed?
Being more specific I talk about this part: jack = User('jack', 'Jack Bean', 'gjffdd') jack.addresses[] jack.addresses = [... Address(email_address='j...@google.com'),... Address(email_address='j...@yahoo.com')] Are addresses automaticamente added and commited? I know it is commiting jack and jack has addresses, but how do i add jack first (session 1) and right after commiting jack without addresses add a set of addresses and commit them to the database? 2013/4/26 Mauricio de Abreu Antunes mauricio.abr...@gmail.com Everytime I code SQLAlchemy relationships I note that insert operation like this (tutorial) does not need commit() http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#working-with-related-objects When using this bidirectional relationship() is the data automatically commited to the dabase? Probably this is an easy question but I can not get how to perform a rollback in jack.addresses. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Re: Relationship - session.commit() is not needed?
I think I'm answering myself: address = Address(args) # passing jack's id session.add(address) session.commit() 2013/4/27 Mauricio de Abreu Antunes mauricio.abr...@gmail.com Being more specific I talk about this part: jack = User('jack', 'Jack Bean', 'gjffdd') jack.addresses[] jack.addresses = [... Address(email_address='j...@google.com'),... Address(email_address='j...@yahoo.com')] Are addresses automaticamente added and commited? I know it is commiting jack and jack has addresses, but how do i add jack first (session 1) and right after commiting jack without addresses add a set of addresses and commit them to the database? 2013/4/26 Mauricio de Abreu Antunes mauricio.abr...@gmail.com Everytime I code SQLAlchemy relationships I note that insert operation like this (tutorial) does not need commit() http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#working-with-related-objects When using this bidirectional relationship() is the data automatically commited to the dabase? Probably this is an easy question but I can not get how to perform a rollback in jack.addresses. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] How to insert ROW%ROWTYPE?
Nice Job! Do you know The True reason since % is not a escape character? Em quinta-feira, 18 de abril de 2013, Wolfgang Meiners escreveu: Ok, i got it! It is as simple as that: replace tt timetable%ROWTYPE --- === new line inserted with tt timetable%%ROWTYPE; --- === new line inserted Wolfgang -- 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 javascript:;. To post to this group, send email to sqlalchemy@googlegroups.comjavascript:; . Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Session.add doesn't work on Python 3.3.0?
I think you need to commit your session data. Em terça-feira, 16 de abril de 2013, Tim Cooijmans escreveu: I have a strange error using Python 3.3.0 and SQLAlchemy 0.8.0: from sqlalchemy import create_engine, Integer, String, Text, Column, Sequence, DateTime, ForeignKeyfrom sqlalchemy.orm import sessionmaker, relationship, backreffrom sqlalchemy.ext.declarative import declarative_base engine = create_engine(sqlite:///:memory:, echo=True)Session = sessionmaker(bind = engine)Base = declarative_base() class Product(Base): __tablename__ = products id = Column(Integer, primary_key=True) name = Column(String(200)) description = Column(Text) Base.metadata.create_all(engine) session = Session() product = Product() product.id = 1 product.name = Test product.description = Test session.add(product)print(product in session) # Expected true session.commit()print(session.query(Product).all()) # Expected the previous item As commented in code I expected the add method to add the product to the database using an INSERT. But it doesn't nor does it throw an exception. This is the log: 2013-04-16 18:03:14,368 INFO sqlalchemy.engine.base.Engine PRAGMA table_info(products)2013-04-16 18:03:14,369 INFO sqlalchemy.engine.base.Engine ()2013-04-16 18:03:14,371 INFO sqlalchemy.engine.base.Engine CREATE TABLE products ( id INTEGER NOT NULL, name VARCHAR(200), description TEXT, PRIMARY KEY (id)) 2013-04-16 18:03:14,371 INFO sqlalchemy.engine.base.Engine ()2013-04-16 18:03:14,371 INFO sqlalchemy.engine.base.Engine COMMITFalse2013-04-16 18:04 -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Session.add doesn't work on Python 3.3.0?
I tried your same code here I got True. Em terça-feira, 16 de abril de 2013, Michael Bayer escreveu: cant reproduce, running with Python 3.3.0 here against 0.8.0 I get the INSERT: PRAGMA table_info(products) 2013-04-16 16:14:41,019 INFO sqlalchemy.engine.base.Engine () 2013-04-16 16:14:41,019 INFO sqlalchemy.engine.base.Engine CREATE TABLE products ( id INTEGER NOT NULL, name VARCHAR(200), description TEXT, PRIMARY KEY (id) ) 2013-04-16 16:14:41,020 INFO sqlalchemy.engine.base.Engine () 2013-04-16 16:14:41,020 INFO sqlalchemy.engine.base.Engine COMMIT True 2013-04-16 16:14:41,021 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2013-04-16 16:14:41,021 INFO sqlalchemy.engine.base.Engine INSERT INTO products (id, name, description) VALUES (?, ?, ?) 2013-04-16 16:14:41,021 INFO sqlalchemy.engine.base.Engine (1, 'Test', 'Test') 2013-04-16 16:14:41,022 INFO sqlalchemy.engine.base.Engine COMMIT 2013-04-16 16:14:41,022 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2013-04-16 16:14:41,022 INFO sqlalchemy.engine.base.Engine SELECT products.id AS products_id, products.name AS products_name, products.description AS products_description FROM products 2013-04-16 16:14:41,023 INFO sqlalchemy.engine.base.Engine () [__main__.Product object at 0x105c3dbd0] On Apr 16, 2013, at 2:42 PM, Tim Cooijmans timcooijm...@gmail.com wrote: from sqlalchemy import create_engine, Integer, String, Text, Column, Sequence, DateTime, ForeignKeyfrom sqlalchemy.orm import sessionmaker, relationship, backreffrom sqlalchemy.ext.declarative import declarative_base engine = create_engine(sqlite:///:memory:, echo=True)Session = sessionmaker(bind = engine)Base = declarative_base() -- 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 javascript:_e({}, 'cvml', 'sqlalchemy%2bunsubscr...@googlegroups.com');. To post to this group, send email to sqlalchemy@googlegroups.comjavascript:_e({}, 'cvml', '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. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Updating a table using sqlalchemy.sql.expression update
These tips are veeery good! I sometimes get lost about the best way to use the best ORM library in the world. 2013/3/7 Simon King si...@simonking.org.uk This is an unusual way to update an object that you've already retrieved: result = session.query(Executions). \ filter_by(id=execution_id).first() if result.end_date is None: e = update(Executions).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) self.connection.execute(e, execution_id=execution_id, now=datetime.datetime.now()) It would be more natural to write it like this: if result.end_date is None: result.end_date = datetime.datetime.now() session.flush() Also, if id is the primary key on your Executions class, you can write the first line as: result = session.query(Executions).get(execution_id) On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: in the 0.7 series, you can't pass an ORM mapped class as the subject of the core update() construct: e = update(Executions).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) that statement will work as is if you just refer to the Table: e = update(Executions.__table__).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) also note that the indirection between bindparam(foo) and connection.execute(stmt, foo=some value) is not needed; you can embed literal values directly in the statement, and the Core will convert them to bound parameters (just use echo=True to see it in action): e = update(Executions).where(Executions.id==execution_id). \ values(end_date=datetime.datetime.now()) At the ORM level, you can use query.update(): session.query(Executions).filter(Executions.id==execution_id).update({end_date:datetime.now()}, synchronize_session=False) On Mar 6, 2013, at 8:06 PM, Mauricio de Abreu Antunes mauricio.abr...@gmail.com wrote: So, i have read @StackOverflow some tips. There is a lot of people saying they have to make a query on the table and then update it. there is no way to upgrade without performing a query?! On Wednesday, March 6, 2013 6:17:35 PM UTC-3, Mauricio de Abreu Antunes wrote: Hello, I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1. Reading the tutorial, I tried to write my codes like those examples but I had no success working on it. Code is here: https://gist.github.com/mauricioabreu/5103163 Do I need to map the table Executions to execute an update expression on it? Sorry if this is a very noob question. If you need more info about the problem let me know. -- 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. -- 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. -- 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. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Updating a table using sqlalchemy.sql.expression update
I wanna perform a query on process_files and hereafter a update/join like this: SELECT files.id AS files_id, files.name AS files_name, files.directory AS files_directory, files.active AS files_active, files.connection_id AS files_connection_id FROM files JOIN process_files ON files.id = process_files.files_id Actually the piece os select above is a result from: result = session.query(Files).join(process_files) It is ok, but i wanna filter it with process_files.process_id = 1 for example. Reading the docs i could not find the proper way to do this: http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.join Am i reading the wrong part of the docs? 2013/3/7 Mauricio de Abreu Antunes mauricio.abr...@gmail.com These tips are veeery good! I sometimes get lost about the best way to use the best ORM library in the world. 2013/3/7 Simon King si...@simonking.org.uk This is an unusual way to update an object that you've already retrieved: result = session.query(Executions). \ filter_by(id=execution_id).first() if result.end_date is None: e = update(Executions).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) self.connection.execute(e, execution_id=execution_id, now=datetime.datetime.now()) It would be more natural to write it like this: if result.end_date is None: result.end_date = datetime.datetime.now() session.flush() Also, if id is the primary key on your Executions class, you can write the first line as: result = session.query(Executions).get(execution_id) On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: in the 0.7 series, you can't pass an ORM mapped class as the subject of the core update() construct: e = update(Executions).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) that statement will work as is if you just refer to the Table: e = update(Executions.__table__).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) also note that the indirection between bindparam(foo) and connection.execute(stmt, foo=some value) is not needed; you can embed literal values directly in the statement, and the Core will convert them to bound parameters (just use echo=True to see it in action): e = update(Executions).where(Executions.id==execution_id). \ values(end_date=datetime.datetime.now()) At the ORM level, you can use query.update(): session.query(Executions).filter(Executions.id==execution_id).update({end_date:datetime.now()}, synchronize_session=False) On Mar 6, 2013, at 8:06 PM, Mauricio de Abreu Antunes mauricio.abr...@gmail.com wrote: So, i have read @StackOverflow some tips. There is a lot of people saying they have to make a query on the table and then update it. there is no way to upgrade without performing a query?! On Wednesday, March 6, 2013 6:17:35 PM UTC-3, Mauricio de Abreu Antunes wrote: Hello, I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1. Reading the tutorial, I tried to write my codes like those examples but I had no success working on it. Code is here: https://gist.github.com/mauricioabreu/5103163 Do I need to map the table Executions to execute an update expression on it? Sorry if this is a very noob question. If you need more info about the problem let me know. -- 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. -- 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. -- 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. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype
Re: [sqlalchemy] Updating a table using sqlalchemy.sql.expression update
Simon, Here is the table (not sure if the code to create a relationship table is correct): # Many to Many - Process x Files process_files = Table(process_files, Base.metadata, Column(process_id, Integer, ForeignKey(process.id)), Column(files_id, Integer, ForeignKey(files.id)) ) The error: AttributeError: 'Table' object has no attribute 'process_id' Why can not I access process_id? 2013/3/7 Simon King si...@simonking.org.uk The ORM tutorial covers querying with joins: http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#querying-with-joins In this case, you probably want something like this (if ProcessFiles is a class mapped to the process_files table): result = session.query(Files).join(ProcessFiles).filter(ProcessFiles.process_id==1) Hope that helps, Simon On 7 Mar 2013, at 20:35, Mauricio de Abreu Antunes mauricio.abr...@gmail.com wrote: I wanna perform a query on process_files and hereafter a update/join like this: SELECT files.id AS files_id, files.name AS files_name, files.directory AS files_directory, files.active AS files_active, files.connection_id AS files_connection_id FROM files JOIN process_files ON files.id = process_files.files_id Actually the piece os select above is a result from: result = session.query(Files).join(process_files) It is ok, but i wanna filter it with process_files.process_id = 1 for example. Reading the docs i could not find the proper way to do this: http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.join Am i reading the wrong part of the docs? 2013/3/7 Mauricio de Abreu Antunes mauricio.abr...@gmail.com These tips are veeery good! I sometimes get lost about the best way to use the best ORM library in the world. 2013/3/7 Simon King si...@simonking.org.uk This is an unusual way to update an object that you've already retrieved: result = session.query(Executions). \ filter_by(id=execution_id).first() if result.end_date is None: e = update(Executions).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) self.connection.execute(e, execution_id=execution_id, now=datetime.datetime.now()) It would be more natural to write it like this: if result.end_date is None: result.end_date = datetime.datetime.now() session.flush() Also, if id is the primary key on your Executions class, you can write the first line as: result = session.query(Executions).get(execution_id) On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: in the 0.7 series, you can't pass an ORM mapped class as the subject of the core update() construct: e = update(Executions).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) that statement will work as is if you just refer to the Table: e = update(Executions.__table__).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) also note that the indirection between bindparam(foo) and connection.execute(stmt, foo=some value) is not needed; you can embed literal values directly in the statement, and the Core will convert them to bound parameters (just use echo=True to see it in action): e = update(Executions).where(Executions.id==execution_id). \ values(end_date=datetime.datetime.now()) At the ORM level, you can use query.update(): session.query(Executions).filter(Executions.id==execution_id).update({end_date:datetime.now()}, synchronize_session=False) On Mar 6, 2013, at 8:06 PM, Mauricio de Abreu Antunes mauricio.abr...@gmail.com wrote: So, i have read @StackOverflow some tips. There is a lot of people saying they have to make a query on the table and then update it. there is no way to upgrade without performing a query?! On Wednesday, March 6, 2013 6:17:35 PM UTC-3, Mauricio de Abreu Antunes wrote: Hello, I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1. Reading the tutorial, I tried to write my codes like those examples but I had no success working on it. Code is here: https://gist.github.com/mauricioabreu/5103163 Do I need to map the table Executions to execute an update expression on it? Sorry if this is a very noob question. If you need more info about the problem let me know. -- 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] Updating a table using sqlalchemy.sql.expression update
Thanks. I'm gonna read the communication to watch the difference between Core and ORM. I changed that process_files description to Process_Files(Base): #mapping fields It worked! 2013/3/7 Simon King si...@simonking.org.uk SQLAlchemy is broadly separated into 2 parts, core and ORM (which you can see as the left and right-hand sides on the front page of the documentation, http://docs.sqlalchemy.org/en/rel_0_8/) Table objects like the one you have below are part of the Core API. Columns of Table objects are accessible via the c property: process_files.c.process_id The ORM is built on top of the core API. When you do something like: class Files(Base): __tablename__ = 'files' id = sa.Column('id', sa.Integer, primary_key=True) …you are creating a mapped class. You can access the columns of these directly like Files.id. If you ever need access to the underlying Table object, you can get it from Files.__table__. When using things like session.query(), you can often use tables or mapped classes interchangeably. Hope that helps, Simon On 7 Mar 2013, at 20:57, Mauricio de Abreu Antunes mauricio.abr...@gmail.com wrote: Simon, Here is the table (not sure if the code to create a relationship table is correct): # Many to Many - Process x Files process_files = Table(process_files, Base.metadata, Column(process_id, Integer, ForeignKey(process.id)), Column(files_id, Integer, ForeignKey(files.id)) ) The error: AttributeError: 'Table' object has no attribute 'process_id' Why can not I access process_id? 2013/3/7 Simon King si...@simonking.org.uk The ORM tutorial covers querying with joins: http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#querying-with-joins In this case, you probably want something like this (if ProcessFiles is a class mapped to the process_files table): result = session.query(Files).join(ProcessFiles).filter(ProcessFiles.process_id==1) Hope that helps, Simon On 7 Mar 2013, at 20:35, Mauricio de Abreu Antunes mauricio.abr...@gmail.com wrote: I wanna perform a query on process_files and hereafter a update/join like this: SELECT files.id AS files_id, files.name AS files_name, files.directory AS files_directory, files.active AS files_active, files.connection_id AS files_connection_id FROM files JOIN process_files ON files.id = process_files.files_id Actually the piece os select above is a result from: result = session.query(Files).join(process_files) It is ok, but i wanna filter it with process_files.process_id = 1 for example. Reading the docs i could not find the proper way to do this: http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.join Am i reading the wrong part of the docs? 2013/3/7 Mauricio de Abreu Antunes mauricio.abr...@gmail.com These tips are veeery good! I sometimes get lost about the best way to use the best ORM library in the world. 2013/3/7 Simon King si...@simonking.org.uk This is an unusual way to update an object that you've already retrieved: result = session.query(Executions). \ filter_by(id=execution_id).first() if result.end_date is None: e = update(Executions).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) self.connection.execute(e, execution_id=execution_id, now=datetime.datetime.now()) It would be more natural to write it like this: if result.end_date is None: result.end_date = datetime.datetime.now() session.flush() Also, if id is the primary key on your Executions class, you can write the first line as: result = session.query(Executions).get(execution_id) On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: in the 0.7 series, you can't pass an ORM mapped class as the subject of the core update() construct: e = update(Executions).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) that statement will work as is if you just refer to the Table: e = update(Executions.__table__).where(Executions.id==bindparam(execution_id)). \ values(end_date=bindparam(now)) also note that the indirection between bindparam(foo) and connection.execute(stmt, foo=some value) is not needed; you can embed literal values directly in the statement, and the Core will convert them to bound parameters (just use echo=True to see it in action): e = update(Executions).where(Executions.id==execution_id). \ values(end_date=datetime.datetime.now()) At the ORM level, you can use query.update(): session.query(Executions).filter(Executions.id==execution_id).update({end_date:datetime.now
[sqlalchemy] Updating a table using sqlalchemy.sql.expression update
Hello, I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1. Reading the tutorial, I tried to write my codes like those examples but I had no success working on it. Code is here: https://gist.github.com/mauricioabreu/5103163 Do I need to map the table Executions to execute an update expression on it? Sorry if this is a very noob question. If you need more info about the problem let me know. -- 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] Re: Updating a table using sqlalchemy.sql.expression update
So, i have read @StackOverflow some tips. There is a lot of people saying they have to make a query on the table and then update it. there is no way to upgrade without performing a query?! On Wednesday, March 6, 2013 6:17:35 PM UTC-3, Mauricio de Abreu Antunes wrote: Hello, I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1. Reading the tutorial, I tried to write my codes like those examples but I had no success working on it. Code is here: https://gist.github.com/mauricioabreu/5103163 Do I need to map the table Executions to execute an update expression on it? Sorry if this is a very noob question. If you need more info about the problem let me know. -- 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] Storing and Retrieving BLOB in SqlAlchemy
Well, I think you need to import BLOB from dialects like: from sqlalchemy.dialects.oracle import BLOB in your Model you can describe the field type following it: MyBLOBColumn = Column(BLOB) Actually, IMHO i don't think saving large contents (binary from binary files) as pure text in the database is the best option. 2013/2/26 dalia dalia@gmail.com Hi, My intention is - to store .xls, .doc and .pdf files (with images in them) to store in a Oracle database and retrieve them. I'm using SQLAlchemy declarative code. The problem is, the data gets stored in the database but when I try to fetch the data back, it comes out as 0KB. I'm explaining the code and actions below - The model - class ReportFiles(Base): __tablename__ = 'report_files' id = Column(Integer, primary_key=True) report_file = Column(BLOB) file_name = Column(Unicode(50)) Storing in database - content = cStringIO.StringIO() def store_in_db(): session = meta.Session() self._get_file(content) ### This is a function which generates the file (.xls / .doc / .pdf) in the content contentstr = content.getvalue() tbl = model.ReportFiles() tbl.file_name = test file tbl.report_file = contentstr session.add(tbl) session.commit() After the store, if I query the database, I get this result - not sure if it has stored alright - select file_name, length(report_file) from report_files; Name | Length -- test file | 5632 select file_name, report_file from report_files; Name | file -- test file | (BLOB) This doesn't look right because instead of the file, it just shows (BLOB) Retrieving from database - now I want the file to be downloaded as an excel (.xls) file def _get_report(self): tbl = model.ReportFiles file = meta.Session.query(tbl).get(id=1) contentstr = file.report_file response.content_type = 'application/vnd.ms-excel; charset=utf-8' response.headers['Content-disposition'] = 'attachment; filename = %s' %file.file_name response.content_length = len(contentstr) print Length = %s %len(contentstr) return contentstr This downloads the .xls file fine. The print statement prints the file size as 5632. The download dialogue says the file size is 5.5KB. But when I open the file, it says 'file type not in right format'. When I open it in notepad, it is a 0 KB file. When I list the file in the directory, it is a 0KB file. Can somebody please tell me what is wrong here? Looks like the file doesn't get stored properly? Or am I missing something obvious? Or do I need to handle BLOB types differently? Many Thanks. -- 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. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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] Storing and Retrieving BLOB in SqlAlchemy
Well, I would store the pshysical file in a system folder. After this I would save the path to the file in a table. Then you could manage your files via sqlalchemy, listing all files and working with them properly. 2013/2/26 dalia dalia@gmail.com Hi Mauricio, Thanks for the reply. But importing BLOB specifically did not solve the problem. It is the same. In the INSERT statement, it looks like it is storing the file in Binary, but then when I query the database, it just shows (BLOB) and not the data. My requirement is to store .xls, .pdf and .doc files which contain images inside. The maximum file size can go up to 500KB. The file I'm testing with is a .xls file (size 5.5KB). That's why I chose to store them in a BLOB type. What do you suggest? On Tuesday, February 26, 2013 2:55:36 PM UTC, Mauricio de Abreu Antunes wrote: Well, I think you need to import BLOB from dialects like: from sqlalchemy.dialects.oracle import BLOB in your Model you can describe the field type following it: MyBLOBColumn = Column(BLOB) Actually, IMHO i don't think saving large contents (binary from binary files) as pure text in the database is the best option. 2013/2/26 dalia dali...@gmail.com Hi, My intention is - to store .xls, .doc and .pdf files (with images in them) to store in a Oracle database and retrieve them. I'm using SQLAlchemy declarative code. The problem is, the data gets stored in the database but when I try to fetch the data back, it comes out as 0KB. I'm explaining the code and actions below - The model - class ReportFiles(Base): __tablename__ = 'report_files' id = Column(Integer, primary_key=True) report_file = Column(BLOB) file_name = Column(Unicode(50)) Storing in database - content = cStringIO.StringIO() def store_in_db(): session = meta.Session() self._get_file(content) ### This is a function which generates the file (.xls / .doc / .pdf) in the content contentstr = content.getvalue() tbl = model.ReportFiles() tbl.file_name = test file tbl.report_file = contentstr session.add(tbl) session.commit() After the store, if I query the database, I get this result - not sure if it has stored alright - select file_name, length(report_file) from report_files; Name | Length -- test file | 5632 select file_name, report_file from report_files; Name | file -- test file | (BLOB) This doesn't look right because instead of the file, it just shows (BLOB) Retrieving from database - now I want the file to be downloaded as an excel (.xls) file def _get_report(self): tbl = model.ReportFiles file = meta.Session.query(tbl).get(**id=1) contentstr = file.report_file response.content_type = 'application/vnd.ms-excel; charset=utf-8' response.headers['Content-**disposition'] = 'attachment; filename = %s' %file.file_name response.content_length = len(contentstr) print Length = %s %len(contentstr) return contentstr This downloads the .xls file fine. The print statement prints the file size as 5632. The download dialogue says the file size is 5.5KB. But when I open the file, it says 'file type not in right format'. When I open it in notepad, it is a 0 KB file. When I list the file in the directory, it is a 0KB file. Can somebody please tell me what is wrong here? Looks like the file doesn't get stored properly? Or am I missing something obvious? Or do I need to handle BLOB types differently? Many Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@**googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/**group/sqlalchemy?hl=enhttp://groups.google.com/group/sqlalchemy?hl=en . For more options, visit https://groups.google.com/**groups/opt_outhttps://groups.google.com/groups/opt_out . -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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. -- *Mauricio de Abreu Antunes* Mobile: (51)930-74-525 Skype: mauricio.abreua -- 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
[sqlalchemy] Sequence - Oracle
How to use Sequence with SQLAlchemy? Oracle does not support autoincrement, so I tried to use Sequence (sqlalchemy.schema) like: id = Column(Integer, Sequence(seq_customer_id), primary_key=True) but it is not working. If you need more info to help me tell me then i back here to post it. Thanks in advance. -- 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] Re: Sequence - Oracle
Thanks Michael! I'm getting this error FlushError: Instance has NULL identity. But the strange thing it is not happening every time my insert runs. It looks like the sequence is generating the value after the commit, so ID is null. On Tuesday, February 19, 2013 12:42:04 AM UTC-3, Mauricio de Abreu Antunes wrote: How to use Sequence with SQLAlchemy? Oracle does not support autoincrement, so I tried to use Sequence (sqlalchemy.schema) like: id = Column(Integer, Sequence(seq_customer_id), primary_key=True) but it is not working. If you need more info to help me tell me then i back here to post it. Thanks in advance. -- 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.