Aha!  Found and fixed some bugs in my apsw wrapper for stuff that I do not do 
often (such as using * to retrieve duplicate column names as I typically always 
use AS to name columns explicitly, so the Row was being built with duplicate 
names).  Also, removed the code that disabled the long_column_names support as 
it does not actually require changes to the APSW base, those only being 
required if I want to fetch the underlying column metadata).

from __future__ import absolute_import, division, print_function, 
import newapsw
import apsw
db = apsw.Connection('')
create table addresses( id, name );
create table users( id, address_id, name );
create table pets(id, user_id, name );
insert into addresses(id,name) values ( 1, 'there' );
insert into users(id,address_id,name) values ( 1, 1, 'bob' );
insert into pets(id,user_id,name) values ( 1,1, 'odif' );
cr1 = db.execute('''
select *
  from users user
  join addresses address
    on address.id=user.address_id
  join pets pet
    on pet.user_id=user.id;
print(1, cr1.fetchone())
cr2 = db.execute('''
select address.id      AS address_id,
       address.name    AS address_name,
       user.id         AS user_id,
       user.address_id AS user_address_id,
       user.name       AS user_name,
       pet.id          AS pet_id,
       pet.user_id     AS pet_user_id,
       pet.name        AS pet_name
  from users AS user
  join addresses AS address
    on address.id == user.address_id
  join pets AS pet
    on pet.user_id == user.id;
print(2, cr2.fetchone())
pragma full_column_names=1;
pragma short_column_names=0;
cr3 = db.execute('''
select *
  from users user
  join addresses address
    on address.id=user.address_id
  join pets pet
    on pet.user_id=user.id;
print(3, cr3.fetchone())

>py -3 sample.py
1 Row(id=1, address_id=1, name='bob', id_1=1, name_1='there', id_2=1, 
user_id=1, name_2='odif')
2 Row(address_id=1, address_name='there', user_id=1, user_address_id=1, 
user_name='bob', pet_id=1, pet_user_id=1, pet_name='odif')
3 Row(user_id=1, user_address_id=1, user_name='bob', address_id=1, 
address_name='there', pet_id=1, pet_user_id=1, pet_name='odif')

db.execute => db.cursor().execute
The Row object is just built by an exehook and a rowhook getting the column 
names from the cursor.description and mangling them somewhat so that the Row 
has distinct names in its list of column names so that the column name can be 
accessed as an attribute of the Row ...
If you want to see how it all works get http://www.dessus.com/files/<name> 
where <name> is one of apsw-monolith27-gcc32.zip, apsw-monolith36-gcc64.zip, 
apsw-monolith37-gcc64.zip.  The python code is the same in all of them, just 
the compiled apsw pyd is different.  import newapsw to activate all the stuff, 
then import apsw will have it all in the normal apsw library (the newapsw 
module is substituted for the apsw module name in sys.modules).

The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

sqlite-users mailing list

Reply via email to