SQLObject 0.11.6

Hello!

I'm pleased to announce version 0.11.6, a minor bugfix release of 0.11 branch
of SQLObject.

What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).

Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

SQLObject 0.12.4

Hello!

I'm pleased to announce version 0.12.4, a minor bugfix release of branch 0.12
of SQLObject.

What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).

Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

An idea regarding deleteMany

------------------------------------------------------------------------------

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: An idea regarding deleteMany

On Thu, May 06, 2010 at 08:46:55PM +0300, Imri Goldberg wrote:
> In the last few days I've started using helper "smart" deleteMany functions
> which given a query, call deleteMany on all affected classes.
> 
> So for example, if I have a many to many relationship between Foo and Bar,
> and the join-table is called FooBar, smart delete many will do something
> like:
> Foo.deleteMany(foo_query)
> FooBar.deleteMany((FooBar.q.foo == Foo.q.id) & foo_query)
> 
> I was thinking that this could be generalized to a generic smartDeleteMany
> function (or whatever name you choose), which automatically does all the
> cascade actions, without specifying it manualy for each class.
> 
> What do you think? Is this useful?

   Useful, certainly.

Oleg.
--

-- 
     Oleg Broytman            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------

Re: An idea regarding deleteMany

------------------------------------------------------------------------------

_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: An idea regarding deleteMany

On Thu, May 06, 2010 at 09:36:54PM +0300, Imri Goldberg wrote:
> Working on it now I'm running into some problems, I will write more once I
> have this worked out.

   Ok, we're waiting...

Oleg.
--

-- 
     Oleg Broytman            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------

left join with subquery and WHERE

Hi!

I wrote a small program to test this problem.
I have 3 tables:

CREATE TABLE pricegroups (id integer primary key);
CREATE TABLE products (id integer primary key, name text);
CREATE TABLE products_pricegroups (id integer primary key, product_id
integer, pricegroup_id integer, percent integer);

I would like to perform the following query:

select * from pricegroups 
left join (select * from products_pricegroups
where products_pricegroups.product_id=1) as a 
on (pricegroups.id=a.pricegroup_id)

How can I accomplish it in mysql?
This is where i am now:

#!/usr/bin/env python

import os
import sqlobject

class Product(sqlobject.SQLObject):

	class sqlmeta:
		table = 'products'

	name = sqlobject.StringCol()

class PriceGroup(sqlobject.SQLObject):

	class sqlmeta:
		table = 'pricegroups'

	percents = sqlobject.MultipleJoin('ProductPriceGroup', joinColumn =
'pricegroup_id')
	
class ProductPriceGroup(sqlobject.SQLObject):

	class sqlmeta:
		table = 'products_pricegroups'

	productId = sqlobject.ForeignKey('Product', dbName = 'product_id')
	pricegroupId = sqlobject.ForeignKey('PriceGroup', dbName =
'pricegroup_id')
	percent = sqlobject.IntCol()

if __name__ == '__main__':

	builder = sqlobject.sqlite.builder()
	connection = builder('join.db')
	connection.debug = 1
	sqlobject.sqlhub.processConnection = connection

	pgl = list(PriceGroup.select(
		join = sqlobject.sqlbuilder.LEFTJOINOn(
			PriceGroup, ProductPriceGroup,
			PriceGroup.q.id == ProductPriceGroup.q.pricegroupId,
			),
		))
	
	for pg in pgl:
		print pg

The next step is to apply the WHERE clause, but I have no idea how to do
it...

Thanks for any help:
Imre Horvath

------------------------------------------------------------------------------

Re: left join with subquery and WHERE

On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> select * from pricegroups 
> left join (select * from products_pricegroups
> where products_pricegroups.product_id=1) as a 
> on (pricegroups.id=a.pricegroup_id)

   Currently it's impossible to do with SQLObject as it assumes both sides
of a JOIN are table names, not a subselect. That could be fixed.

Oleg.
--

-- 
     Oleg Broytman            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------

Re: left join with subquery and WHERE

On Fri, May 14, 2010 at 06:22:05PM +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> > select * from pricegroups 
> > left join (select * from products_pricegroups
> > where products_pricegroups.product_id=1) as a 
> > on (pricegroups.id=a.pricegroup_id)

   Does the query work at all in MySQL?

Oleg.
--

-- 
     Oleg Broytman            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------

Re: left join with subquery and WHERE

On Fri, 2010-05-14 at 18:24 +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 06:22:05PM +0400, Oleg Broytman wrote:
> > On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> > > select * from pricegroups 
> > > left join (select * from products_pricegroups
> > > where products_pricegroups.product_id=1) as a 
> > > on (pricegroups.id=a.pricegroup_id)
> 
>    Does the query work at all in MySQL?
> 
> Oleg.

It's in sqlite, but it works.
It's also works in postgres.
The goal is to select all pricegroups for a product, even if it's not
set for the given product.
this is my test db:

sqlite> .schema
CREATE TABLE pricegroups (id integer primary key);
CREATE TABLE products (id integer primary key, name text);
CREATE TABLE products_pricegroups (id integer primary key, product_id
integer, pricegroup_id integer, percent integer);

sqlite> select * from products;
1|a
2|b
3|v

sqlite> select * from pricegroups;
1
2
3
4
5
6
7
8

sqlite> select * from products_pricegroups;
1|1|1|10
2|1|2|20
3|2|4|20

sqlite> select * from pricegroups 
   ...> left join (select * from products_pricegroups
   ...> where products_pricegroups.product_id=1) as a 
   ...> on (pricegroups.id=a.pricegroup_id);
1|1|1|1|10
2|2|1|2|20
3||||
4||||
5||||
6||||
7||||
8||||

sqlite> select * from pricegroups 
   ...> left join (select * from products_pricegroups
   ...> where products_pricegroups.product_id=2) as a 
   ...> on (pricegroups.id=a.pricegroup_id);
1||||
2||||
3||||
4|3|2|4|20
5||||
6||||
7||||
8||||

I hope i was clear

Regards:
Imre Horvath

------------------------------------------------------------------------------