Ed Leafe wrote:
> On Mar 3, 2009, at 4:17 PM, Ricardo Aráoz wrote:
>
>> Finally I'd like to report that crsr.getDataSet() is not as comfortable
>> as it might be. Sometimes the key will be only 'Name' (in the example
>> above), some other times it will be 'menu.name' (notice in the previous
>> case 'Name' was cased) but some other times I use menu.name in the query
>> and the key will be 'Name'. So I haven't found a rule for the naming of
>> keys and I have to previously test the queries in order to know the key
>> names and get the data I need into a list.
>
> I'm not exactly clear as to what the confusion is here. When you
> run a query, the result returned by the database has some sort of name
> identifying each column in the result set: either the actual column
> name, or an alias if your query used the 'as' clause, or a
> DB-determined name such as 'SUM(mycol)'. The key in the dataset will
> always be the value returned by the dbapi for that column.
>
I've got an example here (after each query there is an output and a
comment) :
--------------------------------------------------------------------------
ci = dabo.db.dConnectInfo(DbType='SQLite')
ci.Database = 'MultiMenu.db'
conn = dabo.db.dConnection(ci)
crsr = conn.getDaboCursor()
menu = 'Hijo'
crsr.execute('select Name '
'from menu '
'inner join path '
'on path.Id = menu.MenuId '
'group by path.Id '
'having count(*) = 1') # get the
children of root
crsr.getDataSet()
<Output> ({'Name': u'Hijo'}, {'Name': u'OtroHijo'}) </Output>
<Comment> This is ok </Comment>
crsr.execute('select menu.Name '
'from menu '
'inner join path '
'on path.Id = menu.MenuId '
'group by path.Id '
'having count(*) = 1') # get the children of root
crsr.getDataSet()
<Output> ({'menu.Name': u'Hijo'}, {'menu.Name': u'OtroHijo'}) </Output>
<Comment> This is ok (notice now it is "menu.Name"
instead of "Name") </Comment>
crsr.execute('select distinct menu.Name '
'from menu '
'inner join path '
'on path.Id = menu.MenuId '
'group by path.Id '
'having count(*) = 1') # get the children of root
crsr.getDataSet()
<Output> ({'menu.Name': u'Hijo'}, {'menu.Name': u'OtroHijo'}) </Output>
<Comment> This is ok (with "distinct" I still get the
same result "menu.Name") (1) </Comment>
crsr.execute("select distinct menu.Name "
"from menu "
"inner join path "
"on path.Id = menu.MenuId "
"left join (select MenuId as 'pId' "
"from menu "
"where name = '" + menu + "') Parent "
"where Id in (select Id "
"from path "
"where AncestorId = Parent.pId) "
"and Id not in "
"(select Id "
"from path "
"where Id in(select Id "
"from path "
"where AncestorId =
Parent.pId) "
"and AncestorId not in "
"(select AncestorId "
"from path "
"where Id = Parent.pId) "
"and AncestorId != Parent.pId) ")
crsr.getDataSet()
<Output> ({'Name': u'Hijo-1'}, {'Name': u'Hijo-2'}) </Output>
<Comment> Ooops!!! Here I'm using "select distinct menu.name" in the
main select
as in the previous query (1) but I'm getting "Name" as key
instead of "menu.Name".
This is the inconsistency I was talking about.
</Comment>
--------------------------------------------------------------------------
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/[email protected]
