[sqlalchemy] Re: Joining three tables - Selecting column from two different tables
Hi, Thanks. Worked like a charm. Also thanks for SqlAlchemy. A refreshing change for someone from java background. I am using this with Jython. Thanks for the Jython support also. Steve On Jan 18, 8:54 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 18, 2011, at 9:11 AM, Steve wrote: Hi all, Newbie here. I just want to execute the following sql using SqlAlchemy . But getting various errors. select ssf.factor,ssf.displayname,pmw.weight from probability_models_weights pmw inner join probability_models pm on pm.id = pmw.model_id inner join success_factors ssf on ssf.id = pmw.factor_id where pm.id = 6 I want to execute this using session. I am using declarative base with the following auto loaded classes. class SucessFactors(WBase): __tablename__ = success_factors __table_args__ = {'autoload':True} class ProbabilityModels(WBase): __tablename__ = probability_models __table_args__ = {'autoload':True} class ProbabilityModelsWeights(WBase): __tablename__ = probability_models_weights __table_args__ = {'autoload':True} I tried the following but it didn't work. session.query(SucessFactors.factor,SucessFactors.displayname,ProbabilityModelsWeights.weight). \ join(ProbabilityModelsWeights,ProbabilityModels, ProbabilityModelsWeights.model_id == ProbabilityModels.id).\ join(ProbabilityModelsWeights,SucessFactors, ProbabilityModelsWeights.factor_id == SucessFactors.id).\ filter(ProbabilityModels.id == model_id).\ all() query.join() is a one-argument form (it will accept two arguments in 0.7, but thats not released yet), so here you want to be saying query(...).select_from(ProbabiliyModelsWeights).join((ProbabiltityModels, ProbabiltiyModelsWeights.model_id==ProbabilityModels.id)). the select_from() accepting a mapped class is a helper that was introudced in 0.6.5. Also note the tuple form inside of join(), i.e. join((target, onclause)) (you won't need that in 0.7). Documented athttp://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins. Thanks in advance. Steve. -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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: scalar association_proxy
Maybe you can squelch the exception by using a synonym with the descriptor argument to map to a fake property. Oooh, I had not come across this construct before - looks as if it might do exactly what I want. I might even be able to make a decorator that wraps this all up nicely. Thank you very much; I shall let you know how I get on! -- 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] select distinct on a relation to an entity with composite primary key
hi list, I have the following (elixir) definitions class Invoice(Entity): user_name = Field(Unicode(255)) item = ManyToOne(Item) class Item(Entity): item_id = Field(Integer, primary_key=True) service_id = Field(Unicode(255), primary_key=True) item_class = Field(Unicode(255), primary_key=True) I wish to select invoices that have distinct items (Pdb) Session.query(Invoice.item.distinct()) *** RuntimeError: maximum recursion depth exceeded while calling a Python object this works (Pdb) Session.query(Invoice.item_item_id.distinct()) sqlalchemy.orm.query.Query object at 0x988e7ec but doesn't lead to the expected result here is the invoice's table description CREATE TABLE invoice ( id INTEGER NOT NULL, user_name VARCHAR(255), item_item_id INTEGER, item_service_id VARCHAR(255), item_item_class VARCHAR(255), PRIMARY KEY (id), CONSTRAINT invoice_item_item_id_item_service_id_item_item_class_fk FOREIGN KEY(item_item_id, item_service_id, item_item_class) REFERENCES item (item_id, service_id, item_class) ) How can I achieve my query ? regards NIL -- 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.
Re: [sqlalchemy] Re: rollback not working
On Jan 20, 2011, at 2:04 AM, bool wrote: If I dont use autocommit:True option, it seems the driver will be in a chained transaction mode and results in every single statement (including selects) being run in a new transaction. This is not desirable either. Is there a way out ? The DBAPI doesn't do that, since DBAPI calls for a single connection to be open in a transaction immediately, the state of which is only ended by calling rollback or commit on that connection. if you're using SQLalchemy using connectionless execution, i.e. table.insert().execute(), then yes each execute() is in its own transaction, as it should be since no transactional boundary has been declared. This style of usage is not appropriate for a large volume of operations. Usually you create a transaction for a series of operations, which is automatic when using the ORM with a Session object, so that there's a reasonable boundary of transactions. -- 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.
Re: [sqlalchemy] select distinct on a relation to an entity with composite primary key
its a little unfortunate that Invoice.item.distinct() is recursion overflowing like that, but in general if you want to select distinct Item objects you'd be saying query(Item).distinct(). not sure I understand what invoices that have distinct items means. I understand invoices that have items. If Invoice inv1 and inv2 both reference item im1, are you saying you don't want either of them ? Only invoice inv3, which references im2, and nobody else references im2 ? thats a fairly tricky query, probably using EXISTS: from sqlalchemy.orm import aliased from sqlalchemy import and_ inv = aliased(Invoice) query(Invoice).filter(~Invoice.any(and_(Invoice.item_id==inv.item_id, Invoice.id!=inv.id))) On Jan 20, 2011, at 6:33 AM, NiL wrote: hi list, I have the following (elixir) definitions class Invoice(Entity): user_name = Field(Unicode(255)) item = ManyToOne(Item) class Item(Entity): item_id = Field(Integer, primary_key=True) service_id = Field(Unicode(255), primary_key=True) item_class = Field(Unicode(255), primary_key=True) I wish to select invoices that have distinct items (Pdb) Session.query(Invoice.item.distinct()) *** RuntimeError: maximum recursion depth exceeded while calling a Python object this works (Pdb) Session.query(Invoice.item_item_id.distinct()) sqlalchemy.orm.query.Query object at 0x988e7ec but doesn't lead to the expected result here is the invoice's table description CREATE TABLE invoice ( id INTEGER NOT NULL, user_name VARCHAR(255), item_item_id INTEGER, item_service_id VARCHAR(255), item_item_class VARCHAR(255), PRIMARY KEY (id), CONSTRAINT invoice_item_item_id_item_service_id_item_item_class_fk FOREIGN KEY(item_item_id, item_service_id, item_item_class) REFERENCES item (item_id, service_id, item_class) ) How can I achieve my query ? regards NIL -- 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. -- 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: select distinct on a relation to an entity with composite primary key
Hi Michael, thank you very much for your prompt answer. What I want to achieve is, counting the number of distinct items, grouped by user_name Given Item1: item_id = 1 service_id = 'test' item_class = 'dummy' Item2: item_id = 2 service_id = 'other' item_class = 'dummy' Invoice1: id = 1 user_name = 'lorem' item = Item1 Invoice2: id = 2 user_name = 'lorem' item = Item1 Invoice3: id = 3 user_name = 'ipsum' item = Item1 Invoice4: id = 4 user_name = 'ipsum' item = Item2 the expected result is: user: lorem, distinct item count : 1 user: ipsum, distinct item count : 2 -- 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.
Re: [sqlalchemy] Re: select distinct on a relation to an entity with composite primary key
from sqlalchemy import func, distinct query(Invoice.user_name, func.count(distinct(Item.id))).join(Invoice.items).group_by(Invoice.user_name) On Jan 20, 2011, at 9:51 AM, NiL wrote: Hi Michael, thank you very much for your prompt answer. What I want to achieve is, counting the number of distinct items, grouped by user_name Given Item1: item_id = 1 service_id = 'test' item_class = 'dummy' Item2: item_id = 2 service_id = 'other' item_class = 'dummy' Invoice1: id = 1 user_name = 'lorem' item = Item1 Invoice2: id = 2 user_name = 'lorem' item = Item1 Invoice3: id = 3 user_name = 'ipsum' item = Item1 Invoice4: id = 4 user_name = 'ipsum' item = Item2 the expected result is: user: lorem, distinct item count : 1 user: ipsum, distinct item count : 2 -- 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. -- 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: select distinct on a relation to an entity with composite primary key
thanks again but the unicity of Item is guaranteed by the triplet of PK I can't just discriminate the distinct() based on the item_id only (it is not unique by itself in my set of data) -- 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.
Re: [sqlalchemy] Re: select distinct on a relation to an entity with composite primary key
session.query(Invoice.user_name, Item).join(Invoice.item).distinct().from_self(Invoice.user_name, func.count(1)).group_by(Invoice.user_name) On Jan 20, 2011, at 11:45 AM, NiL wrote: thanks again but the unicity of Item is guaranteed by the triplet of PK I can't just discriminate the distinct() based on the item_id only (it is not unique by itself in my set of data) -- 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. -- 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] SQLAlchemy case insensitive like for unicode word
Hello! I have gtk application with sqlite db, contain russian words. My model code class Patient(Base): lastname = Column(Unicode) /code Search operation code patients = self.session.query(Patient) lastname = unicode(self.lastname_entry.get_text()) if lastname: patients = patients.filter(Patient.lastname.like(u%%%s%% % lastname)) /code It perfectly work if i search as 'Ivanov', but didn't return results if i search 'ivanov'. Note, i search russian words, not english. In SQLite manager (firefox plugin) search query code SELECT * FROM patients WHERE lastname LIKE %ivanov% /code If i query db and look at value: code In [28]: p.lastname Out[28]: u'\u0413\u0430\u043f\u0447\u0443\u043a' /code and than check it in query, generated by SQLAlchemy code In [29]: patients.filter(Patient.lastname.ilike(u%%%s%% % l.decode('utf-8'))).count() 2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250 SELECT count(1) AS count_1 FROM patients WHERE lower(patients.lastname) LIKE lower(?) 2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250 (u'%\u0433\u0430\u043f\u0447\u0443\u043a%',) Out[29]: 0 /code It is looked like lower function in SQLAlchemy didn't understood cyrillic ... Thanks! PS: Sorry for my english) -- 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: I need a final push
Hi All, After some additional peeking around I decided to do a test with SQLAlchemy alone. I took the tutorial fr0m the book Essential SQLAlchemy as my guide. This is what I got working. # testing the func following the tutorial in the book Essential SQLALchemy #pg.25 from sqlalchemy import * from datetime import datetime metadata=MetaData('sqlite:///tutorial.sqlite') #metadata.bind.echo=True person_table=Table( 'person', metadata, Column('id', Integer, primary_key=True), Column('birthdate', DateTime, default=datetime.now)) metadata.create_all() # stmt=person_table.insert() #stmt.execute(birthdate=datetime(2000, 4, 4, 0, 0)) #stmt.execute(birthdate=datetime(2000, 3, 3, 0, 0)) #stmt.execute(birthdate=datetime(2000, 2, 2, 0, 0)) #stmt.execute(birthdate=datetime(2000, 1, 1, 0, 0)) #pg 28 Mapping from sqlalchemy.orm import * class Person(object):pass mapper(Person, person_table) Session= sessionmaker() session=Session() query =session.query(Person) def monthfrom(date): print(date) #i do a split because I get seconds as 00.0 if date != None: datesplit=date.split(' ')[0] a=datetime.strptime(datesplit, '%Y-%m-%d').month else: a=1 return a metadata.bind.connect().connection.connection.create_function(monthfrom, 1, monthfrom) print('monthfrom in:') pp=query.order_by(func.monthfrom(Person.birthdate)).all() print('result:') for p in pp: print (p.birthdate) For the first run you have to uncomment the 4 lines with the insert execution to fill your empty db. Put the comments back on or your db will fill up Next task will be to get it transplanted to my app. There is still one issue though. the function def monthfrom get the date with the seconds as 00.00 But the print(p.birthdate) shows the seconds as 00 See: monthfrom in: 2000-04-04 00:00:00.00 2000-03-03 00:00:00.00 2000-02-02 00:00:00.00 2000-01-01 00:00:00.00 result: 2000-01-01 00:00:00 2000-02-02 00:00:00 2000-03-03 00:00:00 2000-04-04 00:00:00 That is why the def monthfrom does a .split Question is this a bug? Thanks Frans. -- 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.
Re: [sqlalchemy] Re: sqlalchemy rocks my socks off!
+1 2011/1/16 Jan Müller m...@dfi-net.de: +1 On Jan 15, 9:58 am, Eric Ongerth ericonge...@gmail.com wrote: +1 On Jan 13, 5:08 pm, rdlowrey rdlow...@gmail.com wrote: To Michael Bayer: sqlalchemy simplifies my life every day and makes me vastly more productive! Many thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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.
Re: [sqlalchemy] SQLAlchemy case insensitive like for unicode word
That's SQLite's lower() function. If you'd like to use Python's lower() function, you should call lower() on the string and use column.like(mystring.lower()). But that won't do case-insensitive comparison since you need to call lower() on the database column in the statement. So you really need to first get this statement to work with pysqlite, to figure out what collation issues may exist with sqlite3 or encoding issues with pysqlite: import sqlite3 connection = sqlite3.connect(/path/to/your/db) cursor = connection.cursor() cursor.execute(SELECT * FROM patients WHERE lower(lastname) LIKE lower(?), ['Ivanov']) print cursor.fetchall() SQLAlchemy doesn't look at the contents of your string at all with pysqlite, its a pass through. On Jan 20, 2011, at 4:15 PM, proft wrote: Hello! I have gtk application with sqlite db, contain russian words. My model code class Patient(Base): lastname = Column(Unicode) /code Search operation code patients = self.session.query(Patient) lastname = unicode(self.lastname_entry.get_text()) if lastname: patients = patients.filter(Patient.lastname.like(u%%%s%% % lastname)) /code It perfectly work if i search as 'Ivanov', but didn't return results if i search 'ivanov'. Note, i search russian words, not english. In SQLite manager (firefox plugin) search query code SELECT * FROM patients WHERE lastname LIKE %ivanov% /code If i query db and look at value: code In [28]: p.lastname Out[28]: u'\u0413\u0430\u043f\u0447\u0443\u043a' /code and than check it in query, generated by SQLAlchemy code In [29]: patients.filter(Patient.lastname.ilike(u%%%s%% % l.decode('utf-8'))).count() 2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250 SELECT count(1) AS count_1 FROM patients WHERE lower(patients.lastname) LIKE lower(?) 2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250 (u'%\u0433\u0430\u043f\u0447\u0443\u043a%',) Out[29]: 0 /code It is looked like lower function in SQLAlchemy didn't understood cyrillic ... Thanks! PS: Sorry for my english) -- 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. -- 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.
Re: [sqlalchemy] Re: I need a final push
On Jan 20, 2011, at 4:53 PM, F.A.Pinkse wrote: Hi All, After some additional peeking around I decided to do a test with SQLAlchemy alone. I took the tutorial fr0m the book Essential SQLAlchemy as my guide. very very old outdated book. If you want a current book, go to http://www.sqlalchemy.org/docs/ , download the PDF and print it out. I find reading HTML online much easier though (and use the search, it works). This is what I got working. def monthfrom(date): print(date) #i do a split because I get seconds as 00.0 if date != None: datesplit=date.split(' ')[0] a=datetime.strptime(datesplit, '%Y-%m-%d').month else: a=1 return a metadata.bind.connect().connection.connection.create_function(monthfrom, 1, monthfrom) wow, thats in the book ? that is not at all how that should be done. Though I will grant this is a recipe that isn't in our main docs (I've never seen SQLite python plugin functions used before), probably worth adding to the SQLite docs. Anyway, this is how you should install your function: from sqlalchemy.interfaces import PoolListener class MyListener(PoolListener): def connect(self, dbapi_con, con_record): dbapi_con.create_function(monthfrom, 1, monthfrom) engine = create_engine('sqlite:///path_to_my_db', listeners=[MyListener()]) reference : http://www.sqlalchemy.org/docs/core/interfaces.html#connection-pool-events SQLite doesn't have a date type. The SQLAlchemy DateTime type stores the value as a string. This isn't the only way to go, you can also store dates as epochs, that is integers, which on SQLite may be more amenable to date arithmetic on the server, but in any case you're just dealing with extracting here, no big deal. The SQLite DateTime type in SQLA stores the value using the format: %04d-%02d-%02d %02d:%02d:%02d.%06d % (value.year, value.month, value.day, value.hour, value.minute, value.second, value.microsecond) so you're seeing the microsecond value there. you need to deal with that too. Surprisingly the docs seem a little light on this too so ticket 2029 is added to fill this in. # testing the func following the tutorial in the book Essential SQLALchemy #pg.25 from sqlalchemy import * from datetime import datetime metadata=MetaData('sqlite:///tutorial.sqlite') #metadata.bind.echo=True person_table=Table( 'person', metadata, Column('id', Integer, primary_key=True), Column('birthdate', DateTime, default=datetime.now)) metadata.create_all() # stmt=person_table.insert() #stmt.execute(birthdate=datetime(2000, 4, 4, 0, 0)) #stmt.execute(birthdate=datetime(2000, 3, 3, 0, 0)) #stmt.execute(birthdate=datetime(2000, 2, 2, 0, 0)) #stmt.execute(birthdate=datetime(2000, 1, 1, 0, 0)) #pg 28 Mapping from sqlalchemy.orm import * class Person(object):pass mapper(Person, person_table) Session= sessionmaker() session=Session() query =session.query(Person) def monthfrom(date): print(date) #i do a split because I get seconds as 00.0 if date != None: datesplit=date.split(' ')[0] a=datetime.strptime(datesplit, '%Y-%m-%d').month else: a=1 return a metadata.bind.connect().connection.connection.create_function(monthfrom, 1, monthfrom) print('monthfrom in:') pp=query.order_by(func.monthfrom(Person.birthdate)).all() print('result:') for p in pp: print (p.birthdate) For the first run you have to uncomment the 4 lines with the insert execution to fill your empty db. Put the comments back on or your db will fill up Next task will be to get it transplanted to my app. There is still one issue though. the function def monthfrom get the date with the seconds as 00.00 But the print(p.birthdate) shows the seconds as 00 See: monthfrom in: 2000-04-04 00:00:00.00 2000-03-03 00:00:00.00 2000-02-02 00:00:00.00 2000-01-01 00:00:00.00 result: 2000-01-01 00:00:00 2000-02-02 00:00:00 2000-03-03 00:00:00 2000-04-04 00:00:00 That is why the def monthfrom does a .split Question is this a bug? Thanks Frans. -- 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. -- 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.