[ZODB-Dev] query the ZODB database

[Norman Khine]

Hello,
having come from RDBMS background I would like to find out how to query the
ZODB database.

So, for an RDBMS application I have five related tables, something like
"We have an employees table, code-related with departments and locations,
which is also related to countries. The sales table has references to the
employees and categories tables. Only employees belonging to the Sales
department can be involved in sales, but they can sell anything, from
software to services to education."

I would like to write a python script which would generate the following
equivalent sql code.

SELECT name, gender, location FROM locations INNER JOIN employees USING
(loc_code);

this will produce a table as:

name 		gender 	location
Luigi 	M 		Roma
Mario 	M 		Roma
Fred 		M 		Milano
Cinzia 	F 		Cagliari
....


what I want is in using a python script be able to get is a row for each
town, with a column for each gender and a total column, an sql would be:
SELECT location, SUM(IF(gender='M',1,0)) AS M,
SUM(IF(gender='F',1,0)) AS F, COUNT(*) AS total
GROUP by location;

this gives:

location 	M F total
Boston 	1 2 3
Cagliari 	1 2 3
London 	1 1 2
Manchester 	1 1 2

And here is a bit more complex sql

SELECT location
, SUM(IF(dept = "Development", salary,0)) AS `Development`
, SUM(IF(dept = "Personnel", salary,0)) AS `Personnel`
, SUM(IF(dept = "Research", salary,0)) AS `Research`
, SUM(IF(dept = "Sales", salary,0)) AS `Sales`
, SUM(IF(dept = "Training", salary,0)) AS `Training`
, SUM(salary) AS total
FROM locations INNER JOIN employees USING (loc_code)
INNER JOIN departments USING (dept_code)
GROUP BY location;

which should give a table

location 	Development 	Personnel 	Research 	Sales 	Training 	total
Boston 	11900 		0 		0 		5950 		0 		17850
Cagliari 	0 			0 		16800 	0 		0 		16800
London 	0 			5700 		0 		0 		5700 		11400

My question is: What are the equivalent python statements to produce the
results from the above couple of examples, and what is the best way to
design OO databases, would it be better to build a number of dictionaries to
contain each specific type ie  for the different tables, if so what relates
each table/dictionary to each other?

Many thanks

Norman



zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zz/********/z/****\zzz|****\*\zz|*******|z
z/^^^^^^^^/z/******\zz|*^^^^|*|z|*|^^^^^|z norman khine
zzzzzz/**/z|**/^^\**|z|*|zzz|*|z|*|zzzzzzz mailto:norman@khine.net
zzzzz/**/zz|*|zzzz|*|z|****/*/zz|*****|zzz purley
z/******/zz|*|zzzz|*|z|*|^^zzzzz|*|^^^|zzz UK
zzZ/**/zzzz|**\^^/**|z|*|zzzzzzz|*|zzzzzzz
zz/******/zz\******/zz|*|zzzzzzz|*|*****|z
z/^^^^^^/zzzz\^^^^/zzz|^|zzzzzzz|^^^^^^^|z
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz