What would be the best way to generate a summary of the 'count(*) 'of all my database table columns?

[Cody Goodman]

I have multiple databases:

dog

-------

type

color

sound

car

------

model

maker

condition

I need to generate a grid with the table names, then under them the number of columns there are. I have made multiple DAL objects for each database like so:

dog = DAL('mysql://root:@localhost/dog)

car = DAL('mysql://root:@localhost/car)

I then thought I could just make select statements getting the count of each column like this:

number_of_dog_colors = len(db().select(dog.type.color))

Of course my syntax for that is wrong and I got a :

<type 'exceptions.AttributeError'> 'function' object has no attribute 'color'

I was just wondering what the correct syntax to do that was, and if I'm going about it correctly. Once I get the syntax correct, I was going to make a list of all the table names, then get the count for each. If anyone knows a better way to do this, please let me know.

Thanks in advance for any help.

Sincerely,

Cody Goodman

[Jim Steil]

How about this?

number_of_dog_colors = db(db.dog.id>0).count(distinct=True)

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2411/4947 - Release Date: 04/19/12

--
Jim Steil
VP of Information Technology
Quality Liquid Feeds, Inc.
608.935.2345 office
608.341.9896 cell

[Cliff]

Is this what you want?

Table   |  column count

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

dog     | 3

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

car     | 3

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

This will get the data.

For table in db.tables:

  column_count = 0

    for field in table.fields

      column_count += 1

  print table.name

  print column_count

[Cody Goodman]

That is close to what I need guys, but I should have made a better example. I have a legacy database called my_legacy_db which is separate from the normal db.

my_legacy_db

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

users

 - email

 - username

 - name

So cliff, your first part would work to generate field names and put everything in a dict to build the query's. The problem is when I do this query:

db().select(my_legacy_db.users)

I get this error:

In [20] : db().select(my_legacy_db.users)

Traceback (most recent call last):

  File "/opt/web-apps/web2py/gluon/contrib/shell.py", line 233, in run

    exec compiled in statement_module.__dict__

  File "<string>", line 1, in <module>

  File "/opt/web-apps/web2py/gluon/dal.py", line 7578, in select

    return adapter.select(self.query,fields,attributes)

  File "/opt/web-apps/web2py/gluon/dal.py", line 1307, in select

    sql = self._select(query, fields, attributes)

  File "/opt/web-apps/web2py/gluon/dal.py", line 1196, in _select

    raise SyntaxError, 'Set: no tables selected'

SyntaxError: Set: no tables selected

So I know how to fix the problem, but I don't know why I can't query my_legacy_db.

[Khalil KHAMLICHI]

db(db.legacy-db.table).select()

I think you are forgetting "db." in front of table name.

[Cody Goodman]

Well I tried that, and it gave me:

In [2] : db(db.legacy_db.table).select()

Traceback (most recent call last):
  File "/opt/web-apps/web2py/gluon/contrib/shell.py", line 233, in run
    exec compiled in statement_module.__dict__
  File "<string>", line 1, in <module>

  File "/opt/web-apps/web2py/gluon/dal.py", line 6343, in __getattr__
    return self[key]
  File "/opt/web-apps/web2py/gluon/dal.py", line 6337, in __getitem__
    return dict.__getitem__(self, str(key))
KeyError: 'legacy_db

I believe that the DAL doesn't like how my database is made, so I'm just going to rebuild it with the ORM in web2py.'

[Cédric Mayer]

I suppose you have somewhere
my_legacy_db = DAL(...)

In the "db()" part, you need a query (unless in the select, you have the list of fields (not tables) to retrieve), like:
my_legacy_db(my_legacy_db.users.id > 0) or my_legacy_db(my_legacy_db.users.name.contains('foo'))
or (selectgin fields) :
my_legacy_db().select(my_legacy_db.users.ALL) or my_legacy_db().select(my_legacy_db.users.email,my_legacy_db.users.username)

Then you can select a specific field:
my_legacy_db(my_legacy_db.users.id > 0).select(my_legacy_db.users.email)
Or you can count distinct elements (like names ?)
my_legacy_db().count(my_legacy_db.users.name, distinct=True)